Conceptual Models

1. Conceptual Models Agenda - Steps in the design of a DB - Need for conceptual models - The Entity-Relationship Model (ER-Model)

2. DB Design: Step 1 • Requirements collection • - Data • - What information needs to be stored • - How much data • - Functional • - Who will access what data • - Constraints between data • - How often will the data be accessed • - Security requirements

3. DB Design: Step 2 Conceptual design - Model of the objects and relationships between them - Object-oriented model - Relational model - Networked model …

4. DB Design: Step 3 Logical design - the data structures for the DB - the constraint specifications - the security/access requirement specifications, views

5. DB Design: Step 4 Physical design - Selection of computer - hard-disk - file-organization ...

6. DB Design: Step 5 Application Programming and Usage optimization - Convenient GUI’s for parametric users (idiot-proof) - Tracking data usage, usage optimization (e.g., indexes)

7. Step 1. Requirements collection Example: DB of a company operations: • The company is organized into departments. Each department has a name, number, and a manager. Each department is located in one or more places. We also store the start date of the department manager. • Each department controls a number of projects. Each project has a name, a number, and is located in a single place. • Employee information: social security number, address, salary, sex, and birth date. Each employee works for one department, but may work on many projects. We allocate the number of hours an employee works on a given project per week. We also record the supervisor of each employee. • Each employee may have a number of dependents. For each dependent: name, where they work, sex, birth date, and relationship to the employee.

8. Entity type Entity type Instance of Entity Instance of Entity John Smith John Smith Name Name 55 2311, Kirby, Houston, Texas Address Address Employee Employee 55 Age Age 2311, Kirby, Houston, Texas Phone Phone 713 713 - - 749 749 - - 2630 2630 Conceptual design: The ER model Observation: Most data describes some “thing”  some “entity” Example: Employees work for a Company Each entity is described by a set of attributes

9. Date-of-Birth Year Month Day Types of attributes 1. Simple Attributes that have atomic value Examples: Last Name, First name, Age 2. Composite Attributes that are made of up several components Examples: Date of birth, Address

10. Types of attributes.. 1. Simple 2. Composite 3. Multi-valued If the same entity has several values for an attribute Examples: Phone Number, Author 4. Derived The value of the attribute can be calculated from other data Example: Date of Birth is stored  Age can be derived Example: Grades are stored  CGI can be derived

11. Lname Name OtherNames Address Student SID Phone CAR State Model VehicleID Year LicenseNo Make KEY Attributes Definition: A set of attributes that have unique value for each instance of an entity type Example: SID for a student Example: {LicenseNo, State} and {VehicleID} for CAR Importance of the idea of “KEY” attribute(s): identifier

12. Weak Entity Types Entities with no key attribute(s) Example: (from our Employee DB requirements) Each employee may have a number of dependents. For each dependent: name, where they work, sex, birth date, and relationship to the employee. DEPENDENT Name DoB Company Sex Relationship

13. Relationships Relationships describe the structure of the connections between entities instances of Relationships Examples: Fugee Tsung is chair of IELM Department Lionel Ni is chair of COMP Department Generalize instances of Entities instances of Entities Entity type Entity type Relationship type Chairs Employee Department

14. Relationships.. Degree of relationship: Number of entities involved in each instance Degree = 2 Examples: Multiple relationships between same entity types Manages Employee Project Works-on

15. days/wk Relationships… Relationships can have their own attributes

16. Relationships…. Roles: Relationships between entities of same type Question: What is the degree of the relationship type ‘Supervises’

17. Structural constraints between relationships Cardinality constraints (for binary relationships) A binary relationship can be 1:1, 1:N, or N:M (Many:Many) Examples:

18. Structural constraints between relationships Participation constraints Total participation, Partial participation Examples:

19. Conceptual Design: The ER Model All information must be represented by an ER diagram Rules: 1. Must capture all information 2. Must show cardinality, participation of relationships 3. All weak entity types must participate in an identifying relationship

20. Example • The company is organized into departments. Each department has a name, number, and a manager. Each department is located in one or more places. We also store the start date of the department manager. • Each department controls a number of projects. Each project has a name, a number, and is located in a single place. • Employee information: social security number, address, salary, sex, and birth date. Each employee works for one department, but may work on many projects. We allocate the number of hours an employee works on a given project per week. We also record the supervisor of each employee. • Each employee may have a number of dependents. For each dependent: name, where they work, sex, birth date, and relationship to the employee.

21. Example..