Reference no: EM132509082

ICTDBS502 Design a database - Australia Institute of Business & Technology

Case Study: Amazing Restaurant

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.

Assessment 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:
Writeyourreport 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.

- Database requirement analysis information tofileSID_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:
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.

Assessment 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

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 requestusername 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).

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, thepassword 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

Assessment 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.

Attachment:- Design a database.rar