Scribd
Upload
94 views

CREATE DATABASE LabTask

The document creates a database called LabTask and defines 7 tables - Branch, Staff, PropertyForRent, Client, PrivateOwner, Viewing, and Registration. It then populates the tables with sample data by executing multiple INSERT statements.

Uploaded by

Tehmoor Amjad
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
94 views

CREATE DATABASE LabTask

The document creates a database called LabTask and defines 7 tables - Branch, Staff, PropertyForRent, Client, PrivateOwner, Viewing, and Registration. It then populates the tables with sample data by executing multiple INSERT statements.

Uploaded by

Tehmoor Amjad
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 6

CREATE DATABASE LabTask;

CREATE TABLE Branch


(
branchNo varchar(4),
street varchar(15),
city varchar(10),
postcode varchar(10),
constraint PK_branch primary key (branchNo)
);

CREATE TABLE Staff


(
staffNo varchar(4),
fName varchar(5),
lName varchar(5),
position varchar(10),
sex varchar(1),
DOB varchar(12),
salary int,
branchNo varchar(4),
constraint PK_staff primary Key(staffNo),
constraint FK_BranchStaff foreign key (branchNo) references
Branch(branchNo)

);

CREATE TABLE PropertyForRent(


propertyNo varchar(4),
street varchar(15),
city varchar(8),
postcode varchar(10),
_type varchar(5),
rooms int,
rent int,
ownerNo varchar(10),
staffNo varchar(4),
branchNo varchar (4),
constraint PK_PropertyForRent primary key (propertyNo),
constraint FK_PrivateOwnerPropertyForRent foreign
key (ownerNo) references privateOwner(ownerNo),
constraint FK_StaffPropertyForRent foreign
key (staffNo) references Staff(StaffNo),

constraint FK_BranchPropertyForRent foreign


key (branchNo) references Branch(branchNo),

);

CREATE TABLE Client


(
clientNo varchar(10),
fName varchar(10),
lName varchar(10),
telNo varchar(25),
prefType varchar(10),
maxRent int,
constraint PK_Client primary key (clientNo)

);

CREATE TABLE PrivateOwner(


ownerNo varchar(10),
fName varchar(10),
lName varchar(10),
_address varchar(50),
telNo varchar(25),
constraint PK_PrivateOwner primary key (ownerNo)

);

CREATE TABLE Viewing


(
clientNo varchar(10),
property varchar(10),
viewDate varchar(15),
comment varchar(25),
constraint PK_viewing primary key (clientNo, propertyNo, viewDate),
constraint FK_ClientViewing foreign key (clientNo) references
client(clientNo),
constraint FK_PropertyForRentViewing foreign
key (propertyNo) references
propertyForRent(propertyNo)

);

CREATE TABLE Registration


(
clientNo varchar(10),
branchNo varchar(10),
staffNo varchar(10),
dateJoined varchar(20),
constraint PK_Registration primary key (clientNo, branchNo),
constraint FK_clientRegistration foreign key (clientNo) references
client(clientNo),
constraint FK_branchRegistration foreign key (branchNo) references
Branch(branchNo),
constraint FK_staffRegistration foreign key (staffNo)
references staff(staffNo)

);
Or
CREATE TABLE Viewing
(
clientNo varchar(10),
propertyNo varchar(4),
viewDate varchar(10),
comment varchar(20),
constraint PK_viewing primary key (clientNo, propertyNo, viewDate),
constraint FK_ClientViewing foreign key (clientNo) references Client(clientNo),
constraint FK_PropertyForRentViewing foreign key (propertyNo) references
PropertyForRent(propertyNo)

);
CREATE TABLE Registration
(
clientNo varchar(10),
branchNo varchar(4),
staffNo varchar(4),
dateJoined varchar(20),
constraint PK_Registration primary key (clientNo, branchNo),
constraint FK_clientRegistration foreign key (clientNo) references client(clientNo),
constraint FK_branchRegistration foreign key (branchNo) references Branch(branchNo),

constraint FK_staffRegistration foreign key (staffNo)


references staff(staffNo)

);

insert into Branch(BranchNo,street,city,postcode)


values( 'B005','22 Deer Rd','London','SW1 4EH' );

insert into Branch(BranchNo,street,city,postcode)


values( 'B007','16 Argyll St','Aberdeen','AB2 3SU' );

insert into Branch(BranchNo,street,city,postcode)


values( 'B003','163 Main St','Glasgow','G11 9QX' );

insert into Branch(BranchNo,street,city,postcode)


values( 'B004','32 Manse Rd','Bristol','BS99 1NZ' );

insert into Branch(BranchNo,street,city,postcode)


values( 'B002','56 Clover Dr','London','NW10 6EU' );

2nd table data


insert into Staff(staffNo, fName, lName, position, sex, DOB, salary,branchNo)
values('SL21','John','White', 'Manager', 'M', '1-Oct-45',30000,'B005');

insert into Staff(staffNo, fName, lName, position, sex, DOB, salary,branchNo)


values('SG37','Ann','Beech', 'Assistant', 'F', '10-Nov-60',12000,'B003');
insert into Staff(staffNo, fName, lName, position, sex, DOB, salary,branchNo)
values('SG14','David','Ford', 'Supervisor', 'M', '24-Mar-58',18000,'B003');

insert into Staff(staffNo, fName, lName, position, sex, DOB, salary,branchNo)


values('SA9','Mary','Howe', 'Assistant', 'F', '19-Feb-70',9000,'B007');

insert into Staff(staffNo, fName, lName, position, sex, DOB, salary,branchNo)


values('SG5','Susan','Brand', 'Manager', 'F', '3-Jun-40',24000,'B003');

insert into Staff(staffNo, fName, lName, position, sex, DOB, salary,branchNo)


values('SL41','Julie','Lee', 'Assistant', 'F', '13-Jun-65',9000,'B005');

3RD TABLE

INSERT into
PropertyForRent(propertyNo,street,city,postcode,_type,rooms,rent,ownerNo,staffNo,branchNo
)
values('PA14','16 Holhead','Aberdeen', 'AB7 5SU', 'House',6,650,'CO46','SA9','B007');

INSERT into
PropertyForRent(propertyNo,street,city,postcode,_type,rooms,rent,ownerNo,staffNo,branchNo
)
values('PL94','6 Argyll St','London', 'NW2', 'Flat',4,400,'CO87','SL41','B005');

INSERT into
PropertyForRent(propertyNo,street,city,postcode,_type,rooms,rent,ownerNo,branchNo)
values('PG4','6 Lawrence St','Glasgow', 'G11 9QX', 'Flat',3,350,'CO40','B003');

INSERT into
PropertyForRent(propertyNo,street,city,postcode,_type,rooms,rent,ownerNo,staffNo,branchNo
)
values('PG36','2 Manor Rd','Glasgow', 'G32 4QX', 'Flat',3,375,'CO93','SG37','B003');

INSERT into
PropertyForRent(propertyNo,street,city,postcode,_type,rooms,rent,ownerNo,staffNo,branchNo
)
values('PG21','18 Dale Rd','Glasgow', 'G12', 'House',5,600,'CO87','SG37','B003');

INSERT into
PropertyForRent(propertyNo,street,city,postcode,_type,rooms,rent,ownerNo,staffNo,branchNo
)
values('PG16','5 Novar Dr','Glasgow', 'G12 9AX', 'Flat',4,450,'CO93','SG14','B003');

4th table
INSERT INTO Client(clientNo,fName,lName,telNo,prefType,maxRent)
values('CR76','John','Kay','0207-774-5632','Flat',425);

INSERT INTO Client(clientNo,fName,lName,telNo,prefType,maxRent)


values('CR56','Aline','Steward','0141-848-1825','Flat',350);

INSERT INTO Client(clientNo,fName,lName,telNo,prefType,maxRent)


values('CR74','Mike','Ritchie','01475-392178','House',750);

INSERT INTO Client(clientNo,fName,lName,telNo,prefType,maxRent)


values('CR62','Mary','Tregear','01224-196720','Flat',600);

5th table
insert into PrivateOwner(ownerNo,fName,lName,_address,telNo)
values('CO46','Joe','Keogh','2 Fergus Dr,Aberdeen AB2 7SX','01224-861212');

insert into PrivateOwner(ownerNo,fName,lName,_address,telNo)


values('CO87','Carol','Farrel','6 Achray St,Glasgow G32 9DX','0141-357-7419');

insert into PrivateOwner(ownerNo,fName,lName,_address,telNo)


values('CO40','Tina','Murphy','63 Well St,Glasgow G42','0141-943-1728');

insert into PrivateOwner(ownerNo,fName,lName,_address,telNo)


values('CO93','Tony','Shaw','12 Park PI,Glasgow G4 0QR','0141-225-7025');

6th table

INSERT into Viewing(clientNo,property,viewDate,comment)


values('CR56','PA14','24-May-04','too small');

INSERT into Viewing(clientNo,property,viewDate,comment)


values('CR76','PG4','20-Apr-04','too too remote');

INSERT into Viewing(clientNo,property,viewDate)


values('CR56','PG4','26-May-04');

INSERT into Viewing(clientNo,property,viewDate,comment)


values('CR62','PA14','14-May-04','no dining room');

INSERT into Viewing(clientNo,property,viewDate)


values('CR56','PG36','28-Apr-04');

7th table
INSERT into Registration(clientNo,branchNo,staffNo,dateJoined)
VALUES ('CR76','B005','SL41','2-Jan-04');

INSERT into Registration(clientNo,branchNo,staffNo,dateJoined)


VALUES ('CR56','B003','SG37','11-Apr-03');

INSERT into Registration(clientNo,branchNo,staffNo,dateJoined)


VALUES ('CR74','B003','SG37','16-Nov-02');

INSERT into Registration(clientNo,branchNo,staffNo,dateJoined)


VALUES ('CR62','B007','SA9','7-Mar-03');

You might also like