Question 1

Explain the distinctions among the terms primary key, candidate key, and superkey.

A superkey is a set of one or more attributes that, taken collectively, allows us to identify uniquely an entity in the entity set. A superkey may contain extraneous attributes. If K is a superkey, then so is any superset of K. A superkey for which no proper subset is also a superkey is called a candidate key. It is possible that several distinct sets of attributes could serve as candidate keys. The primary key is one of the candidate keys that is chosen by the database designer as the principal means of identifying entities within an entity set.

Question 2

A university registrar's office maintains data about the following entities:

• courses, including number, title, credits, syllabus, and prerequisites;
• course offerings, including course number, year, semester, section number, instructor(s), timings, and classroom;
• students, including student-id, name, and program; and
• 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. Document all assumptions that you make about the mapping constraints. Construct appropriate tables for this E-R diagram.

Question 3

We can convert any weak entity set to a strong entity set by simply adding appropriate attributes. Why, then, do we have weak entity sets?

• We want to avoid the data duplication and consequent possible inconsistencies caused by duplicating the key of the strong entity.
• Weak entities reflect the logical structure of an entity being dependent on another entity.
• Weak entities can be deleted automatically when their strong entity is deleted.
• Weak entities can be stored physically with their strong entities.

Question 4

Consider a database used to record the marks that students get in different exams of different course offerings.

• Construct an E-R diagram that models exams as entities, and uses a ternary relationship, for the above database.
• Construct an alternative E-R diagram that uses only a binary relationship between students and course-offerings. Make sure that only one relationship exists between a particular student and course-offering pair, yet you can represent the marks that a student gets in different exams of a course offering.

Question 5

Consider a university database for the scheduling of classrooms for final exams. This database could be modeled as the 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 to include each of the additional entity sets.

The additional entity sets are useful if we wish to store their attributes as part of the database. For the course entity set, we have chosen to include three attributes. If only the primary key (c-number) were included, and if courses have only one section, then it would be appropriate to replace the course (and section) entity sets by an attribute (c-number) of exam. The reason it is undesirable to have multiple attributes of course as attributes of exam is that it would then be difficult to maintain data on the courses, particularly if a course has no exam or several exams. Similar remarks apply to the room entity set.