Select Page
  

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”
guidlines.docx

example.docx

Don't use plagiarized sources. Get Your Custom Essay on
Database for Restaurant Management System
Just from $10/Page
Order Essay

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

Order your essay today and save 10% with the discount code ESSAYHSELP