Database Management Systems CS 542 Spring 2017 Modeling

Database Management Systems CS 542 --- Spring 2017 Modeling Your Data Chapter 2. Instructor: Elke Rundensteiner Worcester Polytechnic Institute

Overview of Database Design ─What are the entities and relationships in your application? ─What information store in database? ─What integrity constraints or business rules hold? Worcester Polytechnic Institute

ER Model : Database Design ─A database `schema’ in the ER Model represented pictorially Worcester Polytechnic Institute

ER Model Basics • Entity: Real-world object described by a set of attributes. name ssn lot • Entity Set: A collection of similar entities. E. g. , all employees. Employees ─ ─ ─ All entities in entity set have same set of attributes. Each entity set has a key. Each attribute has a domain. Worcester Polytechnic Institute

ER Model Basics (Contd. ) • Relationship: Association among two or more entities. E. g. , Attishoo works in Pharmacy department • Relationship Set: Collection of similar relationships. ─ An n-ary relationship set R relates n entities e 1 from E 1, . . . , en from En Worcester Polytechnic Institute

ER Model Basics since name ssn dname lot Employees budget did Works_In Departments • Relationship: Association among two or more entities. Worcester Polytechnic Institute

ER Model Basics • Relationship: § Same entity set can participate in different relationship sets (“roles”) in same set. name ssn lot Employees super-visor subordinate Reports_To Worcester Polytechnic Institute

Key Constraints 1 -to-1 1 -to Many-to-1 Many-to-Many Worcester Polytechnic Institute

since name ssn lot Employees dname budget did Works_In Departments • Works_In: An employee can work in many departments, and a dept can have many employees. Worcester Polytechnic Institute

Which key constraint ? since name ssn dname lot Employees did Works_In 1 -to-1 1 -to Many budget Departments Many-to-1 Many-to-Many Worcester Polytechnic Institute

Which Key Constraint Case ? ? • Consider Manager Relationship? since name ssn dname lot Employees did Manages budget Departments Worcester Polytechnic Institute

Which Key Constraint Case ? ? • Consider Manager Relationship : “Each dept has at most one manager. ” since name ssn dname lot Employees 1 -to-1 did Manages budget Departments 1 -to Many-to-1 Many-to-Many Worcester Polytechnic Institute

Key Constraint: 1 - to - Many since name • Each dept has at most one manager as per key constraint on Manages. dname ssn lot did Manages Employees budget Departments since name ssn dname lot Employees [0: n] did Manages budget [0: 1]Departments Worcester Polytechnic Institute

Participation Constraints • Must every department have a manager? since dname lot did ssn ? Employees budget ? Manages Departments Worcester Polytechnic Institute

Participation Constraints If every department must have a manager, that is, the participation of Departments in Manages is said to be total (vs. partial). since name ssn dname did lot Employees ? Manages ? budget Departments Worcester Polytechnic Institute

Participation Constraints • Every department must have a manager (exactly one)! since name ssn dname did lot Employees Manages budget Departments Worcester Polytechnic Institute

Participation Constraints ? since name ssn dname lot Employees did Works_In budget Departments Worcester Polytechnic Institute

Participation Constraints since name ssn did lot Employees dname Manages budget Departments Works_In since • every department has a manager • every employee works in at least one dept. • every dept has at least one employee Worcester Polytechnic Institute

Weak Entities • Weak entity can be identified uniquely only by considering the primary key of another (owner) entity. name ssn lot Employees cost Policy pname age Dependents Worcester Polytechnic Institute

Weak Entities • • Owner entity set and weak entity set must participate in a one-to-many relationship set (one owner and many weak entities). Weak entity set must have total participation in this identifying relationship set. name ssn lot Employees cost Policy pname age Dependents Worcester Polytechnic Institute

ISA (`is a’) Hierarchies 1. As in C++, or other PLs, attributes are inherited. 2. If we declare A ISA B, every A entity is also considered to be a B entity. name ssn lot Employees hourly_wages hours_worked ISA contractid Hourly_Emps Contract_Emps Worcester Polytechnic Institute

ISA (`is a’) Hierarchies Implicit Relationship Between Super. And Subentity? 1 -1 ? name ssn lot Employees hourly_wages hours_worked ISA contractid Hourly_Emps Contract_Emps • Reasons for using ISA: ─ To add descriptive attributes specific to a subclass. ─ To identify entities that participate in a relationship. Worcester Polytechnic Institute

ISA (`is a’) Hierarchies name • Overlap constraints: Can Joe be Hourly_Emps as well as Contract_Emps entity? (Allowed/disallowed) ssn lot Employees hourly_wages hours_worked ISA • Covering constraints: Does every Employee entity have to be Hourly_Emps or Contract_Emps entity? (Yes/no) contractid Hourly_Emps Contract_Emps Worcester Polytechnic Institute

Aggregation • Used when we have to model a relationship involving entity sets and a relationship set. Worcester Polytechnic Institute

Aggregation allows us to treat a relationship set as an entity set for purposes of participation in (other) relationships ssn name lot Employees Monitors since started_on pid pbudget Projects until dname did Sponsors budget Departments Worcester Polytechnic Institute

Aggregation ssn name lot Employees Monitors since started_on pid pbudget Projects until dname did Sponsors budget Departments Aggregation vs. ternary relationship: v Monitors is a distinct relationship, with a descriptive attribute. v Or, say each sponsorship is monitored by at most one employee. Worcester Polytechnic Institute

Summary of Conceptual Design • Conceptual design follows requirements analysis: ─ Yields a high-level description of data to be stored • ER model popular for conceptual design ─ Constructs are expressive - the way people think about their applications. • Basic constructs: entities, relationships, and attributes • Some additional constructs: weak entities, ISA hierarchies, and aggregation. • Note: There are many variations on ER model. Worcester Polytechnic Institute

Summary of ER Modeling • Constraints play important role in determining the best database design for an enterprise. • Integrity constraints expressed in ER model: ─ ─ key constraints, participation constraints, overlap/covering constraints for ISA hierarchies. Some foreign key constraints also implicit in definition of a relationship set. • Others cannot be expressed : ─ Some constraints (notably, functional dependencies) cannot be expressed in ER model. Worcester Polytechnic Institute