From e09aa90e0e388d4c42e38381961bc3fa1771cff3 Mon Sep 17 00:00:00 2001
From: Nguyễn Gia Phong <mcsinyx@disroot.org>
Date: Wed, 8 Jul 2020 14:11:07 +0700
Subject: [usth/ICT2.{5,12}] Process images and base data

---
 usth/ICT2.5/practical/1.sql | 58 +++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 58 insertions(+)
 create mode 100644 usth/ICT2.5/practical/1.sql

(limited to 'usth/ICT2.5/practical/1.sql')

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;
-- 
cgit 1.4.1