Reference no: EM132391580

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

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.

Q1. Database modelling

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). The GRD should be in a form similar to Figure 17.9 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.

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

Attachment:- Database Design Assignment File.rar