With our knowledge of normalization, we are now going to put our skills to work with us
Question:
With our knowledge of normalization, we are now going to put our skills to work with us by examining a dataset and breaking it up using the Normalization process. You have been hired by a new concert promoter to build a database to hold information for their concerts. This promoter doesn't understand anything about databases, they are counting on you to build the structure up for the long-term. Instead of building databases right away, you will be using Normalization to translate raw concert data into the 3 Normal Forms.
Evaluation Steps:
As a reminder, each section requires a title that outlines which Normal Form is being presented.
Section #1: Normal Form #1
- Reviewing the "Assignment 1 - Part 1 - Raw Data.xlsx" Excel file and create Normal Form #1
- Hint: Normal Form #1 is used to divide the raw data into tables. In this step, there should only be two tables for Normal Form #1. Do not connect the tables together at this time
- Reminders: tables will need you to create Primary Key column for each table and choose the correct Data Types (cannot be string or numeric, it needs to be the actual data type e.g. VARCHAR)
- Primary Key columns will need a (PK) next to the column name E.g ConcertID (PK)
- Each table needs an appropriate name, this should refer to what is being stored in it
Section #2: Normal Form #2
- Now separate your data further and build Normal Form #2
- Hint: Normal Form #2 is used to take data based on an identifier and separate it into another table. In this step, there should be 3 tables in total for Normal Form #2.
- Create the Relationships between the tables to connect the Primary and Foreign Keys. This means you will be adding Foreign keys to connect the tables together
- Hint: Primary Key columns will need a (PK) next to the column name (E.g ConcertID (PK)) and Foreign Keys will need a (FK) next to the column name
- Reminders: tables will need a Primary Keys, Foreign Keys and Data Types (cannot be string or numeric, it needs to be the actual data type e.g. VARCHAR)
- Each table needs an appropriate name, this should refer to what is being stored in it
Section #3: Normal Form #3
- Now separate your data further and build Normal Form #3
- Hint: Normal Form #3 is used to remove data from tables that are not dependant on an identifier. In short, these are lookup tables for values that repeat - look in the data and see if you can find column values that repeat. In this step, there should be at least 4 tables in total for Normal Form #3.
- Create the Relationships between the tables to connect the Primary and Foreign Keys (for the new table(s))
- Hint: Primary Key columns will need a (PK) next to the column name (E.g ConcertID (PK)) and Foreign Keys will need a (FK) next to the column name
- Reminders: tables will need a Primary Keys, Foreign Keys and Data Types (cannot be string or numeric, it needs to be the actual data type e.g. VARCHAR)
- Each table needs an appropriate name, this should refer to what is being stored in it
DATA-
FirstName | LastName | DOB | FavouriteArtist | FavouriteGenre | Venue | TicketPrice | ConcertDateAndTime | AgeGroup | Artist | Genre |
Robert | Smith | 2000-09-09 | Harry Styles | Pop | Center in the Square | 95 | 2022-08-30 20:00 | All Ages | Justin Bieber | Pop |
Jane | Tomas | 1967-01-02 | Neil Young | Folk | Exhibition Place | 102 | 2022-06-13 19:00 | All Ages | Neil Young | Folk |
Hardik | Singh | 1999-04-26 | K-OS | Rap | Velvet Underground | 80 | 2022-04-12 20:00 | Youth | Drake | Rap |
Hardik | Singh | 1999-04-26 | K-OS | Rap | Velvet Underground | 80 | 2022-04-12 20:00 | Youth | Cardi B | Rap |
Hardik | Singh | 1999-04-26 | K-OS | Rap | The Opera House | 120 | 2022-04-16 21:00 | 18+ | Eminem | Rap |
Cameron | Ronald | 1985-06-09 | Nine Inch Nails | Rock | Scotiabank Arena | 75 | 2022-05-18 19:30 | 18+ | Nine Inch Nails | Industrial |
Richard | Richardson | 1978-10-05 | Ed Sheeran | Pop | Scotiabank Arena | 100 | 2022-05-25 13:00 | All Ages | Pharrell Williams | Pop |
Hardik | Singh | 1999-04-26 | K-OS | Rap | Rogers Centre | 105 | 2022-10-22 20:30 | 18+ | Megan Three Stallion | Rap |
Robert | Smith | 2000-09-09 | Harry Styles | Pop | The Opera House | 99 | 2022-06-30 19:00 | All Ages | Ed Sheeran | Pop |
Jennifer | Spenz | 1989-12-12 | Outkast | Rap | Velvet Underground | 120 | 2022-08-01 21:00 | Youth | Doja Cat | Rap |
Jennifer | Spenz | 1989-12-12 | Outkast | Rap | Velvet Underground | 80 | 2022-04-12 20:00 | Youth | Drake | Rap |
Robert | Smith | 2000-09-09 | Harry Styles | Pop | Scotiabank Arena | 100 | 2022-05-25 13:00 | All Ages | Pharrell Williams | Pop |
Foundations of Financial Management
ISBN: 978-1259024979
10th Canadian edition
Authors: Stanley Block, Geoffrey Hirt, Bartley Danielsen, Doug Short, Michael Perretta