SfcnNZEU

1CREATE DATABASE IF NOT EXISTS albu_andrei;
2use albu_andrei ;
3
4CREATE TABLE IF NOT EXISTS Manager
5(
6staffNo int,
7PRIMARY KEY(`staffNo`)
8);
9
10CREATE TABLE IF NOT EXISTS Staff
11(
12staffNo int,
13supervisorStaffNo int,
14branchNo int,
15PRIMARY KEY(`staffNo`)
16);
17
18CREATE TABLE IF NOT EXISTS Branch
19(
20branchNo int,
21mgrStaffNo int,
22PRIMARY KEY(`branchNo`)
23);
24
25CREATE TABLE IF NOT EXISTS Telephone
26(
27telNo int,
28branchNo int,
29PRIMARY KEY(`telNo`)
30);
31
32CREATE TABLE IF NOT EXISTS Registration
33(
34clientNo int,
35branchNo int,
36staffNo int,
37PRIMARY KEY(`clientNo`)
38);
39
40CREATE TABLE IF NOT EXISTS Client
41(
42clientNo int,
43PRIMARY KEY(`clientNo`)
44);
45
46CREATE TABLE IF NOT EXISTS Lease
47(
48leaseNo int,
49clientNo int,
50propertyNo int,
51PRIMARY KEY(`leaseNo`)
52);
53
54CREATE TABLE IF NOT EXISTS Viewing
55(
56clientNo int,
57propertyNo int,
58PRIMARY KEY(`clientNo`, `propertyNo`)
59);
60
61CREATE TABLE IF NOT EXISTS PropertyForRent
62(
63propertyNo int,
64ownerNo int,
65staffNo int,
66branchNo int,
67PRIMARY KEY(`propertyNo`)
68);
69
70CREATE TABLE IF NOT EXISTS PrivateOwner
71(
72ownerNo int,
73PRIMARY KEY(`ownerNo`)
74);
75
76CREATE TABLE IF NOT EXISTS BusinessOwner
77(
78ownerNo int,
79PRIMARY KEY(`ownerNo`)
80);
81
82CREATE TABLE IF NOT EXISTS Advert
83(
84propertyNo int,
85newspaperName char(20),
86dateAdvert int,
87PRIMARY KEY(`propertyNo`, `newspaperName`, `dateAdvert`)
88);
89
90CREATE TABLE IF NOT EXISTS Newspaper
91(
92newspaperName char(20),
93PRIMARY KEY(`newspaperName`)
94);
95
96ALTER TABLE branch
97ADD CONSTRAINT Manages
98FOREIGN KEY (mgrStaffNo) REFERENCES Manager(staffNo);
99
100ALTER TABLE Manager
101ADD CONSTRAINT IS_A
102FOREIGN KEY (staffNo) REFERENCES Staff(staffNo);
103
104ALTER TABLE Staff
105ADD CONSTRAINT Supervises
106FOREIGN KEY (supervisorStaffNo) REFERENCES Staff(staffNo);
107
108ALTER TABLE Staff
109ADD CONSTRAINT Has
110FOREIGN KEY (branchNo) REFERENCES Branch(branchNo);
111
112ALTER TABLE Telephone
113ADD CONSTRAINT Provides
114FOREIGN KEY (branchNo) REFERENCES Branch(branchNo);
115
116ALTER TABLE Registration
117ADD CONSTRAINT Registers
118FOREIGN KEY (branchNo) REFERENCES Branch(branchNo);
119
120ALTER TABLE Registration
121ADD CONSTRAINT Processes
122FOREIGN KEY (staffNo) REFERENCES Staff(staffNo);
123
124ALTER TABLE Registration
125ADD CONSTRAINT Agrees
126FOREIGN KEY (clientNo) REFERENCES Client(clientNo);
127
128ALTER TABLE Lease
129ADD CONSTRAINT Holds
130FOREIGN KEY (clientNo) REFERENCES Client(clientNo);
131
132ALTER TABLE Viewing
133ADD CONSTRAINT Requests
134FOREIGN KEY (clientNo) REFERENCES Client(clientNo);
135
136ALTER TABLE Viewing
137ADD CONSTRAINT Takes
138FOREIGN KEY (propertyNo) REFERENCES PropertyForRent(propertyNo);
139
140ALTER TABLE Lease
141ADD CONSTRAINT LeasedBy
142FOREIGN KEY (propertyNo) REFERENCES PropertyForRent(propertyNo);
143
144ALTER TABLE PropertyForRent
145ADD CONSTRAINT Oversees
146FOREIGN KEY (staffNo) REFERENCES Staff(staffNo);
147
148ALTER TABLE PropertyForRent
149ADD CONSTRAINT Offers
150FOREIGN KEY (branchNo) REFERENCES Branch(branchNo);
151
152ALTER TABLE PropertyForRent
153ADD CONSTRAINT POwns
154FOREIGN KEY (ownerNo) REFERENCES PrivateOwner(ownerNo);
155
156ALTER TABLE PropertyForRent
157ADD CONSTRAINT BOwns
158FOREIGN KEY (ownerNo) REFERENCES BusinessOwner(ownerNo);
159
160ALTER TABLE Advert
161ADD CONSTRAINT PlacedIn
162FOREIGN KEY (propertyNo) REFERENCES PropertyForRent(propertyNo);
163
164ALTER TABLE Advert
165ADD CONSTRAINT Display
166FOREIGN KEY (newspaperName) REFERENCES Newspaper(newspaperName);