In this Assignment, you will use a dependency diagram to assist with normalizing your class project database into 3rdnormal form. Additionally, example structure and sample data will need to be created and included.
Part 1: Create Dependency Diagram and Complete Database Design
Dependency Diagram
Based on the dependency diagram below, create an ERD in 3NF.
Figure: see image.
Correct and Complete the Database Design
In the following parts of the Assignment, you will redo your business rules and ERD. . You will then assert that your database is normalized to 3NF. You will build a data dictionary and then provide sample data.
Note: The purpose statement of your database need not be repeated.
Part 2: Database Design Update and Normalization Assertion
Update your business rules and ERD. Show and explain the changes. Ensure the database is normalized to 3NF. Provide the updated business rules and the final ERD for your database.
Assert that the database is normalized to 3NF. Use the format given here, substituting rules and what is checked for in place of the items in angle brackets.
Normalization:
1NF - < The three rules for 1NF should be stated and it should be asserted that these are met by all tables.>
2NF - No < state what is checked for> exist in any of the tables.
3NF - No < state what is checked for> exist in any of the relations.
Part 3: Database Documentation
Define the purpose of each table and provide a data dictionary.
Data dictionary
State the table name and the purpose of the table. Then, in a Microsoft Word table (one Word table for each of your tables; give the fields, description of each field, data type of each field (including length for character or decimal fields), whether or not each field allows NULL values, and whether the field is a primary key (or part of a primary key) or is a foreign key. Following is a sample part of a data dictionary for an Apartment table.
Apartment - Describes the apartment.
| Field | Description | Data Type | Allow Nulls | Key |
| ApartmentID | Apartment identifier | Char(1) | No | Primary Key |
| NumberBedrooms | Number of bedrooms | Integer | No | Not a key |
| NumberFloors | Number of floors | Integer | No | Not a key |
| NumberBaths | Number of bathrooms | Decimal(3, 2) | No | Not a key |
| GarageSpace | Which garage space goes with the apartment | Varchar(20) | Yes | Not a key |
Part 4: Create Sample Data
Convert the schema into tables within a Word document. Each table should have at least 5 tuples/rows but no more than 10 tuples/rows of example data. Take care that your sample data in each column match the data type specified for that column in your data dictionary. Please ensure that any foreign key values match an existing referenced value.
An example of sample data for a table:
Apartment
| ApartmentID | NumberBedrooms | NumberFloors | NumberBaths | GarageSpace |
| A | 3 | 1 | 1.75 | West Detached |
| B | 2 | 2 | 1.5 | Middle Detached |
| C | 2 | 2 | 1.5 | Middle Detached |
| D | 2 | 2 | 1.5 | Under Building |
| E | 3 | 2 | 1.75 | East Detached |