Lecture 5: Conceptual Database DesignBuilding an Application with a DBMSDatabase DesignDatabase Design Formalisms2. Entity / Relationship DiagramsKeys in E/R DiagramsWhat is a Relation ?Multiplicity of E/R RelationsSlide 10Multi-way RelationshipsArrows in Multiway RelationshipsSlide 13Slide 14Roles in RelationshipsAttributes on RelationshipsConverting Multi-way Relationships to BinaryFrom E/R Diagrams to Relational SchemaEntity Set to RelationRelationships to RelationsSlide 21Multi-way Relationships to Relations3. Design PrinciplesDesign Principles: What’s Wrong?Slide 25Modeling SubclassesSlide 27Understanding SubclassesSubclasses to RelationsModeling UnionTypes With SubclassesModeling Union Types with SubclassesSlide 32Constraints in E/R DiagramsKeys in E/R DiagramsSingle Value ConstraintsReferential Integrity ConstraintsOther ConstraintsWeak Entity SetsHandling Weak Entity SetsLecture 5:Conceptual Database DesignJanuary 14th, 2003Building an Application with a DBMS•Requirements modeling (conceptual, pictures)–Decide what entities should be part of the application and how they should be linked.•Schema design and implementation–Decide on a set of tables, attributes.–Define the tables in the database system.–Populate database (insert tuples).•Write application programs using the DBMS–way easier now that the data management is taken care of.Database 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 designsDatabase Design Formalisms1. Object Definition Language (ODL):– Closer in spirit to object-oriented models– I don’t teach it anymore.2. Entity/Relationship model (E/R):–More relational in nature.•Both can be translated (semi-automatically) to relational schemas •ODL to OO-schema: direct transformation (C++ or Smalltalk based system).2. Entity / Relationship DiagramsEntitiesAttributesRelationships between entities ProductaddressbuysKeys in E/R Diagrams•Every entity set must have a keyProductname categorypriceaddressname ssnPersonbuysmakesemploysCompanyProductname categorystockpricenamepriceWhat is a Relation ?•A mathematical definition:–if A, B are sets, then a relation R is a subset of A x B•A={1,2,3}, B={a,b,c,d}, R = {(1,a), (1,c), (3,b)}- makes is a subset of Product x Company:123abcdA=B=makes CompanyProductMultiplicity of E/R Relations•one-one:•many-one•many-many123abcd123abcd123abcdaddressname ssnPersonbuysmakesemploysCompanyProductname categorystockpricenamepriceWhat doesthis say ?Multi-way RelationshipsHow do we model a purchase relationship between buyers,products and stores?PurchaseProductPersonStoreCan still model as a mathematical set (how ?)Q: what does the arrow mean ?A: if I know the store, person, invoice, I know the movie tooRentalVideoStorePersonMovieInvoiceArrows in Multiway RelationshipsQ: what do these arrow mean ?A: store, person, invoice determines movie and store, invoice, movie determines personRentalVideoStorePersonMovieInvoiceArrows in Multiway RelationshipsQ: how do I say: “invoice determines store” ?A: no good way; best approximation:Q: Why is this incomplete ?RentalVideoStorePersonMovieInvoiceArrows in Multiway RelationshipsRoles in RelationshipsPurchaseWhat if we need an entity set twice in one relationship?ProductPersonStoresalespersonbuyerAttributes on RelationshipsPurchaseProductPersonStoredateConverting Multi-way Relationships to BinaryPurchasePersonStoreProductStoreOfProductOfBuyerOfdateFrom E/R Diagramsto Relational Schema•Entity set relation•Relationship relationEntity Set to RelationProductname categorypriceProduct(name, category, price) name category price gizmo gadgets $19.99Relationships to Relations makes CompanyProductname categoryStock pricenameMakes(product-name, product-category, company-name, year) Product-name Product-Category Company-name Starting-year gizmo gadgets gizmoWorks 1963Start Yearprice (watch out for attribute name conflicts)Relationships to Relations makes CompanyProductname categoryStock pricenameNo need for Makes. Modify Product: name category price StartYear companyName gizmo gadgets 19.99 1963 gizmoWorksStart YearpriceMulti-way Relationships to RelationsPurchaseProductPersonStorenamepricessn namenameaddressPurchase( , , )3. Design PrinciplesPurchaseProductPersonWhat’s wrong?PresidentPersonCountryMoral: be faithful!Design Principles:What’s Wrong?PurchaseProductStoredatepersonNamepersonAddrMoral: pick the right kind of entities.Design Principles:What’s Wrong?PurchaseProductPersonStoredateDatesMoral: don’t complicate life more than it already is.Modeling SubclassesThe world is inherently hierarchical. Some entities are special cases of others• We need a notion of subclass.• This is supported naturally in object-oriented formalisms.ProductsSoftware productsEducational productsProductname categorypriceisa isaEducational ProductSoftware ProductAge GroupplatformsSubclasses in E/R DiagramsUnderstanding Subclasses•Think in terms of records:–Product–SoftwareProduct–EducationalProductfield1field2field1field2field1field2field3field4field5Subclasses to RelationsProductname categorypriceisa isaEducational ProductSoftware ProductAge GroupplatformsName Price CategoryGizmo 99 gadgetCamera 49 photoToy 39 gadgetName platformsGizmo unixName Age GroupGizmo todlerToy retiredProductSw.ProductEd.ProductModeling UnionTypes With SubclassesFurniturePiecePersonCompanySay: each piece of furniture is owned either by a person, or by a companyModeling Union Types with SubclassesSay: each piece of furniture is owned either by a person, or by a companySolution 1. Acceptable, imperfect (What’s wrong ?)FurniturePiecePersonCompanyownedByPerson ownedByPersonModeling Union Types with SubclassesSolution 2: better, more laboriousisaFurniturePiecePersonCompanyownedByOwnerisaConstraints 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
View Full Document
Unlocking...