CMSC 424 – Database designLecture 4: Relational ModelER to Relational modelBook: Chap. 2 and 6Mihai PopAdministrative issues•Oracle accounts•SQL homework next weekSummary•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 !• Before = “Network Data Model” (Cobol as DDL, DML)• Very contentious: Database Wars (Charlie Bachman vs. Mike Stonebraker)Introduced by Ted Codd (late 60’s – early 70’s)• Separation of logical, physical data models (data independence)• Declarative query languages• Formal semantics• Query optimization (key to commercial success)Relational data model contributes:• Ingres CA • Postgres Illustra Informix IBM• System R Oracle, DB21st prototypes:Relational Data ModelWhy Called 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)})Mathematical relationsAccount ⊆ Branches × Accounts × Balances{ (Downtown, A-101, 500),(Brighton, A-201, 900),(Brighton, A-217, 500) }Database relationsGiven attribute domainsBranches = { Downtown, Brighton, … }Accounts = { A-101, A-201, A-217, … }Balances = RRelations500900500A-101A-201A-217DowntownBrightonBrightonbalanceacct_nobnameAccount =Relational database semantics defined in terms of mathematical relations { (Downtown, A-101, 500), (Brighton, A-201, 900), (Brighton, A-217, 500) }Considered equivalent to…Terms and Definitions1. Tables = Relations2. Columns = Attributes3. Rows = Tuples4. Relation Schema (or Schema)1. A list of attributes and their domains2. We will require the domains to be atomic3. E.g. account(account-number, branch-name, balance)5. Relation Instance1. A particular instantiation of a relation with actual values2. Will change with timeSo…• 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 constraintsOthers are explicitly represented using other constructsE/R Diagrams RelationsConvert entity sets into a relational schema with the same set of attributesCustomercnameccitycstreetCustomer_Schema(cname, ccity, cstreet)BranchbnamebcityassetsBranch_Schema(bname, bcity, assets)E/R Diagrams RelationsSay One-to-Many Relationship from Customer to Account Many accounts per customerAccountCustomerDepositoracct-nobalancecnameccitycstreetaccess-dateCustomer_Schema(cname, ccity, cstreet)Account_Schema(acct-no, balance,cname, access-date)E/R Diagrams RelationsE = (a 1, …, an)Entity SetsRelational SchemaE/RE1…a 1anE/R Diagrams RelationsE = (a 1, …, an)Entity SetsR = (a 1, b 1, c1, …, cn) a1: E1’s key b1: E2’s key c1, …, ck: attributes of RRelationship SetsRelational SchemaE/RNot the whole story for Relationship Sets …E1…a 1anE1………RE2a 1anc1ckb 1bmE/R Diagrams Relations E1= (a 1, …, an)E2 = (b 1, …, bm)R = (a 1, b 1, c1, …, cn)n:mRelational SchemaRelationship CardinalityRE1………RE2a 1anc1ckb 1bmE/R Diagrams RelationsE1 = (a 1, …, an, b1, c1, …, cn)E2 = (b 1, …, bm)n:1 E1= (a 1, …, an)E2 = (b 1, …, bm)R = (a 1, b 1, c1, …, cn)n:mRelational SchemaRelationship CardinalityRRE1………RE2a 1anc1ckb 1bmE/R Diagrams RelationsE1 = (a 1, …, an)E2 = (b 1, …, bm,, a1, c1, …, cn)1:nE1 = (a 1, …, an, b1, c1, …, cn)E2 = (b 1, …, bm)n:1 E1= (a 1, …, an)E2 = (b 1, …, bm)R = (a 1, b 1, c1, …, cn)n:mRelational SchemaRelationship CardinalityRRRE1………RE2a 1anc1ckb 1bmE/R Diagrams Relations Treat as n:1 or 1:n1:1E1 = (a 1, …, an)E2 = (b 1, …, bm,, a1, c1, …, cn)1:nE1 = (a 1, …, an, b1, c1, …, cn)E2 = (b 1, …, bm)n:1 E1= (a 1, …, an)E2 = (b 1, …, bm)R = (a 1, b 1, c1, …, cn)n:mRelational SchemaRelationship CardinalityRRRRE1………RE2a 1anc1ckb 1bmTranslating E/R Diagrams to RelationsAcct-BranchAccount BranchBorrowerCustomer LoanDepositorLoan-BranchQ. How many tables does this get translated into?A. 6 (account, branch, customer, loan, depositor, borrower)acct_nobalancebnamebcityassetscnameccitycstreetlnoamtBank Database500700400350900700750A-101A-215A-102A-305A-201A-222A-217DowntownMianusPerryR.H.BrightonRedwoodBrightonbalanceacct_nobnameAccountA-101A-215A-102A-305A-201A-217A-222JohnsonSmithHayesTurnerJohnsonJonesLindsayacct_nocnameDepositorHarrisonRyeHarrisonRyePittsfieldStanfordPrincetonPittsfieldPalo AltoWoodsideBrooklynStanfordMainNorthMainNorthParkPutnamNassauSpringAlmaSand HillSenatorWalnutJonesSmithHayesCurryLindsayTurnerWilliamsAdamsJohnsonGlennBrooksGreenccitycstreetcnameCustomer9M2.1M1.7M0.4M8M0.3M3.7M7.1MBrooklynPalo AltoHorseneckHorseneckHorseneckBenningtonRyeBrooklynDowntownRedwoodPerryMianusR.H.PownelN. TownBrightonassetsbcitybnameBranchL-17L-23L-15L-14L-93L-11L-17L-16JonesSmithHayesJacksonCurrySmithWilliamsAdamslnocnameBorrower10002000150015005009001300L-17L-23L-15L-14L-93L-11L-16DowntownRedwoodPerryDowntownMianusR.H.PerryamtlnobnameLoanE/R Diagrams & RelationsRelational SchemaE/RE1 = (a 1, …, an)E2 = (a 1, b 1, …, bm)Weak Entity SetsE1……IRE2a 1anb 1bmE/R Diagrams & RelationsRelational SchemaE/REmp = (ssn, name)Emp-Phones = (ssn, phone)Multivalued AttributesEmpSmith…001…namessnEmp-Phones Employeessnnamephone4-12344-5678…001001…phonessnE/R Diagrams & RelationsRelational SchemaE/RMethod 1:E = (a 1, …, an)E1 = (a 1, b1, …, bm)E2 = (a 1, c1, …, ck)SubclassesEE2ISAE1……b 1bmc 1ck…a
View Full Document
Unlocking...