CMSC424: Database Design

1. CMSC424: Database Design Instructor: Amol Deshpande amol@cs.umd.edu

2. Today • Recap: Relational Model • E/R Model to an Relational Model • Remember: We still use E/R models for conceptual modeling of the database • Relational Algebra • Operating on the relations

3. Database relations Given attribute domains Branches = { Downtown, Brighton, … } Accounts = { A-101, A-201, A-217, … } Balances = R Review: Relational Data Model Key Abstraction: Relation Mathematical relations • Given sets: R = {1, 2, 3}, S = {3, 4} • R  S = { (1, 3), (1, 4), (2, 3), (2, 4), (3, 3), (3, 4) } • A relation on R, S is any subset () of R  S (e.g: { (1, 4), (3, 4)}) Account Branches  Accounts  Balances { (Downtown, A-101, 500), (Brighton, A-201, 900), (Brighton, A-217, 500) }

4. Review: Terms and Definitions • Tables = Relations • Columns = Attributes • Rows = Tuples • Relation Schema (or Schema) • A list of attributes and their domains • We will require the domains to be atomic • E.g. account(account-number, branch-name, balance) • Relation Instance • A particular instantiation of a relation with actual values • Will change with time

5. So… • That’s the basic relational model • That’s it ? • What about the constraints ? • How do we represent one-to-one vs many-to-one relationships ? • Many of those constraints get embedded in the schema • Especially relationship cardinality constraints • Others are explicitly represented using other constructs

6. E/R Diagrams  Relations • Convert entity sets into a relational schema with the same set of attributes Customer Customer_Schema(cname, ccity, cstreet) ccity cname cstreet assets bcity bname Branch_Schema(bname, bcity, assets) Branch

7. balance acct-no Account access-date Depositor Customer ccity cname cstreet E/R Diagrams  Relations • Convert relationship sets also into a relational schema • Remember: A relationship is completely described by primary keys of associate entities and its own attributes Account_Schema(acct-no, balance) Depositor_Schema(cname, acct-no, access-date) Customer_Schema(cname, ccity, cstreet) Well… Not quite. We can do better. It depends on the relationship cardinality

8. E/R Diagrams  Relations • Say One-to-Many Relationship from Customer to Account •  Many accounts per customer balance acct-no Account_Schema(acct-no, balance, cname, access-date) Account access-date Depositor Customer Customer_Schema(cname, ccity, cstreet) ccity cname cstreet Exactly same information, fewer tables

9. E/R Diagrams  Relations E1 a1 … an

10. R E1 E2 b1 bm a1 … an … c1 ck … E/R Diagrams  Relations E1 a1 … an Not the whole story for Relationship Sets …

11. R E1 E2 b1 bm a1 … an … c1 ck … E/R Diagrams  Relations R CMSC424, Spring 2005

12. R E1 E2 b1 bm a1 … an … c1 ck … E/R Diagrams  Relations R R CMSC424, Spring 2005

13. R E1 E2 b1 bm a1 … an … c1 ck … E/R Diagrams  Relations R R R CMSC424, Spring 2005

14. R E1 E2 b1 bm a1 … an … c1 ck … E/R Diagrams  Relations R R R R CMSC424, Spring 2005

15. Translating E/R Diagrams to Relations assets bcity balance bname acct_no Acct-Branch Account Branch Loan-Branch Depositor Borrower Customer Loan ccity cname amt lno cstreet Q. How many tables does this get translated into? A. 6 (account, branch, customer, loan, depositor, borrower)

16. Bank Database

17. IR E1 E2 b1 bm a1 … an … E/R Diagrams & Relations

18. Employee name ssn phone E/R Diagrams & Relations Emp Emp-Phones

19. an … a1 E ISA E1 E2 bm ck c1 b1 … … E/R Diagrams & Relations

20. an … a1 E ISA E1 E2 bm ck c1 b1 … … E/R Diagrams & Relations

21. E/R Diagrams & Relations • Subclasses example: • Method 1: • Account = (acct_no, balance) • SAccount = (acct_no, interest) • CAccount = (acct_no, overdraft) • Method 2: • SAccount = (acct_no, balance, interest) • CAccount = (acct_no, balance, overdraft) Q: When is method 2 not possible? A: When subclassing is partial