Good and Bad Database Design

by K. Yue

1. Introduction

• Bad database designs:
  1. Unnecessary redundancy: inefficient storage
  2. Anomaly: conflicts in data, difficulties in maintenance.

Example:

Consider the relation

EMPLOYEE(EMP_NO, NAME, DEPT_NO, MANAGER_NO).

Assumptions made:

1. Every employee works for only one department.
2. Every department has only one manager.
3. Every manager manages only one department.
4. Every employee is represented as a tuple in the EMPLOYEE relation.
5. Every employee has an unique EMP_NO.

Thus, EMP_NO is a candidate key.

Problem:

• Unnecessary redundancy: the fact Manager 54321 manages department D123 is stored three times.

Update Anomaly:

(a) 44444 is the new manager of department D123

• Inefficiency in update.
• Potential inconsistency.

(b) Jennifer Lopez changes to working for department D337:

• Need to know the manager of D337.
• Potential inconsistency.

The command:

UPDATE Employee
SET DEPT_NO = 'D337'
WHERE NAME = 'Jennifer Lopez';

will produces inconsistent result.

Insertion Anomaly:                                            

Creating a new department D777, with manager 77111, currently with no employee is not possible.

Deletion Anomaly: 

John Smiths no longer works here. Result: the information that 33323 is the manager of department D337 is lost.

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)

DEPARTMENT(DEPT_NO, MANAGER_NO)

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)

SQL:           

SELECT EMP.*, DEPARTMENT.MANAGER_NO INTO EMPLOYEE
FROM EMP, DEPARTMENT
WHERE EMP.DEPT_NO = DEPARTMENT.DEPT_NO

1. There is no loss of information.
2. No previously mentioned redundancy and anomaly.

Methods for good designs

• Integrity Rules:  Constraints for avoiding impossible configurations.
• Normal Forms:  A set of rules for designing good relation schemes.

Integrity Rules:

• Most of the integrity rules are application-dependent.
• Need to analyze the semantic of the applications to find out the integrity rules.
• These are known as Database-Specific Integrity Rules.

Examples:

1. Student Id should be a seven digit number
2. Date of Birth should be greater than 1900.
3. The room number of Delta Building should start with a 'D'.
4. A student cannot take more than 24 credits in any semester.
5. A student must show proof of meningitis shot before registration for the first semester.

General Integrity Rules:

• Should be satisfied by every database, but not necessarily enforced by every DBMS package.
• Entity Integrity Rule and Referential Integrity Rule: based on the concepts of primary keys, superkeys, and foreign keys

Entity Integrity Rule

• Entity Integrity: no component of a candidate key of a relation can have a null value.
• Meaning: In a relational database, a tuple that cannot be identified will not be stored.

Example:  

EMPLOYEE(EMP_NO, NAME, DEPT_NO, SALARY)

• If EMP_NO is a candidate key, this EMPLOYEE instance does not satisfy the entity integrity rule.
• If we accept the relation instance above as valid, then EMP_NO cannot be a candidate key.

Referential Integrity Rule

• Referential integrity rule: the database does not contain any unmatched non-null foreign key values.
• Any non-null value of a foreign key K must appear in the base (host) relation where K is a candidate key.

Example:   

EMP(EMP_NO, NAME, DEPT_NO)

DEPARTMENT(DEPT_NO, MANAGER_NO)

• DEPT_NO is a foreign key in EMP, referencing DEPT_NO in DEPARTMENT.
• The referential integrity rule is satisfied.
• Note that DEPT_NO may be null in EMP.

Example:   

EMP(EMP_NO, NAME, DEPT_NO)

DEPARTMENT(DEPT_NO, MANAGER_NO)

• The referential integrity rule is not satisfied.

Note:

• In practical DBMS, pay attention when the referential integrity rule is enforced.
• For example, in MySQL, only the INNODB data engine may enforce the referential integrity rule.
• If the DBMS does not enforce the referential integrity rule, it will be the task of the DB developer to do so.