Database Systems {week 04b}

1. Rensselaer Polytechnic Institute CSCI-4380 – Database Systems David Goldschmidt, Ph.D. Database Systems{week 04b}

2. Entity/Relationship (E/R) models • Modeling a database begins by identifying the information to be stored • Need to also define how information elements are related to one another • Further, define constraintson the information,including defining keys,referential integrity, etc.

3. Entities and entity sets • An entity is a building block of our database, abstracting an object of some sort • e.g. movie, faculty member, student • An entity set is a collection of similar entities • e.g. all movies, all faculty members, all students • This is similar to the object/class conceptsof OOP, but there are no methods/functions

4. Keys and attributes • Each entity set has a keyand (usually) other attributes • The key consists of one or more attributes • The key should functionally determineall other attributes! • Entity sets should be in BCNF or 3NF • Entity sets should not have attributes thatrelate to other entity sets, unless they are foreign keys

5. E/R diagram keys are underlined • An E/R diagram is a graph representing: • Entity sets (rectangles) • Attributes (ovals) • Relationships (diamonds) • i.e. connections between two or more entity sets id Students name enroll-in E/R diagrams are a notationfor describing database schemas Courses

6. Many-to-many relationships • Each course has many enrolled students • Each students enrolls in many classes • Note that many implies zero or more Students enroll-in Courses

7. One-to-many relationships • Each department has many faculty • Each faculty member belongs to at most one department (so zero or one) “at most one” Faculty in Departments

8. One-to-one relationships • Each department has at most one chair • Each faculty member can be chair of at most one department Faculty chair-of Departments

9. Three-way relationships • For a particular major and student, there is at most one faculty member who is the advisor • A faculty member may advise many students in many majors Students advised-by Faculty Majors

10. Three-way relationships • A student enrolls in a course with at most one department code (e.g. CSCI) • Is this the correct relationship? Students enroll-in Courses DeptCodes (e.g. CSCI)

11. Revising three-way relationships • The department code is actuallyfunctionally determined by thecourse itself (e.g. by CRN) • Note that this does not account for cross-listed courses.... Students enroll-in Courses has DeptCodes (e.g. CSCI)

12. Revising three-way relationships • Is this the correct relationship? • It is correct (and necessary) if a faculty member can advise students across multiple majors • Otherwise, each faculty should functionally determine the major s/he advises Students advised-by Faculty Majors

13. Recursive relationships (and roles) sequel-of • A movie may have many sequels • For each sequel, there is at most one original movie original Movies sequel role exactly one in this case...

14. Relationship attributes grade • The grade attribute is associated withthe given relationship • And involves both a student and a course • This is merely a shortcut.... Students enroll-in Courses

15. Removing relationship attributes Grades grade • The grade attribute of the Grades entity set (relation) is functionally determined by the entire tuple involving Students and Courses • i.e. a student enrolled in a course has a grade Students enroll-in Courses

16. Exercises • Go back to the relations we’veworked with in class and createE/R diagrams for each • e.g. MusicGroup, Company • Also create a detailed E/R diagram for the celebrities relations (and relationships!) • see next slide....

17. Exercises