Data Modeling Using the Entity-Relationship (ER) Model

1. Data Modeling Using the Entity-Relationship (ER) Model IS 320: Introduction to Database HatoonAlSagri IS Department

2. Database Design Real-world domain Conceptual model DBMS data model Create Schema (DDL) Load data (DML) Steps in building a database for an application: 1. Understand real-world domain being captured 2. Specify it using a database conceptual model (ER) 3. Translate specification to model of DBMS (relational) 4. Create schema using DBMS commands (DDL) 5. Load data (DML) IS Department

3. Entity Sets • A database can be modeled as: • a collection of entities, • relationship among entities. • An entity is an object that exists and is distinguishable from other objects. • Example: specific person, company, event, plant • Entities have attributes • Example: people have names and addresses • An entity set is a set of entities of the same type that share the same properties. • Example: set of all persons, companies, trees, holidays IS Department

4. Example COMPANY Database • Simple example database application, called COMPANY, that serves to illustrate the basic ER model concepts and their use in schema design. • The COMPANY database keeps track of a company’s employees, department, and project. • After the requirements collection and analysis phase, the database designers provided the following description of the “miniworld”-part of the company IS Department

5. COMPANY database- Requirements • 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. A department may have several location. • Each department controls a number of projects. each project has a name, number and is located at a single location. • We store each employee’s name, social security number, address, salary, sex, and birth date. 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, birth date, and relationship to employee. IS Department

6. The ER Data model for the COMPANY database. IS Department

7. Entity sets: attributes Example: customer = (customer-id, customer-name, customer-street, customer-city)loan = (loan-number, amount) • An entity is represented by a set of attributes, that is descriptive properties possessed by all members of an entity set. • Domain – the set of permitted values for each attribute • Attribute types: • Simple and composite attributes. • Single-valued and multi-valued attributes • E.g. multivalve attribute: phone-numbers • Derived attributes • Can be computed from other attributes • E.g. age, given date of birth IS Department

8. IS Department ER Model - Entity and Attributes salary name address sex Bdate Ssn Employee

9. Types of Attributes (1) • Composite versus Simple (Atomic) Attributes • Simple attribute: Each entity has a single atomic value for the attribute. For example, SSN or Sex. • Composite attribute: The attribute may be composed of several components. For example, Address (House#, Street, City, State, ZipCode) Name (Fname, Mname, Lname). • Composition may form a hierarchy where some components are themselves composite. IS Department

10. Example of a composite attribute IS Department

11. IS Department Composite versus Simple (Atomic) Attributes Mname Fname Lname Salary Name Address Sex Bdate Ssn Employee

12. Types of Attributes (2) • Single-Value versus Multivalued Attributes • Most attributes have a single value for a particular entity; such attributes are called Single-valued For example, Age of a PERSON • An entity may have multiple values for that attribute. such attributes are called Multivalued For example, Color of a CAR or Tel_num of a STUDENT. Denoted as {Color} or {Tel_num}. • A multivalued attribute may have lower and upper bounds to constrain the number of values allowed for each individual entity. IS Department

13. IS Department Single-Value versus Multivalued Attributes Mname Lname Fname DOB Name St_no Tel_no STUDENT

14. Types of Attributes (3) • Stored versus Derived Attributes • Derived attribute is an attribute that represents a value that is derived from the value of a related attribute, not necessarily in the same entity type. For example, Age and BirthDate of a PERSON The value of Age can be determined from the current date and the value of that person’s BirthDate. The Age attribute called a derived attribute is said to be derivable from the BirthDate attribute which is called a stored attribute. Other example, Total_cost is derived from quantity*unit_price IS Department

15. IS Department Stored versus Derived Attributes Mname Lname Fname DOB Name St_no Tel_no Age STUDENT

16. Types of Attributes (4) • Null value is a special value, In some cases a particular entity may not have an applicable value for an attribute. For example, The ApartmentNumber of an Address Null value cases: Not applicable for an attribute; or exist but missing; or not known • In general, composite and multi-valued attributes may be nested arbitrarily to any number of levels although this is rare. Such attributes are called Complex Attributes For example,PreviousDegrees of a STUDENT is a composite multi-valued attribute denoted by {PreviousDegrees (College, Year, Degree, Field)}. IS Department

17. IS Department Complex Attributes initial FName LName Area_cd name DOB No St_no Tel_no EX STUDENT

18. Key Attributes An attribute of an entity type for which each entity must have a unique value is called a key attribute of the entity type. For example, SSN of EMPLOYEE. Candidate key (CK) is the minimal set of attributes that uniquely identifies an entity. It cannot contain null. For example,student_no, social_security_no, branch_no… Primary Key (PK) is a candidate key that is selected to uniquely identify each entity. Alternate Key (AK) is a candidate key that is NOT selected to be the primary key. IS Department

19. Keys Candidate key (CK) is the minimal set of attributes that uniquely identifies an entity. It cannot contain null. For example,student_no, social_security_no, branch_no… Primary Key (PK) is a candidate key that is selected to uniquely identify each entity. Alternate Key (AK) is a candidate key that is NOT selected to be the primary key. IS Department

20. Keys Example Candidate Key Alternate Keys Primary Key EMPLOYEE (Id, SSN, Full_name, DOB, Dept_no) IS Department

21. Keys A key can be: Simple key is a candidate key of one attribute For example,student_no, branch_no… Composite key is a candidate key that consists of two or more attributes For example, STUDENT (Fname, Mname, Lname) CLASS (crs_code, section_no) ADVERT (property_no, newspaperName, dateAdvert) IS Department

22. Choice of PK Choice of Primary Key (PK) is based on: Attribute length Number of attributes required Certainty of uniqueness Each Primary key is underlined IS Department

23. Primary Key in ERD IS Department initial FName LName Area_cd Name Section_no Name DOB No St_ID Crs_code Tel_no Hours EX STUDENT CLASS Age Composite Key Simple Key

24. COMPANY database • 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 departmentmanager.A department may have several location. • Each department controls a number of PROJECTs. Each project has a name, number and islocated at a single location. • We store each EMPLOYEE’sname, social security number, address, salary, sex, and birth date. 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, birth date, and relationship to employee. IS Department

25. IS Department EMPLOYEE Entity Mname Lname Fname name Ssn Address EMPLOYEE Bdate Salary Sex

26. DEPARTMENTS Entity Both name and number are unique for a department. A department may be spread over many locations. The number of employees in a department is derivable from the Works-for relationship. IS Department Name Number DEPARTMENT NumberOf Employees Locations

27. IS Department PROJECT Entity Number Name Location PROJECT * Number of hours per week that an employee currently works on each project ???

28. DEPENDENT Entity Dependents are only uniquely identifiable in the context of an employee weak entity type partial key is name Note the standard pattern for weak entities. IS Department Ssn EMPLOYEE dept. of Name Sex DEPENDENT Bdate Relationship

29. Initial Design of Entity Types: EMPLOYEE, DEPARTMENT, PROJECT, DEPENDENT IS Department

30. Relationships and Relationship Types A relationship relates two or more distinct entities with a specific meaning. For example,EMPLOYEE John Smithworks on the ProductX PROJECT or EMPLOYEE Franklin Wongmanages the Research DEPARTMENT. Relationships of the same type are grouped or typed into a relationship type. For example, the WORKS_ON relationship type in which EMPLOYEEs and PROJECTs participate, or the MANAGES relationship type in which EMPLOYEEs and DEPARTMENTs participate. The degree of a relationship type is the number of participating entity types. IS Department

31. Example relationship instances of the WORKS_FOR relationship between EMPLOYEE and DEPARTMENT IS Department

32. Example relationship instances of the WORKS_ON relationship between EMPLOYEE and PROJECT IS Department

33. Degree of Relationship Type STUDY STUDENT COURSE REGISTER STUDENT COURSE STAFF Degree of relationship refers to number of participating entity types in a relationship. A relationship of degree two (2 entity types) are binary. A relationship of degree three (3 entity types) are ternary. IS Department

34. Relationships and Relationship Types MANAGES DEPARTMENT EMPLOYEE WORKS-FOR More than one relationship type can exist with the same participating entity types. For example, MANAGES and WORKS_FOR are distinct relationships between EMPLOYEE and DEPARTMENT, but with different meanings and different relationship instances. IS Department

35. Weak Entity Types • 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 IS Department

36. Weak Entity Types IS Department Mname Lname Relationship Fname Address Ssn Sex Name name Sex Bdate Salary Bdate dept. of EMPLOYEE DEPENDENT

37. Recursive Relationship Type We can also have a recursive relationship type. Recursive relationship is a relationship type where the same entity type participates more than once in a different role. It is a unary relationship. Both participations are same entity type in different roles. For example, SUPERVISION relationships between EMPLOYEE (in role of supervisor or boss) and (another) EMPLOYEE (in role of subordinate or worker). In ER diagram, need to display role names to distinguish participations. IS Department

38. A Recursive Relationship Supervision IS Department EMPLOYEE COURSE requester prerequisite Supervisee Supervisor REQUIRE SUPERVISION Role indicates the purpose that each participating entity type plays in a relationship

39. Roles Role can be used when two entities are associated through more than one relationship to classify the purpose of each relationship IS Department

40. Attributes of Relationship types WORKS-ON Hours A relationship type can have attributes; For example, HoursPerWeek of WORKS_ON; its value for each relationship instance describes the number of hours per week that an EMPLOYEE works on a PROJECT. IS Department

41. Constraints on Relationships • ( Also known as ratio constraints ) • Cardinality Ratio (specifies maximum participation) • One-to-one (1:1) • One-to-many (1:N) or Many-to-one (N:1) • Many-to-many • Existence Dependency Constraint (specifies minimum participation) (also called Participation Constraint) • Zero (optional participation, not existence-dependent) • One or more (mandatory, existence-dependent) IS Department

42. IS Department Many-to-one (N:1) RELATIONSHIP

43. Many-to-many (M:N) RELATIONSHIP IS Department

44. Structural Constraints one way to express semantics of relationships Structural constraints on relationships: • Cardinality ratio (of a binary relationship): 1:1, 1:N, N:1, or M:N • Shown by placing appropriate number on the link. • Participation constraint (on each participating entity type): total (called existence dependency) or partial. • Shown by double lining the link NOTE: These are easy to specify for Binary Relationship Types. IS Department

45. IS Department 1 1 MANAGES EMPLOYEE DEPARTMENT WORKS_FOR 1 N EMPLOYEE DEPARTMENT N M WORKS_ON EMPLOYEE PROJECT 1 DEPENDENTS_OF N DEPENDENT EMPLOYEE CONTROLS 1 N PROJECT DEPARTMENT N SUPERVISION 1 EMPLOYEE EMPLOYEE

46. IS Department The ER Data model for the COMPANY database

47. Alternative (min, max) notation for relationship structural constraints: • Specified on each participation of an entity type E in a relationship type R • Specifies that each entity e in E participates in at least min and at most max relationship instances in R • Default(no constraint): min=0, max=n • Must have minmax, min0, max 1 • Derived from the knowledge of mini-world constraints Examples: • A department has exactly one manager and an employee can manage at most one department. • Specify (0,1) for participation of EMPLOYEE in MANAGES • Specify (1,1) for participation of DEPARTMENT in MANAGES • An employee can work for exactly one department but a department can have any number of employees. • Specify (1,1) for participation of EMPLOYEE in WORKS_FOR • Specify (1,N) for participation of DEPARTMENT in WORKS_FOR IS Department

48. IS Department The (min,max) notation relationship constraints

49. COMPANY ER Schema Diagramusing (min, max) notation IS Department

50. The Enhanced Entity-Relationship (EER) Model IS 320: Introduction to Database IS Department