Data Management - Applications - C170 Performance Labs - uCertify
1.1.1 - Conceptual Schema - ER Model
Tables: Customer, DonutOrder, Donut ans:
... [Show More] 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'
),
(
'Jolie',
'Angelina'
);
... 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, [Show Less]