Design Concepts ER Model Chapter 2 Instructor

Design Concepts & ER Model Chapter 2 Instructor: Xin Zhang Database Management Systems Raghu Ramakrishnan 1

Inside a Database • Tables • Relationship among tables • Operations (queries) Database Management Systems Raghu Ramakrishnan 2

Overview of db design v Requirement analysis – Data to be stored – Applications to be built – Operations (most frequent) subject to performance requirement v Conceptual db design – Description of the data (including constraints) – By high level model such as ER v Logical db design – Choose DBMS to implement – Convert conceptual db design into database schema v v Beyond ER design Schema refinement (normalization) Physical db design – Analyze the workload – Refine db design to meet performance criteria (focus on Indexing) v Security design Database Management Systems Raghu Ramakrishnan 3

Conceptual design v Issues to consider: (ER Model is used at this stage. ) – What are the entities and relationships in the enterprise? – What information about these entities and relationships should we store in the database (i. e. , attributes)? – What are the integrity constraints or business rules that hold? v Solution: – A database `schema’ in the ER Model can be represented pictorially (ER diagrams). – Can map an ER diagram into a relational schema. Database Management Systems Raghu Ramakrishnan 4

ER Model Basics ssn name lot Employees v Entity: Real-world object distinguishable from other objects. An entity is described (in DB) using a set of attributes. v Entity Set: A collection of similar entities. E. g. , all employees. – All entities in an entity set have the same set of attributes. – Each entity set has a key. – Each attribute has a domain. What’s the key? How many keys one object can have? Database Management Systems Raghu Ramakrishnan 5

Key A key is a minimal set of attributes whose values uniquely identify an entity in the set. v Candidate key. v Primary key. v Database Management Systems Raghu Ramakrishnan 6

Entity, Entity Set, Attribute, and Schema ID or SSN 999 -38 -4431 Name User. ID John Smith 999 -28 -3341 Miki Jordan 21 GPA 3. 68 mjordan 28 3. 45 25 jsmith 331 -43 -4567 David Kim dkim 535 -34 -5678 Paul Lee plee Database Management Systems Age Raghu Ramakrishnan 26 4. 00 3. 89 7

ER Model Basics (Contd. ) since name ssn lot Employees dname budget did Works_In Departments Relationship Set v Relationship: Association among 2 or more entities. E. g. , Sam works in the Accounting Department. v Relationship Set: Collection of similar relationships. – An n-ary relationship set R relates n entity sets E 1. . . En; each relationship in R involves entities e 1 E 1, . . . , en En u Same entity set could participate in different relationship sets, or in different “roles” in same set. Database Management Systems Raghu Ramakrishnan 8

Entity vs. Entity Set Object --- Student John Smith (999 -21 -3415, jsmith@, John Smith, 18, 3. 5) Students in ITCS 3160 999 -21 -3415, jsmith@, John Smith, 18, 3. 5 999 -31 -2356, jzhang@, Jie Zhang, 20, 3. 0 999 -32 -1234, ajain@, Anil Jain, 21, 3. 8 Database Management Systems Raghu Ramakrishnan 9

Example of Keys Primary key Candidate key 999 -21 -3415, jsmith@, John Smith, 18, 3. 5 999 -31 -2356, jzhang@, Jie Zhang, 20, 3. 0 999 -32 -1234, ajain@, Anil Jain, 21, 3. 8 Database Management Systems Raghu Ramakrishnan 10

Relationship vs. Relationship Set John Smith (999 -21 -3415, jsmith@, John Smith, 18, 3. 5) Relationship ITCS 3160 (3160, ITCS, DBMS, J. Fan, 3, Kenn. 236) Database Management Systems Raghu Ramakrishnan 11

Relationship vs. Relationship Set 999 -21 -3415, jsmith@, John Smith, 18, 3. 5 999 -31 -2356, jzhang@, Jie Zhang, 20, 3. 0 999 -32 -1234, ajain@, Anil Jain, 21, 3. 8 Relationship set(“Enrolled in”) 3160, ITCS, DBMS, J. Fan, 3, Kenn. 236 6157, ITCS, Visual DB, J. Fan, 3, Kenn. 236 Database Management Systems Raghu Ramakrishnan Students Courses 12

Relationship vs. Relationship Set Name Id Login Students Age Id GPA Name Credit room Courses Enrolled_In Grade Database Management Systems Raghu Ramakrishnan Descriptive attribute 13

Example 1 v Build an ER Diagram for the following information: – Students u Have an Id, Name, Login, Age, GPA – Courses u Have an Id, Name, Credit Hours – Students enroll in courses u Receive a grade Database Management Systems Raghu Ramakrishnan 14

Example 1 Answer Name Id Login Students Age Id GPA Name Credit Courses Enrolled_In Grade Database Management Systems Raghu Ramakrishnan 15

Example 2 v Build an ER Diagram for the following information: – Patients u Name, Address, Phone #, Age – Drugs u Name, Manufacturer , Expiration Date – Patients are prescribed drugs u Dosage, # Days Database Management Systems Raghu Ramakrishnan 16

Example 2 Answer Name Addr Patients Phone Name Age Manuf Exp Drug Prescribed Dosage Database Management Systems #days Raghu Ramakrishnan 17

Constraints Key constraints v Participation constraints v Database Management Systems Raghu Ramakrishnan 18

Potential Relationship Types 1 -to-1 Database Management Systems 1 -to Many-to-1 Raghu Ramakrishnan Many-to-Many 19

Potential Relationship Types Students v v v ? IN ? CS Dept Mary studies in the CS Dept. Tom studies in the CS Dept. Jack studies in the CS Dept. … The CS Dept has lots of students. No student in the CS Dept works in other else Dept at the same time. Database Management Systems Raghu Ramakrishnan 20

Potential Relationship Types Students v v v ? take ? Courses Mary is taking the ITCS 3160, ITCS 2212. Tom is taking the ITCS 3160, ITCS 2214. Jack is taking the ITCS 1102, ITCS 2214. … 61 students are taking ITCS 3160. 120 students are taking ITCS 2214. Database Management Systems Raghu Ramakrishnan 21

Key Constraints v. Consider Works_In: An employee can work in many departments; a dept can have many employees. since name ssn lot Employees Database Management Systems dname did Works_In Raghu Ramakrishnan budget Departments 22

Key Constraints v. Consider Works_In: An employee can work in at most one department; a dept can have many employees. since name ssn lot Employees Database Management Systems dname did Works_In Raghu Ramakrishnan budget Departments 23

Key Constraints v In contrast, each dept has at most one manager, according to the key constraint on Manages. At most one!!! since name ssn dname lot did Employees Manages budget Departments Key Constraint (time constraint) Database Management Systems Raghu Ramakrishnan 24

Participation Constraints v Does every department have a manager? – If so, this is a participation constraint: the participation of Departments in Manages is said to be total (vs. partial). u Every did value in Departments table must appear in a row of the Manages table (with a non-null ssn value!) since name ssn dname did lot Employees Partial Total Manages budget Departments Total w/key constraint Works_In Total since Database Management Systems Raghu Ramakrishnan 25

What are the policies behind this ER model? since name ssn dname did lot Employees Total Manages budget Departments Total w/key constraint Works_In Total since Database Management Systems Raghu Ramakrishnan 26

since name dname ssn did lot Employees Manages Any Difference? budget Departments Works_In since name ssn dname did lot Employees Partial Total Manages budget Departments Total w/key constraint Works_In Total since Database Management Systems Raghu Ramakrishnan 27

Weak Entities vs. Owner Entities v A weak entity can be identified uniquely only by considering the primary key of another (owner) entity. – Owner entity set and weak entity set must participate in a one-to-many relationship set (1 owner, many weak entities). – Weak entity set must have total participation in this identifying relationship set. name ssn Primary Key for weak entity lot Employees cost Policy Identifying Relationship Database Management Systems Raghu Ramakrishnan pname age Dependents Weak Entity 28

Ternary Relationship ssn name lot Employees from did Works_In 3 dname budget Departments Duration to Why? since name ssn lot Employees Database Management Systems dname budget did Works_In Departments Raghu Ramakrishnan 29

name ssn ISA (`is a’) Hierarchies v. As in C++, or other PLs, attributes are inherited. hourly_wages lot Employees hours_worked ISA contractid v. If we declare A ISA B, every A Contract_Emps Hourly_Emps entity is also considered to be a B entity. v Overlap constraints: Can Joe be an Hourly_Emps as well as a Contract_Emps entity? (Allowed/disallowed) v Covering constraints: Does every Employees entity also have to be an Hourly_Emps or a Contract_Emps entity? (Yes/no) v Reasons for using ISA: – To add descriptive attributes specific to a subclass. – To identify entitities that participate in a relationship. Database Management Systems Raghu Ramakrishnan 30

ssn Aggregation v Used when we have to model a relationship involving (entitity sets and) a relationship set. – Aggregation allows us to treat a relationship set as an entity set for purposes of participation in (other) relationships. – Monitors mapped to table like any other relationship set. Database Management Systems name lot Employees Monitors until Aggregation started_on pid dname pbudget Projects Raghu Ramakrishnan did Sponsors budget Departments 31

Real Database Design v Build an ER Diagram for the following information: – Walmart Stores u Store Id, Address, Phone # – Products u Product Id, Description, Price – Manufacturers u Name, Address, Phone # – Walmart Stores carry products u Amount in store – Manufacturers make products u Amount in factory/warehouses Database Management Systems Raghu Ramakrishnan 32

Conceptual Design Using the ER Model v Design choices: – Should a concept be modeled as an entity or an attribute? – Should a concept be modeled as an entity or a relationship? – Identifying relationships: Binary or Ternary? Aggregation? Always follow the requirements. Database Management Systems Raghu Ramakrishnan 33

Entity vs. Attribute Should address be an attribute of Employees or an entity (connected to Employees by a relationship)? v Depends upon the use we want to make of address information, and the semantics of the data: v u If we have several addresses per employee, address must be an entity (since attributes cannot be set-valued). u If the structure (city, street, etc. ) is important, e. g. , we want to retrieve employees in a given city, address must be modeled as an entity (since attribute values are atomic). Database Management Systems Raghu Ramakrishnan 34

Entity vs. Attribute (Contd. ) v v name from to dname Works_In 2 does not ssn lot did budget allow an employee to Departments Works_In 2 work in a department for Employees two or more periods. Similar to the problem of wanting to record several addresses for an name dname employee: we want to ssn lot did budget record several values of the Works_In 3 Departments Employees descriptive attributes for each instance of this Duration to from relationship. Database Management Systems Raghu Ramakrishnan 35

Entity vs. Relationship v First ER diagram OK if a manager gets a separate discretionary budget for each dept. – Redundancy of dbudget, which is stored for each dept managed by the manager. – Misleading: suggests dbudget tied to managed dept. since name ssn dbudget lot Employees did What if a manager gets a discretionary budget that covers all managed depts? Database Management Systems budget Departments Manages 2 name ssn dname lot Employees did Manages 3 budget Departments v since apptnum Mgr_Appts dbudget Raghu Ramakrishnan 36

Binary vs. Ternary Relationships* ssn v If each policy is owned by just 1 employee: – Key constraint on Policies would mean policy can only cover 1 dependent! name Employees Bad design Policies policyid ssn name age Dependents Covers cost pname lot age Dependents Employees Purchaser Better design Database Management Systems pname lot policyid Raghu Ramakrishnan Beneficiary Policies cost 37

Binary vs. Ternary Relationships (Contd. ) v v Previous example illustrated a case when binary relationships were better than one ternary relationship. An example in the other direction: a ternary relation Contracts relates entity set Parts, Departments and Suppliers, and has descriptive attributes qty. No combination of binary relationships is an adequate substitute: – S can supply P, D needs P, and D deals-with S does not imply that D has agreed to buy P from S. – How do we record qty? Database Management Systems Raghu Ramakrishnan 38

Summary of Conceptual Design v Conceptual design follows requirements analysis, – Yields a high-level description of data to be stored v ER model popular for conceptual design – Constructs are expressive, close to the way people think about their applications. Basic constructs: entities, relationships, and attributes (of entities and relationships). v Some additional constructs: weak entities, ISA hierarchies, and aggregation. v Note: There are many variations on ER model. v Database Management Systems Raghu Ramakrishnan 39

Summary of ER (Contd. ) v Several kinds of integrity constraints can be expressed in the ER model: key constraints, participation constraints, and overlap/covering constraints for ISA hierarchies. Some foreign key constraints are also implicit in the definition of a relationship set. – Some constraints (notably, functional dependencies) cannot be expressed in the ER model. – Constraints play an important role in determining the best database design for an enterprise. Database Management Systems Raghu Ramakrishnan 40

Summary of ER (Contd. ) v ER design is subjective. There are often many ways to model a given scenario! Analyzing alternatives can be tricky, especially for a large enterprise. Common choices include: – Entity vs. attribute, entity vs. relationship, binary or n-ary relationship, whether or not to use ISA hierarchies, and whether or not to use aggregation. v Ensuring good database design: resulting relational schema should be analyzed and refined further. FD information and normalization techniques are especially useful. Database Management Systems Raghu Ramakrishnan 41

Homework Assignment v Problem 2. 3 at the end of Chapter 2 – Pages 52 v Due Jan 18, 2007: send e-version v Format for homework: name, ID. Database Management Systems Raghu Ramakrishnan 42