Transforming ER model into Relational Database

1
Transforming ER model into Relational Database
2
Step 1

• Make relation schemes with the attributes of each
  entity sets
• Excluding weak entity sets and multi-valued
  attributes.

3
Example of step 1

• student(student, name, address, major,
  earned_credits)

Earned_credits
Student
address
major
name
student
4
Step 2

• Make a relation scheme with the primary key of
  strong entity set and attributes of weak entity
  set
• The primary key of the relation scheme is (the
  primary key of strong entity set) (the
  determinant of weak entity set)

5
Example of step 2

• Dependent_family(emp, name, relationship,
  birthday, sex, occupation)

6
Step 3

• Transformation of 11 relationship set
• When the relationship set has partial
  participation P and total participation T, insert
  the primary key of entity set with P into the
  relation scheme with T.
• When the relationship has same participation
  (partial or total), any direction of insertion of
  the related primary key is OK.
• Reason all entities belonging to T are affected
  by the relationship, so that we can avoid NULL
  values in the corresponding fields, if otherwise
  inserted.

7
Example of step 3

• employee(emp, name, entered date)
• department(dept, dept_name, location)
• ? department(dept, dept_name, location, emp)

Dept_name
location
Dept
Emp
1
1
employee
department
manage
8
Example of step 3(cont.)

• Whats meaning of emp in department(dept,
  dept_name, location, emp) ?
• question
• Whats problem, if we insert the primary key of
  entity set with total participation into relation
  scheme of partial participation?
• employee(emp, name, entered date,dept)
• Whats meaning of dept here?

9
Step 4

• Transformation of 1N relationship set
• Append the primary key of entity set at
  1-relationship to the relation scheme at
  N-relationship
• (ex)
• customer(cust, name, , telephone), and
• order(order, payment_method, order_date)
• One customer can order many times?1N
  relationship
• order(order, payment_method, order_date, cust)

10
Example of step 4

• For relation schemes
• customer(cust, name, , telephone), and
• order(order, payment_method, order_date)
• How about if we attach order to relation scheme
  customer(i.e., attach Many-sides P.K. to
  1-sides) ?
• ? customer(cust, name, , telephone, order)
• Whats the problem?

11
Step 5

• Transformation of NM relationship
• Make a new relation scheme with primary keys of
  related entity sets.
• Include the attributes of the relationship into
  the new relation scheme.

12
Example of step 5

• Order(order, payment_method, order_date)
• Product(product, product_name, assortment,
  standard, unit_price, total_stock)
• ?Order_product(order, product,
  ordered_quantity)
• See the next slide for an example ERD

13
Example of step 5(cont.)

• Order(order, payment_method, order_date)
• Product(product, product_name, assortment,
  standard, unit_price, total_stock)
• ? Order_product(order, product,
  ordered_quantity)

N
M
Order
Product
Ordered_quantity
14
Step 6

• Make a new relation scheme with multi-valued
  attribute and the primary key of the entity set
• (ex)
• employee(emp, name, address, hobbies)
• ? employee(emp, name, address),
  employee_hobby(emp, hobby)

15
Step 7

• Works for multiple(gt2) relationship
• Make a new relation scheme with the primary keys
  of related entity sets.
• (ex) register(student, course, professor)
• See the next slide for an example ERD

16
Example of step 7

• Make a new relation scheme with the primary keys
  of related entity sets.
• (ex) register(student, course, professor)

Course
course
Professor
Student
register
student
professor
17
Transformation of IS-A relationship

• Make a relation scheme for upper entity set
• For the lower entity sets, make relation schemes
  with
• (the primary key of upper entity set)
• (the attributes of lower entity sets)
• (ex)
• Upper entity set
• Employee(emp, name, address, entered_date)
• Lower entity sets
• Temporary_worker(emp, daily_allowance)
• Regular_worker(emp, salary)
• Contract_worker(emp, daily_allowance, contract)