CMPT 258 Database Systems

1. CMPT 258 Database Systems The Entity-Relationship Model (Chapter 2)

2. Overview of Database Design • Conceptual design: (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? • What are the integrity constraintsthat hold? • A database ‘schema’ in the ER Model can be represented pictorially (ER diagrams). • Can map an ER diagram into a relational schema.

3. name ssn lot Employees ER Model Basics • Entity-Relationship (ER) data model • A database can be modeled as a collection of entities and relationship among entities • Widely used to develop an initial database design • Entity: • Real-world object distinguishable from other objects. • An entity is described (in DB) using a set of attributes. • Degree of an entity: number of attributes

4. name ssn lot Employees ER Model Basics (cont.) • 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. • Key: a minimal set of attributes whose values uniquely identify an entity in the set

5. name ssn lot Employees ER Model Basics (cont.) • Each entity set has a key. • Key: a minimal set of attributes whose values uniquely identify an entity in the set • There could be more than one candidate keys; if so, we designate one of them as the primary key.

6. ER Model Basics (cont.) • Each attribute has a domain. • A domain defines the possible values of each attributes • E.g., attribute name might be the set of 20-character string • E.g., attribute rating may have its domain of integers 1 through 10. • Specifying a domain • Data type specified for each domain

7. Domain Constraints • Typically include: • Numeric data types for integers and real numbers • Characters • Booleans • Fixed-length strings • Variable-length strings • Date, time, timestamp • Money • Other special data types

9. since name dname ssn lot budget did Works_In Employees Departments ER Model Basics (cont.) • Relationship: Association among two or more entities. • E.g., James works in Pharmacy department. • Descriptive attribute, since • Used to record information about the relationship, rather than about any one of the participating entities.

10. since name dname ssn lot budget did Works_In Employees Departments ER Model Basics (cont.) • Relationship: Association among two or more entities. • A relationship must be uniquely identified by the participating entities, without reference to the descriptive attributes.

11. since since name name dname dname ssn did did budget budget lot Departments Employees Manages Works_In since ER Model Basics (cont.) • Relationship Set: Collection of similar relationships. • Same entity set could participate in different relationship sets

12. ER Model Basics (cont.) • Instance of a relationship set is a set of relationships.

13. Ternary Relationship • Suppose that each department has offices in several locations and we want to record the locations at which each employee works.

14. name ssn lot Employees super-visor subor-dinate Reports_To ER Model Basics (cont.) • A relationship might involve two entities in the same entity set • Reports_To (emp1, emp2) • Two entities with different “roles” in same set. • role indicators • Reports_To (subordinate_ssn, • supervisor_ssn)

15. 1-to-1 1-to Many Many-to-1 Many-to-Many since name dname ssn lot budget did Works_In Employees Departments Key Constraints • Consider Works_In: An employee can work in many departments; a dept can have many employees. • 1:1 • 1:N • N:1 • M:N M N

16. since name dname ssn lot did budget Employees Manages Departments 1-to-1 1-to Many Many-to-1 Many-to-Many Key Constraints • In contrast, each dept has at most one manager, according to the key constrainton Manages. 1 N

17. since name dname ssn lot did budget Employees Manages Departments 1-to-1 1-to Many Many-to-1 Many-to-Many Key Constraints • The constraint that each department has at most one manager is an example of a key constraint, and it implies that each Departments entity appear in at most one Manages relationship. • Arrowstates that given a Department entity, we can uniquely determine the manages relationship in which it appears.

18. 1-to-1 1-to Many Many-to-1 Many-to-Many since name dname ssn lot did budget Employees Manages Departments Key Constraints • Each department has only one manager • And each employee can manage only one department. • Cardinality ratio 1 1 18

19. Exercises

20. since since name name dname dname ssn did did budget budget lot Departments Employees Manages Works_In since Participation Constraints • 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). • Every Departments entity must appear in an instance of the Manages relationship.

21. since since name name dname dname ssn did did budget budget lot Departments Employees Manages Works_In since Participation Constraints • 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). • Every Departments entity must appear in an instance of the Manages relationship. 1 N N M

22. since since name name dname dname ssn did did budget budget lot Departments Employees Manages Works_In since • If the participation of an entity set in a relationship set is total, the two are connected by a thick line

23. Exercises 23

24. 24

25. name cost pname age ssn lot Policy Dependents Employees Weak Entities • Employees can purchase insurance to cover their dependents. • Only dependents’ SSN is not needed • If an employee quits, any policy owned by the employee is terminated and all relevant policy and dependent info will be deleted from the database. ssn

26. name cost pname age ssn lot Policy Dependents Employees Weak Entities • Employees can purchase insurance to cover their dependents. • Only dependents’ SSN is not needed • If an employee quits, any policy owned by the employee is terminated and all relevant policy and dependent info will be deleted from the database. ssn N 1

27. name cost pname age ssn lot Policy Dependents Employees Weak Entities • Employees can purchase insurance to cover their dependents. • Only dependents’ SSN is not needed • If an employee quits, any policy owned by the employee is terminated and all relevant policy and dependent info will be deleted from the database. N 1

28. name cost pname age ssn lot Policy Dependents Employees Weak Entities • A weak entity can be identified uniquely only by considering the primary key of another (owner) entity. • Partial key: the set of attributes of a weak entity set that uniquely identify a weak entity for a given owner entity N 1

29. name cost pname age ssn lot Policy Dependents Employees Weak Entities • Owner entity set and weak entity set must participate in aone-to-one or one-to-many relationship set (one owner, one or more weak entities). • Weak entity set must have total participation in this identifying relationship set. • identifying owner, strong entity

30. Summary so far • Entities and Entity Set (boxes) • Relationships and Relationship sets (diamonds) • binary • ternary • n-ary • Key constraints (1:1,1:N, N:1, M:N, arrows on 1 side) • Participation constraints (total and partial) • Weak entities - require strong entity for key • Next, a couple more advanced concepts…