Data Management - Applications - C170 Performance Labs - uCertify

1.1.1 - Conceptual Schema - ER Model Tables: Customer, DonutOrder, Donut 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? 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 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? 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". create database DonutOrderingDB; 3.2.2 - Dropping database in MySQL You need to delete a database named DonutOrderingDB. drop database DonutOrderingDB; 3.2.3 - Understanding types of relationships M:M 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) 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 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 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) 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). 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.