DATABASE

Table of Contents
6. Database ..................................................................................................................... 2

6.1 Introduction ....................................................................................................................... 2

6.1.1 Table ...........................................................................................................................................3
6.1.2 Keys & Relations .........................................................................................................................3
6.1.3 Primary Key.................................................................................................................................3
6.1.4 Foreign Key .................................................................................................................................4
6.1.5 Record or Row ............................................................................................................................4
6.1.6 Column .......................................................................................................................................4
6.1.7 Field ............................................................................................................................................5

6.2 RDBMS ............................................................................................................................... 5

6.2.1 Relations .....................................................................................................................................7

6.3 SQL ................................................................................................................................... 12

6.3.1 SQL Data Types ........................................................................................................................13
6.3.2 SQL Statements........................................................................................................................16
6.3.3 SQL Clauses .............................................................................................................................23
6.3.4 SQL Operators ..........................................................................................................................26
6.3.5 SQL Constraints .......................................................................................................................32
6.3.6 SQL Join ...................................................................................................................................37
6.3.7 Indexing ....................................................................................................................................38

6.4 Normalization .................................................................................................................. 41

6.5 Project Section ................................................................................................................ 46

References ............................................................................................................................ 47

1
 6. Database
6.1 Introduction
A database serves as a valuable tool for gathering and structuring information
efficiently. It has the capacity to store a wide range of data, such as details about
individuals, products, orders, and more. Many databases originate as lists in programs
like word processors or spreadsheets. However, as these lists expand in size, issues
such as redundancies and inconsistencies tend to emerge within the data. In this list
format, the information becomes increasingly challenging to comprehend, and the
options for searching and extracting specific data subsets become quite limited. It is
advisable to migrate the data into a specialized database managed by a Database
Management System (DBMS).
Example Raw Collection of Data
employeeName employeePhoneNo employeeEmail departmentRoomNo departmentName
Adam 097343732444 adam@[Link] DPTRN-001 IT
John 098478288233 john@[Link] DPTRN-001 IT
Alice 094774738222 alice@[Link] DPTRN-002 Accounting
Wayne 098843833838 wayne@[Link] DPTRN-003 Admin
Sam 097437347484 sam@[Link] DPTRN-002 Accounting
Snow 098489487482 snow@[Link] DPTRN-003 Admin

Example Seperating to Tables

Departments Table
departmentId departmentRoomNo departmentName
1 DPTRN-001 IT
2 DPTRN-002 Accounting
3 DPTRN-003 Admin

Employees Table
employeeId employeeName employeePhoneNo employeeEmail departmentId
1 Adam 097343732444 adam@[Link] 1
2 John 098478288233 john@[Link] 1
3 Alice 094774738222 alice@[Link] 2
4 Wayne 098843833838 wayne@[Link] 3
5 Sam 097437347484 sam@[Link] 2
6 Snow 098489487482 snow@[Link] 3

2
6.1.1 Table

In the raw collection of data table, we have combined multiple data into a single
table. Each row represents an employee details (name, phone, email), and department
details (roomNo, name). This table can be simpler to understand, they may result in
data duplication and can make it challenging to maintain data integrity and consistency
as the database grows. After breaking down to tables, data is divided into separate
tables to reduce redundancy and maintain data integrity through keys.

6.1.2 Keys & Relations

From the above example, Keys are the ID columns of the separate tables. These
are the relations of each row between two tables.
• Employee "Adam" has "departmentId" 1, which corresponds to the "IT"
department in the "Department" table.
• Employee "John" also has "departmentId" 1, indicating that both "Adam" and
"John" belong to the "IT" department.
• Employee "Alice" has "departmentId" 2, which corresponds to the "Accounting"
department.

6.1.3 Primary Key

A primary key is a field or a set of fields in a database table that uniquely identifies
each record (row) in that table. It ensures that there are no duplicate or null values
within the primary key column(s). The primary key serves as a means to enforce data
integrity and maintain the uniqueness of records. It is used to quickly access, update,
and reference specific records in the table. Only one primary key can exist for each
table.
In the above example, “departmentId” field from “departments” table can serve
as the primary key, ensuring that each “department” has a unique identifier.

3
6.1.4 Foreign Key

A foreign key is a field in a database table that establishes a link or relationship

between the data in two tables. It creates referential integrity by enforcing relationships
between tables, ensuring that data in the related table is consistent. The foreign key in
one table typically matches the primary key in another table. It allows you to retrieve
data from related tables, facilitating queries and ensuring data consistency.
In a "employees" table, the "departmentId" field can be a foreign key that
references the "departmentId" primary key in the "departments" table, linking each
order to a specific employee.

6.1.5 Record or Row

A record, also known as a row, represents a single, complete set of data within a
database table. It is a horizontal data structure and is composed of a collection of
values or fields that correspond to the columns defined in the table's schema.
Each record typically represents a distinct entity or piece of information, such as
an individual employee, a customer, a product, or any other unit of data that the table is
designed to store.

6.1.6 Column

A column refers to a vertical data structure within a database table. It represents a

specific attribute or piece of information associated with each record in the table.
Columns are organized in a tabular format, with each column having a unique name
that identifies the type of data it contains.
For example, in a table representing employees, you might have columns such as
"employeeID," "employeeName," "departmentName" and so on.

4
6.1.7 Field

A field typically refers to the same concept as a column but is often used more
broadly in the context of data. It can be a specific piece of data within a record,
regardless of whether it is in a database table or not.
Fields are the individual data points within a record. For example, in a record
representing an employee, the fields might include the employee's first name, last
name, and department.
So, we are working with tables, keys, and relations and to manage those relational
data for application, we call Relational Database Management System (RDBMS).

6.2 RDBMS
RDBMS stands for Relational Database Management System. Software used to
store, manage, query, and retrieve data stored in relational databases is called a
relational database management system (RDBMS). An RDBMS provides an interface
between users, applications, and databases, as well as administrative functions for
managing data storage, access, and performance. Most relational database management
systems use the SQL language to access databases.
RDBMS commonly used today
• MySQL
• Oracle database
• Microsoft SQL Server
• SQLite, etc.

MySQL
MySQL is one of the popular free and open-source SQL databases. It is typically
used for web application development and is often accessed using PHP as the database
backend. Additionally, widely used software applications such as WordPress, Joomla,
and Magento use MySQL as their default database management system. The main
advantages of MySQL are that it's easy to use, cheap, reliable, and has a lot of developers
to answer your questions.

5
Oracle Database
Oracle Database, commonly referred to as Oracle DB, is a powerful, widely used
developed by Oracle Corporation and the code is not open sourced. It is known for its
scalability, high performance, and comprehensive set of features. Oracle DB is often used
in enterprise-level applications, data warehousing, and mission-critical systems. Most of
the world’s top banks run Oracle applications because Oracle offers a powerful
combination of technology and comprehensive, pre-integrated business applications,
including essential functionality built specifically for banks.

Microsoft SQL Server

SQL Server is developed by Microsoft. Like Oracle DB, the code not open source.
It is offered in various editions that can be categorized three primary categories:
mainstream, specialized, and discontinued editions. SQL Server's range of features and
editions allows businesses to choose the version that best meets their needs, from small-
scale applications to enterprise-level systems.

SQLite
SQLite is a popular open-source SQL database and self-contained serverless,
lightweight RDBMS. Unlike traditional client/server databases, SQLite operates as an
embedded library within your application, allowing you to store and manage data in a
single file without a separate database server. SQLite can run on a variety of operating
systems including Windows, Linux, macOs, mobile platforms such as Android and iOS,
PDAs, MP3 players, set-top boxes and other electronic gadgets.

6
6.2.1 Relations

The relations are used to describe the relationships that exist between the tables
in a relational database. These relations are established to maintain data integrity and
create meaningful links between different pieces of data.

Types of relationships in a database

There are 3 main types of relationship in a database:
• one-to-one
• one-to-many
• many-to-many

One To One Relationship

Let’s see some real-life examples of one-to-one relationships:

• Country - capital city: Each country has exactly one capital city. Each capital city is
the capital of exactly one country.

• Person - their fingerprints. Each person has a unique set of fingerprints. Each set
of fingerprints identifies exactly one person.

• Email - user account. For many websites, one email address is associated with
exactly one user account and each user account is identified by its email address.

• User profile - user settings. One user has one set of user settings. One set of user
settings is associated with exactly one user.

7
One-to-One Relationship in an ER Diagram:

Additional Foreign Key with Unique Constraint

Each country has a unique capital, and each capital is associated with only one
country. This type of relationship can be represented using two separate tables: one for
countries and another for capitals. Here's how you might model this relationship:

Country Table : Capital Table :

In this example:
The "Country" table contains information about different countries, where each row
represents a specific country. The “Capital” table contains information about various
capitals, with each row representing a unique capital. The CountryId column acts as a
foreign key that references the “Country” table, establishing a one-to-one relationship.
Each country is associated with only one capital, and each capital is associated with
only one country.

8
One To Many Relationship
Here are some real-life examples of one-to-many relationships:
• Departments and Employees: Departments within an organization can contain
multiple employees, but each employee is associated with only one department.
• Countries and cities: A country can have multiple cities, but each city belongs to a
specific country.
• Parents and Children: A parent can have multiple children, but each child has only
one set of parents.

• Universities and Students: A university can have many students, but each student
can attend only one university.

• Customers and Orders: A customer can place multiple orders, but each order is
placed by her one customer.

• Projects and tasks: Projects are made up of multiple tasks, each task being part of
a specific project.

The relationship between classes and students is typically modeled as a one-to-

many relationship within a relational database. Each class can contain multiple
students, but each student can only be associated with one class. To represent this
relationship in your database schema, you would typically use two separate tables,
one for classes and one for students. Here's how to model this relationship:

Classes Table : Students Table :

| ClassID | ClassName | | StudentID | StudentName | ClassID |

|-----------|------------------| |--------------|-------------------|-----------|
|1 | Math 101 | |1 | John |1 |
|2 | English 201 | |2 | Jane |2 |
|3 | History 301 | |3 | Mark |1 |
|4 | Lisa |3 |

9
In this example:
The Classes table contains information about different classes, with each row
representing a unique class. The Students table contains information about different
students, with each row representing a unique student. The "ClassID" column acts as a
foreign key referencing the "Classes" table, establishing a one-to-many relationship.
Each student is associated with a class, and each class can have multiple students.

Many To Many Relationship

Here are some real-life examples of many-to-many relationships:

• Students and Courses: Many students can enroll in multiple courses, and each
course can have multiple students.

• Authors and Books: Many authors can contribute to multiple books, and each
book can contain contributions from multiple authors.

• Actors and Movies: Many actors can appear in multiple movies, and each
movie can have multiple actors.

• Ingredients and Recipes: Many ingredients can be used in multiple recipes, and
each recipe may require multiple ingredients.
• Teachers and Subjects: Many teachers can teach multiple subjects, and each
subject can be taught by multiple teachers.
• Patients and Doctors: Many patients can have appointments with multiple
doctors, and each doctor can have multiple patients.
• Employees and Projects: Many employees can work on multiple projects, and
each project may involve multiple employees.

The "book to author" relationship is a classic example of a many-to-many

relationship. In this scenario, multiple authors can contribute to multiple books, and each
book can contain contributions from multiple authors. To properly model this relationship
in a relational database, you must use a junction table, often referred to as the
"Authorship" or "BookAuthor" table. Here's how to model this relationship:

10
Books Table : Authors Table: BookAuthor Table:

| BookID | Title | | AuthorID | AuthorName | | BookID | AuthorID |

|--------|---------------------| |----------|-------------------| |--------|----------|
|1 | Introduction to SQL| |1 | John Smith | |1 |1 |
|2 | Database Design | |2 | Jane Doe | |1 |2 |
|3 | Data Modeling | |3 | David Johnson | |2 |2 |
|3 |3 |

In this example :
The "Books" table contains information about different books, with each row
representing a unique book. The "Authors" table contains information about different
authors, with each row representing a unique author. The "BookAuthor" (junction) table
establishes the many-to-many relationship between books and authors. Each row in
this table represents a combination of a book and an author, indicating that the author
contributed to the book. Both "BookID" and "AuthorID" columns serve as foreign keys
that reference the "Book" and "Author" tables, respectively.

11
6.3 SQL
Known as Structured Query Language, SQL is used to manage, modify, and
access data in relational databases. SQL is the fundamental and integral language used
in relational database systems. Widely adopted by popular database systems such as
MySQL, MS Access, Oracle, Sybase, Informix, PostgresSQL and SQL Server.

• Access data: SQL lets users retrieve information stored in databases.

• Describe data: Users can use SQL to explain the structure and contents of the
data.
• Define and manipulate data: SQL enables users to create, modify, and manage
data in databases.
• Embed in other languages: SQL can be integrated with other programming
languages using modules and libraries.
• Create and drop databases and tables: SQL allows users to make new
databases and tables or remove them when needed.
• Create views, stored procedures, and functions: Users can create custom views
and functions to organize data and tasks efficiently.
• Set permissions on tables, procedures, and views: SQL permits users to control
who can access and modify specific data and functions.
When you are executing an SQL command for any RDBMS, the system determines
the best way to carry out your request and SQL engine figures out how to interpret the
task.

12
6.3.1 SQL Data Types

Exact Numeric Data Types:

Data Type From To

Bigint -9,223,372,036,854,775,808 9,223,372,036,854,775,807

Int -2,147,483,648 2,147,483,647

Smallint -32,768 32,767

Tinyint 0 255

Bit 0 1

Decimal -10^38 +1 10^38 -1

Numeric -10^38 +1 10^38 -1

Money -922,337,203,685,477.5808 +922,337,203,685,477.5807

Smallmoney -214,748.3648 +214,748.3647

Approximate Numeric Data Types:

Data Type From To

Float -1.79E + 308 1.79E + 308

Real -3.40E + 38 3.40E + 38

Date and Time Data Types:

Data Type From To

Datetime Feb 2, 1846 Dec 31, 9999

Smalldatetime Jan 1, 1900 Jun 6, 2079

Date Stores a date like June 30, 1991

Time Stores a time of day like 12:30 P.M.

13
Note: Here, datetime has 3.33 milliseconds accuracy where as small datetime has 1
minute accuracy.

Character Strings Data Types:

Data Type Description

Maximum length of 8,000 characters. (Fixed length non-

Char
Unicode characters)

Maximum of 8,000 characters. (Variable-length non-Unicode

Varchar
data).

Maximum length of 231characters, Variable-length non-

varchar(max)
Unicode data (SQL Server 2005 only).

Variable-length non-Unicode data with a maximum length of

Text
2,147,483,647 characters.

Unicode Character Strings Data Types:

Data Type Description

Nchar Maximum length of 4,000 characters. (Fixed length Unicode)

Nvarchar Maximum length of 4,000 characters. (Variable length Unicode)

Maximum length of 231characters (SQL Server 2005 only). (Variable length

nvarchar(max)
Unicode)

Ntext Maximum length of 1,073,741,823 characters. (Variable length Unicode)

14
Binary Data Types:

Data Type Description

Binary Maximum length of 8,000 bytes (Fixed-length binary data )

Varbinary Maximum length of 8,000 bytes. (Variable length binary data)

Maximum length of 231 bytes (SQL Server 2005 only). (Variable length
varbinary(max)
Binary data)

Image Maximum length of 2,147,483,647 bytes. (Variable length Binary Data)

Misc Data Types:

Data Type Description

Stores values of various SQL Server-supported data types, except text,

sql_variant
ntext, and timestamp.

Stores a database-wide unique number that gets updated every time a row
timestamp
gets updated

uniqueidentifier Stores a globally unique identifier (GUID)

Stores XML data. You can store xml instances in a column or a variable
xml
(SQL Server 2005 only).

cursor Reference to a cursor object

table Stores a result set for later processing

15
6.3.2 SQL Statements

Standard SQL statements (Commands) for interacting with relational databases

are CREATE, SELECT, INSERT UPDATE, DELETE, etc.. These orders can be
classified into the following groups depending on their nature:
• DDL: Data Definition Language
• DML: Data Manipulation Language
• DCL: Data Control Language
• DQL: Data Query Language

Demo Database
Below is a selection from the "customers" table in the sample database:

cus_i company contact_nam address city postal_cod country

d _name e e
1 Company Jhon Doe 123 Main Anytown 12345 USA
A St
2 Company Jane Smith 456Elm St Otherville 67890 Canada
B
3 Company Alice Johnson 789 Oak Another 32413 UK
C St City
4 Company Bob Williams 987 Pine Somewher 43545 Australia
D St e
5 Kunden A Hans Muller Am Bach Berlin 10115 German
123 y
6 Customer Emily Brown 456 Elm Los Angles 90001 USA
E St
7 Kunden C Anna Schmidt Hauptstra Munich 80331 UK
e 456
8 Customer Ava Nguyen 567 Oak Another 64353 UK
F St City
9 Customer Rose 890 Oak Another 32433 UK
G st City

16
DDL: Data Definition Language
This includes changes to the structure of the table like creation of table, altering
table, deleting a table etc.
Statement Description
CREATE To create new table or database
ALTER modifies an existing database object, such as a table.
TRUNCATE Delete data from table
DROP To drop a table
RENAME To rename a table

CREATE Statement
In SQL, the "CREATE" statement is used to create various database objects,
including tables, database, indexes, views, and more.
Here, I'll provide an example of a "CREATE DATABASE" statement, which is
used to create a new database.

CREATE DATABASE my_company;

Another example is “CREATE TABLE” statement, which is used to create a new

table.

CREATE TABLE customers (

cus_id INT PRIMARY KEY,
company_name VARCHAR(100),
contact_name VARCHAR(50),
address VARCHAR(255),
city VARCHAR(50),
postal_code VARCHAR(20),
country VARCHAR(50)
);

17
CREATE TABLE employees(
id INT PRIMARY KEY,
name VARCHAR (20) NOT NULL,
salary DECIMAL (18, 2),
city CHAR (25),
designation VARCHAR(255),
join_date DATE,
age INT
);

CREATE TABLE products (

product_id INT PRIMARY KEY,
category_id INT,
name VARCHAR(255),
description TEXT,
price DECIMAL(10, 2),
tax DECIMAL(5, 2),
total_price DECIMAL(10, 2),
stock_quantity INT
);

CREATE TABLE categories (

category_id INT PRIMARY KEY,
name VARCHAR(255)
);

18
ALTER Statement
The ALTER statement is indeed used to modify existing database objects,
including tables. You can use it to add, modify, or delete columns, change data types,
and more. This statement is to add “email” column in “employees” table.

ALTER TABLE employees ADD COLUMN email VARCHAR(100);

TRUNCATE Statement
This statement is used to delete all the data from a table, but it does not remove
the table structure itself. It's a way to quickly remove all rows from a table while keeping
the table intact

TRUNCATE TABLE employees;

DROP Statement
The DROP statement is used to delete an entire database object, including
tables, views, or even the entire database. It removes both the data and the structure.

DROP TABLE employees;

RENAME Statement
MySQL does not have a dedicated RENAME statement for renaming tables.
Instead, you typically rename a table using the ALTER TABLE statement with the
RENAME TO clause.

ALTER TABLE old_table_name RENAME TO new_table_name;

19
DML: Data Manipulation Language
DML commands are used to manipulate the data stored in the table, not the table
itself. DML commands are not automated. Changes are not persistent in the database,
meaning they can be rolled back.

Statement Description
INSERT INTO To insert a new row
UPDATE To update existing row
DELETE To delete row

INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table. It is possible
to write the INSERT INTO statement in two ways.
First way is specifying both the column names and the values to be inserted.

INSERT INTO customers

(cus_id, company_name, contact_name, address, city, postal_code, country)
VALUES(1, 'ABC Inc.', 'John Doe', '123 Main St', 'Anytown', '12345', 'USA'),
(2, 'XYZ Corp', 'Jane Smith', '456 Oak St', 'Another City', '67890',
'Canada');

If you are adding values for all the columns of the table, you do not need to
specify the column names in the SQL query. However, make sure the order of the
values is in the same order as the columns in the table.

INSERT INTO customers

VALUES(3, 'ABC Inc.', 'John Doe', '123 Main St', 'Anytown', '12345', 'USA');

UPDATE Statement
The UPDATE statement is used to modify the existing records in a table. The
following SQL statement updates the customers with a new contact person and a new
city.

20
UPDATE customers
SET contact_name = 'ABC', company_name = 'ABC Company'
WHERE cus_id = 1;

DELETE Statement
The DELETE statement is used to delete existing records in a table.

DELETE FROM customers;

DCL: Data Control Language

Data control language are the commands to grant and take back authority from
any database user.
Statement Description
GRANT Grant permission of right
REVOKE Take back permission

GRANT Statement
The GRANT statement is used to give specific privileges or permissions to a user
or user account in MySQL. These privileges determine what actions the user can
perform within the database. Here's the basic syntax:

GRANT SELECT, INSERT, UPDATE, DELETE ON training_exe.ordersstock TO

'username'@'hostname' IDENTIFIED BY 'password';

REVOKE Statement
The REVOKE statement is used to remove previously granted privileges from a
user or user account. It essentially takes away specific permissions. The syntax is
straightforward:

21
REVOKE SELECT, INSERT, UPDATE, DELETE ON database_name.table_name FROM
'username'@'hostname';

22
DQL: Data Query Language
Command Description
SELECT Retrieve record from one or more table

SELECT Statement
The SELECT statement is used to select data from a database. The returned data
is stored in a result table, called the result-set.

SELECT column1, column2, ... FROM table_name;

Here, column1, column2, ... are the field names of the table you want to select
data from. If you want to select all the fields available in the table, use the following syntax:

SELECT * FROM customers;

The following SQL statement selects the "cus_name" and "city" columns from the
"customers" table:

SELECT company_name,city FROM customers;

6.3.3 SQL Clauses

SQL clause helps to retrieve a set or collection of records from a table. It also helps
to set a condition on the columns or records of a table. Different clauses available in the
SQL are as follows:
• WHERE CLAUSE
• GROUP BY CLAUSE
• HAVING CLAUSE
• ORDER BY CLAUSE

23
Demo Data
Below is a selection from the "employees" table in the sample database:

id name salary city designation join_date age

1 John 50000 New York SE 2020-03-15 28

2 Emily 60000 Los Angeles Marketing 2019-08-22 20

3 Williams 70000 Chicago Sales Manager 2021-01-10 25

4 Jessica 45000 Houston Admin 2018-11-05 26

5 Robert 80000 San Senior SE 2017-06-20 28

Francisco

6 Olivia 55000 Data Analyst Project 2022-02-18 25

Manager

7 Sophia 48000 Boston HR 2023-04-30 24

WHERE CLAUSE
The WHERE clause in SQL is used with the SELECT query, one of the data
manipulation language commands. WHERE clauses can be used to limit the number of
rows displayed in the result set. This generally helps in filtering the records. It returns only
those queries which fulfill the specific conditions of the WHERE clause. WHERE clause
is used in SELECT, UPDATE, DELETE statement, etc.

24
WHERE Clause with SELECT Query
WHERE clause in a SELECT query to retrieve all the column values for every
record from a table. This is an example syntax:

SELECT * FROM employees WHERE salary > 50000;

The above query will display all those records of an employee where an
employee's salary is greater than 50000. Below 50000 salary will not be displayed as
per the conditions.

GROUP BY CLAUSE
The GROUP BY clause is used to arrange similar records into groups in the
Structured Query [Link] is used with SELECT Statement and placed after the
WHERE clause. GROUP BY clause is to group results based on one or more columns.
This is an example query to display all the records of the employees table but
group the results based on the age column.

SELECT * FROM employees GROUP BY age;

HAVING CLAUSE
When we need to put some condition on the column of the table, we use the
WHERE clause in SQL. But at that time, if you want to apply a condition on a column in
the GROUP BY clause. We will use GROUP BY clause and HAVING clause for column
conditions.
This is an example query to display the name of employees, salary, and city
where the employee's maximum salary is greater than 40000 and group the results by
designation.

SELECT name, city, max(salary) AS salary FROM employees GROUP BY

designation HAVING MAX(salary) > 50000;

25
ORDER BY CLAUSE
The ORDER BY clause in SQL is used to sort data based on a specific column of
a table. It arranges the data in either ascending (ASC) or descending (DESC) order,
making it easier to understand and analyze. If you don't specify the sorting order, the
default is ascending. It's like arranging things alphabetically or numerically so you can
find them more easily.
Syntax of ORDER BY clause to sort in descending order:

SELECT name, salary FROM employees ORDER BY salary DESC;

6.3.4 SQL Operators

MySQL operators are symbols or keywords used in SQL clauses such as

WHERE ,ON etc.. to perform searching data, filtering data, comparing values,
calculating, combining conditions. The operators in SQL can be categorized as:
• Arithmetic Operators
• Comparison Operators
• Logical Operators

Demo Data
pro categ name description price tax total_price stock_qua
duc ory_id ntity
t_id

1 3 Produ Slim-fit blue jeans 49.9 0.4 49.99 100

ct A with a classic look. 9

2 2 Kitche Powerful blender for 79.9 2 79.99 200

n smoothies and 9
Blend soups.
er

26
 3 1 Hiking Durable and 65 1.5 65 20
Boots waterproof hiking
boots.

4 2 Micro Compact microwave 90 2 90 16

wave with various cooking
Oven options.

5 4 Produ Fitness tracker with 129. 129.99 50

ct B heart rate monitor. 99

Demo Data
Below is a selection from the "categories" table in the sample database:

category_id name

1 Clothing

2 Electronics

Arithmetic Operators
Arithmetic operators perform simple arithmetic operations such as addition,
subtraction, multiplication, etc.

Operator Description
+ Addition

- Subtraction

* Multiplication

/ Dividing

% Modulo (Remainder)

27
Example:

// Addition
SELECT price + tax AS total_price FROM products;

// Subtraction
SELECT price - tax AS total_price FROM products;

// Multiplication
SELECT price * (1 + tax/100) AS total_price FROM products;

// Dividing
SELECT price / (1 + tax/100) AS total_price FROM products;

// Modulo (Remainder)
SELECT price % 10 AS remainder FROM products;

Comparison Operators
In SQL, when compare two values using comparison operators, they return a
Boolean result, where 1 typically represents "true" and 0 represents "false." These
operators are used to evaluate conditions in SQL statements.

Operator Description
= Equal to

< Less than

> Greater than

<= Less than or equal to

>= Greater than or equal to

<> , != Not equal to

28
Example :

//Equal to
SELECT * FROM products WHERE price = 100.00;

// Less than
SELECT * FROM products WHERE price < 50.00;

// Greater than
SELECT * FROM products WHERE price > 50.00;

// Less than or equal to

SELECT * FROM products WHERE price <= 100.00;

// Greater than or equal to

SELECT * FROM products WHERE price >= 50.00;

Logical Operators
In SQL, logical operators are used to combine or compare multiple conditions or
SQL commands, and they return Boolean results, where 1 represents "true" and 0
represents "false."

Operator Description

ALL TRUE if comparisons a value to all other values are TRUE.

AND TRUE if all the conditions separated by AND are TRUE.

OR TRUE if any of the conditions separated by OR is TRUE

BETWEEN
TRUE if the operand lies within the range of comparisons.

29
IN TRUE if the operand is equal to one of a list of expressions.

NOT
Reverses the value of any other Boolean operator.

ANY TRUE if any one of a set of comparisons are TRUE.

LIKE TRUE if the operand matches a pattern specially with wildcard.

Example:

//All
SELECT * FROM products WHERE price > ALL (SELECT name FROM products) ;

//AND
SELECT * FROM products WHERE category_id = 1 AND stock_quantity >= 50;

//OR
SELECT * FROM products WHERE category_id = 1 OR stock_quantity >= 50;

//BETWEEN
SELECT * FROM products WHERE price BETWEEN 50 AND 1000;

//IN
SELECT * FROM products WHERE category_id IN (1);

//NOT
SELECT * FROM products WHERE NOT price < 50;

//LIKE
SELECT * FROM products WHERE name LIKE 'Product%';

30
Wildcard Characters
A wildcard character is used to replace one or more characters in a string.
Wildcard characters are used with the LIKE operator. The LIKE operator is used in the
WHERE clause to find a specified pattern in a column.

Wildcard Characters in MySQL

Symbol Description Example

% Represents zero or more characters bl% finds bl, black, blue, and blob

- Represents a single character h_t finds hot, hat, and hit

It can also be used to combine symbols. Here are some examples showing the
different LIKE operators with the '%' and '_' symbols:

LIKE Operator Description

WHERE customer_name LIKE 'a%' Finds any values that starts with "a"

WHERE customer_name LIKE '%a' Finds any values that ends with "a"

WHERE customer_name LIKE '%or%' Finds any values that have "or" in any position

WHERE customer_name LIKE '_r%' Finds any values that have "r" in the second
position

WHERE customer_name LIKE 'a_%_%' Finds any values that starts with "a" and are at
least 3 characters in length

WHERE customer_name LIKE 'a%o' Finds any values that starts with "a" and
ends with "o"

SELECT * FROM customers WHERE city LIKE 'a%';

31
6.3.5 SQL Constraints

Constraints are predefined rules and conditions applied to the data stored in a
database table to maintain data integrity and ensure that the data meets specific
requirements. This means checking for some conditions and rules before inserting data
into database table. The following constraints are commonly used in SQL:
• NOT NULL: Column cannot be null
• UNIQUE: All values in a column are different.
• PRIMARY KEY: A combination of a NOT NULL and UNIQUE.
• FOREIGN KEY: Uniquely identified a rows/records in any another table
• CHECK: Validates condition for new value
• DEFAULT: Set default value if not passed
• CREATE INDEX: Used to create and retrieve data from the database very quickly

NOT NULL Constraint

By default, the columns can hold NULL values. The NOT NULL constraint in a
column means that the column cannot store NULL values. Requires that every row has
a value for the specified column.

CREATE TABLE customers_constraint(

id INT NOT NULL,
name VARCHAR (20) NOT NULL,
age INT NOT NULL,
address CHAR (25),
salary DECIMAL (18, 2)
);

To add a NOT NULL constraint to an existing column in the “customers” table in

MySQL, you would write a statement to the following:

ALTER TABLE customers_constraint MODIFY salary DECIMAL (18, 2) NOT NULL;

32
UNIQUE Constraint
The UNIQUE constraint prevents duplicate values. This constraint can be applied
to one or more than one column of a table, which means more than one unique constraint
can exist on a single table.

For example, the SQL command below creates a new table named "customers"
and adds necessary columns. Here, the "id" column is designated as "UNIQUE," ensuring
that you cannot insert two records with the same age.

CREATE TABLE customers_unique(

id INT NOT NULL UNIQUE,
name VARCHAR (20) NOT NULL,
age INT NOT NULL,
address CHAR (25),
salary DECIMAL (18, 2)
);

ALTER TABLE customers_unique MODIFY id INT NOT NULL UNIQUE;

PRIMARY KEY Constraint

PRIMARY KEY constraint is a combination of NOT NULL and UNIQUE
constraints. The column to which we have applied the PRIMARY KEY constraint will
always contain a unique value and will not allow NULL values.

CREATE TABLE customers_primary(

id INT PRIMARY KEY,
name VARCHAR (20) NOT NULL,
age INT NOT NULL,
address CHAR (25),
salary DECIMAL (18, 2)
);

33
 -- Drop the existing primary key
ALTER TABLE customers_primary DROP PRIMARY KEY;
-- Add a new primary key
ALTER TABLE customers_primary ADD PRIMARY KEY (ID);

NOTE: If you use the ALTER TABLE statement to add a PRIMARY KEY, the primary
key column(s) must already have been declared to not contain NULL values (when the
table was first created).

FORIEIGN KEY Constraint

FOREIGN KEY is a key used to link two tables together. This is sometimes called
a referencing key. FOREIGN KEY values must match a PRIMARY KEY in a different
table. Consider the structure of the two tables as follows:
customers Table:

CREATE TABLE customers_forieign(

id INT PRIMARY KEY,
name VARCHAR (20) NOT NULL,
age INT NOT NULL,
address CHAR (25),
salary DECIMAL (18, 2)
);

orders Table:

34
 CREATE TABLE orders(
id INT PRIMARY KEY,
date DATETIME,
amount DOUBLE,
cus_id INT,
FOREIGN KEY (cus_id) REFERENCES customers(cus_id)
);

If orders table has already been created, and the foreign key has not yet been
set, use the syntax for specifying a foreign key by altering a table.

ALTER TABLE ORDERS

ADD FOREIGN KEY (cus_id) REFERENCES customers (cus_id);

CHECK Constraint
A CHECK constraint in SQL is used to enforce a condition on the values that are
allowed to be inserted or updated in a column.
CHECK constraint predicates are written in the form of an expression that can
evaluate to either TRUE, FALSE and mathematical comparison operator
(LIKE, <, >, <=, OR, >=) to limit the range of data.
The following SQL creates a CHECK constraint on the "age" column when the
"customers" table is created. The CHECK constraint ensures that the age of a
customer must be 18, or older:

CREATE TABLE customers_check(

id INT PRIMARY KEY,
name VARCHAR (20) NOT NULL,
age INT NOT NULL CHECK (age >= 18),
address CHAR (25),
salary DECIMAL (18, 2)
);

35
 If customers table has already been created, then to add a CHECK constraint to
age column, you would write a statement like the following:

ALTER TABLE customers_check

MODIFY age INT NOT NULL CHECK (age >= 18 );

DROP a CHECK Constraint:

To drop a CHECK constraint, use the following SQL. This syntax does not work
with MySQL:

ALTER TABLE customers_check DROP COLUMN age;

DEFAULT Constraint
The DEFAULT constraint provides a default value to a column when the INSERT
INTO statement does not provide a specific value.
salary column is set to 5000.00 by default, so in case INSERT INTO statement does
not provide a value for this column. Then by default, this column would be set to
5000.00.

CREATE TABLE customers_default(

id INT PRIMARY KEY,
name VARCHAR (20) NOT NULL,
age INT NOT NULL CHECK (AGE >= 18),
address CHAR (25),
salary DECIMAL (18, 2) DEFAULT 5000.00
);

If the customers table has already been created, and you want to add a
DEFAULT constraint to the salary column, you would write a statement like the
following:

36
 ALTER TABLE customers_default MODIFY salary DECIMAL (18, 2) DEFAULT 500.00;

To drop a DEFAULT constraint, use the following SQL:

ALTER TABLE customers_default ALTER COLUMN salary DROP DEFAULT;

6.3.6 SQL Join

A JOIN clause is used to combine rows from two or more tables, based on a related
column between them. Different types of Joins are as follows:
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN

INNER JOIN
The INNER JOIN is a keyword that selects records that have matching values in
both tables.

SELECT [Link] AS product_name, [Link] AS category_name

FROM products INNER JOIN categories ON products.category_id =
categories.category_id;

LEFT JOIN
The LEFT JOIN keyword is used to return all records from the left table (table1),
and the matching records from the right table (table2).

SELECT [Link] AS product_name, [Link], [Link] AS

category_name FROM products LEFT JOIN categories ON products.category_id =
categories.category_id;

37
RIGHT JOIN
The RIGHT JOIN keyword is used to return all records from the right table
(table2), and the matching records from the left table (table1).

SELECT [Link] AS product_name, [Link], [Link]

AS category_name FROM products RIGHT JOIN categories ON
products.category_id = categories.category_id;

6.3.7 Indexing

Indexing is a schema object. Indexing in SQL is used to create and retrieve data
from the database very quickly than otherwise. The users cannot see the indexes, they
are just used to speed up searching, sorting, and filtering data. By using indexes, the
database engine can quickly locate the required rows without scanning the entire table.
In this article, you will learn how to create, alert, and remove an index in the SQL
database.

INDEX
You can create index on single or multiple columns. To create an INDEX on AGE
column, to optimize the search on customers for a particular age, following is the SQL
syntax:
For single columns:

CREATE INDEX idx_age ON employees (age);

For multiple columns:

CREATE INDEX index ON TABLE (column1 ,column2 , column3 , …);

38
UNIQUE INDEX

CREATE UNIQUE INDEX index ON TABLE(column);

To create an UNIQUE INDEX on age column, to optimize the search on

customers for a particular age, following is the SQL syntax:

CREATE UNIQUE INDEX idx_age ON employees(age);

ALTER INDEX
ALTER INDEX is a SQL statement used to modify an existing index in a database.

ALERT INDEX old_Index_Name RENAME TO new_Index_Name;

DROP INDEX

ALERT INDEX TABLE_Name DROP INDEX Index_Name;

When should indexes be created:

• A column contains a wide range of values.
• A column does not contain many null values.
• One or more columns are frequently used together in a where clause or a join
condition.

When should indexes be avoided:

• The table is small
• The columns are not often used as a condition in the query
• The column is updated frequently

39
40
6.4 Normalization
Database Normalization is a process that should be done for every database you
design. The process of obtaining a database design and establishing formal
specifications and rules is called Normal Forms. The database normalization process is
further categorized into the following types:
1. First Normal Form (1 NF)
2. Second Normal Form (2 NF)
3. Third Normal Form (3 NF)
4. Boyce Codd Normal Form or Fourth Normal Form (BCNF or 4 NF)
5. Fifth Normal Form (5 NF)
6. Sixth Normal Form (6 NF)

One of the driving forces behind database normalization is to streamline data by

reducing redundant data. Data redundancy means that multiple copies of the same data
are spread across multiple locations within the same database.
The drawbacks of data redundancy include:
• Data maintenance becomes tedious – data deletion and data updates become
problematic
• It creates data inconsistencies
• Insert, Update and Delete anomalies become frequent. An update anomaly, for
example, means that the versions of the same record, duplicated in different places in
the database, will all need to be updated to keep the record consistent
• Having unnecessary duplicate data increases the database's size and uses up a lot
of space on the disk.

41
 This article is an effort to provide basic details of database normalization. The concept
of normalization is a huge topic, and the scope of this article is to provide enough
information to understand the first three forms of database normalization.
1. First Normal Form (1 NF)
2. Second Normal Form (2 NF)
3. Third Normal Form (3 NF)
To demonstrate the process of normalization from an unnormalized table to
various normal forms, we provide a simplified example with tables.

Unnormalized Table (Original Data Table)

In the unnormalized table, you might have all the data in a single table without
considering the principles of normalization. Here's what the original unnormalized table
might look like:

Table: Unnormalized_Library
Library ID Library Name Location Book Title Author
1 Central Library New York Introduction to SQL John Smith
1 Central Library New York Database Design Alice Johnson
2 City Library Los Angeles Python Programming Bob Brown
2 City Library Los Angeles Web Development Jane Smith
3 University Library Chicago Data Structures Sam White
3 University Library Chicago Algorithms Carol Green

Step-by-Step Normalization
1NF (First Normal Form):
• each table contains atomic values
• each row can be uniquely identified using a primary key.

42
Table: 1NF_Library
Library ID Library Name Location
1 Central Library New York
2 City Library Los Angeles
3 University Library Chicago

Table: 1NF_Books
Library ID Book Title Author
1 Introduction to SQL John Smith
1 Database Design Alice Johnson
2 Python Programming Bob Brown
2 Web Development Jane Smith
3 Data Structures Sam White
3 Algorithms Carol Green

2NF (Second Normal Form):

The 2NF rule is:
• The table must be already in 1NF, and all non-key columns of the tables must
depend on the PRIMARY KEY
• The partial dependencies are removed and placed in a separate table
We address partial dependencies. We've introduced the 2NF_Books table with
Book ID as the primary key, ensuring that each book record is associated with a
specific library. The 2NF_LibraryBooks table represents the relationship between
libraries and books, resolving partial dependencies.

Table: 2NF_Library (Same as 1NF)

Library ID Library Name Location
1 Central Library New York
2 City Library Los Angeles
3 University Library Chicago

43
Table: 2NF_Books
Book ID Book Title Author
978-0-12 Introduction to SQL John Smith
978-0-76 Database Design Alice Johnson
978-1-11 Python Programming Bob Brown
978-1-23 Web Development Jane Smith
978-0-35 Data Structures Sam White
978-0-78 Algorithms Carol Green

Table: 2NF_LibraryBooks
Library ID Book ID
1 978-0-12
1 978-0-76
2 978-1-11
2 978-1-23
3 978-0-35
3 978-0-78

3NF (Third Normal Form):

The 3NF rule is
• The table must be already in 1NF, and non-primary key columns shouldn’t depend
on the other non-primary key columns
• There is no transitive functional dependency.

We remove transitive dependencies. Author details are separated into the

3NF_Authors table. The 3NF_LibraryBooks table only depends on its primary key,
Book ID, and the 3NF_BookAuthors table represents the relationship between books
and authors. This ensures that there are no transitive dependencies in the data.

44
Table: 3NF_Library (Same as 1NF, 2NF)
Library ID Library Name Location
1 Central Library New York
2 City Library Los Angeles
3 University Library Chicago

Table: 3NF_LibraryBooks (Same as 2NF)

Library ID Book ID
1 978-0-12
1 978-0-76
2 978-1-11
2 978-1-23
3 978-0-35
3 978-0-78

Table: 3NF_Books
Book ID Book Title
978-0-12 Introduction to SQL
978-0-76 Database Design
978-1-11 Python Programming
978-1-23 Web Development
978-0-35 Data Structures
978-0-78 Algorithms

Table: 3NF_Authors
Author ID Author Name
1 John Smith
2 Alice Johnson
3 Bob Brown
4 Jane Smith

45
 5 Sam White
6 Carol Green

Table: 3NF_BooksAuthors
Book ID Author ID
978-0-12 1
978-0-76 2
978-1-11 3
978-1-23 1
978-0-35 5
978-0-78 2

6.5 Project Section

Let's consider scenario involving a company's data. Students will use an
unnormalized data table and normalize it up to 3NF.

Unnormalized Data Table

Employee Employee Department Department Project Project
Name ID Head Name Description
Alice 101 HR John Project A HR Software
Bob 102 IT Jane Project A IT Infrastructure
Carol 103 IT Jane Project B Website
Development
David 104 Sales Sam Project C Sales Expansion
Eve 105 HR John Project C Training
Program

46
References
[Link]
[Link]
[Link]

47