Test Script for ECE356 Midterm

q2.sql

drop table if exists SPP;
drop table if exists Sup;
drop table if exists Part;
drop table if exists Proj;

CREATE TABLE Sup (
    sup_num int,
    sup_name varchar(25),
    city varchar(25),
    primary key (sup_num)
);

CREATE TABLE Part (
    part_num int,
    part_name varchar(12),
    color varchar(5),
    weight int,
    city varchar(8),
    primary key (part_num)
);

CREATE TABLE Proj (
    proj_num int,
    proj_name varchar(10),
    city varchar(8),
    primary key (proj_num)
);

CREATE TABLE SPP (
    sup_num int,
    part_num int,
    proj_num int,
    qty int,
    foreign key (sup_num)
        references Sup (sup_num),
    foreign key (part_num)
        references Part (part_num),
    foreign key (proj_num)
        references Proj (proj_num)
);

INSERT INTO Sup
	(`sup_num`, `sup_name`, `city`)
VALUES
	(1, 'acme', 'waterloo'),
	(2, 'fedex', 'ottawa'),
	(3, 'ups', 'toronto'),
	(4, 'microsoft', 'waterloo')
;
	
INSERT INTO Part
	(`part_num`, `part_name`, `color`, `weight`, `city`)
VALUES
	(1, 'bolt', 'gray', 1, 'waterloo'),
	(2, 'wretch', 'black', 2, 'toronto'),
	(3, 'drill', 'red', 5, 'waterloo'),
	(4, 'screw driver', 'green', 2, 'ottawa')
;

INSERT INTO Proj
	(`proj_num`, `proj_name`, `city`)
VALUES
	(1, 'skynet', 'waterloo'),
	(2, 'death star', 'toronto')
;

INSERT INTO SPP
	(`sup_num`, `part_num`, `proj_num`, `qty`)
VALUES
	(1, 2, 1, 1),
	(1, 1, 1, 4),
	(1, 3, 1, 3),
	(1, 4, 1, 4),
	(2, 3, 2, 4),
	(2, 2, 2, 9)
;