vZs1FCLe

1/* --------------------------------------------
2	PROBLEM 1 
3----------------------------------------------- */
4DROP TABLE IF EXISTS dorm_room CASCADE;
5DROP TABLE IF EXISTS student CASCADE;
6
7CREATE TABLE dorm_room (
8	building TEXT UNIQUE,
9    number INT PRIMARY KEY,
10    dorm_room_building TEXT UNIQUE,
11    dorm_room_number INT UNIQUE
12);
13
14CREATE TABLE student (
15    id INT PRIMARY KEY,
16    first_name TEXT NOT NULL,
17    last_name TEXT NOT NULL,
18    dorm_room_building TEXT,
19    dorm_room_number INT,
20    FOREIGN KEY (dorm_room_building)
21    	REFERENCES dorm_room(dorm_room_building)
22    	ON DELETE CASCADE
23);
24
25/* --------------------------------------------
26	PROBLEM 2
27----------------------------------------------- */
28DROP TABLE IF EXISTS workstation CASCADE;
29DROP TABLE IF EXISTS developer CASCADE;
30
31CREATE TABLE workstation (
32    hostname TEXT PRIMARY KEY UNIQUE
33);
34
35CREATE TABLE developer (
36    first_name TEXT,
37    last_name TEXT,
38    hostname TEXT NOT NULL,
39    PRIMARY KEY(first_name, last_name),
40    FOREIGN KEY (hostname)
41    	REFERENCES workstation(hostname)
42    	ON DELETE CASCADE
43);
44
45/* --------------------------------------------
46	PROBLEM 3
47----------------------------------------------- */
48DROP TABLE IF EXISTS pupil CASCADE;
49DROP TABLE IF EXISTS assigned_seat CASCADE;
50
51CREATE TABLE assigned_seat (
52    number INT,
53    assigned_seat_number INT PRIMARY KEY
54    /* PRIMARY KEY(number, assigned_seat_number) */
55);
56
57CREATE TABLE pupil (
58	id INT PRIMARY KEY,
59    name TEXT,
60    assigned_seat_number INT,
61    FOREIGN KEY (assigned_seat_number)
62    	REFERENCES assigned_seat(assigned_seat_number)
63    	ON DELETE CASCADE
64);
65
66/* --------------------------------------------
67	PROBLEM 4
68----------------------------------------------- */
69DROP TABLE IF EXISTS artist CASCADE;
70DROP TABLE IF EXISTS painting CASCADE;
71
72CREATE TABLE artist (
73   name TEXT PRIMARY KEY,
74   year_born INT NOT NULL,
75   year_died INT
76);
77 
78CREATE TABLE painting (
79   id INT PRIMARY KEY,
80   name TEXT,
81   artist_name TEXT,
82   FOREIGN KEY (artist_name)
83   		REFERENCES artist(name)
84    	ON DELETE CASCADE
85);
86
87/* --------------------------------------------
88	PROBLEM 5
89----------------------------------------------- */
90DROP TABLE IF EXISTS dorm_resident CASCADE;
91
92CREATE TABLE dorm_resident (
93   id INT PRIMARY KEY,
94   first_name TEXT NOT NULL,
95   last_name TEXT NOT NULL,
96   room_number INT,
97   resident_assistant INT,
98   FOREIGN KEY (resident_assistant)
99   		REFERENCES dorm_resident(id)
100    	ON DELETE CASCADE
101);
102
103/* --------------------------------------------
104	PROBLEM 6
105----------------------------------------------- */
106DROP TABLE IF EXISTS patient CASCADE;
107DROP TABLE IF EXISTS prescription CASCADE;
108DROP TABLE IF EXISTS doctor CASCADE;
109DROP TABLE IF EXISTS medication CASCADE;
110
111CREATE TABLE patient (
112   id INT PRIMARY KEY,
113   first_name TEXT,
114   last_name TEXT
115);
116 
117CREATE TABLE prescription (
118   patient_id INT,
119   doctor_id INT,
120   medication_name TEXT,
121   PRIMARY KEY (patient_id, doctor_id, medication_name)
122);
123
124CREATE TABLE doctor (
125	id INT PRIMARY KEY,
126    last_name TEXT
127);
128
129CREATE TABLE medication (
130	name TEXT PRIMARY KEY  
131);