Database Systems and Design

 

Faculty of Computing, Engineering and Media (CEM) – Course work Specification 2019/20

Module name:

Database Systems and Design

Module code:

IMAT5103 RESIT

Title of the Assignment:

EER Modelling and SQL

This coursework item is:

Summative

This summative coursework will be marked anonymously

Yes

The learning outcomes that are assessed by this coursework are:

1. Select and analyse a problem domain (see project list in appendix) so as to identify data requirements in businesses.

2. Design and implement a database system for the identified requirements using database modelling techniques and appropriate data description and manipulation languages.

This coursework is:

Individual

This coursework constitutes 70 % to the overall module mark.

Your mark and feedback will be available to you on:

If for any reason this is not forthcoming by the due date your module leader will let you know why and when it can be expected. The Head of Studies (headofstudies-tec@dmu.ac.uk ) should be informed of any issues relating to the return of marked coursework and feedback.

21st September, 2020

When completed you are required to submit your summative coursework to:

1. Blackboard shell via Turnitin;

Late submission of coursework policy: Late submissions will be processed in accordance with current University regulations which state:

“The time period during which a student may submit a piece of work late without authorisation and have the work capped at 40% [50% at PG level] if passed is 14 calendar days. Work submitted unauthorised more than 14 calendar days after the original submission date will receive a mark of 0%. These regulations apply to a student’s first attempt at coursework. Work submitted late without authorisation which constitutes reassessment of a previously failed piece of coursework will always receive a mark of 0%.”

Academic Offences and Bad Academic Practices:

These include plagiarism, cheating, collusion, copying work and reuse of your own work, poor referencing or the passing off of somebody else's ideas as your own. If you are in any doubt about what constitutes an academic offence or bad academic practice you must check with your tutor. Further information and details of how DSU can support you, if needed, is available at:

http://www.dmu.ac.uk/dmu-students/the-student-gateway/academic-support-office/academic-offences.aspx and

http://www.dmu.ac.uk/dmu-students/the-student-gateway/academic-support-office/bad-academic-practice.aspx

Second Chance Hotel Overview: SCH Hotel is a family owned hotel in Leicester. Starting out as a hotel purely for individual customers to book for overnight stays, it has grown into a business that offers several additional hotel-based event services to clients, such as wedding bookings, business events as well as themed parties. The manual approach to the bookings for all of these services has become increasingly difficult. Staff are more frequently finding that rooms, which are used for more than one service, are being double booked. This is a big embarrassment for SCH Hotel management, who can see this impacting negatively on their widespread reputation as a high-quality hotel and service establishment. Further information about SCH Hotel and its bookings database requirements: SCH Hotel takes bookings for wedding, business events and themed parties, as well as for individual stays at the hotel. All bookings have a date and time of booking, together with a

2

unique booking code, the name of the staff member who took the booking, the credit card details for the booking reservation and the method of payment (either by credit card, debit card, cash or vouchers). The remainder of the amount to pay has to be settled on the last day of the event or individual stay at the hotel, and again the method of payment used by the client is recorded alongside the amount paid. Bookings for wedding receptions are taken sometimes one year before the actual day of the wedding. These bookings are typically taken over the telephone. The names of the bride and groom are recorded together with the details of the client (i.e., client’s full name, address, contact telephone number and e-mail address) who is making the booking and the date for which the wedding reception is required. The booking also includes details of wedding requirements, such as number of guests, the food menu required for the reception (either Menu W1, W2 or W3), the facilities of the wedding, and special request on the decoration of the wedding room. The wedding room that the client wants to book (there are currently two event rooms that are available for weddings; the Chatsworth Suite which holds 60 guests and the Fleetwood Suite which can cater for up to 110 guests) is checked to see if it is available on the date required, and if it is available then it is booked for the wedding. Bookings for business events also require the same client details to be stored (i.e., client’s full name, address, contact telephone number and e-mail address). In addition, the name and registered address of the business organisation are taken, as well as a description about the events and the date if a networking reception is required. A business event may book more than one event room, including the aforementioned Chatsworth and Fleetwood suites. SCH Hotel also currently has five small meeting rooms that can also be booked as part of a business event booking. These meeting rooms vary in size: their maximum capacities range from 10 to 25 people. Some meeting rooms have projector and whiteboard facilities. Private tea/coffee making facilities are also available if requested…the booking also includes whether or not the hotel should provide additional facilities for the events. Clients book a particular themed party by providing their client details (i.e., full name, Address, contact telephone number and e-mail address) to SCH Hotel. Special requests for a particular themed party should be considered. The number in the client’s party is also stored, and the existing bookings for that night are checked to ensure there are places. (Each themed party uses one of the suites (currently either Fleetwood or Chatsworth) as its venue, and each theme party has a maximum number of participants.). If there are sufficient places, then the themed party is booked. There is also a food menu available for themed party (either Menu T1, T2 or T3). Finally, clients can book one or more individual bedrooms for one or more nights. Bedrooms ranges from Single, Twin, Double and Family Rooms. Most have ensuite facilities. When an individual client is wanting to book one or more bedrooms, the client details are taken (i.e., client’s full name, address, contact telephone number and e-mail address), and one or more rooms are allocated for the stay period required. Information regarding whether this booking includes breakfast also needs to be recorded. People who stay for over five days will get a 15% off voucher for their next stay. People who are staying overnight because of a wedding, themed night or business event at the hotel get 10% off the normal room price. As well as being able to take bookings without the potential of double bookings, the system should support the following functionalities:

• Work out automatically the amount that a client will pay for a particular booking

• Work out the total income associated with different types of booking (wedding, business events, themed party and individual) respectively.

• Provide a report showing the number of repeat customers within a given year.

3

• Provide an overview of the amount of bedroom bookings we get for each type of event (i.e., business events, weddings & theme parties).

Tasks: You are to develop a database design (both conceptual and logical) for SCH’s bookings system, and then implement and subsequently query an ORACLE database that is derived from your database design. For this assignment, you must work individually.

Task 1: Provide a conceptual database design for SCH’s bookings database system (50%). The first task is to develop:

Task 1.1 An EER Diagram that captures the detailed requirements for SCH’s bookings database system. The EER Diagram needs to show any weak and strong entities, the primary keys for strong entities, and any relationships between entities (including any generalisation: specialisation structures). *:* relationships must be decomposed, and any actual traps identified should be eliminated using appropriate methods. Please note that this EER diagram should not list any attributes other than the primary key.

Task 1.2 For each entity, there should be an associated written list of all the attributes that the entity possesses.

Task 1.3 Any assumptions made during conceptual database design (i.e., anything that you assume that is not written in the SCH scenario) should be listed.

Task 2: Logical Database Design and DB creation/manipulation (50%) There are five components to this task:

Task 2.1: Provide a Logical Database Design for SCH’s bookings database system from your conceptual database design. This diagram is a significantly revised version of the diagram produced for Task 1.1. Only primary keys and foreign keys need to be shown in the diagram.

Task 2.2 Derive a corresponding set of well-normalised tables. A full listing of each table is required. Remember to indicate all primary and foreign key fields for each of the tables using suitable and consistent notation. All key and any non-key attributes should be listed within each table.

Task 2.3: Create the tables using Oracle DBMS or MySQL. You need to create all the tables that you identified within your logical database design. Make sure the appropriate fields are defined as key, and that other suitable data integrity rules are enforced. Each of your tables should contain your username as part of the table name. E.g. if your username is ‘mit13sf’, then if you needed a Booking table then you would create a table ‘mit13sfBooking. (Hint: make sure you create the tables in an appropriate order – for instance, those that have foreign keys cannot be created first – why? Think about it!). Failure to correctly name the tables will result in zero marks for Tasks 2.4, Task 2.5 and Task 2.6

Task 2.4: Create the four most useful indexes on your tables You need to create a total of FOUR appropriate indexes on the tables using the CREATE [UNIQUE] INDEX statement. (Hint: it may be useful to consider what queries you wish to perform in Task 2.6 first, and do not forget that primary keys do not need any user-defined indexes as these are provided automatically by Oracle). Write a short explanation as to why you decided to create each particular index.

4

Task 2.5: Data Population Using suitable Oracle statements, populate your Oracle tables with some fictitious yet appropriate test data (about FIVE records per large table and TEN records per small table (or as many rows as is relevant) should be enough).

Task 2.6: SQL Query writing Define and run SIX queries of your choice (but appropriate to the scenario). Each query should require TWO or more of the following querying facilities, (and all of these facilities should be used at least once in your set of queries) and should be properly justified as to why the query would be useful to the SCH Hotel:

o Selection of particular table columns o Inner Join of at least 2 tables

o Outer Join of at least 2 tables

o Use of count and/or another similar mathematical expression o Use of a sorting/ordering facility

o A condition using <, >, LIKE etc.

o A condition using IN, NOT NULL, or similar.

o A sub-query

Deliverables to be submitted for assessment:

1.1 An EER Diagram that captures the detailed requirements for SCH’s bookings database system. The EER Diagram needs to show any weak and strong entities, the primary keys for strong entities, and any relationships between entities (including any generalisation: specialisation structures). *:* relationships must be decomposed, and any actual traps identified should be eliminated using appropriate methods. Please note that this EER diagram should not list any attributes other than the primary key

(marks out of 30)

1.2 For each entity, there should be an associated written list of all the attributes that the entity possesses

(marks out of 10)

1.3 A list of assumptions made during conceptual database design (i.e., anything that you assume that is not written in the SCH scenario) should be listed

(marks out of 10)

2.1 EER diagram representing a Logical Database Design for SCH’s bookings database system from your conceptual database design, together with a listing of the corresponding set of well-normalised tables, including workings to indicate how these were derived from 1.3. Remember to indicate all primary and foreign key fields for each of the tables using suitable and consistent notation. All key and any non-key attributes should be listed within each table

(marks out of 10)

2.2 SQL script for table creation. Each of your tables should contain your username as part of the table name. E.g. if your username is ‘dmu12345’, then if you needed a Booking table then you would create a table dmu12345Booking. (Hint: make sure you create the tables in an appropriate order – for instance, those that

5

have foreign keys cannot be created first – why? Think about it!). Failure to name tables in this manner will result in zero marks for sections 2.3 to 2.6

(marks out of 10)

2.3 SQL script to create the four most useful indexes on your tables. You need to create a total of FOUR appropriate indexes on the tables using the CREATE [UNIQUE] INDEX statement. (Hint: it may be useful to consider what queries you wish to perform in Task 2.5 first, and do not forget that primary keys do not need any user-defined indexes as these are provided automatically by Oracle). Write a short explanation as to why you decided to create each particular index.

(marks out of 5)

2.4 SQL script Data Population. Populate your tables with some fictitious yet appropriate test data (about FIVE records per large table and TEN records per small table (or as many rows as is relevant) should be enough).

(marks out of 5)

2.5 SQL script for all queries, and a list of the corresponding results

(marks out of 20)

You may be asked to attend a viva lasting for up to 15 minutes with one or more tutors. Your mark may go up or down as a result of a viva. Failure to attend a viva, if you have been asked to do so, may result in you obtaining zero marks for the entire assessment.

Module leader/tutor name:

Chandrika Lakhani

Contact details:

chandrika.lakhani@dmu.ac.uk