Database design and development
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 varchar(10) PRIMARY KEY, FName nvarchar(20), LName nvarchar(20), position varchar(15), gender char(1), DOB datetime, salary numeric(8,2), branchNo 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, rooms numeric(2) default 4, rent numeric(5,2) default 0, ownerNo varchar(10) FOREIGN KEY REFERENCES PrivateOwner, staffNo varchar(10) FOREIGN KEY REFERENCES Staff, branchNo 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','555 1234','House',450,'
[email protected]'); INSERT INTO Client VALUES ('CR62','Wilma','Flintstone','555 1234','Flat',350,'
[email protected]'); INSERT INTO Client VALUES ('CR74','Albert','Johnstone','555 6677','Flat',450,'
[email protected]'); INSERT INTO Client VALUES ('CR77','Clark','Kent','555 9999','Flat',400,'
[email protected]'); INSERT INTO Client VALUES ('CR79','Joe','Bloggs','123 4567','House',450,'
[email protected]'); INSERT INTO Client VALUES ('CR83','Edward','Scissorhands','123 4567','House',300,'
[email protected]'); INSERT INTO Client VALUES ('CR91','Albert','Enistein','555 6789',NULL,450,'
[email protected]'); INSERT INTO Client VALUES ('CR92','Snorrie','Sturrluson','333 4567',NULL,400,'
[email protected]'); INSERT INTO Client VALUES ('CR93','Ferdinand','Oblogiotta','123 5555','House',450,'
[email protected]'); INSERT INTO Client VALUES ('CR94','Joe','Schmoe','123 45678','House',550,'
[email protected]'); INSERT INTO Client VALUES ('CR95','Bill','Gates','123 5555','House',1000,'
[email protected]'); INSERT INTO Client VALUES ('CR96','Bruce','Wayne','555 6789','House',900,'
[email protected]'); INSERT INTO Client VALUES ('CR97','Wayne','Johnson','777 5678','Unit',200,'
[email protected]'); INSERT INTO PrivateOwner VALUES ('CO46', 'Joe', 'Keogh', '2 Fergus Dr, Aberdeen AB2 7SX', '01224-861212'); INSERT INTO PrivateOwner VALUES ('CO87', 'Carol', 'Farrel', '6 Achray St, Glasgow G32 9DX', '0141-357-7419'); INSERT INTO PrivateOwner VALUES ('CO40', 'Tina', 'Murphy', '63 Well St, Glasgow G42', '0141-943-1728'); INSERT INTO PrivateOwner VALUES ('CO93', 'Tony', 'Shaw', '12 Park Pl, Glasgow G4 0QR', '0141-225-7025'); INSERT INTO PrivateOwner VALUES ('CO94', 'Peter', 'Stan', '25 Frank Rd, Glasgow G4 0QR', '0141-235-7635'); 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 15 marks 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 results 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 be 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 recorded, and payment instalment arrangements 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 corresponding to a manager. ii. Draw the Global Relation Diagram (GRD) corresponding 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 records into your