My week 15 and week 16 assignment with output.

week15.sql

/*By Ryan Jayakody*/

USE sh;

/*Adding 4 part records into part table*/
INSERT INTO part(Partno,Partdesc,Onhand,Partclass,Unitprice) VALUES('1','Bolt for engine 3',3,'AP',50.15);
INSERT INTO part(Partno,Partdesc,Onhand,Partclass,Unitprice) VALUES('2','Bolt for engine 6',2,'KI',30.85);
INSERT INTO part(Partno,Partdesc,Onhand,Partclass,Unitprice) VALUES('3','Bolt for engine 1',6,'HW',20.75);
INSERT INTO part(Partno,Partdesc,Onhand,Partclass,Unitprice) VALUES('4','Bolt for engine 9',0,'SP',70.25);

/*Add 2 salesreps to the salesrep table.*/
INSERT INTO salesrep(Srepno,Srepname,Srepstreet,Srepcity,Srepprov,Sreppcode,Totcomm,Commrate) VALUES
('1','Bob McDonalds','MavisGarden','Mississuaga','ON','L5W3D5',654.67,15.67);
INSERT INTO salesrep(Srepno,Srepname,Srepstreet,Srepcity,Srepprov,Sreppcode,Totcomm,Commrate) VALUES
('2','John Appleseed','Acorn Pl','Mississuaga','ON','L5E5D3',1654.67,15.67);

/*Add yourself and one other fictitious person into the CUSTOMER table.*/
INSERT INTO customer(Custno,Custname,Custstreet,Custcity,Custprov,Custpcode,Disc,Balance,Credlimit,Srepno) VALUES
('1','Ryan Jayakody','ValleyRose Rd','Mississauga','ON','L6W3D4',5.4,23.50,100,'2');
INSERT INTO customer(Custno,Custname,Custstreet,Custcity,Custprov,Custpcode,Disc,Balance,Credlimit,Srepno) VALUES
('2','Ian Mcgruver','Mavis Rd','Mississauga','ON','L6W3F6',1.4,143.30,200,'1');

/*Add 2 orders for yourself - populate both order and orderprod.*/
INSERT INTO orders(Orderno,Orderdate,Custno) VALUES ('00001',2010-12-31,'1');
INSERT INTO orders(Orderno,Orderdate,Custno) VALUES ('00002',2011-02-10,'2');

INSERT INTO orderprod(Orderno,Partno,Orderqty,Orderprice) VALUES ('00001','2',1,58.15);
INSERT INTO orderprod(Orderno,Partno,Orderqty,Orderprice) VALUES ('00002','1',1,38.85);

/*Add 1 invoice for one of your orders - populate both invoice and invprod.*/
INSERT INTO invoice(Invno,Invdate,Orderno) VALUES ('00001',2010-12-31,'00001');
INSERT INTO invoice(Invno,Invdate,Orderno) VALUES ('00002',2010-02-10,'00002');

INSERT INTO invprod(Invno,Partno,Shipqty) VALUES ('00001','1',1);
INSERT INTO invprod(Invno,Partno,Shipqty) VALUES ('00002','2',1);


/* RETURN RESULTS

mysql> USE sh;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT * FROM PART;
+--------+-------------------+--------+-----------+-----------+
| Partno | Partdesc          | Onhand | Partclass | Unitprice |
+--------+-------------------+--------+-----------+-----------+
| 1      | Bolt for engine 3 |      3 | AP        |     50.15 |
| 2      | Bolt for engine 6 |      2 | KI        |     30.85 |
| 3      | Bolt for engine 1 |      6 | HW        |     20.75 |
| 4      | Bolt for engine 9 |      0 | SP        |     70.25 |
+--------+-------------------+--------+-----------+-----------+
4 rows in set (0.02 sec)

mysql> SELECT * FROM salesrep;
+--------+----------------+-------------+-------------+----------+-----------+---------+----------+
| Srepno | Srepname       | Srepstreet  | Srepcity    | Srepprov | Sreppcode | Totcomm | Commrate |
+--------+----------------+-------------+-------------+----------+-----------+---------+----------+
| 1      | Bob McDonalds  | MavisGarden | Mississuaga | ON       | L5W3D5    |  654.67 |     9.99 |
| 2      | John Appleseed | Acorn Pl    | Mississuaga | ON       | L5E5D3    | 1654.67 |     9.99 |
+--------+----------------+-------------+-------------+----------+-----------+---------+----------+
2 rows in set (0.00 sec)

mysql> mysql> SELECT * FROM customer;
+--------+---------------+---------------+-------------+----------+-----------+------+---------+-----------+--------+
| Custno | Custname      | Custstreet    | Custcity    | Custprov | Custpcode | Disc | Balance | Credlimit | Srepno |
+--------+---------------+---------------+-------------+----------+-----------+------+---------+-----------+--------+
| 1      | Ryan Jayakody | ValleyRose Rd | Mississauga | ON       | L6W3D4    |  5.4 |   23.50 |       100 | 2      |
| 2      | Ian Mcgruver  | Mavis Rd      | Mississauga | ON       | L6W3F6    |  1.4 |  143.30 |       200 | 1      |
+--------+---------------+---------------+-------------+----------+-----------+------+---------+-----------+--------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM order;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order' at line 1
mysql> SELECT * FROM orders;
+---------+------------+--------+
| Orderno | Orderdate  | Custno |
+---------+------------+--------+
| 00001   | 0000-00-00 | 1      |
+---------+------------+--------+
1 row in set (0.00 sec)

mysql> SELECT * FROM orderprod;
+---------+--------+----------+------------+
| Orderno | Partno | Orderqty | Orderprice |
+---------+--------+----------+------------+
| 00001   | 2      |        1 |      58.15 |
+---------+--------+----------+------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM invoice;
+-------+------------+---------+
| Invno | Invdate    | Orderno |
+-------+------------+---------+
| 00001 | 0000-00-00 | 00001   |
| 00002 | 0000-00-00 | 00002   |
+-------+------------+---------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM invprod;
+-------+--------+---------+
| Invno | Partno | Shipqty |
+-------+--------+---------+
| 00001 | 1      |       1 |
| 00002 | 2      |       1 |
+-------+--------+---------+
2 rows in set (0.00 sec)

*/

week16.sql

USE sh;

CREATE UNIQUE INDEX pkpart ON part(Partno);
CREATE UNIQUE INDEX pksalesrep ON salesrep(Srepno);
CREATE UNIQUE INDEX fkcustomersalesrep ON customer(Srepno,custno);
CREATE UNIQUE INDEX fkordercust ON orders(Oderno,Custno);
CREATE UNIQUE INDEX fkorderprod ON orderprod(orderno, partno)
CREATE UNIQUE INDEX fkinvorder ON invoice(Invno, Orderno);
CREATE UNIQUE INDEX fkinvprod ON invprod(Invno,Partno);