CMSC424: Database DesignTodayData ModelingEntity-Relationship ModelER Diagram: Starting ExampleRelationship CardinalitiesTypes of AttributesNext: KeysEntity KeysSlide 10Next: Recursive RelationshipsRecursive RelationshipsNext: Weak Entity SetsWeak Entity SetsSlide 15Slide 16Slide 17More…Example DesignSlide 20Slide 21Slide 22Slide 23Thoughts…Slide 25Design IssuesRecapRelational Data ModelKey Abstraction: RelationWhy Called Relations?RelationsSlide 32DefinitionsSo…Keys and RelationsKeysSlide 37Slide 38More on KeysSchema Diagram for the Banking EnterpriseExtra slides…Next: Data ConstraintsParticipation ConstraintSlide 44Cardinality ConstraintsNext: SpecializationFinally: AggregationSlide 48Slide 49CMSC424: Database DesignInstructor: Amol Deshpande [email protected]Next class, homework etc..E/R ModelRelational ModelData ModelingGoals:Conceptual representation of the data“Reality” meets “bits and bytes”Must make sense, and be usable by other peopleEnd result should capture all the domain knowledgeEntity-Relationship ModelTwo key conceptsEntities:An object that exists and is distinguishable from other objectsExamples: Bob Smith, BofA, CMSC424Form entity sets with other entities of the same typeRelationships:Relate 2 or more entities E.g. Bob Smith has account at College Park BranchForm relationship sets with other relationships of the same type that share the same properties5ER Diagram: Starting ExampleRectangles: entity setsDiamonds: relationship setsEllipses: attributescustomerhascust-streetcust-idcust-namecust-cityaccountbalancenumberaccess-dateRelationship CardinalitiesOne-to-OneOne-to-ManyMany-to-OneMany-to-ManycustomerhasaccountcustomerhasaccountcustomerhasaccountcustomerhasaccountTypes of Attributescustomercust-streetcust-idcust-namecust-cityhasaccountbalancenumberaccess-datephone no.date-of-birthagemonthday yearComposite AttributeNext: KeysKey = set of attributes that uniquely identifies an entity or a relationshipcustomercust-streetcust-idcust-namecust-cityphone no.agedate-of-birthPossible Keys: {cust-id} {cust-name, cust-city, cust-street} {cust-id, age} cust-name ?? Probably not.Domain knowledge dependent !!Entity KeysEntity KeysSuperkeyany attribute set that can distinguish entitiesCandidate keya minimal superkeyCan’t remove any attribute and preserve key-ness{cust-id, age} not a candidate key {cust-name, cust-city, cust-street} is assuming cust-name is not uniquePrimary keyCandidate key chosen as the key by DBAUnderlined in the ER DiagramNext: Recursive RelationshipsSometimes a relationship associates an entity set to itselfRecursive Relationships Must be declared with rolesemployeeworks-foremp-streetemp-idemp-nameemp-citymanagerworkerNext: Weak Entity SetsAn entity set without enough attributes to have a primary keyE.g. Transaction EntityAttributes:transaction-number, transaction-date, transaction-amount, transaction-typetransaction-number: may not be unique across accountsWeak Entity SetsA weak entity set must be associated with an identifying or owner entity setAccount is the owner entity set for TransactionWeak Entity SetsaccountbalancenumberTransactionhastrans-typetrans-numbertrans-datetrans-amt Still need to be able to distinguish between different weak entities associated with the same strong entityWeak Entity SetsaccountbalancenumberTransactionhastrans-typetrans-numbertrans-datetrans-amt Discriminator: A set of attributes that can be used for thatWeak Entity SetsPrimary key:Primary key of the associated strong entity + discriminator attribute setFor Transaction:{account-number, transaction-number}More…Read Chapter 6 for:Semantic data constraintsSpecialization/Generalization/AggregationGeneralization: opposite of specializationLower- and higher-level entitiesAttribute inheritanceHomework 1 !!Example DesignWe will model a university databaseMain entities:ProfessorProjectsDepartmentsGraduate studentsetc…professorareanameSSNrankprojectstartsponsorproj-numberbudgetdeptofficenamedept-nohomepagegradagenameSSN degreeprofessorareanameSSNrankprojectstartsponsorproj-numberbudgetdeptofficenamedept-nohomepagegradagenameSSN degreePICo-PIRAMajorChairSupervisesMentoradviseeadvisorApptTime (%)professorareanameSSNrankprojectstartsponsorproj-numberbudgetdeptofficenamedept-nohomepagegradagenameSSN degreePICo-PIRAMajorChairApptSupervisesMentoradviseeadvisorTime (%)professorareanameSSNrankprojectstartsponsorproj-numberbudgetdeptofficenamedept-nohomepagegradagenameSSN degreePICo-PIRAMajorChairApptSupervisesMentoradviseeadvisorTime (%)And so on…Thoughts…Nothing about actual dataHow is it stored ? No talk about the query languagesHow do we access the data ?Semantic vs Syntactic Data ModelsRemember: E/R Model is used for conceptual modelingMany conceptual models have the same propertiesThey are much more about representing the knowledge than about database storage/queryingThoughts…Basic design principlesFaithfulMust make senseSatisfies the application requirementsModels the requisite domain knowledgeIf not modeled, lost afterwardsAvoid redundancyPotential for inconsistenciesGo for simplicityTypically an iterative process that goes back and forthDesign IssuesEntity sets vs attributesDepends on the semantics of the applicationConsider telephoneEntity sets vs Relationsihp setsConsider loanN-ary vs binary relationshipsPossible to avoid n-ary relationships, but there are some cases where it is advantageous to use themIt is not an exact science !!RecapEntity-relationship ModelIntuitive diagram-based representation of domain knowledge, data properties etc…Two key concepts:EntitiesRelationshipsWe also looked at:Relationship cardinalitiesKeysWeak entity sets…• 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)1. Separation of logical, physical data models (data independence)2. Declarative query languages3. Formal semantics4. Query optimization (key to commercial success)Relational data model contributes:• Ingres CA • Postgres Illustra Informix IBM• System R Oracle, DB21st prototypes:Relational Data
View Full Document
Unlocking...