· 6 years ago · Dec 04, 2019, 02:02 AM
1CREATE SCHEMA IF NOT EXISTS dreamhome;
2SET search_path TO dreamhome,public;
3
4DROP TABLE IF EXISTS branch;
5
6CREATE TABLE branch
7(branchNo char(5) PRIMARY KEY,
8 street varchar(35),
9 city varchar(10),
10 postcode varchar(10)
11);
12
13INSERT INTO branch VALUES('B005','22 Deer Rd','London','SW1 4EH');
14INSERT INTO branch VALUES('B007','16 Argyll St', 'Aberdeen','AB2 3SU');
15INSERT INTO branch VALUES('B003','163 Main St', 'Glasgow','G11 9QX');
16INSERT INTO branch VALUES('B004','32 Manse Rd', 'Bristol','BS99 1NZ');
17INSERT INTO branch VALUES('B002','56 Clover Dr', 'London','NW10 6EU');
18
19DROP TABLE if EXISTS staff;
20
21CREATE TABLE staff
22(staffNo char(5) PRIMARY KEY,
23 fName varchar(10),
24 lName varchar(10),
25 position varchar(10),
26 sex char(1),
27 DOB date,
28 salary int,
29 branchNo char(5)
30);
31
32INSERT INTO staff VALUES('SL21','John','White','Manager','M','1965-10-01',30000,'B005');
33INSERT INTO staff VALUES('SG37','Ann','Beech','Assistant','F','1980-11-10',12000,'B003');
34INSERT INTO staff VALUES('SG14','David','Ford','Supervisor','M','1978-03-24',18000,'B003');
35INSERT INTO staff VALUES('SA9','Mary','Howe','Assistant','F','1990-02-19',9000,'B007');
36INSERT INTO staff VALUES('SG5','Susan','Brand','Manager','F','1960-06-03',24000,'B003');
37INSERT INTO staff VALUES('SL41','Julie','Lee','Assistant','F','1985-06-13',9000,'B005');
38
39DROP TABLE IF EXISTS privateOwner;
40CREATE TABLE privateOwner
41(ownerNo char(5) PRIMARY KEY,
42 fName varchar(10),
43 lName varchar(10),
44 address varchar(50),
45 telNo char(15),
46 email varchar(50),
47 password varchar(40)
48);
49
50INSERT INTO privateOwner VALUES('CO46','Joe','Keogh','2 Fergus Dr. Aberdeen AB2 ','01224-861212', 'jkeogh@lhh.com', null);
51INSERT INTO privateOwner VALUES('CO87','Carol','Farrel','6 Achray St. Glasgow G32 9DX','0141-357-7419', 'cfarrel@gmail.com', null);
52INSERT INTO privateOwner VALUES('CO40','Tina','Murphy','63 Well St. Glasgow G42','0141-943-1728', 'tinam@hotmail.com', null);
53INSERT INTO privateOwner VALUES('CO93','Tony','Shaw','12 Park Pl. Glasgow G4 0QR','0141-225-7025', 'tony.shaw@ark.com', null);
54
55DROP TABLE IF EXISTS propertyForRent;
56CREATE TABLE propertyForRent
57(propertyNo char(5) PRIMARY KEY,
58 street varchar(35),
59 city varchar(10),
60 postcode varchar(10),
61 type varchar(10),
62 rooms smallint,
63 rent int,
64 ownerNo char(5) not null,
65 staffNo char(5),
66 branchNo char(5)
67);
68
69INSERT INTO propertyForRent VALUES('PA14','16 Holhead','Aberdeen','AB7 5SU','House',6,650,'CO46','SA9','B007');
70INSERT INTO propertyForRent VALUES('PL94','6 Argyll St','London','NW2','Flat',4,400,'CO87','SL41','B005' );
71INSERT INTO propertyForRent VALUES('PG4','6 Lawrence St','Glasgow','G11 9QX','Flat',3,350,'CO40', NULL, 'B003');
72INSERT INTO propertyForRent VALUES('PG36','2 Manor Rd','Glasgow','G32 4QX','Flat',3,375,'CO93','SG37','B003' );
73INSERT INTO propertyForRent VALUES('PG21','18 Dale Rd','Glasgow','G12','House',5,600,'CO87','SG37','B003');
74INSERT INTO propertyForRent VALUES('PG16','5 Novar Dr','Glasgow','G12 9AX','Flat',4,450,'CO93','SG14','B003' );
75
76DROP TABLE IF EXISTS client;
77CREATE TABLE client
78(clientNo char(5) PRIMARY KEY,
79 fName varchar(10),
80 lName varchar(10),
81 telNo char(15),
82 prefType varchar(10),
83 maxRent int,
84 email varchar(50)
85);
86
87INSERT INTO client VALUES('CR76','John','Kay','0171-774-5632','Flat',425, 'john.kay@gmail.com');
88INSERT INTO client VALUES('CR56','Aline','Steward','0141-848-1825','Flat',350, 'astewart@hotmail.com');
89INSERT INTO client VALUES('CR74','Mike','Ritchie','01475-943-1728','House',750, 'mritchie@yahoo.co.uk');
90INSERT INTO client VALUES('CR62','Mary','Tregear','01224-196720','Flat',600, 'maryt@hotmail.co.uk');
91
92DROP TABLE IF EXISTS viewing;
93CREATE TABLE viewing
94(clientNo char(5) not null,
95 propertyNo char(5) not null,
96 viewDate date,
97 comment varchar(15)
98);
99INSERT INTO viewing VALUES('CR56','PA14','2015-05-24','too small');
100INSERT INTO viewing VALUES('CR76','PG4','2015-04-20','too remote');
101INSERT INTO viewing VALUES('CR56','PG4','2015-05-26','');
102INSERT INTO viewing VALUES('CR62','PA14','2015-05-14','no dining room');
103INSERT INTO viewing VALUES('CR56','PG36','2015-04-28','');
104
105DROP TABLE IF EXISTS registration;
106CREATE TABLE registration
107(clientNo char(5) not null,
108 branchNo char(5) not null,
109 staffNo char(5) not null,
110 dateJoined date
111);
112
113INSERT INTO registration VALUES('CR76','B005','SL41','2015-01-13');
114INSERT INTO registration VALUES('CR56','B003','SG37','2014-04-13');
115INSERT INTO registration VALUES('CR74','B003','SG37','2013-11-16');
116INSERT INTO registration VALUES('CR62','B007','SA9','2014-03-07');