E/R Exercises – Part I

1. E/R Exercises – Part I October 5, 2014

2. Database Design Sequence

3. Entity set Weak entity set Relationship set Attribute Weak relationship set Multiplicity of relationships Referential integrity Review

4. Different Sets of Notations • Two different sets of ER notations are commonly used. • One from Ullman’s book as shown on the last slide • Another from Ramakrishnan’s book with main differences in: - arrow always pointing to “relationship”; - notations of partial or total participation constrains; - notation of aggregations.

5. Exercise #1 - 1 • A database for abank includes information about customers and their accounts. • Information about customer includes name, address, phone, and social security number. • Accounts have numbers, type (saving, checking), and balances. • An account can be owned by several customers, and a customer can have multiple accounts.

6. number type balance name address phone own Customer Account SSN Exercise #1 - 2

7. number type balance name address phone own Customer Account SSN Exercise #2 Change your design so that an account can have exactly one customer

8. number type balance name address phone own Customer Account SSN Exercise #3 Further change your design so a customer can have at most one account

9. Exercise #4 - 1 • Change your original design so that a customer can have a set of addresses (street, city, state) and a set of phones. • Remember that we do not allow non-atomic types for attributes.

10. number type balance Exercise #4 - 2 street city Address Phone number state Lives at has name own Customer Account SSN

11. Exercise #5 - 1 • Further change your original design so that a customer can have a set of addresses. Also, at each address, there is a set of phones.

12. number type balance Exercise #5 - 2 street city Address Phone number has state Lives at name own Customer Account SSN

13. Exercise #6 - 1 • Design a database for a university registrar. • This database should include information about students, departments, professors, courses, which student are enrolled in which courses, which professors teach which courses, student grades, TA for the course (TAs are students), which courses a department offers, and any other information you deem appropriate.

14. Exercise #6 – Solution #1 position EID semester section section tutor of teaches Professor hired semester TA grade semester section enroll Course offer Student Department number name credit name name address phone id Is this a good design? OK, but not a good design. “semester” and “section” appear in several relationships -> Can’t guarantee the consistency of DB easily

15. Exercise #6 – Solution #2 position EID Professor hired TA section semester isIn Course offer Student Department grade number name credit name name address phone id No. Lots of redundancy (in “isIn”), although the E/R diagram itself looks simpler Is this a good design?

16. Exercise #6 – Solution #3 position EID tutor of teaches Professor hired TA grade section semester enroll Course offer Student Department number name credit name name address phone id This solution has less redundancy comparing to the previous 2. But redundancy still exists in “Course”. Is this a good design?

17. Exercise #6 – Solution #4 position EID tutor of teaches Professor hired semester Available Course TA section enroll Course offer Student Department grade number name credit name name address phone id Yes! Is this a good design?