Reference no: EM131662201

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 evaluate or verify the 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.

Table Normalisation and Outer Joins

Mr X is planning on creating a database for his own beauty salon. However, his salon is much simpler. It's on the same venue and offers only itemised services (i.e. no timed services). Each booking a client makes simply books a particular therapist for several therapy items on a timeslot. Mr X had a look at our design on the top right in ERD and believes that his solution is even simpler and should be sufficient for his core business anyway. His database has just 1 table with the following attributes

bookingId	an ID unique for each booking
clientId	an ID unique for each client
clientName	client name corresponding to the clientId
staffId	an ID unique for each therapist
staffName	staff/therapist name corresponding to the staffId
item	therapy item
price	price charged for the corresponding therapy item
start	start time for the booked timeslot
finish	all booked treatments finish by this time

and his first few records are like (the attributes are in the order of those listed in the above table)

B1	C1	Clair	S1	Sandra	nail polishing 10		10:00 1/9/2015 11:00 1/9/2015
B1	C1	Clair	S1	Sandra	facial	20	10:00 1/9/2015 11:00 1/9/2015
B2	C2	Cathy	S1	Sandra	manicure	30	13:30 2/9/2015 14:30 2/9/2015
B3	C3	Camelia	S2	Sarah	nail polishing 10		14:00 2/9/2015 14:30 2/9/2015

Mr X can of course easily calculate the total cost for each booking from this database. Hence Mr X normally just sits back comfortably with a cup of coffee, looking over the bustling people passing by his salon, thinking why on earth does anyone ever need a database designer.

Normalisation:

1. Is this table already in 2NF and why? If not, normalise it to 2NF. Don't jump all the way to 3NF.

2. If any table created from the above normalisation to 2NF is not in 3NF, then normalise it into 3NF.

3. We note that the dependencies are implied by the business rules set out by the mini-case and our explanations at the top of this question.

Outer joins:

4. Complete the Additional Exercises β(b) and β(c) for Practical 11. Provide the SQL, and the screenshots of the results.

Logical Database Design

5. 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, and you are allowed to make use of any part of our above displayed ER diagram skeleton to incorporate into your design in any way you like if you feel your original design is not in a state to be implemented later. The actual assessment of this part is in the point 6 below. While there can be a variety of acceptable designs, we here attach one simplistic rough sketch for the comparison purpose.

6. For all the relations that arise from this ("first-cut") ER diagram, list all those (in schemas) that are already in 3NF. If there are some relations that are not in 3NF yet, list them as well.

7. 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 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. Identify and discuss the potential data redundancies or anomalies that may still exist in your design, if any.

Physical Database Design and SQL Queries

For the physical implementation of the database, students are allowed to implement the following simplifications in their table and data design.

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.

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.

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.

Each Booking books exactly 1 therapist for a timeslot during which the therapist may perform either a timed service or an itemised service, or both.

For the timed service, the hourly rate is allowed to be fixed to exactly the same as twice the half- hour rate. Hence one rate will suffice.

8. 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 3 tuples or more per table, but should be sufficient to illustrate meaningfully the working of the general queries to be completed below. List the content of your tables with screenshots. Screenshots of active windows (under Microsoft Windows) can be obtained by pressing CTRL-ALT-PRTSC keys together, see the example on the right. Your screenshots must contain your username as in the above example, and you may list several tables on a single screenshot.

9. Write in SQL the commands to complete the following queries, and show your results in screenshots.

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

(b) List all the client names and their corresponding telephone numbers.

(c) For all the beauty therapists who hold one or more qualifications, list the therapist names and their corresponding qualifications.

(d) For a given day, say, 2015-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.

(e) List all the itemed services along with the therapists who can provide such services. The list should be sorted alphabetically in the service names.

(f) List the names of all the clients along with the corresponding total number of bookings.

(g) Find the name of the therapists whose hourly rate for the timed service is the cheapest, along with their actual hourly rate.

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

11. For your final designed database, find a scenario in which data integrity can not be ensured by your current primary keys and foreign keys, nor by adding directly more of such keys. Write a SQL statement that will determine if such a problem exists or not for any given state of the database.

12. A single plain-text file containing SQL statements for creating all the tables and making all the queries. 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. Marks will be deducted in the corresponding questions if this SQL script in plain-text file is not submitted.

13. Each student must state explicitly who he or she once teamed up with if that person is not currently the group member for the submission, unless no shared work is involved. Students are not permitted to have shared work for this assignment with more than one person (the team member) including potential former team member, unless approved by the unit coordinator in writing.

Attachment:- case.rar