Assignment 1: ER Diagram and Relational Model

Assignment 1: ER Diagram and Relational Model
Weight: 8%. Total=61 marks
Question 1 (18 marks, 3 marks each). For each of the following statements, represent it using CREATE
TABLE statement. Note that there are both entity sets and relationship sets to be considered.
1. Patients attend doctors. You want to store the attending information and the date of attending.
Patients identified by pid. Doctors is identified by did.
2. Continue 1, each patient attends doctors at most once.
3. Continue 2, each patient attends doctors at least once.
4. Continue 1, only existing doctors can be attended by a patient.
5. Continue 1, every doctor must be attended by a patient.
6. Continue 1, each of (Name, Address) and (Name,Age) uniquely identifies a patient.
Question 2 (18 marks, 3 marks each) A university database contains information about professors
(identified by ssn) and courses (identified by courseid). Professors teach courses; each of the following
situations concerns the Teaches relationship set. For each situation, draw an ER diagram that describes
it (assuming that no further constraints hold) and using CREATE TABLE to model the information in the
ER diagram.
1. Professors can teach the same course in several semesters, and each offering must be recorded.
2. Professors can teach the same course in several semesters, and only the most recent such offering
needs to be recorded. (Assume this condition applies in all subsequent questions.)
3. Every professor must teach some course.
4. Every professor teaches exactly one course (no more, no less).
5. Every professor teaches exactly one course (no more, no less), and every course must be taught by
some professor.
6. Now suppose that certain courses can be taught by a team of professors jointly, but it is possible that
no one professor in a team can teach the course. Model this
situation, introducing additional entity sets and relationship sets if necessary.
Question 3 (25 marks). You are asked to set up a database, ArtBase, for art galleries. This database will
capture all the information that galleries need to
Maintain:
• Galleries keep information about artists, their names (which are unique), birthplaces, age, and
style of art.
• For each piece of artwork, the artist, the year it was made, its unique title, its type of art (e.g.,
painting, lithograph, sculpture, photograph), and its price must be stored.
• Pieces of artwork are also classified into groups of various kinds, for example, portraits, still lifes,
works by Picasso, or works of the 19th century; a given piece may belong to more than one
group.
• Each group is identified by a name (like those above) that describes the group.
• Finally, galleries keep information about customers. For each customer, galleries keep their
unique name, address, total amount of dollars they have spent in the gallery (very important!),
and the artists and groups of art that each customer tends to like.
(1) Draw the ER diagram for the database (10 marks)
(2) Represent the data in the ER diagram using CREATE TABLE statements (10 marks)
(3) If Artwork as a weak entity set with the partial key title and the owner entity set Artist, describe the
changes needed in (1) and (2) (5 marks)