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 110 manages department D123 is stored three times.

Update Anomaly:

(a) 415 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 520, currently with no employee is not possible.

Deletion Anomaly: 

John Smiths no longer works here. Result: the information that 333 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)

Relational algebra:

EMPLOYEE = EMP |x| DEPARTMENT

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:

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