Chapter 3:

1. Chapter 3: Data Modeling Using the Entity-Relationship (ER) Model

2. Outline • Example Database Application (COMPANY) • ER Model Concepts • Entities and Attributes • Entity Types, Value Sets, and Key Attributes • Relationships and Relationship Types • Weak Entity Types • Roles and Attributes in Relationship Types • ER Diagrams - Notation • ER Diagram for COMPANY Schema • Data Modeling Tools Introduction to Databases

3. Database Design 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) Real-world domain Conceptual model DBMS data model Create Schema (DDL) Load data (DML) Introduction to Databases

4. Entity-Relationship Model (E/R) • The Entity-Relationship model (ER) is a high-level description of the structure of the DB • The Entity-Relationship Diagram (ERD) is a graphical model for representing the conceptual model for the data • A ER models the DB using three element types: -Entities - Attributes - Relationships Introduction to Databases

5. 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 Introduction to Databases

6. 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. Introduction to Databases

7. The ER Data model for the COMPANY database. Introduction to Databases

8. ER Model Concepts- Entity • Entitiesare specific objects or things in the mini world that are represented in the database. • An entity may be an object with a physical existence or it may be an object with a conceptual existence. For example the EMPLOYEE John Smith, the Research DEPARTMENT the ProductX PROJECT Introduction to Databases

9. Entities & Entity Type • Entity is an object that exists and is distinguishable from other objects. For example, Person (John Smith) Course (IS320) University (Imamu) • Entity Type is a set of entities of the same type that share the same properties. For example, set of all persons, companies, trees, courses STUDENT PATIENT Introduction to Databases

10. ENTITY SET corresponding to theENTITY TYPE PATIENT Entity Type PATIENT Name, Age, Weight, height patient1 (Name: Nourah, Age: 20, Weight: 50, height: 150) patient2 (Name: Mona, Age: 25, Weight: 53, height: 160) patient3 (Name: Fahad, Age: 35, Weight: 79, height: 180) . . . Entity Introduction to Databases 10

11. ER Model Concepts- Attribute • Attributesare properties used to describe an entity. All entities in one entity type have the same attributes For example an EMPLOYEE entity may have a Name, SSN, Address, Sex, BirthDate. • A specific entity will have a value for each of its attributes. For example a specific employee entity may have Name='John Smith', SSN='123456789', Address ='731, Fondren, Houston, TX', Sex='M', BirthDate='09-JAN-55‘ • Each attribute has a value set (or data type) associated with it For example integer, string, ….. Introduction to Databases

12. In an entity-relationship diagram, each entity type is represented by a rectangle. Each attribute of an entity set is represented by an oval, with a line to the rectangle representing its entity set. ER Model - Entity and Attributes Employee DEPARTMENT name Salary 12 Introduction to Databases

13. ER Model - Entity and Attributes salary name address sex Bdate Ssn Employee Introduction to Databases

14. 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. Introduction to Databases

15. Example of a composite attribute Introduction to Databases

16. Composite versus Simple (Atomic) Attributes Mname Fname Lname Salary Name Address Sex Bdate Ssn Employee Introduction to Databases

17. 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. Introduction to Databases

18. Single-Value versus Multivalued Attributes Mname Lname Fname DOB Name St_no Tel_no STUDENT Introduction to Databases

19. ENTITY SET corresponding to theENTITY TYPE CAR CAR Registration(RegistrationNumber, State), VehicleID, Make, Model, Year, (Color) car1 ((ABC 123, TEXAS), TK629, Ford Mustang, convertible, 1999, (red, black)) car2 ((ABC 123, NEW YORK), WP9872, Nissan 300ZX, 2-door, 2002, (blue)) car3 ((VSY 720, TEXAS), TD729, Buick LeSabre, 4-door, 2003, (white, blue)) . . . Introduction to Databases

20. 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 Introduction to Databases

21. Stored versus Derived Attributes Mname Lname Fname DOB Name St_no Tel_no Age STUDENT Introduction to Databases

22. 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)}. Introduction to Databases

23. Complex Attributes initial FName LName Area_cd name DOB No St_no Tel_no EX STUDENT Introduction to Databases

24. Entity Types and Key Attributes • Entities with the same basic attributes are grouped or typed into an entity type. For example, the EMPLOYEE entity type or the PROJECT entity type. • 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. Introduction to Databases

25. 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. Introduction to Databases

26. Keys Example EMPLOYEE (Id, SSN, Full_name, DOB, Dept_no) Candidate Key Alternate Keys Primary Key Introduction to Databases

27. 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 (Lname,Fname, Init) CLASS (crs_code, section_no) ADVERT (property_no, newspaperName, dateAdvert) Introduction to Databases

28. 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 Introduction to Databases

29. Primary Key in ERD 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 Introduction to Databases

30. 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. Introduction to Databases

31. EMPLOYEE Entity Mname Lname Fname name Ssn Address EMPLOYEE Bdate Salary Sex Introduction to Databases

32. 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. Name Number DEPARTMENT NumberOf Employees Locations Introduction to Databases

33. PROJECT Entity Number Name Location PROJECT * Number of hours per week that an employee currently works on each project ??? Introduction to Databases

34. 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. Ssn EMPLOYEE dept. of Name Sex DEPENDENT Bdate Relationship Introduction to Databases

35. Initial Design of Entity Types:EMPLOYEE, DEPARTMENT, PROJECT, DEPENDENT Introduction to Databases