ONLINE FOOD ORDERING DATABASE
Chen ER Diagram
Tables
Name: Restaurant
Primary Key (PK): r_Id
Foreign Key(FK): N/A
Attribute Name
Data Type
Data Type Size
Property
r_Id
Int
5
Primary Key
r_name
VarChar(N)
N
Not Null
R_address
Varchar(N)
N
Not Null
R_phone
Numeric
12
Not Null
Name: Employee
Primary Key(PK): emp_Id
Foreign Key(FK): N/A
Attribute Name
Data Type
Data Type Size
Property
emp_id
Int
5
Primary Key
emp_name
VarChar(N)
N
Not Null
emp_address
Varchar(N)
N
Not Null
emp_phone
Numeric
12
Not Null
emp_department
Varchar(N)
Optional
emp_email
Varchar(N)
Not Null
Name: Custome
Primary Key (PK) : C_Id
Foreign Key(FK) : N/A
Attribute Name
Data Type
Data Type Size
Property
c_id
Int
5
Primary Key
c_name
VarChar(N)
N
Not Null
c_address
Varchar(N)
N
Not Null
c_phone
Numeric
12
Not Null
c_email
Varchar(N)
50
Not Null
c_username
Varchar(N)
35
Not Null
c_password
Varchar(N)
10
Not Null
Name : Menus
Primary Key (PK) : food_Id
Foreign Key(FK): N/A
Attribute Name
Data Type
Data Type Size
Property
food_id
Int
5
Primary Key
food_name
VarChar(N)
N
Not Null
food_price
Varchar(N)
N
Not Null
food_cusine
Varchar(N)
N
Not Null
Name: Staff_Roaste
Foreign Key (FK): emp_id
Attribute Name
Data Type
Data Type Size
Property
Emp_id
Int
5
Foreign Key
Shift
VarChar(N)
N
Not Null
Available
Varchar(N)
N
Not Null
Name : Orde
Primary Key (PK) : Order_Id
Foreign Key(FK): C_Id
Attribute Name
Data Type
Data Type Size
Property
order_id
Int
5
Primary Key
c_id
Int
5
Foreign Key
food_id
Int
5
Not Null
order_quantity
Int
Not Null
order_items
Int
Not Null
order_price
Float
Not Null
order_discount
Float
Not Null
total_price
Float
Not Null
Name : Payment
Primary Key (PK) : P_id
Foreign Key(F): Order_id and c_id
Attribute Name
Data Type
Data Type Size
Property
p_id
Int
5
Primary Key
order_id
Int
5
Foreign Key
c_id
Int
5
Foreign key
total_payment
Float
Not Null
Name: Drive
Primary Key(PK): D_id
Foreign Key(FK): Order_id,c_id
Attribute Name
Data Type
Data Type Size
Property
d_id
Int
5
Primary Key
order_id
Int
5
Foreign Key
c_id
Int
5
Foreign key
d_name
Varchar(N)
25
Not Null
no of deliveries
Int
5
Not Null
Brief description of the above designed Chen ER diagram for Online Food Ordering System:
Online Food Ordering System consists of a Restaurant which has employees working with and aspiring customers (new customers) as well as the registered customers associated with it, ordering variety of foods served by the restaurant according to the Menus displayed. In order to serve the high volume of customers the restaurant has devised a shift wise roaster for its employees, so that each and every order could be completed in an efficient way.
Whenever a customer visits the online food ordering system and selects the desired restaurant available according to the choice, a menu of that very restaurant is displayed before the customer, which lists the variety of the foods available with that particular restaurant. The customer is then required to select the foods available along with the quantity of the food and places the order, which is recorded in the order table. The system then starts looking for the available staff member in the shift according to the roaster and assigns the order to the available staff member. The Customer is then required to complete payment process according the food items selected in order which is recorded in the payment and when the payment from customer is confirmed. The order is then moved to delivery system which registered divers associated with it and assigns the order for delivery to the available driver.
Concept of Entity Relationship Modeling:
Rapid increase in the requirements for transaction and information emphasized the need to create more complex database structures which in turn requires effective design tools for it. Complex structural design requires the conceptual simplicity for yielding the desired results. The concept of graphical design prevailed over the hierarchical and network design, as it allowed the developers to examine the structures graphically in the form of entities and their relationships and became the widely accepted standard for the data modeling.
Graphical representation of entity and their relationships was introduced by Peter Chen in 1976 [1], known as entity relationship(ER) model or ERM and according to Colonel & Mo
is, (2012) [4], the wide and quick acceptance of Chen ERM was because of its ability to complement the concepts from relational database models. ER models are normally represented by Entity relationship diagram (ERD), a graphical representation to model database components. Generally, each ER model has following components:
Entity: According to Beynon-Davies, Paul (2017) [2], an entity is anything about which data will be collected and stored in the database. It is represented by rectangle having entity name generally written in capital case and always a noun and in singular in its center. Every row in the relational table represents the entity occu
ence also known as instance of the entity. Collection of like entities form entity set. The characteristics of every entity are described by its traits known as attributes.
Relationship: Relationship is simply an association between the entities. According to Colonel & Mo
is, (2012) [4] entities can be associated with each other in three ways, such as one-to-many (1:M), many-to-many (M:M) and one-to-one (1:1). Every relationship is represented by a name associated with it, which can be an active or passive ve
.
There are various advantages for using Chen’s ER diagram model, the most important and notable advantages are the simplicity and the graphical representation of prior to any coding. The graphical representation defined by Peter Chen provides the blueprint of the database code and helps in identification of e
ors at the beginning. Entity relationship diagram also influences the relationship [5] between the tables by defining the cardinality on either sides of the relationships and emphasizes that how the two or more tables are connected to each other and hence visualizes the dependencies as well among the entities represented by the tables.
References:
1. Chen, P. P. S XXXXXXXXXXThe entity-relationship model—toward a unified view of data. ACM transactions on database systems (TODS), 1(1), 9-36.
2. Beynon-Davies, P. (2017). Database systems. Bloomsbury Publishing.
3. Abiteboul, S., Hull, R., & Vianu, V. (1995). Foundations of databases (Vol. 8). Reading: Addison-Wesley.
4. Coronel, C., Mo
is, S., Rob, P., Loney, K., & Gennick, J XXXXXXXXXXDatabase Systems: Design, Implementation, and Management, 2012.
5. Imam, A. A., Basri, S., Ahmad, R., Aziz, N., & Gonzålez-Aparicio, M. T. (2017, November). New cardinality notations and styles for modeling NoSQL document-store databases. In TENCON XXXXXXXXXXIEEE Region 10 Conference (pp XXXXXXXXXXIEEE.
Copyright 2019 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN XXXXXXXXXX
Database
Design Process
• Data analysis and requirements
• Entity Relationship modeling and normalization
• Data model verification
• Distributed database design*
• Determine end-user views, outputs and transaction requirements
• Define entities, attributes, domains and relationships
• Draw ER diagrams; normalize entity attributes
• Identify ER modules and validate insert, update, and delete rules
• Validate reports, queries, views, integrity, access, and security
• Define the fragmentation and allocation strategy
DBMS and Hardware Independent
DBMS Dependent
Hardware Dependent
• Determine DBMS and data model to use
• Define tables, columns, relationships, and constraints
• Normalized set of tables
• Ensure entity and referential integrity; define column constraints
• Ensure the model supports user requirements
• Define tables, indexes, and views’ physical organization
• Define users, security groups, roles, and access controls
• Define database and query execution parameters
• Map conceptual model to logical model components
• Validate logical model using normalization
• Validate logical modeling integrity constraints
• Validate logical model against user requirements
Conceptual
Design
DBMS
Selection Select the DBMS
Logical
Design
Section Stage Steps Activities
Physical
Design
9-5
9-4
9-6
9-7
* See Chapter 12, Distributed Database Management Systems
+ See Chapter 11, Database Performance Tuning and Query Optimization
• Define data storage organization
• Define integrity and security measures
• Determine performance measures+
Copyright 2019 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. Due to electronic rights, some third party content may be suppressed from the eBook and/or eChapter(s).
Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it.
BUSINESS RULES
Properly document and verify all business rules with the end users.
Ensure that all business rules are written precisely, clearly, and simply. The business rules
must help identify entities, attributes, relationships, and constraints.
Identify the source of all business rules, and ensure that each business rule is justified,
dated, and signed off by an approving authority.
DATA MODELING
Naming Conventions: All names should be limited in length (database-dependent size).
ENTITY NAMES:
Should be nouns that are familiar to business and should be short and meaningful
Should document a
eviations, synonyms, and aliases for each entity
Should be unique within the model
For composite entities, may include a combination of a
eviated names of the entities
linked through the composite entity
ATTRIBUTE NAMES:
Should be unique within the entity
Should use the entity a
eviation as a prefix
Should be descriptive of the characteristic
Should use suffixes such as _ID, _NUM, or _CODE for the PK attribute
Should not be a reserved word
Should not contain spaces or special characters such as @, !, or &
RELATIONSHIP NAMES:
Should be active or passive ve
s that clearly indicate the nature of the relationship
Entities:
Each entity should represent a single subject.
Each entity should represent a set of distinguishable entity instances.
All entities should be in 3NF or higher. Any entities below 3NF should be justified.
The granularity of the entity instance should be clearly defined.
The PK is clearly defined and supports the selected data granularity.
Attributes:
Should be simple and single-valued (atomic data)
Should document default values, constraints, synonyms, and aliases
Derived attributes should be clearly identified and include source(s)
Should not be redundant unless they are justified for transaction accuracy,
performance, or maintaining a history
Nonkey attributes must be fully dependent on the PK attribute
Relationships:
Should clearly identify relationship participants
Should clearly define participation, connectivity, and document cardinality
ER Model:
Should be validated against expected processes: inserts, updates, and deletes
Should evaluate where, when, and how to maintain a history
Should not contain redundant relationships except as required (see Attributes)
Should minimize data redundancy to ensure single-place updates
Should conform to the minimal data rule: “All that is needed is there and
all that is there is needed.”
Data Modeling