💻

Database concepts
Database : A database is a tabular organization of data which comprises of
rows (record) and columns (attributes)
Each record contains values for the corresponding attributes.

💡 Abstraction
Data abstraction in dbms refers to the process of hiding irrelevant
details from the user.

Consider a university database system. A student accessing their

grades doesn't need to see the entire database structure. Instead,
they only view their own grades for each course. This is abstraction in
DBMS - hiding complex details and showing only relevant information
to the user.

Data/character : the smallest unit of file organization.

Field : A field is a set of characters/data which are used together to represent
specific data elements. It is also termed as data-items.
Record is a collection of fields.

A collection of logically related records is called a file. A file is also termed

as a table or a relation.

Database concepts 1
Database management system (DBMS)
DBMS : it is a set of programs that enables the users to define, create and
maintain the database and provide controlled access to this database.

Advantages :

Elimination of data redundancy : Duplication of data leads to a wastage in

storage space. A DBMS eliminates data redundancy (duplication of data) by
integrating the files so that multiple copies of the same data are not stored

Data consistency : changes made at one place are reflected throughout.

Sharing of data

Reduced programming efforts

Improved data integrity : Data integrity refers to the validity and consistency
of stored data. The system itself checks for the correct information to be
entered by the user in the correct format.

Privacy and Security

Improved backup and recovery

Data model : integrated collection of conceptual tools that can be used to

describe the structure of the database along with the appropriate data types,
relationships and constraints required to be applied on the data.

Database concepts 2
There are various data models and one of them is RDBMS or relational
database model.

Data models implement abstraction.

Relational database model

The relational model, organizes data in the form of independent tables (
consisting of rows and columns ) that are related to each other.

A relation is a two-dimensional table which contains number of rows (tuples)

and columns (attributes).

Attribute : in a relational table, an attribute is a set of values of a particular type.

It represents a column of a table.
Tuple : each row in a table is called tuple/row/record. A single entry to the table
is called a record.

📌 Degree : denotes the number of columns/attributes.

Cardinality : denotes the number of rows/tuples.

Domain of a relation : it is the set of all possible values an attribute can have.

For example : in the table student, the attribute gender has a domain value of
two as it can have only two possible values : male or female.

Database keys
Primary keys : A primary key is an attribute or a collection of attributes which
uniquely identify tuples within the table.

It is always unique in nature.

It cannot be left null or re-declared.

One table can have only one primary key

Candidate keys : refers to all the attributes in a table that are capable of
becoming a primary key or are candidates.

Database concepts 3
Alternate Keys: A candidate key that is not a primary key or in other words, any
attribute that is a candidate for the primary key but is not a primary key , is an
alternate key.

Out of the multiple candidate keys , only one is selected to be the primary key.
The rest are alternate keys.

Foreign key : it’s a key found in a table whose value is derived from the primary
table of another table. It is the primary key of a table that is placed into a
related table to present the relationship between these tables.

MySQL :
MySQL is an open-source and freely available relational database management
system (RDBMS) that uses Structured Query Language (SQL). It is the most
common language used to create, operate, update, manipulate and
communicate with a database.

Advantages of SQL:
Easy to use : very easy to learn and use

Large volume of database can be handled easily.

No coding required.

SQL can be easily linked to other high-level programming languages.

Portable : Compatible with various database programs.

Not a case sensitive language.

SQL datatypes:

1. Numeric Data Types:

INT: Stores integers (whole numbers). Commonly used for IDs and
counting.

Example: INT(11)

FLOAT/DOUBLE: Used for floating-point numbers (numbers with decimals).

FLOAT is for smaller precision, and DOUBLE is for higher precision. Each float

occupies 4 bytes.

Example: FLOAT(10, 2) for 2 decimal places.

Database concepts 4
DECIMAL: Stores exact numeric values (fixed-point numbers), often used
for financial data to avoid rounding issues.

Example: DECIMAL(10, 2) for money.

2. String (Character) Data Types:

CHAR: Fixed-length strings (space-padded). Used when the size is
consistent.

This data type stores x number of characters in the string which has a fixed
length.

Example: CHAR(10) always stores 10 characters.

VARCHAR: Variable-length strings. Efficient for storing text that can vary in
size.

Example: VARCHAR(255) stores up to 255 characters.

3. Date and Time Data Types:

DATE: Stores dates in the format YYYY-MM-DD .

Example: DATE (e.g., '2024-10-06').

TIME: Stores time only in HH:MM:SS format.

Example: TIME(14:23:00)

CHAR VARCHAR

Provides variable length memory storage.

Provides fixed length memory storage. It
It specifies a variable length character
specifies a fixed length character string.
string.

Can store a maximum of 255 characters. Can store up to 65,535 characters.

It is used when columns are expected to be It is used when columns are expected to
of fixed length. vary considerably in size.

If a value entered is shorter than its length x,

No blanks are added.
blanks are added.

Database concepts 5
It takes memory space of 1 byte per
It takes memory space of 1 byte per
character , +2 byte to hold variable
character.
length information.

Search operation is faster. Search operation is slower.

Constraints in SQL:
Constraints are set of rules created to apply data validations. They ensure
accuracy, correctness and reliability of data.

Constraint Description

Primary key The field or column which uniquely identifies a primary key.

Ensures that column cannot have null values. NULL means

NOT NULL
missing/unknown/not applicable value.

UNIQUE Ensures all the values entered are distinct.

DEFAULT Assigns a default value if the value is not provided or given.

The column which refers to value of an attribute defined as the

FOREIGN KEY
primary key in another table.

SQL statements:
A keyword refers to an individual SQL element that has special meaning in SQL.

for example : SELECT and FROM are keywords.

A clause is a distinct logical part of an SQL statement which are a collection of
keywords.

for example : SELECT EMPNO, FROM TABLE WHERE SALARY>20000 are

clauses.

A statement or a command is a combination of two or more clauses.

All statements in SQL terminates with a semicolon (;). Also SQL, is not case
sensitive.

Data Definition Language (DDL): Used to define and manage database

schema (structure). It contains necessary statements for creating,
manipulating, altering and deleting the table.

Database concepts 6
CREATE: Creates a new table or database.
→ Create table is the most extensively used DDL command.

CREATE DATABASE school;

CREATE TABLE students (id INT, name VARCHAR(50));

USE: to select and open an already existing database.

USE students;

SHOW: to display tables or databases

SHOW TABLES;
SHOW DATABASES;

DROP: Deletes a table or database.

DROP TABLE students;

DROP DATABASE SCHOOL; #database deleted

DESCRIBE : or desc is used to view the structure of the table along with
the name of the columns, data type of the columns and constraints
applied to the column.

DESCRIBE student;

ALTER TABLE COMMAND

It is used to perform the following tasks:

Add a column to the existing database.

Rename any existing column.

Change the data type of any column or to modify its size.

Remove or physically delete a column.

a) Adding a column to an existing table :

Database concepts 7
ALTER TABLE <table name> ADD (<column name><data type>
[size] );
mysql> ALTER TABLE STUDENT ADD (MOBILE_NO INTEGER);

The column will be created with NULL as values. UPDATE command

can be used to change it.
b) Adding a column with default value :
mysql> ALTER TABLE Student ADD (CITY CHAR(6) DEFAULT “NEW DELHI”);

c) Modifying an existing column definition :

ALTER TABLE <table name> modify (<column name><data type>
[size] );
mysql> ALTER TABLE STUDENT MODIFY (name char(30));

📖 To remove a primary key constraint either use the

previous method or use
mysql> alter table student drop primary key;

To add a primary key constraints use:

mysql> alter table student add primary key(roll_no);

d) Renaming a column :

ALTER TABLE <table name> change [COLUMN] <old name><new

name> column_definition;
mysql> ALTER TABLE STUDENT CHANGE COLUMN CITY STATE VARCHAR(10);

This shall rename the city column to state.

e) Removing a column :

ALTER TABLE <table name> drop <column name>;

mysql> ALTER TABLE STUDENT drop Phone_no;

This shall remove the phone number column.

Database concepts 8
Data Manipulation Language (DML): Used to manipulate (modify) data in
the database.

INSERT: Adds new records to a table.

INSERT INTO students VALUES (1, 'John',"M","XII C");

#inserting specific values

INSERT INTO STUDENTS(ROLLNO,NAME) VALUES(1,"MANOJ");
#gender , class holds value null

UPDATE: Modifies existing records in a table

UPDATE students SET name = 'Alice' WHERE id = 1;

#updating multiple columns

update student set name="Valuson",marks="69" where id
=1;

#updating to null
update student set brain=NULL where id=1;

#updating using expression

update student set mark=mark+10 where (id=1 or id=1
0);

DELETE: Removes records from a table.

DELETE FROM students WHERE id = 1;

#This deletes all rows (when you don't include where

clause.
DELETE FROM STUDENT;

TRUNCATE: Delete all the rows from the table

TRUNCATE TABLE <Table name>;

Data Query Language (DQL): Used to retrieve data from the database.

Database concepts 9
SELECT: Fetches records from one or more tables.
Asterisks “*” means all. Select * means display all columns from a
relation
Select * from studenets;

📖 SELECT DISTINCT <COLUMN_NAME> FROM <TABLE_NAME>;

“DISTINCT” is used to remove duplicate rows from the result set
of a select statement.

Differences between DDL and DML commands :

DDL Commands DML Commands

DDL stands for data definition language DML stands for data manipulation language

These commands are used to perform

These commands are used to manipulate
tasks related to data definition , i.e related
data, i.e records or rows in a table or
to the structure of database objects
relation
(relations, databases)

examples : create, alter, drop etc: examples : insert into , update, delete etc:

DML commands are further classified into

two types :
DDL is not further classified
a) Procedural DMLS
b) Non-Procedural DMLS

SQL Operators:
Operation & function Description

Used in arithmetic calculations and expressions.

Arithmetic Operators
+, -, *, /, %, **

Used in conditional expressions

Comparison/Relational
operators
=, >, <, <=, >=, <>

Used in conditional expressions

Logical operators
AND, OR, NOT

Database concepts 10
Operation & function Description

Checks whether an attribute value is within a range or

not.
Usage :

select roll_no, marks from student where marks

Between/Not between
between 80 and 100; #includes the end points i.e
80,100

select roll_no, marks from student where marks not

between 80 and 100;

Checks if an attribute value is null or not.

Usage :
Is null/is not null

select * from students where roll is not null.

Checks whether an attribute matches a given string

pattern or not.

a) Percent (%) : Matches any string.

b) Underscore (_
Like/ Not Like ) : Matches any one character.

Usage :
select * from students where name like “a%”;
select * from students where name like “a__d
”;

Checks if an attribute value matches any value within a

given list or not.
In/ Not in Usage :

select * from students where roll_no in (1,2,3,5,7,9);

AGGREGATE FUNCTIONS

Returns the total number of records with non-null

count()
values for a given column

min() Returns lowest attribute found in a given column.

max() Returns highest attribute found in a given column

sum() Returns sum of all values from a given column

avg() Returns average of all value from a given column

Database concepts 11
📖 NOTE:

While comparing character, date, time using relational operators, it

should be enclosed within quotation marks.

ORDER OF PRESCENDENCE : NOT, AND, OR

Comments in SQL:
Single line: use # or - -
Multiline : use /* */

Illustration :

SELECT ROLLNO,NAME,STREAM
/* THIS STATEMENT SHALL DISPLAY ALL THE RECORDS OF THOSE ST
UDENTS WHO ARE IN SCIENCE AND
HAVE MARKS MORE THAN 75% */
FROM STUDENT #STUDENT TABLE IN USE
WHERE STREAM='SCIENCE' AND MARKS>75; --CONDITION FOR SELECT
ION

SQL Aliases:
SQL aliases are used to give a temporary name to a database table or column in
a table.
→ Column Aliases : gives column headings in the query result set.
→ Table Aliases : shorten table name by giving an easy alternate name, making
it easier to perform joins.

Syntax:
Select <column1> as <alias> from <table name> as <alias>;

Select name as "NAMEE", DOB as "Date of Birth" from student as stud;

Database concepts 12
If the alias name contains spaces, you must enclose it in quotations and the
alias is only valid within the scope of the SQL statement.

📖 SORTING IN SQL:
Can be done by using the order by clause.
select * from student order by roll <asc/desc>;

Default is ascending.

Group By:
Group by clause can be used in a select statement to collect data across
multiple records and group the results by one or more columns.
Usage:
select name, stream from student where marks>90 group by stream;

📖 Having clause :
It is used in combination with the group by clause and it is used to
allow aggregate functions to be used along with the specified
condition.

Where vs Having :

Where clause works in respect to the whole table whereas having

works only on group by.
If
where and having both are present, where will be executed first.
Where is used to put a condition on individual row of a table and
having is used to put a condition on an individual group formed by
group by clause.

Database concepts 13
SQL Joins
Cartesian Product / Cross Product : It is a binary product and is denoted by (x).

Degree of the two relations is the sum of the degrees of two relations on which
it was performed.
Number of tuples in the new relation is the product of the cardinality of two
relations on which it was performed.

Syntax :

Select <columns> from table1,table2

mysql> select * from student,fees;

Equi join : it is a simple SQL join which uses the equal to sign as a comparison
operator for defining a relationship between the two tables on the basis of a
common field, i.e primary and foreign key.

Syntax :

Select <columns> from table1, table2 where table1.primary_key = table2.foreign_key ;

mysql> select A.rollno, A.Name, B.Fee

from student A, Fees B
where A.rollno = B.rollno;

Natural Join : It is similiar to Equi join expect that duplicate columns are
eliminated in Natural Join that would otherwise appear in Equi Join. In this the
join condition is not required and it automatically joins based on the common
value.
Syntax :

Select <columns> from table1 natural join table2

mysql> select A.rollno, A.Name, B.Fee

from student A natural join Fees B;

Database concepts 14
Database concepts 15