DATABASE BASICS

1
Example
2
ER Diagram Normalisation

• An employee can teach many course, and a course
  can be taught by 1 employee only (One-to-Many
  relationship)?

Un-Normalised 1st Normalisation 2nd Normalisation 3rd Normalisation
Emp_ID Table 1 - EMPLOYEE
Name Emp_ID
Dept Name
Salary Dept
Course Salary
Date_Completed Table 2 - COURSE
Emp_ID
Course
Date_Completed

3
ER Diagram Normalisation
M
M
EMPLOYEE
COURSE
1
1

• An employee can teach many course, and a course
  can be taught by many employees (Many-to-Many
  relationship)?

Un-Normalised 1st Normalisation 2nd Normalisation 3rd Normalisation
Emp_ID Table 1 - EMPLOYEE Table 1 - EMPLOYEE Table 1 - EMPLOYEE
Name Emp_ID Emp_ID Table 2 - COURSE
Dept Name Name CourseNo
Salary Dept Dept CourseTitle
Course Salary Salary Description
Date_Completed Table 2 - COURSE Table 2 - COURSE Duration
Emp_ID Course Table 3 ENROL
Course Date_Completed Emp_ID
Date_Completed Table 3 ENROL CourseNo
Emp_ID Date Completed
Course
4
Exercise 1
5
Exercise 1 Business Rules ER Diagram

• Business Rules / Assumptions
• ER Diagram

6
Exercise 1 - Normalisation
Un-Normalised 1st Normalisation 2nd Normalisation 3rd Normalisation











7
Exercise 2
8
Exercise 2
9
Exercise 2 Business Rules ER Diagram

• Business Rules / Assumptions
• ER Diagram

10
Exercise 2 - Normalisation
Un-Normalised 1st Normalisation 2nd Normalisation 3rd Normalisation