Good and Bad Database Design

by K. Yue

1. Introduction

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

Example:

Consider the relation

EMPLOYEE(EMP_NO, NAME, DEPT_NO, MANAGER_NO).

Assumptions:

• Every employee works for only one department.
• Every department has only one manager.
• Every manager manages only one department.

Problems:

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

Update Anomalies:

(a) 44444 is now the manager of department D123

• Inefficiency in update.
• Potential inconsistency.

(b) Bill Clinton now works for department D337:

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

The command:

UPDATE Employee
SET DEPT_NO = 'D337'
WHERE NAME = 'Bill Clinton';

will produce inconsistent result.

Insertion Anomalies:                                            

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

Deletion Anomalies: 

John Smithson 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 2 or more relations.

Example:

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.*, 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 anomalies.

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:

• EMP_NO is a positive integer less than 99999.
• Date of Birth should be greater than 1800.
• Part size must be greater than 0.
• Quantity should be greater than 0.
• If the supplier city is 'Houston', then the status of the supplier is greater than 20.

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

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 recorded.

Example:  

EMPLOYEE(EMP_NO, NAME, DEPT_NO, SALARY)

• EMPLOYEE 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

• 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 value of K in the relation where K is the candidate key.

Example 1:   

EMP(EMP_NO, NAME, DEPT_NO)

DEPARTMENT(DEPT_NO, MANAGER_NO)

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

EMP(EMP_NO, NAME, DEPT_NO)

DEPARTMENT(DEPT_NO, MANAGER_NO)

• The referential integrity rule is not satisfied.