DATABASE DEVELOPMENT

Develop Database

School: GS RUGANDO TSS

Class: level 4 SWD and NIT

Module name: SWDDD 402 Database development

Competency: Develop a Database

Learning Hours: 120

TRAINER: NSENGIYUMVA Jean Marie Vianney

Elements of Competence and Performance Criteria

Elements of Performance criteria

competence
1.1. Fundamentals are properly described based on database standards
1. Analyse
1.2. Data dictionary is clearly described based on database model
Database
1.3. Database Task requirements are properly identified based on user
requirements
2.1. Conceptual Database schema is properly designed based on system
2. Design
requirements
Database
2.2. Logical Database schema is properly designed based on system
 requirements

2.3. Database optimization is effectively enforced based on database schema

2.4. Physical Database Schema is appropriately created based on the Physical

Data Model.

3.1. Data definition language commands are effectively applied based on

database schema

3.2. Data manipulation language commands are effectively applied based on

database schema

3.3. Data control language commands are effectively applied based on

3. Implement database schema
Database
3.4. Data Query Language commands are effectively applied based on
database schema

3.5. Transaction Control Language commands are effectively applied based

on database schema

4.1. Access control is properly enforced based on database security measures

4.2. Auditing and logging are clearly managed based on the security policies
4. Secure
Database 4.3. Data encryption is correctly implemented based on data security
measures

4.4. Backup and Recovery of data are regularly configured based on DBMS

Learning outcome ONE: Analyse Database

1.1. Fundamentals are properly described based on database standards

Description of database fundamental

 Definition of key terms

● Database: Database is a collection of related data(table). A database
can also contain views, indexes, sequences, data types, operators, and
functions.

● Data, in the context of databases, refers to all the single items that are
stored in a database, either individually or as a set. Data in a database
is primarily stored in database tables, which are organized into
columns that dictate the data types stored therein. It is

● Information: is the data that has been converted into more useful or
intelligent form. For example: Report card sheet. The information is
needed for the following reasons − To gain knowledge about the
surroundings. To keep the system up to date.

● Entities: is a thing, place, person or object that is independent of

another. In databases, you store information about things in the real
world, and these things are database entities.

● Attributes/Field: DBMS consists of an ER (Entity Relationship) model,

and we generally use it to describe data elements and study their
relationships within the specified system. These data elements are also
known as entities which are real-world objects with self-
existence. These entities have their own properties and characteristics
defined with the help of attributes. Basically, attributes provide
additional information and describe the characteristics &
properties of an entity in DBMS. An entity may contain any
number of attributes while one of the attributes is considered to be
a primary key attribute. An attribute can take its values from a set of
possible values for each entity instance in an ER model in DBMS.
 ●

Types of attributes

 Single valued attributes consist of a single value for each entity instance and can’t
store more than one value.
 Multi-valued attributes can take up and store more than one value at a time for an
entity instance from a set of possible values. They are represented by a co-centric
elliptical shape.
 Derived attributes are those attributes whose values can be derived from the values
of other attributes.
 Key attributes act as the primary key for an entity, and they can uniquely identify an
entity from an entity set.
 Complex attributes are formed by the combination of multi-valued and composite
attributes.
  Values of stored attributes remain constant and fixed for an entity instance.

● Records: is simply a set of data stored in a table, for example, a

customer record. A record in a database is an object that can contain
one or more values. Groups of records are then saved in a table; the
table defines the data that each record may contain.

● Table: Tables are database objects that contain all the data in a
database. In tables, data is logically organized in a row-and-column
format similar to a spreadsheet. Each row represents a unique record,
and each column represents a field in the record.

● Database schema: defines how data is organized within a relational

database; this is inclusive of logical constraints such as, table names,
fields, data types, and the relationships between these entities. is a
structure that represents the logical storage of the data in a
database. It represents the organization of data and provides
information about the relationships between the tables in a given
database.

Types of Database Schema

The database schema is divided into three types, which are:

1. Logical Schema
2. Physical Schema
3. View Schema
1. Physical Database Schema

A physical database schema specifies how the data is stored physically on a storage system or
disk storage in the form of Files and Indices. Designing a database at the physical level is called
a physical schema.

2. Logical Database Schema

The Logical database schema specifies all the logical constraints that need to be applied to
the stored data. It defines the views, integrity constraints, and table. Here the term integrity
constraints define the set of rules that are used by DBMS (Database Management System) to
maintain the quality for insertion & update the data. The logical schema represents how the
data is stored in the form of tables and how the attributes of a table are linked together.

The primary key is used to uniquely identify the entry in a document or record. The Ids of the
upper three circles are the primary keys.

The Foreign key is used as the primary key for other tables. The FK represent the foreign
key in the diagram. It relates one table to another table.

3. View Schema
The view level design of a database is known as view schema. This schema generally
describes the end-user interaction with the database systems.

● DBMS: Database Management Systems (DBMS) are software systems

used to store, retrieve, and run queries on data. A DBMS serves as an
interface between an end-user and a database, allowing users to create,
read, update, and delete data in the database.

● SQL: Structured query language (SQL) is a programming language for

storing and processing information in a relational database. A
relational database stores information in tabular form, with rows and
columns representing different data attributes and the various
relationships between the data values. You can use SQL statements to
store, update, remove, search, and retrieve information from the
database. You can also use SQL to maintain and optimize database
performance.

What are SQL commands?

Structured query language (SQL) commands are specific keywords or SQL statements that
developers use to manipulate the data stored in a relational database. You can categorize SQL
commands as follows.

Data definition language

Data definition language (DDL) refers to SQL commands that design the database structure.
Database engineers use DDL to create and modify database objects based on the business
requirements. For example, the database engineer uses the CREATE command to create
database objects such as tables, views, and indexes.

Data query language

Data query language (DQL) consists of instructions for retrieving data stored in relational
databases. Software applications use the SELECT command to filter and return specific
results from a SQL table.

Data manipulation language

Data manipulation language (DML) statements write new information or modify existing
records in a relational database. For example, an application uses the INSERT command to
store a new record in the database.

Data control language

Database administrators use data control language (DCL) to manage or authorize database
access for other users. For example, they can use the GRANT command to permit certain
applications to manipulate one or more tables.

Transaction control language

The relational engine uses transaction control language (TCL) to automatically make
database changes. For example, the database uses the ROLLBACK command to undo an
erroneous transaction.

COMMIT: This command is used to save all the transactions in the DB.

ROOL BACK: The “rollback” term refers to the method of undoing changes. Thus, this
command could only be used in order to reverse transactions that occurred since the last
ROLLBACK or COMMIT command. All the modifications must be cancelled in case any
SQL grouped statements produce a certain error.

SAVEPOINT: It is used to roll back a certain transaction to a certain point rather than the
entire transaction.

✔ Application of database
 ✔ Identification of database models

A database model shows the logical structure of a database, including the relationships and
constraints that determine how data can be stored and accessed.

There are more database models but, basically, we focus on five most used, those five are the
following:

 Hierarchical database model

The hierarchical model organizes data into a tree-like structure, where each record has
a single parent or root. Sibling records are sorted in a particular order. That order is
used as the physical order for storing the database. This model is good for describing
many real-world relationships.
  Relational model

 The most common model, the relational model sorts data into tables, also known as
relations, each of which consists of columns and rows. Each column lists an attribute
of the entity in question, such as price, zip code, or birth date. Together, the attributes
in a relation are called a domain. A particular attribute or combination of attributes is
chosen as a primary key that can be referred to in other tables, when it’s called a
foreign key.

 Each row, also called a tuple, includes data about a specific instance of the entity in
question, such as a particular employee.

 The model also accounts for the types of relationships between those tables, including
one-to-one, one-to-many, and many-to-many relationships. Here’s an example:
  Network model: are the types of Database models that are designed to represent
objects and their relationships flexibly. The network model extends the hierarchical
model by allowing many-to-many relationships between linked records, which
implies multiple parent records.




 Object-oriented database model: is the data model where data is stored in the form of
objects. This model is used to represent real-world entities. The data and data
relationship is stored together in a single entity known as an object in the Object
Oriented Model. The Object-Oriented Database Management System is built on top
of Object Oriented Model.
 Here Transport, Bus, Ship, and Plane are objects.
 Bus has Road Transport as the attribute.
 Ship has Water Transport as the attribute.
 Plane has Air Transport as the attribute.
 The Transport object` is the base object and the Bus, Ship, and Plane objects derive
from it.

Take a look at another example-

1.2. Data dictionary is clearly described based on database model

Definition of Data dictionary: A data dictionary is a collection of descriptions of the data

objects or items in a data model to which programmers and others can refer. Often, a data
dictionary is a centralized metadata repository.
Types of data dictionaries

There are two types of data dictionaries: active and passive.

Active data dictionaries are created within the databases they describe and automatically
reflect any updates or changes in their host databases. This avoids any discrepancies between
the data dictionaries and their database structures.

Passive data dictionaries are created separately from the databases they describe to act as a
repository for data information. Passive data dictionaries require additional work to stay in
sync with the databases they describe. As such, database managers must handle passive
directories with care to ensure there are no discrepancies.

Data dictionary components

The specific components of a data dictionary can vary, but they typically take the form of
various types of metadata. Examples of these components include the following:

 data object listings, such as names and definitions;

 data element properties, such as data types, unique identifiers, sizes and indexes;

 entity relationship diagrams;

 system-level diagrams;

 reference data;

 missing data and quality indicator codes; and

 business rules for validation of data quality and schema objects.

Pros and cons of data dictionaries

Data dictionaries can be valuable tools for the organization and management of large data
listings. These are some of the biggest benefits of using a data dictionary:

 provides organized, comprehensive lists of data;

 easily searchable;

 provides reporting and documentation for data across multiple programs;

  simplifies the structure for system data requirements;

 reduces data redundancy;

 maintains data integrity across multiple databases; and

 provides relationship information between different database tables.

However, data dictionaries can also prove difficult for some to manage. Here are some of the
downsides:

 lack of functional details regarding data;

 diagrams that are not always visually appealing; and

 can be difficult for nontechnical users to understand.

Brief

Example of Data dictionary

1.3. Database Task requirements are properly identified based on user requirements

Identification of database requirements

Database requirement for a system are the description of what the system should do, the
service or services that it provides and the constraints on its operation.
A condition or capability that must be met or possessed by a system or system component
to satisfy a contract, standard, specification, or other formally imposed document.

Types of Database requirements

Functional requirements define a function that a system or system element must be

qualified to perform and must be documented in different forms. The functional requirements
describe the behavior of the system as it correlates to the system's functionality.

Non-functional requirements

Non-functional requirements are not related to the software's functional aspect. They can be
the necessities that specify the criteria that can be used to decide the operation instead of
specific behaviors of the system. Basic non-functional requirements are - usability, reliability,
security, storage, cost, flexibility, configuration, performance, legal or regulatory requirements,
etc.

They are divided into two main categories:

Execution qualities like security and usability, which are observable at run time.

Evolution qualities like testability, maintainability, extensibility, and scalability thatembodied

in the static structure of the software system.

Now, let's see the comparison chart between the functional and non-functional requirements.
Functional Requirements Non-functional requirements

Functional requirements help to understand the They help to understand the system's
functions of the system. performance.

Functional requirements are mandatory. While non-functional requirements are not

mandatory.

They are easy to define. They are hard to define.

They describe what the product does. They describe the working of product.

It concentrates on the user's requirement. It concentrates on the expectation and

experience of the user.

It helps us to verify the software's functionality. It helps us to verify the software's

performance.

These requirements are specified by the user. These requirements are specified by the
software developers, architects, and
technical persons.

There is functional testing such as API testing, system, There is non-functional testing such as
integration, etc. usability, performance, stress, security, etc.

Examples of the functional requirements are - Examples of the non-functional

Authentication of a user on trying to log in to the system. requirements are -
The background color of the screens should
be light blue.

These requirements are important to system operation. These are not always the important
requirements, they may be desirable.

Completion of Functional requirements allows thesystem While system will not work only with non-
to perform, irrespective of meeting the non- functional functional requirements.
requirements.
 ✔ Methods to collect data

Data collection happens when you gather and analyze valuable information (e.g.,
names, email addresses, customer feedback, and website analytics) from a variety of
sources to build compelling marketing campaigns, learn more about your customers, or
create financial budgets.
1. Interviews are a method of data collection that involves two or more people
exchanging information through a series of questions and answers.
An interview is a research method that involves asking questions to collect data from
individuals who have knowledge, experience or opinions on a particular topic or
subject matter.

Types of Interviews

There are three main types of interviews to research customers:

 structured,
 unstructured
 semi-structured interviews.

1. A structured interview is one where the researcher asks the participants a list
of questions that have been prepared in advance.

2. Unstructured or non-directive interviews in research: The opposite of

structured interviews is unstructured interviews. Unstructured interviews do
not rely on prearranged questions but follow up based on interviewees'
responses. Unstructured interviews are sometimes called non-directive
interviews. "Non-directive" means not prepared/prearranged.

3. Semi-structured interviews in research

A mix of structured and unstructured interviews is called a semi-structured interview. Semi-

structured interviews include a list of predetermined questions, yet not set in order. The
interviewer can choose what to ask depending on the situation.

Interview research is especially useful when the following are true:

  You wish to gather very detailed information
 You anticipate wanting to ask respondents follow-up questions based on their
responses
 You plan to ask questions that require lengthy explanation
 You are studying a complex or potentially confusing topic to respondents
 You are studying processes, such as how people make decisions

2. A questionnaire is a research instrument that consists of a set of questions or other

types of prompts that aims to collect information from a respondent. A research
questionnaire is typically a mix of close-ended questions and open-ended questions.

Types of questions

1. Open-ended, long-form questions offer the respondent the ability to elaborate

on their thoughts.

2. Close ended questions are defined as question types that ask respondents to
choose from a distinct set of pre-defined responses, such as “yes/no” or among
set multiple choice questions.

3. Observation is way of gathering data by watching behavior, events, or noting physical

characteristics in their natural setting. Observations can be overt (everyone knows
they are being observed) or covert (no one knows they are being observed and the
observer is concealed).
4. Documentation is the process of recording any aspect of project design, sampling,
data collection, cleaning and analysis that may affect results.
[Link]
set-1/

Learning outcome TWO: Design Database

● Description of database schema

A database schema is a blueprint that outlines a relational database’s architecture, describing

how data is organized within it and how its different elements, like foreign and primary keys,
data types, and fields, relate to one another. Typically, database schemas are visually
represented using an entity-relationship diagram, which depicts how values are stored, their
relationship to one another, and the rules governing them. The process of designing a schema is
referred to as data modelling.

Database schema benefits

There are many benefits to using a database schema. Some of the most common include:
 Database security. A schema can outline access permissions to certain parts of a
database, allowing greater control over who sees what and why. Database
administrators can then use this information to grant permission to those who require it.
 Greater fidelity. A schema ensures that a database is properly maintained by those who
use it. Among other things, this can greatly limit the number of duplicates and
unnecessary information contained within a database.
 Improved communication. A schema allows stakeholders to communicate with one
another more effectively about how to use and maintain a database over time. This can
significantly cut down on confusion and miscommunication.

Types of database schema

There are several different types of schema used for databases. The three most common types
you’ll likely encounter in the field include:
 Conceptual schema. A conceptual database schema represents all the elements
contained in a database and illustrates their relationship to one another, but it doesn’t
contain any tables. As a result, it provides a big-picture view of the database without
offering real-world details.
 Logical database schema. Logical schemas flesh out conceptual schemas with more
concrete details about the objects that will be contained within them, such as names,
tables, views, and integrity constraints.
 Physical database schema. A physical schema is an actual design for a relational
database. It includes all the technical and contextual information needed for the schema
and is created with a specific physical data system in mind.
Data Abstraction is a process of hiding unwanted or irrelevant details from the end user. It
provides a different view and helps in achieving data independence which is used to enhance
the security of data.
Physical or Internal Level

It is the lowest level of abstraction for DBMS which defines how the data is actually stored, it
defines data-structures to store data and access methods used by the database.

Logical or Conceptual Level

Logical level is the intermediate level or next higher level. It describes what data is stored in
the database and what relationship exists among those data. It tries to describe the entire or
whole data because it describes what tables to be created and what are the links among those
tables that are created.

View or External Level

It is the highest level. In view level, there are different levels of views and every view only
defines a part of the entire data. It also simplifies interaction with the user and it provides many
views or multiple views of the same database.

View level can be used by all users (all levels' users). This level is the least complex and easy
to understand.

✔ Types of data independence

Data Independence
o Data independence can be explained using the three-schema architecture.
o Data independence refers characteristic of being able to modify the schema at one
level of the database system without altering the schema at the next higher level.

There are two types of data independence:

1. Logical Data Independence

o Logical data independence refers characteristic of being able to change the conceptual
schema without having to change the external schema.
o Logical data independence is used to separate the external level from the conceptual
view.

o If we do any changes in the conceptual view of the data, then the user view of the data
would not be affected.

o Logical data independence occurs at the user interface level.

2. Physical Data Independence

o Physical data independence can be defined as the capacity to change the internal
schema without having to change the conceptual schema.
o If we do any changes in the storage size of the database system server, then the
Conceptual structure of the database will not be affected.
o Physical data independence is used to separate conceptual levels from the internal
levels.
o Physical data independence occurs at the logical interface level.
 ● Design of conceptual database schema

A conceptual schema or conceptual data model is a high-level description of informational

needs underlying the design of a database. It typically includes only the main concepts and
the main relationships among them.

 Entity-relationship model is the high-level data model. It stands for the Entity-
relationship model and is used to represent a logical view of the system from a data
perspective. ER model makes use of ER diagrams, which are the diagrams sketched
to design a database. ER diagrams are built on three basic
concepts: entities, attributes, and relationships between them. An ER diagram in
DBMS defines entities, associated attributes, and relationships between entities.
 

[Link]

✔ Identification of database Relationship

ER Model stands for Entity Relationship Model is a high-level conceptual data model
diagram. ER model helps to systematically analyze data requirements to produce a well-
designed database. The ER Model represents real-world entities and the relationships
between them.

Why use ER Diagrams?

Here, are prime reasons for using the ER Diagram

 Helps you to define terms related to entity relationship modeling

 Provide a preview of how all your tables should connect, what fields are going to be
on each table
 Helps to describe entities, attributes, relationships
 ER diagrams are translatable into relational tables which allows you to build
databases quickly
 ER diagrams can be used by database designers as a blueprint for implementing data
in specific software applications
 The database designer gains a better understanding of the information to be contained
in the database with the help of ERP diagram
 ERD Diagram allows you to communicate with the logical structure of the database to
users

Facts about ER Diagram Model

Now in this ERD Diagram Tutorial, let’s check out some interesting facts about ER
Diagram Model:

 ER model allows you to draw Database Design

 It is an easy-to-use graphical tool for modelling data
 Widely used in Database Design
 It is a GUI representation of the logical structure of a Database
 It helps you to identify the entities which exist in a system and the relationships
between those entities

ER Diagrams Symbols & Notations

Entity Relationship Diagram Symbols & Notations mainly contains three basic symbols
which are rectangle, oval and diamond to represent relationships between elements, entities
and attributes. There are some sub-elements which are based on main elements in ERD
Diagram. ER Diagram is a visual representation of data that describes how data is related to
each other using different ERD Symbols and Notations.

Following are the main components and its symbols in ER Diagrams:

 Rectangles: This Entity Relationship Diagram symbol represents entity types

 Ellipses: Symbol represent attributes
 Diamonds: This symbol represents relationship types
 Lines: It links attributes to entity types and entity types with other relationship types
 Primary key: attributes are underlined
 Double Ellipses: Represent multi-valued attributes
Components of the ER Diagram

This model is based on three basic concepts:

 Entities
 Attributes
 Relationships

ER Diagram Examples

For example, in a University database, we might have entities for Students, Courses, and
Lecturers. Students entity can have attributes like Rollno, Name, and DeptID. They might
have relationships with Courses and Lecturers.

WHAT IS ENTITY?

A real-world thing either living or non-living that is easily recognizable and nonrecognizable.
It is anything in the enterprise that is to be represented in our database.
n entity can be place, person, object, event or a concept, which stores data in the database.
The characteristics of entities are must have an attribute, and a unique key. Every entity is
made up of some ‘attributes’ which represent that entity.

Examples of entities:

 Person: Employee, Student, Patient

 Place: Store, Building
 Object: Machine, product, and Car
 Event: Sale, Registration, Renewal
 Concept: Account, Course

Entity notation

Relationship

Relationship is nothing but an association among two or more entities. E.g., Tom works in
the Chemistry department.

Entities take part in relationships. We can often identify relationships with verbs or verb
phrases.

For example:
  You are attending this lecture
 I am giving the lecture
 Just loke entities, we can classify relationships according to relationship-types:
 A student attends a lecture
 A lecturer is giving a lecture.

Weak Entities

A weak entity is a type of entity which doesn’t have its key attribute. It can be identified
uniquely by considering the primary key of another entity. For that, weak entity sets need to
have participation.

In above ER Diagram examples, “Trans No” is a discriminator within a group of transactions

in an ATM.

Let’s learn more about a weak entity by comparing it with a Strong Entity

Strong Entity Set Weak Entity Set

Strong entity set always has a primary key. It does not have enough attributes to build a primary key.

It is represented by a rectangle symbol. It is represented by a double rectangle symbol.

It contains a Primary key represented by the It contains a Partial Key which is represented by a dashed
underline symbol. underline symbol.

The member of a strong entity set is called The member of a weak entity set called as a subordinate
as dominant entity set. entity set.
Strong Entity Set Weak Entity Set

Primary Key is one of its attributes which In a weak entity set, it is a combination of primary key and
helps to identify its member. partial key of the strong entity set.

In the ER diagram the relationship between

The relationship between one strong and a weak entity set
two strong entity set shown by using a
shown by using the double diamond symbol.
diamond symbol.

The connecting line of the strong entity set The line connecting the weak entity set for identifying
with the relationship is single. relationship is double.

Attributes

It is a single-valued property of either an entity-type or a relationship-type.

For example, a lecture might have attributes: time, date, duration, place, etc.

An attribute in ER Diagram examples, is represented by an Ellipse

Types of Attributes Description

Simple attributes can’t be divided any further. For
Simple attribute example, a student’s contact number. It is also
called an atomic value.
Types of Attributes Description
It is possible to break down composite attribute. For
example, a student’s full name may be further
Composite attribute
divided into first name, second name, and last
name.
This type of attribute does not include in the
physical database. However, their values are
derived from other attributes present in the
Derived attribute
database. For example, age should not be stored
directly. Instead, it should be derived from the DOB
of that employee.
Multivalued attributes can have more than one
Multivalued attribute values. For example, a student can have more than
one mobile number, email address, etc.
Cardinality Defines the numerical attributes of the relationship between two entities or entity
sets.

Different types of cardinal relationships are:

 One-to-One Relationships
 One-to-Many Relationships
 Many to One Relationships
 Many-to-Many Relationships
1. One-to-one:

One entity from entity set X can be associated with at most one entity of entity set Y and vice
versa.

Example: One student can register for numerous courses. However, all those courses have a
single line back to that one student.

2. One-to-many:

One entity from entity set X can be associated with multiple entities of entity set Y, but an
entity from entity set Y can be associated with at least one entity.
For example, one class is consisting of multiple students.

3. Many to One

More than one entity from entity set X can be associated with at most one entity of entity set
Y. However, an entity from entity set Y may or may not be associated with more than one
entity from entity set X.

For example, many students belong to the same class.

4. Many to Many:

One entity from X can be associated with more than one entity from Y and vice versa.

For example, Students as a group are associated with multiple faculty members, and faculty
members can be associated with multiple students.
How to Create an Entity Relationship Diagram (ERD)

Now in this ERD Diagram Tutorial, we will learn how to create an ER Diagram. Following
are the steps to create an ER Diagram:

Steps to Create an ER Diagram

Let’s study them with an Entity Relationship Diagram Example:

In a university, a Student enrolls in Courses. A student must be assigned to at least one or

more Courses. Each course is taught by a single Professor. To maintain instruction quality, a
Professor can deliver only one course
Step 1) Entity Identification

We have three entities

 Student
 Course
 Professor
Step 2) Relationship Identification

We have the following two relationships

 The student is assigned a course

 Professor delivers a course

Step 3) Cardinality Identification

For them problem statement we know that,

 A student can be assigned multiple courses

 A Professor can deliver only one course

Step 4) Identify Attributes

You need to study the files, forms, reports, data currently maintained by the organization to
identify attributes. You can also conduct interviews with various stakeholders to identify
entities. Initially, it’s important to identify the attributes without mapping them to a particular
entity.

Once, you have a list of Attributes, you need to map them to the identified entities. Ensure an
attribute is to be paired with exactly one entity. If you think an attribute should belong to
more than one entity, use a modifier to make it unique.

Once the mapping is done, identify the primary Keys. If a unique key is not readily available,
create one.
Entity Primary Key Attribute
Student Student_ID StudentName
Professor Employee_ID ProfessorName
Course Course_ID CourseName

For Course Entity, attributes could be Duration, Credits, Assignments, etc. For the sake of
ease we have considered just one attribute.

Step 5) Create the ERD Diagram

A more modern representation of Entity Relationship Diagram Example

Best Practices for Developing Effective ER Diagrams

Here are some best practice or example for Developing Effective ER Diagrams.

 Eliminate any redundant entities or relationships

 You need to make sure that all your entities and relationships are properly labeled
 There may be various valid approaches to an ER diagram. You need to make sure that
the ER diagram supports all the data you need to store
 You should assure that each entity only appears a single time in the ER diagram
 Name every relationship, entity, and attribute are represented on your diagram
 Never connect relationships to each other
 You should use colors to highlight important portions of the ER diagram
Example of Entity Relationship Diagram

● Design of logic database schema

 Description of logic database schema

The logical schema defines the structure of the data itself and the relationships between the
various attributes, tables, and entries.

 Table constraints

Database constraints are a key feature of database management systems. They ensure that
rules defined at data model creation are enforced when the data is manipulated (inserted,
updated, or deleted) in a database.
The following constraints are commonly used in SQL:
SQL constraints are rules that allow data to be entered into a table only if it meets the
predefined conditions.

 NOT NULL - Ensures that a column cannot have a NULL value

 UNIQUE - Ensures that all values in a column are different
 PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely
identifies each row in a table
 FOREIGN KEY - Prevents actions that would destroy links between tables
 CHECK - Ensures that the values in a column satisfies a specific condition
 DEFAULT - Sets a default value for a column if no value is specified
 INDEX - Used to create and retrieve data from the database very quickly

Convert conceptual database schema to logic database schema

Practice: Convert from ERD to LMD: Translate the below ERD

Homework [60 marks]

Below is the LMD, perform the following questions asked to it:

 a. Identify the types of relationships among the entities. /10 marks
b. Assign the cardinalities to each table in the relationship. / 5 marks
c. Identify the types of tables/entities available in the figure. /5 marks
d. Translate this LMD into ERD. / 10 marks
e. Build the Data dictionary of the entire Database. /20 marks
f. Create physical database into DBMS. / 10 marks
 Optimization of database

Data normalization: in the context of databases refers to a set of techniques used to organize
and structure relational databases efficiently. The main goal of data normalization in
databases is to reduce data redundancy, improve data integrity, and ensure the consistency of
the data while minimizing anomalies like update anomalies, insertion anomalies, and deletion
anomalies. It is an essential concept in relational database design.

Then why do you need it? If there is no normalization in SQL, there will be many
problems, such as:

 Insert Anomaly: This happens when we cannot insert data into the table without
another.
  Update Anomaly: This is due to data inconsistency caused by data redundancy
and data update.
 Delete exception: Occurs when some attributes are lost due to the deletion of
other attributes.
Need for normalization:
1) It eliminates redundant data.

2) It reduces chances of data error.

3) The normalization is important because it allows database to take up less disk

space.

4) It also helps in increasing the performance.

5) It improves the data integrity and consistency.

Advantages:
There are many benefits to normalizing a database. Some of the main advantages are:

 By using normalization redundancy of database or data duplication can be

resolved.
 We can minimize null values by using normalization.
 Results in a more compact database (due to less data redundancy/zero).
 Minimize/avoid data modification problems.
 It simplifies the queries
 The database structure is clearer and easier to understand.
 The database can be expanded without affecting existing data.
 Finding, sorting, and indexing can be faster because the table is small and more
rows can be accommodated on the data page.

Data normalization is typically achieved by dividing a database into multiple related tables
and establishing relationships between them. This process is guided by a set of rules known
as normal forms. The most common normal forms are:

1. First Normal Form (1NF) : A table is in 1NF if it has no repeating groups or arrays,
and each attribute (column) contains only atomic (indivisible) values. This means that
each cell in the table should hold a single piece of data.
 2. Second Normal Form (2NF): A table is in 2NF if it is in 1NF and all non-key
attributes are functionally dependent on the entire primary key. In other words, all
attributes should depend on the entire primary key, not just part of it.
Third Normal Form (3NF) : A table is in 3NF if it is in 2NF and has no transitive
dependencies. This means that non-key attributes should not depend on other non-key
attributes.

A "transitive dependency" is a concept in the context of database design and normalization

theory. It refers to a particular type of functional dependency between attributes (columns) in
a relational database. In a transitive dependency, one attribute depends on another attribute
through a third attribute.

A functional dependency occurs when the value of one attribute determines the value of
another attribute.

Data inconsistency is the term that refers to mismatched data values in DBMS, whereas
data redundancy refers to the unwanted repetition of data in different locations.

To illustrate this concept, consider the following scenario:

Suppose you have a table with three attributes: A, B, and C. A is said to have a transitive
dependency on B if the following conditions hold:

1. A depends on B.
2. B depends on C.
3. A does not directly depend on C.

In other words, the dependency from A to C is indirect and mediated through B. This kind of
dependency is considered a transitive dependency.

3. Boyce-Codd Normal Form (BCNF) : A table is in BCNF if, for every non-trivial
functional dependency, the left-hand side of the dependency is a superkey. In BCNF,
there are no partial dependencies, making it a more stringent form of normalization
than 3NF.
 4. Fourth Normal Form (4NF) and beyond: These normal forms address more
complex types of dependencies, such as multi-valued dependencies and join
dependencies, but they are less commonly encountered in practice.
Here is a list of Normal Forms in SQL:

 1NF (First Normal Form)

 2NF (Second Normal Form)
 3NF (Third Normal Form)
 BCNF (Boyce-Codd Normal Form)
 4NF (Fourth Normal Form)
 5NF (Fifth Normal Form)
 6NF (Sixth Normal Form)

The Theory of Data Normalization in MySQL server is still being developed further. For
example, there are discussions even on 6th Normal Form. However, in most practical
applications, normalization achieves its best in 3rd Normal Form. The evolution of
Normalization in SQL theories is illustrated below-

Database Normal Forms

Database Normalization With Examples

Database Normalization Example can be easily understood with the help of a case study.
Assume, a video library maintains a database of movies rented out. Without any
normalization in database, all information is stored in one table as shown below. Let’s
understand Normalization database with normalization example with solution:
Here you see Movies Rented column has multiple values. Now let’s move into 1st Normal
Forms:

1NF (First Normal Form) Rules

 Each table cell should contain a single value.

 Each record needs to be unique.

The above table in 1NF-

1NF Example

Example of 1NF in DBMS

Before we proceed let’s understand a few things —

What is a KEY in SQL

A KEY in SQL is a value used to identify records in a table uniquely. An SQL KEY is a
single column or combination of multiple columns used to uniquely identify rows or tuples in
the table. SQL Key is used to identify duplicate information, and it also helps establish a
relationship between multiple tables in the database.
Note: Columns in a table that are NOT used to identify a record uniquely are called non-key
columns.

What is a Primary Key?

Primary Key in DBMS

A primary is a single column value used to identify a database record uniquely.

It has following attributes

 A primary key cannot be NULL

 A primary key value must be unique
 The primary key values should rarely be changed
 The primary key must be given a value when a new record is inserted.

What is Composite Key?

A composite key is a primary key composed of multiple columns used to identify a record
uniquely

In our database, we have two people with the same name Robert Phil, but they live in
different places.

Composite key in Database

Hence, we require both Full Name and Address to identify a record uniquely. That is a
composite key.
Let’s move into second normal form 2NF

2NF (Second Normal Form) Rules

 Rule 1- Be in 1NF
 Rule 2- Single Column Primary Key that does not functionally dependant on any
subset of candidate key relation

It is clear that we can’t move forward to make our simple database in 2nd Normalization form
unless we partition the table above.

We have divided our 1NF table into two tables viz. Table 1 and Table2. Table 1 contains
member information. Table 2 contains information on movies rented.

We have introduced a new column called Membership_id which is the primary key for table
1. Records can be uniquely identified in Table 1 using membership id

Database – Foreign Key

In Table 2, Membership_ID is the Foreign Key

Foreign Key in DBMS

Foreign Key references the primary key of another Table! It helps connect your Tables

 A foreign key can have a different name from its primary key
 It ensures rows in one table have corresponding rows in another
 Unlike the Primary key, they do not have to be unique. Most often they aren’t
 Foreign keys can be null even though primary keys can not
Why do you need a foreign key?

Suppose, a novice inserts a record in Table B such as

You will only be able to insert values into your foreign key that exist in the unique key in the
parent table. This helps in referential integrity.

The above problem can be overcome by declaring membership id from Table2 as foreign key
of membership id from Table1

Now, if somebody tries to insert a value in the membership id field that does not exist in the
parent table, an error will be shown!

What are transitive functional dependencies?

A transitive functional dependency is when changing a non-key column, might cause any
of the other non-key columns to change

Consider the table 1. Changing the non-key column Full Name may change Salutation.

Let’s move into 3NF

3NF (Third Normal Form) Rules

 Rule 1- Be in 2NF
 Rule 2- Has no transitive functional dependencies

To move our 2NF table into 3NF, we again need to again divide our table.

3NF Example

Below is a 3NF example in SQL database:

We have again divided our tables and created a new table which stores Salutations.

There are no transitive functional dependencies, and hence our table is in 3NF

In Table 3 Salutation ID is primary key, and in Table 1 Salutation ID is foreign to primary

key in Table 3

Now our little example is at a level that cannot further be decomposed to attain higher normal
form types of normalization in DBMS. In fact, it is already in higher normalization forms.
Separate efforts for moving into next levels of normalizing data are normally needed in
complex databases. However, we will be discussing next levels of normalisation in DBMS in
brief in the following.

BCNF (Boyce-Codd Normal Form)

Even when a database is in 3rd Normal Form, still there would be anomalies resulted if it has
more than one Candidate Key.

Sometimes is BCNF is also referred as 3.5 Normal Form.

4NF (Fourth Normal Form) Rules

If no database table instance contains two or more, independent and multivalued data
describing the relevant entity, then it is in 4th Normal Form.

5NF (Fifth Normal Form) Rules

A table is in 5th Normal Form only if it is in 4NF and it cannot be decomposed into any
number of smaller tables without loss of data.

6NF (Sixth Normal Form) Proposed

6th Normal Form is not standardized, yet however, it is being discussed by database experts
for some time. Hopefully, we would have a clear & standardized definition for 6th Normal
Form in the near future…
That’s all to SQL Normalization!!!

[Link]

Homework

Perform 1NF, 2NF and 3NF for the below scenario:

Imagine we're building a restaurant management application. That application needs to store
data about the company's employees and it starts out by creating the following table of
employees:

EMPLOYEE_ID NAME JOB_CODE JOB STATE_CODE HOME_STATE

E001 Alice J01 Chef 26 Michigan

E001 Alice J02 Waiter 26 Michigan

E002 Bob J02 Waiter 56 Wyoming

E002 Bob J03 Bartender 56 Wyoming

E003 Alice J01 Chef 56 Wyoming

Indexing in DBMS
o Indexing is used to optimize the performance of a database by minimizing the number
of disk accesses required when a query is processed.
o The index is a type of data structure. It is used to locate and access the data in a
database table quickly.

Index structure:
Indexes can be created using some database columns.

o The first column of the database is the search key that contains a copy of the primary
key or candidate key of the table. The values of the primary key are stored in sorted
order so that the corresponding data can be accessed easily.
o The second column of the database is the data reference. It contains a set of pointers
holding the address of the disk block where the value of the particular key can be found.

Indexing Methods

Ordered indices

The indices are usually sorted to make searching faster. The indices which are sorted are
known as ordered indices.
Example: Suppose we have an employee table with thousands of record and each of which is
10 bytes long. If their IDs start with 1, 2, 3. .. and so on and we have to search student with
ID-543.

o In the case of a database with no index, we have to search the disk block from starting
till it reaches 543. The DBMS will read the record after reading 543*10=5430 bytes.
o In the case of an index, we will search using indexes and the DBMS will read the record
after reading 542*2= 1084 bytes which are very less compared to the previous case.

Primary Index

o If the index is created on the basis of the primary key of the table, then it is known as
primary indexing. These primary keys are unique to each record and contain 1:1 relation
between the records.
o As primary keys are stored in sorted order, the performance of the searching operation
is quite efficient.

o The primary index can be classified into two types: Dense index and Sparse index.

Dense index

o The dense index contains an index record for every search key value in the data file. It
makes searching faster.
o In this, the number of records in the index table is same as the number of records in
the main table.
o It needs more space to store index record itself. The index records have the search key
and a pointer to the actual record on the disk.
Sparse index

o In the data file, index record appears only for a few items. Each item points to a block.
o In this, instead of pointing to each record in the main table, the index points to the
records in the main table in a gap.

Clustering Index

o A clustered index can be defined as an ordered data file. Sometimes the index is created
on non-primary key columns which may not be unique for each record.

o In this case, to identify the record faster, we will group two or more columns to get
the unique value and create index out of them. This method is called a clustering index.
o The records which have similar characteristics are grouped, and indexes are created
for these group.

Example: suppose a company contains several employees in each department. Suppose we use
a clustering index, where all employees which belong to the same Dept_ID are considered
within a single cluster, and index pointers point to the cluster as a whole. Here Dept_Id is a
non-unique key.
The previous schema is little confusing because one disk block is shared by records which
belong to the different cluster. If we use separate disk block for separate clusters, then it is
called better technique.
Secondary Index

In the sparse indexing, as the size of the table grows, the size of mapping also grows. These
mappings are usually kept in the primary memory so that address fetch should be faster. Then
the secondary memory searches the actual data based on the address got from mapping. If the
mapping size grows then fetching the address itself becomes slower. In this case, the sparse
index will not be efficient. To overcome this problem, secondary indexing is introduced.

In secondary indexing, to reduce the size of mapping, another level of indexing is introduced.
In this method, the huge range for the columns is selected initially so that the mapping size of
the first level becomes small. Then each range is further divided into smaller ranges. The
mapping of the first level is stored in the primary memory, so that address fetch is faster. The
mapping of the second level and actual data are stored in the secondary memory (hard disk).
For example:

o If you want to find the record of roll 111 in the diagram, then it will search the highest
entry which is smaller than or equal to 111 in the first level index. It will get 100 at this
level.
o Then in the second index level, again it does max (111) <= 111 and gets 110. Now
using the address 110, it goes to the data block and starts searching each record till it
gets 111.
o This is how a search is performed in this method. Inserting, updating or deleting is
also done in the same manner.

Description of DBMS

A Database Management System (DBMS) is a software system that is designed to manage

and organize data in a structured manner. It allows users to create, modify, and query a
database, as well as manage the security and access controls for that database.

Some key features of a DBMS include:

 1. Data modeling: A DBMS provides tools for creating and modifying data
models, which define the structure and relationships of the data in a database.
2. Data storage and retrieval: A DBMS is responsible for storing and retrieving
data from the database, and can provide various methods for searching and
querying the data.
3. Concurrency control: A DBMS provides mechanisms for controlling
concurrent access to the database, to ensure that multiple users can access the
data without conflicting with each other.
4. Data integrity and security: A DBMS provides tools for enforcing data
integrity and security constraints, such as constraints on the values of data and
access controls that restrict who can access the data.
5. Backup and recovery: A DBMS provides mechanisms for backing up and
recovering the data in the event of a system failure.
6. DBMS can be classified into two types: Relational Database Management
System (RDBMS) and Non-Relational Database Management System (NoSQL
or Non-SQL)
7. RDBMS: Data is organized in the form of tables and each table has a set of rows
and columns. The data is related to each other through primary and foreign keys.
8. NoSQL: Data is organized in the form of key-value pairs, document, graph, or
column-based. These are designed to handle large-scale, high-performance
scenarios.
DBMS allows users the following tasks:

 Data Definition: It helps in the creation, modification, and removal of

definitions that define the organization of data in the database.
 Data Updation: It helps in the insertion, modification, and deletion of the actual
data in the database.
 Data Retrieval: It helps in the retrieval of data from the database which can be
used by applications for various purposes.
 User Administration: It helps in registering and monitoring users, enforcing
data security, monitoring performance, maintaining data integrity, dealing with
concurrency control, and recovering information corrupted by unexpected
failure.
Advantages of using a DBMS:

1. Data organization: A DBMS allows for the organization and storage of data in
a structured manner, making it easy to retrieve and query the data as needed.
2. Data integrity: A DBMS provides mechanisms for enforcing data integrity
constraints, such as constraints on the values of data and access controls that
restrict who can access the data.
3. Concurrent access: A DBMS provides mechanisms for controlling concurrent
access to the database, to ensure that multiple users can access the data without
conflicting with each other.
4. Data security: A DBMS provides tools for managing the security of the data,
such as controlling access to the data and encrypting sensitive data.
5. Backup and recovery: A DBMS provides mechanisms for backing up and
recovering the data in the event of a system failure.
6. Data sharing: A DBMS allows multiple users to access and share the same
data, which can be useful in a collaborative work environment.

Disadvantages of using a DBMS:

1. Complexity: DBMS can be complex to set up and maintain, requiring

specialized knowledge and skills.
2. Performance overhead: The use of a DBMS can add overhead to the
performance of an application, especially in cases where high levels of
concurrency are required.
3. Scalability: The use of a DBMS can limit the scalability of an application, since
it requires the use of locking and other synchronization mechanisms to ensure
data consistency.
4. Cost: The cost of purchasing, maintaining and upgrading a DBMS can be high,
especially for large or complex systems.
5. Limited use cases: Not all use cases are suitable for a DBMS, some solutions
don’t need high reliability, consistency or security and may be better served by
other types of data storage.

There are several types of DBMS, including:

 1. Relational DBMS (RDBMS): An RDBMS stores data in tables with rows and
columns, and uses SQL (Structured Query Language) to manipulate the data.
2. Object-Oriented DBMS (OODBMS): An OODBMS stores data as objects,
which can be manipulated using object-oriented programming languages.
3. NoSQL DBMS: A NoSQL DBMS stores data in non-relational data structures,
such as key-value pairs, document-based models, or graph models.

Hardware, Software, Data, Database Access Language, Procedures and Users all together
form the components of a DBMS.

Let us discuss the components one by one clearly.

Hardware

The hardware is the actual computer system used for keeping and accessing the database. The
conventional DBMS hardware consists of secondary storage devices such as hard disks.
Databases run on the range of machines from micro computers to mainframes.

Software

Software is the actual DBMS between the physical database and the users of the system. All
the requests from the user for accessing the database are handled by DBMS.

Data

It is an important component of the database management system. The main task of DBMS is
to process the data. Databases are used to store the data, retrieved, and updated to and from the
databases.

Users

There are a number of users who can access or retrieve the data on demand using the application
and the interfaces provided by the DBMS.

Examples of DBMS

1. Microsoft Access
2. MySQL
 3. Oracle Database
4. MongoDB
5. IBM Db2 DBMS
6. Amazon RDS
7. PostgreSQL
8. Apache Cassandra
9. Informix
10. Maria DB
11. SQLite
12. Microsoft SQL Server
etc
 Preparation of DBMS Environment (MySQL)

What is MySQL is a relational database management system (RDBMS) developed by Oracle

that is based on structured query language (SQL).

4 Keys to understand Mysql

 MySQL is widely compatible

 MySQL databases are relational
 MySQL is open-source
 MySQL is easy to use

MySQL installation processes

To download MySQL from Internet, go to [Link]/downloads , download it and

mysql workbench 8.0 CE then install it.

Convert logic database schema to physical database schema

A logical data model is a data model that provides a detailed, structured description of data
elements and the connections between them. It includes all entities — a specific object
transferred from the real world (relevant to business) — and the relationships among them.
These entities have defined their attributes as their characteristics.

Logical Data Model Examples

A physical data model specifies how the data model will be built in the database. It outlines
all table structures, including column name, data types, column constraints, primary key and
foreign key with indexes to the relevant table column, relationships between tables, stored
procedures, and views.

Physical Data Model Examples

Entities have been transformed into tables and attributes into table columns. Their names are
also translated into technical terms — how they could be implemented and stored in the
database. In addition, each column's data type has been specified.

Learning outcome 3: Implement Database

● Description to SQL

Introduction to SQL

Structured query language (SQL) is a programming language for storing and processing
information in a relational database. A relational database stores information in tabular form,
with rows and columns representing different data attributes and the various relationships
between the data values.

What are the components of a SQL system?

SQL table

A SQL table is the basic element of a relational database. The SQL database table consists of
rows and columns. Database engineers create relationships between multiple database tables
to optimize data storage space.

SQL statements

SQL statements, or SQL queries, are valid instructions that relational database management
systems understand. Software developers build SQL statements by using different SQL
language elements. SQL language elements are components such as identifiers, variables, and
search conditions that form a correct SQL statement.

Stored procedures

Stored procedures are a collection of one or more SQL statements stored in the relational
database. Software developers use stored procedures to improve efficiency and performance.
For example, they can create a stored procedure for updating sales tables instead of writing
the same SQL statement in different applications.

What are SQL commands?

Structured query language (SQL) commands are specific keywords or SQL statements that
developers use to manipulate the data stored in a relational database. You can categorize SQL
commands as follows.

Data definition language

Data definition language (DDL) refers to SQL commands that design the database structure.
Database engineers use DDL to create and modify database objects based on the business
requirements. For example, the database engineer uses the CREATE command to create
database objects such as tables, views, and indexes.

List of DDL commands:

CREATE: This command is used to create the database or its objects (like table, index,
function, views, store procedure, and triggers).
DROP: This command is used to delete objects from the database.
ALTER: This is used to alter the structure of the database.
TRUNCATE: This is used to remove all records from a table, including all spaces
allocated for the records are removed.
COMMENT: This is used to add comments to the data dictionary.
RENAME: This is used to rename an object existing in the database.

Data query language

Data query language (DQL) consists of instructions for retrieving data stored in relational
databases. Software applications use the SELECT command to filter and return specific
results from a SQL table.

SELECT: It is used to retrieve data from the database.

Data manipulation language

Data manipulation language (DML) statements write new information or modify existing
records in a relational database. For example, an application uses the INSERT command to
store a new record in the database.

List of DML commands:

INSERT: It is used to insert data into a table.
UPDATE: It is used to update existing data within a table.
DELETE: It is used to delete records from a database table.
LOCK: Table control concurrency.
CALL: Call a PL/SQL or JAVA subprogram.
EXPLAIN PLAN: It describes the access path to data.

Data control language

Database administrators use data control language (DCL) to manage or authorize database
access for other users. For example, they can use the GRANT command to permit certain
applications to manipulate one or more tables.

GRANT: This command gives users access privileges to the database.

REVOKE: This command withdraws the user’s access privileges given by using the
GRANT command.

Transaction control language

The relational engine uses transaction control language (TCL) to automatically make
database changes. For example, the database uses the ROLLBACK command to undo an
erroneous transaction.

COMMIT: Commits a Transaction.

ROLLBACK: Rollbacks a transaction in case of any error occurs.

SQL sub-languages

In SQL a Subquery can be simply defined as a query within another query. In other words
we can say that a Subquery is a query that is embedded in WHERE clause of another SQL
query.

Syntax: There is not any general syntax for Subqueries. However, Subqueries are seen to
be used most frequently with SELECT statement as shown below:
SELECT column_name

FROM table_name

WHERE column_name expression operator

( SELECT COLUMN_NAME from TABLE_NAME WHERE ... );
SQL operators

SQL Arithmetic Operators

Operator Description Example

+ Add SELECT 30 + 20;

- Subtract SELECT 30 - 20;

* Multiply SELECT 30 * 20;

/ Divide SELECT 30 / 10;

% Modulo SELECT 17 % 5;

SQL Bitwise Operators

Operator Description

& Bitwise AND

| Bitwise OR

^ Bitwise exclusive OR

SQL Comparison Operators

Operator Description Example

= Equal to SELECT * FROM Products WHERE Price = 18;

> Greater than SELECT * FROM Products WHERE Price > 30;

< Less than SELECT * FROM Products WHERE Price < 30;

>= Greater or equal to SELECT * FROM Products WHERE Price >= 30;

<= Less or equal to SELECT * FROM Products WHERE Price <= 30;

<> OR != Not equal to SELECT * FROM Products WHERE Price <> 18; or

SELECT * FROM Products WHERE Price != 18;

SQL Compound Operators

Operator Description

+= Add equals
-= Subtract equals

*= Multiply equals

/= Divide equals

%= Modulo equals

&= Bitwise AND equals

^-= Bitwise exclusive equals

|*= Bitwise OR equals

SQL Logical Operators

Operator Description Example

SELECT ProductName FROM Products WHERE

ALL TRUE if all of the subquery
ProductID = ALL (SELECT ProductID FROM
values meet the condition
OrderDetails WHERE Quantity = 10);
SELECT * FROM Customers WHERE City = "London"
AND TRUE if all the conditions
AND Country = "UK";
separated by AND is TRUE

SELECT * FROM Products WHERE Price > ANY

ANY TRUE if any of the
(SELECT Price FROM Products WHERE Price > 50);
subquery values meet the
condition

SELECT * FROM Products WHERE Price BETWEEN

BETWEEN TRUE if the operand is
50 AND 60;
within the range of
comparisons

SELECT SupplierName FROM Suppliers WHERE

EXISTS TRUE if the subquery
EXISTS (SELECT ProductName FROM Products
returns one or more records
WHERE [Link] = [Link]
AND Price < 20);
 SELECT * FROM Customers WHERE City IN
IN TRUE if the operand is
('Paris','London');
equal to one of a list of
expressions

SELECT * FROM Customers WHERE City LIKE 's%';

LIKE TRUE if the operand
matches a pattern

SELECT * FROM Customers WHERE City NOT LIKE

NOT Displays a record if the
's%';
condition(s) is NOT TRUE

SELECT * FROM Customers WHERE City = "London"

OR TRUE if any of the
OR Country = "UK";
conditions separated by OR
is TRUE

SELECT * FROM Products WHERE Price > SOME

SOME TRUE if any of the
(SELECT Price FROM Products WHERE Price > 20);
subquery values meet the
condition

Application of DDL commands

The SQL CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a new SQL database.

Syntax

CREATE DATABASE databasename;

CREATE DATABASE testDB;

The SQL DROP DATABASE Statement

The DROP DATABASE statement is used to drop an existing SQL database.

Syntax

DROP DATABASE databasename;

DROP DATABASE testDB;

Create Constraints

Constraints can be specified when the table is created with the CREATE TABLE statement,
or after the table is created with the ALTER TABLE statement.

Syntax

CREATE TABLE table_name (

column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);

The following constraints are commonly used in SQL:

 NOT NULL - Ensures that a column cannot have a NULL value

 UNIQUE - Ensures that all values in a column are different
 PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely
identifies each row in a table
 FOREIGN KEY - Prevents actions that would destroy links between tables
 CHECK - Ensures that the values in a column satisfies a specific condition
 DEFAULT - Sets a default value for a column if no value is specified
 CREATE INDEX - Used to create and retrieve data from the database very quickly.

The MySQL CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in a database.

Syntax

CREATE TABLE table_name (

column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

Create Table Using Another Table

A copy of an existing table can also be created using CREATE TABLE.

The new table gets the same column definitions. All columns or specific columns can be
selected.

If you create a new table using an existing table, the new table will be filled with the existing
values from the old table.

Syntax

CREATE TABLE new_table_name AS

SELECT column1, column2,...
FROM existing_table_name
WHERE .... ;

The following SQL creates a new table called "TestTables" (which is a copy of the
"Customers" table):

CREATE TABLE TestTable AS

SELECT customername, contactname
FROM customers;

The MySQL DROP TABLE Statement

The DROP TABLE statement is used to drop an existing table in a database.

Syntax
DROP TABLE table_name;

DROP TABLE Shippers;

MySQL TRUNCATE TABLE

The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table
itself.

Syntax

TRUNCATE TABLE table_name;

MySQL ALTER DATABASE Statement

The ALTER DATABASE Statement of MySQL allows you to modify/change the

characteristics of an existing database.

Syntax

Following is the syntax of the ALTER DATABASE statement −

ALTER DATABASE [database_name] alter_option ...

Where, database_name is the name of the database you need to change and this statement
provides four options −

 CHARACTER SET − This option allows you to change the default character set of
the database.
 COLLATE − This option allows you to change the collation of the database.
 ENCRYPTION − This option allows you enable (Y) or disable (N) the default
database encryption.
 ReadOnly − Using this option you can allow modifications on the database along
with the objects within (0) or make it read only (1).

Example

Suppose we have created a database as shown below −

mysql> CREATE DATABASE myDatabase;

Following query changes the character set of the above create database −

mysql> ALTER DATABASE myDatabase CHARACTER SET= ascii;

Query OK, 1 row affected, 1 warning (0.20 sec)

You can see the list of all the available character sets using the SHOW CHARACTER
SET Statement

Altering the COLLATION

Similarly, following query changes the collation of the database named mydatabase −

mysql> ALTER DATABASE mydatabase COLLATE utf8_general_ci;

Query OK, 1 row affected, 1 warning (0.20 sec)

You can see the list of all the available collations using the SHOW COLLATION Statement.

Making the database ReadOnly

You can set mydatabase read-only using the following query −

mysql> ALTER DATABASE mydatabase READ ONLY = 1;

Query OK, 1 row affected (0.43 sec)

All Options in one query

You can use all the options in one query as follows −

mysql> ALTER DATABASE mydatabase CHARACTER SET utf8 COLLATE

utf8_general_ci ENCRYPTION = 'Y' READ ONLY = 1;

You can verify the characteristics of the created database as shown below −

mysql> SHOW CREATE DATABASE mydatabase;

| Database | Create Database
| mydatabase | CREATE DATABASE `mydatabase` /*!40100 DEFAULT CHARACTER
SET utf8 */ /*!80016 DEFAULT ENCRYPTION='Y' */ /* READ ONLY = 1 */ |
1 row in set (0.08 sec)

MySQL ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The ALTER TABLE statement is also used to add and drop various constraints on an existing
table.

ALTER TABLE - ADD Column

To add a column in a table, use the following syntax:

ALTER TABLE table_name

ADD column_name datatype;

The following SQL adds an "Email" column to the "Customers" table:

ALTER TABLE Customers

ADD Email varchar(255);

ALTER TABLE - DROP COLUMN

To delete a column in a table, use the following syntax (notice that some database systems
don't allow deleting a column):

ALTER TABLE table_name

DROP COLUMN column_name;

The following SQL deletes the "Email" column from the "Customers" table:

ALTER TABLE Customers

DROP COLUMN Email;

ALTER TABLE - MODIFY COLUMN

To change the data type of a column in a table, use the following syntax:

ALTER TABLE table_name

MODIFY COLUMN column_name datatype;

Look at the "Persons" table:

ID LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

Now we want to add a column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons

ADD DateOfBirth date;

ID LastName FirstName Address City DateOfBirth

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

Change Data Type Example

Now we want to change the data type of the column named "DateOfBirth" in the "Persons"
table.

We use the following SQL statement:

Example

ALTER TABLE Persons

MODIFY COLUMN DateOfBirth year;

Notice that the "DateOfBirth" column is now of type year and is going to hold a year in a
two- or four-digit format.

DROP COLUMN Example

Next, we want to delete the column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

Example

ALTER TABLE Persons

DROP COLUMN DateOfBirth;

The "Persons" table will now look like this:

ID LastName FirstName Address City

1 Hansen Ola Timoteivn 10 Sandnes

2 Svendson Tove Borgvn 23 Sandnes

3 Pettersen Kari Storgt 20 Stavanger

Application of DML commands

MySQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two ways:

1. Specify both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

2. 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. Here, the INSERT
INTO syntax would be as follows:
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Example
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode,
Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

Insert Data Only in Specified Columns

It is also possible to only insert data in specific columns.

The following SQL statement will insert a new record, but only insert data in the
"CustomerName", "City", and "Country" columns (CustomerID will be updated
automatically):

Example

INSERT INTO Customers (CustomerName, City, Country)

VALUES ('Cardinal', 'Stavanger', 'Norway');

The MySQL UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

UPDATE Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City = 'Frankfurt'
WHERE CustomerID = 1;

UPDATE Multiple Records

It is the WHERE clause that determines how many records will be updated.

The following SQL statement will update the PostalCode to 00000 for all records where
country is "Mexico":

Example

UPDATE Customers
SET PostalCode = 00000
WHERE Country = 'Mexico';

Update Warning!
Be careful when updating records. If you omit the WHERE clause, ALL records will be
updated!

Example

UPDATE Customers
SET PostalCode = 00000;

The MySQL DELETE Statement

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

DELETE Syntax

DELETE FROM table_name WHERE condition;

Example
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

Delete All Records

It is possible to delete all rows in a table without deleting the table. This means that the table
structure, attributes, and indexes will be intact:

DELETE FROM table_name;

Example

DELETE FROM Customers;

Application of DQL Command

The MySQL SELECT Statement

The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

SELECT Syntax

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 table_name;

The following SQL statement selects the "CustomerName", "City", and "Country" columns
from the "Customers" table:

Example
SELECT CustomerName, City, Country FROM Customers;

SELECT * Example

The following SQL statement selects ALL the columns from the "Customers" table:

Example

SELECT * FROM Customers;

The MySQL SELECT DISTINCT Statement

The SELECT DISTINCT statement is used to return only distinct (different) values.

Inside a table, a column often contains many duplicate values; and sometimes you only want
to list the different (distinct) values.

SELECT DISTINCT Syntax

SELECT DISTINCT column1, column2, ...

FROM table_name;

SELECT Example Without DISTINCT

The following SQL statement selects all (including the duplicates) values from the "Country"
column in the "Customers" table:

Example

SELECT Country FROM Customers;

Now, let us use the SELECT DISTINCT statement and see the result.
SELECT DISTINCT Examples

The following SQL statement selects only the DISTINCT values from the "Country" column
in the "Customers" table:

Example

SELECT DISTINCT Country FROM Customers;

The following SQL statement counts and returns the number of different (distinct) countries
in the "Customers" table:

Example

SELECT COUNT(DISTINCT Country) FROM Customers;

SQL aggregate function

1. COUNT FUNCTION

o COUNT function is used to Count the number of rows in a database table. It can work
on both numeric and non-numeric data types.
o COUNT function uses the COUNT(*) that returns the count of all the rows in a
specified table. COUNT(*) considers duplicate and Null.

Syntax
 COUNT(*)
or COUNT( [ALL|DISTINCT] expression )

Sample table:

PRODUCT_MAST

PRODUCT COMPANY QTY RATE COST

Item1 Com1 2 10 20

Item2 Com2 3 25 75

Item3 Com1 2 30 60

Item4 Com3 5 10 50

Item5 Com2 2 20 40

Item6 Cpm1 3 25 75

Item7 Com1 5 30 150

Item8 Com1 3 10 30

Item9 Com2 2 25 50

Item10 Com3 4 30 120

Example: COUNT()

SELECT COUNT(*) FROM PRODUCT_MAST;

Output:

10

Example: COUNT with WHERE

SELECT COUNT(*) FROM PRODUCT_MAST WHERE RATE>=20;

Output:

Example: COUNT() with DISTINCT

SELECT COUNT(DISTINCT COMPANY) FROM PRODUCT_MAST;

Output:

Example: COUNT() with GROUP BY

SELECT COMPANY, COUNT(*) FROM PRODUCT_MAST GROUP BY COMPANY;

Output:

Com1 5
Com2 3
Com3 2

Example: COUNT() with HAVING

SELECT COMPANY, COUNT(*) FROM PRODUCT_MAST GROUP BY COMPANY

HAVING COUNT(*)>2;

Output:

Com1 5
Com2 3

2. SUM() Function

Sum function is used to calculate the sum of all selected columns. It works on numeric fields
only.
Syntax

SUM() or SUM( [ALL|DISTINCT] expression )

Example: SUM()

SELECT SUM(COST) FROM PRODUCT_MAST;

Output:

670

Example: SUM() with WHERE

SELECT SUM(COST) FROM PRODUCT_MAST WHERE QTY>3;

Output:

320

Example: SUM() with GROUP BY

SELECT SUM(COST) FROM PRODUCT_MAST WHERE QTY>3 GROUP BY COMPA

NY;

Output:

Com1 150
Com2 170

Example: SUM() with HAVING

SELECT COMPANY, SUM(COST) FROM PRODUCT_MAST GROUP BY COMPANY

HAVING SUM(COST)>=170;

Output:

Com1 335
Com3 170
3. AVG function

The AVG function is used to calculate the average value of the numeric type. AVG function
returns the average of all non-Null values.

Syntax

AVG() or AVG( [ALL|DISTINCT] expression )

Example:

SELECT AVG(COST) FROM PRODUCT_MAST;

Output:

67.00

4. MAX Function

MAX function is used to find the maximum value of a certain column. This function
determines the largest value of all selected values of a column.

Syntax

MAX() or MAX( [ALL|DISTINCT] expression )

Example:

SELECT MAX(RATE) FROM PRODUCT_MAST;

30

5. MIN Function

MIN function is used to find the minimum value of a certain column. This function
determines the smallest value of all selected values of a column.

Syntax
MIN() or MIN( [ALL|DISTINCT] expression )

Example:

SELECT MIN(RATE) FROM PRODUCT_MAST;

Output:

10

[Link]

SQL CLAUSES

o SQL clause helps us to retrieve a set or bundles of records from the table.
o SQL clause helps us to specify a condition on the columns or the records of a table.

Different clauses available in the Structured Query Language are as follows:

1. WHERE CLAUSE
2. GROUP BY CLAUSE
3. HAVING CLAUSE
4. ORDER BY CLAUSE

Let's see each clause one by one with an example. We will use MySQL database for writing
the queries in examples.

1. WHERE CLAUSE

A WHERE clause in SQL is used with the SELECT query, which is one of the data
manipulation language commands. WHERE clauses can be used to limit the number of rows
to be displayed in the result set, it 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.

WHERE clause with SELECT Query

Asterisk symbol is used with a WHERE clause in a SELECT query to retrieve all the column
values for every record from a table.
Syntax of where clause with a select query to retrieve all the column values for every record
from a table:

1. SELECT * FROM TABLENAME WHERE CONDITION;

If according to the requirement, we only want to retrieve selective columns, then we will use
below syntax:

1. SELECT COLUMNNAME1, COLUMNNAME2 FROM TABLENAME WHERE

CONDITION;

Consider the employee table with the following data:

E_ID Name Salary City Designation Date_of_Joining Age

1 Sakshi Kumari 50000 Mumbai Project Manager 2021-06-20 24

2 Tejaswini Naik 75000 Delhi System Engineer 2019-12-24 23

3 Anuja Sharma 40000 Jaipur Manager 2021-08-15 26

4 Anushka Tripathi 90000 Mumbai Software Tester 2021-06-13 24

5 Rucha Jagtap 45000 Bangalore Project Manager 2020-08-09 23

6 Rutuja Deshmukh 60000 Bangalore Manager 2019-07-17 26

7 Swara Baviskar 55000 Jaipur System Engineer 2021-10-10 24

8 Sana Sheik 45000 Pune Software Engineer 2020-09-10 26

9 Swati Kumari 50000 Pune Software Tester 2021-01-01 25

10 Mayuri Patel 60000 Mumbai Project Manager 2020-10-02 24

11 Simran Khanna 45500 Kolhapur HR 2019-01-02 26

12 Shivani Wagh 50500 Delhi Software Developer 2016-09-10 25

13 Kiran Maheshwari 50000 Nashik HR 2013-12-12 23

14 Tejal Jain 40000 Delhi Project Manager 2017-11-10 25

15 Mohini Shah 38000 Pune Software Developer 2019-03-05 20

Example 1:

Write a query to retrieve all those records of an employee where employee salary is greater
than 50000.

Query:

mysql> 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.

You will get the following output:

As per the expected output, only those records are displayed where an employee's salary is
greater than 50000. There are six records in the employee's table which satisfy the given
condition.
Example 2:

Write a query to update the employee's record and set the updated name as 'Harshada Sharma'
where the employee's city name is Jaipur.

Query:

mysql> UPDATE employees SET Name = "Harshada Sharma" WHERE City = "Jaipur";

The above query will update the employee's name to "Harshada Sharma," where the
employee's city is Jaipur.

To verify whether records are updated or not, we will run a select query.

mysql> SELECT * FROM employees;

There is only one record in the employee's table where the employee's city is 'Jaipur'. The id of
the record is 3, which satisfies the given condition. Hence, according to the given condition,
the employee's name with employee id 3 is now changed to 'Harshada Sharma'.
Example 3:

Write a query to delete an employee's record where the employee's joining date is "2013-12-
12".

Query:

mysql> DELETE FROM employees WHERE Date_of_Joining = "2013-12-12";

The above query will delete the employee details of the employee whose joining date is
"2013-12-12".

To verify the results of the above query, we will execute the select query.

mysql> SELECT *FROM employees;

There is only one record in the employee's table where the employee's joining date is '2013-
12-12'. The id of the record is 13, which satisfies the given condition. Therefore according to
the given condition, the employee with employee id 13 is now deleted from the employee's
table.
2. GROUP BY CLAUSE

The Group By clause is used to arrange similar kinds of records into the groups in the
Structured Query Language. The Group by clause in the Structured Query Language is used
with Select Statement. Group by clause is placed after the where clause in the SQL statement.
The Group By clause is specially used with the aggregate function, i.e., max (), min (), avg (),
sum (), count () to group the result based on one or more than one column.

The syntax of Group By clause:

SELECT * FROM TABLENAME GROUP BY COLUMNNAME;

The above syntax will select all the data or records from the table, but it will arrange all those
data or records in the groups based on the column name given in the query.

The syntax of Group By clause with Aggregate Functions:

SELECT COLUMNNAME1, Aggregate_FUNCTION (COLUMNNAME) FROM TABLE

NAME GROUP BY COLUMNNAME;

Let's understand the Group By clause with the help of examples.

Consider the employees table with the following data:

E_ID Name Salary City Designation Date_of_Joining Age

1 Sakshi Kumari 50000 Mumbai Project Manager 2021-06-20 24

2 Tejaswini Naik 75000 Delhi System Engineer 2019-12-24 23

3 Anuja Sharma 40000 Jaipur Manager 2021-08-15 26

4 Anushka Tripathi 90000 Mumbai Software Tester 2021-06-13 24

5 Rucha Jagtap 45000 Bangalore Project Manager 2020-08-09 23

6 Rutuja Deshmukh 60000 Bangalore Manager 2019-07-17 26

7 Swara Baviskar 55000 Jaipur System Engineer 2021-10-10 24

8 Sana Sheik 45000 Pune Software Engineer 2020-09-10 26

9 Swati Kumari 50000 Pune Software Tester 2021-01-01 25

10 Mayuri Patel 60000 Mumbai Project Manager 2020-10-02 24

11 Simran Khanna 45500 Kolhapur HR 2019-01-02 26

12 Shivani Wagh 50500 Delhi Software Developer 2016-09-10 25

13 Kiran Maheshwari 50000 Nashik HR 2013-12-12 23

14 Tejal Jain 40000 Delhi Project Manager 2017-11-10 25

15 Mohini Shah 38000 Pune Software Developer 2019-03-05 20

Example 1:

Write a query to display all the records of the employees table but group the results based on
the age column.

Query:

mysql> SELECT * FROM employees GROUP BY Age;

The above query will display all the records of the employees table but grouped by the age
column.

You will get the following output:

Example 2:

Write a query to display all the records of the employees table grouped by the designation
and salary.

Query:

mysql> SELECT * FROM employees GROUP BY Salary, Designation;

The above query will display all the records of the employees table but grouped by the salary
and designation column.

You will get the following output:

Examples of Group By clause using aggregate functions

Example 1:
Write a query to list the number of employees working on a particular designation and group
the results by designation of the employee.

Query:

mysql> SELECT COUNT (E_ID) AS Number_of_Employees, Designation FROM employ

ees GROUP BY Designation;

The above query will display the designation with the respective number of employees
working on that designation. All these results will be grouped by the designation column.

You will get the following output:

As per the expected output, the designation with the respective employee count is displayed.

Example 2:

Write a query to display the sum of an employee's salary as per the city grouped by an
employee's age.

Query:

mysql> SELECT SUM (Salary) AS Salary, City FROM employees GROUP BY City;

The above query will first calculate the sum of salaries working in each city, and then it will
display the salary sum with the respective salary but grouped by the age column.

You will get the following output:

As per the expected output, the sum of employee salary according to the city to which the
employee belongs to is displayed. If two employees belong to the same city, then they will be
in one group.

3. HAVING CLAUSE:

When we need to place any conditions on the table's column, we use the WHERE clause in
SQL. But if we want to use any condition on a column in Group By clause at that time, we will
use the HAVING clause with the Group By clause for column conditions.

Syntax:

TABLENAME GROUP BY COLUMNNAME HAVING CONDITION;

Consider the employees table with the following data:

E_ID Name Salary City Designation Date_of_Joining Age

1 Sakshi Kumari 50000 Mumbai Project Manager 2021-06-20 24

2 Tejaswini Naik 75000 Delhi System Engineer 2019-12-24 23

3 Anuja Sharma 40000 Jaipur Manager 2021-08-15 26

4 Anushka Tripathi 90000 Mumbai Software Tester 2021-06-13 24

5 Rucha Jagtap 45000 Bangalore Project Manager 2020-08-09 23

6 Rutuja Deshmukh 60000 Bangalore Manager 2019-07-17 26

7 Swara Baviskar 55000 Jaipur System Engineer 2021-10-10 24

8 Sana Sheik 45000 Pune Software Engineer 2020-09-10 26

9 Swati Kumari 50000 Pune Software Tester 2021-01-01 25

10 Mayuri Patel 60000 Mumbai Project Manager 2020-10-02 24

11 Simran Khanna 45500 Kolhapur HR 2019-01-02 26

12 Shivani Wagh 50500 Delhi Software Developer 2016-09-10 25

13 Kiran Maheshwari 50000 Nashik HR 2013-12-12 23

14 Tejal Jain 40000 Delhi Project Manager 2017-11-10 25

15 Mohini Shah 38000 Pune Software Developer 2019-03-05 20

Example 1:

Write a 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.

Query:

mysql> SELECT Name, City, MAX (Salary) AS Salary FROM employees GROUP BY D
esignation HAVING MAX (Salary) > 40000;
You will get the following output:

The above output shows that the employee name, salary, and city of an employee where
employee salary is greater than 40000 grouped by designation. (Employees with a similar
designation are placed in one group, and those with other designation are placed separately).

Example 2:

Write a query to display the name of employees and designation where the sum of an
employee's salary is greater than 45000 and group the results by city.

Query:

mysql> SELECT Name, Designation, SUM (Salary) AS Salary FROM employees GROUP
BY City HAVING SUM (Salary) > 45000;

You will get the following output:

The above output shows the employee name, designation, and salary of an employee. The sum
of salary is greater than 45000 grouped by city. (Employees with similar cities are placed in one
group and those with a different city are not similar are placed separately).
4. ORDER BY CLAUSE

Whenever we want to sort anything in SQL, we use the ORDER BY clause. The ORDER BY
clause in SQL will help us to sort the data based on the specific column of a table. This
means that all the data stored in the specific column on which we are executing the ORDER
BY clause will be sorted. The corresponding column values will be displayed in the sequence
in which we have obtained the values in the earlier step.

As we all know, sorting means either in ASCENDING ORDER or DESCENDING ORDER.

In the same way, ORDER BY CLAUSE sorts the data in ascending or descending order as per
our requirement. The data will be sorted in ascending order whenever the ASC keyword is
used with ORDER by clause, and the DESC keyword will sort the records in descending order.

By default, sorting in the SQL will be done using the ORDER BY clause in ASCENDING
order if we didn't mention the sorting order.

Before moving towards the example of the ORDER BY clause to sort the records, first, we will
look at syntax so it will be easy for us to go through the example.

Syntax of ORDER BY clause without asc and desc keyword:

SELECT COLUMN_NAME1, COLUMN_NAME2 FROM TABLE_NAME ORDER BY

COLUMNAME;

Syntax of ORDER BY clause to sort in ascending order:

SELECT COLUMN_NAME1, COLUMN_NAME2 FROM TABLE_NAME ORDER BY

COLUMN_NAME ASC;

Syntax of ORDER BY clause to sort in descending order:

SELECT COLUMN_NAME1, COLUMN_NAME2 FROM TABLE_NAME ORDER BY

COLUMN_NAME DESC;

Consider we have an employees table with the following data:

E_ID Name Salary City Designation Date_of_Joining Age

1 Sakshi Kumari 50000 Mumbai Project Manager 2021-06-20 24

2 Tejaswini Naik 75000 Delhi System Engineer 2019-12-24 23

3 Anuja Sharma 40000 Jaipur Manager 2021-08-15 26

4 Anushka Tripathi 90000 Mumbai Software Tester 2021-06-13 24

5 Rucha Jagtap 45000 Bangalore Project Manager 2020-08-09 23

6 Rutuja Deshmukh 60000 Bangalore Manager 2019-07-17 26

7 Swara Baviskar 55000 Jaipur System Engineer 2021-10-10 24

8 Sana Sheik 45000 Pune Software Engineer 2020-09-10 26

9 Swati Kumari 50000 Pune Software Tester 2021-01-01 25

10 Mayuri Patel 60000 Mumbai Project Manager 2020-10-02 24

11 Simran Khanna 45500 Kolhapur HR 2019-01-02 26

12 Shivani Wagh 50500 Delhi Software Developer 2016-09-10 25

13 Kiran Maheshwari 50000 Nashik HR 2013-12-12 23

14 Tejal Jain 40000 Delhi Project Manager 2017-11-10 25

15 Mohini Shah 38000 Pune Software Developer 2019-03-05 20

Example 1:
Write a query to sort the records in the ascending order of the employee designation from the
employees table.

Query:

mysql> SELECT * FROM employees ORDER BY Designation;

Here in a SELECT query, an ORDER BY clause is applied on the column 'Designation' to sort
the records, but we didn't use the ASC keyword after the ORDER BY clause to sort in
ascending order. So, by default, data will be sorted in ascending order if we don't specify asc
keyword.

You will get the following output:

As per the expected output, the records are displayed in ascending order of the employee's
designation.

Example 2:

Write a query to display employee name and salary in the ascending order of the employee's
salary from the employees table.

Query:

mysql> SELECT Name, Salary FROM employees ORDER BY Salary ASC;

Here in a SELECT query, an ORDER BY clause is applied to the 'Salary' column to sort the
records. We have used the ASC keyword to sort the employee's salary in ascending order.

You will get the following output:

All the records are displayed in the ascending order of the employee's salary.

Example 3:

Write a query to sort the data in descending order of the employee name stored in the employees
table.

Query:

mysql> SELECT * FROM employees ORDER BY Name DESC;

Here we have used the ORDER BY clause with the SELECT query applied on the Name
column to sort the data. We have used the DESC keyword after the ORDER BY clause to
sort data in descending order.
You will get the following output:

All the records are displayed in descending order of the employee name.

[Link]

Application of DCL commands

DCL commands are used to manage database security and access control. The two primary
DCL commands are:

 GRANT: Used to grant specific privileges to database users or roles.

 REVOKE: Used to revoke previously granted privileges.
Common DCL Commands

GRANT: The GRANT command is used to grant specific privileges to database users or
roles:

Syntax
GRANT [privilege_name] ON [object_name] TO [user_name];
or
GRANT privilege_name ON object_name TO user_name WITH GRANT OPTION;

GRANT SELECT, INSERT ON Employees TO HR_Manager;

This grants the “HR_Manager” role the privileges to select and insert data into the
“Employees” table.

REVOKE: The REVOKE command is used to revoke previously granted privileges:

Syntax
REVOKE [privilege_name] ON [object_name] FROM [user_name];
REVOKE DELETE ON Customers FROM Sales_Team;

This revokes the privilege to delete data from the “Customers” table from the “Sales_Team”
role.

Application of TCL commands

What is TCL?

TCL, or Transaction Control Language, is a subset of SQL used to manage database

transactions. TCL commands ensure data integrity by allowing you to control when changes
to the database are saved permanently or rolled back.

Common TCL Commands in SQL

COMMIT

The COMMIT command is used to save changes made during a transaction to the database
permanently:

BEGIN;

-- SQL statements

COMMIT;

This example begins a transaction, performs SQL statements, and then commits the changes
to the database.
ROLLBACK

The ROLLBACK command is used to undo changes made during a transaction:

BEGIN;

-- SQL statements

ROLLBACK;

This example begins a transaction, performs SQL statements, and then rolls back the changes,
restoring the database to its previous state.

SAVEPOINT

The SAVEPOINT command allows you to set a point within a transaction to which you can
later roll back:

BEGIN;

-- SQL statements

SAVEPOINT my_savepoint;

-- More SQL statements

ROLLBACK TO my_savepoint;

This example creates a savepoint and later rolls back to that point, undoing some of the
transaction’s changes.

TCL Commands in SQL with Examples

Here are code snippets and their corresponding outputs for TCL commands:
 SQL
Code Snippet Output
Command

Changes made in the transaction

COMMIT BEGIN; -- SQL statements COMMIT;
saved permanently.

Changes made in the transaction

ROLLBACK BEGIN; -- SQL statements ROLLBACK;
rolled back.

BEGIN; -- SQL statements SAVEPOINT Savepoint created and later used

SAVEPOINT my_savepoint; -- More SQL statements to roll back to a specific point in
ROLLBACK TO my_savepoint; the transaction.

These examples provide code snippets and their corresponding real-value outputs in a tabular
format for each type of SQL command.

Learning outcome 4: Implement Database security