eB3mtJEN

1DROP SCHEMA IF EXISTS `insert_mail`;
2
3CREATE SCHEMA `insert_mail`;
4
5CREATE TABLE insert_mail.`zipcodes` (
6   `zip` int(11) not null,
7   `city` varchar(30) not null,
8   PRIMARY KEY (zip)
9);
10CREATE TABLE insert_mail.`parts` (
11   `pno` int(11) not null,
12   `pname` varchar(30) not null,
13   `qoh` int(30) not null,
14   `price` int(11) not null,
15   `olevel` int(11) not null,
16   PRIMARY KEY (pno)
17);
18CREATE TABLE insert_mail.`employees` (
19   `eno` int(11) not null,
20   `ename` varchar(30) not null,
21   `zip` int(11) not null,
22   `hdate` varchar(30) not null,
23   PRIMARY KEY (eno),
24   FOREIGN KEY (zip) REFERENCES zipcodes(zip) 
25);
26CREATE TABLE insert_mail.`customers` (
27   `cno` int(11) not null,
28   `cname` varchar(30) not null,
29   `street` varchar(30) not null,
30   `zip` int(11) not null,
31   `phone` varchar(30) not null,
32   PRIMARY KEY (cno),
33   FOREIGN KEY (zip) REFERENCES zipcodes(zip) 
34);
35CREATE TABLE insert_mail.`orders` (
36   `ono` int(11) not null,
37   `cno` int(11) not null,
38   `eno` int(11) not null,
39   `received` varchar(11) not null,
40   `shipped` varchar(30) not null,
41   PRIMARY KEY (ono),
42   FOREIGN KEY (cno) REFERENCES customers(cno),
43   FOREIGN KEY (eno) REFERENCES customers(eno) 
44);
45CREATE TABLE insert_mail.`odetails` (
46   `ono` int(11) not null,
47   `pno` int(11) not null,
48   `qty` int(11) not null,
49   FOREIGN KEY (eno) REFERENCES customers(eno),
50   FOREIGN KEY (pno) REFERENCES parts(pno)
51);