I'm working through a Database Design textbook, and one of the problems involving ER Diagram design is giving me some trouble.
The diagram is for a UNIVERSITY database with the following rules:
1) The university keeps track of each student’s name, a unique student number, a unique Social Security number, address (split into street address, city, state, and zip code), phone number, birth date, sex, level (Freshman, etc.), major department, and degree program (BS, MS, PhD).
2) Each department is described by a unique name, a unique department code, office phone number, and the college it belongs to.
3) Each course has course name, a unique course number, description, number of credit hours, and offering department.
4) Each section has an instructor, semester, year, course, and section number. The section number distinguishes sections of the same course that are taught during the same semester/year, its values being 1, 2, 3, …, up to the number of sections taught during each semester.
5) A grade report has a student, section, a letter grade (F, D, C, B, A), and a numeric grade (0,…, 4).
Specify key attributes of each Entity Type, and structural constraints (using max-min notation) on each Relation Type. For incomplete specifications, if any, make appropriate assumptions, and document them precisely.
This is what I've got so far:
A few questions I couldn't find answers to in the text:
1.) Can a Weak entity type have multiple partial keys?
2.) Can a Weak entity type serve as the identifying entity type for another Weak entity type (at the time of writing this, I'm guessing the answer is "no", but when I was making the draft I thought it might be possible)
3.) The text says it's possible for an entity to have multiple private keys, but is there some "best practice" limit to how many private keys an entity should have?
4.) The problem specifies that the SECTION and GRADE_REPORT entities should have a Course and Section attribute, respectively. However, the Section entity type is already in an identifying relationship with the Course entity type (and the Grade_Report type with the Section type, though as stated above I'm not sure if that's legal), so that seems a little redundant. Should these attributes be deleted, or am I maybe supposed to make them the partial keys of their individual types?
