Database Design and SQL Queries
In assignment 1, you have already started the process of designing a database for the Beauty Salon mini-case (enclosed again below), mainly in the phase of conceptual database design, and a draft ER diagram has been created for this purpose. Here you will further refine your database design for the other design phases.
In this assignment, you will in particular complete the logical design through the normalisation process, have it physically implemented, perform a few important queries in SQL, and establish certain stored procedures or functions in Transact SQL (T-SQL) to process data or verify database integrity. Your design of relational model should be done in such a way that business rules and data integrity are ensured by the intrinsic design of the database as much as possible.
Logical Database Design
1. For the ER diagram you created in assignment 1, the artefact of the conceptual database design, map the ER model into the relational model according to how it was designed in the ER diagram. You may however first refine your ER diagram if necessary. The actual assessment of this part is in the point 3 below.
2. For all the relations that arise from this ("first-cut") ER diagram, list all those that are already in 3NF. If there are some relations that are not in 3NF yet, then convert them into 3NF with proper procedure and explanations. (2 marks)
3. Draw the global relation diagram for your final, revised, and normalised database design, and keep all the relevant details there. It should be in a similar form to Figure 17.9 (page 516) 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. Identify and discuss the potential data redundancies or anomalies that may still exist in your design, if any. (3 mark)
Physical Database Design and SQL Queries
In this part (consisting of points 4 and 5 below), students are allowed to implement the following simplifications in their table and data design.
o Availability for the beauty therapists don't have to be considered. That is, you don't have to worry about whether a therapist works only on Mondays, or if she is going away for a whole month, or anything like these. Hence the therapists can be assumed to be available all the time other than those time slots already booked by someone.
o ActivityVenue for all the beauty therapists can be assumed to be limited to the same beauty parlour alone; hence the activity venue essentially doesn't have to be considered.
o Every customer is assumed to have a client profile created or set up with the beauty parlour before any bookings can be made or any services can be provided to the customer.
5. Create the database tables in SQL (runnable on the School's Microsoft SQL Server) for all the relations in your relation diagram, and enforce there all the relevant constraints including primary keys and foreign keys. Fill the tables with sufficient data - generally around 4 tuples or more per table, but should be sufficient to illustrate meaningfully the working of the general queries to be completed below. (3 marks)
6. Write in SQL the commands to complete the following queries, and show your results in screenshots. Screenshots of active windows (under Microsoft Windows) can be obtained by pressing CTRL-ALT-PRTSC keys together. (4 marks)
o (a) Write a drop table statement so that its execution will delete all the tables you have created for this assignment. No partial mark will be given for this part, if the statement doesn't do the complete job.
WARNING: Before you test this, you must first make sure that you have saved all the statements for the table creation and the record insertion etc in a separate SQL file saved outside the SQL Server. This is to ensure that after the drop table statement deletes everything, you can re-create everything by running your saved SQL script. If you are not sure, don't to this part.
o (b) List all the client names and their corresponding telephone numbers.
o (c) For all the beauty therapists who hold one or more qualifications, list the therapist names and their corresponding qualifications.
o (d) For a given day, say, 2011-11-11, list all the names of the therapists who have/had at least one booking/appointment on that day. Don't repeat the names in the list.
o (e) For a given therapist name, list all bookings she has for the clients this year of 2011. The list should contain the client names, the corresponding start and finish time of the appointments, and the status of the appointments (e.g. if an appointment is pending, cancelled, or completed).
o (f) List all the itemed services along with the therapists who can provide such services. The list should be sorted alphabetically in the service names.
o (g) (Advanced Features) List all the bookingNo, the corresponding client, and the total cost of the itemed services made in the booking. The total cost must be recalculated from all the relevant itemed services.
o (h) (Advanced Features) List all the bookingNo, the corresponding client, and the total cost of all the services associated with the booking. The total cost must be recalculated from all the relevant itemed and timed services.
Advanced Features
6. For this question, we assume that all thereapists are available at any time unless the timeslot has already been booked out by some other clients. For a given period, say, 2011-11-11 2pm-6pm, list which therapists are available for what services, sorted in terms of the services. Your database should contain sufficiently many relevant records to nontrivially illustrate your solution.
7. List the potential problems, in the order of perceived severity, on the data/record consistency and constraints that can not be automatically ensured by your final database design. For those can, they should be done so via such as entity integrity and referential integrity. Write SQL statements or T-SQL procedures that will determine if such problems exist or not for any given state of the database. We note that the overall design quality of the database will also be taken into consideration here.
8. A single plain-text file containing SQL statements for creating all the tables and making all the queries, and (if any) the stored procedures and their execution. The script should be executable on the School's Microsoft SQL Server, otherwise the corresponding marks in the above listed items will be deducted accordingly.
9. A statement on the work distribution in percentage (e.g. 50% for David and 50% for Louise) agreed among all the group members. 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.
10. A hardcopy of your electronic submission is also due at the same time as the electronic submission, and needs to be delivered on your own campus. More precise locations will be announced at vUWS in the due course. However, a student may choose to deliver the hardcopy on the Monday immediately following the deadline date regardless whether this is a mid-semester break. The hardcopy must be the same as the submitted softcopy. The electronic submission is the official submission, submitting a hardcopy without submitting the electronic copy within the due date will be deemed NOT having submitted the assignment.
NOTE: If a hardcopy is not received by the above specified date for a student, then the student will eventually receive only the mark for the assignment derived from a marking sheet, and will not be able to get the more detailed feedback that could be otherwise written to your submitted hardcopy.
Mini Case: Beauty Salon
Beauty Salon is a system to be designed to manage the booking and the payment of a single beauty parlour.
Beauty Therapists: A beauty parlour has a number of staff members most of which are beauty therapists. Some beauty therapists are well-established professionals while others may just be the trainees. Different thereapists may also have different availability for work. For instance, some may not work on Saturdays while others might be on leave for a few weeks.
Beauty Care and Treatments: There are a number of beauty treatments or services a therapist can undertake, including manicure, pedicure, waxing, threading, facials and massages, to name a few. Different therapists may be able to provide different ranges of specific services, depending on the actual individuals. Some (item-based) services such as waxing will be charged per item while others (time-based) such as massage will be charged per half an hour or per hour. The fees for item-based services are fixed across all the therapists. But the fees for time-based services may vary among the therapists due to their different level of expertise.
Treatment Venues: When a booking is being made, it is possible to agree on a specific venue to conduct the services. Such venues could be other similar parlours or shops at which a particular beauty therapist also works.
Clients: Each regular client or patron will typically have her own client record set up on the system, and this will allow her to easily make a booking or make a payment. However, a non-regular customer will also be able to turn up in the beauty parlour and request a service to be done to her. Such customers don't have to create their client profiles and can still receive the services when they just turn up in the parlour, provided there are suitable therapists available at the parlour at the time.
Payments: The payment for the services of each booking or appointment will also be recorded. The payment can be made in cash, or via a credit card. The payment details will be recorded accordingly.
Business Activities: For the typical business activities, the Beauty Salon system will allow one to view which appointments have been made on any given day for any particular therapist, list all the available therapists for a given period of time, browse all the payments for a given day, and many more.