CMSC424: Database DesignStuffTodayReview: Relational Data Model Key Abstraction: RelationReview: Terms and DefinitionsSo…E/R Diagrams RelationsSlide 8Slide 9Slide 10Slide 11Slide 12Slide 13Slide 14Slide 15Translating E/R Diagrams to RelationsBank DatabaseE/R Diagrams & RelationsSlide 19Slide 20Slide 21Slide 22Slide 23Keys and RelationsKeysSlide 26Slide 27More on KeysSchema Diagram for the Banking EnterpriseNextNext…PowerPoint PresentationExample QueriesSlide 34Slide 35CMSC424: Database DesignInstructor: Amol Deshpande [email protected]SQL Assignment AccountsQuestions on E/R Model ?TodayRecap: Relational ModelE/R Model to an Relational ModelRemember: We still use E/R models for conceptual modeling of the databaseRelational AlgebraOperating on the relationsReview: Relational Data Model Key Abstraction: RelationGiven 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 = Rbname acct_no balanceDowntownBrightonBrightonA-101A-201A-217500900500Review: 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 modelThat’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 schemaEspecially 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 RelationsConvert relationship sets also into a relational schemaRemember: A relationship is completely described by primary keys of associate entities and its own attributesDepositor_Schema(cname, acct-no, access-date)AccountCustomerDepositoracct-nobalancecnameccitycstreetaccess-dateWell… Not quite. We can do better.It depends on the relationship cardinalityCustomer_Schema(cname, ccity, cstreet)Account_Schema(acct-no, balance)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)Exactly same information, fewer tablesE/R Diagrams RelationsE/R Relational SchemaEntity SetsE = (a1, …, an)E1…a1anE/R Diagrams RelationsE/R Relational SchemaEntity SetsE = (a1, …, an)Relationship SetsR = (a1, b1, c1, …, cn) a1: E1’s key b1: E2’s key c1, …, ck: attributes of RNot the whole story for Relationship Sets …E1…a1anE1………RE2a1anc1ckb1bmCMSC424, Spring 2005E/R Diagrams RelationsRelationship Cardinality Relational Scheman:m E1= (a1, …, an)E2 = (b1, …, bm)R = (a1, b1, c1, …, cn)RE1………RE2a1anc1ckb1bmCMSC424, Spring 2005E/R Diagrams RelationsRelationship Cardinality Relational Scheman:m E1= (a1, …, an)E2 = (b1, …, bm)R = (a1, b1, c1, …, cn)n:1 E1 = (a1, …, an, b1, c1, …, cn)E2 = (b1, …, bm)RRE1………RE2a1anc1ckb1bmCMSC424, Spring 2005E/R Diagrams RelationsRelationship Cardinality Relational Scheman:m E1= (a1, …, an)E2 = (b1, …, bm)R = (a1, b1, c1, …, cn)n:1 E1 = (a1, …, an, b1, c1, …, cn)E2 = (b1, …, bm)1:n E1 = (a1, …, an)E2 = (b1, …, bm,, a1, c1, …, cn)RRRE1………RE2a1anc1ckb1bmCMSC424, Spring 2005E/R Diagrams RelationsRelationship Cardinality Relational Scheman:m E1= (a1, …, an)E2 = (b1, …, bm)R = (a1, b1, c1, …, cn)n:1 E1 = (a1, …, an, b1, c1, …, cn)E2 = (b1, …, bm)1:n E1 = (a1, …, an)E2 = (b1, …, bm,, a1, c1, …, cn)1:1 Treat as n:1 or 1:nRRRRE1………RE2a1anc1ckb1bmTranslating 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 DatabaseAccountbname acct_no balanceDowntownMianusPerryR.H.BrightonRedwoodBrightonA-101A-215A-102A-305A-201A-222A-217500700400350900700750Depositorcname acct_noJohnsonSmithHayesTurnerJohnsonJonesLindsayA-101A-215A-102A-305A-201A-217A-222Customercname cstreet ccityJonesSmithHayesCurryLindsayTurnerWilliamsAdamsJohnsonGlennBrooksGreenMainNorthMainNorthParkPutnamNassauSpringAlmaSand HillSenatorWalnutHarrisonRyeHarrisonRyePittsfieldStanfordPrincetonPittsfieldPalo AltoWoodsideBrooklynStanfordBranchbname bcity assetsDowntownRedwoodPerryMianusR.H.PownelN. TownBrightonBrooklynPalo AltoHorseneckHorseneckHorseneckBenningtonRyeBrooklyn9M2.1M1.7M0.4M8M0.3M3.7M7.1MBorrowercname lnoJonesSmithHayesJacksonCurrySmithWilliamsAdamsL-17L-23L-15L-14L-93L-11L-17L-16Loanbname lno amtDowntownRedwoodPerryDowntownMianusR.H.PerryL-17L-23L-15L-14L-93L-11L-1610002000150015005009001300E/R Diagrams & RelationsE/R Relational SchemaWeak Entity SetsE1 = (a1, …, an)E2 = (a1, b1, …, bm)E1……IRE2a1anb1bmE/R Diagrams & RelationsE/R Relational SchemaMultivalued AttributesEmp = (ssn, name)Emp-Phones = (ssn, phone)Empssn name001…Smith…Emp-Phones Employeessnnamephonessn phone001001…4-12344-5678…E/R Diagrams & RelationsE/R Relational SchemaSubclassesMethod 1:E = (a1, …, an)E1 = (a1, b1, …, bm)E2 = (a1, c1, …, ck)EE2ISAE1……b1bmc1ck…a1anE/R Diagrams & RelationsE/R Relational SchemaSubclassesMethod 1:E = (a1, …, an)E1 = (a1, b1, …, bm)E2 = (a1, c1, …, ck)Method 2:E1 = (a1, …, an, b1, …, bm)E2 = (a1, …, an, c1, …, ck)EE2ISAE1……b1bmc1ck…a1anE/R Diagrams & RelationsSubclasses example:Method 1:Account = (acct_no, balance)SAccount = (acct_no, interest)CAccount = (acct_no, overdraft)Method
View Full Document
Unlocking...