Entity-Relationship Modeling for Database Design

1. CSE 480: Database Systems • Lecture 2: Entity-Relationship Modeling • Reference: • Read Chapter 3 of the textbook

2. Database Design • Goal is to derive a specification of the database schema • Schema is the description of the database (e.g., names of tables, columns/attributes, attribute types, and constraints)

3. Database Design 4 key steps: Requirement analysis: Discover what information needs to be stored and how the stored information will be used Conceptual database design: Create conceptual schema for the database using high-level data model (e.g., entity-relationship modeling) Logical database design: Convert E-R model to implementation data model (relational model) Physical design: Specify the internal storage structure, indexes, and file organizations for the database files

4. Example: COMPANY Database • Requirement analysis: • The company is organized into DEPARTMENTs. • Each department has a unique name, unique number and an employee who manages the department. We keep track of the start date of the department manager. A department may have several locations. • Each department controls a number of PROJECTs. Each project has a unique name, unique number and is located at a single location • 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 need to 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

5. Conceptual Design: E-R Diagram We will explain how to construct such a diagram in the next two lectures

6. Logical Design: Mapping to Relational Schema We will explain how to map the E-R diagram to a relational schema in lecture 7

7. Entity-Relationship (E-R) Diagram • A design methodology for modeling the concepts in an enterprise (mini-world) • Concepts: • Entity types • Relationship types • Constraints • E-R Diagrams provide a graphical representation of the entities, relationships, and constraints that make up a given design

8. Entity Types: The ‘E’ in E-R Diagram Entities: specific “objects” or “things” in the mini-world that are represented in the database Professor John Doe, Electrical Engineering Department, CSE480, the red car that always park next to the building entrance, etc Entity Type: collection of similar entities Bob Doe and Mary Doe are STUDENTs Electrical Engineering is a DEPARTMENT E-R diagram models the entity types (not individual entities)

9. Attributes of Entity Types • Attributes are properties associated with an entity type • Attributes of EMPLOYEE entity type include Name, SSN, Employee ID, BirthDate, Address, Salary, StartDate, etc • Attributes of STUDENT entity type include Name, PID, GPA, sex, major, last semester enrolled, etc. • When designing the E-R diagram, you need to • List all the entity types • List the attributes associated with each entity type • You also need to know the TYPE of each attribute • Simple or composite • Single-valued or multi-valued • Stored or derived

10. Address( Street_address (Number, Street, Apartment_number), City, State, Zip) Types of Attributes • Simple (Atomic) vs Composite • Simple (atomic) attributes are indivisible • SSN, Gender, Salary, … • Composite attributes may be composed of several components • Name (FirstName, MiddleName, LastName) • May have nested components

11. Types of Attributes • Single-valued vs. Multi-valued • Single-valued: one value for each entity • Examples: Age, Birth Date, SSN • Multi-valued: Multiple values for each entity • Examples: Colors of a CAR, Hobbies of a STUDENT, Email addresses of a PERSON • Multi-valued composite (Complex) attribute • Ex: PreviousDegrees of a STUDENT denoted by {PreviousDegrees (College, Year, Degree, Field)} • Ex: {(MSU, 1994, BS, CS), (UM, 1996, MS, CS)}

12. Types of Attributes • Stored vs Derived • Derived attribute is not physically stored in the database; its value is computed from other attributes or from related entities • Examples: • Age (derived from Birth Date), • NumberOfEmployees (derived by counting number of entities associated with the Employee entity type), • GPA (derived by averaging the grades of each STUDENT entity from the GRADE_REPORT entity type)

13. Example: MOVIE Entity Type MOVIE entity type has the following attributes X X X X X X X X X X X X X X X X X X

14. Constraints on Entity Types • When designing the E-R diagram, you also need to think about constraints on the entity types • Domain constraint • Null constraint • Key constraint

15. Domain Constraint • Each attribute is associated with a set of values(domain or data type) • Employee ID is CHAR(10), • Salary is FLOAT, • StartDate is DATE, • SSN is CHAR(10) • Hourly is BOOLEAN (Hourly employee vs Salaried employee) • The domain of an attribute restricts the range of valid values an attribute can have (domain constraint) • Example: if SSN is CHAR(10) and you try to add a record with SSN = “123-456-1211” to the database, it will violate the domain constraint of the attribute (so the DBMS will throw an error)

16. NULL Constraint • A special placeholder to denote the following: • When an attribute is inapplicable to an entity • When an attribute value is unknown or missing • Not exactly a “value” • If John’s blood type is NULL and Mary’s blood type is NULL, it does not mean that they both have the same attribute values • Null constraint: restricts whether an attribute value can be NULL

17. Key Constraint • When you store a data instance (record) into the database, you expect to be able to retrieve it with a query • To do this, you will need to distinguish each data instance from other instances in the database • Key attribute: attribute for which each entity must have unique value • Examples: SSN of EMPLOYEE, PID of STUDENT, DEPTNUMBER of DEPARTMENT • Key constraint: Prohibits two entities from having the same value for the key attribute • Used to uniquely identify individual entities in a database

18. Key Constraint A key attribute may be composite Registration is a key of CAR entity type with components (Registration_State, Registration_Number) Minimality property: superfluous attribute must not be included in the key Ex: SSN is minimal whereas (SSN, Name) is not minimal Some entity types may have more than one key Ex: VehicleID and (Registration_State, Registration_Number) are keys to CAR

19. Key attributes are underlined Key attributes are underlined Representing Entity Type in E-R Diagram Composite attribute No_Owners Derived attribute Multi-valued attributes have double ovals Entity type Attribute

20. Summary

21. Exercise: COMPANY database • Requirements: • The company is organized into DEPARTMENTs. • Each department has a unique name, unique number and an employee who manages the department. We keep track of the start date of the department manager. A department may have several locations. • Each department controls a number of PROJECTs. Each project has a unique name, unique number and is located at a single location • 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

22. Exercise: COMPANY database • Requirements: • The company is organized into DEPARTMENTs. • Each department has a unique name, unique number and an employee who manages the department. We keep track of the start date of the department manager. A department may have several locations.

23. Exercise: COMPANY database • Requirements: • Each department controls a number of PROJECTs. Each project has a unique name, unique number and is located at a single location

24. Exercise: COMPANY database • Requirements: • 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.

25. Exercise: COMPANY database • Requirements: • Each employee may have a number of DEPENDENTs. For each dependent, we keep track of their name, sex, birthdate, and relationship to employee

26. Entity Types • This initial design is not complete Entity types are not independent

27. Refining design by using relationships • Relationships between entity types

28. Relationships and Relationship Types • Relationship: relates two or more entities • EMPLOYEE John Doe works for Geography DEPARTMENT • EMPLOYEE Mary Smith works for Chemistry DEPARTMENT • Relationships of the same type are grouped into a relationship type

29. Name Location Number CONTROLS PROJECT DEPARTMENT (Draw the rest of its attributes) Representing Relationship Types in ER Diagram

30. Start_date Name Number MANAGES EMPLOYEE DEPARTMENT (Draw the rest of its attributes) Locations Attributes of a Relationship Type Relationship types can also have attributes

31. Hours PROJECT WORKS_ON EMPLOYEE DEPARTMENT SUPERVISION WORKS_FOR Relationship Types for COMPANY database

32. DEPENDENTS_OF DEPENDENT EMPLOYEE (Draw the rest of its attributes) Relationship Types for COMPANY database (Draw the rest of its attributes) Not quite right yet! (We will revisit this in lecture 3)

33. SUPERVISION Recursive Relationships and Roles • Relationship can relate elements of same entity type (recursive relationship) • Ex: Supervises relationship type relates two Employee entity types • Mary supervises Bob • Need to distinguish different entities participating in a relationship

34. SUPERVISION Roles • Use role name to indicate the role that a participating entity plays in a relationship instance • SUPERVISION has roles Subordinate and Supervisor • Role names must be provided for every recursive relationship type • Role names are not necessary where all participating entity types are distinct

35. Relationship Degree • Degree: the number of entity types participating in a relationship type • Binary relationships (WORKS_ON, MANAGES) • Ternary relationship SUPPLY PROJECT SUPPLIER PART

36. Summary • Conceptual database design • Using E-R modeling • Entity types • Domain, null, and key constraints • Relationship types, their attributes, roles, and degree • Relationship Constraints? (next lecture)

37. Exercise • Choose a domain, for example: • Airline reservation system • Electronic medical records • Online bookstore (e.g., Amazon) • Law enforcement (e.g., FBI criminal database) • Online photo sharing (e.g., Flickr) • College football/basketball database • Answer the following questions: • What are the entity types and their corresponding attributes? • What are the relationship types and their corresponding attributes? • What are the constraints? Are these constraints on entity types or relationship types? • Is there any other constraints that cannot be easily modeled? • Draw the E-R diagram