Lecture Notes

The Relational Data Model, Normalisation and effective Database Design

14 years 1 months ago
The Relational Data Model, Normalisation and effective Database Design
I have been designing and building applications, including the databases used by those applications, for several decades now. I have seen similar problems approached by different designs, and this has given me the opportunity to evaluate the effectiveness of one design over another in providing solutions to those problems. It may not seem obvious to a lot of people, but the design of the database is the heart of any system. If the design is wrong then the whole application will be wrong, either in effectiveness or performance, or even both. No amount of clever coding can compensate for a bad database design. Sometimes when building an application I may encounter a problem which can only be solved effectively by changing the database rather than by changing the code, so change the database is what I do. I may have to try several different designs before I find one that provides the most benefits and the least number of disadvantages, but that is what prototyping is all about. The ...
Tony Marston
Added 17 Feb 2009
Updated 23 May 2009
Year 2005
Authors Tony Marston

Table of Content

What is a database?
The Hierarchical Data Model
The Network Data Model
The Relational Data Model
- The Relation
- Keys
- Relationships
- Relational Joins
- Lossless Joins
- Determinant and Dependent
- Functional Dependencies (FD)
- Transitive Dependencies (TD)
- Multi-Valued Dependencies (MVD)
- Join Dependencies (JD)
- Modification Anomalies
Types of Relational Join
- Inner Join
- Natural Join
- Left [Outer] Join
- Right [Outer] Join
- Full [Outer] Join
- Self Join
- Cross Join
Entity-Relationship Diagram (ERD)
Data Normalisation
- 1st Normal Form
- 2nd Normal Form
- 3rd Normal Form
- Boyce-Codd Normal Form
- 4th Normal Form
- 5th (Projection-Join) Normal Form
- 6th (Domain-Key) Normal Form
- Compound Fields
- Summary Fields
- Summary Tables
- Optional Attributes that exist as a group
Personal Guidelines
- Database Names
- Table Names
- Field Names
- Primary Keys
- Foreign Keys
- Generating Unique ids
- The choice between upper and lower case
- Field names should identify their content
- The naming of Foreign Keys
Amendment History
Comments (0)