Lecture 3: Database Modeling (continued)Arrows in Multiway Relationships: CorrectionRoles in RelationshipsAttributes on RelationshipsConverting Multi-way Relationships to BinaryDesign PrinciplesDesign Principles: What’s Wrong?Slide 8Next AttactionsModeling SubclassesSubclasses in ODLUnderstanding SubclassesMultiple Inheritance in ODLPowerPoint PresentationUnderstanding Multiple InheritanceHow do we resolve conflicts?Difference between ODL and E/R inheritanceSlide 19Slide 20ConstraintsModeling ConstraintsKeysSlide 24Keys in ODLKeys in E/R DiagramsSingle Value ConstraintsSlide 28Slide 29Referential Integrity ConstraintsSlide 31Weak Entity Sets1Lecture 3: Database Modeling (continued)April 5, 20022Q: what do these arrow mean ?A: store, person, invoice determines movie and store, invoice, movie determines personRentalVideoStorePersonMovieInvoiceArrows in Multiway Relationships: Correction3Roles in RelationshipsPurchaseWhat if we need an entity set twice in one relationship?ProductPersonStoresalespersonbuyer4Attributes on RelationshipsPurchaseProductPersonStoredate5Converting Multi-way Relationships to BinaryPurchasePersonStoreProductStoreOfProductOfBuyerOfdate6Design PrinciplesPurchaseProductPersonWhat’s wrong?PresidentPersonCountryMoral: be faithful!7Design Principles:What’s Wrong?PurchaseProductStoredatepersonNamepersonAddrMoral: pick the right kind of entities.8Design Principles:What’s Wrong?PurchaseProductPersonStoredateDatesMoral: don’t complicate life more than it already is.9Next Attactions•Subclasses (2.4)•Constraints: (2.5)•Weak entity sets (2.6)10Modeling SubclassesSome objects in a class may be special• define a new class• better: define a subclassProductsSoftware productsEducational productsSo --- we define subclasses (in ODL and in E/R).11Subclasses in ODLinterface SoftwareProduct: Product{ attribute string platform; attribute integer requiredMemory;}interface EducationalProduct: Product{ attribute struct Interval {integer begin, integer end} ageGroup; attribute string topic;}interface SoftwareProduct: Product{ attribute string platform; attribute integer requiredMemory;}interface EducationalProduct: Product{ attribute struct Interval {integer begin, integer end} ageGroup; attribute string topic;}The two classes i nherit all the properties of Product.12Understanding Subclasses•Think in terms of records:–Product–SoftwareProduct–EducationalProductfield1field2field1field2field1field2field3field4field513Multiple Inheritance in ODL ProductEducational ProductEduc-softwareProductSoftware ProductageGrouptopicPlatformsrequired memoryEducational-method14interface EducSoftwareProduct: SoftwareProduct, EducationalProduct { attribute string educational-method;}interface EducSoftwareProduct: SoftwareProduct, EducationalProduct { attribute string educational-method;}15Understanding Multiple Inheritance•Think in terms of records:–EducSoftwareProductfield1field2field3field4field516How do we resolve conflicts? ProductEducational ProductEduc-softwareProductSoftware ProductageGrouptopicPlatformsrequired memoryEducational-methodRating(ATA)Rating(ASA)Rating?17 Productname categorypriceisa isaEducational ProductSoftware ProductAge GroupplatformsSubclasses in E/R Diagrams18•ODL: classes are disjointp1p2p3sp1sp2ep1ep2ep3Difference between ODL and E/R inheritanceProductSoftwareProductEducationalProduct19•E/R: entity sets overlapDifference between ODL and E/R inheritanceSoftwareProductEducationalProductp1p2p3sp1sp2ep1ep2ep3Product20•No need for multiple inheritance in E/R•we have three entity sets, but four different kinds of objectsSoftwareProductEducationalProductp1p2p3sp1sp2ep1ep2ep3Productesp1esp221Constraints•A constraint = an assertion about the database that must be true at all times•part of the db schema•types in programming languages do not have anything similar•correspond to invariants in programming languages22Modeling ConstraintsFinding 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, it must exist in the database. Domain constraints: peoples’ ages are between 0 and 150. General constraints: all others (at most 50 students enroll in a class)23KeysA set of attributes that uniquely identify an object or entity: Person: social-security-number name name + address name + address + agePerfect keys are often hard to find, so organizations usuallyinvent something.24Keys•Multi-attribute keys:–E.g. name + address•Multiple keys:–E.g social-security-number, name + address25Keys in ODLDefining multiple keys: (key ssn employeID (name address age))interface Person (key ssn) { attribute string ssn; attribute string name; ... }interface Person (key ssn) { attribute string ssn; attribute string name; ... }26 Keys in E/R Diagramsaddressname ssnPersonProductname categorypriceNo formal way to specify multiple keys in E/R diagramsUnderline:27Single Value Constraints•Sometimes we can choose to allow one or more values•ODL: –attributes are always single value–relationships have single or multiple valuesrelationship person president;relationship set<person> presidents;28Single Value Constraints•E/R:offers offers29Single Value ConstraintsSingle Value Constraint: •we explicitly require one value•two flavors:–allow nulls–do not allow nulls30Referential Integrity Constraints•In some formalisms we may refer to other object but get garbage instead–e.g. a dangling pointer in C/C++•the Referential Integrity Constraint explicitly requires a reference to exists31Referential Integrity Constraints•In ODL:– means that a relationship cannot be NULL•In E/R:CompanyProductmakesCompanyProductmakes32Weak Entity SetsEntity sets are weak when their key attributes come from otherclasses to which they are related.This happens if: - part-of hierarchies - splitting n-ary relations to binary.UniversityTeamaffiliationnumbersport
View Full Document
Unlocking...