Bakery MYSQL - DDL Script and DML Population

DDL Script

This section of the script file creates all tables, including PRIMARY KEY contstraints, and also assigns FOREIGN KEY constraints.

--------------------------------- DROP Tables -------------------------------------

DROP TABLE IF EXISTS OrderLine;
DROP TABLE IF EXISTS Payment;
DROP TABLE IF EXISTS CreditCard;
DROP TABLE IF EXISTS Item;
DROP TABLE IF EXISTS Pickup;
DROP TABLE IF EXISTS Delivery;
DROP TABLE IF EXISTS Address;
DROP TABLE IF EXISTS Contact;
DROP TABLE IF EXISTS Order_T;
DROP TABLE IF EXISTS Customer;

--------------------------------- CREATE Tables -------------------------------------

CREATE TABLE Customer(
    CustomerID INTEGER PRIMARY KEY,
    Name VARCHAR(35),
    Email VARCHAR(40),
    Password VARCHAR(25),
    Phone CHAR(10)
);

CREATE TABLE Order_T(
    OrderID INTEGER PRIMARY KEY,
    CustomerID INTEGER REFERENCES Customer(CustomerID),
    OrderDate DATE,
    Time TIME,
    CollectionType VARCHAR(10)
);

CREATE TABLE Contact(
    ContactID INTEGER PRIMARY KEY,
    Name VARCHAR(35),
    Phone CHAR(10)
);

CREATE TABLE Address(
    AddressID INTEGER PRIMARY KEY,
    Street VARCHAR(30),
    City VARCHAR(30),
    State CHAR(2),
    Zip CHAR(5)
);

CREATE TABLE Delivery(
    OrderID INTEGER PRIMARY KEY REFERENCES Order_T(OrderID),
    ContactID INTEGER REFERENCES Contact(ContactNum),
    AddressID INTEGER REFERENCES Address(AddressNum)
);

CREATE TABLE Pickup(
    OrderID INTEGER PRIMARY KEY REFERENCES Order_T(OrderID),
    BranchNum INTEGER
);

CREATE TABLE Item(
    ItemID INTEGER PRIMARY KEY,
    ItemName VARCHAR(25),
    Description VARCHAR(75),
    UnitPrice FLOAT,
    WholesaleMin INTEGER
);

CREATE TABLE CreditCard(
    CreditCardNum VARCHAR(16) PRIMARY KEY,
    Name VARCHAR (35),
    Company VARCHAR(20),
    Expiration DATE,
    SecurityCode INTEGER
);

CREATE TABLE Payment(
    PaymentID INTEGER PRIMARY KEY,
    Method VARCHAR(10),
    Amount FLOAT,
    CreditCardNum VARCHAR(16) REFERENCES CreditCard(CreditCardNum),
    OrderID INTEGER REFERENCES Order_T(OrderID)
);

CREATE TABLE OrderLine(
    OrderID INTEGER REFERENCES Order_T(OrderID),
    ItemID INTEGER REFERENCES Item(ItemID),
    Quantity INTEGER,
    Discount INTEGER,
    CONSTRAINT OrderLine_PK
PRIMARY KEY (OrderID, ItemID)
);

DML Population

*** Populate Customer ***

INSERT INTO Customer VALUES (1, 'Walter White', 'chemteacher@aol.com', 'blue', '5552948829');
INSERT INTO Customer VALUES (2, 'Jesse Pinkman', 'student@aol.com', 'meth', '5559283398');
INSERT INTO Customer VALUES (3, 'Hank Schrader', 'cop@dea.gov', 'dea', '5554449293');
INSERT INTO Customer VALUES (4, 'Saul Goodman', 'bestlawyer@optonline.net', 'bettercall', '5550028472');
INSERT INTO Customer VALUES (5, 'Gustavo Fring', 'owner@polloshermanos.com', 'chicken', '555045772');
INSERT INTO Customer VALUES (6, 'Todd Alquist', 'cleaner@yahoo.com', 'wannabe', '5550023245');
INSERT INTO Customer VALUES (7, 'Lydia Rodarte', 'shoppingqueen@aol.com', 'traveler', '5551238472');
INSERT INTO Customer VALUES (8, 'Vince Gilligan', 'creator@bb.com', 'bestshow', '5551537456');
INSERT INTO Customer VALUES (9, 'Aaron Paul', 'actor@bb.com', 'jesse', '5554428472');

*** Populate Order ***

INSERT INTO Order_T VALUES (1, 4, '2013-12-31', '12:15', 'Pickup');
INSERT INTO Order_T VALUES (2, 1, '2013-11-28', '18:45', 'Delivery');
INSERT INTO Order_T VALUES (3, 2, '2014-01-15', '15:00', 'Pickup');
INSERT INTO Order_T VALUES (4, 9, '2013-12-18', '12:05', 'Pickup');
INSERT INTO Order_T VALUES (5, 4, '2014-01-01', '08:00', 'Pickup');
INSERT INTO Order_T VALUES (6, 4, '2014-01-12', '09:30', 'Delivery');
INSERT INTO Order_T VALUES (7, 8, '2014-02-14', '13:15', 'Pickup');
INSERT INTO Order_T VALUES (8, 7, '2013-12-17', '18:00', 'Pickup');
INSERT INTO Order_T VALUES (9, 6, '2013-12-15', '11:45', 'Pickup');
INSERT INTO Order_T VALUES (10, 4, '2013-12-28', '08:30', 'Pickup');
INSERT INTO Order_T VALUES (11, 2, '2013-12-29', '15:45', 'Delivery');
INSERT INTO Order_T VALUES (12, 9, '2013-12-28', '12:00', 'Delivery');
INSERT INTO Order_T VALUES (13, 5, '2013-12-30', '15:00', 'Delivery');
INSERT INTO Order_T VALUES (14, 4, '2014-01-04', '09:00', 'Pickup');
INSERT INTO Order_T VALUES (15, 5, '2014-03-13', '08:30', 'Pickup');
INSERT INTO Order_T VALUES (16, 7, '2014-02-12', '08:45', 'Delivery');
INSERT INTO Order_T VALUES (17, 1, '2014-01-23', '12:30', 'Pickup');
INSERT INTO Order_T VALUES (18, 6, '2014-12-19', '11:15', 'Pickup');
INSERT INTO Order_T VALUES (19, 1, '2014-01-15', '08:45', 'Pickup');
INSERT INTO Order_T VALUES (20, 8, '2014-03-19', '16:45', 'Pickup');

*** Populate Contact ***

INSERT INTO Contact VALUES (1, 'Skylar White', '5559283384');
INSERT INTO Contact VALUES (2, 'Walter White Jr.', '5559283323');
INSERT INTO Contact VALUES (3, 'Marie Schrader', '5551536754');
INSERT INTO Contact VALUES (4, 'Frank Fring', '5550504938');
INSERT INTO Contact VALUES (5, 'Vince Gilligan', '5551537456');
INSERT INTO Contact VALUES (6, 'Todd Alquist', '5550023245');
INSERT INTO Contact VALUES (7, 'Lydia Rodarte', '5551238472');

*** Populate Address ***

INSERT INTO Address VALUES (1, '380 Negro Arroyo Lane', 'Albuquerque', 'NY', '12948');
INSERT INTO Address VALUES (2, '450 Houston Street', 'Downtown', 'NY', '12954');
INSERT INTO Address VALUES (3, '1024 Donald Drive', 'SOHO', 'NY', '12254');
INSERT INTO Address VALUES (4, '18 Broadway', 'Manhattan', 'NY', '12254');

*** Populate Delivery ***

INSERT INTO Delivery VALUES (2, 1, 1);
INSERT INTO Delivery VALUES (6, 6, 4);
INSERT INTO Delivery VALUES (11, 2, 1);
INSERT INTO Delivery VALUES (12, 5, 3);
INSERT INTO Delivery VALUES (13, 4, 2);
INSERT INTO Delivery VALUES (16, 7, 2);

*** Populate Pickup ***

INSERT INTO Pickup VALUES (1, 003);
INSERT INTO Pickup VALUES (3, 001);
INSERT INTO Pickup VALUES (4, 001);
INSERT INTO Pickup VALUES (5, 002);
INSERT INTO Pickup VALUES (7, 002);
INSERT INTO Pickup VALUES (8, 003);
INSERT INTO Pickup VALUES (9, 001);
INSERT INTO Pickup VALUES (10, 003);
INSERT INTO Pickup VALUES (14, 003);
INSERT INTO Pickup VALUES (15, 002);
INSERT INTO Pickup VALUES (17, 001);
INSERT INTO Pickup VALUES (18, 002);
INSERT INTO Pickup VALUES (19, 003);
INSERT INTO Pickup VALUES (20, 001);

*** Populate Item ***

INSERT INTO Item VALUES (1, 'Vanilla Cupcake', 'White cake with vanilla icing', 2.50, 24);
INSERT INTO Item VALUES (13, 'Double Chocolate Fudge Cupcake', 'Overload of chocolate explosion', 2.50, 24);
INSERT INTO Item VALUES (14, 'Smores Cupcake', 'A mix of chocolate and cream', 2.50, 24);
INSERT INTO Item VALUES (15, 'Cookie Dough Cupcake', 'A cookie in the form of a cupcake', 2.50, 24);
INSERT INTO Item VALUES (2, 'Red Velvet Cupcake', 'mix of chocolate and vanilla cake with cream cheese icing', 2.50, 24);
INSERT INTO Item VALUES (3, 'Sugar Cookie', 'large with sprinkles', 1.25, 36);
INSERT INTO Item VALUES (7, 'Chocolate Chip Cookie', 'made with chunky chocolate chips', 1.25, 36);
INSERT INTO Item VALUES (4, 'Pumpkin Pie', 'Fall-time favorite', 11.00, 4);
INSERT INTO Item VALUES (8, 'Apple Crumb Pie', 'Made with locally grown apples', 11.00, 4);
INSERT INTO Item VALUES (9, 'Blueberry Pie', 'Best in New York', 11.00, 4);
INSERT INTO Item VALUES (10, 'Peach Pie', 'Sweet and Tangy', 11.00, 4
INSERT INTO Item VALUES (5, 'Cheesecake', 'NY style', 14.00, 4);
INSERT INTO Item VALUES (11, 'Key lime Pie', 'With just the right hint of lime', 11.00, 4);
INSERT INTO Item VALUES (12, 'Strawberry Shortcake', 'Contains three layers of Strawberrys and Cream', 14.00, 4);
INSERT INTO Item VALUES (6, 'Carrot Cake', 'Made with organic carrots', 14.00, 4);

*** Populate Credit Card ***

INSERT INTO CreditCard VALUES ('000287492745529', 'Edward Snowden', 'American Express', '2016-10-17', '1012');
INSERT INTO CreditCard VALUES ('9999284622931112', 'Joseph Gordon Levitt', 'Visa', '2015-06-30', '0664');
INSERT INTO CreditCard VALUES ('1243346544470980', 'William Gates', 'Mastercard', '2017-09-30', '194');
INSERT INTO CreditCard VALUES ('8392574935702342', 'Barack Obama', 'Visa', '2018-08-30', '232');

*** Populate Payment ***

INSERT INTO Payment VALUES (1, 'Check', 28.00, NULL, 3);
INSERT INTO Payment VALUES (2, 'Cash', 10.00, NULL, 3);
INSERT INTO Payment VALUES (3, 'Credit', 82.00, '000287492745529', 2);
INSERT INTO Payment VALUES (4, 'Cash', 135.70, NULL, 1);
INSERT INTO Payment VALUES (5, 'Cash', 15.00, NULL, 5);
INSERT INTO Payment VALUES (6, 'Cash', 53.20, NULL, 4);
INSERT INTO Payment VALUES (7, 'Credit', 30.00, '1243346544470980', 7);
INSERT INTO Payment VALUES (8, 'Check', 17.75, NULL, 7);
INSERT INTO Payment VALUES (9, 'Cash', 11.00, NULL, 8);
INSERT INTO Payment VALUES (10, 'Cash', 77.00, NULL, 9);
INSERT INTO Payment VALUES (11, 'Cash', 39.00, NULL, 10);
INSERT INTO Payment VALUES (12, 'Cash', 33.00, NULL, 11);
INSERT INTO Payment VALUES (13, 'Cash', 28.00, NULL, 12);
INSERT INTO Payment VALUES (14, 'Credit', 37.00, '1243346544470980', 13);
INSERT INTO Payment VALUES (15, 'Cash', 44.00, NULL, 14);
INSERT INTO Payment VALUES (16, 'Credit', 100.00, '8392574935702342', 14);
INSERT INTO Payment VALUES (17, 'Cash', 44.00, NULL, 15);
INSERT INTO Payment VALUES (18, 'Check', 21.00, NULL, 16);
INSERT INTO Payment VALUES (19, 'Cash', 24.00, NULL, 17);
INSERT INTO Payment VALUES (20, 'Cash', 37.50, NULL, 18);
INSERT INTO Payment VALUES (21, 'Check', 50.00, NULL, 18);
INSERT INTO Payment VALUES (22, 'Credit', 53.00, '9999284622931112', 19);
INSERT INTO Payment VALUES (23, 'Cash', 33.00, NULL, 20);

*** Populate OrderLine ***

INSERT INTO OrderLine VALUES (1, 2, 2, 0);
INSERT INTO OrderLine VALUES (1, 4, 1, 0);
INSERT INTO OrderLine VALUES (2, 6, 5, 20);
INSERT INTO OrderLine VALUES (2, 3, 12, 0);
INSERT INTO OrderLine VALUES (2, 4, 1, 0);
INSERT INTO OrderLine VALUES (3, 1, 3, 0);
INSERT INTO OrderLine VALUES (3, 2, 1, 0);
INSERT INTO OrderLine VALUES (3, 5, 2, 0);
INSERT INTO OrderLine VALUES (4, 12, 4, 20);
INSERT INTO OrderLine VALUES (4, 2, 5, 0);
INSERT INTO OrderLine VALUES (5, 1, 2, 0);
INSERT INTO OrderLine VALUES (5, 2, 2, 0);
INSERT INTO OrderLine VALUES (5, 15, 2, 0);
INSERT INTO OrderLine VALUES (6, 6, 5, 20);
INSERT INTO OrderLine VALUES (7, 11, 1, 0);
INSERT INTO OrderLine VALUES (7, 4, 2, 0);
INSERT INTO OrderLine VALUES (7, 7, 3, 0);
INSERT INTO OrderLine VALUES (7, 8, 1, 0);
INSERT INTO OrderLine VALUES (8, 9, 1, 0);
INSERT INTO OrderLine VALUES (9, 3, 36, 20);
INSERT INTO OrderLine VALUES (9, 7, 36, 20);
INSERT INTO OrderLine VALUES (10, 10, 1, 0);
INSERT INTO OrderLine VALUES (10, 12, 2, 0);
INSERT INTO OrderLine VALUES (10, 2, 2, 0);
INSERT INTO OrderLine VALUES (11, 1, 4, 0);
INSERT INTO OrderLine VALUES (11, 7, 36, 20);
INSERT INTO OrderLine VALUES (12, 6, 2, 0);
INSERT INTO OrderLine VALUES (13, 14, 3, 0);
INSERT INTO OrderLine VALUES (13, 7, 6, 0);
INSERT INTO OrderLine VALUES (13, 11, 1, 0);
INSERT INTO OrderLine VALUES (13, 10, 1, 0);
INSERT INTO OrderLine VALUES (14, 15, 28, 20);
INSERT INTO OrderLine VALUES (14, 13, 28, 20);
INSERT INTO OrderLine VALUES (15, 10, 5, 20);
INSERT INTO OrderLine VALUES (16, 2, 4, 0);
INSERT INTO OrderLine VALUES (16, 4, 1, 0);
INSERT INTO OrderLine VALUES (17, 12, 1, 0);
INSERT INTO OrderLine VALUES (17, 3, 8, 0);
INSERT INTO OrderLine VALUES (18, 13, 5, 0);
INSERT INTO OrderLine VALUES (18, 4, 2, 0);
INSERT INTO OrderLine VALUES (18, 5, 3, 0);
INSERT INTO OrderLine VALUES (18, 11, 1, 0);
INSERT INTO OrderLine VALUES (19, 15, 10, 0);
INSERT INTO OrderLine VALUES (19, 5, 2, 0);
INSERT INTO OrderLine VALUES (20, 8, 3, 0);

Tables

The orginal submission of our tables:

Our new tables as of November 18, 2013:

OrderLine Table

Order_T Table

Payment Table

Customer Table

Contact Table

Address Table

Delivery Table

Pickup Table

CreditCard Table

Item Table

Changes from the Previous Deliverable