Assignment 1
You are free to make additional assumptions, if you feel that some information is missing. Make sure to document all assumptions that you make. All answers should be your own. You are allowed to work in groups of two. Make sure you include your names in the report when you submit.
Tasks
1. MoviesDB (FDS 3.28)
Assume that MoviesDB is a populated database. Given the constraints shown in the E/R diagram, respond to the following statements with True, False, or Maybe. Assign a response of Maybe to statements that, although not explicitly shown to be True, cannot be proven False based on the schema as shown. Justify each answer.
1. There are no actors in this database that have been in no movies. 2. There are some actors who have acted in more than ten movies. 3. Some actors have done a lead role in multiple movies.
4. A movie can have only a maximum of two lead actors.
5. Every director has been an actor in some movie.
6. No producer has ever been an actor.
7. A producer cannot be an actor in some other movie. 8. There are movies with more than a dozen actors.
9. Some producers have been a director as well.
10. Most movies have one director and one producer.
11. Some movies have one director but several producers.
12. There are some actors who have done a lead role, directed a movie, and produced a movie. 13. No movie has a director who also acted in that movie.
2. Births (DBS 2.2.6 and 2.2.7)
Consider a model where an entity set Births is related to Babies, Mothers, Doctors, and Nurses by four binary relationships. How can you use multiplicity to represent the following conditions?
1. Every baby is the result of a unique birth, and every birth is of a unique baby. 2. In addition to (1), every baby has a unique mother.
3. In addition to (1) and (2), for every birth there is a unique doctor.
In each case, what design flaws do you see?
Suppose we change our viewpoint to allow a birth to involve more than one baby born to one mother. How would you represent the fact that every baby still has a unique mother?
3. The registrar’s office (DSC 6.2)
A university registrar’s office maintains data about the following entities: (a) courses, include number, title, credits, syllabus, and prerequisites; (b) course offerings, include course number, year, semester, section number, instructor(s), timings, and classroom; (c) students, including student-id, name, and program; and (d) instructors, including identification number, name, department, and title. Further, the enrollment of students in courses and grades awarded to students in each course they are enrolled for must be appropriately modeled.
Construct an E/R diagram for the registrar’s office.
4. Classroom scheduling (DSC 6.6)
Consider a university database for the scheduling of classrooms for the final exams. This database could be modeled as a single entity set exam with attributes course_name, section_number, room_number, and time. Alternatively, one or more additional entity sets could be defined, along with relationship sets to replace some of the attributes of the exam entity set, as:
course with attributes name, department, and c_number
section with attributes s_number and enrollment, and dependent as a weak entity set on course. room with attributes r_number, capacity, and building
Show an E/R diagram illustrating the use of all three additional entity sets listed.
Explain what application characteristics would influence a decision to include or not include each of the additional entity sets.
Note: A section is a part of course. How sections are used varies from university to university, but they could for example be used to separate multiple versions of the course (imaging that a course has so many students that there has to be parallel lectures) or if a course is given multiple times per year.
Submission and deadlines
Your submission should include solutions to all assignments above.
Submit a report in PDF format on Moodle. You can draw the E/R diagrams by hand and submit scanned versions or photos (as long as they are readable).
Deadline: End of day 2018-11-19.