PuFg9DLr

1DROP DATABASE IF EXISTS online_dating;
2CREATE DATABASE online_dating;
3USE online_dating;
4
5CREATE TABLE registeredPeople
6(
7	id INT AUTO_INCREMENT PRIMARY KEY,
8    name VARCHAR(255) NOT NULL,
9    sex CHAR(1) NOT NULL,
10    relationshipStatus VARCHAR(20) NOT NULL,
11    eyeColor VARCHAR(20) NOT NULL,
12    hairColor VARCHAR(20) NOT NULL,
13    skinColor VARCHAR(20) NOT NULL,
14    height DOUBLE NOT NULL,
15    weight DOUBLE NOT NULL,
16    education VARCHAR(50) NOT NULL,
17    occupation VARCHAR(50) NOT NULL,
18    interests VARCHAR(255) NOT NULL
19);
20
21CREATE TABLE meetups
22(
23	personA_id INT NOT NULL,
24    CONSTRAINT FOREIGN KEY (personA_id) REFERENCES registeredPeople(id),
25    personB_id INT NOT NULL,
26    CONSTRAINT FOREIGN KEY (personB_id) REFERENCES registeredPeople(id),
27    location VARCHAR(50) NOT NULL,
28    length TIME NOT NULL,
29    haveMetAgain BOOLEAN NOT NULL,
30    haveMarried BOOLEAN DEFAULT FALSE,
31    PRIMARY KEY(personA_id, personB_id)
32);
33
34CREATE TABLE weddings
35(
36	id INT AUTO_INCREMENT PRIMARY KEY,
37    union_sideA_id INT NOT NULL,
38    CONSTRAINT FOREIGN KEY (union_sideA_id) REFERENCES meetups(personA_id),
39    union_sideB_id INT NOT NULL,
40    CONSTRAINT FOREIGN KEY(union_sideB_id) REFERENCES meetups(personB_id),
41    location VARCHAR(255) NOT NULL
42);
43
44CREATE TABLE guests
45(
46	wedding_id INT NOT NULL,
47    CONSTRAINT FOREIGN KEY (wedding_id) REFERENCES weddings(id),
48    guest_id INT NOT NULL,
49    CONSTRAINT FOREIGN KEY(guest_id) REFERENCES registeredPeople(id)
50);