Introduction to Database Design

K. Yue

1. Introduction

• Bad database/table designs result in unnecessary redundancy: redundancy with limited or no benefit.
• However, redundancy can serve many purposes, and are employed frequently in computer science.
• Problems:
  1. Inefficient storage
  2. Anomaly: data inconsistency, loss of data integrity, difficulties in maintenance.

Example:

Consider the vastly simplified and poorly-designed relation/table:

Employee_Bad(EmpId, Name, DeptId, MailCode).

Assumptions made:

1. Every employee has an unique EmpId.
2. Every employee is represented as a tuple in the Employee relation.
3. Every employee works for only one department.
4. Every department has an unique DeptId.
5. Every department has exactly one mail code, stored in the field MailCode.

Thus, EmpId is a candidate key (CK). An instance of Employee_Bad:

Problem:

• Unnecessary redundancy: e.g., the fact that the mail code M10 for Department D123 is stored in three rows.

1.1 Update Anomaly:

(a) The mail code of department D123 is updated to M44.

• Inefficiency in update: needs to update all three rows in this example.
• Potential (logical data) inconsistency.

SQL solution: it works but is not efficient.

UPDATE Employee
SET MailCode = 'M44'
WHERE DeptId = 'D123';

(b) Jennifer Lopez is reassigned to work for Department D300:

• Need to know the mail code of D300.
• Potential inconsistency.

The table may become:

The intuitive SQL command:

UPDATE Employee
SET DeptId = 'D300'
WHERE Name = 'Jennifer Lopez';

will produce inconsistent results, as shown in the table above.

One needs to update both DeptId and MailCode. However,

UPDATE Employee
SET DeptId = 'D300',
     MailCode = (SELECT DISTINCT MailCode FROM Employee WHERE DeptId = 'D300')
WHERE Name = 'Jennifer Lopez';

will not work in MySQL as one cannot include a SELECT clause on the same table in the SET clause of an UPDATE statement.

A possible solution using a session variable, @mailCode:

SELECT DISTINCT MailCode INTO @mailCode
FROM Employee WHERE DeptId = 'D300';

UPDATE Employee
SET DeptId = 'D300',
     MailCode = @mailCode
WHERE Name = 'Jennifer Lopez';

1.2 Insertion Anomaly:                                            

It is not possible creating a new Department D777, with the mail code M40 but no employee working for it yet. This is because, as the PK of Employee_Bad, EmpId cannot be null.

1.3 Deletion Anomaly

John Smiths no longer works here. Result: the information that M33 is the mail code of Department D300 is also lost.

1.4 Decomposition

A standard way of resolving unnecessary redundancy in poorly designed tables is by proper decomposition: breaking down a relation into two or more component relations.

Example: the decomposition of the relation Employee_Bad into two relations:

1. Empolyee(EmpId, Name, DeptId)

2. Department(DeptId, MailCode)

To obtain the original relation Employee_Bad(EmpId, Name, DeptId, MailCode) from

Employee(EmpId, Name, DeptId)
Department(DeptId, MailCode)

Relational algebra: using natural join, |x|.

Employee_Bad = Employee |x| Department

This decomposition is said to be a lossless decomposition.

SQL:           

SELECT Employee.*, Department.MailCode
FROM Employee INNER JOIN Department ON (Employee.DeptId = Department.DeptId);

or

SELECT *
FROM Employee NATURAL JOIN Department;

1. There is no loss of information: the definition of lossless decomposition.
2. No previously mentioned unnecessary redundancy and anomaly.

The actions of the four use cases that produced anomaly in Employee_Bad:

1. Empolyee(EmpId, Name, DeptId)

2. Department(DeptId, MailCode)

2. Methods for good database designs

Two main tools:

1. Integrity Rules:  data constraint rules for avoiding data inconsistency.
2. Normal Forms:  a set of rules for designing good relation schemas.

3. Integrity Rules

3.1 Database-Specific Integrity Rules

• Most of the integrity rules are application dependent.
• Need to analyze the semantics of the applications to find out the integrity rules.
• These are known as Database-Specific Integrity Rules, or Application-Specific Integrity Rules.
  • Specific to an application
  • Not universally applicable.

Examples: some database-specific integrity rules.

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 a meningitis shot before registration for the first semester.

3.2 General Integrity Rules

• They should be satisfied by every database.
• However, they are not necessarily enforced by the DBMS.
• Two general integrity rules in relational databases:
  1. Entity Integrity Rule: based on the concepts of primary keys (PK) and candidate keys (CK)
  2. Referential Integrity Rule: based on the concept of foreign keys (FK).

3.2.1 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 row that cannot be identified by ites CK will not be stored.

Example: 

Employee(EmpId, Name, DeptId, Salary)

• If EmpId is a candidate key, this Employee instance does not satisfy the entity integrity rule.
• Conversely, if we accept the relation instance above as valid, EmpId cannot be a candidate key.
• Most DBMS enforce the entity integrity rule. DB developers just need to declare the primary keys (using PRIMARY) and the candidate key (using UNIQUE and not NULL) in their CREATE TABLE statements.

3.2.2 Referential Integrity Rule

• Referential integrity rule: relations should not contain any unmatched non-null foreign key values.
• Any non-null value of a foreign key K must appear in the parent (referenced) relation where K is a candidate key.

Example:   

Employee(EmpId, Name, DeptId)

Department(DeptId, MailCode)

• DeptId is a foreign key in the table EMP, referencing DeptId in the table Department. Employee(DeptId) references Department(DeptId).
• The referential integrity rule is satisfied.
• Note that DeptId may be null in Employee.

Example:   

Employee(EmpId, Name, DeptId)

Department(DeptId, MailCode)

• The referential integrity rule is not satisfied in the example above.

Note:

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