Data Management - Applications - C170 Performance Labs - uCertify

Data Management - Applications - C170 Performance Labs - uCertify 1.1.1 - Conceptual Schema - ER Model Tables: Customer, DonutOrder, Donut ans: Customer: PK - CustomerID Rest are customer related fields DonutOrder: FK - CustomerID, FK - DonutID Qty, SpecialNotes Donut: PK - DonutID Rest are donut fields Relationships: Customer - 1:n Identifying - DonutOrder DonutOrder - n:1 Identifying - Donut Why are the relationships in 1.1.1 Identifying? ans: The PK's of Customer and Donut are included in the composite primary key of DonutOrder. 2.1.1 - Physical Scheme - ER Model Tables: Customer, DonutOrder, Donut ans: Customer: PK - CustomerID Rest are customer related fields DonutOrder: PK- DonutOrderID FK - CustomerID FK - DonutID Rest of order related Donut: PK - DonutID Rest are donut related Relationships: Customer - 1:n Non-identifying - DonutOrder DonutOrder - n:1 Non-identifying - Donut Why are the relationships in 2.1.1 Non-Identifying? ans: The PK in DonutOrder is a unique key that does not include FK of other tables. 3.2.1 - Creating a database You need to create a database named "DonutOrderingDB". ans: create database DonutOrderingDB; 3.2.2 - Dropping database in MySQL You need to delete a database named DonutOrderingDB. ans: drop database DonutOrderingDB; 3.2.3 - Understanding types of relationships M:M ans: B. Each record from first table is associated with many records in second table and one record in second table is associated with many records in first table. 3.2.3 - Understanding types of relationships 1:M (or 1:n) ans: C. Each record from first table is associated with many records in second table but each record in second table is associated with one record in first table. 3.2.3 - Understanding types of relationships 1:1 ans: A. Single record in the first table is related to only one record in the second table and vice versa. 3.2.4 - Display Table Structure in MySQL editor Use the DESCRIBE command to display the following table structure Tables: Customer Donut CustomerDonutOrder ans: describe Customer; describe Donut; describe CustomerDonutOrder; 3.2.5 - Create Tables in MySQL Create a table named customerdonutorder as represented in the following Physical Schema by using the CREATE TABLE command. (Hint: Use DonutOrderTimestamp TIMESTAMP DEFAULT NOW()) customerdonutorder DonutorderID INT(11) CustomerID INT(11) - FK (Customer.CustomerID) DonutOrderTimestamp TIMESTAMP SpecialNotes VARCHAR(500) ans: create table customerdonutorder ( DonutorderID INT(11), CustomerID INT(11), DonutOrderTimestamp TIMESTAMP DEFAULT NOW(), SpecialNotes VARCHAR(500), foreign key (CustomerID) references Customer(CustomerID) ); 3.3.1 - Populate tables in MySQL Populate three tables using the Insert command. (Lots of information from the table schema I'm going to skip). ans: insert into Customer (LastName, FirstName, Street, Apt, City, State, Zip, HomePhone, MobilePhone, OtherPhone) values ( 'Pitt', 'Brad' truncated ), ( 'Jolie', 'Angelina' truncated ); ... then its just using the same insert into command for Donut and CustomerDonutOrder. 3.3.2 - Update Data in a table in MySQL Update the OrderQty as 10 in the uc_donutorder table where DonutOrderID is 1. ans: update uc_donutorder set OrderQty = 10 where DonutOrderID = 1; 3.3.3 - Delete rows in MySQL A table, test1, has already been created. Delete rows where id is 23 or 35. ans: delete from test1 where id = 23 or id = 35; 4.4.1 - Joining Tables in MySQL Write a query that joins (Inner Join) the three tables below and meets the following requirements Tables: uc_customer, uc_donut, uc_donutorder Requirements: - Retrieve DonutOrderID, LastName, DonutName, OrderQty, and DonutPrce - Use aliases o for uc_donutorder, p for uc_donut, and c for uc_customer ans: select DonutOrderID,