Specific Assessment | Good Grade Guarantee!
Learning Outcomes: LO1: Construct a relational data model
LO2: Execute the techniques of data normalisation to 3NF
Criteria: • Understand a set of requirements and construct a suitable data model to represent them in a computer system
• Demonstrate an understanding of normalisation process by normalising the designed model to 3NF and documenting/explaining each step as necessary
• Explaining the pros and cons of normalised databases in general terms
As an analyst/developer intern, you work for a small, local IT consultancy called Bolt-On Solutions that specialises in helping small to medium sized enterprises (SMEs) take advantage of modern computing and the efficiencies they bring. Bolt-On Solutions has just had a meeting with a new local business; The Cat’s Whiskers Hotel.
You have been asked to design the database for the development of a computerised booking system for The Cat’s Whiskers Hotel.
The Cat’s Whiskers Hotel
The Cat’s Whiskers Hotel is a new small, premium boarding cattery on the outskirts of Bolton, adjacent to a farm in quiet countryside, intending to open fully in the summer of 2020. Being a cat-only business (i.e. no dogs are allowed to stay), the cattery will offer a calm, luxurious experience for pet cats of all kinds while their owners are away, creating a stress-free, warm and safe environment for their customers’ beloved felines while receiving the highest standard of care and attention from the cattery’s cat-loving proprietor Joe Felix and his two highly-trained staff members Claire Garfield and Karen Heathcliff.
The cattery’s feline guests will make themselves at home in one of 10 well-equipped, specially floor tiled heated rooms which feature (among other things) activity walls, scratching posts, TVs displaying goldfish, mice or bird videos, comfortable and easy to wash bedding, and are designed to create a cosy environment for cats to stay, play, relax and feel safe in. Whilst each room has a secure double-glazed door and a window, the window can be opened by staff (weather permitting) to allow the cats access to their own secure, enclosed open-air private balconies.
Each room also has a networked IP pan-tilt-zoom CCTV camera, a feed from which is also connected to the cattery’s overall site-wide CCTV system. While the overall system is for security and liability reasons, the system also allows cat owners to access the CCTV cameras in the rooms their cat(s) are staying in via an existing mobile app to see their pets even while on holiday and also to appear to and speak to them on the room TV to provide additional comfort to their cats. This is an optional, chargeable upgrade of £1 per night, regardless of the number of cats or rooms used for the booking.
The current system
Ahead of a full opening and while IT systems are being built and tested, the cattery is accepting a limited number of bookings each week using manual systems. Currently, a cat owner will telephone, email or call at the cattery in person to make an enquiry to see if room(s) are available for their required dates for their cat(s). Normally a room accommodates just one cat but where owners have multiple cats, up to 2 can be accommodated in “standard” rooms, whilst 4 can be accommodated in a “family” room if desired. The cattery has 3 “family” rooms and 7 “standard” rooms. The rooms are numbered 1 to 10 and the first 3 are the “family” rooms.
If the dates can be accommodated a booking is provisionally made in the bookings file and a 10% deposit taken. The bookings file will contain details of the owner’s name, the cats’ names and also which room(s) they’ll be staying in on documents that will be later fully completed by the owners when they arrive (the booking document).
On the day an owner arrives with their cat(s), the member of staff on reception will ask the owner to complete a document for each cat and two separate documents for the owner.
For the owners, the first document contains details about them, their full name, address and how to contact them in an emergency. This document is then stored in the owners file.
The second owner document for the owners (the booking document noted above) relates to the booking itself and records if they are paying for the “standard” or “luxury” meals option or have bought their own food and whether they have chosen the remote CCTV access option.
The cat’s document outlines their name and ID chip number, dietary requirements, date of last vet vaccinations for cat flu, what kind of cat litter is required and a description of each cat’s character/temperament along with anything else staff need to be made aware of, such as how they like to play or medical problems or a specific vet to use in case of emergency. This document is stored in the feline guests file.
Once both parts of the document have been fully completed and the person taking the booking is satisfied the cat(s) can stay, the remaining 90% payment is taken by cash or card, with the payment recorded in the payments book.
The costs of staying are priced on a daily basis, based on the number of cats staying in a room:
1 cat £15.00
2 cats sharing £20.00
3 cats sharing £25.00
4 cats sharing £30.00
Hence 3 cats sharing a room and staying for 2 days would cost £50, whereas if they had a room each it would cost £90.
If a “standard” optional meals package is required then the cost is increased by £1.50 per cat, per day. Whereas, for the “luxury” meals package the increase is £3 per cat, per day.
Once payment has been taken, cats are then regarded as having been “checked in”. Then – accompanied by their owner – they are taken to their room(s) and settled in by the staff and their owner before they leave them to enjoy their stay.
Daily, the staff checks the feline guest file for each cat and feed, play with the cats and clean litter trays several times according to instructions given and note any observations about or issues with the cats in the file also along with the name of the staff member who interacted with/checked the cat on that occasion. If medical attention is required a vet is contacted and the owners alerted and the feline guests file is likewise updated to record this.
When the owners return, cats are “checked out”. The owners are taken to see their cats in their rooms and the cats are returned to their travel boxes and handed back to their owners. The feline guests file is updated to show that the cats completed their stay on that date and if any additional payment is required (such as to cover vet’s bills) then it is taken at this time also.
The Replacement Computerised System
It is expected that the eventual replacement computerised system will support the following operations:
◦ Checking room availability for a given date range
◦ Booking a room
◦ Checking in cat(s)
◦ Checking out cat(s)
◦ A simple summary overview (list) for a given day showing for each room:
▪ Whether the room is unbooked or not
▪ Number of cats in that room and their names
◦ Viewing details for any given room for a specific date range; this should show either
▪ That the room is unbooked for all or part of that period; or ▪ The full recorded details of the cats occupying the room for the period and their requirements etc. along with the total cost of the booking ◦ Recording payments made ◦ Searching for/viewing a named owner’s details (their details and the cats they own)
Requirements / Deliverables
For this first assignment you are required to:
• Design the data model for a computerised system for The Cat’s Whiskers Hotel by producing an Entity-Relationship model using a suitable recognised notation, taking fully into account the explanation of the manual system above
• Having initially built your data model to 1st Normal Form, normalise your data model down to 3rd Normal Form (3NF) via 2nd Normal form.
You should regard this work as to be presented to senior Bolt-On management and developers. Submit your completed work via Moodle on or before the deadline shown above as a design report.
Note that you will not be asked to build the actual application in this module, your tasks are merely to design the database model (in this assignment) by producing a data model and (in the second later assignment) construct the database schema and then verify the model design by implementing it and running queries against it using SQL.
Bolt-On would also like you explain why normalisation is necessary and what the advantages and disadvantages of normalising to 3NF are in general. This should be included as a section in your design report.
While normalising your data model, you must show and explain each stage of normalisation for each table (as appropriate), do not simply design the database in 3NF from the outset and ignore 1NF and 2NF.
You will find it very helpful to create some test data while doing this in order to visualise the scenario better, so you will need to “invent” the details of some imaginary customers and their cats who are to be guests at the hotel.
QUALITY: 100% ORIGINAL – NO PLAGIARISM.
- **REMEMBER TO PRECISE PAGE NUMBER**
- Hit The Order Button To Order A **Custom Paper**