C170 Database Applications TASK 1 PDF

Preview

Summary

Data Management – Applications – CVHT2 TASK 1: NORMALIZATION AND DATABASE DESIGNEdward LoredonullA. Construct a normalized physical database modelA-1 Complete the 2NF To obtain the second normal form (2NF) for the original table, it was necessary to create three tables: Bagel Order, Bagel Order Line...

Description

Data Management – Applications – C170 VHT2 TASK 1: NORMALIZATION AND DATABASE DESIGN Edward Loredo #001155270

A. Construct a normalized physical database model A-1 Complete the 2NF To obtain the second normal form (2NF) for the original table, it was necessary to create three tables: Bagel Order, Bagel Order Line Item, and Bagel. This was required so the non-key attributes would functionally depend on the primary key. Bagel Order ID is the primary key for the Bagel Order table, as is Bagel ID in the Bagel table. The third table, Bagel Order Line Item, takes both primary keys in the tables as foreign keys. This table is also the associative table, also known as the intersection table, of Bagel Order and Bagel. The relationship of Bagel Order and Bagel Order Line item from left to right is many-to-one. While being read from right to left, the association is one-to-many. This is because many order line items can be placed in one Bagel Order, and a Bagel Order must contain, at least but not limited to, one item for it to be an order. The cardinality when read is a many-to-one because of the relationship the two tables have. The relationships between Bagel and Bagel Order Line Item are also many-to-one while being read from left to right and one-to-many in the opposite direction. A bagel can appear as a line item in multiple orders, but the order line needs to contain one or numerous bagels. When the cardinality is read from left to right, it is one-to-many.

A-2 Complete the 3NF The tables must not have transitive dependencies to obtain the third normal form (3NF). This means non-key attributes can’t depend on an attribute that isn’t the primary key. To accomplish this, a fourth table must be created. The Customer table is made, and the attributes that depend on the customer are moved out of the Bagel Order table. The new Customer table will have a one-to-many relationship with the Bagel Order table. Customer ID is the primary key in the Customer table and the foreign key in the Bagel Order table. This is because one customer is required to submit an order. Without the customer, the order isn’t placed. The relationship is one-to-many because the same customer can submit several orders. The cardinality is many-to-one due to the possibility of multiple orders being submitted by a required minimum of one customer. The relationship between Bagel Order and Bagel Order Line Items didn’t change. It is still a oneto-many when read right to left and many-to-one when read left to right. The cardinality remains as many-to-one because multiple order line items can be placed in a bagel order. There was no change in the Bagel Order Line Item and Bagel tables as with the previous relationship. When read right to left, the many-to-one and one-to-many, when read left to right, remains. The one-to-many cardinality had no modifications because it entails at least one bagel be in an order line item. Although one bagel is required, several bagels can be in an order that justifies the one-to-many cardinality.

A-3. Complete the Final Physical Database Model Note: Zoomed in screenshots of the tables are provided

B. Create Database using Jaunty Coffee Co. ERD B-1 – Develop SQL to Create All Tables To create the EMPLOYEE table, I first made the table without the foreign key because COFFEE_SHOP didn’t exist. I set employee_id as the primary key and set each data type to the preferred value. When I created the table, I used a SELECT * to ensure I made the table. I then moved on to create the COFFEE_SHOP table. This table had no foreign key but did have a primary key. I made the primary key during the table creation and set the data types to the chosen values. After creating the first table, I used a SELECT * to verify that the table was created. Now that the COFFEE_SHOP was created with shop_id set as the table’s primary key, I used an ALTER TABLE to add a foreign key to the EMPLOYEE table, referencing shop_id from the COFFEE_SHOP table. CREATE TABLE EMPLOYEE ( employee_id INT, first_name VARCHAR(30), last_name VARCHAR(30), hire_date DATE, job_title VARCHAR(30), shop_id INT, PRIMARY KEY (employee_id) ); SELECT * FROM EMPLOYEE;

Figure 1 – Creation of EMPLOYEE table SELECT *

FROM EMPLOYEE; CREATE TABLE COFFEE_SHOP ( shop_id INT, shop_name VARCHAR(50), city VARCHAR(50), state1 CHAR(2), PRIMARY KEY (shop_id) ); SELECT * FROM COFFEE_SHOP;

Figure 2 – Creation of COFFEE_SHOP table ALTER TABLE EMPLOYEE ADD FOREIGN KEY (shop_id) REFERENCES COFFEE_SHOP(shop_id);

Figure 3 – Adding the Foreign Key to the Employee table I proceeded to create the COFFEE table. This table contained a primary key with two foreign keys. One of the foreign keys referenced a table that hadn’t been created yet. I made the table using coffee_id as the primary key and shop_id as the foreign key. The foreign key shop_id did reference the COFFEE_SHOP table in the syntax. Once again, the data types were set to the requested values. Naturally, I executed a SELECT * to validate that the table was created. Progressing forward, I created the SUPPLIER table, which was the last table to be requested. This table had supplier_id as the primary key and had no foreign key. I made the table using supplier_id as the primary key and set the data types to the desired values. Instinctively I implemented my Select * to certify the table was created. With the SUPPLIER table created, I performed an ALTER TABLE statement to the COFFEE table and added SUPPLIER’s supplier_id as the additional foreign key.

CREATE TABLE COFFEE ( coffee_id INT, shop_id INT, supplier_id INT, coffee_name VARCHAR(30), price_per_pound NUMERIC(5,2), PRIMARY KEY (coffee_id), FOREIGN KEY (shop_id) REFERENCES COFFEE_SHOP(shop_id) ); SELECT * FROM COFFEE;

Figure 4 – Creation of the COFFEE table, which includes the Primary Key and one Foreign Key CREATE TABLE SUPPLIER ( supplier_id INT, company_name VARCHAR(50), country VARCHAR(30), sales_contact_name VARCHAR(60), email VARCHAR(50) NOT NULL, PRIMARY KEY (supplier_id) ); SELECT *

FROM SUPPLIER;

Figure 5 – Creation of the SUPPLIER table ALTER TABLE COFFEE ADD FOREIGN KEY (supplier_id) REFERENCES SUPPLIER(supplier_id);

Figure 6 – Adding the remaining Foreign Key to the COFFEE table All the tables I created were performed in Microsoft SQL Server Management Studio. As a final method to verify the tables were made to the specifications requested, I refreshed my SQL instance, expanded the tables, and opened their respective Columns folder.

Figure 7 – Collection of table columns to verify proper configuration

B-2 Develop SQL code to Populate Tables

The first table I populated was the COFFEE_SHOP table. I created three employees who serve as the manager, sales clerk, and stocker. This can be verified by running a SELECT * on the table. INSERT INTO COFFEE_SHOP (shop_id, shop_name, city, state1) VALUES ('1','SAs Coffee', 'San Antonio', 'TX'); INSERT INTO COFFEE_SHOP (shop_id, shop_name, city, state1) VALUES ('2', 'Denver COffee', 'Denver', 'CO'); INSERT INTO COFFEE_SHOP (shop_id, shop_name, city, state1) VALUES ('3', 'Las Vegas Coffeee', 'Las Vegas', 'NV'); SELECT * FROM COFFEE_SHOP;

Figure 7 – Syntax for INSERT INTO COFFEE_SHOP Table The SUPPLIER table was the second table to be populated with content. The companies inserted into the table sell coffee and coffee supplies. Reviewing the results of the SELECT * on the table validates that the table is populated with information.

INSERT INTO SUPPLIER (supplier_id, company_name, country, sales_contact_name, email) VALUES ('1', 'AJs Coffee Supplies', 'USA', 'Kit Boga', '[email protected]'), ('2', 'Amelias Coffee Cups', 'USA', 'Billy Austin', '[email protected]'), ('3', 'Willies Coffee Filters', 'USA', 'Johnny Adams', '[email protected]');

SELECT * FROM SUPPLIER;

Figure 8 – Syntax for INSERT INTO SUPPLIER COFFEE was the third table to be updated with data. In this table, three products are listed with the requested fields. The table values were verified by running a SELECT * on the table. INSERT INTO COFFEE (coffee_id, shop_id, supplier_id, coffee_name, price_per_pound) VALUES ('1', '1', '1', 'Black Coffee', 4.99), ('2', '2', '2', 'Iced Coffee', 6.99), ('3', '3', '3', 'Pistachio Coffee', 9.99);

Figure 9 – Syntax for INSERT INTO COFFEE The EMPLOYEE table was the last table to have data inserted. Included is the SELECT * for the table to verify that the data was inserted correctly.

INSERT INTO EMPLOYEE (employee_id, first_name, last_name, hire_date, job_title, shop_id) VALUES ('1', 'Zachary', 'Bolts', '20200413', 'Manager', '1'), ('2', 'Jay', 'Norris', '20210218', 'Sales Clerk', '1'), ('3', 'Chuck', 'Boyd', '20191121', 'Stocker', '1');

Figure 10 – Syntax for INSERT INTO EMPLOYEE B-3 Develop View for Employee Information CREATE VIEW employee_info AS SELECT "employee_id", CONCAT(EMPLOYEE.first_name,' ',EMPLOYEE.last_name) employee_full_name, hire_date, job_title, shop_id FROM EMPLOYEE; SELECT * FROM employee_info;

Figure 11 – Syntax for Create View B-4 Develop Code to Create Index CREATE INDEX coffee_name ON COFFEE (coffee_name);

Figure 12 – Syntax for Index B-5 Develop Code to create Select – From – Where SELECT * FROM EMPLOYEE WHERE shop_id = 1;

Figure 13 – Syntax for Select – From – Where B-6 Develop SQL Code for Join Query SELECT * FROM COFFEE a LEFT JOIN COFFEE_SHOP b ON a.shop_id = b.shop_id LEFT JOIN EMPLOYEE c ON c.shop_id = a.shop_id LEFT JOIN SUPPLIER d ON d.supplier_id = a.supplier_id;

Figure 14 – Syntax for Join Query...