Alter Table UOASxxx.Branch Drop Constraint Bno_PK; Drop index UOASxxx.Bno_ndx; Drop table UOASxxx.Branch; Create table UOASxxx.Branch ( Bno Char(4) Not Null, Street Char(12), Area Char(10), City Char(10), Pcode Char(8), Tel_No Char(14), Fax_No Char(14) ) IN UOASxxx.TSSxxx ; Create Unique Index Bno_ndx on UOASxxx.Branch(BNO); Alter Table UOASxxx.Branch Add Constraint Bno_PK Primary key (Bno); Alter Table UOASxxx.Staff Drop Constraint Staff_PK; Drop index UOASxxx.Sno_ndx; Drop table UOASxxx.Staff; Create Table UOASxxx.Staff ( Sno Char(4) Not Null, FName Char(10), LName Char(10), Address Char(35), Tel_No Char(14), Position Char(10), Gender Char(1), DOB Date, Salary Integer, Bno Char(4), Foreign key(Bno) references UOASxxx.Branch) IN UOASxxx.TSSxxx ; Create unique index Sno_ndx on UOASxxx.Staff(SNO); Alter Table UOASxxx.Staff Add Constraint Staff_PK Primary Key (Sno); Alter Table UOASxxx.Renter Drop Constraint Renter_PK; Drop index UOASxxx.Rno_ndx; Drop table UOASxxx.Renter; Create Table UOASxxx.Renter ( Rno Char(4) Not Null, FName Char(10), LName Char(10), Address Char(40), Tel_No Char(14), Pref_Type Char(5), Max_Rent SmallInt, Bno Char(4), Foreign key (Bno) References UOASxxx.Branch) IN UOASxxx.TSSxxx ; Create unique index Rno_ndx on UOASxxx.Renter(Rno); Alter Table UOASxxx.Renter Add Constraint Renter_PK Primary Key(Rno); Alter Table UOASxxx.Owner Drop Constraint Owner_PK; Drop index UOASxxx.Ono_ndx; Drop table UOASxxx.Owner; Create Table UOASxxx.Owner ( Ono Char(4) Not Null, FName Char(10), LName Char(10), Address Char(40), Tel_No Char(14) ) IN UOASxxx.TSSxxx ; Create unique index Ono_ndx on UOASxxx.Owner(ono); Alter Table UOASxxx.Owner Add Constraint Owner_PK Primary Key (Ono); Alter Table UOASxxx.Prop_For_Rent Drop Constraint Prop_Rent_PK; Drop index UOASxxx.Pno_ndx; Drop table UOASxxx.Prop_For_Rent; Create Table UOASxxx.Prop_For_Rent ( Pno Char(4) Not Null, Street Char(14), Area Char(8), City Char(10), Pcode Char(8), Type Char(5), Rooms Smallint, Rent Smallint, Ono Char(4), Sno Char(4), Bno Char(4), Foreign Key(Ono) references UOASxxx.Owner, Foreign Key(Sno) references UOASxxx.Staff, Foreign Key(Bno) references UOASxxx.Branch ) IN UOASxxx.TSSxxx ; Create unique index Pno_ndx on UOASxxx.Prop_For_Rent(Pno); Alter Table UOASxxx.Prop_For_Rent Add Constraint Prop_Rent_PK Primary Key(Pno); Alter Table UOASxxx.Viewing Drop Constraint Viewing_PK; Drop index UOASxxx.View_ndx; Drop Table UOASxxx.Viewing; Create Table UOASxxx.Viewing ( Rno Char(4) Not Null, Pno Char(4) Not Null, Rdate Date, Comments Varchar(256) ) IN UOASxxx.TSSxxx ; Create Unique index View_ndx on UOASxxx.Viewing(Rno,Pno); Alter Table UOASxxx.Viewing Add Constraint Viewing_PK Primary Key (Rno, Pno); Insert into UOASxxx.Branch values ('B5','22 Deer Road','Sidcup','London','SW1 4EH','0171-886-1212','0171-886-1214'); Insert into UOASxxx.Branch values ('B7','16 Argyll St','Dyce','Aberdeen','AB2 3SU','01224-67125','01224-6711'); Insert into UOASxxx.Branch values ('B3','163 Main St','Partick','Glasgow','G11 9QX','0141-339-2178','0141-339-4439'); Insert into UOASxxx.Branch values ('B4','32 Manse Rd','Leigh','Bristol','BS99 1NZ','0117-916-1170','0117-776-1114'); Insert into UOASxxx.Branch values ('B2','36 Clover Dr',NULL,'London','NW10 6EU','0181-963-10301','0181-453-7992'); Insert into UOASxxx.Staff values ('SL21','John','White','19 Taylor St, Cranford,London','0171-884-5112','Manager','M','10/01/1945',30000,'B5'); Insert into UOASxxx.Staff values ('SG37','Ann','Beech','81 George St, Glasgow PA12JR','0141-848-3345','Srn Asst','F','11/10/1960',12000,'B3'); Insert into UOASxxx.Staff values ('SG14','David','Ford','63 Ashby St, Partick, Glasgow G11','0141-339-2177','Deputy','M','03/24/1958',18000,'B3'); Insert into UOASxxx.Staff values ('SA9','Mary','Howe','2 Elm Pl, Aberdeen AB2 3SU',NULL,'Assistant','F','02/19/1970',9000,'B7'); Insert into UOASxxx.Staff values ('SG5','Susan','Brand','5 Gt Western Rd, Glasgow G12','0141-334-2001','Manager','F','06/03/1940',24000,'B3'); Insert into UOASxxx.Staff values ('SL41','Julie','Lee','28 Malvern St, Kilburyn NW2','0181-554-3541','Assistant','F','06/13/1965',9000,'B5'); Insert into UOASxxx.Renter values ('CR76','John','Kay','56 High St, Putney, London SW1 4EH','0171-774-5632','Flat',425,'B5'); Insert into UOASxxx.Renter values ('CR56','Aline','Stewart','64 Fern Dr, Pollock, Glasgow G42 0BL','0141-848-1825','Flat',350,'B3'); Insert into UOASxxx.Renter values ('CR74','Mike','Ritchie','18 Tain St, Gourock PA1G 1YQ','0145-392178','House',750,'B3'); Insert into UOASxxx.Renter values ('CR62','Mary','Treager','5 Tarbot Rd, Kildary, Agerdeen AB9 3ST','01224-196720','Flat',600,'B7'); Insert into UOASxxx.Owner values ('C046','Joe','Keogh','2 Fergus Dr, Banchory, Aberdeen AB2 75SX','01224-861212'); Insert into UOASxxx.Owner values ('C087','Carol','Farrel','6 Archray St, Glasgow G32 9DX','0141-357-7419'); Insert into UOASxxx.Owner values ('C040','Tina','Murphy','63 Well St, Shawlands, Glasgow G42','0141-943-1728'); Insert into UOASxxx.Owner values ('C093','Tony','Shaw','12 Park Pl, Hillhead, Glasgow G4 0QR','0141-224-7025'); Insert into UOASxxx.Viewing values ('CR56','PA14','05/24/1995','too small'); Insert into UOASxxx.Viewing values ('CR76','PG4','04/20/1995','too remote'); Insert into UOASxxx.Viewing values ('CR56','PG4','05/26/1995',NULL); Insert into UOASxxx.Viewing values ('CR62','PA14','05/14/1995','no dining room'); Insert into UOASxxx.Viewing values ('CR56','PG36','04/28/1995',NULL); Insert into UOASxxx.Prop_For_Rent values ('PA14','16 Holhead','Dee','Aberdeen','AB7 5SU','House',6,650,'C046','SA9','B7'); Insert into UOASxxx.Prop_For_Rent values ('PL94','6 Argyll St','Kilburn','London','NW2','Flat',4,400,'C087','SL41','B5'); Insert into UOASxxx.Prop_For_Rent values ('PG4','6 Lawrence St','Partick','Glasgow','G11 9QX','Flat',3,350,'C040','SG14','B3'); Insert into UOASxxx.Prop_For_Rent values ('PG36','2 Manor Rd',NULL,'Glasgow','G32 4QX','Flat',3,375,'C093','SG37','B3'); Insert into UOASxxx.Prop_For_Rent values ('PG21','18 Dale Rd','Hyndland','Glasgow','B12','House',5,600,'C087','SG37','B3'); Insert into UOASxxx.Prop_For_Rent values ('PG16','5 Novar Dr','Hyndland','Glasgow','G12 9AX','Flat',4,450,'C093','SG14','B3');