Introduction to MIS Databases

1
Introduction to MIS Databases
From ER Diagrams to Database Tables Mapping ER
Diagrams to Tables
2
Designing the Tables

• The approach
• Example data to be captured
• Create ER Diagram. Each statement is a single
  path through the diagram
• Tables are formed by traversing the diagram

3
Example ER Diagram
Fname
Lname
Number
Name
N
1
Gender
Locations
WORKS_FOR
Salary
Name
Address
DEPARTMENT
StartDate
Snn
EMPLOYEE
1
1
MANAGES
Birthdate
CONTROLS
Hours
N
N
1
WORKS_ON
PROJECT
1
N
SUPERVISION
DEPENDENTS_OF
Location
Name
N
Number
DEPENDENT
Name
Gender
Birthdate
4
Review Constraints
budget
did
Departments
Works_In

• An employee can work in many departments a
  dept can have many employees. In contrast, each
  dept has at most one manager.

5
Review Participation Constraints

• Does every employee work in a department?
• If so, this is a participation constraint
• Basically means at least one

since
since
name
dname
name
dname
ssn
lot
budget
did
budget
did
Departments
Employees
Manages
Works_In
since
6
Identify Weak Entities

• A weak entity can be identified uniquely only by
  considering the primary key of another (owner)
  entity.
• Owner entity set and weak entity set must
  participate in a one-to-many relationship set
  (one owner, many weak entities).

7
Review Relationships
qty
Departments
Parts
Contract
VS.
Suppliers
Parts
Departments
needs
can-supply
deals-with
Suppliers

• S can-supply P, D needs P, and D
  deals-with S does not imply that D has agreed
  to buy P from S.

8
How do you make the Tables?
Fname
Lname
Number
Name
N
1
Gender
Locations
WORKS_FOR
Salary
Name
Address
DEPARTMENT
StartDate
Snn
EMPLOYEE
1
1
MANAGES
Birthdate
CONTROLS
Hours
N
N
1
WORKS_ON
PROJECT
1
N
SUPERVISION
DEPENDENTS_OF
Location
Name
N
Number
DEPENDENT
Name
Gender
Birthdate
9
Each Entity becomes a Table
ENTITY TABLE PRIMARY
KEY Employee Employee Employee_SSN Departme
nt Department Department_Number Project
Project Project Number
10
1N Relationships - Create the Primary Key
For each 1N relationship create the Primary
Key from the entity on the 1 side to the entity
on the N side where it will become a foreign
key. EMPLOYEE WORKS_FOR DEPARTMENT
N
1 Department Number is moved into the
Employee entity
11
Weak Entities Become Tables as Well
For each weak entity create a table - include
the Primary Key of the owner tables The Primary
Key becomes the owner key plus the
weak entity key Entity Table
Primary Key Dependent
Dependent Employee_SSN

Dependent_No
12
What about MN Relationships?
For each MN relationship create a new table with
the Primary Key being the the Primary Key of
both entities involved in the relationship EMPLOY
EE WORKS_ON PROJECT Empno
Hours
Projno
E1 P1 3 E1 P2
4 E2 P1 5 E3 P2
3
13
Representing the MN Relationships
14
The Finished Table Design
EMPLOYEE
Fname Lname Essn Bdate Address M/F
Salary Superssn Dno
p.k
DEPARTMENT
f.k
Dname Dnumber
Mgrssn Mgrstartdate
p.k
DEPT_LOCATIONS
Dnumber Dlocation
PROJECT
WORKS_ON
Pname Pnumber Plocation Dnumber
f.k
f.k
Essn Pnumber Hours
DEPENDENT
f.k
NOT NULL
NOT NULL
Essn Dependent_name M/F Bdate