diff options
Diffstat (limited to 'usth/ICT2.5/practical/1.sql')
-rw-r--r-- | usth/ICT2.5/practical/1.sql | 58 |
1 files changed, 58 insertions, 0 deletions
diff --git a/usth/ICT2.5/practical/1.sql b/usth/ICT2.5/practical/1.sql new file mode 100644 index 0000000..7fb1394 --- /dev/null +++ b/usth/ICT2.5/practical/1.sql @@ -0,0 +1,58 @@ +CREATE DATABASE Company; +USE Company; + +CREATE TABLE employee ( + fname char(255), + minit char(255), + lname char(255), + ssn int(9) PRIMARY KEY, + bdate date, + address char(255), + sex bigint(255), + salary int(9), + super_ssn int(9), + dno int(9), + FOREIGN KEY (super_ssn) REFERENCES employee(ssn)); + +CREATE TABLE department ( + dname char(255), + dnumber int(9) PRIMARY KEY, + mgr_ssn int(9), + mgr_start_date date, + FOREIGN KEY (mgr_ssn) REFERENCES employee(ssn)); + +ALTER TABLE employee ADD FOREIGN KEY (dno) REFERENCES department(dnumber); + +CREATE TABLE dept_locations ( + dnumber int(9), + dlocation char(255), + PRIMARY KEY (dnumber, dlocation), + FOREIGN KEY (dnumber) REFERENCES department(dnumber)); + +CREATE TABLE project ( + pname char(255), + pnumber int(9) PRIMARY KEY, + plocation char(255), + dnum int(9), + FOREIGN KEY (dnum) REFERENCES department(dnumber)); + +CREATE TABLE works_on ( + essn int(9), + pno int(9), + hours int(9), + PRIMARY KEY (essn, pno), + FOREIGN KEY (essn) REFERENCES employee(ssn), + FOREIGN KEY (pno) REFERENCES project(pnumber)); + +CREATE TABLE dependant ( + essn int(9), + dependant_name char(255), + sex bigint(255), + bdate date, + relationship char(255), + PRIMARY KEY (essn, dependant_name), + FOREIGN KEY (essn) REFERENCES employee(ssn)); + +ALTER TABLE employee ADD partner_ssn int(9); + +DROP DATABASE Company; |