Conceptual Modeling of DataOutlineConceptual Database DesignDatabase Design ProcessDatabase Design ToolsRequirements of a Conceptual Data ModelOverview of Entity/Relationship (E/R) ModelEntiities and Entity SetsAttributesRelationshipsVisualizing ER Relationships as a TableER Diagram -- graphical representation of ER schemaRoles in a RelationshipConstraints on Entity SetsConstraints on Entity Sets (cont.)Cardinality Constraints on Relationship SetsCardinality Constraints on Relationship Sets (cont.)Multiplicity of RelationshipsMany to Many RelationshipMany to One RelationshipRelationship Attribute in a Many to One RelationshipOne to One RelationshipParticipation ConstraintsSlide 24ExampleWeak Entity SetsWeak Entity Sets (cont.)A Chain of Weak Entity SetsA Weak Entity Set with Multiple Owner Entity SetsMultiway RelationshipsCardinality Constraint over Multiway RelationshipsSlide 32Slide 33Representing Ternary Relationship Using Binary RelationshipsSlide 35Slide 36Limitations of the Basic ER Model Studied So FarSlide 38Subclass/Superclass RelationshipsReason why Superclass/Subclass relationships arise in ER SchemasTypes of Class/Subclass RelationshipsSuperclass/Subclass LatticeMultiple InheritanceLimitations of ER ModelAggregationRepresentation without Aggregation in ER ModelReview of ER ModelE/R Design CycleSchema Design IssuesE/R Design PrinciplesRedundant AttributesRedundant RelationshipA Design ProblemUniqueness assumptions:Design 1: Bad designDesign 2 -- good designAnother Design ProblemDesign 1: Bad designDesign 2: Better DesignConceptual Modeling of DataProf. S. MehrotraProf. N. AshishInformation and Computer Science DepartmentUniversity of California at IrvineICS 122 A Fall 2006OutlineDatabase design processEntity/Relationship ModelEntity setsRelationship setsConstraints on entity setsConstraints on relationship setsWeak entity setsSuperclass/subclass relationshipsAggregationGood Design PrinciplesExamplesICS 122 A Fall 2006Conceptual Database DesignIdeas andinformationAbstract ODLE/RODL C++ EmbeddingODL Smalltalk EmbeddingRelationsRelational DBMSsSmalltalk basedOODBMSsC++ based OODBMSsThe design process depends upon the target DBMS•E/R and ODL are popular models used for conceptual design•ODL -- Object Definition Language is an emerging standard for OODBMSsICS 122 A Fall 2006high level specsconceptual schemalogical schema(in DBMS model)miniworldconceptual designlogical designphysical designfunctional analysisapplication designtransaction implementationData requirementsfunctional requirementsapplication programs Physical schemaRequirement AnalysisFunctional DesignDatabase DesignDatabase Design ProcessICS 122 A Fall 2006Database Design ToolsHelp partially automate the design cycle.Graphical interface to specify conceptual schemas.Partially automated techniques to map to logical (DBMS dependent) model.Features of a good design tool:Iterative: errors /shortcomings of original design found later can be corrected without full restart.Interactive: any design choices made by system during design should be based on interaction with designer.Feedback: a designer’s change made at logical and/or physical levels should be automatically translated to changes at higher levels.Example Design tools: ERwin by LogicWorks.Database design tools integrated into CASE tools and supported by most modern DBMSs.ICS 122 A Fall 2006Requirements of a Conceptual Data ModelExpressiveness: should be expressive enough to allow modeling of different types of relationships, objects and constraints of the miniworld.Simplicity: non-specialists should be able to understandMinimality: few basic powerful concepts that are non-overlappingDiagrammatic Representation: to ease interpretationFormality: There should be no ambiguity in the specificationICS 122 A Fall 2006Overview of Entity/Relationship (E/R) ModelEntitiesRelationships Roles of entities in a relationshipConstraints on entities:domain constraintskey constraintsConstraints on relationshipsCardinality Constraints (mapping constraints in SKS)Participation Constraints (existence dependencies in SKS)Weak Entity SetsMultiway relationshipsSubclass/superclass RelationshipsAggregationICS 122 A Fall 2006Entiities and Entity SetsEntitiesnouns, ‘things’ in the world.E.g., students, courses, employees, departments, flights, patients, ...Attributes properties of entities.E.g., course name, deptname, departure time, age, room#, ... Entity set -- a set of entities that have the same attributes.In OO terminology, an entity set is similar to a class, and an entity similar to an instanceICS 122 A Fall 2006Attributessingle-valued vrs multi-valued: color of car could be multi-valuedsalary of employee is single-valuedatomic vrs composite: age of a person is atomicaddress of a person could be compositestored vrs derived:derived attributes are those that can be derived from other attributes or entities, e.g., age can be derived from date of birth. All other attributes are stored attributesICS 122 A Fall 2006sam 62900 main austinpat 62901 north urbana259 10000245 2400364 200000305 20000customeraccountRelationshipsRelationship: association between multiple entities Relationship Set:set if relationships over the same entity setsBinary, Ternary, 4-nary, … relationship setsCust-Account Relationship setICS 122 A Fall 2006Visualizing ER Relationships as a TableRelationship Set Corresponding to the Relationship Cust-AccountRow in the table represents the pair of entities participating in the relationshipCustomer AccountJohn 1001Megan 1001Megan 2001ICS 122 A Fall 2006ER Diagram -- graphical representation of ER schema customercustacctaccountcust namessnostreetcityacct numberbalanceopening date•Entity set -- rectangles; attributes -- ellipses; dashed ellipse -- derived attribute; double ellipse -- multivalued attribute; relationship set -- diamonds; lines connect the respective relationship set with entity sets; •Relationship sets may have 1 or many attributes associated with them -- known as relationship attributes.ICS 122 A Fall 2006Roles in a RelationshipThe function that an entity plays in a relationship is called its roleRoles are normally not explicitly specified unless the meaning of the relationship needs clarificationRoles needed when entity set is related to itself via a