SHARE
TWEET

exampledb

a guest Oct 10th, 2019 88 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP TABLE IF EXISTS department;
  2. CREATE TABLE department (
  3.   dname        VARCHAR(25) NOT NULL,
  4.   dno          INTEGER,
  5.   mgrssn       CHAR(9) NOT NULL,
  6.   mgrstartdate DATE,
  7.   PRIMARY KEY (dno),
  8.   UNIQUE (dname)
  9. );
  10.  
  11.  
  12. DROP TABLE IF EXISTS employee;
  13. CREATE TABLE employee (
  14.   fname    VARCHAR(15) NOT NULL,
  15.   minit    VARCHAR(1),
  16.   lname    VARCHAR(15) NOT NULL,
  17.   ssn      CHAR(9),
  18.   bdate    DATE,
  19.   address  VARCHAR(50),
  20.   gender      CHAR,
  21.   salary   DECIMAL(10,2),
  22.   superssn CHAR(9),
  23.   dno      INTEGER,
  24.   PRIMARY KEY (ssn)
  25. );
  26.  
  27.  
  28. DROP TABLE IF EXISTS project;
  29. CREATE TABLE project (
  30.   pname      VARCHAR(25) NOT NULL,
  31.   pno        INTEGER,
  32.   plocation  VARCHAR(15),
  33.   dno        INTEGER NOT NULL,
  34.   PRIMARY KEY (pno),
  35.   UNIQUE (pname)
  36. );
  37.  
  38.  
  39. DROP TABLE IF EXISTS dept_locations;
  40. CREATE TABLE dept_locations (
  41.   dno       INTEGER,
  42.   dlocation VARCHAR(15),
  43.   PRIMARY KEY (dno,dlocation)
  44. );
  45.  
  46.  
  47. DROP TABLE IF EXISTS dependent;
  48. CREATE TABLE dependent (
  49.   essn           CHAR(9),
  50.   depname VARCHAR(15),
  51.   gender            CHAR,
  52.   bdate          DATE,
  53.   relationship   VARCHAR(8),
  54.   PRIMARY KEY (essn,depname)
  55. );
  56.  
  57.  
  58. DROP TABLE IF EXISTS works_on;
  59. CREATE TABLE works_on (
  60.   ssn   CHAR(9),
  61.   pno    INTEGER,
  62.   hours  DECIMAL(4,1),
  63.   PRIMARY KEY (ssn,pno)
  64. );
  65.  
  66. ALTER TABLE employee ADD CONSTRAINT fke FOREIGN KEY(dno) REFERENCES department(dno);   
  67. ALTER TABLE employee ADD CONSTRAINT fkessn FOREIGN KEY(superssn) REFERENCES employee(ssn);
  68. ALTER TABLE project ADD CONSTRAINT fkpno FOREIGN KEY(dno) REFERENCES department(dno);  
  69. ALTER TABLE dept_locations ADD CONSTRAINT fkdeptpk FOREIGN KEY(dno) REFERENCES department(dno);
  70. ALTER TABLE dependent ADD CONSTRAINT fkdepefk FOREIGN KEY(essn) REFERENCES employee(ssn);  
  71. ALTER TABLE works_on ADD CONSTRAINT fkwonem FOREIGN KEY(ssn) REFERENCES employee(ssn)
  72. ALTER TABLE works_on ADD CONSTRAINT fkwonpr FOREIGN KEY(pno) REFERENCES project(pno);
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top