CMSC424 Database Design Instructor Amol Deshpande amol cs umd edu Stuff SQL Assignment Accounts Questions on E R Model Today Recap E R Relational Model Model to an Relational Model Remember We still use E R models for conceptual modeling of the database Relational Algebra Operating on the relations 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 Database relations Given attribute domains Branches Accounts Balances Downtown Brighton A 101 A 201 A 217 R Account Branches Accounts Balances Downtown A 101 500 Brighton A 201 900 Brighton A 217 500 bname acct no balance Downtown Brighton Brighton A 101 A 201 A 217 500 900 500 Review 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 Convert relationship sets also into a relational schema Remember A relationship is completely described by primary keys of associate entities and its own attributes acct no balance Account Schema acct no balance Account access date Depositor Schema cname acct no access date Depositor Customer cname ccity Customer Schema cname ccity cstreet cstreet Well Not quite We can do better It depends on the relationship cardinality 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 Exactly same information fewer tables 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 a1 E 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 E R 1 a1 R 2 b1 an c1 n m E bm Ec1k a1 an E2 b1 bm R a1 b1 c1 cn CMSC424 Spring 2005 E R Diagrams Relations Relationship Cardinality Relational Schema E R 1 a1 2 b1 an c1 n m E bm c R E1k a1 an E2 b1 bm R a1 b1 c1 cn R E1 a1 an b1 c1 cn E2 b1 bm n 1 CMSC424 Spring 2005 E R Diagrams Relations Relationship Cardinality Relational Schema E R 1 a1 n m n 1 R 1 n R 2 b1 an c1 R E bm Ec1k a1 an E2 b1 bm R a1 b1 c1 cn E1 a1 an b1 c1 cn E2 b1 bm E1 a1 an E2 b1 bm a1 c1 cn CMSC424 Spring 2005 E R Diagrams Relations Relationship Cardinality Relational Schema E R 1 a1 n 1 1 n 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 1 CMSC424 Spring 2005 Translating E R Diagrams to Relations acct no balance Account bname Loan Branch Customer ccity Branch Acct Branch Depositor cname assets bcity Loan Borrower cstreet lno amt Q How many tables does this get translated into A 6 account branch customer loan depositor borrower Bank Database Account Branch 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 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 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 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 ssn phone name ssn phone 001 Smith 001 001 4 1234 4 5678 ssn Emp 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 E R Diagrams Relations E R Relational Schema Subclasses a1 Method 1 E a1 an an E ISA E1 b1 c1 a1 b1 bm E2 a1 c1 ck Method 2 E1 a1 an b1 bm E2 bm E1 E2 ck a1 an c1 ck 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 Today Recap E R Relational Model Model to an Relational Model Remember We still use E R models for conceptual modeling of the database Relational Algebra Operating on the relations 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 Keys Superkey Candidate key set of attributes of table for which every row has distinct set of values Minimal such set of attributes …
View Full Document
Unlocking...