Question

<sub><sup>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.</sup></sub>

_{^{•   Design and develop database for amazing restaurant
•   Design and implement Front end interface using java standard widget toolkit SWT for following:
•   User Login – must request username and password
•   Dashboard (users should only view the options according to their access level) – the dashboard should display the buttons for Booking, Table Assignment, Table Order, Total Bill and Create Staff (admin users only)
•   Make Booking – performs a table booking at the restaurant
•   Booking list – searches for bookings on a particular date, allows to edit the booking (amend dates and delete bookings)
•   Table assignment – assigns a table to the booking
•   Table order – GUI only
•   Total bill – GUI only
•   Create staff – only admin users will be able to access and create a new staff user.
•   Edit Staff - only admin users will be able to access and edit a staff user
•   Connect the Java application to the database using JDBC}}

_{^{Time frame for the project completion is 2 months. Available budget is 20,000.}}

_{^{Students are asked to form groups of 3 -5 students – with one student acting as a Project manager. Your primary tasks are:}}

_{^{1.   Prepare a Project Plan for design and develop database for amazing restaurant.
2.   Design and implement Front end interface using java standard widget toolkit SWT
3.   Test the application
4.   Prepare a full report that consists of all given information. Compile the answers to the given tasks, and format them with project report template.
5.   Prepare a power point presentation. Each group is to give 5 minutes presentation on their plans.}}

_{^{The assumed milestones are:
•   You will gather data/information about the business and perform requirement modelling.
•   You will translate the business requirements into technical specifications.
•   You will present your project plan to the management for final approval prior to implementation.
•   You will maintain and monitor the amazing restaurant system.}}

_{^{The assumed requirements are:
•   You will plan for designing an interface for administration, staff and customers.
•   You will plan for building amazing restaurant database system using either MySQL or MS-ACCESS or other tools of your choice.
•   You will plan for building online interface and access point (with login facilities)}}

_{^{We assume that you have gathered all relevant information from previous assessments.
Complete all given tasks.}}

_{^{Task 1. Identify the Scope, Time, and Cost constraints for the Amazing Restaurant project. Develop project charter and obtain sign-off. (See appendix A)}}

_{^{Task 2. Conduct Feasibility Study and outline if the project is technically and operationally feasible?}}

_{^{Task 3. Use your information-gathering techniques to collect more information about the project. Search for competitors (Other education providers) and find their services.
Which type of questions is more appropriate (e.g. open ended, closed ended, range of answers)?   
Prepare short interview questions to ask the director of Amazing restaurant. Ensure the interview questions are of high standard.
Task 4. Define System Development Life Cycle (SDLC) in your own words. Describe how different phases of SDLC may be applied in your project.
Task 5. Prepare project work breakdown and schedule for your project. (See Appendix B)
Task 6. Build and present Gantt chart to your trainer.
(Note- You can simply use online Smartsheet with the following link to create Gantt chart easily. https://www.smartsheet.com/gantt-chart-software
OR
You can use Excel, MS Project or any other open source software available on your Desktop.)}}

_{^{Task 7. Identify and select the team members (including the manager).
Create a table that shows the team members, their roles and responsibilities. (See appendix C)
Task 8. Determine and discuss training and support needs of your client.
Task 9. When the deliverables change during the project the current budget and deadlines are adjusted to reflect the additional work.
Amazing restaurant decided to put their system on Cloud with full backup services.
Identify the cost and additional requirements to accommodate the above changes.
Monitor and control project scope changes, risks and issues.
Task 10. What could be the possible risk? Create Risk Management plan for your project. (See appendix D)
Task 11. Close the project by proper document signing with appropriate person}}

Answer 1

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 modelwhere 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 fileSID_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 fileSID_businessfunctreq.docx

- As evidence you have successfully created the database, please do the following:

o export the database tables and submit the sql file

o 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 input data), display their first and last names and their hired dates. Order the results based on the hired date in descending order.

Multiple Table Queries

4. For all bookings with more than one booking, display the customer's number, last and first names and the number of bookings made by each customer.

5. For all dishes priced $30 or less, display the dish name, quantity ordered, dish price and the total price for each order. Ordered price is the total quantity ordered by multiply by the unit dish price.

Hint: Total Price per Dish = Dish Price * Quantity ordered

6. Display the list of Ingredients (name of the ingredients) used in preparation for a specific dish. Your result will vary based on the data in your database table.

7. For each table, display the table id and the average number of times the table has been booked.

8. For each customer with an id between a certain range (specify this range based on the data in your database table), display the customer's name and the number of booking with that customer.

9. For each employee who has booked customers who live in certain cities (please specify at least two cities), display the employee id, first name and last name and the average booking taken by the employee.

What to Submit:

Run your SQL query solution/s from the previous question (1 to 9 above) and provide a screenshot of your query plus output. Provide both the SQL statement and a screenshot (you can use Snipping Tool to grab it) of the results of running your SQL statements.

Save all screenshots in a word file called SID_Businessfunctreq.docx.

Task 3: Design access and security system

The database should only be accessed by authorised staff, who will be provided with a username and password, which serves as unique ID. Therefore, you will need to review the business security plan and design the password and access system for the database.

Note: Depending on the role, each user type will have different types of access to the database.

What to do

In this task, you will outline the security requirements for the database.

System Security Strategy

Provide the details of strategy to align the business requirements in regard to authorisation.

System Authorisation and Authentication

Provide detailed information and describe the procedures necessary to access the system. If applicable, include how to get a user ID and log on to the system, as well as the actions a user must take to change and/or reset a password.

User Access Considerations

Describe the different users and/or user groups and the restrictions placed on system accessibility or use for each. Please outline what kind of privileges each user will have.

Database protection against SQL injections

Plan a secure input and output handling to prevent code injections vulnerabilities that allow hackers to get to the data in the database.

Database backup and restore procedures

Outline the policy for database backup and restore procedures

What to submit

Record the information in the word document file called SID_businessfunctreq.docx containing the detailed information outlined above.

Task 4: Establish database connection and implement security for the system:

Develop a Java program that communicates with the database and manipulate its data.

What to do

1. Create the front-end interface using Java Standard Widget Toolkit (SWT)

Use WindowBuilder to design the SWT application. You have to develop the GUIs for the following:

- User Login – must request username and password

- Dashboard (users should only view the options according to their access level) – the dashboard should display the buttons for Booking, Table Assignment, Table Order, Total Bill and Create Staff (admin users only)

- Make Booking – performs a table booking at the restaurant

- Booking list – searches for bookings on a particular date, allows to edit the booking (amend dates and delete bookings)

- Table assignment – assigns a table to the booking

- Table order – GUI only

- Total bill – GUI only

- Create staff – only admin users will be able to access and create a new staff user.

- Edit Staff - only admin users will be able to access and edit a staff user

2. Connect the Java application to the database using JDBC

Ensure that your application code applies practices to prevent vulnerabilities related to code injections.

3. Develop encryption, using Java programming to secure data communication between application front-end (client) and backend server (database).

Hint:

- import java cryptographic library

Useful links:

https://docs.oracle.com/javase/tutorial/security/userperm/index.html

https://docs.oracle.com/cd/E12839_01/core.1111/e10043/introjps.htm#JISEC1816

https://tersesystems.com/blog/2015/12/22/an-easy-way-to-secure-java-applications/

Encryption is the most effective way to achieve data security, and sensitive information should always be protected.

Due to data sensitivity, your program should include data encryption and decryption for the password.

Implement the following: When the user signs up, they enter a password, which needs to be encrypted before being stored in the database. Upon login, the password entered needs to be validated and matched with the value stored in the database to grant system access.

4. Test the application

The application needs to be tested for the functionalities below. You have to test and take a screenshot of the outcome to ensure that the code is working as expected.

- User Login

§ Correct username and password entered

§ Incorrect username and password entered

- Dashboard

§ Admin user view

§ Non-admin user view

- Make Booking

§ Enter 2 bookings into the database using the GUI

- Booking list

§ Search for a booking

- Table assignment

§ Assign a table to a booking

- Create staff

§ New staff user is created

- Edit staff

§ Edit staff details

What to submit

- After developing and debugging your program, submit your program as a JAR file with the source code.

- Test cases and screenshots work saved in SID_businessfunctreq.docx

Task 5: Confirm the database design with client:

You will need to obtain final approval from Amazing Restaurant Management (your trainer will play the role) for your physical data model.

What to do

Complete the below Physical Data Model sign-off form and submit it in your assessment document for signing and final approval (your trainer won’t actually sign this form but will return it to you with feedback to help improve if necessary).

If the feedback requires that you need to make changes then you take on-board their recommendations, make changes to resolve the issues, and resubmit your work again for final approval.

PROJECT NAME: AMAZING RESTAURANT

Physical Data Model sign-off

YES/NO

Amazing Restaurant physical database meets functional requirements and accommodates business rules by being able to generate the required reports.

Feedback:

System security protects Amazing data

Feedback:

Data Modelling by: …………………………………………………………………………… Date: ……………………

Amazing Management (trainer): …………………………………………….. Signature: ………………………………

What to submit

Save the form in the word file called SID_businessfunctreq.docx.

Summary of deliverables

Please replace SID for your student number when naming the files.

Here is a summary of files that you have to submit:

¨ Word file SID_businessfunctreq.docx

¨Database tables sql file

¨ Java application JAR file