Start to add foreign keys

1 files changed, 22 insertions, 23 deletions

diff --git a/labs/2/run.sql b/labs/2/run.sql
index 01355ae..8027009 100644
--- a/labs/2/run.sql
+++ b/labs/2/run.sql
@@ -3,7 +3,7 @@ CREATE TABLE employees (
   fname varchar(255),
   minit varchar(1),
   lname varchar(255),
-  sin varchar(9),
+  sin varchar(9) primary key,
   bdate date,
   address varchar(255),
   gender varchar(1),
@@ -11,28 +11,14 @@ CREATE TABLE employees (
   dno integer
 );
 
-INSERT INTO employees VALUES ('Harris', 'T', 'Chomsky', 123, '1965-12-10', 'Edmonton', 'M', 50000, 12);
-INSERT INTO employees VALUES ('Kristian', 'C', 'Bohr' , 456, '1975-10-05', 'Ottawa', 'M', 28000, 11);
-INSERT INTO employees VALUES ('Charlotte', 'F', 'Bouchard' , 789, '1985-08-06', 'Montreal', 'F', 40000, 11);
-INSERT INTO employees VALUES ('Said', 'J', 'Ahmad', 111, '1970-09-07', 'Toronto', 'M', 30000, 12);
-INSERT INTO employees VALUES ('Andrew', 'U', 'Brahe', 222, '1970-04-02', 'Winnipeg', 'M', 20000, 10);
-INSERT INTO employees VALUES ('Nadia', 'O', 'Mamary', 333, '1960-01-08', 'Saskatoon', 'F', 35000, 10);
-INSERT INTO employees VALUES ('Yuan', 'P', 'Nielsen', 987, '1983-02-27', 'Moncton', 'F', 32000, 11);
-INSERT INTO employees VALUES ('Neil', 'A', 'Dion', 654, '1953-02-27', 'Moncton', 'M', 32000, 11);
-INSERT INTO employees VALUES ('Karen', 'C', 'Ming', 321, '1963-11-16', 'Victoria', 'F', 26000, 12);
-
 DROP TABLE IF EXISTS departments;
 CREATE TABLE departments (
   dname varchar(255),
-  dnumber integer,
+  dnumber integer primary key,
   mgrsin integer,
-  mgrstartdate date,
+  mgrstartdate date
 );
 
-INSERT INTO departments VALUES ('ConsProd', 10, 333, '2004-10-01');
-INSERT INTO departments VALUES ('InduProd', 11, 654, '2005-05-01');
-INSERT INTO departments VALUES ('Research', 12, 111, '2000-06-15');
-
 DROP TABLE IF EXISTS projects;
 CREATE TABLE projects (
   pname varchar(255),
@@ -41,20 +27,33 @@ CREATE TABLE projects (
   dnum integer
 );
 
-INSERT INTO projects VALUES ('Mobile University', 'Ottawa', 10);
-INSERT INTO projects VALUES ('Interactive TV', 'Ottawa', 12);
-INSERT INTO projects VALUES ('Intelligent Agent', 'Athabasca', 11);
-INSERT INTO projects VALUES ('E-Commerce', 'Edmonton', 10);
-INSERT INTO projects VALUES ('Mobile Office', 'Athabasca', 11);
-
 DROP TABLE IF EXISTS locations;
 CREATE TABLE locations (
   dnbr integer,
   dlocation varchar(255)
 );
 
+INSERT INTO employees VALUES ('Harris', 'T', 'Chomsky', 123, '1965-12-10', 'Edmonton', 'M', 50000, 12);
+INSERT INTO employees VALUES ('Kristian', 'C', 'Bohr' , 456, '1975-10-05', 'Ottawa', 'M', 28000, 11);
+INSERT INTO employees VALUES ('Charlotte', 'F', 'Bouchard' , 789, '1985-08-06', 'Montreal', 'F', 40000, 11);
+INSERT INTO employees VALUES ('Said', 'J', 'Ahmad', 111, '1970-09-07', 'Toronto', 'M', 30000, 12);
+INSERT INTO employees VALUES ('Andrew', 'U', 'Brahe', 222, '1970-04-02', 'Winnipeg', 'M', 20000, 10);
+INSERT INTO employees VALUES ('Nadia', 'O', 'Mamary', 333, '1960-01-08', 'Saskatoon', 'F', 35000, 10);
+INSERT INTO employees VALUES ('Yuan', 'P', 'Nielsen', 987, '1983-02-27', 'Moncton', 'F', 32000, 11);
+INSERT INTO employees VALUES ('Neil', 'A', 'Dion', 654, '1953-02-27', 'Moncton', 'M', 32000, 11);
+INSERT INTO employees VALUES ('Karen', 'C', 'Ming', 321, '1963-11-16', 'Victoria', 'F', 26000, 12);
+INSERT INTO departments VALUES ('ConsProd', 10, 333, '2004-10-01');
+INSERT INTO departments VALUES ('InduProd', 11, 654, '2005-05-01');
+INSERT INTO departments VALUES ('Research', 12, 111, '2000-06-15');
+INSERT INTO projects VALUES ('Mobile University', 1, 'Ottawa', 10);
+INSERT INTO projects VALUES ('Interactive TV', 2, 'Ottawa', 12);
+INSERT INTO projects VALUES ('Intelligent Agent', 3, 'Athabasca', 11);
+INSERT INTO projects VALUES ('E-Commerce', 4, 'Edmonton', 10);
+INSERT INTO projects VALUES ('Mobile Office', 5, 'Athabasca', 11);
 INSERT INTO locations VALUES (10, 'Edmonton');
 INSERT INTO locations VALUES (10, 'Ottawa');
 INSERT INTO locations VALUES (11, 'Athabasca');
 INSERT INTO locations VALUES (12, 'Ottawa');
 INSERT INTO locations VALUES (12, 'Montreal');
+
+ALTER TABLE employees ADD CONSTRAINT departments_fk FOREIGN KEY (dno) REFERENCES departments (dnumber);