Database for Restaurant Management System

Anonymous
timer Asked: Mar 30th, 2019
account_balance_wallet $40

Question Description

This assignment will assess your ability to design and develop a small-scale database system. Your database should, if possible be a real one. Alternatively, you may create a simulated real world database based on information requirements in an organization with which you are familiar. The length of assignment should be 2500 words.

The overall task of this assessment, is to write a report that describes a complete database structure with all its related components based on a selected topic.

My topic is “Database for Restaurant Management System


The Report should include the following sections:

1 Initial Study (250 words)

2 Requirements analysis (500 words)

3 Design:

3.1 Modelling the requirement using the E/R conceptual model (250 words)

3.2 Data Dictionary (500 words)

3.3 Converting the conceptual model to logical schema (500 words)

3.4 Normalizing the logical schema (500 words)





*** Word count: 2500 words

*** In-Text Citation and References using Harvard style

*** Two attachments have been uploaded named: “Guidelines & Example”

Unformatted Attachment Preview

Each of these sections is described below in more details. 1. Initial Study The goal of this phase is essentially to gather the company details and its business processes. The company details should be gathered in terms of name, location, when was it started, what is their main business? The details of business and/or operational processes used by the organization should be provided. Details on what the company wishes to accomplish, what problems and limitations users have, what is the new database system have to do, and in what way must it be done should also be given. The initial study should also include scope of the database. 2. Requirements gathering & analysis In the requirements gathering & analysis phase, the requirements collected and analyzed. The collected requirements help to understand the system that does not yet exist. The purpose of the requirement analysis is to identify and describe the data that are used by the processes of the organization. In this section you should use and document different methods like interview, questionnaire, observation, reading company documents and reports. The purpose of these methods is to gather all the information needs of users involved in or affected by the business process. The documentation for the interview should include the details of the interviewee, the questions asked, and the response. The documentation for the questionnaire should include the analysis of questions which make use of some data. Documentation for the observation and reading documents should include data used by the processes. At the end required entities should be documented and relationship matrix should be given to depict the relationship between entities. 3. Design In this phase, the conceptual models and logical model has to be developed based on the requirement gathering & analysis. In conceptual schema design, the data requirements collected in requirement gathering & analysis phase are examined and a conceptual database schema is produced. The conceptual schema is then translated into the logical model and normalized. 3.1 Modeling the requirement using the E/R conceptual model A data modeling phase involves modeling the database structure itself by using a method such as entity relationship diagramming (ER diagram). This provides a means of visually representing various aspects of the database structure, such as the tables, table relationships and relationship characteristics. In this section you need to determine entities about which information is to be maintained, determine the properties or characteristics of those entities, determine the relationships between entities. Draw the entity relationship diagram using the Crow’s Foot notation. You should draw the E-R diagram in Visio 2013. You should include all attributes for each entity and the relationship with cardinality and all keys must be properly documented. Your E-R diagram should have minimum of five to six entities. 3.2 Data Dictionary In this section you need to produce the data dictionary for each Entity. The data dictionary should include name of the attribute, type, size, domain, description. The data dictionary should also include information on keys and constraints such as whether the specified attribute is a primary key, foreign key, unique, not null and whether there is any condition to check using the check constraint. The data dictionary section must be supported with proper explanation and justification. The explanation must include criteria of considering PK and other constraints. You May follow the template provided below for reference: The data dictionary table shown above must be placed as a screenshot image in the report to avoid the content high similarity. 3.3 Converting the conceptual model to logical schema In this section you should convert your E-R diagram to logical/relational schema. Use the proper rules to transform 1:1, 1: M/M: 1 and M: N to logical schema. You should underline the primary keys and put an asterisk for foreign keys. 3.4 Normalizing the logical schema The Normalization phase is the process of decomposing large tables into smaller tables in order to eliminate redundant data and avoid problems with inserting, modifying or deleting data. Table structures are tested against normal forms, which are a specific set of rules that can be used to test a table structure to be sure it is sound and free of problems. You need to check your produced Relational Schema above for 1NF, 2NF and 3NF.If it satisfies 1NF/2NF/3NF criteria then you ONLY need to include the statement “The Relational Schema satisfies 1NF/2NF/3NF criteria”. If your schema does not satisfy the criteria of 1NF/2NF/3NF then you need to reproduce your schema in 1NF/2NF/3NF.The conversion should be documented. Database Management Systems () Assessment 1 (Report) Database Ticket Booking Content Subject Pages Initial Study 1 Requirements gathering & analysis 2 Entities and Attributes 3 Relationship Matrix 4 Design, Modeling the requirement using the E/R conceptual model 5 E-R Diagram 6 Data Dictionary 7-12 Converting the conceptual model to logical schema 13-14 Normalizing the logical schema 15-17 Initial Study National Transport was the name of Muasalat between 1972 to 2015. Muasalat is the first public transportation company in the Sultanate of Oman. By Regal Declaration in July 1984, Muasalat was accorded the concession for provided that community transport services to encompassing of the Sultanate of Oman. In November 2015 Muasalat is taking abundant strides in the improvement of governmental transport in Oman commencing with the expansion of Muscat internal line and external line. Muasalat has a quantity of services, the most facility use is Bus public transport. A bus reservation system contracts with maintenance of accounts of details of each traveler. It additionally incorporates upkeep of data like calendar and points of interest of each transport. Muasalat has decided to use Database for Buses Ticket Booking. It observed the employed of the Bus reservation framework and after going over it, it becomes more acquainted with that there are numerous activities, which they need to do physically. It takes many time and producing numerous blunders while data access. Due to this, at times numerous issues happen and they were opposite numerous disputes with customers. To take care of the overhead issue, and further keeping records of traveler subtle elements, seat availability, fee per seat, a bill generation and different things, we are available this proposition of a electronic planning system. -1- Requirements gathering & analysis The attention of the project is to Bus public transport to oversee information, so that all the businesses become reckless and there should not be any blame in transaction like calculation blame, the bill generation and extra things. It exchanges all the printed material. It saves records of all bills also, giving to affirm 100% successful employment of the computerized Bus recording system. The central database for the bus ticket booking all the details of the travelers, divers and address. The hierarchical details of tickets, ticket types, trips and buses should be viable in record. It should be also record each seats and fare of the different type of buses. All the information of the arrangement from time to time need to revive in the database. A passenger can sign up any time and where through the internet and should fill in all details before to completed his register. After that the passenger can select easy to cheese a number of tickets and include any tickets can select many passengers in the sometime as well. through the process specifies age of passenger. After that also can select the type of tickets include one way, return and open return. able to the traveler see the journeys arranged through select the tickets contain are dates, station and time. There are two methods of payment. The drivers have access to know some details of them include are the type of buses and trip, when they use staff number and password through log-in also can register attends to work as well. As the database is facilitated on the internet, the application can get to information from any piece of the world, by a number of people concurrently. -2- Entities and Attributes login userid Username password passenger Passengerid Firstname Lastname sex Data_of_birth Phone Email Username password Bus trip Busid Driverid (FK) Type Capacity Model Tripid Ticket_type (FK) date Start station Start time End station End time seat Seateid Passengerid (FK) Type number Seat_no driver Deriverid Firstname Lastname Sex Data_of_birth Email Phone Username password address Addressid Passengerid (FK) City State Street House_no Ticket type Ticket_typeid Ticketid (FK) Journey data Location One way Return Open return ticket Ticketed Passengerid (FK) Adults_no Senior_no Children_no Infant_no Card payment fare Fareid Seatid (FK) Passengers no Total amount Cardid Paymentid (FK) Number Expire data Security number bill Billid Passengerid (FK) Data Time Total amount -3- payment Paymentid Passengerid (FK) Cash Card Driver regs Address select pay get drive of Ticket has Ticket type has Trip of Bus Give detail Seat has Fare Payment Card Payment Bill has of of -4- bill Card payment payment fare seat bus select trip has Ticket type driver ticket regs address Passenger passenger Login_ user Relationship Matrix Design At this phase we have finished the underlying examination and necessity requirement gathering. The necessities for the ticket booking Database of Muasalat company were completely analyzed. while the necessities were analyzed by us, because we have the entities and their relation. Now we build up an applied typical of the database. Modeling the requirement using the E/R conceptual model The ER diagram displays all the entities we have itemized their attributes and the relationships between the entities. -5- E-R Diagram -6- Data Dictionary Passenger Attributes Data Type Domain Size PK Passengerid Firstname Lastname Sex Data_of_birth Phone Email Number Text Text Text Date Number varchar 0-9 A-Z A-Z A-Z 0-9 0-9,+,- username password varchar varchar 0-9,A-Z 0-9,A-Z Attributes Data Type Domain Size PK Driverid Firstname Lastname Sex Data_of_birth Phone Email Number Text Text Text Date Number varchar 0-9 A-Z A-Z A-Z 0-9 0-9,+,- usename password varchar varchar 0-9, A-Z 0-9, A-Z 10 10 10 8 10 9 0-9,A-Z,_,- 20 Not Unique Foreign Check Null Key Yes Yes Yes Yes Yes ,@,. 10 10 Yes Yes Driver 0-9, A-Z, _, -, @,. 10 10 10 8 10 9 20 Not Unique Foreign Check Null Key Yes Yes Yes Yes 10 10 Yes Yes -7- Yes Address Attributes Data Type Domain Size PK Addressid Passengerid City State Street House_no Number Number Text Text Character Number 0-9 0-9 A-Z A-Z 0-9, A-Z 0-9 Yes Attributes Data Type Domain Size PK Ticketeid Passengerid Adults_no Senior_no Children_no Infant_no Number Number Number Number Number Number 0-9 0-9 1-9 0-9 0-9 1-9 Yes 10 10 15 15 15 10 Not Null Unique Foreign Check Key Yes YES Ticket 20 20 2 2 2 2 Not Null Unique Foreign Check Key Yes YES -8- Ticket type Attributes Data Type Domain Size PK Ticket_typeid Ticketid Journey data Location One way Return Open return Number Number Date Text Number Text Text 0-9 0-9 0-9 A-Z 0-9 0-9 0-9 Yes 20 20 10 10 2 2 2 Not Unique Foreign Check Null Key Yes Trip Attributes Data Type Domain Size PK Tripid Ticket type Date Number Number Date Text Time Text Time 0-9 0-9 0-9 A-Z 0-9, A-Z 0-9 Yes Start station Start time End station End time 20 20 10 10 5 10 5 Not Null Unique Foreign Check Key Yes -9- Bus Attributes Data Type Domain Size PK Busid Driverid Type Capacity Model Number Number Character Number Number 0-9 0-9 0-9, A-Z 0-9 0-9 YES 20 20 10 2 4 Not Unique Foreign Check Null Key YES Seat Attributes Data Type Number Seateid Passengerid Number Character Type number Seat_no Number Number Domain Size PK 0-9 0-9 0-9, A-Z 0-9 0-9 YES 10 10 10 2 2 Not Null Unique Foreign Check Key YES - 10 - Fare Attributes Data Type Domain Size PK Fareid Seatid Passengers no Number Number Number 0-9 0-9 0-9 20 20 2 YES 0-9,. 10 Total amount Decimal Not Null Unique Foreign Check Key YES Payment Attributes Data Type Paymentid Number Passengerid Number Decimal Cash Card Decimal Domain Size PK 0-9 0-9 0-9,. 0-9,. YES 20 20 10 10 Not Null Unique Foreign Check Key YES - 11 - Card payment Attributes Data Type Number Cardid Paymentid Number Number Number Expire data Security number Date Number Domain Size PK 0-9 0-9 0-9 0-9 0-9 20 20 16 10 3 Not Unique Foreign Check Null Key YES YES bill Attributes Data Type Number Billid Passengerid Number Date Data Time Total amount Time Decimal Domain Size PK 0-9 0-9 0-9 0-9 0-9,. YES 20 20 10 5 10 Not Null Unique Foreign Check Key YES - 12 - Converting the conceptual model to logical schema In this step the main entities we have account, an ER diagram and their attribute which shows the relations between the entities. From up to now created information we can now grow the logical schema of the Buses Ticket Booking Database. We will quantity any table/relation in the serial order. First, we change the entities to logical schema PASSANGER (passenger id , first name , last name , sex , day of birth , phone , email , username, password) DRIVER (driver id , first name , last name , sex , day of birth , email, phone , username , password ) BUS ( bus id , driver id , type , capacity , mode ) TICKET ( ticket id , passenger_id , senior no , adults no , infant no ) SEAT ( seat id , type , number , seat no , passenger_id ) PAYMENT ( payment id , cash , card , passenger_id ) BILL ( bill id , date , time , amount , total , passenger_id ) Next we change our relation between entities to the logical schema. A ticket is selected by a passenger, whereas a passenger can select a number of tickets This is M:1 relation. We have to contain the information of passenger in the ticket relationship as a foreign key. Then now the ticket relationship will be - 13 - TICKET (ticket id, senior no, adults no, infant no, passenger_id REFERENCES passenger). A seat has two kinds of them are (VIP) and (tourism) is selected by a passenger whereas a passenger can select a number of seats. This is M:1 relationship. We have to contain the information of a passenger in the seat relationship as a foreign key. Then the seat relation now will be, SEAT (seat_id, type, number, seat no, passenger_id REFERENCES passenger). Payment is paid by a passenger. however , a traveler can pay for man tickets . This is M:1 relation. We have to contain the information of passenger in the payment relationship as a foreign key. Then the bill relationship now will be, PAYMENT (payment id, cash, card, passenger_id REFERENCES passenger). Bill is got by a passenger. whereas a traveler can get many bills . This is M:1 relation. We have to contain the information of a passenger in the payment relationship as a foreign key. Then the bill relationship now will be, BILL (bill id, date, time, a mount, total, passenger_id REFERENCES passenger). After we look to the relation driver dive, a bus it is 1:1 and second hand, the bus is driven by one driver, it is some relation between each other 1:1. Then the bus relation now becomes, driver_bus (drvier_id, bus_id). - 14 - Normalizing the logical schema The logical schema of the every tables in our database was finalized . The Next stage in the Database design is to normalize the up to now established database. When we analysis the tables passenger and driver they couple have comparable attributes. Two attributes username and password has got its location in the couple tables. It is also analyzed that the password totally rely upon the username and it is autonomous with other keys in the relation We evacuate the attributes from the both tables and make a new table called login_user which will store the login credentials of the couple passenger and driver . The primary used in login_user will be additional to together the passenger and driver relation as FOREIGN KEY. The new relation is as follows. LOGIN_USER (userid, username, password) Now, passenger and driver tables can be changed as below. PASSANGER (passenger id, first name, last name, sex, day of birth, phone, email, userid REFERENCES login_user (userid)) DRIVER (driver id, first name, last name, sex, day of birth, email, phone, serid REFERENCES login_user (userid)). Looking at the table TICKET (ticketid, passengerid, senior no, adults no, infant no) We see that it is in 2NF, but the table has to be carried to 3NF. The attribute passenger_id which is also a primary key do not donate to the other attributes in the relation. This connection can be additionally part into two as given underneath. TICKET (ticketid, senior no, adults no, infant no) Passenger_Ticket(passengerid,ticketid). Similarly, SEAT (seat id, type, number, seat no, passenger_id) can be normalized by splitting the table as given below SEAT (seat id, type, number, seat no) - 15 - Passenger_Seat (passengerid, seatid). Similarly, PAYMENT (paymentid, cash, card, passengerid) can be normalized by splitting the table as given below PAYMENT (paymentid, cash, card) Passenger_Payment(passengerid,paymentid). Similarly, BILL (billid, date, time, amount, total, passengerid) can be normalized by splitting the table as given below BILL (billid, date, time, amount, total) Passenger_Bill (passengerid, billid). Looking at the table BUS (busid, driverid, type, capacity, mode) The attribute Driverid which is too a primary key do not give to the other attributes in the relation. This connection can be additionally part into two as given underneath BUS (busid, type, capacity, mode) Driver_Bus(driverid,busid) We can see that all additional tables are in 3NF and no more standardization want to be done. The last database is as given beneath. PASSANGER (passenger id, first name, last name, sex, day of birth, phone, email, userid REFERENCES login_user (userid)) DRIVER (driver id, first name, last name, sex, day of birth, email, phone, serid REFERENCES login_user (userid)). LOGIN_USER (userid, username, password) TICKET (ticketid, senior no, adults no, infant no) SEAT (seat id, type, number, seat no) - 16 - PAYMENT (paymentid, cash, card) BILL (billid, date, time, amount, total) BUS (busid, type, capacity, mode) Passenger_Ticket(passengerid, ticketid). Passenger_Seat (passengerid, seatid). Passenger_Payment(passengerid,paymentid). Passenger_Bill (passengerid, billid). Driver_Bus(driverid,busid). - 17 - ...
Purchase answer to see full attachment

Tutor Answer

Knutsen
School: UT Austin

Hello there, have a look at the complete answer. In case of any concern, feel free to alert me.Regards

Restaurant DBMS

KFC RESTAURANT DATABASE MANAGEMENT SYSTEM

Student’s Name

Course
Instructor’s Name
Name of the Institution
Date

1

Restaurant DBMS

2

Initial Study
Kentucky Fried Chicken was started back in the year 1930. The founder of the restaurant
was known as Coronel Harland. It is commonly known through its abbreviation, KFC. In 1991,
the restaurant adopted the use of its abbreviation, KFC, from the complete words, Kentucky Fried
Chicken. However, the restaurant began using its full name again in 2007. The name proceeded to
be used during the branding and other activities of the restaurant such as advertising. The restaurant
is the largest dealer of fast chicken food products globally. The main types of foods served by
Kentucky Fried Chicken restaurant are inclusive of salads, wraps, burgers, and pieces among
others. Although it majors on the fried chicken products, the restaurant also serves the roasted
chicken products, desserts, and sides. It also deals in the products that are not sourced from the
chicken as their raw material. Some of such products are sourced from the beef and pork meat.
Products obtained from such raw materials are inclusive of kebabs and burgers (Rahman 2019).
This work, therefore, is geared towards drawing a report of a database management system of the
Kentucky Fried Chicken restaurant.
The main items contained in the menu list of Kentucky Fried Chicken are chicken,
coleslaw, sides, desserts, burgers, and kebabs among others. The customers served by the
restaurant are diversified regarding their social classes and regions of residence. The restaurant is
situated in over eighty countries globally to enable it to reach as many customers as possible. The
products are also prepared to favor the preferences of the customers. Mainly, it involves fast food
types. The restaurant employs the use of a database management system to make control orders
from the customers, the purchases made, and the consumption of raw materials among others
(Rahman 2019).
Requirements analysis

Restaurant DBMS

3

This project is aimed at controlling the transactions that are carried out by the restaurant.
It is aimed at making the businesses of the restaurant safe by reducing the losses that may accrue
from poor management of data and misappropriation of information. It takes care of the
information circulation among the staff members and the between them and their customers. It is
also meant to put the billing system under sufficient control. The generation of the bills of the
restaurant may be lead to great errors if not put under control. Therefore, there is need to come to
employ a Bus system that that facilitates adequate preservation and transfer of data within the
restaurant (Dignös et al. 2016, pg.26). Such a Bus system should be made to have the maximum
access and control of every transaction done by the restaurant.
The database includes the details of the products that are provided by Kentucky Fried
Chicken, its staff members, address, type of services offered, types of customers, and the detail
that lead the customers in making their ch...

flag Report DMCA
Review

Anonymous
Excellent job

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors