1Lecture 06:E/R DiagramsMonday, October 9, 20062Outline• E/R diagrams (Chapter 2)• From E/R diagrams to relations (3.2, 3.3)• Wednesday:– Project• Friday: – Functional dependencies, normal forms:– Warning: this is hard, come to class3Database Design• Why do we need it?– Agree on structure of the database before deciding on a particular implementation.• Consider issues such as:– What entities to model– How entities are related– What constraints exist in the domain– How to achieve good designs• Several formalisms exists– We discuss E/R diagrams4Entity / Relationship DiagramsObjects entitiesClasses entity setsAttributes are like in ODL.Relationships: like in ODL except- first class citizens (not associated with classes)- not necessarily binaryProductaddressbuys5PersonCompanyProductbuysmakesemploysname categorypriceaddressname ssnstockpricename6Keys in E/R Diagrams• Every entity set must have a keyProductname categoryprice7What is a Relation ?• A mathematical definition:– if A, B are sets, then a relation R is a subset of A × B• A={1,2,3}, B={a,b,c,d},A × B = {(1,a),(1,b), . . ., (3,d)}R = {(1,a), (1,c), (3,b)}- makes is a subset of Product ×××× Company:123abcdA=B=makesCompanyProduct8Multiplicity of E/R Relations• one-one:• many-one• many-many123abcd123abcd123abcd9addressname ssnPersonbuysmakesemploysCompanyProductname categorystockpricenamepriceWhat doesthis say ?10Multi-way RelationshipsHow do we model a purchase relationship between buyers,products and stores?PurchaseProductPersonStoreCan still model as a mathematical set (how ?)11Q: what does the arrow mean ?Arrows in Multiway RelationshipsA: a given person buys a given product from at most one storePurchaseProductPersonStore12Q: what does the arrow mean ?Arrows in Multiway RelationshipsA: a given person buys a given product from at most one storeAND every store sells to every person at most one product PurchaseProductPersonStore13Q: How do we say that every person shops at at most one store ?Arrows in Multiway RelationshipsA: cannot. This is the best approximation.(Why only approximation ?)PurchaseProductPersonStore14Converting Multi-way Relationships to BinaryPurchasePersonStoreProductStoreOfProductOfBuyerOfdate153. Design PrinciplesPurchaseProductPersonWhat’s wrong?PresidentPersonCountryMoral: be faithful!16Design Principles:What’s Wrong?PurchaseProductStoredatepersonNamepersonAddrMoral: pick the rightkind of entities.17Design Principles:What’s Wrong?PurchaseProductPersonStoredateDatesMoral: don’t complicate life morethan it already is.18From E/R Diagramsto Relational Schema• Entity set relation• Relationship relation19Entity Set to RelationProductname categorypriceProduct(name, category, price)name category pricegizmo gadgets $19.9920Relationships to Relationsmakes CompanyProductname categoryStock pricenameMakes(product-name, product-category, company-name, year)Product-name Product-Category Company-name Starting-yeargizmo gadgets gizmoWorks 1963Start Yearprice(watch out for attribute name conflicts)21Relationships to Relationsmakes CompanyProductname categoryStock pricenameNo need for Makes. Modify Product:name category price StartYear companyNamegizmo gadgets 19.99 1963 gizmoWorksStart Yearprice22Multi-way Relationships to RelationsPurchaseProductPersonStorenamepricessn namenameaddressPurchase(prodName,stName,ssn)23Modeling SubclassesSome objects in a class may be special• define a new class• better: define a subclassProductsSoftware productsEducational productsSo --- we define subclasses in E/R24Productname categorypriceisa isaEducational ProductSoftware ProductAge GroupplatformsSubclasses25Understanding Subclasses• Think in terms of records:– Product– SoftwareProduct– EducationalProductfield1field2field1field2field1field2field3field4field526Subclasses to RelationsProductname categorypriceisa isaEducational ProductSoftware ProductAge Groupplatformsgadget39Toyphoto49Cameragadget99GizmoCategoryPriceNameunixGizmoplatformsNameretiredToytodlerGizmoAge GroupNameProductSw.ProductEd.Product27• OO: classes are disjoint (same for Java, C++)p1p2p3sp1sp2ep1ep2ep3Difference between OO and E/R inheritanceProductSoftwareProductEducationalProduct28• E/R: entity sets overlapDifference between OO and E/R inheritanceSoftwareProductEducationalProductp1p2p3sp1sp2ep1ep2ep3Product29No need for multiple inheritance in E/RSoftwareProductEducationalProductp1p2p3sp1sp2ep1ep2ep3Productesp1esp2We have three entity sets, but four different kinds of objects.30Modeling UnionTypes With SubclassesFurniturePiecePersonCompanySay: each piece of furniture is owned either by a person, or by a company31Modeling Union Types with SubclassesSay: each piece of furniture is owned either by a person, or by a companySolution 1. Acceptable, imperfect (What’s wrong ?)FurniturePiecePerson CompanyownedByPerson ownedByPerson32Modeling Union Types with SubclassesSolution 2: better, more laboriousisaFurniturePiecePerson CompanyownedByOwnerisa33Constraints in E/R DiagramsFinding constraints is part of the modeling process. Commonly used constraints:Keys: social security number uniquely identifies a person.Single-value constraints: a person can have only one father.Referential integrity constraints: if you work for a company, itmust exist in the database.Other constraints: peoples’ ages are between 0 and 150.34Keys in E/R Diagramsaddressname ssnPersonProductname categorypriceNo formal way to specify multiplekeys in E/R diagramsUnderline:35Single Value Constraintsmakesmakesv. s.36Referential Integrity ConstraintsCompanyProductmakesCompanyProductmakesEach product made by at most one company.Some products made by no companyEach product made by exactly one company.37Other ConstraintsCompanyProductmakes<100What does this mean ?38Weak Entity SetsEntity sets are weak when their key comes from otherclasses to which they are related.UniversityTeamaffiliationnumbersport nameNotice: we encountered this when convertingmultiway relationships to binary relationships (last lecture)39Handling Weak Entity SetsUniversityTeamaffiliationnumbersport nameConvert to a relational schema (in
View Full Document
Unlocking...