6ck7SdAQ

1drop database if exists company;
2
3
4create database company;
5use company;
6
7create table employee (
8fname varchar(10),
9minit char(1),
10lname varchar(420) not null unique,
11ssn integer,
12bdate date,
13address varchar(420),
14sex char(1),
15salary float,
16superssn integer,
17dno integer,
18primary key(ssn),
19constraint foreign key (superssn) references employee(ssn),
20constraint foreign key (dno) references department(dnumber));
21
22create table department (
23dname varchar(10),
24dnumber integer,
25mgrssn integer,
26mgrstartdate date,
27primary key(dnumber),
28constraint foreign key (mgrssn) references employee(ssn));
29
30create table dept_locations (
31dnumber integer,
32dlocation varchar(420),
33primary key(dnumber, dlocation),
34constraint foreign key (dnumber) references department(dnumber));
35
36create table  project (
37pname varchar(420),
38pnumber integer,
39plocation varchar(420),
40dnum integer,
41primary key(pnumber));
42
43create table  works_on (
44essn integer,
45pno integer,
46hours float,
47primary key(essn, pno),
48constraint foreign key (essn) references employee(ssn),
49constraint foreign key (pno) references project(pnumber));
50
51create table dependent (
52essn integer,
53dependent_name varchar(420),
54sex char(1),
55bdate date,
56relationship char(69),
57primary key(essn, dependent_name),
58constraint foreign key (essn) references employee(ssn));