Download Database Design: Creating Tables and Relationships for a Coffee Shop and more Exams Human Resource Management in PDF only on Docsity!
Data Management – Applications C170 VHT2 Task
1NORMALIZATION AND DATABASE DESIGN
Construct a normalized physical database model to represent the ordering process for Nora’s Bagel Bin by doing the following: A-1 Complete the second normal form (2NF) section of the attached “Nora’s Bagel Bin Database Blueprints” document by doing the following: a. Assign each attribute from the 1NF table into the correct 2NF table. b. Describe the relationship between the two pairs of 2NF tables by indicating their cardinality in each of the dotted cells: one-to-one (1:1), one-to-many (1: M), many-to-one (M: 1), or many-to-many (M: M). c. Explain how you assigned attributes to the 2NF tables and determined the cardinality of the relationships between your 2NF tables.
To assign attributes to the 2NF tables three tables were created; “Bagel Order”, “Bagel Order Line Item” and “Bagel”. This will support aligning all of the non- attribute keys to their respective primary key; “Bagel Order ID” is the primary key for the “Bagel Order” table, “Bagel ID’ is the primary key for the “Bagel” table and the table “Bagel Order Line Item” intersects both tables by having both primary keys and associating them. The relationship between the “Bagel Order” and “Bagel Order Line Item” tables from left to right is many-to-one. While being read right to left, the association is one to many. Because many “Bagel Order Line Item” can be placed in one “Bagel Order”, and a “Bagel Order” must contain at least, but not limited to one “Bagel Order Line Item”. The cardinality when read is a many to one because of the relationship between the two tables. The relationship between the “Bagel” and the “Bagel Order Line Item” tables from left to right is many-to-one and read from right to left is one-to-many, because one “Bagel” can be a line item in multiple orders, but the “Bagel Order Line Item” could contain one or more “Bagel”. Reading the cardinality from left to right is one-to-many. A-2 Complete the third normal form (3NF) section of the attached “Nora’s Bagel Bin Database Blueprints” document by doing the following: a. Assign each attribute from your 2NF "Bagel Order" table into one of the new 3NF tables. Copy all other information from your 2NF diagram into the 3NF diagram. b. Provide each 3NF table with a name that reflects its contents. c. Create a new field that will be used as a key linking the two 3NF tables you named in part A2b. Ensure that your primary key (PK) and foreign key (FK) fields are in the correct locations in the 3NF diagram.
a. Copy the table names and cardinality information from your 3NF diagram into the “Final Physical Database Model” and rename the attributes. b. Assign one of the following five data types to each attribute in your 3NF tables: CHAR (), VARCHAR (), TIMESTAMP, INTEGER, or NUMERIC (). Each data type must be used at least once. B. Create a database using the attached “Jaunty Coffee Co. ERD” by doing the following: B-1 Develop SQL code to create each table as specified in the attached: “Jaunty Coffee Co. ERD” by doing the following: a. Provide the SQL code you wrote to create all tables.
CREATE TABLE EMPLOYEE
Employee_id INT, first_name VARCHAR (30), last name VARCHAR (30), hire date, job title VARCHAR (30), shipside INT, PRIMARY KEY (employee_id)); CREATE TABLE COFFEE_SHOP ( Shipside INT, shop name VARCHAR (50), city VARCHAR (50), state1 CHAR (2), PRIMARY KEY (shipside)); ALTER TABLE EMPLOYEE ADD FOREIGN KEY (shipside) REFERENCES COFFEE_SHOP (shipside); 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) ); 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) ); ALTER TABLE COFFEE ADD FOREIGN KEY (supplier_id) REFERENCES SUPPLIER(supplier_id); b. Demonstrate that you tested your code by providing a screenshot showing your SQL commands and the database server’s response.
( 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) ); 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) ); ALTER TABLE COFFEE ADD FOREIGN KEY (supplier_id) REFERENCES SUPPLIER(supplier_id); INSERT INTO COFFEE_SHOP (shop_id, shop name, city, state1) Values ('1', 'SJ Coffee', 'San Juan', 'PR'); INSERT INTO COFFEE_SHOP (shop_id, shop name, city, and state1) VALUES ('2', 'Doha Coffee', 'Doha', 'and QT’); INSERT INTO COFFEE_SHOP (shop_id, shop name, city, state1) VALUES ('3', 'Tampa Coffee', 'Tampa', 'and FL’); INSERT INTO SUPPLIER (supplier_id, company_name, country, sales_contact_name, email) VALUES ('1', 'AKs Coffee Supplies', 'USA', 'John Bush', '[email protected]'); INSERT INTO SUPPLIER (supplier_id, company_name, country, sales_contact_name, email) VALUES ('2', 'Kyle’s Coffee Straws', 'USA', 'Kyle David', '[email protected]'); INSERT INTO SUPPLIER (supplier_id, company_name, country, sales_contact_name, email) VALUES ('3', 'All Supplies Needed', 'USA', 'Peter Thomas', '[email protected]');
INSERT INTO COFFEE (coffee_id, coffee name, price_per_pound) VALUES ('1', 'Death Coffee', '7.99'); INSERT INTO COFFEE (coffee_id, coffee name, price_per_pound) VALUES ('2', 'Vanilla Coffee', '4.99'); INSERT INTO COFFEE (coffee_id, coffee name, price_per_pound) VALUES ('3', 'Custom Coffee', '5.99'); INSERT INTO EMPLOYEE (employee_id, first_name, last name, hire date, job title, and shop_id) VALUES ('1', 'Zack', 'Malcolm', '20191123', 'Social Media Manager', '3'); INSERT INTO EMPLOYEE (employee_id, first_name, last name, hire date, job title, and shop_id) VALUES ('2', 'Walter', 'Jackson', '20220305', 'General Manager', '1'); INSERT INTO EMPLOYEE (employee_id, first_name, last name, hire date, job title, and shop_id) VALUES ('3', 'Dylan', 'McHugh', '20150718', 'Barista', '2') b. Demonstrate that you tested your code by providing a screenshot showing your SQL commands and the database server’s response. B-3 Develop SQL code to create a view by doing the following: a. Provide the SQL code you wrote to create your view. The view should show all of the information from the “Employee” table but concatenate each employee’s
INSERT INTO COFFEE_SHOP (shop_id, shop name, city, and state1) VALUES ('2', 'Doha Coffee', 'Doha', 'and QT’); INSERT INTO COFFEE_SHOP (shop_id, shop name, city, state1) VALUES ('3', 'Tampa Coffee', 'Tampa', 'and FL’); INSERT INTO SUPPLIER (supplier_id, company_name, country, sales_contact_name, email) VALUES ('1', 'AKs Coffee Supplies', 'USA', 'John Bush', '[email protected]'); INSERT INTO SUPPLIER (supplier_id, company_name, country, sales_contact_name, email) VALUES ('2', 'Kyle’s Coffee Straws', 'USA', 'Kyle David', '[email protected]'); INSERT INTO SUPPLIER (supplier_id, company_name, country, sales_contact_name, email) VALUES ('3', 'All Supplies Needed', 'USA', 'Peter Thomas', '[email protected]'); INSERT INTO COFFEE (coffee_id, coffee name, price_per_pound) VALUES ('1', 'Death Coffee', '7.99'); INSERT INTO COFFEE (coffee_id, coffee name, price_per_pound) VALUES ('2', 'Vanilla Coffee', '4.99'); INSERT INTO COFFEE (coffee_id, coffee name, price_per_pound) VALUES ('3', 'Custom Coffee', '5.99'); INSERT INTO EMPLOYEE (employee_id, first_name, last_name, hire date, job title, and shop_id) VALUES ('1', 'Zack', 'Malcolm', '20191123', 'Social Media Manager', '3'); INSERT INTO EMPLOYEE (employee_id, first_name, last_name, hire date, job title, and shop_id) VALUES ('2', 'Walter', 'Jackson', '20220305', 'General Manager', '1'); INSERT INTO EMPLOYEE (employee_id, first_name, last_name, hire date, job title, and shop_id) VALUES ('3', 'Dylan', 'McHugh', '20150718', 'Barista', '2'); 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; b. Demonstrate that you tested your code by providing a screenshot showing your SQL commands and the database server’s response. B-4 Develop SQL code to create an index on the coffee name field by doing the following: a. Provide the SQL code you wrote to create your index on the coffee name field from the “Coffee” 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) ); CREATE TABLE COFFEE_SHOP
INSERT INTO SUPPLIER (supplier_id, company_name, country, sales_contact_name, email) VALUES ('1', 'AKs Coffee Supplies', 'USA', 'John Bush', '[email protected]'); INSERT INTO SUPPLIER (supplier_id, company_name, country, sales_contact_name, email) VALUES ('2', 'Kyle’s Coffee Straws', 'USA', 'Kyle David', '[email protected]'); INSERT INTO SUPPLIER (supplier_id, company_name, country, sales_contact_name, email) VALUES ('3', 'All Supplies Needed', 'USA', 'Peter Thomas', '[email protected]'); INSERT INTO COFFEE (coffee_id, coffee name, price_per_pound) VALUES ('1', 'Death Coffee', '7.99'); INSERT INTO COFFEE (coffee_id, coffee name, price_per_pound) VALUES ('2', 'Vanilla Coffee', '4.99'); INSERT INTO COFFEE (coffee_id, coffee name, price_per_pound) VALUES ('3', 'Custom Coffee', '5.99'); INSERT INTO EMPLOYEE (employee_id, first_name, last_name, hire_date, job title, shop_id) VALUES ('1', 'Zack', 'Malcolm', '20191123', 'Social Media Manager', '3'); INSERT INTO EMPLOYEE (employee_id, first_name, last_name, hire_date, job title, shop_id) VALUES ('2', 'Walter', 'Jackson', '20220305', 'General Manager', '1'); INSERT INTO EMPLOYEE (employee_id, first_name, last_name, hire_date, job title, shop_id) VALUES ('3', 'Dylan', 'McHugh', '20150718', 'Barista', '2'); 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;
CREATE INDEX coffee name ON COFFEE (coffee name); b. Demonstrate that you tested your code by providing a screenshot showing your SQL commands and the database server’s response. B-5 Develop SQL code to create an SFW (SELECT-FROM-WHERE) query for any of your tables or views by doing the following: a. Provide the SQL code you wrote to create your SFW query. SELECT* FROM EMPLOYEE; SELECT* FROM COFFEE_SHOP; SELECT* FROM COFFEE; SELECT* FROM SUPPLIER; SELECT*
FROM SUPPLIER;
SELECT*
FROM employee info; SELECT* FROM EMPLOYEE WHERE shop_id=1; 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; b. Demonstrate that you tested your code by providing a screenshot showing your SQL commands and the database server’s response. 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 an order. The cardinality when read is a many-to-one because of the two tables' relationship. 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. 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.
