Develop a demonstration prototype system

Assignment Help Database Management System
Reference no: EM131270517

Database Management for Business Individual Project: Database Case Study

Aim: To give you practical experience in with database modelling, normalization and writing SQL statements.

Background Information

A local Sydney hotel requires a database system to manage their bookings and payments.

When a guest rings up or comes into the hotel to book a room for the first time they are asked to provide their name, address, and telephone number.

Guests may make reservations for a room which includes the date and time, number of adults, number of children, expected arrival date & time, expected departure date & time, and notes. Reservations may be confirmed and / or cancelled.

When the guest checks in to the hotel reception enter a record of the room number, and check in date & time. Guests may also check in without a reservation.

When the guest checks out then the checkout date & time, and number of days are entered into the database. The number of days is calculated but may be overwritten by a manager and the guest is charged for the accommodation plus additional service charges including restaurant, dry cleaning, valet parking, room service, etc.

Once the checkout details have been completed then the guest is invoiced for the accommodation and also additional service charges. Payments are recorded for each room booking. Payments are expected to be in full. However, the system allows multiple payments to be recorded against each room booking.

The database system also needs to keep a record of database users, staff, rooms, room type, facilities, payments, payment types, and guest notes.

Assumptions

Some service charges have a fixed cost. However, the charge amount may be adjusted up or down when charges are entered against the guest booking record. Total charges are calculated by the system by multiplying the charge amount (price) by the quantity.

System Requirements

The system is a prototype system and as such is not a full production version. You will be required to enter a representative sample data into your tables in order to test the design and operation of your database. You are required to import the sample data provided into your tables and you are required to enter at least two new records of your own in some of the tables.

Project Specification

1. Part A:

Using MySQL, you are required to develop a demonstration prototype system that handles hotel bookings and payments. Use MySQL to create a new database called HMS. Create tables according to your ERD. Follow a standard naming convention for table names and also field names. Avoid using spaces and any special characters in table and also field names. Use underscore_case or use camelCase to separate parts of a name.

Create relationships between tables and enforce the referential integrity as shown below.

Relationships:

  • Guests can have one or more bookings.
  • Each room booking can have numerous service charges associated with it.
  • Each room is classified by room type, such as deluxe, suite, or twin share.
  • Each room is also provisioned with various facilities such as TV, spa, etc.
  • Each guest record may require one or more notes so as to keep a history of information related to the guest record.
  • Notes may be assigned to a particular staff person (or database user) to follow up.
  • Each room booking may have one or many payments and each payment is identified as to the payment method.

5. The database should include suitable validation and integrity checks as well as appropriate referential integrity checks. That is, AS A MINIMUM, your system should ensure that the following events cannot occur:

Referential Integrity Constraints:

  • A booking record cannot be entered for a guest that does not exist.
  • A guest cannot be deleted for which a reservation or a booking has been recorded. Similarly a guest cannot be deleted once notes have been entered for the guest record. Likewise, staff (users) cannot be deleted once staff persons have been assigned to follow up a note.
  • A booking cannot be deleted once the booking has a service charge or payment details associated with it. Similarly a service type cannot be deleted once the service type has been entered against any service charge detail record for a guest booking.
  • Only service types that have been entered into the service types table may be entered as charges for service charge details.
  • Rooms cannot be deleted once rooms have been assigned to a guest room booking record.
  • Payment methods cannot be deleted once payment methods have been recorded against payments and room bookings that have matching payment details cannot be deleted once payment records have been entered.

Add at least two new records into the appropriate tables to include your details as a guest, room booking details of your own, and notes details related to your guest record.

2. Part B

Use the Hotel Management System (HMS) database that you created in MySQL to design and execute SQL queries that answer the following questions.

Number your answers to each question clearly. The answer to each question must be tabulated as shown in the example below and include the SQL statement and also the output that is produced when you execute the statement in your database. The output includes the records that are listed and also the message that appears when you run the SQL statement.

1. List the first name, last name of guests (join guest first and last name with a space in between and use the alias Guest Name for the column heading), and email address (for all guests that have an email address only). Sort the output in ascending order by the guest last name.

2. List the room number, rate, and number of beds for all rooms that have 2 beds.

3. List the total amount owing on service charges for each guest grouped by guest last name and the service description. Use the alias "Total Amount" for the sum of the service charges for the guest. Sort the output in ascending order by the guest last name.

4. List the guest last name, first name, mobile, and email for all guests that do not have a mobile phone number recorded in the guests table. Sort the output in ascending order by the guest last name, and then first name.

5. List the guest last name for all guests that have a suburb that has the word 'hill' anywhere in the suburb name. Sort the output ascending order by the guest name.

6. Count the number of rooms grouped by room type. Use the alias "Count of room type" for the count.

7. List the room number and facility type for room numbers 18 or 19. Sort in ascending order of the room number, and also the facility type.

8. List the guest first and last name for all guests that do not have a room booking entered in the database.

9. List the payment method description and sum of payment amounts for all payments which were made after 1-July- 2014.

3. Part C

Write a page to the department manager that describes your experience building the database. You can discuss any challenges / difficulties that you experienced or solutions that you found. Comment on any limitations and / or strengths of your database design. Comment on whether your database meets all the system requirements as specified in Part A Question 4. Avoid making excuses or comments that reflect negativity. Include an acknowledgement of all students you have spoken to about the assignment.

Attachment:- Project Assignment.rar

Reference no: EM131270517

Questions Cloud

Market fail in the presence of an externality : It is possible for an activity to have both positive and negative externality. Identify three activities that result in both positive and negative externality and carefully explain the positive and negative externality that results.
Managing dynamic arrays and grade management : Managing Dynamic Arrays and Grade Management- Managing dynamically sized arrays can be a pain while dealing with memory on heap. Memory leaks, dangling pointers, etc can lead to invalid memory accesses/segmentation faults that halt your program ab..
Determine the condition of the outlet steam : Potential and kinetic energy changes are negligible. Determine the condition of the outlet steam by giving either a temperature or a quality
What is continental drift : What is continental drift? What was Alfred Wegener's hypothesis for continental drift, and why was his idea not accepted? Explain the evidence collected in the last.
Develop a demonstration prototype system : ISY103 - Database Management for Business Individual Project: Database Case Study. Using MySQL, you are required to develop a demonstration prototype system that handles hotel bookings and payments. Use MySQL to create a new database called HMS
What are the fundamental flaws of given thinking : After the deal was signed, many Finnish companies expected that contracts and money would start rolling in by merely calling up McDonnell Douglas.- What are the fundamental flaws of given thinking?
Use the naïve and the econometric hedging ratios : Use the naïve and the econometric hedging ratios to calculate: Actions to take, Return from Cash Market, Return from futures market, net return from cash and futures markets and net realized price of live cattle (cents/lb.)
What was alfred wegener hypothesis for continental drift : Provide a brief summary of how the scientific method is applied to develop the theory. Explain 1 example of invention or innovation that was used or is currently used to collect more information on the continental drift theory and ..
Identify a potentially attractive target market : Identify a potentially attractive target market for the company's future international expansion strategy. You will be provided with 3 specific countries below to choose from.

Reviews

len1270517

11/8/2016 6:05:04 AM

Need as per the instructions along with the screenshots. Please make it proper as my career depends on it. A soft copy of your assignment documentation report must be zipped and uploaded to AIH Moodle. The SQL that can be used to restore your database should also be uploaded to AIH Moodle. You can create the SQL for your database as follows: Use the mysqldump command to create a text version of the database. Use mysqldump to create SQL file that contains a list of SQL statements which can be used to restore/recreate the original database.

Write a Review

 

Database Management System Questions & Answers

  Examine hard-to-obtain data from two separate databases

You are interested in examining some hard-to-obtain data from two separate databases. Each database comprises numerical values - so there are 2n values total

  Design a database schema

Design a Database schema

  Your company has put in the request for a new database

your company has put in the request for a new database system and you have been tasked with architecting the security

  Describe the values of the two business intelligence tools

Estimate the costs and describe the values of the two business intelligence tools you have identified from Part 1 of this discussion based on their functionality, integrating architecture, and benefits to the enterprise. Provide your rationale

  Oracle has many features for managing and tracking users we

oracle has many features for managing and tracking users. we have discussed user accounts with username password

  List the sales for each customer

Each query is worth 2 marks. These tables exist in sphinx and are owned by the user dtoohey. You may, if you wish, create your own copies of the tables under your own account. If you do so, you should ensure that you copy the sample data in dtoohe..

  Determining the actual size of these database constructs

Discuss your thoughts on the effects of indexes, data types, filegroups, and transaction logs on space considerations.

  List the name of employee and total hours

1 List the name of employee and total hours he works on the project (use outer join so that employees who don't work on project will be also listed withzero project count ) 2 List the name of project that ‘chen’ does not work on (request: must use NO..

  Complete development of a relational database

Complete development of a relational database. You are to do the following: Use Visio to create the appropriate diagrams. Create a script named mdbXXX.sql (where XXX are your initials) that Create the tables in 3NF needed to implement your DB schema

  Display the last name and phone number of all dog owners

Display the last name and phone number of all dog owners. Use a subquery to do this. Display the first and last name of owners and the type of animal of all unknown breeds. Display the pet name and owner last name of all dogs.

  Write the statements to save the contents of the richtextbox

Create the necessary event handler that will execute when the user clicks the Save button to save the file in a rich text format.

  Which is not a factor to consider in software evaluation

Which is not a factor to consider in software evaluation?

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