Developing A Detailed Entity-Relationship Model For Mini Case Assessment Two Answer

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. Cont. from Assignment 1

(DreamHome database) SQL queries (8 marks)

A DreamHome database has been created according to a case study for a property rental business (you did this in assignment 1) as diagram below:

Use the already created tables – done in assignment 1 and the script was also provided to answer the seven (7) SQL questions

Remember: You must add your full student name and ID and date of creation of each answer as a comment at the top of each SQL statement (see example below).

Note that you must provide the screen shots (via SNIPPING TOOL or you may use ALT- PRINT-SCREEN on Windows) for your results (of executing SQL statements) similar to the example shown below:

If your username is absent from a screenshot, then the screenshot may not be considered as your work. Not supplying the screenshots, or the screenshots are not readable or not containing your full name and ID, may lead to the loss of up to 30% of the corresponding marks.

SQL Questions:

1. Write an SQL statement to list all the properties that have not been viewed by any clients as yet. (1 mark)
2. Write an SQL statement to list the staff full name and position of all staff who manage a property (propertyForRent). Sort the output according to postcode of the property in descending order (screenshot required). (1 mark)
3. List the highest salaried staff and show their first name and their position: give the column heading: ‘Highest_Salary’. (1 mark)
4. Use an Inner Join and show all the properties that are listed in each branch and the name of their owner: list the branch name, owner name along with the Property number and city. (1 mark)
5. Write an SQL statement to list all the properties that have been viewed by clients less than 3 times and with comments containing something about rooms. More precisely, list the client full name, the client email, the propertyNo, the street of the property, and comments made for the viewings for that property. Order the output first by the client fName, then by the propertyNo (screenshot required). (1.5 marks)
6. Show the date of viewing and the number of times that each property for rent has been viewed and display the heading “Times_Viewed’ for the resultant column. Sort by highest times viewed and most recent viewing (1.5 marks)
7. For the staff, 'Julie','Lee', use SQL command to change this name to your own student name. (1 mark)

Q2. Normalisation question (3 marks)

Think of a scenario where data is kept in a single table and is unnormalised (0NF): show an example of your scenario by making the table (cannot use any example of tables covered in the lectures or from your textbook) with few records. Your example has to be your own. Show and describe the type of dependencies in your chosen table through a dependency diagram.

Q3. Database modelling (4 marks)

In this part, you are asked to design a database to support an Accommodation Booking System for HappyHolidays (HH), which is a mid-size online reservation company. The major business requirements are summarised below in the Mini Case:

HH receives booking requests from prospective visitors for accommodation in various locations. Accommodation can be of different types; for example, house, apartment, motel room, cabin, etc. Each type is uniquely identified by a code (H = house, Ap=apartment, C=cabin). Each type could also have specific information such as rate, size (1 bed, 2, bed, 3 bed, etc.), location and minimum and maximum number of required stay (in days). Visitors details are recorded, at least their full name, telephone number, address, and postcode. A visitor is allowed up to 2 pets if they rent a house, but they can’t have any pets if they rent any other type of accommodation. Pet type, breed and name also need to be recorded. Pets are given a kennel each. Each pet is identified by name and collar ID. Also kennels are labelled to identify the pet in it at any time.

Your Task:

You are asked to develop a detailed Entity-Relationship model (ER) for this mini case. You must use the same notation scheme for the ER diagram as the textbook (use UML notations as shown in the last page of the textbook, and don't use Crow Foot notations), and the ER diagram should be strictly in the way shown or used in the textbook – using any other notations other than UML will result in loss of marks. ER can be drawn in draw.io, or even Microsoft PowerPoint. A screenshot of the ER can then be placed as an object in your assignment Word document.

1. The ER diagram should include, among others, representative attributes for all entity types, proper subclassing if any, and correct participation multiplicities for the relationship types. It should be meaningful and well designed, and should also include all relevant and necessary aspects, and indicate any supplementary business rules if you decide to introduce.
2. Map the above ER diagram into a global relation diagram (GRD). 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.

Please note that an ERD is the artefact of the conceptual database design phase, while a GRD is the artefact of the logical database design phase which relates to the relational models. As such, a good ERD should be conceptually more concise and the relationships there should in general remain so rather than becoming extra entities as in a relational model.

Note on Submission

• This assignment must be submitted electronically via vUWS 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 students name and ID, in addition to the title page that includes these details.
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:

%%

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

%%

• 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.