Notes

CREATE TABLE Branch(
Bno VARCHAR(2),
Street VARCHAR(25) NOT NULL,
Area VARCHAR(15) NULL,
City VARCHAR(15) NOT NULL,
Pcode VARCHAR(15) NOT NULL,
Tel_No VARCHAR(15) NOT NULL,
Fax_No VARCHAR(15) NOT NULL,
PRIMARY KEY(Bno)
);

CREATE TABLE Owner(
Ono VARCHAR(4),
FName VARCHAR(10) NOT NULL,
LName VARCHAR(10) NOT NULL,
Address VARCHAR(50) NOT NULL,
Tel_No VARCHAR(15) NOT NULL,
PRIMARY KEY(Ono)
);

CREATE TABLE Staff(
Sno VARCHAR(4),
FName VARCHAR(10) NOT NULL,
LName VARCHAR(10) NOT NULL,
Address VARCHAR(50) NOT NULL,
Tel_No VARCHAR(15) NULL,
Position VARCHAR(10) NOT NULL,
Sex CHAR NOT NULL,
DOB DATE NOT NULL,
Salary INT NOT NULL,
NIN VARCHAR(15) NOT NULL UNIQUE,
Bno VARCHAR(2) NOT NULL,
PRIMARY KEY(Sno),
FOREIGN KEY(Bno) REFERENCES Branch(Bno)
);

CREATE TABLE Renter(
Rno VARCHAR(4),
FName VARCHAR(10) NOT NULL,
LName VARCHAR(10) NOT NULL,
Address VARCHAR(50) NOT NULL,
Tel_No VARCHAR(15) NOT NULL,
Pref_Type VARCHAR(10) NOT NULL,
Max_Rent INT NOT NULL,
Bno VARCHAR(2) NOT NULL,
PRIMARY KEY(Rno),
FOREIGN KEY(Bno) REFERENCES Branch(Bno)
);

CREATE TABLE Property_For_Rent(
Pno VARCHAR(4),
Street VARCHAR(25) NOT NULL,
Area VARCHAR(15) NULL,
City VARCHAR(15) NOT NULL,
Pcode VARCHAR(15) NOT NULL,
Type VARCHAR(10) NOT NULL,
Rooms INT NOT NULL,
Rent INT NOT NULL,
Ono VARCHAR(4) NOT NULL,
Sno VARCHAR(4) NOT NULL,
Bno VARCHAR(2) NOT NULL,
PRIMARY KEY(Pno),
FOREIGN KEY(Bno) REFERENCES Branch(Bno),
FOREIGN KEY(Ono) REFERENCES Owner(Ono),
FOREIGN KEY(Sno) REFERENCES Staff(Sno)
);

CREATE TABLE Viewing(
Rno VARCHAR(4),
Pno VARCHAR(4),
Date DATE NOT NULL,
Comment VARCHAR(25) NULL,
PRIMARY KEY(Rno, Pno, Date),
FOREIGN KEY(Rno) REFERENCES Renter(Rno),
FOREIGN KEY(Pno) REFERENCES Property_For_Rent(Pno)
);

//------------------------------------------------------------------------------------------------------------------

INSERT INTO Branch VALUES ('B5','22 Deer Rd', 'Sidcup', 'London','SW1 4EH','0171-886-1212','0171-886-1214');
INSERT INTO Branch VALUES ('B7','16 Argyll St', 'Dyce', 'Aberdeen','AB2 3SU','01224-67125','01224-67111');
INSERT INTO Branch VALUES ('B3','163 Main St', 'Patrick', 'Glasgow','G11 9QX','0141-339-2178','0141-339-4439');
INSERT INTO Branch VALUES ('B4','32 Marse Rd', 'Leigh', 'Bristol','BS99 1NZ','0117-916-1170','0117-776-1114');
INSERT INTO Branch VALUES ('B2','56 Clover Dr', NULL, 'London','NW10 6EU','0181-963-1030','0181-453-7992');

INSERT INTO Owner VALUES ('CO46', 'Joe', 'Keogh', '2 Fergus Dr,Banchory,Aberdeen AB2 7SX','01224-861212');
INSERT INTO Owner VALUES ('CO87', 'Carol', 'Farrel', '6 Achray St,Glasgow G32 9DX','0141-357-7419');
INSERT INTO Owner VALUES ('CO40', 'Tina', 'Murphy', '63 Well St,Shawlands,Glasgow G42','0141-943-1728');
INSERT INTO Owner VALUES ('CO93', 'Tony', 'Shaw', '12 Park Pl,Hillhead,Glasgow G4 0QR','0141-225-7025');

INSERT INTO Staff VALUES ('SL21', 'John', 'White', '19 Taylor St,Cranford,London', '0171-884-5112','Manager','M','1945-10-01',30000,'WK442011B','B5');
INSERT INTO Staff VALUES ('SG37', 'Ann', 'Beech', '81 George St,Glasgow PA1 2JR', '0141-848-3345','Snr Asst','F','1960-10-10',12000,'WL432514C','B3');
INSERT INTO Staff VALUES ('SG14', 'David', 'Ford', '63 Ashby St,Patrick,Glasgow G11', '0141-339-2177','Deputy','M','1958-03-24',18000,'WL220658D','B3');
INSERT INTO Staff VALUES ('SA9', 'Mary', 'Howe', '2 Elm Pl,Aberdeen AB2 3SU', NULL,'Assistant','F','1970-02-19',9000,'WM532187D','B7');
INSERT INTO Staff VALUES ('SG5', 'Susan', 'Brand', '5 Gt Western Rd,Glasgow G12', '0141-334-2001','Manager','F','1940-06-03',24000,'WK588932E','B3');
INSERT INTO Staff VALUES ('SL41', 'Julie', 'Lee', '28 Malvern St,Kilburn NW2', '0181-554-3541','Assistant','F','1965-06-13',9000,'WA290573K','B5');

INSERT INTO Renter VALUES ('CR76', 'John', 'Kay', '56 High St,Putney,London SW1 4EH', '0171-774-5632','Flat',425,'B5');
INSERT INTO Renter VALUES ('CR56', 'Aline', 'Stewart', '64 Fern Dr,Pollock,Glasgow G42 0BL', '0141-848-1825','Flat',350,'B3');
INSERT INTO Renter VALUES ('CR74', 'Mike', 'Ritchie', '18 Tain St,Gourock PA1G 1YQ', '01475-392178','House',750,'B3');
INSERT INTO Renter VALUES ('CR62', 'Mary', 'Tregear', '5 Tarbot Rd,Kildary,Aberdeen AB9 3ST', '01224-196720','Flat',600,'B7');

INSERT INTO Property_For_Rent VALUES ('PA14','16 Holhead','Dee','Aberdeen','AB7 5SU','House',6,650,'CO46','SA9','B7');
INSERT INTO Property_For_Rent VALUES ('PL97','6 Argyll St','Kilburn','London','NW7','Flat',4,400,'CO87','SL41','B5');
INSERT INTO Property_For_Rent VALUES ('PG4','6 Lawrence St','Patrick','Glasgow','G11 9QX','Flat',3,350,'CO40','SG14','B3');
INSERT INTO Property_For_Rent VALUES ('PG36','2 Manor Rd',NULL,'Glasgow','G32 4QX','Flat',3,375,'CO93','SG37','B3');
INSERT INTO Property_For_Rent VALUES ('PG21','18 Dale Rd','Hyndland','Glasgow','G12','House',5,600,'CO87','SG37','B3');
INSERT INTO Property_For_Rent VALUES ('PG16','5 Novar Dr','Hyndland','Glasgow','G12 9AX','Flat',4,450,'CO93','SG14','B3');

INSERT INTO Viewing VALUES ('CR56','PA14','1998-05-24','too small');
INSERT INTO Viewing VALUES ('CR76','PG4','1998-04-20','too remote');
INSERT INTO Viewing (Rno,Pno,Date) VALUES ('CR56','PG4','1998-05-26');
INSERT INTO Viewing VALUES ('CR62','PA14','1998-05-14','no dining room');
INSERT INTO Viewing VALUES ('CR56','PG36','1998-04-28',NULL);