First Normal Form Assignment
Attached Files:
- exampleData.sql (740 B)
In this exercise you will:1.) Run the attached SQL script “exampleData.sql” in your preferred way (E.g., workbench or from the command line tool in a terminal window.)This script creates a database named FN1 and a table named “VoterData”, and loads three rows of data into the table.Notice that the data are not stored in First Normal Form because two of the columns: “address” and “email” are used in a way that makes their data nonatomic. A valid address is hierarchical: street number, street name, city/town name, state, zip code, etc. The email field is used to store more than one email address. (If you want to know more about postal addresses read this: https://en.wikipedia.org/wiki/Address)2.) Watch Caleb Curry’s youtube video on First Normal Form (https://www.youtube.com/watch?v=JjwEhK4QxRo) and understand what this means and how he redesigns his database. Notice that the database I gave you is similar to his.3.) Redesign the provided example database so that the email information is in first normal form. This means that you will create a new table called “EmailAddresses” that is specialized for storing email address information, and, it has a foreign key relationship with the VoterData table, etc.
DROP DATABASE IF EXISTS FN1;
CREATE DATABASE FN1;
USE FN1;
DROP TABLE IF EXISTS VoterData;
CREATE TABLE VoterData (
ID int NOT NULL AUTO_INCREMENT,
fName VARCHAR(128),
lName VARCHAR(128),
address VARCHAR(255),
email VARCHAR(128),
PRIMARY KEY (ID)
);
INSERT INTO VoterData (fName, lName, address, email) VALUES (“Mary”, “Smith”, “123 Main Street, Waltham, MA”, “marys@gmail.com, marys@example.com”);
INSERT INTO VoterData (fName, lName, address, email) VALUES (“Franz”, “Kafka”, “PO Box 13, Red Knuckle, MT”, “Franzk@gmail.com, SadCockroach@example.com”);
INSERT INTO VoterData (fName, lName, address, email) VALUES (“Primo”, “Levy”, “123 Elm Street, Milford, NH”, “chemist@gmail.com, periodictable@example.com”);
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
