ASSIGNMENT TWO: 15 marks

Due end of week 11 (Friday 21 May 2021) by midnight

Please submit via Turnitin on vUWS

This assignment should be done in a group of no more than 2 students. Make sure to add a footer to each page with your names and IDs.

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.

Students are expected to continue with their existing assignment group or form a new group if they haven't formed a group for Assignment 1. If any student is making a new assignment group, thus leaving a previous assignment group, he must first obtain a written approval from his tutor or the unit convenor, unless he will not make use of any work jointly done in the previous team work.

Group Issues and contingency plans

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 (if still have minimum 2 weeks to work together) or otherwise 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 a legitimate reason for an assignment extension. However, a student is obligated to properly notify his or her existing assignment partner in good time (via email) should it become imperative that the student need to terminate the existing partnership due to unexpected circumstances. A copy of any such emails from assignment partner can be attached as an image to the end of the assignment as evidence.

● 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, not in PDF.

Q1. (DreamHome database) SQL queries (9 marks) 

A DreamHome database has been created according to a case study for a property rental business.

Use the already given SQL script to create these tables as the diagram below:

Now you can answer the following SQL questions:

1. Add 1 new record in the ‘CLIENT’ table: This record is about you, so enter you your first name and last name. (screenshot required to show the newly inserted record). If this question is not done, then only 50% can be awarded for the remaining questions considering they are correct.

2. List all the clients’ full name for those who prefer to view a ‘house’. Screenshot is required for the results.

3. Write an SQL statement to list all the properties that have been viewed by one or more clients. More precisely, list the client full name, the client email, the propertyNo, the street of the property, and all the viewings that occurred in Jan-2018' date. Order the output first by the client fName, then by the propertyNo (screenshot required) - Screenshot is required for the results.

4. Show the number of times that each property for rent has been viewed. Screenshot is required for the results.

5. List the highest salaried staff and show their first name and their position: give the column heading: ‘Highest_Salary’. Screenshot is required for the results

6. Use an Inner Join and show all the staff who work in the ‘London’ branch: list the staff Number and the full name along with the branch city. Screenshot is required for the results.

7. List all the staff with salary range of $15,500-$30,000. Screenshot is required for the results.

Note that the screenshots you provide should again conform to the requirements indicated in the previous question. For students working in a team of 2 members, the student name contained in the screenshots for this question should be different from the one in the previous question. Otherwise the person whose name is neither on the screenshots of this question nor on those of the previous one may lose the corresponding marks.

Q2. Discussion questions (2 marks)

A. Discuss the anomalies associated with unnormalized tables and provide examples of how this would cause problems.

B. Explain the working of the DBMS in controlling multi-user transactions.

Q3. Database modelling (4 marks)

In this part, you are asked to design a database to support a car wash business in your area. The major business requirements are summarised below in the Mini Case:

WashMe receive requests from car owners to get their car washed and / or polished – WashMe refers to each client request as a service and they charge the car owner an agreed set rate. There are different rates to be paid for each service, depending on the owners’ request (ie: hand wash, polish, detailing, etc.). WashMe exist in various locations, and car owners may choose where to take their car to be serviced. There are the regular car owners that also receive a discount, and this would be recorded in the system.

1. Based on the above case, create a global relation diagram (GRD) to represent the business rules. 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. Include in the diagram all the primary keys, foreign keys, and the multiplicity constraints. Make sure not to keep any many to many relationships at this level.

Please note a GRD is the artefact of the logical database design phase which relates to the relational models, while an ERD is the artefact of the conceptual database design phase.

Note on Submission

● This assignment must be submitted electronically via vUWS on or before the due date. No email submissions will be accepted.

● It is the students' responsibility to retrieve and keep all their submission receipts. If in doubt, consult your tutors well before the submission due date.

Your submission must include the following parts:

1) main document in Microsoft Word that describes your solutions question by question (make sure to label each question), in the right order, including pertinent diagrams, screenshots, and SQL scripts whenever relevant. Add a footer with your student’s name and ID, in addition to the title page that includes these details. If you are working with another student, then both your names should be listed on the title page and in the footer.

2) A plain text file as an Appendix at the end of the document, containing all SQL scripts in the order of their appearance in the main document for each question, separated by relevant comments similar to the following:

o %%

o %% Question 3-ii-(a) - List names of the clients having a booking ...

o %%

● The electronic submission should contain the paper work in Microsoft Word, and the pertinent SQL source code. Otherwise 1 mark may be deducted for the missing SQL source parts even if the code is already contained in the main Word document.

● Please note that if your SQL source code gets rejected by the SQL Server at the School, you automatically lose 50% of the marks allocated to that coding part.

● Each group must submit exactly one copy of their assignment solution electronically by one of the team members. If the other group member really wants to submit it as well due to whatever reasons, then the name of the submitted files must start with "please_ignore_" (such files will not be treated as regular submissions and will be ignored during the marking). Otherwise 1 mark may be deducted for the duplicated electronic submission.

● Each submission must be accompanied by a declaration of the ownership of the submitted work as described in the Learning Guide Companion. No signature is however required for the electronic submissions. Please note that an examiner or lecturer/tutor has the right not to mark this assignment if a pertinent declaration is not present in your submission.

● Late submissions will attract a daily incremented late penalty of 10% per day.

● Electronic submission on the due date after 8pm before 12 midnight will still be accepted without penalty. However, any submission failure in that period due to either the student faults or the fault or malfunction of the School's or WSU's servers will not be accepted as the legitimate reasons for a late submission. Beware that School's servers often need to be shut down for maintenance from late Fridays or just before public holidays.

● A statement on the work distribution in percentage (e.g. 50% for David and 50% for Louise) agreed among all the group members. Also see the group contract page to be completed. If this statement is absent, then it will be assumed that all group members have made equal amount of contribution to the assignment solution. Achieving a 50%/50% work distribution is also the goal of this team work; the person who contributes less than 50% may result in having less mark than the other team member.

● The main purpose of having an assignment team is to enable students to discuss the database design with another student so as to better understand everything there, rather than splitting the actual work. Hence, regardless of whether a team member contributed 100% or just 50%, the mark remains the same. However, a team member may receive less marks if he contributes less than 50%.

● Any student submitting the assignment on his own must state explicitly whether he was once in a group with another student, and what part of the submitted work actually inherited from a previous joint team work. Failure to make this statement may result in a plagiarism case lodged if the work is similar to another student's, and a late addition of such a statement may lead to the assignment being considered as a late submission.

● A friendly reminder: Assignment group members should each maintain a constant, effective, and productive communication with their respective assignment partner, and should always have a contingency plan, Plan B, for the potential failure of the partnership no matter how impossible it may appear at the time. While partners will typically all do their best to contribute to the better understanding of the assignment, there can be unforeseeable circumstances or misadventures that could result in an abortive termination of the partnership. Hence it is each student's own responsibility to ensure that his or her partnership is working, and he or she has a plan B for any potential partnership breakdown. This is a trade-off for all the advantages of having an assignment partner. Hence please always keep a copy of everything about your assignment yourself. Failure of a partnership at any time will not be accepted as an excuse for the failure to submit the assignment in time.