Company Database

mysql mysql -u root -p
CREATE DATABASE WORK;
USE WORK;
CREATE TABLE employee(
    fname           VARCHAR(15)     NOT NULL,
    minit           CHAR,
    lname           VARCHAR(15)     NOT NULL,
    ssn         CHAR(9)         NOT NULL,
    bdate           DATE,
    address         VARCHAR(30),
    sex         CHAR,
    salary          DECIMAL(10, 2),
    super_ssn       CHAR(9),
    dno         INT         NOT NULL,
    PRIMARY KEY(ssn)
);

CREATE TABLE department(
    dname           VARCHAR(15)         NOT NULL,
    dnumber         INT             NOT NULL,
    mgr_ssn         CHAR(9)         NOT NULL,
    mgr_start_date  DATE,
    UNIQUE(dname),
    PRIMARY KEY(dnumber),
    FOREIGN KEY(mgr_ssn) REFERENCES employee(ssn)
);

CREATE TABLE dept_locations(
    dnumber         INT             NOT NULL,
    dlocation       VARCHAR(15)         NOT NULL,
    PRIMARY KEY(dnumber, dlocation),
    FOREIGN KEY(dnumber) REFERENCES department(dnumber)
);

CREATE TABLE project(
    pname           VARCHAR(15)     NOT NULL    PRIMARY KEY,
    pnumber         INT             NOT NULL    UNIQUE,
    plocation       VARCHAR(15),
    dnum            INT             NOT NULL,
    FOREIGN KEY(dnum) REFERENCES department(dnumber)
);

CREATE TABLE works_on(
    essn            CHAR(9)         NOT NULL,
    pno             INT             NOT NULL,
    hours           DECIMAL(3,1)        NOT NULL,
    PRIMARY KEY(essn, pno),
    FOREIGN KEY(essn) REFERENCES employee(ssn),
    FOREIGN KEY(pno) REFERENCES project(pnumber)
);

CREATE TABLE dependent(
    essn            CHAR(9)         NOT NULL,
    dependent_name      VARCHAR(15)         NOT NULL,
    sex             CHAR,
    bdate           DATE,
    relationship        VARCHAR(8),
    PRIMARY KEY(essn, dependent_name),
    FOREIGN KEY(essn) REFERENCES employee(ssn)
);

INSERT INTO employee(fname, minit, lname, ssn, bdate, address, sex, salary, super_ssn, dno)
VALUES
("John", "B", "Smith", 123456789, "1965-01-09", "731 Fonden, Houston, TX", "M", 30000, 333445555, 5),
("Franklin", "T", "Wong", 333445555, "1955-12-08", "638 Voss, Houston, TX", "M", 40000, 888665555, 5),
("Alicia", "J", "Zelaya", 999887777, "1968-01-19", "3321 Castle, Spring, TX", "F", 25000, 987654321, 4),
("Jennifer", "S", "Wallace", 987654321, "1941-06-20", "291 Berry, Bellaire, TX", "F", 43000, 888665555, 4),
("Ramesh", "K", "Narayan", 666884444, "1962-09-15", "975 Fire Oak, Humble, TX", "M", 38000, 333445555, 5),
("Joyce", "A", "English", 453453453, "1972-07-31", "5631 Rice, Houston, TX", "M", 25000, 333445555, 5),
("Ahmad", "V", "Jabbar", 987987987, "1969-03-29", "980 Dallas, Houston, TX", "M", 25000, 987654321, 4),
("James", "E", "Borg", 888665555, "1937-11-10", "450 Stone, Houston, TX", "M", 55000, NULL, 1);

INSERT INTO department(dname, dnumber, mgr_ssn, mgr_start_date)
VALUES
("Research", 5, 333445555, "1988-05-22"),
("Administration", 4, 987654321, "1995-01-01"),
("Headquarters", 1, 888665555, "1981-06-19");

ALTER TABLE employee ADD FOREIGN KEY(super_ssn) REFERENCES employee(ssn);
ALTER TABLE employee ADD FOREIGN KEY(dno) REFERENCES department(dnumber);

INSERT INTO dept_locations(dnumber, dlocation)
VALUES
(1, "Houston"),
(4, "Stafford"),
(5, "Bellaire"),
(5, "Sugarland"),
(5, "Houston");

INSERT INTO project(pname, pnumber, plocation, dnum)
VALUES
("ProductX", 1, "Bellaire", 5),
("ProductY", 2, "Sugarland", 5),
("ProductZ", 3, "Houston", 5),
("Computerization", 10, "Stafford", 4),
("Reorganization", 20, "Houston", 1),
("Newbenefits", 30, "Stafford", 4);

INSERT INTO works_on(essn, pno, hours)
VALUES
(123456789, 1, 32.5),
(123456789, 2, 7.5),
(666884444, 3, 40.0),
(453453453, 1, 20.0),
(453453453, 2, 20.0),
(333445555, 2, 10.0),
(333445555, 3, 10.0),
(333445555, 10, 10.0),
(333445555, 20, 10.0),
(999887777, 30, 30.0),
(999887777, 10, 10.0),
(987987987, 10, 35.0),
(987987987, 30, 5.0),
(987654321, 30, 20.0),
(987654321, 20, 15.0),
(888665555, 20, NULL);

INSERT INTO dependent(essn, dependent_name, sex, bdate, relationship)
VALUES
(333445555, "Alice", "F", "1986-04-05", "Daughter"),
(333445555, "Theodore", "M", "1983-10-25", "Son"),
(333445555, "Joy", "F", "1958-05-03", "Spouse"),
(987654321, "Abner", "M", "1942-02-28", "Spouse"),
(123456789, "Michael", "M", "1988-01-04", "Son"),
(123456789, "Alice", "F", "1988-12-30", "Daughter"),
(123456789, "Elizabeth", "F", "1967-05-05", "Spouse");