CMSC 424 Database design Lecture 4 Relational Model ER to Relational model Book Chap 2 and 6 Mihai Pop Administrative issues Oracle accounts SQL homework next week Summary Entity relationship Model Intuitive diagram based representation of domain knowledge data properties etc Two key concepts Entities Relationships We also looked at Relationship cardinalities Keys Weak entity sets Summary Details unimportant Key idea We can represent many data properties and constraints conceptually using this Read Chapter 6 Assignment will require you to do this anyway Relational Data Model Introduced by Ted Codd late 60 s early 70 s Before Network Data Model Cobol as DDL DML Very contentious Database Wars Charlie Bachman vs Mike Stonebraker Relational data model contributes Separation of logical physical data models data independence Declarative query languages Formal semantics Query optimization key to commercial success 1st prototypes Ingres CA Postgres Illustra Informix IBM System R Oracle DB2 Why Called Relations 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 Database relations Given attribute domains Branches Downtown Brighton Accounts A 101 A 201 A 217 Balances R Account Branches Accounts Balances Downtown A 101 500 Brighton A 201 900 Brighton A 217 500 Relations Account bname acct no balance Downtown Brighton Brighton A 101 A 201 A 217 500 900 500 Considered equivalent to Downtown A 101 500 Brighton A 201 900 Brighton A 217 500 Relational database semantics defined in terms of mathematical relations Terms and Definitions 1 2 3 4 Tables Relations Columns Attributes Rows Tuples Relation Schema or Schema 1 2 3 5 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 1 2 A particular instantiation of a relation with actual values Will change with time 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 E R Diagrams Relations Convert entity sets into a relational schema with the same set of attributes Customer cname ccity bname bcity Branch Customer Schema cname ccity cstreet cstreet assets Branch Schema bname bcity assets E R Diagrams Relations Say One to Many Relationship from Customer to Account Many accounts per customer acct no balance Account access date Account Schema acct no balance cname access date Depositor Customer cname ccity Customer Schema cname ccity cstreet cstreet E R Diagrams Relations E R Entity Sets a1 E Relational Schema 1 E a1 an an E R Diagrams Relations E R Entity Sets E a1 Relational Schema 1 E a1 an an Relationship Sets E a1 R 1 E b1 an c1 R a1 b1 c1 cn 2 bm ck Not the whole story for Relationship Sets a1 E1 s key b1 E2 s key c1 ck attributes of R E R Diagrams Relations Relationship Cardinality Relational Schema R E 1 a1 R 2 b1 an c1 n m E bm ck E1 a1 an E2 b1 bm R a1 b1 c1 cn E R Diagrams Relations Relationship Cardinality Relational Schema R E 1 a1 n 1 2 b1 an c1 n m E bm ck R E1 a1 an E2 b1 bm R a1 b1 c1 cn R E1 a1 an b1 c1 cn E2 b1 bm E R Diagrams Relations Relationship Cardinality Relational Schema R E 1 a1 n 1 2 b1 an c1 n m E bm ck R E1 a1 an E2 b1 bm R a1 b1 c1 cn R E1 a1 an b1 c1 cn E2 b1 bm R E1 a1 an E2 b1 bm a1 c1 cn 1 n E R Diagrams Relations Relationship Cardinality Relational Schema R E 1 a1 n 1 2 b1 an c1 n m E bm ck R E1 a1 an E2 b1 bm R a1 b1 c1 cn R E1 a1 an b1 c1 cn E2 b1 bm R E1 a1 an E2 b1 bm a1 c1 cn R Treat as n 1 or 1 n 1 n 1 1 Translating E R Diagrams to Relations acct no balance bname Acct Branch Account Branch Loan Branch Depositor Customer cname ccity Loan Borrower cstreet assets bcity lno amt Q How many tables does this get translated into A 6 account branch customer loan depositor borrower Bank Database Branch Account bname acct no balance bname bcity assets Downtown Mianus Perry R H Brighton Redwood Brighton A 101 A 215 A 102 A 305 A 201 A 222 A 217 500 700 400 350 900 700 750 Downtown Redwood Perry Mianus R H Pownel N Town Brighton Brooklyn Palo Alto Horseneck Horseneck Horseneck Bennington Rye Brooklyn 9M 2 1M 1 7M 0 4M 8M 0 3M 3 7M 7 1M Depositor cname acct no Johnson Smith Hayes Turner Johnson Jones Lindsay A 101 A 215 A 102 A 305 A 201 A 217 A 222 Borrower cname lno Jones Smith Hayes Jackson Curry Smith Williams Adams L 17 L 23 L 15 L 14 L 93 L 11 L 17 L 16 Customer cname cstreet ccity Jones Smith Hayes Curry Lindsay Turner Williams Adams Johnson Glenn Brooks Green Main North Main North Park Putnam Nassau Spring Alma Sand Hill Senator Walnut Harrison Rye Harrison Rye Pittsfield Stanford Princeton Pittsfield Palo Alto Woodside Brooklyn Stanford Loan bname lno amt Downtown Redwood Perry Downtown Mianus R H Perry L 17 L 23 L 15 L 14 L 93 L 11 L 16 1000 2000 1500 1500 500 900 1300 E R Diagrams Relations E R Relational Schema Weak Entity Sets IR E 1 a1 an E1 a1 an E E2 a1 b1 bm 2 b1 bm E R Diagrams Relations E R Relational Schema Multivalued Attributes Emp Emp Phones Employee ssn name phone ssn name 001 Smith Emp ssn name ssn phone ssn 001 001 phone 4 1234 4 5678 Emp Phones E R Diagrams Relations E R Relational Schema Subclasses a1 Method 1 E a1 an an E ISA E1 b1 E2 bm c1 ck E1 a1 b1 bm E2 a1 c1 ck
View Full Document
Unlocking...