SQL script for assignment 1: SQL script dreamhome.sql CREATE TABLE Branch ( branchNo varchar(10) PRIMARY KEY, street varchar(20), city varchar(20), postcode varchar(10) ); CREATE TABLE Staff ( staffNo...

SQL script for assignment 1: SQL script dreamhome.sql
CREATE TABLE Branch (
anchNo varchar(10) PRIMARY KEY,
street varchar(20),
city varchar(20),
postcode varchar(10)
);
CREATE TABLE Staff (
staffNo varchar(10) PRIMARY KEY,
FName nvarchar(20),
LName nvarchar(20),
position varchar(15),
gender char(1),
DOB datetime,
salary numeric(8,2),
anchNo varchar(10) FOREIGN KEY REFERENCES Branch);
CREATE TABLE PrivateOwner (
ownerNo varchar(10) PRIMARY KEY,
fName nvarchar(20),
lName nvarchar(20),
Address varchar(40),
telNo varchar(15)
);
CREATE TABLE PropertyForRent (
propertyNo varchar(10) PRIMARY KEY,
street varchar(20),
city varchar(20),
postcode varchar(10),
type varchar(10) not null,
ooms numeric(2) default 4,
ent numeric(5,2) default 0,
ownerNo varchar(10) FOREIGN KEY REFERENCES PrivateOwner,
staffNo varchar(10) FOREIGN KEY REFERENCES Staff,
anchNo varchar(10) FOREIGN KEY REFERENCES Branch
);
CREATE TABLE Client (
clientNo varchar(10) PRIMARY KEY,
fName nvarchar(20),
lName nvarchar(20),
telNo varchar(15),
prefType varchar(10),
maxRent numeric(6,2),
eMail varchar(40)
);
CREATE TABLE Viewing (
clientNo varchar(10)
constraint clientNo_fk FOREIGN KEY(clientNo) REFERENCES Client,
propertyNo varchar(10)
FOREIGN KEY REFERENCES PropertyForRent,
viewDate datetime,
comment varchar(100),
constraint viewing_pk PRIMARY KEY(clientNo,propertyNo)
);
SET NOCOUNT ON
INSERT INTO Branch VALUES ('B002','56 Cover Drive','London','NW10 6EU');
INSERT INTO Branch VALUES ('B003','163 Main Street','Glasgow','G11 9QX');
INSERT INTO Branch VALUES ('B004','32 Manse Road','Bristol','BS99 1NZ');
INSERT INTO Branch VALUES ('B005','22 Deer Road','London','SW1 4EH');
INSERT INTO Branch VALUES ('B007','16 Argyll Street','Aberdeen','AB2 3SU');
INSERT INTO Branch VALUES ('B008','30 Rose Street','Rockdale','SW2 4EH');
INSERT INTO Staff VALUES ('S193','Mary','Howe','Assistant','F',19/2/1970,9000,'B007');
INSERT INTO Staff VALUES ('SG14','David','Ford','Supervisor','M',24/3/1958,18000,'B003');
INSERT INTO Staff VALUES ('SG37','Ann','Beech','Assistant','F',10/11/1960,12000,'B003');
INSERT INTO Staff VALUES ('SG5','Susan','Brand','Manager','F',3/6/1962,24000,'B003');
INSERT INTO Staff VALUES ('SL21','John','White','Manager','M',1/10/1945,30000,'B005');
INSERT INTO Staff VALUES ('SL41','Julie','Lee','Assistant','F',13/6/1965,9000,'B005');
INSERT INTO Staff VALUES ('SL42','Stacey','Moore','Team Leader','F',15/11/1975,55000,'B008');
INSERT INTO Client VALUES ('CR56','Fred','Flintstone',' XXXXXXXXXX','House',450,' XXXXXXXXXX');
INSERT INTO Client VALUES ('CR62','Wilma','Flintstone',' XXXXXXXXXX','Flat',350,' XXXXXXXXXX');
INSERT INTO Client VALUES ('CR74','Albert','Johnstone',' XXXXXXXXXX','Flat',450,' XXXXXXXXXX');
INSERT INTO Client VALUES ('CR77','Clark','Kent',' XXXXXXXXXX','Flat',400,' XXXXXXXXXX');
INSERT INTO Client VALUES ('CR79','Joe','Bloggs',' XXXXXXXXXX','House',450,' XXXXXXXXXX');
INSERT INTO Client VALUES ('CR83','Edward','Scissorhands',' XXXXXXXXXX','House',300,' XXXXXXXXXX');
INSERT INTO Client VALUES ('CR91','Albert','Enistein',' XXXXXXXXXX',NULL,450,' XXXXXXXXXX');
INSERT INTO Client VALUES ('CR92','Sno
ie','Stu
luson',' XXXXXXXXXX',NULL,400,'sno
XXXXXXXXXX');
INSERT INTO Client VALUES ('CR93','Ferdinand','Oblogiotta',' XXXXXXXXXX','House',450,' XXXXXXXXXX');
INSERT INTO Client VALUES ('CR94','Joe','Schmoe',' XXXXXXXXXX','House',550,' XXXXXXXXXX');
INSERT INTO Client VALUES ('CR95','Bill','Gates',' XXXXXXXXXX','House',1000,' XXXXXXXXXX');
INSERT INTO Client VALUES ('CR96','Bruce','Wayne',' XXXXXXXXXX','House',900,' XXXXXXXXXX');
INSERT INTO Client VALUES ('CR97','Wayne','Johnson',' XXXXXXXXXX','Unit',200,' XXXXXXXXXX');
INSERT INTO PrivateOwner VALUES ('CO46', 'Joe', 'Keogh', '2 Fergus Dr, Aberdeen AB2 7SX', ' XXXXXXXXXX');
INSERT INTO PrivateOwner VALUES ('CO87', 'Carol', 'Fa
el', '6 Achray St, Glasgow G32 9DX', ' XXXXXXXXXX');
INSERT INTO PrivateOwner VALUES ('CO40', 'Tina', 'Murphy', '63 Well St, Glasgow G42', ' XXXXXXXXXX');
INSERT INTO PrivateOwner VALUES ('CO93', 'Tony', 'Shaw', '12 Park Pl, Glasgow G4 0QR', ' XXXXXXXXXX');
INSERT INTO PrivateOwner VALUES ('CO94', 'Peter', 'Stan', '25 Frank Rd, Glasgow G4 0QR', ' XXXXXXXXXX');
INSERT INTO PropertyForRent VALUES ('PA14','16 Holhead','Aberdeen','AB7 5SU','House',6,650,'CO46','SA9','B007');
INSERT INTO PropertyForRent VALUES ('PG16','5 Novar Drive','Glasgow','G12 9AX','Flat',4,450,'CO93','SG14','B003');
INSERT INTO PropertyForRent VALUES ('PG21','18 Dale Road','Glasgow','G12','House',5,600,'CO87','SG37','B003');
INSERT INTO PropertyForRent VALUES ('PG36','2 Manor Road','Glasgow','G32 4QX','Flat',3,375,'CO93','SG37','B003');
INSERT INTO PropertyForRent VALUES ('PG4','6 Lawrence Street','Glasgow','G11 9QX','Flat',3,350,'CO40','SA9','B003');
INSERT INTO PropertyForRent VALUES ('PG97','Muir Drive','Aberdeen','AB42 1DD','House',3,380,'CO46','SA9','B007');
INSERT INTO PropertyForRent VALUES ('PL94','6 Argyll Street','London','NW2','Flat',4,400,'CO87','SL41','B005');
INSERT INTO PropertyForRent VALUES ('PL95','6 Mary Road','London','NW2','Villa',5,500,'CO94','SL42','B008');
INSERT INTO Viewing VALUES ('CR56', 'PA14', '24-May-2008', 'too small');
INSERT INTO Viewing VALUES ('CR77', 'PG4', '20-April-2008', 'too remote');
INSERT INTO Viewing VALUES ('CR56', 'PG4', 26/5/2008, NULL );
INSERT INTO Viewing VALUES ('CR91', 'PA14', '14-May-2008', 'no dining room');
INSERT INTO Viewing VALUES ('CR56', 'PG36', '28-Apr-08', NULL );
INSERT INTO Viewing VALUES ('CR97', 'PL95', '30-Jun-2008', 'on-suite bathroom' );
SET NOCOUNT OFF

ASSIGNMENT ONE XXXXXXXXXXmarks
Due end of week 8 (Friday 3 May, 2019) by
midnight
Please submit via Turnitin on vUWS
This assignment should be done in a group of no more than 2
students.
This assignment needs to be completed in a group of no more than 2 student
members. If such a group is formed, both members must come from the same
campus, and also within the tutorial classes of the same tutor whenever possible.
While lecturers and tutors will help as much as they can, it is essentially each
student's own motivation and responsibility to form a group for this assignment. By
default, each student is working in a group containing a single member of himself.
In the rare case of one group member becoming seriously ill or uncontactable or
not responding, the other member should consider forming a different group or
working on his or her own for the assignment. As in real life, everyone should
have a contingency plan, or Plan B, and failure of assignment partnership at
any point of time will not be accepted as the legitimate reasons for an
assignment extension. However, a student is obligated to properly notify his or
her existing assignment partner in good time should it become imperative that the
student need to terminate the existing partnership due to unexpected circumstances.
 For all the SQL queries in this assignment, students need to submit their SQL
statements constructed directly, that is, not to generate the SQL script for you by
the GUI, as this would defeat the purpose of practicing how to formulate SQL
queries directly.
 Students' main document submitted for their answers to this Assignment must be
written in Microsoft Word – DO not submit in PDF.
 All SQL SELECT statements written and generated must be copied into a
Word document and accompanied with screenshots of the script plus the
esults of each statement.
 It is anticipated that most students will finish forming their respective
assignment group within 2 weeks, that is, within semester week 3 and week
4. However, the earlier, the better.
Q1. Database modelling - case study (5 marks)
In this question, you are required to construct a Swimming Database for ABC
children swimming school, so that the database can be used to maintain the listing
of enrolled young children in the Learn-2-Swim program. Each swimming
program has an 8 weeks’ duration and caters for young swimmers between the
ages of 4-14. The swimmers are grouped into levels according to their age and
swimming ability/confidence. ABC conducts a swimming assessment at the end of
the 8 weeks and keeps records of the learners/swimmers progress and level. This
assessment determines whether they can be placed in a higher level. There are 3
levels: beginner (1), intermediate (2) and advanced (3). In each level there cannot
e any more than 6 swimmers. At the time of enrolling, parents can pay for one,
two, or three programs (program is 8 weeks) and a discount is given if they pay for
3 programs in advance. Swimming instructors can take 2-3 sessions (session is 1
hour of swimming instruction) per week for any given level.
A typical entry of such a listing might contain the data similar to the following:

Child Gender M
Level 2
Enrolment date 10/5/2018
Enrolment duration 3P
Enrolment Fee $400
Name Karl Edwards
Date of birth 6/11/2013
Instructor Name Maddy Nice
Session day Tuesday
Session date 22/5/2018
Design a minimum database (a database of only essential attributes) to fulfil this
purpose, and draw the ER diagram for your design. Indicate on the ER diagram the
primary keys and the relationship multiplicities. You must use the same notation
scheme for the ER diagram as the textbook, and the ER diagram should be strictly
in the sense the textbook uses.
List the schemas for all the derived tables for this database, marking out the
primary keys and foreign keys if any.
Here’s an example of a table schema where table name is client:
Client(clientNo,fName,lName,telNo,prefType,maxRent,eMail)
Q2. Basic database design and queries (6 marks)
An advertising agency wants to create a simple database to manage their clients’
advertising campaigns. Clients’ details need to be recorded. Clients are grouped
into regions where a region can have up to 20 clients. Clients request advertising
campaigns, and a record is kept of every campaign. Each campaign is based on a
unique theme and has an associated cost as quoted to each client. Each campaign is
managed by an account manager, an advertising manager and a graphic design
manager, who may also manage other campaigns. Payments for each campaign is
ecorded, and payment instalment a
angements are available for those clients who
meet some certain criteria. Date and amount of each payment is recorded.
i. Design a minimum database (a database of only essential attributes) to fulfil
this purpose, and draw the ER diagram for your design. Indicate on the ER
diagram the primary keys and the relationship multiplicities.
By minimum we mean that you don't need to add anything that is not
explicitly stated in the requirements, unless it's one of your artificially
generated keys. For instance, you don't need to include a telephone number
or email address for the entity co
esponding to a manager.
ii. Draw the Global Relation Diagram (GRD) co
esponding to the ER diagram
in the above, indicating all the primary keys and foreign keys. The GRD
should be in a form similar to Figure 17.9 (page 554 or 516 for edition 5) of
the textbook, but all the attributes should be kept there too.
iii. Write an SQL script (of statements) that generates all the tables for your
designed database. Screenshots are required for the records of all the created tables.
iv. Write an SQL script to insert records into your tables. Insert sufficient
ecords into your