when a customer makes a booking in person or over the phone, the clerk will first check if the customer's details are registered (some customers have pre-registered their details to make dealing with Monash Functions quicker, even though they may not have booked a function as yet). All customers are identified by a customer ID. If the customer has not already registered their details, the clerk will ask the customer to provide their details, including name, address and contact number. Once the customer is registered with Monash Functions and has been assigned a customer ID, the customer will proceed with the booking by providing information for the following questions: On what date is the function to be held? What is the start and end time of the function? (Bookings must be of one or more consecutive hours, starting exactly at the start of an hour of a specific date.) What is the number of guests expected? . Will the function be held at the customer's home or at a function room of Monash Functions (the company has function rooms in a number of local suburbs)? Will a chef, or chefs, be required to prepare the food for the function? A customer cannot book two functions on the same date and at the same start time. If a function room is required, the customer will specify the preferred suburb, and the clerk will provide the customer with a list of names and addresses of suitable function rooms to select from. Within Monash Functions, each function room is assigned a function room id to identify the room. As not all function rooms have a kitchen available for the chef to prepare the food, the clerk will need to take this factor into consideration while compiling the list of suitable rooms. The clerk currently looks at a 'Room Unavailable document, which records the start and end dates of function rooms unavailable due to maintenance. The clerk will also need to consider the function room guest capacity versus the number of guests expected. Monash Functions identifies chefs via a chef ID and has, on record, the chefs' name and contact number. If a chef is required for a function, the customer can request a specific chef (or chefs) who work for Monash Functions or can request that any suitable chef(s) be assigned to the function. Some functions are large enough to require the assignment of several chefs. The customer may indicate the type of cuisine (e.g. Western, Chinese, Japanese or Indian) to be prepared for the function to ensure the chef(s) assigned is/are capable of preparing that type of food. Only one type of cuisine can be nominated for any function. Not all chefs are specialised in more than one type of cuisine, although some are. Some functions will not be assigned a cuisine type. When two or more chefs are assigned to a function, one of the chefs is designated as the head chef. The customer must pay the total cost for the booking three days (at the latest) before the day of the function. If the payment has not been made by this deadline, the booking is cancelled. Each chef has an individual hourly base rate at which their time is charged to the customer. While function room incurs additional hourly room rates, functions held at the customer's house incurs a flat surcharge negotiated at the time of booking (primarily depending on the size of the house). The customer gets a 5% discount if the booking period is more than four hours. Assessment overview This task continues the work you have started with your team in Assessment 1A by refining/extending the model you developed and implementing it as a set of tables under your Monash Oracle database account. You will continue working in the same team for this assessment. Since this is an ongoing development process based on your Assessment 1A submission and marker feedback, you must ensure that your Assessment 1A submission and the marker feedback remains confidential and is only seen by yourself and the IT04132 teaching staff. Please note: The marking criteria for this unit will be explained in detail in your live sessions. Please make sure you attend, or in the case that you are unable to attend that you watch the recording of that explanation. This assessment supports unit learning outcomes 1, 2 and 3. Assessment details This assessment's brief must be read in conjunction with the Assessment 1A brief-i.e. your final model must encompass both sets of requirements. You may modify your Assessment 1A conceptual model in any manner you wish as you work through Assessment 1B, provided your final model meets both sets of requirements. In developing your final logical data model, composite attributes present on your conceptual model must be expanded into their component simple attributes, unless otherwise directed. If the supplementary material presented in this document does not guide you in deciding the components you may make any reasonable decision on their simple component attributes. Further discussions with Monash Functions have revealed the following: -0-0 Monash Functions Brief 2 (i) When a customer makes a booking, if a function room is required, the clerk provides the details of all the function rooms which suit the customer's needs (suburb, number of guests, desired date etc) and the customer selects, as part of the booking, the room they wish to use. (ii) Some function rooms used by MF have no kitchen, these are dealt with by cooking the food at the MF base and transporting it to the venue where it is served from bain-maries. (iii) MF is concerned that the hourly room rate charge for some of these function rooms appears to be increasing at a rapid rate. For this reason, MF would like to maintain a history of the hourly room rate charges for each function room that they use. This data will be used to identify potential overcharging issues and general pricing trends in the provision of function rooms. (iv) MF previously indicated that a chef must specialise in at least one type of cuisine, but may specialise in many. For each of these specialisations, MF would like to record the number of years of experience the chef has in this type of cuisine. Each cuisine is charged at a set rate to the guests for whom it is provided, for example, the current charge for the provision of Chinese cuisine is $30/Guest, the current charge for each type of cuisine needs to be maintained by the database. The cuisine charge is updated by MF whenever they deem it necessary (only the current cuisine charge is required in the system, no history of the changes are required). (v) MF would like to provide the option for customers to select a decoration theme for their function. Current themes of decoration which are available to customers are 'Wedding', 'Birthday', 'Kids', 'Outdoor', and 'For All Occasions. MF would like to expand the range of decoration themes that they support in the future. They would like these themes to be flexible so they can easily add new themes when necessary. They do note that some customers choose not to have a decoration theme or manage decorations themselves. (vi) Functions are charged to customers in several components: O a base rate per guest covering standard menu items, chef time, cutlery etc and the surcharge, where necessary, for holding the function at the customers' home O a cuisine rate per guest where specialist food is required (there are currently only Western, Chinese, Japanese or Indian offered, although MF intends to expand this in the near future), and O a room rate per hour where the function is held in a function room. (vi) At regular intervals (every six months or so) MF sends one of their chefs to each of the function rooms on their books to evaluate the current state of the room and its amenities (a function room is never evaluated twice in one day, however, a chef may evaluate multiple function rooms in one day). The details of all such evaluations are recorded. This evaluation may approve the use of the room but comment on minor issues which can be easily addressed, in which case the function room will continue to be allocated. If a function room fails this evaluation it is left in the system but is considered as currently not suitable for use. When a new booking is being taken for a particular function room, the room's most recent evaluation is checked to ensure that the room can be used. For a function room that is not approved for use, a re-evaluation will be initiated when the listed issues have been corrected. Tasks to complete 1. Normalisation to 3NF Perform normalisation to 3NF for the data depicted in the sample documents. Note that only one normalisation is required for the function room evaluation, you have been provided with two samples so you can appreciate some of the variety which occurs. The approach you are required to use is the same approach as shown in the normalisation tutorial solution. The normalisation must be carried out form by form, beginning by you representing the document you are working on as a single UNF form. During normalisation, you must: not add surrogate keys include all attributes (you must not remove any attribute as derivable) clearly show UNF, 1NF, 2NF and 3NF clearly identify the Primary Key in all relations by underlining the PK attribute/s. clearly identify all dependencies at the various normalisation stages (Partial at 1NF, Transitive at 2NF and Full at 3NF). You should use the same notation as depicted in the normalisation sample solutions, for example: o attr1 -> attr2, attr3 if none exist you must note this by stating: o no partial dependencies present and/or no transitive dependencies present O Carry out attribute synthesis. the attribute names used in your normalisation and those on your subsequent logical model must be the same. Your normalisation must be carried out in an MS Word document in your group's private MS Teams channel so that a full development history (stage by stage) is available. 2. Logical level design Based on your Assessment 1A conceptual model, your markers feedback, your reading of this case study and the normalisations you carried out in Step 1, prepare a logical level design for the Monash Functions database. In generating your logical model ensure: The logical model must be drawn using the Oracle Data Modeler. The information engineering or Crow's foot notation must be used in drawing the model. Your logical model must not show datatypes. All relations depicted must be in 3NF. You are required to add at least one surrogate key to your design (you are free to select the most appropriate relation to make this change in). All attributes must be commented in the database (ie. the comments must be part of the table structure, not simply comments in the schema file). Check clauses/look up tables must be applied to attributes where appropriate. You must include the legend as part of your model. If your laptop username is a nickname please edit the legend panel to show your actual name. Note that your GIT repository must clearly indicate your development history with multiple commits/pushes as you work on your model. 3. Schema Generate the schema for the database in Oracle Data Modeler and use the schema to create the database in your Oracle account (this should be tested in your individual Oracle accounts - a group Oracle account is not available). The only edit you are permitted to carry out to the generated schema file is to add header comment(s) containing your details (student name/ID) and the commands to spool/echo your run of the script. In generating your schema file ensure you: Capture the output of the run of your schema statements using the spool command. Ensure your script includes DROP table statements at the start of the script. Name the schema file as mf_schema.sql. Give the schema file an extension of .sql.