i have uploaded the files of the assignment.
Assessment Cover Sheet Student Name Student Number Course Title Diploma of Information Technology Lecturer Name Assignment Name/Number Practical Assessment Due Date I declare that This assignment is my own work, based on my personal study and/or research I have acknowledged all material and sources used in the preparation of this assignment, including any material generated in the course of my employment If this assignment was based on collaborative preparatory work, as approved by the teachers of the unit, I have not submitted substantially the same final version of any material as another student Neither the assignment, nor substantial parts of it, have been previously submitted for assessment in this or any other institution I have not in part, or in whole, or otherwise plagiarised the work of other students I have read and I understand the criteria used for assessment The assignment is within the word and page limits specified in the unit outline The use of any material in this assignment does not infringe the intellectual property/copyright of a third party I understand that this assignment may undergo electronic detection for plagiarism, and a copy of the assignment retained on the database and used to make comparisons with other assignments in future Signature Date Case Study: Amazing Restaurant The world-renowned curry chef Mr Amazing opened the Amazing Restaurant in 2017. The restaurant provides authentic dishes from the foothills of the Himalayas to the hot seas of the Indian Ocean. The restaurant has a seating capacity for 300 customers. The restaurant’s recent growth no longer makes it feasible to maintain customer, table booking, order and day-to-day activities using its manual systems. With the data stored in the database, management will be able to ensure that data are current and more accurate than in the present manual systems. In addition, Mr Amazing will be able to obtain answers to their questions concerning the data in the database easily and quickly, with the option of producing a variety of useful reports. In lieu of this, Mr Amazing, has decided to contact your consultancy company to set up a database to deal with the day to day running of the business. After careful investigation and analysis, your consultants realise that it is not all as calm as is first perceived. The restaurant has a booking service where customers can book their tables beforehand and also book a certain table if they wish. Customers need to provide their title, full name (first and last), city and phone number. The staff checks if the customer is in the database and uses the existing record. If the customer is booking for the first time, a new record is created. When making the booking, the date, time, number of people that will dine in and table number (optional) information are requested. The name of the staff who took the booking must be recorded. Each booking has a default 2-hour timeslot, which can be changed in case the customer wants to stay longer. Upon entering the establishment, customers need to speak to a receptionist, who checks whether they already have a booking. If the booking exists, the customers are taken to their table. If the customer does not have a booking, the receptionist checks the availability, if the customer is in the database and if there are tables to accommodate, the receptionist adds the booking for the customer before taking them to the table. Once settled, customers then are served by a staff member who takes the orders. Mr Amazing also wishes to publish a recipe book and because of this he needs to know what ingredients are needed in the different dishes. Mr Amazing also thinks that this would help in training new staff and if the chef leaves then he would not take all the knowledge away with them. Specific Requirements: In order to meet the business needs required to design the following database element: structure, indexes, queries and or reports To ensure consistency of the data in the database you are required to develop data validation rules to ensure the correctness of data entered into the database table; identify referential Integrity constraint and database constraints. Below is a list of checks you should implement: 1. Staff gender should be recorded as ‘M’ or ‘F’. 2. Ensure that every customer has a telephone number which is unique 3. Staff numbers should be generated automatically using a sequence. Task 1: Analyse the scenario to determine the database requirement and design data structure A. Business rules and Assumptions What to do The above background information describes the scenario surrounding the software requirements for Amazing Restaurant. From this information, develop the functional requirements, business rules summary and a conceptual model to begin the development process. What to submit: Write your report in a word documents file called SID_businessfunctreq.docx containing your documented findings. Please include the summarised business rules; the conceptual model; and your completed logical data model sign-off form, as they are specified in the below questions. 1) Document the functional requirements for Amazing Restaurant. If you are unsure or wish to clarify any requirements, consult with your trainer who will act as the client. Be sure to only document requirements that fall within the boundaries of the system to be developed. 2) From the details given in the scenario further above, create a summary of the business rules surrounding the software to be developed for Amazing Restaurant. If you are unsure or wish to clarify any business rules, consult with your trainer who will act as the client. Be sure to only document rules that fall within the boundaries of the system to be developed. 3) Draw a conceptual model of the database to power these functional requirements whilst still meeting the business rules. Identify just the entities and relationships between these entities in your conceptual model. For each relationship, indicate the maximum cardinality/participation in your diagram i.e. 1:m, 1:1, m:m. You may show minimum cardinalities/participation in your relationships, but it is not compulsory. 4) You will need to expand your conceptual model into a logical model where you will include attribute (columns)s for each entity along with their data types. Primary keys and keys for each entity. Use Toad data modeller or any other software program to create your conceptual and logical models and save them. Insert your completed conceptual and logical models as images into your word document that you will be submitting. You are not allowed to submit any hand drawn diagram. B. Database development Logical Concept What to do Using Toad Data Modeler, create an ER model diagram (also known as EER) called ‘AmazingRestaurant2’ showing each entity from the conceptual model. Complete the data structures by including all the attributes (columns) for each entity (table) along with their data types. Primary key attributes must be shown. For any column that is an auto-incrementing column, indicate it by ticking the property for that attribute (at this stage, do not worry about including indexes, not null constraints or any other constraints for any of the attributes). Look to see if you have any m:m relationships in your conceptual model. If you do, you will need it break each one down into two 1:m relationships with a connecting (associative) entity in between them. If there are any new entities or entities to remove as you change from conceptual to logical, then go ahead and make the changes. 1) Update your ER model by going through every attribute (column) in every entity (table) to show whether the column: a. is NOT NULL or whether nulls are allowed b. values are UnSigned for numeric data types 2) MySQL will automatically create indexes on primary key attributes, foreign key attributes, and unique attributes. Update your ER model by creating indexes on any other attributes you believe justify an index. Normalisation Use normalisation to validate that your ER design is valid. If you have ‘denormalised’ your design, explain the reasoning for your denormalising and what changes you made as a part of the denormalising. Data Dictionary Document the tables that are derived from this ER diagram that you have created. List all the attributes and their characteristics such as attributes name, description, data type, format, primary or foreign key. Save the work into SID_businessfunctreq.docx. Create the Database 1) Using Toad data Modeler, generate an SQL script from your ER model of the database design. Put the generated SQL script into the Word doc as your answer to this problem. 2) Run this generated SQL script in a query window to physically create the database. Be careful if you receive errors on foreign key constraints that the foreign key and primary keys being connected are exactly the same data type and are identically signed 3) Check that both primary and foreign key are EXACTLY the same data type i.e. INT is not the same as SMALLINT etc 4) Check that both that both primary and foreign key are either signed or unsigned for numeric data types. Having physically created the database, you can compare this back to your conceptual and logical designs and ensure it is correct making any changes necessary. What to Submit · Database requirement analysis information to file SID_businessfunctreq.docx · Generated SQL script and checks for the specific requirements. Include screenshots as evidence that data validation has been implemented. Save the scripts in the file SID_businessfunctreq.docx · As evidence you have successfully created the database, please do the following: · export the database tables and submit the sql file · use Snipping Tool or some other technique to get a screenshot of the created tables in the Navigator Schema window of MySQL. Save it to the word file called SID_businessfunctreq.docx. Populate the database What to do: 1) Populate some sample data into your database in order to perform queries. You will need to show screen shots of your populated database tables in your final report. 2) Ensure you have a suffice amount and diversity of sample data in the database to reflect the Amazing Restaurant scenario to answer the questions below. C. SQL queries What to do Use the database to design queries. Write SQL query statements to generate output reports for the following. Single Table Queries 1. Display the list of unique cities where the customers lived in. Use 'State Address for Customer' as the output header. 2. Display the city and last name of customers who live in cities containing a particular string (such as 'syd'). This may vary based on the data in your table. Sort the results based on the customer's city in ascending order and then by the customer's last name in descending order. 3. For employees who were hired between certain dates (please specify the range of dates based on your