Good and Bad Database Design
by K. Yue
1. Introduction
Example:
Consider the relation
EMPLOYEE(EMP_NO, NAME, DEPT_NO, MANAGER_NO).
Assumptions made:
Thus, EMP_NO is a candidate key.
EMP_NO | NAME | DEPT_NO | MANAGER_NO |
101 | Lady Gaga | D123 | 110 |
122 | Brad Pitts | D123 | 110 |
140 | Lebron James | D123 | 110 |
155 | Narendra Modi | D225 | 205 |
167 | Jennifer Lopez | D225 | 205 |
311 | John Smiths | D337 | 333 |
Problem:
Update Anomaly:
(a) 415 is the new manager of department D123
EMP_NO | NAME | DEPT_NO | MANAGER_NO |
101 | Lady Gaga | D123 | 415 |
122 | Brad Pitts | D123 | 415 |
140 | Lebron James | D123 | 110 if not updated |
155 | Narendra Modi | D225 | 205 |
167 | Jennifer Lopez | D225 | 205 |
311 | John Smiths | D337 | 333 |
SQL: it works but not efficient.
UPDATE employee
SET MANAGER_NO = 415
WHERE DEPT_NO = 'D123';
(b) Jennifer Lopez changes to working for department D337:
EMP_NO | NAME | DEPT_NO | MANAGER_NO |
101 | Lady Gaga | D123 | 110 |
122 | Brad Pitts | D123 | 110 |
140 | Lebron James | D123 | 110 |
155 | Narendra Modi | D225 | 205 |
167 | Jennifer Lopez | D337 | 205 if not updated |
311 | John Smiths | D337 | 333 |
The command:
UPDATE Employee
SET DEPT_NO = 'D337'
WHERE NAME = 'Jennifer Lopez';
will produces inconsistent result.
You need to update both DEPT_NO and MANAGER_NO. However,
UPDATE Employee
SET DEPT_NO = 'D337',
MANAGER_NO = (SELECT DISTINCT MANAGER_NO FROM Employee WHERE DEPT_NO = 'D337')
WHERE NAME = 'Jennifer Lopez';
will not work in MySQL as one cannot include a SELECT clause on the same table in the SET clause.
Insertion Anomaly:
Creating a new department D777, with manager 520, currently with no employee is not possible.
EMP_NO | NAME | DEPT_NO | MANAGER_NO |
101 | Lady Gaga | D123 | 110 |
122 | Brad Pitts | D123 | 110 |
140 | Lebron James | D123 | 110 |
155 | Narendra Modi | D225 | 205 |
167 | Jennifer Lopez | D225 | 205 |
311 | John Smiths | D337 | 333 |
???? | ???? | D777 | 520 (cannot be added) |
Deletion Anomaly:
John Smiths no longer works here. Result: the information that 333 is the manager of department D337 is lost.
EMP_NO | NAME | DEPT_NO | MANAGER_NO |
101 | Lady Gaga | D123 | 110 |
122 | Brad Pitts | D123 | 110 |
140 | Lebron James | D123 | 110 |
155 | Narendra Modi | D225 | 205 |
167 | Jennifer Lopez | D225 | 205 |
A standard way of resolving these issues are by proper decomposition: breaking down a relation into two or more relations.
Example: decomposition into two relations:
EMP(EMP_NO, NAME, DEPT_NO)
EMP_NO | NAME | DEPT_NO |
101 | Lady Gaga | D123 |
122 | Brad Pitts | D123 |
140 | Lebron James | D123 |
155 | Narendra Modi | D225 |
167 | Jennifer Lopez | D225 |
311 | John Smiths | D337 |
DEPARTMENT(DEPT_NO, MANAGER_NO)
DEPT_NO | MANAGER_NO |
D123 | 110 |
D225 | 205 |
D337 | 333 |
To obtain the original relation EMPLOYEE(EMP_NO, NAME, DEPT_NO, MANAGER_NO) from
EMP(EMP_NO, NAME, DEPT_NO)
DEPARTMENT(DEPT_NO, MANAGER_NO)
Relational algebra:
EMPLOYEE = EMP |x| DEPARTMENT
SQL:
SELECT EMP.*, DEPARTMENT.MANAGER_NO INTO EMPLOYEE
FROM EMP, DEPARTMENT
WHERE EMP.DEPT_NO = DEPARTMENT.DEPT_NO;
Methods for good designs
Integrity Rules:
Examples:
General Integrity Rules:
Entity Integrity Rule
Example:
EMPLOYEE(EMP_NO, NAME, DEPT_NO, SALARY)
EMP_NO | NAME | DEPT_NO | SALARY |
101 | Lady Gaga | D123 | 550000000 |
122 | Brad Pitts | D123 | 101000 |
140 | Lebron James | D123 | 50000000 |
155 | Narendra Modi | @ | @ |
@ | Jennifer Lopez | D225 | 2000000 (cannot be added) |
@ | John Smiths | D337 | 70000 (cannot be added) |
Referential Integrity Rule
Example:
EMP(EMP_NO, NAME, DEPT_NO)
EMP_NO | NAME | DEPT_NO |
101 | Lady Gaga | D123 |
122 | Brad Pitts | D123 |
140 | Lebron James | @ |
155 | Narendra Modi | D225 |
167 | Jennifer Lopez | D225 |
311 | John Smiths | D337 |
DEPARTMENT(DEPT_NO, MANAGER_NO)
DEPT_NO | MANAGER_NO |
D123 | 110 |
D225 | 205 |
D337 | 333 |
Example:
EMP(EMP_NO, NAME, DEPT_NO)
EMP_NO | NAME | DEPT_NO |
101 | Lady Gaga | D123 |
122 | Brad Pitts | D123 |
140 | Lebron James | D123 |
155 | Narendra Modi | D225 |
167 | Jennifer Lopez | D225 |
311 | John Smiths | D337 |
350 | Bun Yue | D119 (may not be added) |
DEPARTMENT(DEPT_NO, MANAGER_NO)
DEPT_NO | MANAGER_NO |
D123 | 110 |
D225 | 205 |
D337 | 333 |
Note: