Group Project
Roadmap
1. Collect requirements (users, views, constraints, tasks)
2. Model requirements and validate (ERD and EERD)
3. Map ERD to relational model
4. Verify model with relational algebra
5. Build model using DBMS (SQL)
6. Optimize model (Normalization and Indexing)
7. Build transactions and other user
tools Intro. To DBMS 2
Project Assignment
1. Collect requirements (users, views, constraints, tasks)
Roadmap
1. Collect requirements (users, views, constraints, tasks)
2. Model requirements and validate (ERD and EERD)
3. Map ERD to relational model
4. Verify model with relational algebra
5. Build model using DBMS (SQL)
6. Optimize model (Normalization and Indexing)
7. Build transactions and other user
tools Intro. To DBMS 4
THE ENTITY–
RELATIONSHIP
MODEL
Entity-Relationship Model 6
Data Modeling Using the ER Model
ER Diagrams- Notation
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
Relationships of Higher Degree
Reasons for Modeling
Learn from the modeling process
Reduce complexity by abstraction
Remember details
Communication
◦ with team
◦ with variety of stakeholders
Documentation for future reference
Entity-Relationship Model 8
Entity-Relationship (ER) Model
Helpful for conceptualizing the Real World
Shows simple, static memory of a system
◦ Entity: A thing that exists
e.g. person, automobile, department, employee
◦ Entity Set: A group of similar entities
e.g. all persons, all automobiles, all employees
◦ Attribute: Property of an entity or relationship
e.g. person - name, address
◦ Domain: Set of values allowed for an attribute
Simple COMPANY Database
Employees◦Have unique ID, name, address
◦work for a department
◦may work on many projects
◦May work on no project
Departments◦Have unique Department Number and Manager
Projects◦Have unique Project Number
Need to track hours per week that employee works on each projectEntity-Relationship Model 10
Entity Sets and Attributes
Employees E#, ENAME, ADDRESS Departments D#, DNAME
Projects P#, PNAME E n t i t i e s
A t t r i b u t e s
Entity: A thing that exists
Entity Set: A group of similar entities
Attribute: Property of an entity or relationship
Entity-Relationship Diagram Notation
Entity Sets - rectangles
Attributes - circles
◦linked to entity sets or relationships by edges
Relationships - diamonds
◦linked to entity sets by edges
Entity-Relationship Model 12
Example: Relationships and Attributes
Project Employee Assigned
To
Employee Is Department
In
%TIME
Attribute Notation
Simple (atomic)
Composite
Employee Employee
Employee
LName FName
Name Empno
LName FName
Entity-Relationship Model 14
Multi-valued Attribute Attribute Notation
Student Major
Attribute Notation
Derived Attributes - Include in Department the average
salary of the employees in the department.
Employee Department
Salary AvgSal
Member Of
Entity-Relationship Model 16
Constraints on Relationships between Entity Sets
EmployeesE#, ENAME, ADDRESS Departments D#, DNAME
Projects P#, PNAME
Cardinality
◦ Employees may be assigned to only 1 department at a time.
◦ Employees may be assigned to several projects at once, each with an associated %time.
Cardinality in Relationships
Employees may be assigned to only 1
department at a time.
Employees may be assigned to
several projects at once, each with an associated %time.
Project Employee Assigned
To
Employee Is Department
In
%TIME
1
M N
N
Entity-Relationship Model 18
Participation: Total or Partial Employees must be assigned to a
department.
Total: Each entity must be included at least once in the relationship.
Employees need not be assigned to any projects.
Partial: Each entity instance need not be included at least once in the relationship.
Participation in Relationships
Partial Total
Project Employee Assigned
To
Employee Is Department
In
%TIME
1
M N
N
Entity-Relationship Model
Cardinality: Min-Max Constraint Notation
Original “Chen” Notation
Alternate “Constraint” Notation: (min, max)
Min represents participation (0 partial, >0 total).
Max represents cardinality.
Employee Works Project
Employee Works Project
1 n
(1,…)(1, 1) (0, n)(0,…)
Cardinality: Min-Max Constraint Notation
Original “Chen” Notation
Alternate “Constraint” Notation: (min, max)
Min represents participation (0 partial, >0 total).
Employee Works Project
Employee Works Project
1 n
(1,…)(1, 1) (0, n)(0,…)
Entity-Relationship Model 22
Weak Entities
Do not have key attributes of their own
Identified by being related to specific entities from another entity type
Always has a total participation constraint
Entity Dependent contains children of employees. No key attribute.
Dependents are identified by an employee. Entity Set Employee is called the Identifying Relationship or Owner Entity Type
Name in Dependent is a Partial Key
Name
Employee Dependents
Of Dependent
Name E#
1 N
ER Diagrams Notation
Summary
Give an example of a:
Compound attribute
Give an example of a:
Multivalued attribute
Give an example of a:
Partial key
Give an example of a:
Attribute of a relationship
Give an example of a:
Total participation in a relationship
Give an example of a:
One to one cardinality in a relationship
Worksheet
Complete E-R Worksheet I with classmate(s)
36
