Transcript 2/13 Relational Model

Information Resources
Management
February 13, 2001
Agenda
Administrivia
 Ternary Cardinality (revisited)
 The Relational Model
 Converting an E-R Model to a DB
 Exam Review

Administrivia
Homework #3
 Exam #1, next week 2/20

Ternary Relationships Cardinality

For the entity in question,
 For each unique pair of the other
entities,
 How many of the entity can there be?
 One
 More than one (many)
Ternary Cardinality
A
Has
For each unique
B/C pair, how
many A’s?
C
B
Ternary Cardinality
A
For each unique
B/C pair, how
many A’s?
Has
B
C
Example
(one-to-one-to-one):
Employee is assigned a phone number
for a project. A phone number is used
only for that employee and project.
Example (one-to-one-to-one):
Employee
For each
project/phone#
pair, how many
employees?
Has
Project
Phone
Number
Employee is assigned a phone number for a project. A
phone number is used only for that employee and project.
Example (one-to-one-to-one):
Employee
For each
project/employee
pair, how many
phone numbers?
Has
Project
Phone
Number
Employee is assigned a phone number for a project. A
phone number is used only for that employee and project.
Example (one-to-one-to-one):
Employee
Has
Phone
Number
Project
For each
employee/phone#
pair, how many
projects?
Employee is assigned a phone number for a project. A
phone number is used only for that employee and project.
Example (one-to-one-to-one):
Employee
Has
Project
Phone
Number
Employee is assigned a phone number for a project. A
phone number is used only for that employee and project.
Example
(one-to-one-to-many):
Employee assigned to a project works
at one location for that project but can
work at different locations for different
projects. At a location an employee
only works on one project, but there
can be many employees working on
that same project.
Example
(one-to-one-to-many):
Employee
Has
Location
Project
Example
(one-to-many-to-many):
Employee on a project has one
manager. Manager can manage
several projects. Each project has
one manager. Manager can manage
the same employee on different
projects.
Example
(one-to-many-to-many):
Employee
Has
Manager
Project
Example
(many-to-many-to-many):
Employees use many skills on many
projects and each project has many
employees with varying skills.
Example
(many-to-many-to-many):
Employee
Has
Skill
Project
The Relational Data Model
Components
 E-R Models to Relations

Relational Data Model
Components
Data structure
 Data manipulation
 Data integrity

Data Structure

Tables with rows and columns
Two-dimensional
 Column – Attribute
 Row – single instance of an entity –
each is unique


Sequence is immaterial (rows or
columns)
Data Structure – Alternative
Table
Row
Column
-
Relation
Tuple
Attribute
(domain – all possible values)
Database Schema – Definition
 For
each table/relation:
Name = (attribute, attribute, attribute, …)
 Example:
Employee = (employee_ID, name, dept#,
phone#)
Department = (dept#, dept_name)
Keys
 Attributes
can be either identifiers or
descriptors.
 Identifier uniquely determines an
instance of an entity.
 Identifier
is a key.
Types of Keys

Superkey - any combination of attributes
that uniquely determines each instance of
the entities in an entity set

Candidate Key - superkey for which no
proper subset is also a superkey

Primary Key - selected candidate key used
to identify each row (tuple)
Types of Keys
Superkeys  Candidate Keys  Primary Key
Candidate keys
Superkeys
Primary key
Composite Key
 Key
that consists of more than one
attribute.
 Example:
first name and last name
Example:
Course (course#, name, dept#,
location, time)
Superkeys?
Candidate Keys?
Primary Key?
Database Schema (Updated)
Underline primary key(s)
Name = (key, attribute, attribute,
attribute, …)
 Example:
Employee = (employee_ID, name,
dept#, phone#)
Department (dept#, dept_name)

Foreign Keys
 Attribute
in a relation that serves as
the primary key of another relation in
the same database.
to maintain database integrity –
can’t sell a product that isn’t in the
inventory, etc.
 Used
Database Schema (Updated)
Dashed underline foreign key(s)
Name = (key, attribute, attribute (FK),
attribute, …)
 Example:
Employee = (employee_ID, name,
dept#, phone#)
Department (dept#, dept_name)
 Could also use double underline (PK)

Multivalued Attributes
Attributes that repeat (once or more) for
a single entity.
 Enclosed in braces {}
Name = (key, attribute, attribute (FK),
{attribute, attribute}, …)
 Example:
Employee = (employee_ID, name,
dept#, {phone#, type})
Department (dept#, dept_name)

Converting an E-R Model to a
Database Schema
1.Strong Entities become Relations
 Identify the primary key from the
superkeys and candidate keys.
Converting an E-R Model to a
Database Schema
2. Weak Entities become Relations
 The primary key of a weak entity will
usually have to include the primary
key of the relation on which the weak
entity depends along with additional
identifying information for the weak
entity.
Converting an E-R Model to a
Database Schema
3. Binary, Ternary, and n-ary Relationships
 one-to-one or one-to-many
Identify foreign keys
 many-to-many
or associative entities
Build a “bridge” relationship whose
primary key is a composite key
composed of all the relationship’s
entities primary keys. These are also
foreign keys.
Converting an E-R Model to a
Database Schema
4. Unary Relationships
 one-to-one or one-to-many
Identify “recursive” foreign key
 many-to-many
Build a “bridge” relationship whose
primary key is a composite key
composed of two copies of the
entity’s primary key.
Converting an E-R Model to a
Database Schema
5. Supertype/Subtype - Option 1




Create separate relations for the supertype and each
subtype
Supertype contains all common attributes and the
primary key
Subtypes have the same primary key as the
supertype and only those attributes specific to that
subtype
Add to the supertype a subtype indicator. Only one
indicator is needed for disjoint. More that one is
needed if overlapping. If there is no completeness
constraint, allow the indicator to be null.
Converting an E-R Model to a
Database Schema
5. Supertype/Subtype - Option 2



Create a separate relation for each subtype only
Subtypes have overlapping attributes (those that are
common and would have been put in the relation for
the supertype if it was created)
If the primary key of the supertype is used as a
foreign key in other relations beyond the subtypes, a
primary key only relation for the supertype will need
to be created.
Converting an E-R Model to a
Database Schema
5. Supertype/Subtype - Option 3


Create one relation
Attributes in this relation are a superset created from
the attributes for the supertype and all subtypes.
(Null values must be permitted for the subtype
attributes as only a few are used for any given tuple.)
Example

Banking
Exercise:

Real Estate Office
Homework #4
Convert E-R Diagram to Schema
 Identify
 Primary keys (may be composite)
 Foreign keys
 Dashed underline (not in PK)
 Double underline (in PK)

Exam Review
DBMS - usage & alternatives
 Methodologies and people
 E-R Modeling
 Entity (strong, weak, associative)
 Relationships (degree, cardinality,
connectivity, existence)
 Gen/Spec
