Relational Database Design Overview

Study Notes

Bottom-up (Design by Synthesis): Starts with basic relationships among individual attributes
- Not commonly used due to complexity
Top-down (Design by Analysis): Begins with natural groupings of attributes and refines them
- More practical and widely applicable
- Useful for decomposing real-world forms and reports

Good relational schemas ensure logical grouping of attributes, clear user interpretation, and efficient physical storage
Poor design may lead to redundancy and data anomalies

A formal constraint among attributes used to assess the appropriateness of attribute groupings

Successive normal forms (e.g., 1NF, 2NF, 3NF) are defined based on primary keys and functional dependencies
Relations are decomposed as needed to meet normal forms

These guidelines help assess and improve the quality of a relational schema
- Clear Semantics of Attributes: Ensure the meaning of each attribute is well-defined and understood within the schema
- Reducing Redundant Information: Minimize the repetition of data across tuples
- Reducing NULL Values: Design schemas that minimize NULL values
- Avoiding Spurious Tuples: Ensure the schema design prevents the generation of meaningless or incorrect tuples when performing join operations

The meaning of a relation schema is derived from the interpretation of the attribute values in its tuples
- A well-designed schema should have a clear, unambiguous meaning

EMP_DEPT: Combines employee information with department information, leading to mixed semantics
EMP_PROJ: Combines employee information with project information and the WORKS_ON relationship, creating semantic confusion
- These schemas may be logical but are problematic as base relations due to ambiguous semantics
- They might be more appropriate as views rather than base relations

FD is a property of the relation schema: It is derived from the semantics of the attributes, not just the data
Legal Relation States: Extensions of a relation r(R) that satisfy FD constraints are legal relation states
FDs cannot be directly inferred from relation data: They are defined based on the semantic understanding of the attributes
Counterexample: If two tuples have the same value for X but different values for Y, then X → Y does not hold
FD in Practice: Designers define FDs based on attribute relationships and meaning
- This helps maintain consistency, eliminate redundancy, and reduce anomalies in database design

Functional dependencies are used to develop a formal methodology for testing and improving relation schemas
Each relation has a set of functional dependencies and a designated primary key
This information, combined with normal form conditions, drives the normalization process for schema design

Normalization evaluates a relation schema against normal form criteria and decomposes relations if necessary
It is a top-down, relational design process by analysis
Focuses on First Three Normal Forms (1NF, 2NF, 3NF)
- First Normal Form (1NF): Ensures all values are atomic and each relation has a primary key
- Second Normal Form (2NF): Eliminates partial dependencies (non-key attributes must depend on the entire primary key)
- Third Normal Form (3NF): Removes transitive dependencies (non-key attributes must depend only on the primary key)

Key Normal Forms:
- First, Second, and Third Normal Forms (1NF, 2NF, 3NF): Initially proposed by Codd
- Boyce-Codd Normal Form (BCNF): A stronger version of 3NF by Boyce and Codd

Example - EMP_PROJ Relation:
- Ename depends on Ssn (partial dependency)
- Pname and Plocation depend on Pnumber (partial dependency)
- Hours fully depends on {Ssn, Pnumber} (full dependency)
This means the relation is in 1NF but not in 2NF due to partial dependencies of Ename, Pname, and Plocation

To achieve 2NF, decompose the relation into smaller relations where each nonprime attribute is fully dependent on its corresponding part of the primary key
For the EMP_PROJ relation, it decomposes into three relations:
- EP1(Ssn, Ename)
- EP2(Pnumber, Pname, Plocation)
- EP3(Ssn, Pnumber, Hours)

A relation schema is in 3NF if:
- It satisfies 2NF
- All non-key attributes are dependent on the primary key
- There are no transitive dependencies

If a relation is in 2NF but not in 3NF, it can be decomposed into smaller relations to satisfy 3NF

A relation schema is in BCNF if:
- It satisfies 3NF
- All determinants (attributes that determine other attributes) are candidate keys

There are additional normal forms beyond 3NF and BCNF, but they are less commonly used

Normalization is a crucial step in relational database design, ensuring schema quality and data integrity
Understanding functional dependencies and normalization principles empowers you to design and maintain efficient and reliable database systems.