Description
1. Alter table employees to add columns
a. reportsTo, integer, not null, foreign key to the primary key of table employees and will on update cascade and on delete cascade
b. officeID, integer, not null, foreign key to the primary key of table offices and will on update cascade and on delete cascade
2. Alter table customers to add column
a. salesRepEmployeeID, integer, not null, foreign key to the primary key of table employees and will on update cascade and on delete cascade
3. Alter table orders to add column
a. customerID, integer, not null, foreign key to the primary key of table customers and will on update cascade and on delete cascade
4. Alter table products to add column
a. productLineID, integer, not null, foreign key to the primary key of table productLines and will on update cascade and on delete cascade
SHOW DATABASES;
USE transportation;
ALTER TABLE customers
ADD COLUMN salesRepEmployeeID int NOT NULL,
ADD FOREIGN KEY (salesRepEmployeeID)
REFERENCES employees(ID)
ON UPDATE CASCADE
ON DELETE CASCADE;
ALTER TABLE employees
ADD COLUMN reportsTo int NOT NULL,
ADD FOREIGN KEY (reportsTo)
REFERENCES employees(ID)
ON UPDATE CASCADE
ON DELETE CASCADE;
ALTER TABLE employees
ADD COLUMN officeID int NOT NULL,
ADD FOREIGN KEY (officeID)
REFERENCES offices(ID)
ON UPDATE CASCADE
ON DELETE CASCADE;
ALTER TABLE orders
ADD COLUMN customerID int NOT NULL,
ADD FOREIGN KEY (customerID)
REFERENCES customers(ID)
ON UPDATE CASCADE
ON DELETE CASCADE;
ALTER TABLE products
ADD COLUMN productLineID int NOT NULL,
ADD FOREIGN KEY (productLineID)
REFERENCES productLines(ID)
ON UPDATE CASCADE
ON DELETE CASCADE;