Conceptual Modeling of Data Prof S Mehrotra Information and Computer Science Department University of California at Irvine Outline 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 Examples Conceptual Database Design ODL C Embedding Abstract ODL C based OODBMSs ODL Smalltalk Embedding Relations E R Ideas and Smalltalk based OODBMSs Relational DBMSs information The 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 OODBMSs Database Design Process miniworld Requirement Analysis functional requirements functional analysis Data requirements conceptual design conceptual schema application design high level specs transaction implementation application programs Functional Design logical design logical schema in DBMS model physical design Physical schema Database Design Database 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 Requirements 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 specification Overview 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 Aggregation Entiities 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 instance Attributes 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 attributes Relationships Relationship association between multiple entities Relationship Set set if relationships over the same entity sets Binary Ternary 4 nary relationship sets customer Cust Account Relationship set account 259 10000 sam 62900 main austin 305 20000 pat 62901 north urbana 245 2400 364 200000 Visualizing ER Relationships as a Table Customer John Megan Megan Account 1001 1001 2001 Row in the table represents the pair of entities participating in the relationship Relationship Set Corresponding to the Relationship Cust Account ER Diagram graphical representation of ER schema cust name ssno street city customer balance acct number custacct account opening 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 Roles 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 relationship manager employee works for worker Constraints on Entity Sets Key Constraint With each entity set a notion of a key can be associated A key is a set of attributes that uniquely identify an entity in entity set Examples designer may specify that ssno is a key for a entity set customer entity with attributes ssno accountno balance name address designer may specify that accountno is also a key that is no joint accounts are permitted Denoted in ER diagram by underlining the attributes that form a key multiple keys may exist in which case one chosen as primary key and underlined Other keys called secondary keys either not indicated or listed in a side comment attached to the diagram Constraints on Entity Sets cont Domain constraint with each simple attribute a domain is associated The value of the attribute for each entity is constrained to be in the domain Cardinality Constraints on Relationship Sets Consider binary relationship set R between entity sets A and B One to one an entity in A is associated with at most one entity in B and an entity in B is associated with atmost one entity in A an employee has only one spouse in a married to relationship Many to One An entity in A is associated with at most one entity in B an entity in B is associated with many entities in A an employee works in a single department but a department consists of many employees Cardinality Constraints on Relationship Sets cont Many to Many An entity in A is associated with many entities in B and an entity in B is associated with many entities in A A customer may have many bank accounts Accounts may be joint between multiple customers Multiplicity of Relationships Many to many Many to one One to one multiplicity of relationship in ER diagram represented by an arrow pointing to one Many to Many Relationship customer account custacct opening date Customer John Megan Megan Account 1001 1001 2001 legal Start Date Jan 20th 1999 March 16th 1999 Feb 18th 1994 Customer Account Start Date John 1001