Introduction to RDBMS

A Relational Database Management System (RDBMS) is a program that allows you to create,
update, and administer a relational database. Most modern databases (SQL Server, Oracle,
MySQL) are based on the relational model.

 The Table Structure: In an RDBMS, data is organized into Tables (Relations). Each
table is divided into Rows (Tuples) and Columns (Attributes).
 Data Integrity: RDBMS uses constraints (like Primary Keys, Foreign Keys, and Unique
constraints) to ensure data remains accurate and consistent across the system.

Term Meaning
Relation Table
Tuple Row
Attribute Column
Domain Set of values
Degree No. of columns
Cardinality No. of rows
Primary Key Unique identifier
Foreign Key Reference to another table

Dr. E.F. Codd’s Rules

In 1970, Dr. Edgar F. Codd published 12 rules (actually 13, numbered 0-12) that define what
constitutes a true RDBMS. A system that does not satisfy these rules is not considered fully
relational.

Rule 1: The Information Rule

All information in the database is represented in exactly one way: by values in column positions
within rows of tables. There should be no "hidden" pointers or links; everything must be visible
in a table format.

Rule 2: Guaranteed Access Rule

Every single data element (value) in a relational database is guaranteed to be logically accessible
by providing a combination of the Table Name, Primary Key, and Column Name.

Rule 3: Systematic Treatment of Null Values

The DBMS must allow for Null values to represent missing or inapplicable information in a
systematic way, independent of the data type. These Nulls must be handled differently from a
zero or a blank string.

Rule 4: Active Online Catalog

The description of the database (the metadata) must be stored in the same relational format as the
actual data. This "Data Dictionary" or "Catalog" should be queryable by authorized users using
the same SQL language used for data.

Rule 5: Comprehensive Data Sublanguage Rule

A system may support various languages, but it must support at least one well-defined language
(like SQL) that allows for data definition, view definition, data manipulation, integrity
constraints, and transaction boundaries.

Rule 6: View Updating Rule

All views that are theoretically updatable must also be updatable by the system. If the underlying
base tables can be changed, the "view" of that data should allow for those changes as well.

Rule 7: High-Level Insert, Update, and Delete

The system must support the ability to handle an entire "set" of data at once. It shouldn't just be
able to delete one row; it should be able to delete all rows that meet a certain criteria in a single
command.

Rule 8: Physical Data Independence

Applications and ad hoc queries should remain logically unaffected when changes are made to
the physical storage (how the data is stored on the disk) or the access methods.

Rule 9: Logical Data Independence

The application should not be affected by changes to the logical structure of the tables (like
splitting a table or joining two) as long as the information content remains the same. This is
usually achieved through views.

Rule 10: Integrity Independence

Integrity constraints (like "Age must be > 18") must be stored in the Data Dictionary, not in the
application programs. This ensures the database itself enforces the rules.

Rule 11: Distribution Independence

The end-user should not be able to tell whether the data is stored on a single local disk or
distributed across multiple networks. The SQL should look the same regardless of where the data
physically resides.

Rule 12: Nonsubversion Rule

If the system has a low-level (record-at-a-time) interface, that interface cannot be used to bypass
the security or integrity constraints defined in the high-level relational language (SQL).

Rule Category Focus

Data Representation Rules 1, 3, 4

Data Access Rules 2, 7

Data Independence Rules 8, 9, 11

Integrity & Security Rules 10, 12

3. Normalization
Normalization is an important process in database design that helps improve the database's
efficiency, consistency, and accuracy. It makes it easier to manage and maintain the data and
ensures that the database is adaptable to changing business needs.

Database normalization is the process of organizing the attributes of the database to reduce or
eliminate data redundancy (having the same data but at different places).

Data redundancy unnecessarily increases the size of the database as the same data is repeated in
many places. Inconsistency problems also arise during insert, delete, and update operations.
In the relational model, there exist standard methods to quantify how efficient a database is. These
methods are called normal forms, and there are algorithms to convert a given database into normal
forms.

Normalization generally involves splitting a table into multiple ones, which must be linked each
time a query is made requiring data from the split tables.

Before Normalization: The table is prone to redundancy and anomalies (insertion, update, and
deletion).
After Normalization: The data is divided into logical tables to ensure consistency, avoid
redundancy and remove anomalies making the database efficient and reliable.

Problems in the Employee_Department Relation

1. Insertion Anomaly: If a new department is created but no employee is assigned to it yet, we

cannot store its location because we need an employee record to insert.
2. Update Anomaly: If the location of the HR department changes, we must update it in multiple
rows (for both Nick Wise and Lily Case). If one row is missed, the data becomes inconsistent.
3. Deletion Anomaly: If all employees in the IT department leave, we lose the department
information, including its location.
4. Data Redundancy: The department location is repeated for every employee in the same
department.

Need of Normalization

The primary objective for normalizing the relations is to eliminate the below anomalies. Failure to
reduce anomalies results in data redundancy, which may threaten data integrity and cause
additional issues as the database increases. Normalization consists of a set of procedures that assist
you in developing an effective database structure.

 Insertion Anomalies: Insertion anomalies occur when it is not possible to insert data into a
database because the required fields are missing or because the data is incomplete. For
example, if a database requires that every record has a primary key, but no value is provided
for a particular record, it cannot be inserted into the database.

 Deletion anomalies: Deletion anomalies occur when deleting a record from a database and
can result in the unintentional loss of data. For example, if a database contains information
about customers and orders, deleting a customer record may also delete all the orders
associated with that customer.

 Updation anomalies: Updation anomalies occur when modifying data in a database and can
result in inconsistencies or errors. For example, if a database contains information about
employees and their salaries, updating an employee’s salary in one record but not in all related
records could lead to incorrect calculations and reporting.
Features of Database Normalization

 Elimination of Data Redundancy: One of the main features of normalization is to eliminate

the data redundancy that can occur in a database. Data redundancy refers to the repetition of
data in different parts of the database. Normalization helps in reducing or eliminating this
redundancy, which can improve the efficiency and consistency of the database.
 Ensuring Data Consistency: Normalization helps in ensuring that the data in the database is
consistent and accurate. By eliminating redundancy, normalization helps in preventing
inconsistencies and contradictions that can arise due to different versions of the same data.

 Simplification of Data Management: Normalization simplifies the process of managing data

in a database. By breaking down a complex data structure into simpler tables, normalization
makes it easier to manage the data, update it, and retrieve it.

 Improved Database Design: Normalization helps in improving the overall design of the
database. By organizing the data in a structured and systematic way, normalization makes it
easier to design and maintain the database. It also makes the database more flexible and
adaptable to changing business needs.

 Avoiding Update Anomalies: Normalization helps in avoiding update anomalies, which can
occur when updating a single record in a table affects multiple records in other tables.
Normalization ensures that each table contains only one type of data and that the relationships
between the tables are clearly defined, which helps in avoiding such anomalies.

 Standardization: Normalization helps in standardizing the data in the database. By organizing

the data into tables and defining relationships between them, normalization helps in ensuring
that the data is stored in a consistent and uniform manner.

Normal Forms in DBMS

Normal Forms Description of Normal Forms

First Normal A relation is in first normal form if every attribute in that relation is single-
Form (1NF) valued attribute.

Second A relation that is in First Normal Form and every non-primary-key attribute is
Normal Form fully functionally dependent on the primary key, then the relation is in Second
(2NF) Normal Form (2NF).

A relation is in the third normal form, if there is no transitive dependency for

non-prime attributes as well as it is in the second normal form. A relation is in
3NF if at least one of the following conditions holds in every non-trivial
function dependency X –> Y.
Third Normal  X is a super key.
Form (3NF)  Y is a prime attribute (each element of Y is part of some candidate key).
 Normal Forms Description of Normal Forms

For BCNF the relation should satisfy the below conditions

Boyce-Codd  The relation should be in the 3rd Normal Form.
Normal Form  X should be a super-key for every functional dependency (FD) X−>Y in a
(BCNF) given relation.

Fourth A relation R is in 4NF if and only if the following conditions are satisfied:
Normal Form  It should be in the Boyce-Codd Normal Form (BCNF).
(4NF)  The table should not have any Multi-valued Dependency.

A relation R is in 5NF if and only if it satisfies the following conditions:

Fifth Normal  R should be already in 4NF.
Form (5NF)  It cannot be further non loss decomposed (join dependency)

4. Introduction to SQL, Commands, and Datatypes

SQL (Structured Query Language) is the standard interface for RDBMS.

SQL Commands | DDL, DQL, DML, DCL and TCL Commands

 SQL commands are fundamental building blocks used to perform given operations on database.
The operations include queries of data. creating a table, adding data to tables, dropping the table,
modifying the table and set permission for users.
SQL Commands are mainly categorized into five categories: SQL Commands
1. DDL - Data Definition Language
DDL (Data Definition Language) consists of SQL commands that can be used for defining,
altering and deleting database structures such as tables, indexes and schemas. It simply deals
with descriptions of the database schema and is used to create and modify the structure of
database objects in the database

Command Description Syntax

Create database or its objects CREATE TABLE table_name

CREATE (table, index, function, views, store (column1 data_type, column2
procedure and triggers) data_type, ...);

DROP Delete objects from the database DROP TABLE table_name;

ALTER TABLE table_name

ALTER Alter the structure of the database ADD COLUMN column_name
data_type;

Remove all records from a table,

TRUNCATE TABLE
TRUNCATE including all spaces allocated for
table_name;
the records are removed
Command Description Syntax

Add comments to the data COMMENT ON TABLE

COMMENT
dictionary table_name IS 'comment_text';

RENAME TABLE
Rename an object existing in the
RENAME old_table_name TO
database
new_table_name;

Example:

CREATE TABLE employees (

employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE
);
In this example, a new table called employees is created with columns for employee ID, first
name, last name and hire date.

2. DQL - Data Query Language

DQL is used to fetch data from the database. The main command is SELECT, which retrieves
records based on the query. The output is returned as a result set (a temporary table) that can be
viewed or used in applications.

Command Description Syntax

It is used to retrieve data from the SELECT column1, column2, ...FROM

SELECT database table_name WHERE condition;

Indicates the table(s) from which SELECT column1

FROM
to retrieve data. FROM table_name;

SELECT column1
Filters rows before any grouping FROM table_name
WHERE
or aggregation WHERE condition;
Command Description Syntax

SELECT column1,
GROUP Groups rows that have the same AVG_FUNCTION(column2)
BY values in specified columns. FROM table_name
GROUP BY column1;

SELECT column1,
AVG_FUNCTION(column2)
HAVING Filters the results of GROUP BY FROM table_name
GROUP BY column1
HAVING condition;

SELECT DISTINCT column1, column2,

Removes duplicate rows from ...
DISTINCT
the result set FROM table_name;

SELECT column1
ORDER Sorts the result set by one or more
FROM table_name
BY columns
ORDER BY column1 [ASC | DESC];

By default, it sorts in ascending SELECT * FROM table_name LIMIT

LIMIT number;
order unless specified as DESC

Note: DQL has only one command, SELECT. Other terms like FROM, WHERE, GROUP BY,
HAVING, ORDER BY, DISTINCT and LIMIT are clauses of SELECT, not separate commands.

Example:

SELECT first_name, last_name, hire_date

FROM employees
WHERE department = 'Sales'
ORDER BY hire_date DESC;
This query retrieves employees first and last names, along with their hire dates, from the
employees table, specifically for those in the 'Sales' department, sorted by hire date.

3. DML - Data Manipulation Language

DML commands are used to manipulate the data stored in database tables. With DML, you can
insert new records, update existing ones, delete unwanted data or retrieve information.
Command Description Syntax

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

INSERT Insert data into a table
VALUES (value1, value2, ...);

Update existing data UPDATE table_name SET column1 = value1,

UPDATE
within a table column2 = value2 WHERE condition;

Delete records from a

DELETE DELETE FROM table_name WHERE condition;
database table

Example:

INSERT INTO employees (first_name, last_name, department)

VALUES ('Jane', 'Smith', 'HR');
This query inserts a new record into employees table with first name 'Jane', last name 'Smith'
and department 'HR'.

4. DCL - Data Control Language

DCL (Data Control Language) includes commands such as GRANT and REVOKE which mainly
deal with the rights, permissions and other controls of the database system. These commands are
used to control access to data in the database by granting or revoking permissions.

Command Description Syntax

Assigns new privileges to a user GRANT privilege_type

account, allowing access to [(column_list)] ON [object_type]
GRANT
specific database objects, actions object_name TO user [WITH
or functions. GRANT OPTION];

Removes previously granted

REVOKE [GRANT OPTION FOR]
privileges from a user account,
privilege_type [(column_list)] ON
REVOKE taking away their access to
[object_type] object_name FROM
certain database objects or
user [CASCADE];
actions.
Example:

GRANT SELECT, UPDATE ON employees TO user_name;

This command grants the user user_name the permissions to select and update records in the
employees table.

5. TCL - Transaction Control Language

Transactions group a set of tasks into a single execution unit. Each transaction begins with a
specific task and ends when all the tasks in the group are successfully completed. If any of the
tasks fail, transaction fails. Therefore, a transaction has only two results: success or failure.

Command Description Syntax

BEGIN BEGIN TRANSACTION

Starts a new transaction
TRANSACTION [transaction_name];

Saves all changes made during

COMMIT COMMIT;
the transaction

Undoes all changes made

ROLLBACK ROLLBACK;
during the transaction

Creates a savepoint within the

SAVEPOINT SAVEPOINT savepoint_name;
current transaction

Example:

BEGIN TRANSACTION;
UPDATE employees SET department = 'Marketing' WHERE department = 'Sales';
SAVEPOINT before_update;
UPDATE employees SET department = 'IT' WHERE department = 'HR';
ROLLBACK TO SAVEPOINT before_update;
COMMIT;
In this example, a transaction is started, changes are made and a savepoint is set. If needed, the
transaction can be rolled back to the savepoint before being committed.
 SQL Data Types

 In SQL, each column must be assigned a data type that defines the kind of data it can store, such
as integers, dates, text, or binary values. Choosing the correct data type is crucial for data
integrity, query performance and efficient indexing.

1. Numeric Data Types

Numeric data types are fundamental to database design and are used to store numbers, whether
they are integers, decimals or floating-point numbers. These data types allow for mathematical
operations like addition, subtraction, multiplication and division, which makes them essential
for managing financial, scientific and analytical data.

Exact Numeric Datatype

Exact numeric types are used when precise numeric values are needed, such as for financial data,
quantities, and counts. Some common exact numeric types include:

Data Type Description Range

-9,223,372,036,854,775,808 to
BIGINT Large integer numbers
9,223,372,036,854,775,807
 Data Type Description Range

INT Standard integer values -2,147,483,648 to 2,147,483,647

SMALLINT Small integers -32,768 to 32,767

TINYINT Very small integers 0 to 255

Exact fixed-point numbers

DECIMAL -10^38 + 1 to 10^38 - 1
(e.g., for financial values)

Similar to DECIMAL, used for

NUMERIC -10^38 + 1 to 10^38 - 1
precision data

Example:
CREATE TABLE Product_Sales (
ProductID INT PRIMARY KEY,
Quantity SMALLINT,
UnitPrice DECIMAL(10,2),
TotalAmount DECIMAL(10,2)
);

Approximate Numeric Datatype

These types are used to store approximate values, such as scientific measurements or large ranges
of data that don't need exact precision.

Data Type Description Range

FLOAT Approximate numeric values -1.79E+308 to 1.79E+308

REAL Similar to FLOAT, but with less precision -3.40E+38 to 3.40E+38

Example:
CREATE TABLE Measurements (
SensorID INT,
Temperature FLOAT,
Humidity REAL
);

2. Character and String Data Types

Character data types are used to store text or character-based data. The choice between fixed-
length and variable-length data types depends on the nature of your data.

Data Type Description

The maximum length of 8000 characters. (Fixed-Length non-Unicode

Char Characters)

The maximum length of 8000 characters. (Variable-Length non-Unicode

Varchar Characters)

The maximum length of 2^31 - 1 characters(SQL Server 2005 only).

Varchar(max)
(Variable Length non-Unicode data)

The maximum length of 2,127,483,647 characters(Variable Length non-

Text Unicode data)

Example:
CREATE TABLE Employee_Info (
EmpID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName CHAR(30),
Bio TEXT
);

Unicode Character String Data Types

Unicode data types are used to store characters from any language, supporting a wider variety
of characters. These are given in below table.

Data Type Description

The maximum length of 4000 characters(Fixed-Length Unicode

Nchar Characters)

The maximum length of 4000 characters.(Variable-Length Unicode

Nvarchar Characters)

The maximum length of 2^31 - 1 characters(SQL Server 2005 only).

Nvarchar(max) (Variable Length Unicode data)

Example:
CREATE TABLE International_Users (
UserID INT PRIMARY KEY,
FullName NVARCHAR(100),
Country NCHAR(50)
);

3. Date and Time Data Type

SQL provides several data types for storing date and time information. They are essential for
managing timestamps, events and time-based queries. These are given in the below table.

Data Type Description Storage

Size

DATE stores the data of date (year, month, day) 3 Bytes

TIME stores the data of time (hour, minute,second) 3 Bytes

store both the data and time (year, month, day, hour, minute,
DATETIME 8 Bytes
second)

Example:
CREATE TABLE Orders (
 OrderID INT PRIMARY KEY,
OrderDate DATE,
OrderTime TIME,
ShippedAt DATETIME
);

4. Binary Data Types in SQL

Binary data types are used to store binary data such as images, videos or other file types. These
include

Data Type Description Max Length

Binary Fixed-length binary data. 8000 bytes

VarBinary Variable-length binary data. 8000 bytes

Image Stores binary data as images. 2,147,483,647 bytes

Example:
CREATE TABLE Product_Images (
ImageID INT PRIMARY KEY,
ImageName VARCHAR(100),
ImageData VARBINARY(MAX)
);

5. Boolean Data Type in SQL

The BOOLEAN data types are used to store logical values, typically TRUE or FALSE. It is
commonly used for flag fields or binary [Link] SQLite, there is no
separate BOOLEAN or BIT data type. Instead, boolean values are stored using INTEGER,
where:
 1 represents TRUE
 0 represents FALSE
Example:
CREATE TABLE User_Status (
UserID INT PRIMARY KEY,
IsActive INTEGER,
 IsVerified INTEGER
);

6. Special Data Types

SQL also supports some specialized data types for advanced use cases:
 XML Data Type: Used to store XML data and manipulate XML structures in the database
Example:
CREATE TABLE XML_Records (
RecordID INT PRIMARY KEY,
ConfigData XML
);
 Spatial Data Type (Geometry): stores planar spatial data, such as points, lines, and
polygons, in a database table.
Example:
CREATE TABLE Locations (
LocationID INT PRIMARY KEY,
Area GEOMETRY
);

5. Introduction to SQL*Plus and Formatting

SQL*Plus is an interactive command-line tool specifically for Oracle Databases. While SQL is
the language, SQL*Plus is the "editor" or environment where you run that language.

Formatting Commands:

These commands improve the readability of your reports without affecting the actual data:

 COLUMN [name] HEADING 'Title': Renames the display heading of a column.

 SET PAGESIZE [n]: Determines how many rows display before repeating headings.
 SET LINESIZE [n]: Sets the width of the page.
 BREAK ON [column]: Suppresses duplicate values in a column to create a "grouped"
look.

6. SQL vs. SQL*Plus

Understanding the difference is crucial for any database administrator.
Feature SQL SQL*Plus

Definition A standard language for data. A proprietary Oracle tool.

Commands are stored in the Commands are not stored; they exist for
Persistence
database. the session.

Commands (like SET) do not require a

Punctuation Requires a semicolon (;) to run.
semicolon.

Report formatting and environment

Purpose Data manipulation and definition.
control.

Keyword Commands are placed in the SQL

Commands are not placed in the buffer.
Buffer buffer.