R16RRfp9

1DROP SCHEMA IF EXISTS manaola;
2create database manaola;
3
4use manaola;
5
6CREATE TABLE designers
7(
8    id INT AUTO_INCREMENT,
9    ssn char(10) UNIQUE NOT NULL,
10    last_name VARCHAR(50) NOT NULL,
11    first_name VARCHAR(50) NOT NULL,
12    PRIMARY KEY (id)
13);
14
15CREATE TABLE fashion_shows
16(
17    id INT AUTO_INCREMENT,
18    location VARCHAR(255) NOT NULL,
19    date DATETIME NOT NULL,
20    PRIMARY KEY (id)
21);
22
23CREATE TABLE show_designers
24(
25    designer_id INT NOT NULL,
26    show_id INT NOT NULL,
27    PRIMARY KEY (designer_id, show_id),
28    FOREIGN KEY (designer_id) REFERENCES designers(id),
29    FOREIGN KEY (show_id) REFERENCES fashion_shows(id)
30);
31
32CREATE TABLE customers
33(
34    id INT AUTO_INCREMENT,
35    name VARCHAR(255) NOT NULL,
36    email VARCHAR(255) NOT NULL ,
37    phone char(10) NOT NULL ,
38    PRIMARY KEY (id)
39);
40
41CREATE TABLE tailoring_technicians
42(
43    id INT AUTO_INCREMENT,
44    ssn CHAR(10) UNIQUE NOT NULL,
45    email VARCHAR(255) NOT NULL,
46    last_name VARCHAR(50) NOT NULL,
47    first_name VARCHAR(50) NOT NULL,
48    PRIMARY KEY (id)
49);
50
51CREATE TABLE outfits
52(
53    id INT AUTO_INCREMENT,
54    size VARCHAR(3),
55    price NUMERIC,
56    date_completed DATE,
57    designer_id INT NOT NULL,
58    customer_id INT NOT NULL ,
59    PRIMARY KEY (id),
60    FOREIGN KEY (designer_id) REFERENCES designers(id),
61    FOREIGN KEY (customer_id) REFERENCES customers(id)
62);
63
64CREATE TABLE outfit_jobs
65(
66    tailor_id INT NOT NULL ,
67    outfit_id INT NOT NULL ,
68    date DATE,
69    PRIMARY KEY (tailor_id, outfit_id),
70    FOREIGN KEY (tailor_id) REFERENCES tailoring_technicians(id),
71    FOREIGN KEY (outfit_id) REFERENCES outfits(id)
72);
73
74CREATE TABLE fabrics
75(
76    id INT NOT NULL,
77    fabric_name VARCHAR(255) NOT NULL,
78    PRIMARY KEY (id)
79);
80
81CREATE TABLE outfit_fabrics
82(
83    fabric_id INT NOT NULL,
84    outfit_id INT NOT NULL,
85    PRIMARY KEY (fabric_id, outfit_id),
86    FOREIGN KEY (fabric_id) REFERENCES fabrics(id),
87    FOREIGN KEY (outfit_id) REFERENCES outfits(id)
88);