Database applications , Summaries of Computer Science

Download Database applications and more Summaries Computer Science in PDF only on Docsity!

lOMoARcPSD|

E. Loredo,

Database applications

Data Management – Applications – C

VHT2 TASK 1: NORMALIZATION AND DATABASE DESIGN

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 one- to-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

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 *

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.

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