Management information systems group assignment entity relationship diagram

By defining the entities, their attributes, and showing the relationships between them, an ER diagram can illustrate the logical structure of databases.. The components and features of

UNIVERSITY OF ECONOMICS THE UNIVERSITY OF DA NANG

University of Economics

MANAGEMENT INFORMATION SYSTEMS

GROUP ASSIGNMENT ENTITY RELATIONSHIP DIAGRAM

Lecturer: Lé Dién Tuan

Hoang Trung Duc Nguyễn Thị Ngọc Tiền

Da Nang, 2024

Contents

I Introduction

II Theory of ERD

The components and features ofan ER điagraImI: o- 55 5< =5 5 se sss s5 Styles 0Ÿ CardiniaÏÏy o so << s0 cm nu ni 0m

ERD Notations:

Degree of relationship Set

How to draw an ER Diagram:

Ill Advantages of ERD

1 Introduction of data model for Food Ordering System

2 Analyze data from Excel:

I Introduction

(ER) Diagram or ERD 1s essentially a blueprint that shows visually the

structure of a database system 1n an entity-relationship, which is used in

database design The main components of it are entities, attributes, and the

relationship between these entities By defining the entities, their attributes, and

showing the relationships between them, an ER diagram can illustrate the

logical structure of databases

Using ERD makes it simpler for developers and stakeholders to grasp the

structure of the system; these diagrams offer a clear and succinct way to

communicate the database schema

II Theory of ERD

1 The components and features of an ER diagram:

ER Diagrams are composed of three main components: entities, relationships and

attributes They also illustrate cardinality, which defines relationships in terms of

numbers

Entities, which are represented by rectangles An entity is an object or concept about which you want to store information A definable thing—such as a

person, object, concept or event—that can have data stored about it

Examples: Entity such as a customer, student, car or product Typically shown

as a rectangle

There are 3 types of entity: strong entity, weak entity and associative entity + Strong entity: A strong entity can be defined by its own attributes and is independent from other entities They will also have a primary key,

distinguishing each occurrence of the entity

+ Weak entity: A weak entity relies on the presence of their associated strong entity to ensure their existence and integrity

+ Associative entity:

- All relationships for the associative entity should be many

- The associative entity could have meaning independent of the other

entities

- The associative entity preferably has a unique identifier, and should also have other attributes

Attributes: displayed in a circle or an oval, the attributes refer to the

characteristics of an entity They can be categorized as simple, composite,

derived, single-valued, multi-valued attributes, and an object can have one or

multiple attributes

+ Simple attribute: an attribute that cannot be further subdivided into

components is a simple attribute

Example: the 1d number of a student, the gender of a student

Composite attribute: An attribute that can be split into components is a composite attribute

Example: the name can also be split into first name middle name, and last name

Single-valued attribute: the attribute which takes up only a single value for each entity instance 1s a single-valued attribute

Example: the age of a student

Multi-valued attribute: the attribute which takes up more than a single value for each entity instance is a multi-valued attribute

Example: Mail of a student: Personal email and college email

Derived attribute: an attribute that can be derived from other attributes is derived attributes

Example: The age of a customer can be derived from the date of birth Complex attribute: attribute which can be formed by the nesting of

composite and multi-valued attributes These attributes are rarely used in DBMS (Data Base Management System) That’s why they are not so popular

Relationships: illustrate two or more entities interacting with each other They

are displayed as labels placed on the lines connecting objects (corresponds to

primary key-foreign key equivalencies in related tables) There also has weak relationships, or identifying relationships, are connections that exist between a

weak entity type and its owner

2 Styles of Cardinality:

Cardinality 1s the mathematical sense that just means the number of values in a set In the relationship to databases and ERD, cardinality specifies how many instances of an entity relate to one instance of another entity

- There are three main types of relationships in a database expressed using

cardinality notation in an ER diagram: one to one relationship (1:1), one to

many relationship(1:M) and many to many relationship (M:N or M:M)

¢ One-to-one relationship (1:1)

- In an ER diagram, the one-to-one (1:1) relationship means that one entity has only one event shared with another entity

- For example, let’s examine passports and persons A person can only have one passport, and each passport can only be assigned to a person The one-to-one relationship 1s shown with a short perpendicular line on the relationship line that connects two entities

¢ One-to-many relationship (1:N)

- With this one-to-many (1:N), one entity has an event that occurs one time,

while the other entity can have more than one repetition of the event

- Take for example: customers and accounts A customer can have many account and one account only belongs to a customer

e Many-to-many (M:N or M:M)

- Many-to-many relationships are when both entities have the same event or

relationship happen more than once

- For example, we have the entities of products and customers Many customers

buy many products, and many products are bought by customers The first

relationship 1s shown by M, and the second 1s with N

3 ERD models:

There are usually three models people refer to based on the level of detail you want

to show: conceptual ERD, logical ERD and physical ERD

« Conceptual ERD : This ER model establishes a broad view of what should be

included in the model set Conceptual data models:

+ Include important entities and the relationship between them

+ Do not specify attributes

+ Do not specify primary keys

Conceptual ERDs can be used as the foundation for logical data models They

may also be used to form commonality relationships between ER models as a

basis for data model integration

¢ Logical ERD: This model contains more detail than the conceptual ER model, without regard to how information will be physically implemented in the

database Logical data models:

Include all entities and relationships between them

Specify attributes for each entity

Specify primary keys for each entity

Specify foreign keys, which identify the relationship between different entities

+ Involve normalization

Normalization is the process of removing redundancy in a table so that the

table is easier to modify Normalization typically occurs by dividing an entity table into two or more tables and defining relationships between the tables Physical ERD: The physical data model represents the process of adding

information to the database This model shows all table structures, including column name, column data type, column constraints, primary key, foreign key, and relationships between tables Physical data models:

+ Specify all tables and columns

+ Include foreign keys to identify relationships between tables

+ May include denormalization, depending on user requirements

+ May be significantly different from the logical data model

+ Will differ depending on which DBMS (database management system)

is used

4 ERD Notations:

There are various types of notations: chen notation, crow’s foot notation, UML notaton, Barker’s notation and IDEF1X notation Among these, chen notation

and crow’s foot notation is the most popular

When selecting a notation for your ER model online, there is no definitive

answer The goal is to choose a notation that helps you accomplish your desired

outcome

5 Degree of relationship Set

Now, based on the number of linked entity types, we have 3 types of degrees of

relationships: unary, binary, ternary

Unary: 1n a relation only one entity set is participating then such type of

relationship 1s known as a unary relationship

Binary: binary relationship exists when two entity sets are participating This is the most used relationship and can be easily converted into a relational table Ternary: ternary relationship exists when there are three types of entity and we

call them a degree of relationship is 3

6 How to draw an ER Diagram:

Step 1: Determine the Entities in your ERD

Step 2: Add attributes to each Entity

Step 3: Define the Relationships between Entities

Step 4: Add Cardinality to every Relationship in your ER Diagram

Step 5: Finish and save your ERD

II Advantages of ERD

When it comes to business analysis, ERD is a critical part of designing your software systems or the way you implement your business intelligence

solutions

It’s mainly used by analysts when designing a database The database helps

them communicate the landscape of the business to different teams, and this overview will help you build the applications needed to support the business Visual Representation: ERD offer a visual depiction of the database design,

akin to a blueprint outlining entities (like tables) and their connections This graphical presentation aids stakeholders in grasping the structure and

relationships within the data, enhancing understanding

Effective Communication: They communicate key entities and their

relationships clearly, using symbols like rhombus for relationships, ovals for attributes, and boxes for entities

Simple to understand: ERD is easy to understand and simple to create In

effect, this design can be used to be shown to the representatives for both

approval and confirmation The representatives can also make their

contributions to the design, allowing the possibilities of rectifying and

enhancing the design

High Flexibility: The ERD is quite flexible to use as other relationships can be derived easily from the already existing ones The ERD thereby acts like the

blueprint for the database and it allows the creation of an accurate design that

reflects the needs of the project

Improved Data Quality: By visualizing the relationships between entities, an

ERD can help ensure that data is consistent and accurate For example, ifa

customer can have multiple accounts, an ERD can ensure that each account is

associated with the correct customer and that the data is updated when

necessary This can help prevent data errors and improve data quality overall Easier Database Maintenance: An ERD can also make it easier to maintain a

database over time By documenting the relationships between entities and

attributes, database administrators can more easily identify and fix issues with the database as they arise This can save time and reduce the risk of data loss or corruption

Improved Database Design: ERD can help designers create more efficient and effective database designs By visualizing the relationships between entities, designers can identify potential issues with the database design and make

adjustments before it is implemented This can help prevent costly errors and

unprove the overall performance of the database

Analytical support: The ERD model is an important tool in the process of establishing a useful database Thanks to that, businesses can use data for

analysis to serve the requirements of production and business activities

ERD in E-commerce: In the world of e-commerce, ERD has been used to

manage product information, orders, and customer data For example, ERD can

be used to represent the relationships between products, customers, orders, and payments By visualizing these relationships, e-commerce businesses can

improve their inventory management, order fulfillment, and customer

experience

1 Introduction of data model for Food Ordering System

A Food Ordering System is a digital platform to streamline the ordering

process for food businesses It enables restaurants and other establishments to increase efficiency and reduce time and effort associated with the ordering

process With its automated features like food item branding and digital

payments, the system is easy to use and helps streamline the process of

managing food orders

Food Ordering System is a must-have for any food business running operations efficiently

a ER Diagram for Food Ordering System

Has

Toial_cos (cums -

ii Oe

b Relational model for Food Ordering System

Sub_category

Price Cost

Invoice Customer

Customer_id Name Phone DoB

2 Analyze data from Excel:

a Count of total invoice per customer:

Row Labels

A 8 D F H J À je

1 [Row Labels —_|~ Jcount of invoice id PivotTable Fields

2 LEAn Nam 7 Adi

4 Đường 4 roca fields to add

5 Truong Lim 7

7 Grand Total ”

- TT Customer

10

1

12

lá

15

16 -

7

4

28

0

€ount of invoice Price Cost Profit oice_per yea + : Defer Layout Update

b Total invoice per month/ year:

- The year of 2024

active All

low Labels - Count of involce_id —

41 1 ‘Choose fields to add to report or

52 1

3 1 Search 2

4 1

5 2 Ũ EI

, ¡ CO Food name

07 1

18 3 O Pree

129 2 O Cost

13 10 2

14 11 2 C Sub category id

15 12 1 4M nT nwo

15 Grand Total 2 hnwolce

Drag fields between areas below:

Y Filters 1 Columns

Year x

= Rows © Values

Month + | | Countotiwoced CountoLinvoke Price Cost Profit | Invoice per year @) {«] p) C1 Deter Laycut Update

- The year of 2023

A 8 € D F F 6 H J ^Ì p

2 Actve = All

3 Row Labels ~ Count of Invoice_id — —

41 1 Choose fields to add to report g-

53 1

64 1 card p

9 12 1 O Food name

10 Grand Total 6

12 (1 Cost

B

CO Sub category id

5 4 invoice Bị

16

17

Brag fields between areas belowr

B T Filters W Columns

2

2 Year ’

2

z3

= Rows © Values

2 Month % | Count ot invoice id

2

30

Count_ctinvoice Price_Cost_Profit Invoice ir ® * ca _ Deter Layout Update

c Total price/cost/profit per customer:

10