Question 1
The manager of the Ogre Hostel, needs you help in building an IT to manage the hostel.
Visitors come from all over to the woodlands to their annual reunion at the hostel. Cutting costs, visitors share rooms. Rooms have numbers and are of various types. Room type must match the room type required by the visitor. But, while some visitors really want to share a room, some combinations of visitors must not share a room, and some visitors must be solitary. The IT must manage this. It must also manage the rooms of the hostel, their rating and how many visitors each can hold and how many each currently has. Visitors are billed for their stay based on the duration of their stay, the quality of the room, and the number of cohabitants they had in their room. Each visitor must provide a name and an address, and then gets an ID. Past room assignments are brought into account when the bills are made.
To increase revenue, the hostel added Swamp Tours. There are many types of tours and
visitors are encouraged to take these tours, but there must be a match between the type of the tour and the type of tours the visitor can take. Visitors are billed for the tours too. After some visitors felt somewhat unwelcome, management added a karaoke option. Visitors can choose songs from a list and join in. Most visitors get billed for this too, but dragons do not. Bowing to popular request, some visitor types even get paid for singing. By the way, visitor type should also be managed. The visitor who sings the best karaoke in any given day, this is determined by the score the visitor got, get his or her entire stay free.
The rooms are cleaned by Maids. The IT should keep track of the Maids and which
cleaned what room and when. Maids get paid by check based on the number of rooms each cleaned each month and the quality of each cleaning. The IT must keep track of these checks.
Your assignment is to build an ER diagram.
Question 2
A novice database designer read the story above and made this one table to deal with it.
The table needs to be redesigned so it will be at least in Third Normal Form.
{Visitor_ID, Date, Name, Address, Vistor_Type_code, Vistor_Type_Description, Songs,
Visitor's_score_at_song, Room, Room_Type, Visitors'_Prefered_Room_Type,
Tour_Codes_Taken}