Database System Design for Burn 24 Hour Challenge Company

1Running head: DATABASE SYSTEM DESIGN
Database System design
Burn 24 Hour Challenge Company Database System
[Student Name]
[University Name]

2DATABASE SYSTEM DESIGN
Table of Contents
1 Fan trap................................................................................................................................................3
2 Chasm trap...........................................................................................................................................3
3 Redundant relationship and how to visually spot them........................................................................4
4 1NF,2NF and 3NF...............................................................................................................................4
4.1 1NF normalization.......................................................................................................................5
4.2 2NF normalization.......................................................................................................................6
4.3 3NF normalization.......................................................................................................................7
5 Diagram showing super type and subtype...........................................................................................9
6 Table diagram for subtype implementation.........................................................................................9
7 Database design specifications..........................................................................................................10
7.1 Table and attributes....................................................................................................................10
7.2 Primary keys and foreign keys...................................................................................................11
7.3 Relationships.............................................................................................................................12
8 Sample records..................................................................................................................................12
Reference...................................................................................................................................................16

3DATABASE SYSTEM DESIGN
1 Fan trap
The Fan trap is an issue in the data modelling which acts as the join path problem that is between
three tables which uses the “one-to –many” joins to link a table that is then linked by another
“one-to –many” joins.
It is called fan trap since it have the fanning effects of “one-to –many” joins that as a result
causes in accurate results because of the duplicates that are to be returned when the queries
include the objects that are in two tables.
Below is the representation of the Fan trap
2 Chasm trap
The chasm trap is an issue in the data modelling that occur in case two “many-to-one “ joins
converges onto one table ,however the queries include the measures that are obtained from the
leaf tables.
It is referred to as the chasm trap since the convergence of the “many-to-one” joins results to
returning of multiple rows from the linked tables when the queries are processed.
Below is an example of chasm trap model.

4DATABASE SYSTEM DESIGN
3 Redundant relationship and how to visually spot them
A redundant relationship is type of entities relationships that has pairings that can be easily be
derived from any other relationship.
The relationship can be visually spotted through the observation of the relationship optionality
that defines the entire relationship also the relationship redundancy can be spotted through the
repetition of similar attributes in the various entities in the relationship.
4 1NF,2NF and 3NF
The database normalization is a designing technique that is used in the table’s organization in the
database in order to reduce the data redundancy and dependency this is achieved through the
division of larger database tables into smaller tables and then linking them through the
relationship using foreign key.
The database can be normalized in three main normal forms which include 1NF, 2NF, and 3NF
and this is going to be illustrated using the movie rent record below which is not normalized.