chapter 3 Database.pdf

Chapter3: Data Modeling Using the Entity-Relationship Model Shayma Alkobaisi College of Information Technology United Arab Emirates University  Database Design Mini world What users want from the...

Chapter3: Data Modeling Using the Entity-Relationship Model Shayma Alkobaisi College of Information Technology United Arab Emirates University  Database Design Mini world What users want from the Database Info Collection & Analysis Identify objects and relations between them (abstract but not complete) Conceptual Design Conceptual model such as ER- diagram Logical Design Convert conceptual design into database schema: Physical ER relational database schema Design  Basic Modeling Concepts Models – Description or analogy used to visualize something that cannot be directly observed Webster s Dictionary Data Model – Relatively simple representation of complex real- world data structures  The Entity-Relationship ER model ER model: A popular high-level conceptual data model – Entities: things or objects that exist in real world and are distinguishable, e.g., Students – Relationships: the relationship or association between two or more entities, e.g., Enroll in – Attributes: a set describing entities, e.g., ID Entity Relationship Attribute  The Entity-Relationship ER model Example Design a Database for a bank – Each customer may have saving accounts, checking accounts, or loans – Each loan has a number, an amount, and an interest rate – Each saving and checking account has a number, a balance (amount), and interest rate – Each customer must provide a name, a SSN and a billing address  The Entity-Relationship ER model Example Design a Database for a bank – Each customer may have saving accounts, checking accounts, or loans – Each loan has a number, an amount, and an interest rate – Each saving and checking account has a number, a balance (amount), and interest rate – Each customer must provide a name, a SSN and a billing address  The Entity-Relationship ER model Example SSN number amount name address type rate CUSTOMER HAS ACCOUNT  Attribute Values and Types Values: – Each attribute has a domain of possible values – A particular entity will have a value for each of its attributes E.g., the attribute Name can have a value of Aysha and the attribute ID can have a value of 3441 Types: – Composite Vs. Simple (Atomic) E.g., Address Vs. ID  Attribute Types Cont. Types Cont.: – Single valued Vs. Multivalued E.g., Age Vs. CollegeDegrees or CarColor – Stored Vs. Derive E.g., DOB Vs. Age – Null values Applied when there is no value for a specific attribute  Domain Constraints on Entity sets An attribute is associated with a domain The value of the attribute for each entity is constrained to be in the domain only Example: – Gender: F or M – Age: > 20  Entity Types Entity type: defines a set of entities that have the same attributes Entity Set: collection of all entities of a particular entity type EMPLOYEE Entity Type Name, Age, Salary Entity Attributes e1 (Ali Salem, 45, 10K) e2 Entity Set (Mona Rashid, 32, 8K)  Key Attributes A super key: is a set of attributes whose values uniquely identify an entity in the set A key: is a minimal super key For each entity there must by a key There could be more than one candidate key We designate one of the candidates key as the primary key and the others are called secondary keys  Key Attributes Cont. Primary keys have their name underlined inside the oval A key attribute may be composite. For example, license_plate number is a key of the CAR entity type with components (Number, State)  Key Attributes Cont. dept name number balance course student Account No two students have the same No two accounts have the same number name in the same dept  Relationships and Relationship types A relationship relates two or more distinct entities with a specific meaning – E.g., EMPLOYEE John Smith works on the ProductX PROJECT or EMPLOYEE Franklin Wong manages the Research DEPARTMENT Relationships of the same type are grouped or typed into a relationship type – E.g., the WORKS_ON relationship type in which EMPLOYEEs and PROJECTs participate, or the MANAGES relationship type in which EMPLOYEEs and DEPARTMENTs participate  Relationships and Relationship types Cont. EMPLOYEE WORKS_ON PROJECT EMPLOYEE MANAGES DEPARTMENT  Relationship Degree The degree of a relationship type: is the number of participating entity types. Both MANAGES and WORKS_ON are binary relationships – A relationship of degree three is called ternary More than one relationship type can exist with the same participating entity types – E.g., MANAGES and WORKS_FOR are distinct relationships between EMPLOYEE and DEPARTMENT participate  More on Relations MANAGES EMPLOYEE DEPARTMENT WORKS_FOR  Descriptive Attributes Descriptive attributes: are used to record information about the relationship and NOT the entity A relationship must be uniquely identified by the participating entities without reference to descriptive attributes E.g., we may wish to record that a specific employee works in a specific department since a specific date since ssn name id name Employees Works_In Departments  Weak Entity Types Weak entity: an entity that does not have a key attribute A weak entity must participate in an identifying relationship type with an owner or identifying entity type Entities are identified by the combination of: – A partial key of the weak entity type – The particular entity they are related to in the identifying entity type  Weak Entity Types Cont. Example: – Transactions of different accounts could have the same trans#, so trans# cannot be a key – By borrowing attribute number from account, we have a key for transaction – Transaction is a weak entity set related to accounts via log relationship number balance trans# account log transaction  Constraints on Relationship Types Constrains on relationships limit the possible combinations of entities participating in the corresponding relationships Constrains are determined by the miniworld situation Structural constraints on relationships include: – Participation Constraints (participation type) – Cardinality Constraints (cardinality ratio)  Participation Constraints Two kinds of participation constraint (on each participating entity type): – total participation (called existential dependency) SHOWN BY DOUBLE LINING THE LINK – Otherwise, partial participation employee Belongs-to department Each employee must belong to at least one department  Participation Constraints Cont. Ss# name amount loandid customer borrows loan Belongs-to Customer-of branch branchid location Each customer must be a customer of at least one branch Each loan must belong to at least one branch  Cardinality Constraints The cardinality ratio: specifies the number of relationship instances that an entity can participate in – one-to-one (1:1) – many-to-many (M:N) – one-to-many (1:N) or many to one (N:1)  One-to-one Relationship customer 1 1 custacct account opendate Illegal 1 customer can have (at most) 1 account. Customer Account opendate Tom 1001 Jan 20th 1999 1 account can be owned by (at most) 1 Jane 1001 March 16th 1999 customer Relationship attributes opendate can Illegal be shifted to either entity set. Customer Account opendate Jane 1001 March 16th 1999 Jane 2001 Feb 18th 1994 Legal Customer Account opendate Jane 1001 March 16th 1999 Tom 2001 Feb 18th 1994  Many-to-many Relationship customer M N custacct account opendate legal Multiple customers can share an account Customer Account Opendate Many accounts may have one owner Tom 1001 Jan 20th 1999 Jane 1001 March 16th 1999 (We use customer names as the ids.) Jane 2001 Feb 18th 1994 legal Customer Account Opendate Tom 1001 Jan 20th 1999 Jane 1001 March 16th 1999  Many-to-one Relationship customer N 1 custacct account opendate Illegal L Multiple customers can share an account Customer Account Opendate A customer can have only one account Tom 1001 Jan 20th 1999 Jane 1001 March 16th 1999 Note: could have no account! Jane 2001 Feb 18th 1994 legal J Customer Account Opendate Tom 1001 Jan 20th 1999 Jane 1001 March 16th 1999  Many-to-one Relationship Cont. customer custacct account opendate In a many-to-one relationship, relationship attributes can be repositioned to the entity set on the many side. customer custacct account opendate  SUMMARY OF ER-DIAGRAM NOTATION Symbol Meaning ENTITY TYPE WEAK ENTITY TYPE RELATIONSHIP TYPE IDENTIFYING RELATIONSHIP TYPE ATTRIBUTE KEY ATTRIBUTE MULTIVALUED ATTRIBUTE COMPOSITE ATTRIBUTE DERIVED ATTRIBUTE E1 R E2 TOTAL PARTICIPATION OF E2 IN R N CARDINALITY RATIO 1:N FOR E1:E2 IN R E1 R E2 R (min,max) E STRUCTURAL CONSTRAINT (min, max) ON PARTICIPATION OF E IN R  Simplified Company Example The company is organized into DEPARTMENTs. Each department has a name, number and an employee who manages the department. We keep track of the start date of the department manager. Each department controls a number of PROJECTs. Each project has a name, number and is located at a single location.  Simplified Company Example Cont. We store each EMPLOYEE s social security number, address, salary, sex, and birthdate. Each employee works for one department but may work on several projects. We keep track of the number of hours per week that an employee currently works on each project. We also keep track of the direct supervisor of each employee. Each employee may have a number of DEPENDENTs. For each dependent, we keep track of their name, sex, birthdate, and relationship to employee. ER diagram for the Company Example