Information Resources Management

1
Information Resources Management

• February 13, 2001

2
Agenda

• Administrivia
• Ternary Cardinality (revisited)
• The Relational Model
• Converting an E-R Model to a DB
• Exam Review

3
Administrivia

• Homework 3
• Exam 1, next week 2/20

4
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)

5
Ternary Cardinality
A
B
Has
For each unique B/C pair, how many As?
C
6
Ternary Cardinality
A
B
Has
C
For each unique B/C pair, how many As?
7
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.

8
Example (one-to-one-to-one)
Employee
Project
Has
For each project/phone pair, how many employees?
Phone Number
Employee is assigned a phone number for a
project. A phone number is used only for that
employee and project.
9
Example (one-to-one-to-one)
Employee
Project
Has
For each project/employee pair, how many phone
numbers?
Phone Number
Employee is assigned a phone number for a
project. A phone number is used only for that
employee and project.
10
Example (one-to-one-to-one)
Employee
Project
Has
For each employee/phone pair, how many projects?
Phone Number
Employee is assigned a phone number for a
project. A phone number is used only for that
employee and project.
11
Example (one-to-one-to-one)
Employee
Project
Has
Phone Number
Employee is assigned a phone number for a
project. A phone number is used only for that
employee and project.
12
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.

13
Example(one-to-one-to-many)
Employee
Project
Has
Location
14
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.

15
Example(one-to-many-to-many)
Employee
Project
Has
Manager
16
Example(many-to-many-to-many)

• Employees use many skills on many projects and
  each project has many employees with varying
  skills.

17
Example(many-to-many-to-many)
Employee
Project
Has
Skill
18
The Relational Data Model

• Components
• E-R Models to Relations

19
Relational Data Model Components

• Data structure
• Data manipulation
• Data integrity

20
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)

21
Data Structure Alternative

• Table - Relation
• Row - Tuple
• Column - Attribute
• (domain all possible values)

22
Database Schema Definition

• For each table/relation
• Name (attribute, attribute, attribute, )
• Example
• Employee (employee_ID, name, dept, phone)
• Department (dept, dept_name)

23
Keys

• Attributes can be either identifiers or
  descriptors.
• Identifier uniquely determines an instance of an
  entity.
• Identifier is a key.

24
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)

25
Types of Keys

• Superkeys ? Candidate Keys ? Primary Key

Candidate keys
Superkeys
Primary key
26
Composite Key

• Key that consists of more than one attribute.
• Example first name and last name

27
Example

• Course (course, name, dept, location, time)
• Superkeys?
• Candidate Keys?
• Primary Key?

28
Database Schema (Updated)

• Underline primary key(s)
• Name (key, attribute, attribute, attribute, )
• Example
• Employee (employee_ID, name, dept, phone)
• Department (dept, dept_name)

29
Foreign Keys

• Attribute in a relation that serves as the
  primary key of another relation in the same
  database.
• Used to maintain database integrity cant sell
  a product that isnt in the inventory, etc.

30
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)

31
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)

32
Converting an E-R Model to a Database Schema

• 1. Strong Entities become Relations
• Identify the primary key from the superkeys and
  candidate keys.

33
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.

34
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
  relationships entities primary keys. These are
  also foreign keys.

35
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
  entitys primary key.

36
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.

37
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.

38
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.)

39
Example

• Banking

40
Exercise

• Real Estate Office

41
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)

42
Exam Review

• DBMS - usage alternatives
• Methodologies and people
• E-R Modeling
• Entity (strong, weak, associative)
• Relationships (degree, cardinality, connectivity,
  existence)
• Gen/Spec