DATABASE SYSTEM NOTES DIT (PART-II)

CHAPTER 1
Introduction to Databases
A database is an electronically stored, systematic collection of data that can
include words, numbers, images, videos, and other types of files. Databases are
managed using specialized software called a Database Management
System (DBMS), which allows users to store, retrieve, and manipulate data
efficiently. Databases are the backbone of modern applications, supporting
businesses, organizations, and systems across industries.
Key Features of a Database
 Organized Data Storage: Data is stored in structured formats, such as
tables, documents, or key-value pairs.
 Efficient Access: Advanced search and query capabilities allow for quick
data retrieval.
 Security and Scalability: Databases provide robust security measures and
can scale with growing data needs.

Advantages of DBMS
The advantages of the DBMS are explained below −
 Redundancy problem can be solved.
In the File System, duplicate data is created in many places because all the
programs have their own files which create data redundancy resulting in
wastage of memory. In DBMS, all the files are integrated in a single database.
So there is no chance of duplicate data.
For example: A student record in a library or examination can contain duplicate
values, but when they are converted into a single database, all the duplicate
values are removed.
 Has a very high security level.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Data security level is high by protecting your precious data from unauthorized
access. Only authorized users should have the grant to access the database
with the help of credentials.
 Presence of Data integrity.
Data integrity makes unification of so many files into a single file. DBMS allows
data integrity which makes it easy to decrease data duplicity Data integration
and reduces redundancy as well as data inconsistency.
 Support multiple users.
DBMS allows multiple users to access the same database at a time without any
conflicts.
 Avoidance of inconsistency.
DBMS controls data redundancy and also controls data consistency. Data
consistency is nothing but if you want to update data in any files then all the
files should not be updated again.
In DBMS, data is stored in a single database so data becomes more consistent
in comparison to file processing systems.
 Shared data
Data can be shared between authorized users of the database in DBMS. All the
users have their own right to access the database. Admin has complete access
to the database. He has a right to assign users to access the database.
 Enforcement of standards
As DBMS have central control of the database. So, a DBA can ensure that all
the applications follow some standards such as format of data, document
standards etc. These standards help in data migrations or in interchanging the
data.
 Any unauthorized access is restricted
Unauthorized persons are not allowed to access the database because of
security credentials.
 Provide backup of data
Compiled by Sir Touqeer National Academy Abbottabad
MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Data loss is a big problem for all the organizations. In the file system users have
to back up the files in regular intervals which lead to waste of time and
resources.
DBMS solves this problem of taking backup automatically and recovery of the
database.
Tunability
Tuning means adjusting something to get a better performance. Same in the
case of DBMS, as it provides tunability to improve performance. DBA adjusts
databases to get effective results.

Disadvantages of DBMS
The disadvantages of DBMS are as follows:
 Complexity
The provision of the functionality that is expected of a good DBMS makes the
DBMS an extremely complex piece of software. Database designers,
developers, database administrators and end-users must understand this
functionality to take full advantage of it.
Failure to understand the system can lead to bad design decisions, which leads
to a serious consequence for an organization.
 Size
The functionality of DBMS makes use of a large piece of software which
occupies megabytes of disk space.
 Performance
Performance may not run as fast as desired.
 Higher impact of a failure
The centralization of resources increases the vulnerability of the system
because all users and applications rely on the availability of DBMS, the failure
of any component can bring operation to halt.
 Cost of DBMS

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

The cost of DBMS varies significantly depending on the environment and

functionality provided. There is also the recurrent annual maintenance cost.
Examples of Database Applications
 Banking: Manage customer accounts, transactions, and loan records.
 Retail: Monitor sales, inventory, and customer preferences.
 Healthcare: Store patient records, medical histories, and prescriptions.
 Education: Maintain student data, attendance, and grades.
Components of a Database
Databases consist of several critical components that work together
to store, organize, and retrieve data effectively. Here’s a detailed explanation of
each component:
1. Data
Data is the core component of any database, representing the actual information
stored. It can include numbers, text, images, videos, or documents, depending on
the database’s purpose. For instance, a customer database might store customer
names, addresses, and purchase histories
2. Schema
The schema is the blueprint or structure of the database. It defines how data is
organized and includes details like tables, columns, data types, and relationships
between entities. For example, a table in a customer database might have
columns like CustomerID, Name, and Email. The schema ensures consistency and
helps users understand how the database is designed.
3. DBMS
The DBMS is the software layer that enables interaction with the database. It
manages the storage, retrieval, and manipulation of data while ensuring security
and data integrity. Examples of DBMS software include MySQL, Oracle,
and MongoDB. The DBMS also handles tasks like backup, recovery, and query
optimization to maintain the database’s performance.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

4. Queries
Queries are commands used to interact with the database, allowing users to
retrieve, manipulate, or update data. For relational databases, SQL (Structured
Query Language) is commonly used. For instance, a query like SELECT * FROM
Customers WHERE Country = 'USA'; retrieves all customers from the USA. Queries
are vital for extracting actionable insights and managing data effectively.
5. Users
Users are individuals or applications that interact with the database. They can
have different levels of access based on their roles, such
as administrators, developers, or end-users. For example, a database
administrator might have full control, including the ability to create or delete
tables, while a regular user might only have permission to view specific data.

Types of Database Model

A Database model defines the logical design and structure of a database. It
defines how data will be stored, accessed, and updated in a database
management system.
 As per your application's requirement, we can use a database model to
define your database.
 The database model sets the rule, relationships, constraints, etc. to define
how data is stored in the database.
 It's like creating a blueprint of your Database.
 There are different types of Database models and each one has its own set
of features.
 You can define how you want to structure the application data using a
database model.

1. Hierarchical Model
 The hierarchical database model organizes data into a tree-like structure,
with a single root, to which all the other data is linked.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

 The hierarchy starts from the Root data, and expands like a tree,
adding child nodes to the parent nodes.
 In this model, a child node will only have a single parent node.
 This model efficiently describes many real-world relationships like the index
of a book, etc.
 IBM's Information Management System (IMS) is based on this model.
 Data is organized into a tree-like structure with a one-to-many
relationship between two different types of data, for example,
one department can have many courses, many teachers, and of course
many students (like shown in the diagram below).

2. Network Model
 The Network Model is an extension of the Hierarchical model.
 In this model, data is organized more like a graph, and allowed to
have more than one parent node.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

 In the network database model, data is more related as more relationships

are established in this database model.
 Also, as the data is more related, hence accessing the data is also
easier and fast.
 This database model uses many-to-many data relationships.
 Integrated Data Store (IDS) is based on this database model.

 This was the most widely used database model before Relational Model was introduced.

 The implementation of the Network model is complex, and it's very difficult to maintain it.

 The Network model is difficult to modify also.

 You may want to explore this if you are developing some social networking applications,
although the Graph Database model is new and is far better than the Network Database model.

3. Relational Model
 In this model, data is organized in two-dimensional tables and the
relationship is maintained by storing a common field.
 This model was introduced by E.F Codd in 1970, and since then it has been
the most widely used database model.
Compiled by Sir Touqeer National Academy Abbottabad
MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

 The basic structure of data in the relational model is tables. All the
information related to a particular type is stored in rows of that table.
 Hence, tables are also known as relations in the relational model.
 You can design tables, normalize them to reduce data redundancy,
and use Structured Query language or SQL to access data from the tables.
 Some of the most popular databases are based on this database model. For
example, Oracle, MySQL, etc.

What is a DBMS?
A DBMS is a system that allows users to create, modify, and query databases while ensuring data
integrity, security, and efficient data access. Unlike traditional file systems, DBMS minimizes data
redundancy, prevents inconsistencies, and simplifies data management with features like concurrent
access and backup mechanisms. It organizes data into tables, views, schemas, and reports, providing a
structured approach to data management.

Example:
A university database can store and manage student information, faculty records, and administrative
data, allowing seamless retrieval, insertion, and deletion of information as required.

What is a relational database?

A relational database is a collection of information that organizes data in predefined relationships where
data is stored in one or more tables (or "relations") of columns and rows, making it easy to see and

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

understand how different data structures relate to each other. Relationships are a logical connection
between different tables, established on the basis of interaction among these tables.

Non-Relational Database
A non-relational database is a type of database that does not rely on the traditional tabular structure of
rows and columns found in relational databases. Instead, it uses flexible data models such as key-value
pairs, documents, graphs, and wide-column stores.

This flexibility allows non-relational databases to manage unstructured, semi-structured, and structured
data efficiently. They were designed when data was expected to be partitioned across multiple
machines to scale, in contrast to relational databases, which assumed the data would stay on a single
machine.

Difference Between DBMS and RDBMS

DBMS RDBMS

DBMS stores data as file. RDBMS stores data in tabular form.

Data elements need to access

Multiple data elements can be accessed at the same time.
individually.

No relationship between data. Data is stored in the form of tables which are related to each other.

DBMS does not support

RDBMS supports distributed database.
distributed database.

It stores data in either a

It uses a tabular structure where the headers are the column
navigational or hierarchical
names, and the rows contain corresponding values.
form.

It deals with small quantity of

It deals with large amount of data.
data.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

DBMS RDBMS

Data redundancy is common

Keys and indexes do not allow Data redundancy.
in this model.

It is used for small

organization and deal with It is used to handle large amount of data.
small data.

Security is less More security measures provided.

It supports single user. It supports multiple users.

Examples: XML, Window

Examples: MySQL, PostgreSQL, SQL Server, Oracle, Microsoft
Registry, Forxpro, dbaseIIIplus
Access etc.
etc.

FUNCTIONS OF A DBMS:
1. Data Definition:
 Defining Database Structure:

DBMS enables users to define the structure of the database, including tables, fields, data types, and
constraints.

 Creating and Modifying Schemas:

It allows for the creation and modification of database schemas, which are the blueprints for organizing
data.

 Data Dictionary Management:

DBMS maintains a catalog or dictionary that stores metadata (data about data), such as data types,
constraints, and access rights.

2. Data Manipulation:
 Inserting, Updating, and Deleting Data: DBMS provides functions to insert new data, update
existing data, and delete data from the database.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

 Data Retrieval: It enables users to retrieve specific data based on queries or criteria.

 Data Transformation: DBMS can handle data transformation and presentation, ensuring data is
in the correct format for users.

3. Data Security and Access Control:

 User Authentication and Authorization:

DBMS provides mechanisms to authenticate users and grant them appropriate access rights to the
database.

 Data Encryption and Security:

It can encrypt sensitive data and implement security measures to protect data from unauthorized
access.

 Backup and Recovery:

DBMS provides features for backing up data and recovering it in case of system failures or data loss.

4. Data Integrity:
 Data Validation:
DBMS can enforce data integrity constraints to ensure data accuracy and consistency.

 Transaction Management:
It manages transactions to ensure that data changes are atomic, consistent, isolated, and durable (ACID
properties).

 Concurrency Control:
DBMS manages concurrent access to the database by multiple users, preventing conflicts and ensuring
data consistency.

5. Other Important Functions:

 Database Communication: DBMS facilitates communication between the database and
applications or other systems.

 Application Programming Interfaces (APIs): It provides APIs that allow developers to

interact with the database from their applications.

 Query Optimization: DBMS optimizes queries to ensure efficient data retrieval.

 Reporting and Analysis: DBMS can be used to generate reports and perform data analysis.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

What is the Relational Model?

The relational model represents how data is stored in Relational Databases.

A relational database consists of a collection of tables each of which is assigned a unique name.

Consider a relation STUDENT with attributes ROLL_NO, NAME, ADDRESS, PHONE, and AGE shown in
the table.

Table STUDENT

Key Terms

 Attribute: Attributes are the properties that define an entity. e.g. ROLL_NO, NAME, ADDRESS.

 Data: Facts, figures, statistics etc. having no particular meaning (e.g. 1, ABC, 19 etc).

 Relation Schema: A relation schema defines the structure of the relation and represents the
name of the relation with its attributes. e.g. STUDENT (ROLL_NO, NAME, ADDRESS, PHONE, and

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

AGE) is the relation schema for STUDENT. If a schema has more than 1 relation it is called
Relational Schema.

 Tuple: Each row in the relation is known as a tuple. The above relation contains 4 tuples one of
which is shown as:

1 RAM DELHI 9455123451 18

 Relation Instance: The set of tuples of a relation at a particular instance of time is called
a relation instance. It can change whenever there is an insertion, deletion or update in the
database.

 Degree: The number of attributes in the relation is known as the degree of the relation. The
STUDENT relation defined above has degree 5.

 Cardinality: The number of tuples in a relation is known as cardinality. The STUDENT relation
defined above has cardinality 4.

 Column: The column represents the set of values for a particular attribute. The column ROLL_NO
is extracted from the relation STUDENT.

 NULL Values: The value which is not known or unavailable is called a NULL value. It is
represented by NULL. e.g. PHONE of STUDENT having ROLL_NO 4 is NULL.

 Relation Key: These are basically the keys that are used to identify the rows uniquely or also help
in identifying tables. These are of the following types:

o Primary Key

o Candidate Key

o Super Key

o Foreign Key

o Alternate Key

o Composite Key

Types of Keys in Relational Model (Candidate, Super, Primary, Alternate and Foreign)

Keys are one of the basic requirements of a relational database model. It is widely used to identify the
tuples(rows) uniquely in the table. We also use keys to set up relations amongst various columns and
tables of a relational database.

Why do we require Keys in a DBMS?

We require keys in a DBMS to ensure that data is organized, accurate, and easily accessible. Keys help to
uniquely identify records in a table, which prevents duplication and ensures data integrity.
Keys also establish relationships between different tables, allowing for efficient querying and

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

management of data. Without keys, it would be difficult to retrieve or update specific records, and the
database could become inconsistent or unreliable.

Different Types of Database Keys

Super Key

The set of one or more attributes (columns) that can uniquely identify a tuple (record) is known as Super
Key. For Example, STUD_NO, (STUD_NO, STUD_NAME), etc.

 A super key is a group of single or multiple keys that uniquely identifies rows in a table. It
supports NULL values in rows.

 A super key can contain extra attributes that aren’t necessary for uniqueness. For example, if the
“STUD_NO” column can uniquely identify a student, adding “SNAME” to it will still form a valid
super key, though it’s unnecessary.

Example:

Table STUDENT

STUD_NO SNAME ADDRESS PHONE

1 Shyam Delhi 123456789

2 Rakesh Kolkata 223365796

3 Suraj Delhi 175468965

Consider the table shown above.

STUD_NO+PHONE is a super key.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Relation between Primary Key, Candidate Key, and Super Key

Candidate Key

The minimal set of attributes that can uniquely identify a tuple is known as a candidate key. For Example,
STUD_NO in STUDENT relation.

 A candidate key is a minimal super key, meaning it can uniquely identify a record but contains no
extra attributes.

 It is a super key with no repeated data is called a candidate key.

 The minimal set of attributes that can uniquely identify a record.

 A candidate key must contain unique values, ensuring that no two rows have the same value in
the candidate key’s columns.

 Every table must have at least a single candidate key.

 A table can have multiple candidate keys but only one primary key.

Example:

STUD_NO is the candidate key for relation STUDENT.

Table STUDENT

STUD_NO SNAME ADDRESS PHONE

1 Shyam Delhi 123456789

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

STUD_NO SNAME ADDRESS PHONE

2 Rakesh Kolkata 223365796

3 Suraj Delhi 175468965

 The candidate key can be simple (having only one attribute) or composite as well.

Example:

{STUD_NO, COURSE_NO} is a composite

candidate key for relation STUDENT_COURSE.

Table STUDENT_COURSE

TEACHER_N
STUD_NO O COURSE_NO

1 001 C001

2 056 C005

Note: In SQL Server a unique constraint that has a nullable column, allows the value ‘ null ‘ in that
column only once . That’s why the STUD_PHONE attribute is a candidate here, but cannot be a ‘null’
value in the primary key attribute.

Primary Key

There can be more than one candidate key in relation out of which one can be chosen as the primary
key. For Example, STUD_NO, as well as STUD_PHONE, are candidate keys for relation STUDENT but
STUD_NO can be chosen as the primary key (only one out of many candidate keys).

 A primary key is a unique key, meaning it can uniquely identify each record (tuple) in a table.

 It must have unique values and cannot contain any duplicate values.

 A primary key cannot be NULL, as it needs to provide a valid, unique identifier for every record.

 A primary key does not have to consist of a single column. In some cases, a composite primary
key (made of multiple columns) can be used to uniquely identify records in a table.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

 Databases typically store rows ordered in memory according to primary key for fast access of
records using primary key.

Example:

STUDENT table -> Student(STUD_NO, SNAME, ADDRESS, PHONE) , STUD_NO is a primary key

Table STUDENT

STUD_NO SNAME ADDRESS PHONE

1 Shyam Delhi 123456789

2 Rakesh Kolkata 223365796

3 Suraj Delhi 175468965

Alternate Key

An alternate key is any candidate key in a table that is not chosen as the primary key. In other words, all
the keys that are not selected as the primary key are considered alternate keys.

 An alternate key is also referred to as a secondary key because it can uniquely identify records in
a table, just like the primary key.

 An alternate key can consist of one or more columns (fields) that can uniquely identify a record,
but it is not the primary key

 Eg:- SNAME, and ADDRESS is Alternate keys

Example:

Consider the table shown above.

STUD_NO, as well as PHONE both,
are candidate keys for relation STUDENT but
PHONE will be an alternate key
(only one out of many candidate keys).

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Primary Key, Candidate Key, and Alternate Key

Foreign Key

A foreign key is an attribute in one table that refers to the primary key in another table. The table that
contains the foreign key is called the referencing table, and the table that is referenced is called
the referenced table.

 A foreign key in one table points to the primary key in another table, establishing a relationship
between them.

 It helps connect two or more tables, enabling you to create relationships between them. This is
essential for maintaining data integrity and preventing data redundancy.

 They act as a cross-reference between the tables.

 For example, DNO is a primary key in the DEPT table and a non-key in EMP

Example:

Refer Table STUDENT shown above.

STUD_NO in STUDENT_COURSE is a
foreign key to STUD_NO in STUDENT relation.

Table STUDENT_COURSE

TEACHER_N
STUD_NO O COURSE_NO

1 005 C001

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

TEACHER_N
STUD_NO O COURSE_NO

2 056 C005

It may be worth noting that, unlike the Primary Key of any given relation, Foreign Key can be NULL as
well as may contain duplicate tuples i.e. it need not follow uniqueness constraint. For Example,
STUD_NO in the STUDENT_COURSE relation is not unique. It has been repeated for the first and third
tuples. However, the STUD_NO in STUDENT relation is a primary key and it needs to be always unique,
and it cannot be null.

Relation between Primary Key and Foreign Key

Composite Key

Sometimes, a table might not have a single column/attribute that uniquely identifies all the records of a
table. To uniquely identify rows of a table, a combination of two or more columns/attributes can be
used. It still can give duplicate values in rare cases. So, we need to find the optimal set of attributes that
can uniquely identify rows in a table.

 It acts as a primary key if there is no primary key in a table

 Two or more attributes are used together to make a composite key .

 Different combinations of attributes may give different accuracy in terms of identifying the rows
uniquely.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Example:

FULLNAME + DOB can be combined

together to access the details of a student.

Different Types of Keys

Referential integrity constraints

Referential integrity constraints are rules that ensure relationships between tables remain consistent.
They enforce that a foreign key in one table must either match a value in the referenced primary key of
another table or be NULL. This guarantees the logical connection between related tables in a relational
database.

Why Referential Integrity Constraints Are Important?

 Maintains Consistency: Ensures relationships between tables are valid.

 Prevents Orphan Records: Avoids cases where a record in a child table references a non-existent
parent record.

 Enforces Logical Relationships: Strengthens the logical structure of a relational database.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Example:

Here, in below example Block_No 22 entry is not allowed because it is not present in 2nd table.

Student_id Name Semester Block_No

22CSE101 Ramesh 5th 20

21CSE105 Kamlesh 6th 21

22CSE102 Aakash 5th 20

23CSE106 Mukesh 2nd 22

Block_No Block Location

20 Chandigarh

21 Punjab

25 Delhi

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Relationship in DBMS
Any association between two entity types is called a relationship. Entities take part in the relationship. It
is represented by a diamond shape.

For example, A teacher teaches students. Here, " teaches " is a relationship and this is the relationship
between a teacher’s entity and a student entity.

We have two entity types of 'Customers' (Customer_id, Name, City, Phone) and 'Account'(Account_no,
Type, Balance). We store the data of 'Customer' in one table and his accounts details in the 'Account'
table. Now, to link these two tables we need to insert the primary key 'Customer_id' of the 'Customer'
table in the 'Account' table. This key acts as a foreign key for the 'Account' table and refers to a column
with the same name in the 'Customer' table. This is how a relationship between two tables is
established. There are three types of relationships that can exist between two entities.

 One-to-One Relationship

 One-to-Many or Many-to-One Relationship

 Many-to-Many Relationship

One-to-One Relationship
Such a relationship exists when each record of one table is related to only one record of the other table.

For example, If there are two entities ‘Person’ (Id, Name, Age, Address) and ‘Passport’ (Passport_id,
Passport_no). So, each person can have only one passport and each passport belongs to only one
person.

Such a relationship is not very common. However, such a relationship is used for security purposes. In
the above example, we can easily store the passport id in the ‘Person’ table only. But, we make another
table for the ‘Passport’ because Passport number may be sensitive data and it should be hidden from
certain users. So, by making a separate table we provide extra security that only certain database users
can see it.

One-to-Many or Many-to-One Relationship

Such a relationship exists when each record of one table can be related to one or more than one record
of the other table. This relationship is the most common relationship found. A one-to-many relationship
can also be said as a many-to-one relationship depending upon the way we view it.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

For example, If there are two entity type ‘Customer’ and ‘Account’ then each ‘Customer’ can have more
than one ‘Account’ but each ‘Account’ is held by only one ‘Customer’. In this example, we can say that
each Customer is associated with many Accounts. So, it is a one-to-many relationship. But, if we see it
the other way i.e. many Accounts is associated with one Customer then we can say that it is a many-to-
one relationship.

Many-to-Many Relationship
Such a relationship exists when each record of the first table can be related to one or more than one
record of the second table and a single record of the second table can be related to one or more than
one record of the first table. A many-to-many relationship can be seen as a two one-to-many relationship
which is linked by a 'linking table' or 'associate table'. The linking table links two tables by having fields
which are the primary key of the other two tables. We can understand this with the following example.

Example: If there are two entity type ‘Customer’ and ‘Product’ then each customer can buy more than
one product and a product can be bought by many different customers.

Now, to understand the concept of the linking table here, we can have the ‘Order’ entity as a linking
table which links the ‘Customer’ and ‘Product’ entity. We can break this many-to-many relationship in
two one-to-many relationships. First, each ‘Customer’ can have many ‘Orders’ whereas each ‘Order’ is
related to only one ‘Customer’. Second, each ‘Order’ is related only one Product where’s there can many
orders for the same Product.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

In the above concept of linking can be understood with the help of taking into consideration all the
attributes of the entities 'Customer', 'Order' and 'Product'. We can see that the primary key of both
'Customer' and 'Product' entity is included in the linking table i.e 'Order' table. These key act as foreign
keys while referring to the respective table from the 'Order' table.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Data Models in DBMS

A Data Model in Database Management System (DBMS) is the concept of tools that are developed to
summarize the description of the database. Data Models provide us with a transparent picture of data
which helps us in creating an actual database. It shows us from the design of the data to its proper
implementation of data.

Data Models handle Database management systems (DBMS) since they determine the structure,
storage, and manipulation of data. They act as structural blueprints, assuring that data is arranged
consistently and effectively.

There are three main types of Data Models.

Types of Relational Models

1. Conceptual Data Model

2. Representational Data Model

3. Physical Data Model

It is basically classified into 3 types:-

Data Models

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Why Use Data Models?

 Any firm can benefit greatly from data models since they enable business users to explicitly
specify essential business rules.

 Fewer changes are therefore needed when implementing the plan.

 Additionally, it results in a large reduction in development time, which enables companies to

launch new ventures and initiatives more quickly.

Let's see these Data models in DBMS with examples one by one,

1. Conceptual data model

 The conceptual data model in dbms helps understand the database's needs and requirements
by providing a high-level description of the database.

 This approach is used during the requirement-gathering phase, which is before the Database
Designers begin creating a specific database.

 The entity/relationship model (ER model) is one such common model.

 Entities, relationships, and even properties that database designers utilize are the focus of the
E/R model.

 This model helps anyone, including non-computer science(non-technical) users and

stakeholders, understand it easily.

Entity-Relationship Model ( ER Model)

 This model is built to specify the data and their relationships.

 It is essentially a conceptual design for any database that simplifies creating a data view.

Elements of the ER Model

 Entities: Real-world objects are referred to as entities. It could be a class, name, location, or
item. In an ER Diagram, these are shown by a rectangle.

 Attributes: An entity's description is referred to as an attribute. In an ER Diagram, these are

represented by an ellipse. For a student, it could be their age, roll number, or marks.

 Relationship: Relationships are what establish the connections between various entities.

Key Characteristics of a Conceptual Data Model

 It provides an overview of the business ideas for the entire organization.

 Data models of this kind are created with a corporate audience in mind.

 The conceptual model is created without regard to software or hardware requirements, such as
DBMS vendor and technology, or data storage capacity or location.

 Representing data as a user would see it in the "real world" is the main goal.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

The Conceptual data models also called Domain models establish fundamental concepts and scope
that all stakeholders can use to develop a shared vocabulary.

2. Representational Data Model

 This kind of data model does not describe the physical structure of the database.

 Rather, it is used to represent only the logical portion of the database.

 The representational data model specifically looks into the database's design.

 A relational model is a common type of representational model.

 Relational Calculus and Relational Algebra make up the Relational Model.

 Tables are essentially used in the Relational Model to represent our data and the relationships
among them.

 It is a theoretical idea that is put into practice using a physical data model.

 One benefit of using a representational data model is that it offers a framework upon which the
physical model may be built.

3. Physical Data Model

 The Relational Data Model is accurately executed with the Physical Data Model in DBMS.

 In the end, every piece of data within a database is physically kept on discs and tapes or other
secondary storage devices.

 Files, records, and a few more types of data structures are used to store this.

 It contains all the details on the file formats, database structures, external data structures, and
how they relate to one another.

 In this case, tables are essentially saved in memory for effective access.

 Better work on the relational model is necessary before we can develop a good physical model.

 Relational algebra is realistically implemented using Structured Query Language (SQL).

Key Characteristics of a Physical Data Model

 It is designed for a particular DBMS version, site, data storage capacity, or project-specific
technology.

 Its Columns contain default values, given lengths, and precise datatypes.

 views, authorizations, access profiles, indexes, primary and foreign keys, etc are defined in it.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Importance of Data Modeling

In order to organize and structure data and provide database design clarity, data modelling is
essential. It acts as a common language, promoting efficient stakeholder communication. It
directs the best database architecture for effective data storage and retrieval through visual
representation.

 Visualizes complex data structures, providing a clear roadmap for understanding relationships.

 Acts as a universal language, fostering effective communication between business and technical
stakeholders.

 Creates organized databases by defining entities, properties, and relationships.

 Enhances data quality and integrity by reducing anomalies and redundancy

through normalization.

 Minimizes errors in database and application development.

 Ensures consistency in documentation and system designs across the organization.

 Improves database and application performance.

 Facilitates quick correlation of data across the company.

 Strengthens communication between business intelligence and development teams.

Introduction of ER Model

We typically follow the below steps for designing a database for an application.

 Gather the requirements (functional and data) by asking questions to the database users.

 Do a logical or conceptual design of the database. This is where ER model plays a role. It is the
most used graphical representation of the conceptual design of a database.

 Physical Database Design (Like indexing) and external design (like views)

The Entity Relationship Model is a model for identifying entities (like student, car or company) to be
represented in the database and representation of how those entities are related. The ER data model
specifies enterprise schema that represents the overall logical structure of a database graphically.

Why Use ER Diagrams In DBMS?

 ER diagrams represent the E-R model in a database, making them easy to convert into relations
(tables).

 ER diagrams provide the purpose of real-world modeling of objects which makes them intently
useful.

 ER diagrams require no technical knowledge of the underlying DBMS used.

 It gives a standard solution for visualizing the data logically.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Symbols Used in ER Model

ER Model is used to model the logical view of the system from a data perspective which consists of these
symbols:

 Rectangles: Rectangles represent Entities in the ER Model.

 Ellipses: Ellipses represent Attributes in the ER Model.

 Diamond: Diamonds represent Relationships among Entities.

 Lines: Lines represent attributes to entities and entity sets with other relationship types.

 Double Ellipse: Double Ellipses represent Multi-Valued Attributes.

 Double Rectangle: Double Rectangle represents a Weak Entity.

Symbols used in ER Diagram

Components of ER Diagram

ER Model consists of Entities, Attributes, and Relationships among Entities in a Database System.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Components of ER Diagram

What is Entity?

An Entity may be an object with a physical existence – a particular person, car, house, or employee – or it
may be an object with a conceptual existence – a company, a job, or a university course.

What is Entity Set?

An Entity is an object of Entity Type and a set of all entities is called an entity set. For Example, E1 is an
entity having Entity Type Student and the set of all students is called Entity Set. In ER diagram, Entity
Type is represented as:

Entity Set

We can represent the entity set in ER Diagram but can’t represent entity in ER Diagram because entity is
row and column in the relation and ER Diagram is graphical representation of data.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Types of Entity

There are two types of entity:

1. Strong Entity

A Strong Entity is a type of entity that has a key Attribute. Strong Entity does not depend on other Entity
in the Schema. It has a primary key, that helps in identifying it uniquely, and it is represented by a
rectangle. These are called Strong Entity Types.

2. Weak Entity

An Entity type has a key attribute that uniquely identifies each entity in the entity set. But some entity
type exists for which key attributes can’t be defined. These are called Weak Entity types .

For Example, A company may store the information of dependents (Parents, Children, Spouse) of an
Employee. But the dependents can’t exist without the employee. So Dependent will be a Weak Entity
Type and Employee will be Identifying Entity type for Dependent, which means it is Strong Entity Type .

A weak entity type is represented by a Double Rectangle. The participation of weak entity types is always
total. The relationship between the weak entity type and its identifying strong entity type is called
identifying relationship and it is represented by a double diamond.

Strong Entity and Weak Entity

What is Attributes?

Attributes are the properties that define the entity type. For example, Roll_No, Name, DOB, Age,
Address, and Mobile_No are the attributes that define entity type Student. In ER diagram, the attribute is
represented by an oval.

Attribute

Types of Attributes

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

1. Key Attribute

The attribute which uniquely identifies each entity in the entity set is called the key attribute. For
example, Roll_No will be unique for each student. In ER diagram, the key attribute is represented by an
oval with underlying lines.

Key Attribute

2. Composite Attribute

An attribute composed of many other attributes is called a composite attribute. For example, the
Address attribute of the student Entity type consists of Street, City, State, and Country. In ER diagram,
the composite attribute is represented by an oval comprising of ovals.

Composite Attribute

3. Multivalued Attribute

An attribute consisting of more than one value for a given entity. For example, Phone_No (can be more
than one for a given student). In ER diagram, a multivalued attribute is represented by a double oval.

Multivalued Attribute

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

4. Derived Attribute

An attribute that can be derived from other attributes of the entity type is known as a derived attribute.
e.g.; Age (can be derived from DOB). In ER diagram, the derived attribute is represented by a dashed
oval.

Derived Attribute

The Complete Entity Type Student with its Attributes can be represented as:

Entity and Attributes

Relationship Type and Relationship Set

A Relationship Type represents the association between entity types. For example, ‘Enrolled in’ is a
relationship type that exists between entity type Student and Course. In ER diagram, the relationship
type is represented by a diamond and connecting the entities with lines.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Entity-Relationship Set

A set of relationships of the same type is known as a relationship set. The following relationship set
depicts S1 as enrolled in C2, S2 as enrolled in C1, and S3 as registered in C3.

Relationship Set

Degree of a Relationship Set

The number of different entities sets participating in a relationship set is called the degree of a
relationship set.

1. Unary Relationship: When there is only ONE entity set participating in a relation, the relationship is
called a unary relationship. For example, one person is married to only one person.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Unary Relationship

2. Binary Relationship: When there are TWO entities set participating in a relationship, the relationship
is called a binary relationship. For example, a Student is enrolled in a Course.

Binary Relationship

3. Ternary Relationship: When there are three entity sets participating in a relationship, the relationship
is called a ternary relationship.

4. N-ary Relationship: When there are n entities set participating in a relationship, the relationship is
called an n-ary relationship.

What is Cardinality?

The number of times an entity of an entity set participates in a relationship set is known as cardinality.
Cardinality can be of different types:

1. One-to-One: When each entity in each entity set can take part only once in the relationship, the
cardinality is one-to-one. Let us assume that a male can marry one female and a female can marry one
male. So the relationship will be one-to-one.

the total number of tables that can be used in this is 2.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

one to one cardinality

Using Sets, it can be represented as:

Set Representation of One-to-One

2. One-to-Many: In one-to-many mapping as well where each entity can be related to more than one
entity and the total number of tables that can be used in this is 2. Let us assume that one surgeon
department can accommodate many doctors. So, the Cardinality will be 1 to M. It means one
department has many Doctors.

total number of tables that can used is 3.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

one to many cardinality

Using sets, one-to-many cardinality can be represented as:

Set Representation of One-to-Many

3. Many-to-One: When entities in one entity set can take part only once in the relationship set and
entities in other entity sets can take part more than once in the relationship set, cardinality is many to
one. Let us assume that a student can take only one course but one course can be taken by many
students. So, the cardinality will be n to 1. It means that for one course there can be n students but for
one student, there will be only one course.

The total number of tables that can be used in this is 3.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

many to one cardinality

Using Sets, it can be represented as:

Set Representation of Many-to-One

In this case, each student is taking only 1 course but 1 course has been taken by many students.

4. Many-to-Many: When entities in all entity sets can take part more than once in the relationship
cardinality is many to many. Let us assume that a student can take more than one course and one course
can be taken by many students. So, the relationship will be many to many.

the total number of tables that can be used in this is 3.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

many to many cardinality

Using Sets, it can be represented as:

Many-to-Many Set Representation

In this example, student S1 is enrolled in C1 and C3 and Course C3 is enrolled by S1, S3, and S4. So it is
many-to-many relationships.

How to Draw ER Diagram?

 The very first step is Identifying all the Entities, and place them in a Rectangle, and labeling them
accordingly.

 The next step is to identify the relationship between them and place them accordingly using the
Diamond, and make sure that, Relationships are not connected to each other.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

 Attach attributes to the entities properly.

 Remove redundant entities and relationships.

 Add proper colors to highlight the data present in the database

Convert ER Model to Relational Model

ER Model, when conceptualized into diagrams, gives a good overview of entity-relationship, which is
easier to understand. ER diagrams can be mapped to relational schema, that is, it is possible to create
relational schema using ER diagram. We cannot import all the ER constraints into relational model, but
an approximate schema can be generated.

There are several processes and algorithms available to convert ER Diagrams into Relational Schema.
Some of them are automated and some of them are manual. We may focus here on the mapping
diagram contents to relational basics.

ER diagrams mainly comprise of −

 Entity and its attributes

 Relationship, which is association among entities.

Mapping Entity

An entity is a real-world object with some attributes.

Mapping Process (Algorithm)

 Create table for each entity.

 Entity's attributes should become fields of tables with their respective data types.

 Declare primary key.

Mapping Process

 Create tables for all higher-level entities.

 Create tables for lower-level entities.

 Add primary keys of higher-level entities in the table of lower-level entities.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

 In lower-level tables, add all other attributes of lower-level entities.

 Declare primary key of higher-level table and the primary key for lower-level table.

 Declare foreign key constraints.

What is Database Normalization?

Database normalization is a database design principle for organizing data in an organized and consistent
way.

It helps you avoid redundancy and maintain the integrity of the database. It also helps you eliminate
undesirable characteristics associated with insertion, deletion, and updating.

What is the Purpose of Normalization?

The main purpose of database normalization is to avoid complexities, eliminate duplicates, and organize
data in a consistent way. In normalization, the data is divided into several tables linked together with
relationships.

Database administrators are able to achieve these relationships by using primary keys, foreign keys, and
composite keys.

To get it done, a primary key in one table, for example, employee_wages is related to the value from
another table, for instance, employee_data.

N.B.: A primary key is a column that uniquely identifies the rows of data in that table. It’s a unique
identifier such as an employee ID, student ID, voter’s identification number (VIN), and so on.

A foreign key is a field that relates to the primary key in another table.

A composite key is just like a primary key, but instead of having a column, it has multiple columns.

What is 1NF 2NF and 3NF?

1NF, 2NF, and 3NF are the first three types of database normalization. They stand for first normal
form, second normal form, and third normal form, respectively.

There are also 4NF (fourth normal form) and 5NF (fifth normal form). There’s even 6NF (sixth normal
form), but the commonest normal form you’ll see out there is 3NF (third normal form).

All the types of database normalization are cumulative – meaning each one builds on top of those
beneath it. So all the concepts in 1NF also carry over to 2NF, and so on.

The First Normal Form – 1NF

For a table to be in the first normal form, it must meet the following criteria:

 a single cell must not hold more than one value (atomicity)

 there must be a primary key for identification

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

 no duplicated rows or columns

 each column must have only one value for each row in the table

The Second Normal Form – 2NF

The 1NF only eliminates repeating groups, not redundancy. That’s why there is 2NF.

A table is said to be in 2NF if it meets the following criteria:

 it’s already in 1NF

 has no partial dependency. That is, all non-key attributes are fully dependent on a primary key.

i.e, no non-prime attribute is dependent on any proper subset of any candidate key of the table.

First we will understand what are Prime and Non-prime attributes.

Prime attribute − An attribute, which is a part of the candidate key, is known as a prime attribute.

Non-prime attribute − An attribute, which is not a part of the candidate key, is said to be a non-prime
attribute. For example, we have following table which is having employee data.

The Third Normal Form – 3NF

When a table is in 2NF, it eliminates repeating groups and redundancy, but it does not eliminate
transitive partial dependency.

This means a non-prime attribute (an attribute that is not part of the candidate’s key) is dependent on
another non-prime attribute. This is what the third normal form (3NF) eliminates.

So, for a table to be in 3NF, it must:

 be in 2NF

 have no transitive partial dependency.

A transitive functional dependency is when changing a non-key column,

Examples of 1NF, 2NF, and 3NF

Database normalization is quite technical, but we will illustrate each of the normal forms with examples.

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:

state_cod
employee_id name job_code job e home_state

E001 Alice J01 Chef 26 Michigan

E001 Alice J02 Waiter 26 Michigan

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

state_cod
employee_id name job_code job e home_state

E002 Bob J02 Waiter 56 Wyoming

E002 Bob J03 Bartender 56 Wyoming

E003 Alice J01 Chef 56 Wyoming

All the entries are atomic and there is a composite primary key (employee_id, job_code) so the table is
in the first normal form (1NF).

But even if you only know someone's employee_id, then you can determine their name, home_state,
and state_code (because they should be the same person). This means name, home_state,
and state_code are dependent on employee_id (a part of primary composite key). So, the table is not
in 2NF. We should separate them to a different table to make it 2NF.

Example of Second Normal Form (2NF)

employee_roles Table

employee_id job_code

E001 J01

E001 J02

E002 J02

E002 J03

E003 J01

employees Table

employee_i
d name state_code home_state

E001 Alice 26 Michigan

E002 Bob 56 Wyoming

E003 Alice 56 Wyoming

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

employee_i
d name state_code home_state

jobs table

job_code job

J01 Chef

J02 Waiter

J03 Bartender

home_state is now dependent on state_code. So, if you know the state_code, then you can find
the home_state value.

To take this a step further, we should separate them again to a different table to make it 3NF.

Example of Third Normal Form (3NF)

employee_roles Table

employee_id job_code

E001 J01

E001 J02

E002 J02

E002 J03

E003 J01

employees Table

employee_i
d name state_code

E001 Alice 26

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

employee_i
d name state_code

E002 Bob 56

E003 Alice 56

jobs Table

job_code job

J01 Chef

J02 Waiter

J03 Bartender

states Table

state_cod
e home_state

26 Michigan

56 Wyoming

Now our database is in 3NF.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Chapter-4

What is MySQL?

 MySQL is a relational database management system

 MySQL is open-source

 MySQL is free

 MySQL is ideal for both small and large applications

 MySQL is very fast, reliable, scalable, and easy to use

 MySQL is cross-platform

 MySQL is compliant with the ANSI SQL standard

 MySQL was first released in 1995

 MySQL is developed, distributed, and supported by Oracle Corporation

 MySQL is named after co-founder Ulf Michael "Monty" Widenius's daughter: My

Who Uses MySQL?

 Huge websites like Facebook, Twitter, Airbnb, [Link], Uber, GitHub, YouTube, etc.

 Content Management Systems like WordPress, Drupal, Joomla!, Contao, etc.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

 A very large number of web developers around the world

Show Data On Your Web Site

To build a web site that shows data from a database, you will need:

 An RDBMS database program (like MySQL)

 A server-side scripting language, like PHP

 To use SQL to get the data you want

 To use HTML / CSS to style the page

What is SQL?

SQL stands for Structured Query Language, designed specifically for interacting with relational databases.
SQL is a language for storing, manipulating, and retrieving data in relational database management
systems.

You can use the SQL language to modify and create tables in the database, insert new data, modify
existing data, delete data from the tables, and query those tables for specific information.

What is MySQL?

MySQL is a relational database management system.

You use it to store data in tables, and the rows of those tables can relate to each other by common
information in the columns.

The basic idea is that you have one or more "tables" (or "databases") and a set of rows (records) and
columns (fields).

Other elements, like keys and indexes, help you define them further.

MySQL is a database management system used in web applications.

It is available for Linux, Microsoft Windows, Mac OS X, and Solaris and comes with many web hosting
packages. You can download MySQL Server software, pronounced “my sequel,” from the official website.

MySQL advantages and disadvantages

Features of MySQL:
 MySQL is a Relational Database Management System or RDBMS which means that it stores and
presents data in tabular form, organized in rows and columns.

 MySQL is more secure as it consists of a solid data security layer to protect sensitive data from
intruders and passwords in MySQL are encrypted.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

 MySQL is available for free to download and use from the official site of MySQL.

 MySQL is compatible with most operating systems, including Windows, Linux, NetWare, Novell,
Solaris, and other variations of UNIX.

 MySQL provides the facility to run the clients and the server on the same computer or different
computers, via the internet or local network.

 MySQL has a unique storage engine architecture which makes it faster, cheaper, and more
reliable.

 MySQL is simple and easy to use. You can build and interact with MySQL with only a basic
knowledge of MySQL and a few simple SQL statements.

 MySQL has a client-server architecture. There can be any number of clients or application
programs that communicate with the database server (MySQL) to query data, save changes, etc.

 MySQL is scalable and capable of handling more than 50 million rows. This is enough to handle
almost any amount of data. Although the default file size limit is 4GB it can be increased to 8TB.

 MySQL allows transactions to be rolled back.

 MySQL is very flexible as it supports a large number of embedded applications.

Disadvantages of MySQL:
 MySQL is not very efficient in handling very large databases.

 MySQL doesn’t have as good a developing and debugging tool as compared to paid databases.

 MySQL versions less than 5.0 do not support COMMIT, stored procedure, and ROLE.

 MySQL is prone to data corruption as it is inefficient in handling transactions.

 MySQL does not support SQL check constraints.

What is MySQL Workbench?

MySQL Workbench is a unified cross-platform, open-source relational database design tool that adds
functionality and ease to MySQL and SQL development work. MySQL Workbench provides data
modeling, SQL development, and various administration tools for configuration. It also offers a graphical
interface to work with the databases in a structured way.

 You can create a Graphical Model using MySQL Workbench

 MySQL Workbench provides reverse engineering for live databases to models

 MySQL Workbench offers a forward engineering model to a script/live database

MySQL Workbench - Modeling and Designing Tool

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

 MySQL Workbench possesses tools that allow database administrators to virtually create
physical database design models that can be easily transitioned into MySQL databases using
forward engineering.

 MySQL Workbench adheres to all objects such as tables, views, stored procedures, triggers, etc.

 MySQL Workbench also creates models from a target database or even imported SQL files.

MySQL Workbench - SQL Development Tool

 MySQL Workbench comes with a visual SQL editor.

 The Visual SQL editor gives developers the access to build, edit, and run queries against MySQL
server databases. It has utilities for viewing data and exporting it.

 MySQL Workbench has auto-complete and color highlighters that aid in the writing and
debugging of SQL statements, easily.

 Multiple queries can be run at a time, and the result is automatically displayed.

 It also saves the queries in the history panel for previewing and running it later on.

MySQL Workbench - SQL Administration Tool

MySQL Workbench makes user management much easier.

 You can view the account information of all users on the MySQL server

 MySQL Workbench gives access to add and remove users

 MySQL Workbench grants and drop privileges

 You can modify global and database permissions on the MySQL server

 You can change passwords using MySQL

 You can audit to see who did what and when on the server

How to Install MySQL Workbench?

Moving on, you will look at how to install MySQL Workbench on Windows. The installation process is
similar to other operating systems.

1. Open the MySQL website on a browser. Click on the following link: MySQL Downloads.

2. Select the Downloads option.

3. Select MySQL Installer for Windows.

4. Choose the desired installer and click on download.

5. After it downloads the installer, open it.

6. It will ask for permission; when it does, click Yes. The installer will then open. Now, it will ask to
choose the setup type, here, select Custom.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

7. Click on Next. With this, you will be able to install MySQL server, MySQL Workbench, and MySQL
shell.

8. Open MySQL Servers, select the server you want to install, and move it to the Products/Features
to be installed window section. Now, expand Applications, choose MySQL Workbench and
MySQL shell. Move both of them to ‘Products/Features to be installed’.

9. Click on the Next button. Now, click on the Execute button to download and install the MySQL
server, MySQL Workbench, and the MySQL shell.

10. Once the product is ready to configure, click on Next. Under Type and Networking, go with the
default settings and select Next.

11. For authentication, use the recommended strong password encryption.

12. Set your MySQL Root password and click on next.

13. Go for the default windows service settings and under apply configuration, click on execute.
Once the configuration is complete, click on finish.

14. Complete the installation. This will now launch the MySQL Workbench and the MySQL Shell.

Once MySQL Workbench is installed, select the Local instance and enter the password.

Now, you can use the MySQL query tab to write your SQL queries.

How to install MySQL Command Line Client for Windows

Generally, MySQL Command Line Client is installed together with the MySQL Server, and you don’t need
to download and install the MySQL client separately. To check whether you have the client program on
your machine, go to search, switch to the Apps tab, and type “MySQL”

How to use MySQL Command Line Client

So, how to start MySQL Command Line Client? To access MySQL Server from the command-line client,
open the program and enter the password. After that, you will be able to use the client.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

You can also access MySQL Command Line Client from Command Prompt. For this:

1. Open Command Prompt.

2. Navigate to the bin folder. For example: cd C:\Program Files\MySQL\MySQL Server 9.0\bin

3. Run the mysql -u root -p command.

4. Enter the password.

Introduction to SQL

SQL is a standard language for accessing and manipulating databases.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

What is SQL?

 SQL stands for Structured Query Language

 SQL lets you access and manipulate databases

 SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the
International Organization for Standardization (ISO) in 1987

What Can SQL do?

 SQL can execute queries against a database

 SQL can retrieve data from a database

 SQL can insert records in a database

 SQL can update records in a database

 SQL can delete records from a database

 SQL can create new databases

 SQL can create new tables in a database

 SQL can create stored procedures in a database

 SQL can create views in a database

 SQL can set permissions on tables, procedures, and views

SQL Commands: DML and DDL in SQL

DDL stands for Data Definition Language and refers to SQL commands used to create, modify, and delete
database structures such as tables, indexes, and views. DML stands for Data Manipulation Language and
refers to SQL commands used to insert, update, and delete data within a database. Now, let’s learn
about the DDL and DML commands in depth.

What are DDL Commands in SQL?

DDL (Data Definition Language) is a type of SQL command used to define data structures and modify
data. It creates, alters, and deletes database objects such as tables, views, indexes, and users.

Examples of DDL statements include CREATE, ALTER, DROP and TRUNCATE.

Types of DDL Statements

1. CREATE: It is used to create objects in the database, such as tables, views, stored procedures,
and more.

2. ALTER: It is used to modify the structure of an existing database object.

3. DROP: It is used to delete an entire object or part of an object from the database.

4. TRUNCATE: Used to delete all records from a table but does not delete the table structure.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

5. RENAME: Used to rename an existing database object.

DDL Commands in SQL with Examples

CREATE TABLE Employees (

EmployeeID INT,

FirstName VARCHAR(255),

LastName VARCHAR(255),

Department VARCHAR(255)

);

ALTER TABLE Employees

ADD Salary INT;

DROP TABLE Employees;

This code creates a new employee table containing four columns: EmployeeID, FirstName, LastName,
and Department. It then adds an additional column, Salary, to the table. Finally, it drops the Employees
table from the database.

What are DML Commands in SQL?

DML (Data Manipulation Language) is a type of SQL command used to manipulate data in a database. It
inserts, updates, and deletes data from a database table. Examples of DML statements include INSERT,
UPDATE, and DELETE.

Types of DML Statements

1. INSERT: Used to add new records to a database table.

2. UPDATE: Used to modify existing records in a database table.

3. DELETE: Used to delete existing records from a database table.

4. MERGE: Used to combine data from two or more tables into one.

5. SELECT: Used to retrieve data from one or more tables in a database.

6. CALL: Used to call a stored procedure or function.

DML Commands in SQL with Examples

INSERT INTO Employees (EmployeeID, FirstName, LastName, Department)

VALUES (1, 'John', 'Smith', 'IT');

UPDATE Employees

SET Salary = 50000

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

WHERE EmployeeID = 1;

SELECT * FROM Employees;

DELETE FROM Employees

WHERE EmployeeID = 1;

This code inserts a new row into the Employees table with the values of 1, 'John', 'Smith', and 'IT' for the
respective columns. It then updates the Salary column for the row with an EmployeeID of 1 to 50000. It
then selects all of the rows from the Employees table and finally deletes the row with an EmployeeID of
1.

Difference Between DDL and DML Commands in SQL

Explore the DDL vs DML commands in SQL in the below table. Understand how DDL commands shape
database structures, while DML commands manipulate data within the database.

DDL DML

Used to define database objects like tables, indexes,

Used to manipulate data within the database.
views, etc.

Examples of DDL statements include CREATE, ALTER, Examples of DML statements include SELECT,
and DROP. INSERT, UPDATE, and DELETE.

Changes made using DDL affect the structure of the Changes made using DML affect the data stored
database. in the database.

DDL statements are not transactional, meaning they DML statements are transactional, meaning they
cannot be rolled back. can be rolled back if necessary.

DDL statements are usually executed by a database DML statements are executed by application
administrator. developers or end-users.

DDL statements are typically used during the design DML statements are used during normal
and setup phase of a database. operation of a database.

Examples of DDL statements: CREATE TABLE, DROP Examples of DML statements: SELECT, INSERT,
TABLE, ALTER TABLE, CREATE INDEX, etc. UPDATE, DELETE, etc.

DCL Commands in DBMS

Data control language (DCL) is used to access the stored data. It is mainly used for revoke and to grant
the user the required access to a database. In the database, this language does not have the feature of
rollback.
 It is a part of the structured query language (SQL).
 It helps in controlling access to information stored in a database. It complements the data
manipulation language (DML) and the data definition language (DDL).

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

 It is the simplest among three commands.

 It provides the administrators, to remove and set database permissions to desired users as
needed.
 These commands are employed to grant, remove and deny permissions to users for retrieving
and manipulating a database.
DDL Commands
The Data Definition Language (DDL) commands are as follows?
GRANT Command
It is employed to grant a privilege to a user. GRANT command allows specified users to perform specified
tasks
Syntax
GRANT privilege_name on objectname to user;
Here,
 privilege names are SELECT,UPDATE,DELETE,INSERT,ALTER,ALL
 objectname is table name
 user is the name of the user to whom we grant privileges
REVOKE Command
It is employed to remove a privilege from a user. REVOKE helps the owner to cancel previously granted
permissions.
Syntax
REVOKE privilege_name on objectname from user;
Here,
 privilege names are SELECT,UPDATE,DELETE,INSERT,ALTER,ALL
 objectname is table name
 user is the name of the user whose privileges are removing
command
For Location of DB
cd C:\Program Files\MySQL\MySQL Server 8.0\bin
mysql -u root -p
show databases;
create database national;
use national;
CREATE TABLE EMPLOYEE (
-> EmpID int,
-> FirstName varchar(255),
-> LastName varchar(255),
-> EmpAGE INT,
-> EmpZONE varchar(255)
-> );
DESC EMPLOYEE;
INSERT INTO EMPLOYEE(EmpID, firstName, LastName, EmpAGE, EMPZONE) VALUE (1, 'SHAHBAN',
'AHMED',23, 'ABOTTABAD');
SELECT * FROM EMPLOYEE;
ENTER RECORD JUST CHANGE THE NAME

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

THEN SHOW RECORD

CREATE DATABASE database_name;

CREATE DATABASE student_management;

USE student_management;

Step 1: Open MySQL Command Line Client

bash
CopyEdit
mysql -u root -p
Enter your password to log in.

🔹 Step 2: Create a Database

✅ DDL Syntax:
CREATE DATABASE database_name;
✅ Command:
CREATE DATABASE student_management;
USE student_management;

🔹 Step 3: Create a students Table

✅ DDL Syntax:
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
);
✅ Command:
CREATE TABLE students (
student_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
dob DATE,
gender ENUM('Male', 'Female', 'Other'),
email VARCHAR(100) UNIQUE,
enrollment_date DATE DEFAULT CURRENT_DATE
);

🔹 Step 4: Insert Records into students Table

✅ DML Syntax:
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
✅ Command:
INSERT INTO students (first_name, last_name, dob, gender, email)
VALUES
('John', 'Doe', '2002-08-15', 'Male', '[Link]@[Link]'),

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

('Jane', 'Smith', '2003-03-22', 'Female', '[Link]@[Link]');

🔹 Step 5: View Records

✅ DML Syntax:
SELECT * FROM table_name;
✅ Command:
SELECT * FROM students;

🔹 Step 6: Update a Record

✅ DML Syntax:
UPDATE table_name SET column1 = value1 WHERE condition;
✅ Command:
UPDATE students
SET email = 'john.doe22@[Link]'
WHERE student_id = 1;

🔹 Step 7: Delete a Record

✅ DML Syntax:
DELETE FROM table_name WHERE condition;
✅ Command:
DELETE FROM students
WHERE student_id = 2;

🔹 Step 8: Alter the Table (Add a Column)

✅ DDL Syntax:
ALTER TABLE table_name ADD column_name datatype;
✅ Command:
ALTER TABLE students ADD phone_number VARCHAR(15);

🔹 Step 9: Drop the Table

⚠️This will delete the table and all its data!
✅ DDL Syntax:
DROP TABLE table_name;
✅ Command:
DROP TABLE students;

MySQL DML Commands with Syntax and Examples

DML (Data Manipulation Language) commands are used to manipulate data within database tables. Here
are the essential DML commands with proper syntax and examples for your student management
system.
1. INSERT Command
Syntax:

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

sql
Copy
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
Examples:
sql
Copy
-- Insert single record
INSERT INTO students (first_name, last_name, date_of_birth, gender, email, department)
VALUES ('Alice', 'Johnson', '2001-03-15', 'Female', 'alice.j@[Link]', 'Computer Science');

-- Insert multiple records

INSERT INTO students (first_name, last_name, date_of_birth, gender, email, department) VALUES
('David', 'Wilson', '2000-07-22', 'Male', 'david.w@[Link]', 'Mathematics'),
('Sarah', 'Miller', '2002-01-30', 'Female', 'sarah.m@[Link]', 'Physics');

-- Insert with default values

INSERT INTO students (first_name, last_name, date_of_birth, gender, email)
VALUES ('James', 'Brown', '2001-11-05', 'Male', 'james.b@[Link]');
2. SELECT Command
Syntax:
sql
Copy
SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column_name [ASC|DESC]]
[LIMIT number];
Examples:
sql
Copy
-- Select all columns
SELECT * FROM students;

-- Select specific columns

SELECT student_id, first_name, last_name, department FROM students;

-- Select with WHERE clause

SELECT * FROM students WHERE department = 'Computer Science';

-- Select with multiple conditions

SELECT first_name, last_name, gpa
FROM students
WHERE gpa > 3.5 AND is_active = TRUE;

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

-- Select with ORDER BY

SELECT * FROM students ORDER BY last_name ASC, first_name ASC;

-- Select with LIMIT

SELECT * FROM students LIMIT 5;

-- Select with pattern matching (LIKE)

SELECT * FROM students WHERE email LIKE '%@[Link]';

-- Select with aggregation

SELECT department, COUNT(*) as student_count, AVG(gpa) as avg_gpa
FROM students
GROUP BY department;
3. UPDATE Command
Syntax:
sql
Copy
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Examples:
sql
Copy
-- Update single column
UPDATE students
SET email = '[Link]@[Link]'
WHERE student_id = 1;

-- Update multiple columns

UPDATE students
SET gpa = 3.8, updated_at = CURRENT_TIMESTAMP
WHERE student_id = 3;

-- Update with condition

UPDATE students
SET is_active = FALSE
WHERE academic_year = '4th' AND gpa < 2.0;

-- Update with calculation

UPDATE students
SET gpa = gpa + 0.1
WHERE gpa BETWEEN 3.0 AND 3.5;
4. DELETE Command

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Syntax:
sql
Copy
DELETE FROM table_name
WHERE condition;
Examples:
sql
Copy
-- Delete specific record
DELETE FROM students
WHERE student_id = 10;

-- Delete with condition

DELETE FROM students
WHERE is_active = FALSE AND enrollment_date < '2020-01-01';

-- Delete all records (use with caution!)

-- DELETE FROM students;
5. Advanced DML Operations
Transactions
sql
Copy
START TRANSACTION;
INSERT INTO students (first_name, last_name, email)
VALUES ('Emma', 'Watson', 'emma.w@[Link]');
UPDATE students SET gpa = 4.0 WHERE last_name = 'Watson';
COMMIT;
-- ROLLBACK; -- Use if something goes wrong
JOIN Operations
sql
Copy
-- Assuming we have a courses table
SELECT s.first_name, s.last_name, c.course_name
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id;
Subqueries
sql
Copy
-- Students with above-average GPA
SELECT first_name, last_name, gpa
FROM students
WHERE gpa > (SELECT AVG(gpa) FROM students);

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

MySQL DDL Commands with Syntax and Examples

DDL (Data Definition Language) commands are used to define and modify database structures. Here are
the essential DDL commands with syntax and examples for your student management system.
1. CREATE Command
Create Database
Syntax:
sql
Copy
CREATE DATABASE database_name;
Example:
sql
Copy
CREATE DATABASE student_management_system;
Create Table
Syntax:
sql
Copy
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
[table_constraints]
);
Example:
sql
Copy
CREATE TABLE students (
student_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
date_of_birth DATE NOT NULL,
email VARCHAR(100) UNIQUE,
enrollment_date DATE DEFAULT CURRENT_DATE,
gpa DECIMAL(3,2) CHECK (gpa BETWEEN 0.00 AND 4.00)
);
Create Index
Syntax:
sql
Copy
CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column2, ...);
Example:

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

sql
Copy
CREATE INDEX idx_student_name ON students(last_name, first_name);
2. ALTER Command
Add Column
Syntax:
sql
ALTER TABLE table_name
ADD COLUMN column_name datatype constraints;
Example:
sql
Copy
ALTER TABLE students
ADD COLUMN phone VARCHAR(15) AFTER email;
Modify Column
Syntax:
sql
Copy
ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype new_constraints;
Example:
sql
Copy
ALTER TABLE students
MODIFY COLUMN phone VARCHAR(20) NOT NULL;
Rename Column
Syntax:
sql
Copy
ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;
Example:
sql
Copy
ALTER TABLE students
RENAME COLUMN gpa TO grade_point_average;
Drop Column
Syntax:
sql
Copy
ALTER TABLE table_name
DROP COLUMN column_name;
Example:
sql

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Copy
ALTER TABLE students
DROP COLUMN phone;
Add Constraint
Syntax:
sql
Copy
ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (column_name);
Example:
sql
Copy
ALTER TABLE students
ADD CONSTRAINT chk_gpa CHECK (gpa >= 0 AND gpa <= 4);
3. DROP Command
Drop Table
Syntax:
sql
Copy
DROP TABLE [IF EXISTS] table_name;
Example:
sql
Copy
DROP TABLE IF EXISTS temp_students;
Drop Database
Syntax:
sql
Copy
DROP DATABASE [IF EXISTS] database_name;
Example:
sql
Copy
DROP DATABASE IF EXISTS old_student_db;
Drop Index
Syntax:
sql
Copy
ALTER TABLE table_name
DROP INDEX index_name;
Example:
sql
Copy
ALTER TABLE students
DROP INDEX idx_student_name;

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

4. TRUNCATE Command
Syntax:
sql
Copy
TRUNCATE [TABLE] table_name;
Example:
sql
Copy
TRUNCATE TABLE audit_log;
5. RENAME Command
Rename Table
Syntax:
sql
Copy
RENAME TABLE old_name TO new_name;
Example:
sql
Copy
RENAME TABLE students TO university_students;
Advanced DDL Examples
Create Table with Foreign Key
sql
Copy
CREATE TABLE student_courses (
enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
course_id INT NOT NULL,
enrollment_date DATE DEFAULT CURRENT_DATE,
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
Create Table with Composite Primary Key
sql
Copy
CREATE TABLE course_prerequisites (
course_id INT,
prerequisite_id INT,
PRIMARY KEY (course_id, prerequisite_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id),
FOREIGN KEY (prerequisite_id) REFERENCES courses(course_id)
);
Create View
sql
Copy

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

CREATE VIEW active_students_view AS

SELECT student_id, CONCAT(first_name, ' ', last_name) AS full_name, department
FROM students
WHERE is_active = TRUE;

Basic Data Types in MySQL for Database Management Systems

When designing a student management system (or any database system) in MySQL, choosing the right
data types is crucial for data integrity, storage efficiency, and performance. Here are the fundamental
data types you should know:
1. Numeric Data Types
Integer Types
 TINYINT: 1 byte (-128 to 127 or 0 to 255 unsigned)
sql
Copy
age TINYINT UNSIGNED -- For student age
 SMALLINT: 2 bytes (-32,768 to 32,767)
sql
Copy
classroom_number SMALLINT -- For classroom numbers
 INT/INTEGER: 4 bytes (-2.1B to 2.1B)
sql
Copy
student_id INT AUTO_INCREMENT -- Primary key
 BIGINT: 8 bytes (very large numbers)
sql
Copy
national_id BIGINT -- For long identification numbers
Decimal Types
 DECIMAL(p,s): Fixed-point (exact precision)
sql
Copy
gpa DECIMAL(3,2) -- For GPA values like 3.75
 FLOAT: 4-byte floating point (approximate)
sql
Copy
average_score FLOAT -- For calculated averages
 DOUBLE: 8-byte floating point (more precise than FLOAT)
2. String Data Types
Character Strings
 CHAR(n): Fixed-length (0-255 chars), padded with spaces
sql
Copy
gender CHAR(1) -- 'M' or 'F'
 VARCHAR(n): Variable-length (0-65,535 chars)

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

sql
Copy
email VARCHAR(100) -- Student email addresses
Text Types
 TEXT: Variable-length (up to 65KB)
sql
Copy
address TEXT -- For longer student addresses
 LONGTEXT: Up to 4GB of text
sql
Copy
thesis LONGTEXT -- For storing student papers
3. Date and Time Types
 DATE: Date only (YYYY-MM-DD)
sql
Copy
birth_date DATE -- Student date of birth
 TIME: Time only (HH:MM:SS)
sql
Copy
class_time TIME -- For class schedules
 DATETIME: Date and time (YYYY-MM-DD HH:MM:SS)
sql
Copy
enrollment_datetime DATETIME -- When student enrolled
 TIMESTAMP: Automatic timestamp (similar to DATETIME but with timezone support)
sql
Copy
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
 YEAR: Year value (1901-2155)
sql
Copy
graduation_year YEAR -- Expected graduation year
4. Specialized Types
Boolean
 BOOLEAN/BOOL: Synonym for TINYINT(1) (0 = false, 1 = true)
sql
Copy
is_active BOOLEAN DEFAULT TRUE -- Student status
Enumeration
 ENUM: String object with one value from a list
sql
Copy
year_level ENUM('Freshman','Sophomore','Junior','Senior') -- Student year

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Set
 SET: String object with zero or more values from a list
sql
Copy
extracurriculars SET('Sports','Music','Debate','Theater') -- Student activities
Binary Data
 BLOB: Binary large object (up to 65KB)
sql
Copy
student_photo BLOB -- For storing student photos
Practical Example for Student Management System
sql
Copy
CREATE TABLE students (
student_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
date_of_birth DATE NOT NULL,
gender ENUM('Male','Female','Other') NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
phone VARCHAR(15),
address TEXT,
gpa DECIMAL(3,2) CHECK (gpa BETWEEN 0.00 AND 4.00),
enrollment_date DATETIME DEFAULT CURRENT_TIMESTAMP,
graduation_year YEAR,
is_active BOOLEAN DEFAULT TRUE,
student_type ENUM('Undergraduate','Graduate','Exchange'),
courses_taken SET('Math','Science','History','Arts')
);

Data Control Language (DCL) in SQL

DCL (Data Control Language) is a subset of SQL commands used to control access to a database
by granting or revoking permissions. It ensures database security by defining who can access
what data and what operations they can perform.
1. GRANT Command
The GRANT command gives specific privileges to users or roles, allowing them to perform
certain operations on database objects.
Syntax:
sql
GRANT privilege_type ON object_name

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

TO user_or_role [WITH GRANT OPTION];

Common Privileges:
Privilege Description

SELECT Read data

INSERT Add new data

UPDATE Modify data

DELETE Remove data

ALL PRIVILEGES Full access

CREATE Create tables/databases

DROP Delete tables/databases

ALTER Modify table structure

Examples:
1. Grant SELECT access on a table:
sql
GRANT SELECT ON students TO 'user'@'localhost';
2. Grant INSERT and UPDATE on a table:
sql
GRANT INSERT, UPDATE ON students TO 'teacher'@'%';
3. Grant ALL PRIVILEGES on a database:
sql
GRANT ALL PRIVILEGES ON school_db.* TO 'admin'@'localhost';
4. Grant privileges with GRANT OPTION (allows user to grant permissions to others):
sql
GRANT SELECT ON students TO 'manager'@'%' WITH GRANT OPTION;

2. REVOKE Command
The REVOKE command removes previously granted privileges from a user or role.
Syntax:
sql
REVOKE privilege_type ON object_name
FROM user_or_role;

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Examples:
1. Revoke SELECT access:
sql
REVOKE SELECT ON students FROM 'user1'@'localhost';
2. Revoke multiple privileges:
sql
REVOKE INSERT, UPDATE ON students FROM 'teacher'@'%';
3. Revoke ALL PRIVILEGES:
sql
REVOKE ALL PRIVILEGES ON school_db.* FROM 'admin'@'localhost';
4. Revoke GRANT OPTION (without removing other privileges):
sql
REVOKE GRANT OPTION ON students FROM 'manager'@'%';

Key Differences Between GRANT and REVOKE

Feature GRANT REVOKE

Purpose Gives permissions Takes away permissions

Effect Allows operations Restricts operations

Usage Assigns rights Removes rights

Exampl
GRANT SELECT ON table TO user; REVOKE SELECT ON table FROM user;
e

MySQL Table Creation with Grant and Revoke Examples

Table Creation
First, let's create a sample table for our student management system:
-- Create database (if it doesn't exist)
CREATE DATABASE IF NOT EXISTS school_db;
USE school_db;

-- Create students table

CREATE TABLE students (
student_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

last_name VARCHAR(50) NOT NULL,

email VARCHAR(100) UNIQUE NOT NULL,
enrollment_date DATE DEFAULT (CURRENT_DATE),
gpa DECIMAL(3,2) CHECK (gpa BETWEEN 0.00 AND 4.00),
department VARCHAR(50),
is_active BOOLEAN DEFAULT TRUE
) ENGINE=InnoDB;

-- Create users table for access control

CREATE TABLE users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
password VARCHAR(255) NOT NULL,
role ENUM('admin','teacher','staff','student') NOT NULL
);
GRANT Command (Providing Privileges)
Syntax:
sql
GRANT privilege_type ON database_name.table_name
TO 'username'@'hostname' [IDENTIFIED BY 'password'];
GRANT privilege_type ON Student.table_name
TO 'username'@'hostname' [IDENTIFIED BY 'password'];

Examples:
1. Grant all privileges to admin user:
sql
GRANT ALL PRIVILEGES ON school_db.*
TO 'school_admin'@'localhost' IDENTIFIED BY 'Admin@123';
2. Grant SELECT only to teacher:
sql
GRANT SELECT ON school_db.students
TO 'math_teacher'@'%' IDENTIFIED BY 'Teacher@456';
3. Grant specific privileges to staff:
sql
GRANT SELECT, INSERT, UPDATE ON school_db.students
TO 'office_staff'@'192.168.1.%' IDENTIFIED BY 'Staff@789';
4. Grant column-level privileges:
sql
GRANT SELECT (student_id, first_name, last_name), UPDATE (email)
ON school_db.students TO 'assistant'@'localhost';
5. Grant ability to create temporary tables:
sql
GRANT CREATE TEMPORARY TABLES ON school_db.*
TO 'report_user'@'localhost';

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

REVOKE Command (Removing Privileges)

Syntax:
sql
REVOKE privilege_type ON database_name.table_name
FROM 'username'@'hostname';
Examples:
1. Revoke all privileges from a user:
sql
REVOKE ALL PRIVILEGES ON school_db.*
FROM 'former_employee'@'localhost';
2. Revoke specific privileges:
sql
REVOKE INSERT, UPDATE ON school_db.students
FROM 'temp_staff'@'%';
3. Revoke column-level privileges:
sql
REVOKE UPDATE (email) ON school_db.students
FROM 'assistant'@'localhost';
4. Revoke with cascade option (for procedures):
sql
REVOKE EXECUTE ON PROCEDURE school_db.update_gpa
FROM 'student_helper'@'%' CASCADE;
Viewing Privileges
To check what privileges have been granted:
sql
-- Show grants for current user
SHOW GRANTS;
-- Show grants for specific user
SHOW GRANTS FOR 'math_teacher'@'%';
Practical Example Workflow
1. Create a new teacher account:
sql
CREATE USER 'physics_teacher'@'localhost' IDENTIFIED BY 'Phys1cs!';
2. Grant appropriate privileges:
sql
GRANT SELECT, UPDATE (gpa) ON school_db.students
TO 'physics_teacher'@'localhost';
3. Later, revoke update privileges:
sql
REVOKE UPDATE ON school_db.students
FROM 'physics_teacher'@'localhost';
4. Finally, remove the user completely:
sql
DROP USER 'physics_teacher'@'localhost';

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Here are the commands for showing, dropping, truncating, altering, and recreating the students table in
MySQL:
1. SHOW TABLE Command
Show all tables in the database:
sql
SHOW TABLES;
Show the structure of the students table:
sql
DESCRIBE students;
-- OR
SHOW COLUMNS FROM students;
-- OR (for more detailed info)
SHOW CREATE TABLE students;
2. DROP TABLE Command
Drop the students table completely:
sql
DROP TABLE IF EXISTS students;
3. TRUNCATE TABLE Command
Delete all data but keep the table structure:
sql
TRUNCATE TABLE students;
4. ALTER TABLE Commands
Add a new column:
sql
ALTER TABLE students
ADD COLUMN phone_number VARCHAR(15) AFTER email;
Modify a column:
sql
ALTER TABLE students
MODIFY COLUMN department VARCHAR(100);
Rename a column:
sql
ALTER TABLE students
RENAME COLUMN gpa TO grade_point_average;
Drop a column:
sql
ALTER TABLE students
DROP COLUMN is_active;
Add a constraint:
sql
ALTER TABLE students
ADD CONSTRAINT chk_email CHECK (email LIKE '%@%.%');
5. CREATE TABLE Statement (from previous example)
sql

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

CREATE TABLE students (

student_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
enrollment_date DATE DEFAULT (CURRENT_DATE),
gpa DECIMAL(3,2) CHECK (gpa BETWEEN 0.00 AND 4.00),
department VARCHAR(50),
is_active BOOLEAN DEFAULT TRUE
) ENGINE=InnoDB;
Complete Example Workflow
sql
-- 1. Show existing tables
SHOW TABLES;

-- 2. Drop the table if it exists

DROP TABLE IF EXISTS students;

-- 3. Create the table

CREATE TABLE students (
student_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
enrollment_date DATE DEFAULT (CURRENT_DATE),
gpa DECIMAL(3,2) CHECK (gpa BETWEEN 0.00 AND 4.00),
department VARCHAR(50),
is_active BOOLEAN DEFAULT TRUE
) ENGINE=InnoDB;

-- 4. Alter the table to add a phone number

ALTER TABLE students
ADD COLUMN phone VARCHAR(15) AFTER email;

-- 5. Show the new structure

DESCRIBE students;

-- 6. Truncate the table (after some data has been added)

TRUNCATE TABLE students;

-- 7. Finally drop the table when no longer needed

DROP TABLE students;
MySQL WHERE and NOT WHERE Commands with Syntax and Examples
WHERE Clause (Filtering Records)

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Syntax:
sql
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Examples:
1. Basic equality check:
sql
SELECT * FROM students
WHERE department = 'Computer Science';
2. Numeric comparison:
sql
SELECT first_name, last_name, gpa
FROM students
WHERE gpa > 3.5;
3. Date comparison:
sql
SELECT * FROM students
WHERE enrollment_date >= '2023-01-01';
4. Multiple conditions with AND:
sql
SELECT * FROM students
WHERE department = 'Mathematics' AND gpa >= 3.0;
5. Pattern matching with LIKE:
sql
SELECT * FROM students
WHERE last_name LIKE 'Sm%'; -- Starts with "Sm"
6. List of values with IN:
sql
SELECT * FROM students
WHERE department IN ('Computer Science', 'Physics', 'Mathematics');
7. NULL value check:
sql
SELECT * FROM students
WHERE email IS NULL;
NOT WHERE (Excluding Records)
There are two ways to express "NOT WHERE" in MySQL:
Option 1: Using NOT with WHERE
Syntax:
sql
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
Examples:

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

1. Not equal:
sql
SELECT * FROM students
WHERE NOT department = 'Art History';
-- Equivalent to: WHERE department != 'Art History'
2. Not like:
sql
SELECT * FROM students
WHERE NOT email LIKE '%@[Link]';
3. Not in:
sql
SELECT * FROM students
WHERE NOT department IN ('Art History', 'Drama');
4. Not null:
sql
SELECT * FROM students
WHERE NOT email IS NULL;
-- Equivalent to: WHERE email IS NOT NULL
Option 2: Using <> or != operators
Examples:
1. Not equal:
sql
SELECT * FROM students
WHERE department <> 'Art History';
2. Not between:
sql
SELECT * FROM students
WHERE gpa NOT BETWEEN 2.0 AND 3.0;
Combining WHERE and NOT
Examples:
1. Students in specific departments with low GPA:
sql
SELECT * FROM students
WHERE department IN ('Computer Science', 'Engineering')
AND NOT gpa > 2.5;
2. Active students not in certain departments:
sql
SELECT * FROM students
WHERE is_active = TRUE
AND NOT department IN ('Graduated', 'Suspended');
3. Students enrolled recently but not with specific email domains:
sql
SELECT * FROM students
WHERE enrollment_date > '2023-09-01'

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

AND NOT email LIKE '%@[Link]';

SQL Data Retrieval Commands

1. SELECT Statement (Basic Retrieval)
Syntax:
sql
SELECT column1, column2, ...
FROM table_name;
Examples:
sql
-- Select all columns from students table
SELECT * FROM students;

-- Select specific columns

SELECT student_id, first_name, last_name FROM students;

-- Select with column aliases

SELECT
student_id AS ID,
first_name AS "First Name",
last_name AS "Last Name"
FROM students;

-- Select with calculations

SELECT
first_name,
last_name,
gpa,
gpa * 25 AS percentage
FROM students;
2. SELECT DISTINCT Statement (Unique Values)
Syntax:
sql
SELECT DISTINCT column1, column2, ...
FROM table_name;
Examples:
sql
-- Get all unique departments
SELECT DISTINCT department FROM students;

-- Get unique combinations of department and academic year

SELECT DISTINCT department, academic_year FROM students;

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

-- Count distinct departments

SELECT COUNT(DISTINCT department) AS total_departments FROM students;
3. WHERE Clause (Filtering Results)
Syntax:
sql
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Examples:
Comparison Operators:
sql
-- Students with GPA > 3.5
SELECT * FROM students WHERE gpa > 3.5;

-- Students enrolled before 2023

SELECT * FROM students WHERE enrollment_date < '2023-01-01';

-- Students not in Computer Science

SELECT * FROM students WHERE department != 'Computer Science';
Logical Operators:
sql
-- AND operator
SELECT * FROM students
WHERE department = 'Computer Science' AND gpa >= 3.0;

-- OR operator
SELECT * FROM students
WHERE department = 'Mathematics' OR department = 'Physics';

-- NOT operator
SELECT * FROM students
WHERE NOT is_active = FALSE;
Pattern Matching:
sql
-- Students with last names starting with 'Sm'
SELECT * FROM students WHERE last_name LIKE 'Sm%';

-- Students with emails from specific domain

SELECT * FROM students WHERE email LIKE '%@[Link]';

-- Students with 5-letter last names

SELECT * FROM students WHERE last_name LIKE '_____';
Range Conditions:

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

sql
-- Students with GPA between 3.0 and 3.5
SELECT * FROM students WHERE gpa BETWEEN 3.0 AND 3.5;

-- Students enrolled in 2022

SELECT * FROM students
WHERE enrollment_date BETWEEN '2022-01-01' AND '2022-12-31';
IN Operator:
sql
-- Students in specific departments
SELECT * FROM students
WHERE department IN ('Computer Science', 'Mathematics', 'Physics');

-- Students with specific IDs

SELECT * FROM students
WHERE student_id IN (1001, 1005, 1010);
NULL Values:
sql
-- Students without email addresses
SELECT * FROM students WHERE email IS NULL;

-- Students with email addresses

SELECT * FROM students WHERE email IS NOT NULL;
Combined Examples:
sql
-- Active computer science students with high GPA
SELECT student_id, first_name, last_name, gpa
FROM students
WHERE department = 'Computer Science'
AND gpa >= 3.5
AND is_active = TRUE
ORDER BY gpa DESC;

-- Distinct departments with more than 10 students

SELECT department, COUNT(*) AS student_count
FROM students
GROUP BY department
HAVING COUNT(*) > 10;

Table Manipulation:
1. Show table
2. Create table statement
3. Drop table statement

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

4. Truncate table statement

5. Alter table statement
6. Alter table - add column
7. Alter table – drop column
8. Alter table – Rename / Modify Column
9. Create index statement
10. Insert into statement
11. Update statement
12. Limit clause
13. Insert into select statement
14. SQL Sorting result
15. Descending Order (DESC Statement)
16. Explain in SQL

MySQL Commands for ditpart2 Table

Here's a complete set of commands for a student table with 5 basic attributes and 5 records, along with
all the requested operations:

1. Create the table

sql

CREATE TABLE ditpart2 (

student_id INT PRIMARY KEY,

first_name VARCHAR(50) NOT NULL,

last_name VARCHAR(50) NOT NULL,

age INT,

email VARCHAR(100)

);

2. Insert 5 student records

sql

INSERT INTO ditpart2 (student_id, first_name, last_name, age, email)

VALUES

(1, 'John', 'Doe', 20, '[Link]@[Link]'),

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

(2, 'Jane', 'Smith', 21, '[Link]@[Link]'),

(3, 'Michael', 'Johnson', 19, 'michael.j@[Link]'),

(4, 'Emily', 'Williams', 22, 'emily.w@[Link]'),

(5, 'David', 'Brown', 20, 'david.b@[Link]');

3. Show table structure

sql

DESCRIBE ditpart2;

4. Show table contents

sql

SELECT * FROM ditpart2;

5. Show create table statement

sql

SHOW CREATE TABLE ditpart2;

6. Drop table statement

sql

DROP TABLE IF EXISTS ditpart2;

7. Truncate table statement

sql

TRUNCATE TABLE ditpart2;

8. Alter table - add column

sql

ALTER TABLE ditpart2 ADD COLUMN phone_number VARCHAR(15);

9. Alter table - drop column

sql

ALTER TABLE ditpart2 DROP COLUMN phone_number;

10. Alter table - rename/modify column

sql

-- Rename column

ALTER TABLE ditpart2 RENAME COLUMN email TO email_address;

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

-- Modify column

ALTER TABLE ditpart2 MODIFY COLUMN email_address VARCHAR(150);

11. Create index statement

sql

CREATE INDEX idx_last_name ON ditpart2(last_name);

12. Update statement

sql

UPDATE ditpart2 SET age = 23 WHERE student_id = 5;

13. Limit clause

sql

SELECT * FROM ditpart2 LIMIT 3;

14. Insert into select statement

sql

-- First create a temporary table

CREATE TABLE temp_students LIKE ditpart2;

-- Then insert using select

INSERT INTO temp_students SELECT * FROM ditpart2 WHERE age > 20;

15. SQL Sorting result

sql

SELECT * FROM ditpart2 ORDER BY first_name;

16. Descending Order (DESC Statement)

sql

SELECT * FROM ditpart2 ORDER BY age DESC;

17. Explain in SQL

sql

EXPLAIN SELECT * FROM ditpart2 WHERE age > 20;

To use these commands in MySQL Command Line 8.0:

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

1. First run the CREATE TABLE statement

2. Then run the INSERT statements to populate the table

3. You can then run any of the other commands in any order (though some like DROP will remove
the table)

The table has 5 basic attributes of students (student_id, first_name, last_name, age, email) and contains
5 records as specified.

What is Data Retrieval.

1. Select statement
2. To retrieve selective field
3. SELECT DISTINCT STATEMENT
4. WHERE CLAUSS
5. WHERE NOT
6. ORDER BY CLAUSE
7. ORDER BY DESC
8. GROUP MY CLAUSE
9. HAVING CLAUSE

Data Retrieval in MySQL

Data Retrieval refers to the process of extracting or fetching data from a database. In MySQL, this is
primarily done using the SELECT statement, along with various clauses to filter, sort, and group data.
1. Create a Customer Table
First, let's create a customer table with 5 attributes:
 CustomerID (Primary Key)
 Name
 Age
 Address
 Salary
Syntax:
sql
CREATE TABLE customer (
CustomerID INT PRIMARY KEY,

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Name VARCHAR(50) NOT NULL,

Age INT,
Address VARCHAR(100),
Salary DECIMAL(10, 2)
);
Insert 5 Records:
sql
INSERT INTO customer (CustomerID, Name, Age, Address, Salary)
VALUES
(1, 'John Doe', 28, '123 Main St, New York', 50000.00),
(2, 'Jane Smith', 32, '456 Park Ave, Chicago', 65000.00),
(3, 'Robert Johnson', 45, '789 Elm St, Boston', 80000.00),
(4, 'Emily Davis', 22, '321 Oak St, Los Angeles', 45000.00),
(5, 'Michael Wilson', 35, '654 Pine St, Seattle', 70000.00);

2. Basic SELECT Statement

Definition: Retrieves all records from a table.
Syntax:
sql
SELECT * FROM customer;
Command:
sql
SELECT * FROM customer;
Output:
+------------+----------------+------+-----------------------+----------+
| CustomerID | Name | Age | Address | Salary |
+------------+----------------+------+-----------------------+----------+
|1 | John Doe | 28 | 123 Main St, New York | 50000.00 |
|2 | Jane Smith | 32 | 456 Park Ave, Chicago | 65000.00 |
|3 | Robert Johnson | 45 | 789 Elm St, Boston | 80000.00 |
|4 | Emily Davis | 22 | 321 Oak St, Los Angeles| 45000.00 |
|5 | Michael Wilson | 35 | 654 Pine St, Seattle | 70000.00 |
+------------+----------------+------+-----------------------+----------+

3. Retrieve Selective Fields

Definition: Fetches only specified columns instead of all.
Syntax:
sql
SELECT column1, column2 FROM table_name;
Command:
sql
SELECT Name, Salary FROM customer;
Output:
+----------------+----------+

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

| Name | Salary |
+----------------+----------+
| John Doe | 50000.00 |
| Jane Smith | 65000.00 |
| Robert Johnson | 80000.00 |
| Emily Davis | 45000.00 |
| Michael Wilson | 70000.00 |
+----------------+----------+

4. SELECT DISTINCT Statement

Definition: Retrieves unique values (removes duplicates).
Syntax:
sql
SELECT DISTINCT column_name FROM table_name;
Command:
sql
SELECT DISTINCT Age FROM customer;
Output:

+------+
| Age |
+------+
| 28 |
| 32 |
| 45 |
| 22 |
| 35 |
+------+

5. WHERE Clause
Definition: Filters records based on a condition.
Syntax:
sql
SELECT * FROM table_name WHERE condition;
Command:
sql
SELECT * FROM customer WHERE Salary > 60000;
Output:
+------------+----------------+------+----------------------+----------+
| CustomerID | Name | Age | Address | Salary |
+------------+----------------+------+----------------------+----------+
|2 | Jane Smith | 32 | 456 Park Ave, Chicago| 65000.00 |
|3 | Robert Johnson | 45 | 789 Elm St, Boston | 80000.00 |
|5 | Michael Wilson | 35 | 654 Pine St, Seattle | 70000.00 |

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

+------------+----------------+------+----------------------+----------+

6. WHERE NOT Clause

Definition: Excludes records that match a condition.
Syntax:
sql
SELECT * FROM table_name WHERE NOT condition;
Command:
sql
SELECT * FROM customer WHERE NOT Age > 30;
Output:
+------------+------------+------+-----------------------+----------+
| CustomerID | Name | Age | Address | Salary |
+------------+------------+------+-----------------------+----------+
|1 | John Doe | 28 | 123 Main St, New York | 50000.00 |
|4 | Emily Davis| 22 | 321 Oak St, Los Angeles| 45000.00 |
+------------+------------+------+-----------------------+----------+

7. ORDER BY Clause
Definition: Sorts results in ascending (default) or descending order.
Syntax:
sql
SELECT * FROM table_name ORDER BY column_name;
Command:
sql
SELECT * FROM customer ORDER BY Salary;
Output:
+------------+----------------+------+-----------------------+----------+
| CustomerID | Name | Age | Address | Salary |
+------------+----------------+------+-----------------------+----------+
|4 | Emily Davis | 22 | 321 Oak St, Los Angeles| 45000.00 |
|1 | John Doe | 28 | 123 Main St, New York | 50000.00 |
|2 | Jane Smith | 32 | 456 Park Ave, Chicago | 65000.00 |
|5 | Michael Wilson | 35 | 654 Pine St, Seattle | 70000.00 |
|3 | Robert Johnson | 45 | 789 Elm St, Boston | 80000.00 |
+------------+----------------+------+-----------------------+----------+

8. ORDER BY DESC
Definition: Sorts results in descending order.
Syntax:
sql
SELECT * FROM table_name ORDER BY column_name DESC;
Command:
sql

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

SELECT * FROM customer ORDER BY Age DESC;

Output:
+------------+----------------+------+-----------------------+----------+
| CustomerID | Name | Age | Address | Salary |
+------------+----------------+------+-----------------------+----------+
|3 | Robert Johnson | 45 | 789 Elm St, Boston | 80000.00 |
|2 | Jane Smith | 32 | 456 Park Ave, Chicago | 65000.00 |
|5 | Michael Wilson | 35 | 654 Pine St, Seattle | 70000.00 |
|1 | John Doe | 28 | 123 Main St, New York | 50000.00 |
|4 | Emily Davis | 22 | 321 Oak St, Los Angeles| 45000.00 |
+------------+----------------+------+-----------------------+----------+

9. GROUP BY Clause
Definition: Groups rows with the same values into summary rows.
Syntax:
sql
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
Command:
sql
SELECT Age, COUNT(*) AS Count FROM customer GROUP BY Age;
Output:
+------+-------+
| Age | Count |
+------+-------+
| 22 | 1 |
| 28 | 1 |
| 32 | 1 |
| 35 | 1 |
| 45 | 1 |
+------+-------+

10. HAVING Clause

Definition: Filters groups after GROUP BY (similar to WHERE but for groups).
Syntax:
sql
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING condition;
Command:
sql
SELECT Age, COUNT(*) AS Count FROM customer GROUP BY Age HAVING COUNT(*) > 0;
Output:
+------+-------+
| Age | Count |
+------+-------+
| 22 | 1 |

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

| 28 | 1 |
| 32 | 1 |
| 35 | 1 |
| 45 | 1 |
+------+-------+

What is Constraint in MySQL.

i. NOT NULL Constraint

ii. UNIQUE Constraint
iii. PRIMARY KEY Constraint
iv. FOREIGN KEY Constraint
v. CHECK Constraint
vi. DEFAULT Constraint
vii. CREATE INDEX Constraint

MySQL Constraints with Customer Table

Example
What are Constraints in MySQL?

Constraints are rules enforced on data columns in MySQL tables to maintain

data integrity and accuracy. They limit the type of data that can be stored in
a table, ensuring reliability and consistency of the database.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Customer Table Creation with 7 Constraints

Let's create a comprehensive customer table with 7 attributes demonstrating

each constraint type, then insert 10 sample records.

1. Create the Customer Table with All Constraints

sql
-- Create the database if it doesn't exist
CREATE DATABASE IF NOT EXISTS customer_db;
USE customer_db;

-- Create the main customer table

CREATE TABLE customers (
customer_id INT AUTO_INCREMENT,
name VARCHAR(50) NOT NULL, -- NOT NULL constraint
age INT CHECK (age >= 18 AND age <= 100), -- CHECK constraint
email VARCHAR(100) UNIQUE, -- UNIQUE constraint
address VARCHAR(200) DEFAULT 'Address not provided',-- DEFAULT constraint
salary DECIMAL(10,2) CHECK (salary > 0),
phone VARCHAR(15),
CONSTRAINT pk_customer PRIMARY KEY (customer_id), -- PRIMARY KEY constraint
CONSTRAINT chk_salary CHECK (salary > 0)
);

-- Create a related table for FOREIGN KEY demonstration

CREATE TABLE customer_orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
order_date DATE DEFAULT (CURRENT_DATE), -- Correct for MySQL 8.0
amount DECIMAL(10,2),
CONSTRAINT fk_customer_order FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);

-- Add an INDEX on frequently searched columns

CREATE INDEX idx_customer_email ON customers(email); -- CREATE INDEX constraint
CREATE INDEX idx_customer_phone ON customers(phone);

2. Insert 10 Sample Customer Records

sql
INSERT INTO customers (name, age, email, address, salary, phone) VALUES

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

('John Smith', 28, '[Link]@[Link]', '123 Main St, New York', 55000.00, '555-
0101'),
('Sarah Johnson', 35, 'sarah.j@[Link]', '456 Oak Ave, Boston', 72000.00, '555-0102'),
('Michael Brown', 42, 'michael.b@[Link]', DEFAULT, 85000.00, '555-0103'),
('Emily Davis', 31, 'emily.d@[Link]', '789 Pine Rd, Chicago', 62000.00, '555-0104'),
('Robert Wilson', 45, 'robert.w@[Link]', '321 Elm St, Los Angeles', 90000.00, '555-
0105'),
('Jennifer Lee', 29, 'jennifer.l@[Link]', '654 Maple Dr, Miami', 58000.00, '555-0106'),
('David Miller', 38, 'david.m@[Link]', '987 Cedar Ln, Dallas', 78000.00, '555-0107'),
('Lisa Taylor', 33, 'lisa.t@[Link]', DEFAULT, 67000.00, '555-0108'),
('James Anderson', 40, 'james.a@[Link]', '135 Birch Blvd, Seattle', 82000.00, '555-
0109'),
('Jessica Martinez', 27, 'jessica.m@[Link]', '246 Willow Way, Atlanta', 59000.00, '555-
0110');

Detailed Explanation of Each Constraint

i. NOT NULL Constraint

Definition: Ensures a column cannot contain NULL values.

Syntax:
sql
column_name data_type NOT NULL

Example in our table:

sql
name VARCHAR(50) NOT NULL

Test:
sql
-- This will fail:
INSERT INTO customers (age, email, salary) VALUES (30, 'test@[Link]', 50000);
-- Error: Field 'name' doesn't have a default value

ii. UNIQUE Constraint

Definition: Ensures all values in a column are different.

Syntax:
sql
column_name data_type UNIQUE

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Example in our table:

sql
email VARCHAR(100) UNIQUE

Test:
sql
-- This will fail:
INSERT INTO customers (name, age, email, salary)
VALUES ('Duplicate Email', 30, '[Link]@[Link]', 50000);
-- Error: Duplicate entry '[Link]@[Link]' for key '[Link]'

iii. PRIMARY KEY Constraint

Definition: Uniquely identifies each record (NOT NULL + UNIQUE).

Syntax:
sql
column_name data_type PRIMARY KEY

Example in our table:

sql
CONSTRAINT pk_customer PRIMARY KEY (customer_id)

Test:
sql
-- This will fail:
INSERT INTO customers (customer_id, name, age, email, salary)
VALUES (1, 'Duplicate ID', 30, 'new@[Link]', 50000);
-- Error: Duplicate entry '1' for key '[Link]'

iv. FOREIGN KEY Constraint

Definition: Maintains referential integrity between two tables.

Syntax:
sql
CONSTRAINT constraint_name FOREIGN KEY (column)
REFERENCES parent_table(parent_column)

Example in our table:

sql
CONSTRAINT fk_customer_order FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Test:
sql
-- This works (customer_id 1 exists):
INSERT INTO customer_orders (customer_id, amount) VALUES (1, 150.00);

-- This fails:
INSERT INTO customer_orders (customer_id, amount) VALUES (999, 200.00);
-- Error: Cannot add or update a child row: a foreign key constraint fails

v. CHECK Constraint

Definition: Ensures column values meet a specific condition.

Syntax:
sql
column_name data_type CHECK (condition)

Example in our table:

sql
age INT CHECK (age >= 18 AND age <= 100)
salary DECIMAL(10,2) CHECK (salary > 0)

Test:
sql
-- This will fail (age < 18):
INSERT INTO customers (name, age, email, salary)
VALUES ('Young Customer', 17, 'young@[Link]', 50000);

-- This will fail (salary <= 0):

INSERT INTO customers (name, age, email, salary)
VALUES ('No Salary', 25, 'nosalary@[Link]', 0);

vi. DEFAULT Constraint

Definition: Sets a default value when no value is specified.

Syntax:
sql
column_name data_type DEFAULT default_value

Example in our table:

sql
address VARCHAR(200) DEFAULT 'Address not provided'

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Test:
sql
-- Insert without address:
INSERT INTO customers (name, age, email, salary)
VALUES ('Default Address', 30, 'default@[Link]', 50000);

-- Verify:
SELECT address FROM customers WHERE email = 'default@[Link]';
-- Returns: 'Address not provided'

vii. CREATE INDEX Constraint

Definition: Improves query performance on frequently searched columns.

Syntax:
sql
CREATE INDEX index_name ON table_name (column_name)

Example in our table:

sql
CREATE INDEX idx_customer_email ON customers(email);
CREATE INDEX idx_customer_phone ON customers(phone);

Test:
sql
-- Check existing indexes:
SHOW INDEX FROM customers;

-- Query using indexed column (will be faster):

SELECT * FROM customers WHERE email = '[Link]@[Link]';

Summary of Constraints in Our Customer Table

Constraint Applied To Purpose

NOT NULL name Ensures every customer has a name

UNIQUE email Prevents duplicate email addresses

PRIMARY KEY customer_id Uniquely identifies each customer

FOREIGN KEY customer_orders.customer_i Links orders to customers

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Constraint Applied To Purpose

CHECK age, salary Validates age and salary ranges

DEFAULT address Provides default address when none given

CREATE INDEX email, phone Improves search performance

This comprehensive example demonstrates all major MySQL constraints in a

practical customer database scenario that you can directly run in MySQL 8.0
command line.

MySQL CREATE FUNCTION Statement

A function is a block of organized, reusable code that is used to perform a single, related action.
Functions provide better modularity for your application and a high degree of code reusing.
MySQL provides a set of built-in function which performs particular tasks for example the
CURDATE() function returns the current date.
You can create a stored function using the CREATE FUNCTION statement.
Syntax
Following is the syntax the CREATE FUNCTION statement –
CREATE FUNCTION function_Name(input_arguments) RETURNS output_parameter
Where, function_name is the name of the function you need to create, input_arguments are
the input values of the function and output_parameter is the return value of the function.
MySQL Functions with Product Table Examples
What is a Function in MySQL?
A function in MySQL is a stored program that takes inputs (parameters), performs calculations
or operations, and returns a value. MySQL provides built-in functions for data manipulation,
mathematical operations, string handling, and more.
Here are the types of functions in MySQL with one-line explanations:
1. Mathematical Functions - Perform calculations
2. String Functions - Manipulate text data (e.g., CONCAT(), SUBSTRING(), TRIM())
3. Date/Time Functions - Handle date/time values (e.g., NOW(), DATE_FORMAT(),
DATEDIFF())
4. Aggregate Functions - Operate on multiple rows to return single values (e.g., SUM(),
AVG(), COUNT())

Product Table Creation

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

First, let's create a product table with 3 attributes and insert 5 sample records:
MySQL Functions with Product Table Examples
-- Create database if not exists
CREATE DATABASE IF NOT EXISTS product_db;
USE product_db;
-- Create products table
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
unit VARCHAR(20) NOT NULL,
price DECIMAL(10,2) NOT NULL
);

-- Insert sample data

INSERT INTO products (unit, price) VALUES
('LITER ', 25.50),
(' KG', 42.75),
('GALLON ', 18.99),
(' POUND', 35.20),
('OUNCE', 12.49);
Function Examples with Product Table
i. AVG() Function
Definition: Calculates the average value of a numeric column.
Syntax: SELECT AVG(column_name) FROM table_name;
Example:
sql
SELECT AVG(price) AS average_price FROM products;
Output:
+--------------+
| average_price|
+--------------+
| 26.99 |
+--------------+
ii. COUNT() Function
Definition: Counts the number of rows or non-NULL values.
Syntax: SELECT COUNT(column_name) FROM table_name;
Example:
sql
SELECT COUNT(*) AS total_products FROM products;
Output:
+---------------+

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

| total_products|
+---------------+
|5 |
+---------------+
iii. CONCAT() Function
Definition: Combines two or more strings.
Syntax: SELECT CONCAT(string1, string2) FROM table_name;
Example:
sql
SELECT CONCAT(product_id, ' - ', unit) AS product_info FROM products;
Output:
+--------------+
| product_info |
+--------------+
| 1 - LITER |
| 2 - KG |
| 3 - GALLON |
| 4 - POUND |
| 5 - OUNCE |
+--------------+
iv. LOWER() Function
Definition: Converts a string to lowercase.
Syntax: SELECT LOWER(column_name) FROM table_name;
Example:
sql
SELECT LOWER(unit) AS lowercase_unit FROM products;
Output:
+---------------+
| lowercase_unit|
+---------------+
| liter |
| kg |
| gallon |
| pound |
| ounce |
+---------------+
v. LENGTH() Function
Definition: Returns the length of a string in bytes.
Syntax: SELECT LENGTH(column_name) FROM table_name;
Example:
sql

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

SELECT unit, LENGTH(unit) AS unit_length FROM products;

Output:
+--------+-------------+
| unit | unit_length |
+--------+-------------+
| LITER | 6 |
| KG | 3 |
| GALLON | 7 |
| POUND | 5 |
| OUNCE | 5 |
+--------+-------------+
vi. LTRIM() Function
Definition: Removes leading spaces from a string.
Syntax: SELECT LTRIM(column_name) FROM table_name;
Example:
sql
SELECT LTRIM(unit) AS trimmed_unit FROM products;
Output:
+-------------+
| trimmed_unit|
+-------------+
| LITER |
| KG |
| GALLON |
| POUND |
| OUNCE |
+-------------+
vii. MIN() Function
Definition: Returns the minimum value in a set.
Syntax: SELECT MIN(column_name) FROM table_name;
Example:
sql
SELECT MIN(price) AS minimum_price FROM products;
Output:
+---------------+
| minimum_price |
+---------------+
| 12.49 |
+---------------+
viii. MAX() Function
Definition: Returns the maximum value in a set.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Syntax: SELECT MAX(column_name) FROM table_name;

Example:
sql
SELECT MAX(price) AS maximum_price FROM products;
Output:
+---------------+
| maximum_price |
+---------------+
| 42.75 |
+---------------+
ix. RTRIM() Function
Definition: Removes trailing spaces from a string.
Syntax: SELECT RTRIM(column_name) FROM table_name;
Example:
sql
SELECT RTRIM(unit) AS trimmed_unit FROM products;
Output:
+-------------+
| trimmed_unit|
+-------------+
| LITER |
| KG |
| GALLON |
| POUND |
| OUNCE |
+-------------+
x. REPLACE() Function
Definition: Replaces occurrences of a substring.
Syntax: SELECT REPLACE(column_name, 'old', 'new') FROM table_name;
Example:
sql
SELECT REPLACE(unit, 'OUNCE', 'OZ') AS replaced_unit FROM products;
Output:
+--------------+
| replaced_unit|
+--------------+
| LITER |
| KG |
| GALLON |
| POUND |
| OZ |

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

+--------------+
xi. REPEAT() Function
Definition: Repeats a string a specified number of times.
Syntax: SELECT REPEAT(string, count) FROM table_name;
Example:
sql
SELECT product_id, REPEAT('*', product_id) AS stars FROM products;
Output:
+------------+-------+
| product_id | stars |
+------------+-------+
|1 |* |
|2 | ** |
|3 | *** |
|4 | **** |
|5 | ***** |
+------------+-------+
xii. STRCMP() Function
Definition: Compares two strings (returns 0 if equal, -1 if first < second, 1 if first > second).
Syntax: SELECT STRCMP(string1, string2);
Example:
sql
SELECT unit, STRCMP(unit, 'KG') AS comparison_result FROM products;
Output:
+--------+-------------------+
| unit | comparison_result |
+--------+-------------------+
| LITER | 1 |
| KG | 0 |
| GALLON | 1 |
| POUND | 1 |
| OUNCE | 1 |
+--------+-------------------+
xiii. SUBSTR() Function
Definition: Extracts a substring from a string.
Syntax: SELECT SUBSTR(string, start, length) FROM table_name;
Example:
sql
SELECT unit, SUBSTR(unit, 2, 3) AS substring_unit FROM products;
Output:
+--------+---------------+

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

| unit | substring_unit|
+--------+---------------+
| LITER | ITE |
| KG | G |
| GALLON | AL |
| POUND | OUN |
| OUNCE | UNC |
+--------+---------------+
xiv. SUM() Function
Definition: Returns the sum of values in a numeric column.
Syntax: SELECT SUM(column_name) FROM table_name;
Example:
sql
SELECT SUM(price) AS total_value FROM products;
Output:
+-------------+
| total_value |
+-------------+
| 134.93 |
+-------------+
xv. UPPER() Function
Definition: Converts a string to uppercase.
Syntax: SELECT UPPER(column_name) FROM table_name;
Example:
sql
SELECT UPPER(unit) AS uppercase_unit FROM products;
Output:
+---------------+
| uppercase_unit|
+---------------+
| LITER |
| KG |
| GALLON |
| POUND |
| OUNCE |
+---------------+
These examples demonstrate all the requested MySQL functions using the product table, with
syntax that works in MySQL 8.0 command line. Each function is applied to the table data with
clear output examples.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

MySQL Operators Explained

What are MySQL Operators?

Operators are symbols or keywords used to perform operations on data in SQL queries, such as
arithmetic calculations, comparisons, and logical conditions.

1. Arithmetic Operators

Definition: Perform mathematical calculations on numeric values.

Operator Description Syntax Example Output

+ Addition SELECT a + b SELECT 10 + 5; 15

- Subtraction SELECT a - b SELECT 20 - 8; 12

* Multiplication SELECT a * b SELECT 5 * 6; 30

/ Division SELECT a / b SELECT 15 / 3; 5.0000

% or MOD( Modulus
SELECT a % b SELECT 10 % 3; 1
) (remainder)

Example with Table (Products):

sql

SELECT product_id, price, price * 1.1 AS increased_price FROM products;

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Output:

product_id price increased_price

1 25.50 28.050

2 42.75 47.025

2. Comparison Operators

Definition: Used to compare values in WHERE, HAVING, and JOIN conditions.

Operator Description Syntax Example Output

WHERE a SELECT * FROM products Returns rows where

= Equal to
=b WHERE price = 25.50; price is 25.50

WHERE a ! SELECT * FROM products Excludes rows where

!= or <> Not equal to
=b WHERE price != 25.50; price is 25.50

WHERE a SELECT * FROM products Returns rows with

> Greater than
>b WHERE price > 30; price > 30

WHERE a SELECT * FROM products Returns rows with

< Less than
<b WHERE price < 20; price < 20

Greater than or WHERE a SELECT * FROM products Returns rows with

>=
equal to >= b WHERE price >= 25.50; price ≥ 25.50

Less than or equal WHERE a SELECT * FROM products Returns rows with
<=
to <= b WHERE price <= 30; price ≤ 30

Example:

sql

SELECT * FROM products WHERE price > 30 AND unit = 'KG';

Output:

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

product_id unit price

2 KG 42.75

3. Logical Operators

Definition: Combine multiple conditions in WHERE and HAVING clauses.

Operator Description Syntax Example Output

SELECT * FROM Returns rows

All conditions must WHERE a AND
AND products WHERE price > where both
be true b
45 AND unit = 'LITER'; conditions are met

At least one SELECT * FROM Returns rows

OR condition must be WHERE a OR b products WHERE price < where either
true 20 OR unit = 'KG'; condition is true

SELECT * FROM
Returns rows
NOT Negates a condition WHERE NOT a products WHERE NOT
where price ≤ 30
price > 30;

WHERE a SELECT * FROM Returns rows with

Checks if a value is
BETWEEN BETWEEN x products WHERE price price between 20
in a range
AND y BETWEEN 20 AND 40; and 40

SELECT * FROM Returns rows

Pattern matching WHERE a LIKE
LIKE products WHERE unit where unit starts
(with % and _) pattern
LIKE 'L%'; with 'L'

Example:

sql

SELECT * FROM products WHERE price BETWEEN 15 AND 40 AND NOT unit = 'KG';

Output:

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

product_i
unit price
d

1 LITER 25.50

3 GALLON 18.99

4 POUND 35.20

4. Operator to Negate Conditions (NOT)

Definition: Reverses the result of a condition.

Operator Description Syntax Example Output

Negates a WHERE NOT SELECT * FROM products Returns rows

NOT
condition condition WHERE NOT price > 30; where price ≤ 30

Example:

sql

SELECT * FROM products WHERE NOT unit = 'KG';

Output:

product_i
unit price
d

1 LITER 25.50

3 GALLON 18.99

4 POUND 35.20

5 OUNCE 12.49

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

Summary Table of MySQL Operators

Type Operators Usage

Arithmetic +, -, *, /, % Mathematical calculations

Compariso
=, !=, >, <, >=, <= Comparing values
n

Logical AND, OR, NOT, IN, BETWEEN, LIKE Combining conditions

Negation NOT Reversing conditions

These operators are fundamental for querying and filtering data in MySQL. 🚀

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

What is a View in MySQL?

Definition of a View
A view in MySQL is a virtual table that does not store data physically but instead displays results from a
stored SQL query. Views act as dynamic windows into one or more tables, updating automatically when
the underlying data changes.
Key Characteristics:
 Does not occupy storage space (except for the query definition)
 Can simplify complex queries
 Enhances security by restricting data access
 Presents data in a structured way without modifying original tables

Syntax for Creating a View

sql
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example: Creating and Using a View

1. Create a Sample Table
sql
CREATE TABLE employees (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(50) NOT NULL,
department VARCHAR(50),
salary DECIMAL(10,2)
);

INSERT INTO employees (emp_name, department, salary) VALUES

('John Doe', 'HR', 50000),
('Jane Smith', 'IT', 75000),
('Robert Brown', 'Finance', 60000),
('Emily Davis', 'IT', 80000),
('Michael Lee', 'HR', 55000);

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

2. Create a View for IT Department Employees

sql
CREATE VIEW it_employees AS
SELECT emp_id, emp_name, salary
FROM employees
WHERE department = 'IT';
3. Query the View
sql
SELECT * FROM it_employees;
Output:
+--------+-------------+--------+
| emp_id | emp_name | salary |
+--------+-------------+--------+
| 2 | Jane Smith | 75000 |
| 4 | Emily Davis | 80000 |
+--------+-------------+--------+

Key Features of Views

1. Dynamic Updates
o If the original table changes, the view automatically reflects the latest data.
2. Security
o Restrict access to specific columns (e.g., hide salary from non-HR employees).
3. Simplified Queries
o Example: Create a view for monthly payroll reports.

Modifying & Dropping Views

Update a View
sql
ALTER VIEW it_employees AS
SELECT emp_id, emp_name, salary, department
FROM employees
WHERE department = 'IT';
Delete a View
sql
DROP VIEW it_employees;

When to Use Views?

✔ Complex queries (e.g., joins across multiple tables)
✔ Data security (e.g., expose only non-sensitive columns)
✔ Reusable query logic (avoid rewriting the same query repeatedly)
Example:
sql
CREATE VIEW high_salary_employees AS
SELECT emp_name, department, salary

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

FROM employees
WHERE salary > 60000;
Query the View:
sql
SELECT * FROM high_salary_employees;
Output:
+-------------+------------+--------+
| emp_name | department | salary |
+-------------+------------+--------+
| Jane Smith | IT | 75000 |
| Robert Brown| Finance | 60000 |
| Emily Davis | IT | 80000 |
+-------------+------------+--------+

Summary

Feature Description

Purpose Virtual table from a SQL query

Syntax CREATE VIEW view_name AS SELECT ...

Updates Dynamically Yes (changes in base tables reflect in views)

Storage Does not store data physically

Use Cases Simplifying queries, security, reporting

Another example for Creating Views

1. Creating a Simple View
A simple view queries data from a single table without complex operations.
Syntax:
sql
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example:
sql
-- Create a view for active employees
CREATE VIEW active_employees AS
SELECT emp_id, emp_name, department
FROM employees
;
Querying the View:

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

sql
SELECT * FROM active_employees;

2. Creating a Complex View

A complex view involves:
 Joins across multiple tables
 Aggregation functions (GROUP BY, SUM, AVG)
 Subqueries
Syntax:
sql
CREATE VIEW complex_view_name AS
SELECT t1.column1, t2.column2, ...
FROM table1 t1
JOIN table2 t2 ON [Link] = [Link]
WHERE condition
GROUP BY column1;
Example:
sql
-- Create a view showing department-wise average salaries
CREATE VIEW dept_avg_salary AS
SELECT
[Link],
AVG([Link]) AS avg_salary,
COUNT(e.emp_id) AS employee_count
FROM employees e
GROUP BY [Link];
Querying the View:
sql
SELECT * FROM dept_avg_salary;

The Importance of Views in a Multiuser Database Environment

Views play a critical role in managing databases with multiple users by enhancing security, simplifying
access, and improving efficiency. Here's why they are essential:

1. Enhanced Security
Column-Level & Row-Level Data Protection
 Views restrict access to sensitive columns (e.g., hiding salary from non-HR users).
 Filter rows based on user roles (e.g., a manager sees only their team's data).
2. Simplified Access Control
Role-Based Permissions
 Grant permissions on views instead of base tables to limit exposure.
 Avoid giving direct table access to users.

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

3. Data Consistency
Single Source of Truth
 Views standardize how data is presented across users/departments.
 Ensure all users see the same calculated metrics (e.g., monthly reports).
4. Performance Optimization
Pre-Computed Complex Queries
 Store joins, aggregations, or filtered data as views to reduce repetitive processing.
 Improve query performance for frequently accessed data.
5. Logical Data Independence
Shield Users from Schema Changes
 Modify underlying tables without breaking applications that use views.
 Example: Rename a column in the table but keep the view’s interface unchanged.
6. Simplified User Experience
Abstract Complexity
 Hide complex joins or subqueries behind simple views.
 Let users query customer_orders instead of writing 5-table joins.

Summary: Why Views Matter in Multiuser Environments

Benefit Use Case

Hide columns/rows with WHERE and selective column

Security
inclusion.

Access Control Grant view permissions instead of direct table access.

Consistency Standardize data presentation across teams.

Performance Pre-compute complex queries (joins/aggregations).

Abstraction Decouple apps from schema changes.

Compliance Restrict sensitive data access to authorized roles.

Conclusion
Views are essential in multiuser databases to:
✅ Secure data (limit exposure)
✅ Simplify permissions (role-based access)
✅ Standardize reporting (consistent metrics)
✅ Improve performance (reuse complex queries)

What are MySQL Joins?

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

A Join in MySQL combines rows from two or more tables based on a related column, allowing you to
query data from multiple tables in a single operation.

Why Joins are Helpful?

 Combine related data from multiple tables (e.g. orders + customers)

 Avoid data redundancy (normalized databases use joins instead of duplicate data)

 Improve query efficiency (fetch related data in one query instead of multiple)

 Support complex reporting (aggregate data across tables)

Types of MySQL Joins

1. INNER JOIN → Returns only matching rows from both tables.

2. LEFT JOIN (LEFT OUTER JOIN) → Returns all rows from the left table + matched rows from the
right table.

3. RIGHT JOIN (RIGHT OUTER JOIN) → Returns all rows from the right table + matched rows from
the left table.

4. CROSS JOIN → Returns the Cartesian product (all possible row combinations).

1. Definition of INNER JOIN

An INNER JOIN returns only the rows where there is a match in both tables based on the
specified condition. It filters out non-matching rows from both tables.

2. Table Creation & Sample Data

Supplier Table

sql

CREATE TABLE supplier (

supplier_id INT PRIMARY KEY,

supplier_name VARCHAR(50) NOT NULL

);

INSERT INTO supplier (supplier_id, supplier_name) VALUES

(1, 'Supplier A'),

(2, 'Supplier B'),

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

(3, 'Supplier C'),

(4, 'Supplier D');

Order Table

sql

CREATE TABLE orders (

order_id INT PRIMARY KEY,

supplier_id INT,

order_date DATE,

FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id)

);

INSERT INTO orders (order_id, supplier_id, order_date) VALUES

(101, 1, '2023-01-15'),

(102, 2, '2023-02-20'),

(103, 1, '2023-03-10'),

(104, 3, '2023-04-05');

3. INNER JOIN Syntax

sql

SELECT table1.column1, table2.column2, ...

FROM table1

INNER JOIN table2 ON table1.common_column = table2.common_column;

4. Visual Illustration of INNER JOIN

Supplier Table Order Table

+-------------+------------+ +----------+-------------+------------+

| supplier_id | supplier_ | | order_id | supplier_id | order_date |

+-------------+------------+ +----------+-------------+------------+

|1 | Supplier A | | 101 |1 | 2023-01-15 |

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

|2 | Supplier B | | 102 |2 | 2023-02-20 |

|3 | Supplier C | | 103 |1 | 2023-03-10 |

|4 | Supplier D | | 104 |3 | 2023-04-05 |

+-------------+------------+ +----------+-------------+------------+

INNER JOIN Result (Matches supplier_id in both tables)

+-------------+------------+----------+------------+

| supplier_id | supplier_ | order_id | order_date |

+-------------+------------+----------+------------+

|1 | Supplier A | 101 | 2023-01-15 |

|1 | Supplier A | 103 | 2023-03-10 |

|2 | Supplier B | 102 | 2023-02-20 |

|3 | Supplier C | 104 | 2023-04-05 |

+-------------+------------+----------+------------+

*Note: Supplier D (id=4) is excluded because it has no orders.*

5. INNER JOIN Example

sql

SELECT s.supplier_id, s.supplier_name, o.order_id, o.order_date

FROM supplier s

INNER JOIN orders o ON s.supplier_id = o.supplier_id;

Output

+-------------+--------------+----------+------------+

| supplier_id | supplier_name| order_id | order_date |

+-------------+--------------+----------+------------+

|1 | Supplier A | 101 | 2023-01-15 |

|1 | Supplier A | 103 | 2023-03-10 |

|2 | Supplier B | 102 | 2023-02-20 |

|3 | Supplier C | 104 | 2023-04-05 |

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

+-------------+--------------+----------+------------+

6. Key Takeaways

1. INNER JOIN only returns rows with matching values in both tables.

2. Non-matching rows (e.g., Supplier D) are excluded.

3. Syntax: INNER JOIN table ON condition.

4. Used to combine related data from multiple tables efficiently.

MySQL JOIN Types Explained with Examples

1. LEFT JOIN (LEFT OUTER JOIN)

Definition

A LEFT JOIN returns all rows from the left table (first table) and matched rows from the right table. If no
match exists, NULL values are returned for right table columns.

Visual Illustration

Supplier Table (Left) Order Table (Right)

+-------------+------------+ +----------+-------------+------------+

| supplier_id | supplier_ | | order_id | supplier_id | order_date |

+-------------+------------+ +----------+-------------+------------+

|1 | Supplier A | | 101 |1 | 2023-01-15 |

|2 | Supplier B | | 102 |2 | 2023-02-20 |

|3 | Supplier C | | 103 |1 | 2023-03-10 |

|4 | Supplier D | | 104 |3 | 2023-04-05 |

+-------------+------------+ +----------+-------------+------------+

LEFT JOIN Result

+-------------+------------+----------+------------+

| supplier_id | supplier_ | order_id | order_date |

+-------------+------------+----------+------------+

|1 | Supplier A | 101 | 2023-01-15 |

|1 | Supplier A | 103 | 2023-03-10 |

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

|2 | Supplier B | 102 | 2023-02-20 |

|3 | Supplier C | 104 | 2023-04-05 |

|4 | Supplier D | NULL | NULL |

+-------------+------------+----------+------------+

Syntax & Example

sql

SELECT s.supplier_id, s.supplier_name, o.order_id, o.order_date

FROM supplier s

LEFT JOIN orders o ON s.supplier_id = o.supplier_id;

Output

+-------------+--------------+----------+------------+

| supplier_id | supplier_name| order_id | order_date |

+-------------+--------------+----------+------------+

|1 | Supplier A | 101 | 2023-01-15 |

|1 | Supplier A | 103 | 2023-03-10 |

|2 | Supplier B | 102 | 2023-02-20 |

|3 | Supplier C | 104 | 2023-04-05 |

|4 | Supplier D | NULL | NULL |

+-------------+--------------+----------+------------+

2. RIGHT JOIN (RIGHT OUTER JOIN)

Definition

A RIGHT JOIN returns all rows from the right table and matched rows from the left table. If no match
exists, NULL values are returned for left table columns.

Visual Illustration

Supplier Table (Left) Order Table (Right)

+-------------+------------+ +----------+-------------+------------+

| supplier_id | supplier_ | | order_id | supplier_id | order_date |

+-------------+------------+ +----------+-------------+------------+

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

|1 | Supplier A | | 101 |1 | 2023-01-15 |

|2 | Supplier B | | 102 |2 | 2023-02-20 |

|3 | Supplier C | | 103 |1 | 2023-03-10 |

|4 | Supplier D | | 104 |3 | 2023-04-05 |

| NULL | NULL | | 105 |5 | 2023-05-12 |

+-------------+------------+ +----------+-------------+------------+

RIGHT JOIN Result

+-------------+------------+----------+------------+

| supplier_id | supplier_ | order_id | order_date |

+-------------+------------+----------+------------+

|1 | Supplier A | 101 | 2023-01-15 |

|1 | Supplier A | 103 | 2023-03-10 |

|2 | Supplier B | 102 | 2023-02-20 |

|3 | Supplier C | 104 | 2023-04-05 |

| NULL | NULL | 105 | 2023-05-12 |

+-------------+------------+----------+------------+

Syntax & Example

sql

SELECT s.supplier_id, s.supplier_name, o.order_id, o.order_date

FROM supplier s

RIGHT JOIN orders o ON s.supplier_id = o.supplier_id;

Output

+-------------+--------------+----------+------------+

| supplier_id | supplier_name| order_id | order_date |

+-------------+--------------+----------+------------+

|1 | Supplier A | 101 | 2023-01-15 |

|1 | Supplier A | 103 | 2023-03-10 |

|2 | Supplier B | 102 | 2023-02-20 |

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

|3 | Supplier C | 104 | 2023-04-05 |

| NULL | NULL | 105 | 2023-05-12 |

+-------------+--------------+----------+------------+

3. CROSS JOIN

Definition

A CROSS JOIN returns the Cartesian product of both tables (all possible combinations of rows). No join
condition is needed.

Visual Illustration

Supplier Table Order Table

+-------------+------------+ +----------+------------+

| supplier_id | supplier_ | | order_id | order_date |

+-------------+------------+ +----------+------------+

|1 | Supplier A | | 101 | 2023-01-15 |

|2 | Supplier B | | 102 | 2023-02-20 |

+-------------+------------+ +----------+------------+

CROSS JOIN Result

+-------------+------------+----------+------------+

| supplier_id | supplier_ | order_id | order_date |

+-------------+------------+----------+------------+

|1 | Supplier A | 101 | 2023-01-15 |

|1 | Supplier A | 102 | 2023-02-20 |

|2 | Supplier B | 101 | 2023-01-15 |

|2 | Supplier B | 102 | 2023-02-20 |

+-------------+------------+----------+------------+

Syntax & Example

sql

SELECT s.supplier_id, s.supplier_name, o.order_id, o.order_date

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science
DATABASE SYSTEM NOTES DIT (PART-II)

FROM supplier s

CROSS JOIN orders o;

Output

+-------------+--------------+----------+------------+

| supplier_id | supplier_name| order_id | order_date |

+-------------+--------------+----------+------------+

|1 | Supplier A | 101 | 2023-01-15 |

|1 | Supplier A | 102 | 2023-02-20 |

|1 | Supplier A | 103 | 2023-03-10 |

|1 | Supplier A | 104 | 2023-04-05 |

|2 | Supplier B | 101 | 2023-01-15 |

|2 | Supplier B | 102 | 2023-02-20 |

|2 | Supplier B | 103 | 2023-03-10 |

|2 | Supplier B | 104 | 2023-04-05 |

|3 | Supplier C | 101 | 2023-01-15 |

| ... (all combinations) ...

+-------------+--------------+----------+------------+

Key Takeaways

1. INNER JOIN: Only matched rows.

2. LEFT JOIN: All left table rows + matched right rows.

3. RIGHT JOIN: All right table rows + matched left rows.

4. CROSS JOIN: All possible row combinations (Cartesian product).

Compiled by Sir Touqeer National Academy Abbottabad

MS computer Science