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 GUIs 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
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
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
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
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
11, 1N, or NM (ManyMany)
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..