DATA DESIGN

Lecturer: NGUYEN DAC QUYNH MI

WHAT IS DATABASE
▪ A database is an organized collection of data, stored and accessed
electronically.
▪ A database is a structured collection of data that allows for efficient
storage, retrieval, and management of information. It is typically
managed by a Database Management System (DBMS), which
provides tools for interacting with the data.

2
 WHY WE NEED DATABASES
▪ Organization: Databases help organize data in a structured way, making it easier to find
and manage.
▪ Efficiency: They enable quick retrieval and manipulation of data, which is essential for
applications that require real-time information.
▪ Data Integrity: Databases enforce rules to maintain the accuracy and consistency of
data, reducing errors and redundancy.
▪ Security: They provide mechanisms to control access to sensitive data, ensuring that
only authorized users can view or modify it.
▪ Scalability: Databases can handle large volumes of data and can be scaled as needed,
accommodating growth over time.
3
HOW DOES IT WORK
Let’s check Library Management System.
Requirements: Allow members to borrow books
Data Types:
▪ Book: BookID, Title, Author, ISBN, Publication Year
▪ Member: MemberID, MembershipID, FullName, Contact Information
▪ Loan: MemberID, BookID, Loan Date, Return Date
How it works:
▪ When a new book is added, it's stored in the "Books" table.
▪ When a member joins, their information is stored in the "Members" table.
▪ When a book is borrowed, a new entry is created in the "Loans" table, linking the
member to the book
4
5
6
 TYPES OF DATABASE
There are several different types of databases, including:

• Relational databases: These databases store data in the form of tables, with rows
representing records and columns representing fields. Relationships between data can be
established using keys.

• Object-oriented databases: These databases store data in the form of objects, which are
self-contained units of data and functionality. Object-oriented databases are designed to
support the storage and management of complex, interrelated data.

7
TYPES OF DATABASE

• NoSQL databases: These databases are designed to support the storage and
management of large amounts of unstructured data. They do not use the traditional table-
based structure of relational databases, and they often support horizontal scaling, which
allows them to handle very large amounts of data and high levels of concurrency.

8
RELATIONAL DATABASES
• Relational databases: These
databases store data in the form of
tables, with rows representing
records and columns representing
fields. Relationships between data
can be established using keys.

9
RELATIONAL DATABASES
• Primary Key: Unique, non-null
identifier for records in a table

• Secondary Key: An additional

identifier that can have duplicates and
may contain NULL values

• EMPLOYEE (EmployeeID, Email,

Name, Phone, Address)

• A foreign key is a crucial concept in

relational databases that establishes a
relationship between two tables

10
TYPES OF RELATIONSHIP
▪ One-to-one database
relationship

▪ In a one-to-one relationship, a
record in one table can
correspond to only one record in
another table (or in some cases,
no records).

11
TYPES OF RELATIONSHIP
▪ One-to-MANY database relationship

▪ One-to-many relationships are the

most common type of relationships
between tables in a database. In a
one-to-many (sometimes called many-
to-one) relationship, a record in one
table corresponds to zero, one, or
many records in another table.

12
TYPES OF RELATIONSHIP
▪ Many-to-MANY database
relationship

▪ A many-to-many relationship
indicates that multiple records
in a table are linked to multiple
records in another table

13
DATABASE MANAGEMENT SYSTEM
• A Database Management System (DBMS) is software that facilitates
the creation, manipulation, and maintenance of databases. It
provides an interface between users and the database, allowing
users to perform operations like querying, updating, and
administering data.

14
 SQL – STRUCTURED QUERY LANGUAGE
• SQL (Structured Query Language) is a standardized programming
language used for managing and manipulating relational databases.

• SQL is a powerful language for interacting with relational databases. Its

standardized syntax allows for portability across different database
systems, making it a vital tool for developers, data analysts, and
database administrators. Understanding SQL is essential for anyone
working with data in a relational database context.

15
 SQL – STRUCTURED QUERY LANGUAGE
• Key Features of SQL

• Data Querying: The SELECT statement allows users to retrieve specific data based on
certain criteria.

• Data Manipulation:

• INSERT, UPDATE, DELETE

• Data Definition:

• CREATE, ALTER, DROP

16
SQL – STRUCTURED QUERY LANGUAGE

17
 SQL – STRUCTURED QUERY LANGUAGE
• SELECT Name, Email FROM Customers WHERE CustomerID = 1;

• INSERT INTO Customers (Name, Email) VALUES ('Alice',

'alice@example.com’);

• UPDATE Customers SET Email = 'alice_new@example.com' WHERE

CustomerID = 1;

• DELETE FROM Customers WHERE CustomerID = 1;

18
WEB-BASED DESIGN

19
 WEB-BASED DESIGN
• To access data in a web-based system, the database must be
connected to the Internet or intranet.

• The database and the Internet speak two different languages, however.

• Databases are created and managed by using various languages and

commands that have nothing to do with HTML, which is the language of
the web.

• The objective is to connect the database to the web and enable data to
be viewed and updated.

20
WEB-BASED DESIGN
• To bridge the gap, it is necessary to use middleware, which is a software that
integrates different applications and allows them to exchange data.

• Middleware can interpret client requests in HTML form and translate the requests
into commands that the database can execute. When the database responds to
the commands, middleware translates the results into HTML pages that can be
displayed by the user’s browser, as shown in Figure 9-9.

• Note that the four steps in the process can take place using the Internet or a
company intranet as the communications channel

21
ERD – ENTITY RELATIONSHIP DIAGRAM
Key terms
▪ ENTITY: An entity is a person, a place, a thing, or an event for which data is
collected and maintained. For example, an online sales system may include entities
named CUSTOMER, ORDER, PRODUCT, and SUPPLIER.
▪ TABLE OR FILE: Data is organized into tables or files. A table, or file, contains a set
of related records that store data about a specific entity. Tables and files are shown
as two-dimensional structures that consist of vertical columns and horizontal rows
▪ FIELD: A field, also called an attribute, is a single characteristic or fact about
an entity. For example, a CUSTOMER entity might include the Customer ID,
First Name, Last Name, Address, City, State, Postal Code, and Email Address.
▪ RECORD: A record, also called a tuple (rhymes with couple), is a set of related fields
that describes one instance, or occurrence, of an entity, such as one customer, one
order, or one product.

23
Entity Relationship Diagram (ERD)

▪ An entity-relationship diagram
(ERD) is a model that shows the
logical relationships and interaction
among system entities. An ERD
provides an overall view of the
system and a blueprint for creating
the physical data structures.

24
Types of Relationships

▪ A one-to-one relationship,
abbreviated 1:1, exists when exactly
one of the second entity occurs for
each instance of the first entity

25
Types of Relationships

▪ A one-to-many relationship,
abbreviated 1:M, exists when one
occurrence of the 1st entity can
relate to many instances of the 2nd
entity, but each instance of the
second entity associate with only
one instance of the first entity.

26
Types of Relationships
▪ A many-to-many relationship,
abbreviated M:N, exists when one
instance of the
first entity can relate to many instances
of the second entity, and one instance
of the second entity can relate to many
instances of the first entity.

27
ASSOCIATIVE ENTITY
▪ An M:N relationship is different from 1:1
or 1:M relationships because the event
or transaction that links the two entities
is actually a third entity, called an
associative entity, that has its own
characteristics

28
EXAMPLES
REGISTRATION
ID StudentID CourseID RegistrationDate
STUDENT RE01 01 C01 7/11/2024
StudentID StudentName Phone RE02 01 C03 7/11/2024
01 Trần Thị Lam 0909788988 RE03 03 C01 7/11/2024
02 Nguyễn Nam 0978677899 RE04 02 C01 9/11/2024
03 Lê Na 0909876543 RE05 03 C02 11/11/2024

COURSE
CourseID CourseName SubjectID SessionID RoomID
C01 PTTKHT_C0 MIS01 Ses1 R01
1
C02 PTTKHT_C0 MIS01 Ses2 R01
2
C03 Maths_03 Maths01 Ses1 R02

29
EXAMPLES

30
DATA
MODELING
SYMBOL SETS

31
CARDINALITY
▪ Relationships have two indicators.
These are shown on both sides of the
line.

▪ The first one (often called multiplicity)

refers to the maximum number of times
that an instance of one entity can be
associated with instances in the related
entity. It can be one or many

32
CARDINALITY
▪ The first one (often
called multiplicity) refers to
the maximum number of times
that an instance of one entity can
be associated with instances in
the related entity. It can
be one or many

33
CARDINALITY
▪ The second describes
the minimum number of times
one instance can be related to
others. It can be zero or one, and
accordingly describes the
relationship
as optional or mandatory.

34
CARDINALITY

35
CARDINALITY

36
CARDINALITY

37
CARDINALITY

38
CARDINALITY

39
40
DATA NORMALIZATION (CHUẨN HÓA DỮ LIỆU)

41
DATA NORMALIZATION (CHUẨN HÓA DỮ LIỆU)

42
DATA NORMALIZATION (CHUẨN HÓA DỮ LIỆU)
1. Loại bỏ sự trùng lặp: Bằng cách loại bỏ dữ liệu trùng lặp, chuẩn hóa giúp giảm yêu cầu lưu
trữ, cải thiện hiệu suất truy xuất dữ liệu và giảm nguy cơ không nhất quán.

2. Đảm bảo tính nhất quán: Chuẩn hóa cải thiện tính toàn vẹn dữ liệu bằng cách loại bỏ các
tình huống không nhất quán có thể xảy ra khi dữ liệu được lưu trữ lặp đi lặp lại.

3. Đơn giản hóa việc bảo trì: Bằng cách chia dữ liệu thành các bảng nhỏ hơn, có cấu trúc tốt,
chuẩn hóa đơn giản hóa việc quản lý và bảo trì cơ sở dữ liệu.

4. Hỗ trợ khả năng mở rộng: Cơ sở dữ liệu chuẩn hóa dễ dàng thích ứng với các sửa đổi, mở
rộng và nâng cấp hệ thống trong tương lai.

43
DẠNG CHUẨN HÓA ĐẦU TIÊN (1NF)
Quá trình chuẩn hóa tuân theo một tập hướng dẫn được gọi là dạng chuẩn.
Ba dạng chuẩn đầu tiên đóng vai trò quan trọng trong việc đạt được cấu trúc
cơ sở dữ liệu tổ chức và hiệu quả.

Dạng chuẩn hóa đầu tiên yêu cầu mỗi cột trong bảng chỉ chứa các giá trị
nguyên tử (không thể phân tách), và không có nhóm dữ liệu lặp lại.

Nói cách khác, mỗi thuộc tính của bảng phải chứa một giá trị duy nhất.

44
DẠNG CHUẨN HÓA ĐẦU TIÊN (1NF)

45
DẠNG CHUẨN HÓA ĐẦU TIÊN (1NF)

46
DẠNG CHUẨN HÓA THỨ HAI (2NF)

Dạng chuẩn hóa thứ hai xây dựng trên cơ sở của 1NF bằng cách loại bỏ các
phụ thuộc một phần. Phụ thuộc một phần xảy ra khi một thuộc tính phụ
thuộc vào chỉ một phần của khóa chính.

Để đạt được 2NF, bảng nên được chia thành các bảng riêng biệt, trong đó
mỗi thuộc tính phụ thuộc hoàn toàn vào toàn bộ khóa chính.

47
DẠNG CHUẨN HÓA THỨ HAI (2NF)

48
DẠNG CHUẨN HÓA THỨ HAI (2NF)

49
DẠNG CHUẨN HÓA THỨ BA (3NF)

Dạng chuẩn hóa thứ ba tiếp tục cải thiện cấu trúc bảng bằng cách loại bỏ
các phụ thuộc chuyển tiếp. Phụ thuộc chuyển tiếp xảy ra khi một thuộc tính
phụ thuộc vào một thuộc tính khác không phải là khóa chính.

50
DẠNG CHUẨN HÓA THỨ BA (3NF)

51
DẠNG CHUẨN HÓA THỨ BA (3NF)

52
DẠNG CHUẨN HÓA THỨ BA (3NF)

53
WHY NEED TO DESIGN ERD
1. It helps in understanding the data relationships.

2. ER diagrams are like a blueprint for designing a database.

3. Helps in communicating about databases with database designers,

developers, users, etc.

4. They help in describing different relationships and operations within an

organization.

54
HOW TO DRAW AN ERD
Step 1: Identifying Entities

▪ Determine the main objects you want to represent in the database. Eg, "students",
"courses", or "products“

Step 2: Defining Attributes

▪ Identify the properties(attributes) of properties of each entity. These attributes provide

more details about an entity.

Step 3: Specifying Relationships

▪ Create relationships between entities to specify how entities interact with each other.
Relationships are verbs like "teaches", "studies", or "sells".
55
HOW TO DRAW AN ERD
Step 4: Drawing Entities

• Draw entities as rectangle and write the name

Step 5: Adding Attributes

▪ To add attributes of a entity write attributes inside the rectangle or connect them with
lines.

Step 6: Connecting Entities

▪ Draw lines between the related entities to represent their connection.

56
HOW TO DRAW AN ERD
Step 7: Specifying Cardinality

Indicate the minimum and maximum number of relationship instances associated with an
entity using notations like crow's foot.

Step 8: Organizing ER Diagram

Organize all entities and relationships in a clean way for better readability and
understanding

57
ERD FOR BANK
Step 1: Define Entities

58
ERD FOR BANK
Step 2:

59
ERD FOR BANK
Step 2:

60
ERD FOR BANK
Step 3: Specifying Cardinality for Bank

61
ERD FOR BANK
Identify Primary Keys

62
REFERENCES
▪ https://www.geeksforgeeks.org/system-design-for-library-management/

▪ https://www.simplilearn.com/tutorials/dbms-tutorial/what-is-a-
database#:~:text=Databases%20Used%20For%3F-
,Databases%20are%20used%20to%20store%20and%20manage%20large%20amounts%20of,%2C%20
scientific%2C%20and%20government%20organizations.

▪ https://www.w3schools.com/sql/default.asp

▪ https://www.visual-paradigm.com/guide/data-modeling/what-is-entity-relationship-diagram/

▪ https://thinhnotes.com/chuyen-nghe-ba/erd-la-gi/

▪ https://thinhnotes.com/chuyen-nghe-ba/15-phut-thuc-hanh-voi-so-do-erd/

▪ https://www.youtube.com/watch?v=OXpvOx5KjhQ

63
 THANK YOU

It’s time for your questions

64