Expectations: 1. Normalize the data, provide rationale for the tables you create, create the relational tables that you identified as part of the normalization process using appropriate SQL/DDL, insert the data into the tables using SQL/DDL, create queries as indicated using SQL/DML Aim: To review Relational concepts including normalization and SQL coding. Customer Alice Address 13-14 5th Ave Ordered Plums Amount 40 Priceperunit 1 Onhand 100 Vendor Very Fresh Alice 1314 5th Ave Oranges 30 3 300 Garden Heaven Alice 13-14 5thAve Pears 10 4 400 Produce One Alice 1314 5th Avenue Oranges 40 3 300 Garden Heav. Bill 55 Mapel Drive Apples 50 2 200 Very Fresh Bill 55 Maple Drive Bananas 100 5 500 Garden Heaven Bill 55 Maple Dr. Bananas 100 5 500 Caitlin 1 Oak Road Apt. 3A Plums 40 1 100 Caitlin Elizabeth 1 Oak Rd. Ap. 3A Plums 40 1 100 V. Fresh 16 Sunshine Lane Kiwi 30 7 700 Laura 66 East 4th Street Grapes 60 6 600 Garden Hyn. Very Fresh Veggie Heaven Veggie Haven Laura 66 East 4th Street Apples 10 2 200 Very Fresh • SECTION One - Normalize the table above ° using the methods taught in class and also the readings/videos assigned for this week split the table into separate tables. ° о Identify the primary, secondary (foreign) and/or composite keys Using the example given below as your guide, document each table you created after you have normalized the data in this manner. EXAMPLE: Tablename1 (fieldname1, fieldname2, fieldname3, fieldname4) - fieldname1 is underlined to show it is a primary key ° Please note: make sure each table does not contain any unnecessary repetition and errors. Make sure each table has a unique identifier (Primary key, Composite key). Underline the primary or composite key. SECTION 2 Describe what you created For each table you created in section 1, a) Discuss your rationale for creating the table and, b) list all the tables that are related to the table you are describing. Please see example Example: The rationale for creating Tablenamel was that..... Tablename1 is related to Tablename2 through the key fieldname1. Tablename 1 is also related to Tablename3 through the fieldname1 etc.
This is section 1 answer (just do the section 2 only)
Section 1:
Step 1: Normalize the Table
To normalize the provided data, we must ensure that the
Customers
Products
Orders
Customers Table
Attributes: CustomerID, CustomerName, Address
Primary Key: CustomerID
Rationale: Each customer should have a unique identifier.
Products Table
Attributes: ProductID, ProductName, PricePerUnit, Onhand, Vendor
Primary Key: ProductID
Rationale: Each product is unique and identified by a ProductID.
Orders Table
Attributes: OrderID, CustomerID (FK), ProductID (FK), Quantity, Amount
Primary Key: OrderID
Foreign Keys: CustomerID references Customers, ProductID references Products
Rationale: Orders are unique transactions linking customers to the products they purchase.
Step 2: SQL Code for Table Creation and Data Insertion
Creating Tables
CREATE TABLE Customers (
CustomerID INT AUTO_INCREMENT PRIMARY KEY,
CustomerName VARCHAR(255),
Address VARCHAR(255)
);
CREATE TABLE Products (
ProductID INT AUTO_INCREMENT PRIMARY KEY,
ProductName VARCHAR(255),
PricePerUnit DECIMAL(10,2),
Onhand INT,
Vendor VARCHAR(255)
);
CREATE TABLE Orders (
OrderID INT AUTO_INCREMENT PRIMARY KEY,
CustomerID INT,
ProductID INT,
Quantity INT,
Amount DECIMAL(10,2),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);
Inserting Data into Customers Table
INSERT INTO Customers (CustomerName, Address) VALUES
('Alice', '13-14 5th Ave'),
('Bill', '55 Maple Drive'),
('Caitlin', '1 Oak Road Apt. 3A'),
('Elizabeth', '16 Sunshine Lane'),
('Laura', '66 East 4th Street');
Inserting Data into Products Table
INSERT INTO Products (ProductName, PricePerUnit, Onhand, Vendor) VALUES
('Plums', 1, 100, 'Very Fresh'),
('Oranges', 3, 300, 'Garden Heaven'),
('Bananas', 5, 500, 'Garden Heaven'),
('Apples', 2, 200, 'Very Fresh'),
('Kiwi', 7, 700, 'Veggie Heaven'),
('Grapes', 6, 600, 'Veggie Heaven');
Inserting Data into Orders Table
Note that you will need to fetch or know the CustomerID and ProductID to insert orders properly. We can assume that IDs are known.
INSERT INTO Orders (CustomerID, ProductID, Quantity, Amount) VALUES
(1, 2, 40, 120),
(1, 2, 50, 150),
(2, 3, 100, 500),
(3, 4, 40, 40),
(4, 5, 30, 210),
(5, 4, 10, 20);
Step by step
Solved in 2 steps
