Description
Exercise #1
Provide a relational schema in 3NF following bubble diagrams. The name given to
tables should be more significant.
AUTHOR (auteur_id, name, telephone, local)
Author_id name
telephone local
REVISION (report_id, title, pages, date_revision, code_status, status)
report_id title
pages
code_status status date_revision
REPORT_SUBMITTED (report_id, title, pages, author_id, rank, name, telephone)
Author_id name
telephone
title
pages
report_id rank
Exercise #2
A management organization of shows, concert halls, and ticket sales for shows manages a
database with the following relational schema:
Show (show_id, title, start_date, duration, singer, hall_id)
Concert (concert_id, date, hour, show_id)
Hall (hall_id, name, address, capacity)
Express the following SQL queries in relational algebra:
1) What are the dates of the concert of Lionel Richie at the Bell Center?
SELECT date
FROM Concert C, Show S, Hall H
WHERE C.show_id = S.show_id
AND S.hall_id = H.hall_id
AND S.singer = ‘Lionel Richie’
AND H.name = ‘Bell Center’
2) What are the singers who have never scheduled the concert at Saint-Denis 2?
SELECT singer
FROM Show
WHERE singer NOT IN (SELECT singer
FROM Show S, Hall H
WHERE S.hall_id = H.hall_id
AND H.name = ‘Saint-Denis 2’)
3) What are the singers who performed at least one concert in all halls? Here you
must use the division operator
SELECT singer
FROM Show S1
WHERE NOT EXISTS
(SELECT *
FROM Hall H
WHERE NOT EXISTS
(SELECT *
FROM Show S2
WHERE S2.singer = S1.singer
AND H.hall_id = S2.hall_id))
Submitting Assignment #4
– Naming convention Acrobat file: Create one file .pdf, containing a solution of
your assignment using the following naming convention:
The pdf file should be called A4_TeamName, where TeamName is
your student group name.
– Submit your file .pdf in the appropriate assignment folder via ENCS Website. The
deadline is not respected would be discarded and no replacement submission will
be allowed.
– Submit only ONE version for each team of your assignment. It is not an
individual submission. If more than one version is submitted the last one, before
the deadline date, will be graded and all others will be disregarded.
Evaluation Criteria of Assignment #4 (50 points)
Activities Points
Exercise #1:
– Provide a 6 relational schema in 3NF following bubble diagrams:
o 5 pts. each
30 pts.
Exercise #2:
– Express the three SQL queries in relational algebra:
o a.5pts.
o b. 7 pts.
o c. 8 pts.
20 pts.