300941 Database Design and Development Assignment

Assignment Help Database Management System
Reference no: EM132391526

300941 - Database Design and Development (Advanced) Assignment - Western Sydney University, Australia

Q1. Database modeling

In this part, you are asked to design a database to support a Beauty Salon Booking System. The major business requirements are summarised below in the Mini Case: A Beauty Salon Booking System. In this part, you are asked to design a database to support a Coach Booking System. The major business requirements are summarised below in the Mini Case: A Coach Booking System. You are asked to develop a detailed Entity-Relationship model for this mini case. Your ER model should consist of a detailed ER diagram integrated with itemised discussions on the features of the entities and relationships and all the assumptions you made where applicable. The ER diagram and the accompanying document should identify keys, constraints, entity types, relationship types, specialisation/generalisation if any, etc. 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 Crew Foot notations), and the ER diagram should be strictly in the way the textbook uses for. We note that our past experiences show drawing an ER diagram on Microsoft Powerpoint gives one better control and flexibility when compared with drawing diagrams with Microsoft Word.

i. 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 meaningfully and well designed, and should also include all relevant and necessary aspects, and indicate any supplementary business rules if you decide to introduce.

ii. Map the above ER diagram into a global relation diagram (GRD). Include in the diagram all the primary keys, foreign keys, and the multiplicity constraints.

iii. Please note that an ERD is the artefact of the conceptual database design phase, while a GRD is the artfact 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.

Q2. More analysis and SQL

i. 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 if you wish.

ii. Write in SQL the commands to complete the following queries, and show your results in screenshots. Where parameters for the queries below are not completely specified, the parameters should be chosen so as to generate non-trivial (non-empty) results for the queries.

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

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

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

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

(a) For a given coach and a given date, list all the names of the clients who have a booking with the coach for that day.

(b) List all the coaches and their respective total number of coaching hours that are ever booked for.

(c) For a given date, say 1 Oct 2019, list all the coaches booked for that day, the corresponding booked coaching timeslot and venue, and the name of the client. The output should be properly sorted.

(d) For a given timeslot, say 1 Oct 2019 between 10-11am, and for a training item, say tennis or anything of your choice, list all the coaches who are still available, that is, who have not been booked for the timeslot yet.

iii. For your final designed database, find a scenario in which a relatively prominent business data integrity can not be ensured by your current primary keys and foreign keys, nor by adding directly more of such keys or check clauses in the created tables. In other words, the data integrity ensured by the keys within the database may not be enough to ensure all the data integrity within the business context. Write a SQL statement that will determine if such a problem exists or not, and where, for any given state of the database.

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

Attachment:- Database Design and Development Assignment File.rar

Reference no: EM132391526

Questions Cloud

Probability of the whole piping system failing : In order to transfer a liquid from point A to point B, two identical pipes in parallel are installed. During cold weather, the probability of one of the pipes
Find the mean of this probability distribution : Suppose that the probabilities are 0.5, 0.25, 0.15, and 0.10 that 0,1,2, or 3 students are absent from a certain class on any day.
COS80013 Internet Security- Assignment Problem : COS80013 Internet Security Assignment Help and Solutions-Swinburne University of Technology Australia-Discover and analyse these new technologies and security.
What is the probability that the mechanic can sleep : What is the probability that the mechanic can sleep in for an hour? What is the probability that the mechanic will lose the extra hour and have only 15 minutes
300941 Database Design and Development Assignment : 300941 - Database Design and Development Assignment Help and Solution, Western Sydney University, Australia - Design database to support Salon Booking System
Probability in six randomly selected flights : Use formula for the multinomial distribution to determine the probability in six randomly selected flights, one will arrive early, three will arrive on time
Point estimate for the proportion of heads : What is the point estimate for the proportion of heads in all flips of this token? Round your answer to 2 decimal places.
Reflecting on the above scenario identify what the : Reflecting on the above scenario identify what the Registered Nurse assumed? Why would the Registered Nurse have assumed this? What are your own values, assumptions, beliefs, and or biases about same sex couples? What are your own values, assumptions..
Calculated value of bryce hypothesis test : To three decimal places, what is the calculated value of Bryce's hypothesis test?

Reviews

len2391526

10/23/2019 3:42:48 AM

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. All required screenshots must be clearly readable, and the relevant text in on the screenshots must be directly legible on a normal A4-sized printout of the submitted document. Otherwise the screenshots will be deemed having not been submitted. Students' main document submitted for their answers to this Assignment must be written in Microsoft Word, not in PDF.

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

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