Tutorial 1.2

-- Creating the database for testing and only for the first tutorial.
CREATE SCHEMA `AdvancedDatabasesTest`;
-- Question 1 Part A
CREATE TABLE `AdvancedDatabasesTest`.`Customer` (
    CustomerNumber INT,
    CustomerName VARCHAR(32),
    CustomerAddress VARCHAR(128),
    Balance DECIMAL(6 , 2 ),
    CreditLimitation INT
);
CREATE TABLE `AdvancedDatabasesTest`.`Order` (
    OrderNumber INT,
    OrderDate INT,
    CustomerNumber INT
);
CREATE TABLE `AdvancedDatabasesTest`.`OrderedPart` (
    OrderNumber INT,
    PartNumber VARCHAR(4),
    NumbersOrdered INT,
    QuotationPriced DECIMAL(6 , 2 )
);
CREATE TABLE `AdvancedDatabasesTest`.`Part` (
    PartNumber VARCHAR(4),
    PartDescription VARCHAR(32),
    UnitsOnHand INT,
    ItemClass VARCHAR(2),
    WarrantyHighStandardNumber INT,
    UnitPrice DECIMAL(6 , 2 )
);
DROP TABLE `AdvancedDatabasesTest`.`Customer`;
DROP TABLE `AdvancedDatabasesTest`.`Order`;
DROP TABLE `AdvancedDatabasesTest`.`OrderedPart`;
DROP TABLE `AdvancedDatabasesTest`.`Part`;
-- Question 1 Part B
CREATE TABLE `AdvancedDatabasesTest`.`Customer` (
    CustomerNumber INT NOT NULL,
    CustomerName VARCHAR(32),
    CustomerAddress VARCHAR(128),
    Balance DECIMAL(6 , 2 ),
    CreditLimitation INT,
    CONSTRAINT PK_CustomerNumber PRIMARY KEY (CustomerNumber)
);
CREATE TABLE `AdvancedDatabasesTest`.`Order` (
    OrderNumber INT NOT NULL,
    OrderDate INT,
    CustomerNumber INT NOT NULL,
    CONSTRAINT PK_OrderNumber PRIMARY KEY (OrderNumber),
    CONSTRAINT FK_CustomerNumber FOREIGN KEY (CustomerNumber)
        REFERENCES `AdvancedDatabasesTest`.`Customer` (CustomerNumber)
);
CREATE TABLE `AdvancedDatabasesTest`.`Part` (
    PartNumber VARCHAR(4) NOT NULL,
    PartDescription VARCHAR(32),
    UnitsOnHand INT,
    ItemClass VARCHAR(2),
    WarrantyHighStandardNumber INT,
    UnitPrice DECIMAL(6 , 2 ),
    CONSTRAINT PK_PartNumber PRIMARY KEY (PartNumber)
);
CREATE TABLE `AdvancedDatabasesTest`.`OrderedPart` (
    OrderNumber INT NOT NULL,
    PartNumber VARCHAR(4) NOT NULL,
    NumbersOrdered INT,
    QuotationPriced DECIMAL(6 , 2 ),
    PRIMARY KEY (OrderNumber , PartNumber),
    FOREIGN KEY (OrderNumber)
        REFERENCES `AdvancedDatabasesTest`.`Order` (OrderNumber),
    FOREIGN KEY (PartNumber)
        REFERENCES `AdvancedDatabasesTest`.`Part` (PartNumber)
);
DROP TABLE `AdvancedDatabasesTest`.`OrderedPart`;
DROP TABLE `AdvancedDatabasesTest`.`Part`;
DROP TABLE `AdvancedDatabasesTest`.`Order`;
DROP TABLE `AdvancedDatabasesTest`.`Customer`;
-- Question 1 Part C
-- Question 2: Declaration of Primary Key for Customer Table at Line 79.
-- Question 7: Addition of Unique Constraint at Line 81
CREATE TABLE `AdvancedDatabasesTest`.`Customer` (
    CustomerNumber INT NOT NULL PRIMARY KEY,
    CustomerName VARCHAR(32),
    CustomerAddress VARCHAR(128) UNIQUE,
    Balance DECIMAL(6 , 2 ),
    CreditLimitation INT
);
-- Question 3: Declaration of Foreign Key for Order Table at Line 90.
CREATE TABLE `AdvancedDatabasesTest`.`Order` (
    OrderNumber INT NOT NULL PRIMARY KEY,
    OrderDate INT,
    CustomerNumber INT NOT NULL,
    FOREIGN KEY (CustomerNumber)
        REFERENCES `AdvancedDatabasesTest`.`Customer` (CustomerNumber)
);
-- Question 6: Addition of Check Constraint at Line 101.
CREATE TABLE `AdvancedDatabasesTest`.`Part` (
    PartNumber VARCHAR(4) NOT NULL PRIMARY KEY,
    PartDescription VARCHAR(32),
    UnitsOnHand INT,
    ItemClass VARCHAR(2),
    WarrantyHighStandardNumber INT,
    UnitPrice DECIMAL(6 , 2 ),
    CONSTRAINT CHK_PartDescription CHECK (PartDescription = 'IRON')
);
-- Question 2: Declaration of Primary Key for OrderedPart Table at Line 111.
-- Question 3: Declaration of Foreign Key for OrderedPart Table at Line 112 and Line 114.
-- Question 5: Addition of Default Constraint at Line 106.
CREATE TABLE `AdvancedDatabasesTest`.`OrderedPart` (
    OrderNumber INT NOT NULL,
    PartNumber VARCHAR(4) NOT NULL,
    NumbersOrdered INT DEFAULT 1,
    QuotationPriced DECIMAL(6 , 2 ),
    PRIMARY KEY (OrderNumber , PartNumber),
    FOREIGN KEY (OrderNumber)
        REFERENCES `AdvancedDatabasesTest`.`Order` (OrderNumber),
    FOREIGN KEY (PartNumber)
        REFERENCES `AdvancedDatabasesTest`.`Part` (PartNumber)
);
-- Question 4 Part A
ALTER TABLE `AdvancedDatabasesTest`.`Customer`
    ADD Region VARCHAR(16);
-- Question 4 Part B
ALTER TABLE `AdvancedDatabasesTest`.`Customer`
    MODIFY COLUMN CustomerAddress VARCHAR(256);
-- Question 4 Part C
ALTER TABLE `AdvancedDatabasesTest`.`Customer`
    DROP COLUMN CustomerAddress;
-- Question 8
ALTER TABLE `AdvancedDatabasesTest`.`Part` DROP CHECK CHK_PartDescription;
DROP TABLE `AdvancedDatabasesTest`.`OrderedPart`;
DROP TABLE `AdvancedDatabasesTest`.`Part`;
DROP TABLE `AdvancedDatabasesTest`.`Order`;
DROP TABLE `AdvancedDatabasesTest`.`Customer`;
CREATE TABLE `AdvancedDatabasesTest`.`Customer` (
    CustomerNumber INT NOT NULL,
    CustomerName VARCHAR(32),
    CustomerAddress VARCHAR(128),
    Balance DECIMAL(6 , 2 ),
    CreditLimitation INT,
    CONSTRAINT PK_CustomerNumber PRIMARY KEY (CustomerNumber),
    CONSTRAINT UC_CustomerAddress UNIQUE (CustomerAddress)
);
CREATE TABLE `AdvancedDatabasesTest`.`Order` (
    OrderNumber INT NOT NULL,
    OrderDate INT,
    CustomerNumber INT NOT NULL,
    CONSTRAINT PK_OrderNumber PRIMARY KEY (OrderNumber),
    CONSTRAINT FK_CustomerNumber FOREIGN KEY (CustomerNumber)
        REFERENCES `AdvancedDatabasesTest`.`Customer` (CustomerNumber)
);
CREATE TABLE `AdvancedDatabasesTest`.`Part` (
    PartNumber VARCHAR(4) NOT NULL,
    PartDescription VARCHAR(32),
    UnitsOnHand INT,
    ItemClass VARCHAR(2),
    WarrantyHighStandardNumber INT,
    UnitPrice DECIMAL(6 , 2 ),
    CONSTRAINT PK_PartNumber PRIMARY KEY (PartNumber)
);
CREATE TABLE `AdvancedDatabasesTest`.`OrderedPart` (
    OrderNumber INT NOT NULL,
    PartNumber VARCHAR(4) NOT NULL,
    NumbersOrdered INT DEFAULT 1,
    QuotationPriced DECIMAL(6 , 2 ),
    CONSTRAINT PK_OrderedPartNumbers PRIMARY KEY (OrderNumber , PartNumber),
    CONSTRAINT FK_OrderNumber FOREIGN KEY (OrderNumber)
        REFERENCES `AdvancedDatabasesTest`.`Order` (OrderNumber),
    CONSTRAINT FK_PartNumber FOREIGN KEY (PartNumber)
        REFERENCES `AdvancedDatabasesTest`.`Part` (PartNumber)
);
-- Question 9
-- Inserting data into Customer table.
INSERT INTO `AdvancedDatabasesTest`.`Customer`
(`CustomerNumber`,
`CustomerName`,
`CustomerAddress`,
`Balance`,
`CreditLimitation`)
VALUES
(124,
'Adams, Sally',
'481 Oak, Lansing',
418.75,
500);
INSERT INTO `AdvancedDatabasesTest`.`Customer`
(`CustomerNumber`,
`CustomerName`,
`CustomerAddress`,
`Balance`,
`CreditLimitation`)
VALUES
(256,
'Samuels, Ann',
'215 Pete, Grant',
10.75,
800);
INSERT INTO `AdvancedDatabasesTest`.`Customer`
(`CustomerNumber`,
`CustomerName`,
`CustomerAddress`,
`Balance`,
`CreditLimitation`)
VALUES
(311,
'Charles, Don',
'48 College, Ira',
200.10,
300);
INSERT INTO `AdvancedDatabasesTest`.`Customer`
(`CustomerNumber`,
`CustomerName`,
`CustomerAddress`,
`Balance`,
`CreditLimitation`)
VALUES
(315,
'Daniels, Tom',
'914 Cherry, Kent',
320.75,
300);
INSERT INTO `AdvancedDatabasesTest`.`Customer`
(`CustomerNumber`,
`CustomerName`,
`CustomerAddress`,
`Balance`,
`CreditLimitation`)
VALUES
(405,
'Wiliams, Al',
'519 Watson, Grant',
201.75,
800);
INSERT INTO `AdvancedDatabasesTest`.`Customer`
(`CustomerNumber`,
`CustomerName`,
`CustomerAddress`,
`Balance`,
`CreditLimitation`)
VALUES
(412,
'Admas, Sally',
'16 Elm, Lansing',
908.75,
1000);
INSERT INTO `AdvancedDatabasesTest`.`Customer`
(`CustomerNumber`,
`CustomerName`,
`CustomerAddress`,
`Balance`,
`CreditLimitation`)
VALUES
(522,
'Nelson, Mary',
'108 Pine, Ada',
49.50,
800);
INSERT INTO `AdvancedDatabasesTest`.`Customer`
(`CustomerNumber`,
`CustomerName`,
`CustomerAddress`,
`Balance`,
`CreditLimitation`)
VALUES
(567,
'Baker, Joe',
'808 Ridge, Harper',
201.20,
300);
INSERT INTO `AdvancedDatabasesTest`.`Customer`
(`CustomerNumber`,
`CustomerName`,
`CustomerAddress`,
`Balance`,
`CreditLimitation`)
VALUES
(587,
'Rogerts, Judy',
'512 Pine, Ada',
57.75,
500);
INSERT INTO `AdvancedDatabasesTest`.`Customer`
(`CustomerNumber`,
`CustomerName`,
`CustomerAddress`,
`Balance`,
`CreditLimitation`)
VALUES
(622,
'Martin, Dan',
'419 Chip, Grant',
575.50,
500);
-- Inserting data into Part table.
INSERT INTO `AdvancedDatabasesTest`.`Part`
(`PartNumber`,
`PartDescription`,
`UnitsOnHand`,
`ItemClass`,
`WarrantyHighStandardNumber`,
`UnitPrice`)
VALUES
('AX12',
'IRON',
104,
'HW',
3,
17.95);
INSERT INTO `AdvancedDatabasesTest`.`Part`
(`PartNumber`,
`PartDescription`,
`UnitsOnHand`,
`ItemClass`,
`WarrantyHighStandardNumber`,
`UnitPrice`)
VALUES
('AZ52',
'SKATES',
20,
'SG',
2,
24.90);
INSERT INTO `AdvancedDatabasesTest`.`Part`
(`PartNumber`,
`PartDescription`,
`UnitsOnHand`,
`ItemClass`,
`WarrantyHighStandardNumber`,
`UnitPrice`)
VALUES
('BA74',
'BASEBALL',
40,
'SG',
1,
4.95);
INSERT INTO `AdvancedDatabasesTest`.`Part`
(`PartNumber`,
`PartDescription`,
`UnitsOnHand`,
`ItemClass`,
`WarrantyHighStandardNumber`,
`UnitPrice`)
VALUES
('BH22',
'TOASTER',
95,
'HW',
3,
34.95);
INSERT INTO `AdvancedDatabasesTest`.`Part`
(`PartNumber`,
`PartDescription`,
`UnitsOnHand`,
`ItemClass`,
`WarrantyHighStandardNumber`,
`UnitPrice`)
VALUES
('BT04',
'STOVE',
11,
'AP',
2,
402.99);
INSERT INTO `AdvancedDatabasesTest`.`Part`
(`PartNumber`,
`PartDescription`,
`UnitsOnHand`,
`ItemClass`,
`WarrantyHighStandardNumber`,
`UnitPrice`)
VALUES
('BZ66',
'WASHER',
52,
'AP',
3,
311.95);
INSERT INTO `AdvancedDatabasesTest`.`Part`
(`PartNumber`,
`PartDescription`,
`UnitsOnHand`,
`ItemClass`,
`WarrantyHighStandardNumber`,
`UnitPrice`)
VALUES
('CA14',
'SKILLET',
2,
'HW',
3,
19.95);
-- Inserting data into Order table.
INSERT INTO `AdvancedDatabasesTest`.`Order`
(`OrderNumber`,
`OrderDate`,
`CustomerNumber`)
VALUES
(12489,
90291,
124);
INSERT INTO `AdvancedDatabasesTest`.`Order`
(`OrderNumber`,
`OrderDate`,
`CustomerNumber`)
VALUES
(12491,
90291,
311);
INSERT INTO `AdvancedDatabasesTest`.`Order`
(`OrderNumber`,
`OrderDate`,
`CustomerNumber`)
VALUES
(12494,
90491,
315);
INSERT INTO `AdvancedDatabasesTest`.`Order`
(`OrderNumber`,
`OrderDate`,
`CustomerNumber`)
VALUES
(12495,
90491,
256);
INSERT INTO `AdvancedDatabasesTest`.`Order`
(`OrderNumber`,
`OrderDate`,
`CustomerNumber`)
VALUES
(12498,
90591,
522);
INSERT INTO `AdvancedDatabasesTest`.`Order`
(`OrderNumber`,
`OrderDate`,
`CustomerNumber`)
VALUES
(12500,
90591,
124);
INSERT INTO `AdvancedDatabasesTest`.`Order`
(`OrderNumber`,
`OrderDate`,
`CustomerNumber`)
VALUES
(12504,
90591,
522);
-- Inserting data into OrderedPart table.
INSERT INTO `AdvancedDatabasesTest`.`OrderedPart`
(`OrderNumber`,
`PartNumber`,
`NumbersOrdered`,
`QuotationPriced`)
VALUES
(12489,
'AX12',
11,
14.95);
INSERT INTO `AdvancedDatabasesTest`.`OrderedPart`
(`OrderNumber`,
`PartNumber`,
`NumbersOrdered`,
`QuotationPriced`)
VALUES
(12491,
'BT04',
1,
402.99);
INSERT INTO `AdvancedDatabasesTest`.`OrderedPart`
(`OrderNumber`,
`PartNumber`,
`NumbersOrdered`,
`QuotationPriced`)
VALUES
(12491,
'BZ66',
1,
311.95);
/* INSERT INTO `AdvancedDatabasesTest`.`OrderedPart`
(`OrderNumber`,
`PartNumber`,
`NumbersOrdered`,
`QuotationPriced`)
VALUES
(12494,
'CB03',
4,
175.00);
INSERT INTO `AdvancedDatabasesTest`.`OrderedPart`
(`OrderNumber`,
`PartNumber`,
`NumbersOrdered`,
`QuotationPriced`)
VALUES
(12495,
'CS11',
2,
57.95); */
INSERT INTO `AdvancedDatabasesTest`.`OrderedPart`
(`OrderNumber`,
`PartNumber`,
`NumbersOrdered`,
`QuotationPriced`)
VALUES
(12498,
'AZ52',
2,
22.95);
INSERT INTO `AdvancedDatabasesTest`.`OrderedPart`
(`OrderNumber`,
`PartNumber`,
`NumbersOrdered`,
`QuotationPriced`)
VALUES
(12498,
'BA74',
4,
4.95);
INSERT INTO `AdvancedDatabasesTest`.`OrderedPart`
(`OrderNumber`,
`PartNumber`,
`NumbersOrdered`,
`QuotationPriced`)
VALUES
(12500,
'BT04',
1,
502.99);
/* INSERT INTO `AdvancedDatabasesTest`.`OrderedPart`
(`OrderNumber`,
`PartNumber`,
`NumbersOrdered`,
`QuotationPriced`)
VALUES
(12504,
'CZ81',
2,
108.99); */
-- Disabling Safe Update Mode
SET SQL_SAFE_UPDATES = 0;
-- Question 10
DELETE FROM `AdvancedDatabasesTest`.`Part`
WHERE
    `PartDescription` = 'IRON';
-- Error Code: 1451. Cannot delete or update a parent row
-- Enabling Safe Update Mode
SET SQL_SAFE_UPDATES = 1;
-- Question 11
UPDATE `AdvancedDatabasesTest`.`Customer`
SET
    `CustomerAddress` = '210 Watson, Grant'
WHERE
    `CustomerNumber` = 522;
-- Disabling Safe Update Mode
SET SQL_SAFE_UPDATES = 0;
-- Question 12
DELETE FROM `AdvancedDatabasesTest`.`Customer`
WHERE
    `CustomerName` = 'Rogerts, Judy';
-- Enabling Safe Update Mode
SET SQL_SAFE_UPDATES = 1;