Project Part 3 Database Normalization SQL

In this Project, you will normalize the LetsMeet data. In particular, you will eliminate redundant columns by looking for tables that fail to meet Third Normal Form (3NF). Recall that a 3NF table should not have any transitive dependencies. Generally speaking, all non-key columns should be dependent on "the key, the whole key, and nothing but the key." The LetsMeet data is in your schema. When you look at the navigator on the left side of MySQL Workbench, your schema should be bold. If it's not bold, double click on your schema. Note that you will submit all of the code you write within the SQL platform for grading. Be sure to stop every now and then to save your work. This will ensure that it isn't lost.

STEP 1: Find Redundant Columns A. Generate the LetsMeet ERD to view all the tables and columns. Scan the tables and columns to identify potential data redundancies. ERD How-to Reminder Select Reverse Engineer under the Database tab. Select your schema and keep pressing Next. Then press Execute and Finish to close the window and view the ERD. You will know that a column is redundant if: the same information exists in another table for columns that are not PRIMARY or FOREIGN KEYs. the information does not provide any value or is irrelevant to the table itself. For example, if you are looking at the venue_ table, you might consider the following columns redundant: the localized_country_name column, because it exists in the city table already. the RAND() column because it does not provide any useful information about the venues. While you have the ERD open, use it to identify the redundant columns in each table in the Knowledge Check below. INSTRUCTIONS QUESTION 1/6 Redundant Columns Identify the redundant columns in each table in the letsmeet database.

START STEP 2: Eliminate Redundant Columns After completing the previous Knowledge Check, you can see that the letsmeet tables contain quite a few redundant columns. In this step you will remove some columns you identified. You don't need to eliminate all the columns you identified, but should eliminate at least five columns from the letsmeet database. You should write a SQL statement for each column that you want to remove. Underneath each of the statements used to remove the columns, you should use a multi-line comment to state which column you are removing and why. To write comments across multiple lines, write /* to open a comment. Any text you write after it will be a comment, and it will not execute in SQL when the code is run. user types /* to open a comment.To close your comment and return to regular SQL, write */ at the end. user types */ at the end to close a comment Be sure to click the Wrap Text button in the top toolbar before you write insights. This way, all of your comments will wrap around the screen and be visible to your instructor. icons If you choose to remove the RAND() column from the venue_ table, you will need to enclose it using backquotes like in the following snippet. This is because RAND() is actually a SQL function that returns a random number. So unless it is enclosed in backquotes, you will not be able to drop it. DROP COLUMN `RAND()`; ALTER TABLE DROP ALTER TABLE table_name ADD COLUMN column_name datatype; ALTER TABLE table_name DROP COLUMN column_name;

STEP 3: Split grp_member Table The grp_member table does not have a PRIMARY KEY according to the ERD. member_id int(11) NO city varchar(45) YES country char(2) YES hometown varchar(45) YES joined datetime YES member_name varchar(45) YES member_status enum('active','prereg') YES visited datetime YES group_id int(11) NO MUL The member_id column seems like a natural PRIMARY KEY, but it is not unique because members are listed multiple times for each group they are a part of. No PRIMARY KEY means that this table is not normalized. You will need to split it into two tables: one that includes information about the members and one that includes the groups they are a part of. A. Create a new table called group_sign_ups that includes only group_id, member_id, and joined. Use CREATE TABLE ... SELECT DISTINCT to transfer the relevant data. CREATE TABLE... SELECT DISTINCT CREATE TABLE grocery_products SELECT DISTINCT products, grocery_name, cost FROM grocery; B. Create a new table called members that includes columns about members from the grp_member table. Use CREATE TABLE ... SELECT DISTINCT to transfer the relevant data. Be sure to only include columns that are directly related to member information. This includes columns like member ID, member name, city, hometown, and member status. It should not include information like when a member visited the Let's Meet website. This is unrelated to data about who each member is! C. Alter the members table to include a PRIMARY KEY. D. Alter the group_sign_ups table to include a FOREIGN KEY that references the members table. D. Alter the group_sign_ups table to include a FOREIGN KEY that references the grp table. E. DROP the grp_member table. You have two options to reset the grp_member table: A. Leave and come back in. All SQL labs will reset the database when you leave the virtual machine entirely and re-enter. This is the easiest option. Save your file(s) in your project Leave the virtual machine entirely Re-enter your project B. Run Downloaded SQL code By downloading the file below, copying the text back onto the virtual machine you can reset the grp_member table. This is slightly more complicated as you will need to use the virtual machine "paste to remote session" option. Downloading this grp_member.sql file. Copy the contents on the group_member.sql file into a new table of the project using the clipboard. Run the new file and exit the tab,

STEP 4: Create a New ERD Create a new ERD that shows your updated tables and columns. With your new ERD open, click File > Save Model to make sure it saves. Clicking on File then navigating down to Save Model allows you to save your ERD work.