Draw the Entity-Relationship model by showing all the necessary attributes, identifiers, and relationships.

Posted: March 9th, 2022

AIMS:
• Design and construct a database system according to the selected preferred topic for each team. The list of topics is mentioned below. Unfortunately, due to our present Coronavirus restriction and consequently no access to Oracle lab in the university, you may create an online Oracle account (https://livesql.oracle.com) to work with your project.
• Each topic is open for interpretation, and you may modify it and propose new features. Your team should select only one topic and be creative to construct a database with all the required tables and insert real or fake records. Your project should contain a minimum of three (3) tables with at least ten records per table.
• Construct your database with the appropriate relationships along with the required primary keys and foreign keys. For many-to-many relationships, ensure that an appropriate junction table will also be created.
Our topic selected is : Restaurant Management Database
The restaurant maintains the catalog for the list of food and beverage items that it provides. Apart from providing food facilities at their own premises, the restaurant takes orders online through their site. Orders on the phone are also entertained. To deliver the orders, we have delivery boys. Each delivery boy is assigned to a specific area code. The delivery boy cannot deliver outside the area which is not assigned to the delivery boy (for every delivery boy there can be a single area assigned to that delivery boy). The customer record is maintained so that premium customers can be awarded discounts.
REQUIREMENTS:
1. Draw the Entity-Relationship model by showing all the necessary attributes, identifiers, and relationships.
2. Create the tables of the database with all the required data types and relationships. Insert 10 records in each table. All the actual scripts to create tables and insert records will be submitted along with the screenshot of the actual tables created with the records in it.
3. Construct seven (7) queries that you can think of for generating reports from your database. The queries should involve a combination of SELECT from multiple tables, aggregate functions (GROUP BY, ORDER BY, SUM, COUNT), logical operators (AND, OR, NOT), and special operators (BETWEEN, LIKE, IN, IS NULL).
4. For the second part of the project, you need to do the following:
a. Create one (1) function or procedure that you find useful in your chosen database. It could be as simple as displaying further information, display books borrowed by a borrower, doctor and room of a patient, or display most commonly ordered menu in a hotel or restaurant. Take note that a function or procedure usually accepts a parameter to perform its intended task.
Example #1: displayBorrowedBooks(‘Peter Parker’);
o This function/procedure will display a list of books currently borrowed by Peter Parker.
Example #2: BestMenu();
o This function/procedure will display a list of the best or most commonly ordered menus of a hotel or restaurant.
Example #3: getPatientInfo(‘Abdullah Alshukeily’);
This function/procedure will display the room number, doctor’s name, and other related information of a patient in a hospital.
b. Create one (1) trigger that will disallow any INSERT or UPDATE into one of your tables (you may select whichever table in your database) if it happens from 7:00 pm to 12:00 am of any day. You also have to insert a record into an audit table (create this extra table) for such an attempt, such as the date of the attempt, whether it was an INSERT or UPDATE operation, and the table’s name.
Sample Audit Table
AttemptDate Operation TableAffected
23-OCT-21 Insert Patient
26-NOV-21 Update Patient
5. Your course project should include the source code of all the scripts and a screenshot of the script’s output. Ensure that all the seven queries, function/procedure, and trigger you have created in your project must include at least one or two sentences to describe its purpose.
2. By submitting this paper, you agree: (1) that you are submitting your paper to be used and stored as part of the SafeAssign™ services in accordance with the Blackboard Privacy Policy; (2) that your institution may use your paper in accordance with your institution’s policies; and (3) that your use of SafeAssign will be without recourse against Blackboard Inc. and its affiliates.
Comments from Customer
our topic is about: Restaurant Management Database
The restaurant maintains the catalog for the list of food and beverage items that it provides. Apart from providing food facilities at their own premises, the restaurant takes orders online through their site. Orders on the phone are also entertained. To deliver the orders, we have delivery boys. Each delivery boy is assigned to a specific area code. The delivery boy cannot deliver outside the area which is not assigned to the delivery boy (for every delivery boy there can be a single area assigned to that delivery boy). The customer record is maintained so that premium customers can be awarded discounts.
Use the website Oracle life sql to Create the tables of the database with all the required data types and relationships. Insert 10 records in each table. All the actual scripts to create tables and insert records will be submitted along with the screenshot of the actual tables created with the records in it.
I have put 3 pages in case
Draw the Entity-Relationship model by showing all the necessary attributes, identifiers, and relationships > for this part you can draw it online and put it in word doc there’s no specific tool for this