CMSC424: Database Design

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

2. Stuff • SQL Assignment • Accounts • Questions on E/R Model ?

3. 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

4. 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) }

5. 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

6. 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

7. 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

8. 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 balance acct-no Account_Schema(acct-no, balance) Account access-date Depositor_Schema(cname, acct-no, access-date) Depositor Customer Customer_Schema(cname, ccity, cstreet) Well… Not quite. We can do better. It depends on the relationship cardinality ccity cname cstreet

9. 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 Exactly same information, fewer tables cstreet

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

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

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

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

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

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

16. 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)

17. Bank Database

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

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

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

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

22. 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

23. 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

24. Keys and Relations • Recall: • Keys: Sets of attributes that allow us to identify entities • Very loosely speaking, tuples === entities • Just as in E/R Model: • Superkeys, candidate keys, and primary keys

25. Keys • Superkey • set of attributes of table for which every row has distinct set of values • Candidate key • Minimal such set of attributes • Primary key • DB Chosen Candidate key • Plays a very important role • E.g. relations typically sorted by this

26. Invalid!! Keys • Also act as integrity constraints • i.e., guard against illegal/invalid instance of given schema e.g., Branch = (bname, bcity, assets)

27. Keys • In fact, keys are one of the primary ways to enforce constraints/structure • Consider a one-to-many relationship e.g. • Between customers and accounts • The relational model will be: • Customers(custid, custname,…) • Accounts(accountid, custid, balance,…) • Allows for multiple accounts per customer, but not multiple customers per account • Not possible to store such information • In other words, constraints will lead to less representation power • Contrast with: • Customers(custid, custname,…) • Accounts(accountid, balance,…) • CustomerHasAccounts(custid, accountid)

28. More on Keys • Determining Primary Keys • If relation schema derived from E-R diagrams, we can determine the primary keys using the original entity and relationship sets • Otherwise, same way we do it for E-R diagrams • Find candidate keys (minimal sets of attributes that can uniquely identify a tuple) • Designate one of them to be primary key • Foreign Keys • If a relation schema includes the primary key of another relation schema, that attribute is called the foreign key

29. Schema Diagram for the Banking Enterprise

30. Next • Query language for operating on the relations • Theoretical: • Relational Algebra • Tuple Relational Calculus • Domain Relational • Practical: • SQL (loosely based on TRC) • Datalog

31. Next… • Query language for operating on the relations • Theoretical: • Relational Algebra • Tuple Relational Calculus • Domain Relational • Practical: • SQL (loosely based on TRC) • Datalog

33. Example Queries • Find the names of all customers who have a loan at the Perryridge branch. customer_name (branch_name=“Perryridge” (borrower.loan_number = loan.loan_number(borrower x loan))) • Find the names of all customers who have a loan at the Perryridge branch but do not have an account at any branch of the bank. customer_name (branch_name = “Perryridge” (borrower.loan_number = loan.loan_number(borrower x loan))) – customer_name(depositor)

34. Example Queries • Find the names of all customers who have a loan at the Perryridge branch. • Query 1customer_name (branch_name = “Perryridge”( borrower.loan_number = loan.loan_number (borrower x loan))) • Query 2 customer_name(loan.loan_number = borrower.loan_number ( (branch_name = “Perryridge” (loan)) x borrower))

35. Example Queries • Find the largest account balance • Strategy: • Find those balances that are not the largest • Rename account relation as d so that we can compare each account balance with all others • Use set difference to find those account balances that were not found in the earlier step. • The query is: balance(account) - account.balance (account.balance < d.balance(accountxrd(account)))