Physical database design and sql queries, Data Structure & Algorithms

Assignment Help:

In this part, 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.


Related Discussions:- Physical database design and sql queries

Binary search, An unsorted array is searched through linear search that sca...

An unsorted array is searched through linear search that scans the array elements one by one until the wanted element is found. The cause for sorting an array is that we search

Hash clash, Q. What do you understand by the term by hash clash? Explain in...

Q. What do you understand by the term by hash clash? Explain in detail any one method to resolve the hash collisions.

Steps of pre-order traversal, Pre-order Traversal The method of doing a...

Pre-order Traversal The method of doing a pre-order traversal iteratively then has the several steps(suppose that a stack is available to hold pointers to the appropriate nodes

Algorithms, 2. Write a note on i) devising ii) validating and...

2. Write a note on i) devising ii) validating and iii) testing of algorithms.

Matrices multiplication, Write an algorithm for multiplication of two spars...

Write an algorithm for multiplication of two sparse matrices using Linked Lists.

Algorithms, write short note on algorithms

write short note on algorithms

What are circular queues, What are circular queues?  Circular queue: St...

What are circular queues?  Circular queue: Static queues have a very large drawback that once the queue is FULL, even though we erase few elements from the "front" and relieve

Interest rate, explain the determination of interest rate in the classical ...

explain the determination of interest rate in the classical system.

Whether the infix expression has balanced parenthesis or not, Using stacks,...

Using stacks, write an algorithm to determine whether the infix expression has balanced parenthesis or not Algorithm parseparens This algorithm reads a source program and

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd