BASIC SQL
Tutor: Nguyễn Lê Chí Bảo
MODULE 2.
RELATIONAL
DATABASE
Module 2:RELATIONAL DATABASE
Relational Database
Primary key/Foreign key
Relations/References
ALTER TABLE
Relational Database
Module 2:RELATIONAL DATABASE
WHAT IS RELATIONAL DATABASE?
• A relational database is a type of database that stores and provides access to data points
that are related to one another.
• Relational database are based on the relational model, an intuitive, straightforward way of
representing data in tables.
Module 2:RELATIONAL DATABASE
WHAT IS RELATIONAL DATABASE?
• Schema = structural description of relations in database
• Instance = actual contents at given point in time
Primary key/Foreign key
Module 2:RELATIONAL DATABASE
PRIMARY KEY
• The PRIMARY KEY constraint uniquely identifies each record in a table.
• Primary keys must contain UNIQUE values and cannot contain NULL values.
• A table can have only ONE primary key; and in the table, this primary key can
consist of single or multiple columns (fields).
Primary key Primary key
OrderID Name Price Category Quantity OrderID ItemID Quantity Brand
53619 Mouse 100 ELHA 2 682777 1111 2 Apple
31797 Keyboard 200 ELHA 3 682777 2222 1 Samsung
55002 T-Shirt 500 Fashion 4 463381 3333 1 Xiaomi
Module 2:RELATIONAL DATABASE
FOREIGN KEY
• The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
• A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY
in another table.
• The table with the foreign key is called the child table, and the table with the primary key is called
the referenced or parent table.
Module 2:RELATIONAL DATABASE
FOREIGN KEY
Primary key Foreign key
CustomerID FirstName LastName OrderID Name Price Category Quantity CustomerID
1111 Steve Johnson 53619 Mouse 100 ELHA 2 1111
2222 Beatriz Willams 31797 Keyboard 299 ELHA 3 2222
3333 Gabriel Brown 55002 T-Shirt 500 Fashion 4 2222
4444 Eric Miller 15946 Short 50 Fashion 1 1111
32385 Shamo 50 FMCG 1 3333
Relations/ References
Module 2:RELATIONAL DATABASE
Table Relationship
Every database table relationship is, therefore, built on top of Foreign Key columns,
and there can be three table relationship types:
• One-to-Many is the most common relationship, and it associates a row from a parent table to
multiple rows in a child table.
• One-to-One requires the child table Primary Key to be associated via a Foreign Key with the
parent table Primary Key column.
• Many-to-Many requires a link table containing two Foreign Key columns that reference the two
different parent tables.
Module 2:RELATIONAL DATABASE
Table Relationship – One to Many
One-to-Many is the most common relationship, and it associates a row from a parent table to multiple
rows in a child table.
One Customer- to – Many Orders
Module 2:RELATIONAL DATABASE
Table Relationship – Many to Many
Many -to-Many requires a link table containing two Foreign Key columns that reference the two different
parent tables.
EventID Name … Date
1 Sale 9.9 09/09/2021
2 Sale 10.10 10/10/2021
3 Sale 11.11 11/11/2021
4 Sale 12.12 12/12/2021
CustomerID EventID
1111 1
1111 2
2222 2
3333 3
Module 2:RELATIONAL DATABASE
Table Relationship – One to One
One-to-One requires the child table Primary Key to be associated via a Foreign Key
with the parent table Primary Key column.
CustomerID FirstName LastName CustomerID Mobile Email
1111 Steve Johnson 1111 089885566 Johnson@[Link]
2222 Beatriz Willams 2222 021654659 Willams@[Link]
3333 Gabriel Brown 3333 056446368 Brown@[Link]
4444 Eric Miller 4444 082164825 Miller@[Link]
ALTER TABLE
Module 2:RELATIONAL DATABASE
ALTER TABLE
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
The ALTER TABLE statement also used to add and drop various constraints on an existing table
Module 2:RELATIONAL DATABASE
ALTER TABLE
ADD Column MODIFY Column
ALTER TABLE table_name ALTER TABLE table_name
ADD column_name datatype; ALTER COLUMN column_name datatype;
DROP Column
ALTER TABLE table_name
DROP COLUMN column_name;
Module 2:RELATIONAL DATABASE
Relational Database
Primary key/Foreign key
Relations/References
ALTER TABLE
Module 2:RELATIONAL DATABASE
PRACTICE
THANKS
CREDITS: This presentation template was created by
Slidesgo, including icons by Flaticon, and Name: Nguyễn Lê Chí Bảo
infographics & images by Freepik and illustrations Email: bao2101998@[Link]
by Storyset Phone: 091 544 2420
Linkedin: nguyenlechibao