UNIT II Topics:

Relational Model: Introduction to relational model, concepts of domain, attribute,

tuple, relation, importance of null values, constraints (Domain, Key constraints,
integrity constraints) and their importance BASIC SQL: Simple Database schema,
data types, table definitions (create, alter), different DML operations (insert,
delete, update), basic SQL querying (select and project) using where clause,
arithmetic & logical operations, SQL functions(Date and Time, Numeric, String
conversion).

 Relational Model: Introduction to relational model

 The Relational Model organizes data using tables (relations) consisting of
rows and columns.
 The relational model represents how data is stored and managed in
Relational Databases where data is organized into tables, each known as a
relation.
 Each row of a table represents an entity or record and each column
represents a particular attribute of that entity.
 The relational model transforms conceptual designs from ER diagrams into
implementable structures. These structures are used in relational
database systems like Oracle SQL and MySQL.

 Key Terms in the Relational Model

1. Attribute: Attributes are the properties that define an entity. For example,
ROLL_NO, NAME, ADDRESS etc.
2. Relation Schema: A relation schema defines the structure of the relation
and represents the name of the relation with its attributes. For example,
STUDENT (ROLL_NO, NAME, ADDRESS, PHONE and AGE) is the relation
schema for STUDENT. If a schema has more than 1 relation it is called
Relational Schema.
3. Tuple: A Tuple represents a row in a relation. Each tuple contains a set of
attribute values that describe a particular entity. For example, (1, RAM,
DELHI, 9455123451, 18) is a tuple in the STUDENT table.
 4. 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.
5. Degree: The number of attributes in the relation is known as the degree
of the relation. For example, The STUDENT relation has a degree of 5, as it
has 5 attributes.
6. Cardinality: The number of tuples in a relation is known as cardinality.
For example, The STUDENT relation defined above has cardinality 4.
7. NULL Values: The value which is not known or unavailable is called a
NULL value. It is represented by NULL. For example, PHONE of STUDENT
having ROLL_NO 4 is NULL.
 Types of Keys in the Relational Model
1. Primary Key: A Primary Key uniquely identifies each tuple in a relation. It
must contain unique values and cannot have NULL values.
2. Candidate Key: A Candidate Key is a set of attributes that can uniquely
identify a tuple in a relation.
3. Super Key: A Super Key is a set of attributes that can uniquely identify a
tuple.
4. Foreign Key: A Foreign Key is an attribute in one relation that refers to
the primary key of another relation.
5. Composite Key: A Composite Key is formed by combining two or more
attributes to uniquely identify a tuple.
 Characteristics of the Relational Model
 Data Representation: Data is organized in tables (relations), with rows
(tuples) representing records and columns (attributes) representing data
fields.
 Atomic Values: Each attribute in a table contains atomic values, meaning
no multi-valued or nested data is allowed in a single cell.
 Unique Keys: Every table has a primary key to uniquely identify each
record, ensuring no duplicate rows.
 Attribute Domain: Each attribute has a defined domain, specifying the
valid data types and constraints for the values it can hold.
 Data Independence: The model ensures logical and physical data
independence, allowing changes in the database schema without affecting
the application layer.
 Relational Operations: Supports operations like selection, projection,
join, union and intersection, enabling powerful data retrieval manipulation.
 Data Consistency: Ensures data consistency through constraints,
reducing redundancy and anomalies.
 Set-Based Representation: Tables in the relational model are treated as
sets and operations follow mathematical set theory principles.
 Constraints in Relational Model

1. Domain Constraints
Domain Constraints ensure that the value of each attribute A in a tuple must be
an atomic value derived from its specified domain, dom(A). Domains are
defined by the data types associated with the attributes. Common data types
include:
  Numeric types: Includes integers (short, regular and long) for whole
numbers and real numbers (float, double-precision) for decimal
values, allowing precise calculations.
 Character types: Consists of fixed-length (CHAR) and variable-
length (VARCHAR, TEXT) strings for storing text data of various sizes.
 Boolean values: Stores true or false values, often used for flags or
conditional checks in databases.
 Specialized types: Includes types for date (DATE), time (TIME),
timestamp (TIMESTAMP) and money (MONEY), used for precise
handling of time-related and financial data.
2. Key Integrity
Every relation in the database should have at least one set of attributes that
defines a tuple uniquely. Those set of attributes is called keys. e.g.; ROLL_NO in
STUDENT is key. No two students can have the same roll number. So a key has
two properties:
 It should be unique for all tuples.
 It can’t have NULL values.
3. Referential Integrity Constraints
When one attribute of a relation can only take values from another attribute of
the same relation or any other relation, it is called referential integrity .
 Advantages of the Relational Model:
 Simple model: Relational Model is simple and easy to use in comparison
to other languages.
 Flexible: Relational Model is more flexible than any other relational model
present.
 Secure: Relational Model is more secure than any other relational model.
 Data Accuracy: Data is more accurate in the relational data model.
 Data Integrity: The integrity of the data is maintained in the relational
model.
 Operations can be Applied Easily: It is better to perform operations in
the relational model.
 Disadvantages of the Relational Model:
 Performance: The relational model can experience performance issues
with very large databases.
 Complexity for Complex Data: The model struggles with hierarchical or
complex data relationships, which might be better handled with
other models like the Graph or Document model.
 Normalization Overhead: Extensive use of normalization can result in
complex queries and slower performance.
 Data Types:
Data Type Description Range

-9,223,372,036,854,775,808 to
BIGINT Large integer numbers
9,223,372,036,854,775,807

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 (e.g.,

DECIMAL -10^38 + 1 to 10^38 - 1
for financial values)

Similar to DECIMAL, used for -10^38 + 1 to 10^38 – 1

NUMERIC
precision data

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

Similar to FLOAT, but with less

REAL -3.40E+38 to 3.40E+38
precision

The maximum length of 8000

Char characters. (Fixed-Length non- Char
Unicode Characters)

Varchar The maximum length of 8000 Varchar

characters. (Variable-Length non-
 Data Type Description Range

Unicode Characters)

The maximum length of 2^31 -

1 characters(SQL Server 2005
Varchar(max) Varchar(max)
only). (Variable Length non-
Unicode data)

The maximum length of

2,127,483,647
Text Text
characters(Variable Length non-
Unicode data)

stores the data of date (year,

DATE 3 Bytes
month, day)

stores the data of time (hour,

TIME 3 Bytes
minute,second)

store both the data and time

DATETIME (year, month, day, hour, minute, 8 Bytes
second)

 Table in a Database?
A table is a collection of related data in an organized manner in the
form of rows and columns. It is an organized arrangement of data and
information in tabular form containing rows and columns, making it
easier to understand and compare data.

Create a Database using the below query.

Query :CREATE DATABASE StudentsDatabase;
 Create a Table named as Students.
Query :CREATE TABLE Students (
ID INT PRIMARY KEY,
NAME VARCHAR(50),
AGE INT,
COURSE VARCHAR(50)
);
Insert the data into it.
Query : INSERT INTO Students (ID, NAME, AGE,
COURSE)VALUES
(1, 'MINAL', 22, 'COMPUTER SCIENCE'),
(2, 'MRIDUL', 21, 'CIVIL'),
(3, 'MARAM', 19, 'CHEMICAL'),
(4, 'MOHAMMAD', 24, 'ELECTRICAL');
Output:

SQL commands are the fundamental building blocks for communicating with a database
management system (DBMS). It is used to interact with the database with some operations. It
is also used to perform specific tasks, functions, and queries of data.

SQL Commands are mainly categorized into five categories:

 DDL – Data Definition Language
 DQL – Data Query Language
 DML – Data Manipulation Language
  DCL – Data Control Language
 TCL - Transaction Control Language

DDL - Data Definition Language

consists of the 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
Create database or its objects (table, index, function, views, store procedure, and triggers)
CREATE TABLE table_name (column1 data_type, column2 data_type, ...);
 CREATE TABLE employees ( employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE );
DROP : Delete objects from the database
DROP TABLE table_name;
ALTER: Alter the structure of the database
ALTER TABLE table_name ADD COLUMN column_name data_type;
TRUNCATE : Remove all records from a table, including all spaces allocated for the
records are removed
TRUNCATE TABLE table_name;
DQL - Data Query Language
DQL statements are used for performing queries on the data within schema objects. The
purpose of the DQL Command is to get some schema relation based on the query passed to it.

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

SELECT
from the database table_name WHERE condition;

Example: SELECT first_name, last_name, hire_date FROM employees WHERE

department = 'Sales' ORDER BY hire_date DESC;
DML - Data Manipulation Language
The SQL commands that deal with the manipulation of data present in the database belong to
DML or Data Manipulation Language and this includes most of the SQL statements.
 INSERT INTO table_name (column1,
INSERT Insert data into a table
column2, ...) 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');
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.

Assigns new privileges to

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

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

Example
GRANT SELECT, UPDATE ON employees TO user_name;
. Basic SQL Querying
🔸 SELECT and PROJECT

 SELECT operation is used to retrieve rows from a table that satisfy certain
conditions.
  PROJECT operation refers to retrieving specific columns (attributes) of a table.
Example:
SELECT name, salary FROM employees;
This query "projects" only the name and salary columns.

2. WHERE Clause,
 The where clause specifies conditions that the result must satisfy
 Corresponds to the selection predicate of the relational algebra.
 To find all instructors in Comp. Sci. dept
 select name
from instructor
where dept_name = 'Comp. Sci.'
 SQL allows the use of the logical connectives and, or, and not
 The operands of the logical connectives can be expressions involving the comparison
operators <, <=, >, >=, =, and <>.
 Comparisons can be applied to results of arithmetic expressions
 To find all instructors in Comp. Sci. dept with salary > 70000
 The from clause lists the relations involved in the query
 Corresponds to the Cartesian product operation of the relational algebra.
 Find the Cartesian product instructor X teaches
 select * from instructor, teaches
 generates every possible instructor – teaches pair, with all attributes from both
relations.
 For common attributes (e.g., ID), the attributes in the resulting table are renamed
using the relation name (e.g., [Link])
o select name from instructor where dept_name = 'Comp. Sci.' and salary >
70000
Example:
SELECT * FROM employees WHERE salary > 50000 AND department = 'IT';
SQL: Arithmetic and logical operations
SQL provides operators for performing various operations on data, including arithmetic
calculations and logical evaluations.
1. Arithmetic operators
These operators perform mathematical calculations on numerical data within a database.
 + (Addition): Adds two numbers or values.
o Example: SELECT 10 + 5; returns 15.
 SELECT salary, salary + 5000 AS "SALARY_BONUS" FROM employee;
 - (Subtraction): Subtracts one number from another.
o Example: SELECT 10 - 5; returns 5.

o SELECT dealer_price - standard_cost AS profit_margin FROM

products;
 * (Multiplication): Multiplies two numbers or values.
o Example: SELECT 10 * 5; returns 50.
 SELECT quantity * unit_price AS total_cost FROM order_items;
 / (Division): Divides one number by another.
o Example: SELECT 10 / 5; returns 2.

o SELECT (commission * 100) / total_sales AS commission_percentage

FROM sales_reps;
 % (Modulo): Returns the remainder of a division operation.
o Example: SELECT 17 % 4; returns 1.

o SELECT ID, NAME FROM employee WHERE ID % 2 = 0;

2. Logical operators
These operators combine or negate conditions to filter data and return boolean results (TRUE,
FALSE, or UNKNOWN).
 AND: Combines multiple conditions, returning TRUE only if all conditions are true.
o Example: SELECT * FROM products WHERE price > 50 AND category
= 'dresses'; retrieves products with price over 50 AND the category 'dresses'.
 OR: Combines multiple conditions, returning TRUE if at least one condition is true.
o Example: SELECT * FROM products WHERE price = 50 OR category =
'dresses'; retrieves products with price equal to 50 OR the category
'dresses'.
 NOT: Negates a condition, returning TRUE if the condition is false.
 o Example: SELECT * FROM Customers WHERE CustomerName NOT
LIKE 'A%'; returns records where the customer name doesn't start with
the letter 'A'.
3. Combining operators and operator precedence
You can combine multiple operators in a single query to create more complex filtering and
calculations. SQL follows operator precedence to determine the order in which operations are
evaluated. Arithmetic and compound operators have the highest precedence, followed by
comparison and then logical operators. Parentheses can be used to override the default order
of precedence.

SQL functions and creating tables with relationships

SQL provides a variety of built-in functions to manipulate and transform data, including
those for dates and times, numeric values, string manipulation, and conversions between data
types. Creating tables with relationships, using primary and foreign keys, is fundamental for
structuring relational databases and ensuring data integrity. Let's explore these concepts with
examples.
These functions manipulate date and time values. Examples include:
 NOW(): Returns the current date and time.
 CURDATE(): Returns the current date.
 CURTIME(): Returns the current time.
 DATE_ADD(date, INTERVAL value unit): Adds a time interval to a date.
 DATEDIFF(date1, date2): Calculates the difference between two dates in days.
 DATE_FORMAT(date, format): Formats a date into a specified string format
 Example :
 SELECT NOW(), CURDATE(), CURTIME(), DATE_ADD(CURDATE(),
INTERVAL 7 DAY) AS next_week, DATEDIFF('2025-08-01', '2025-07-21') AS
days_difference, DATE_FORMAT(NOW(), '%W, %M %D, %Y') AS
formatted_date;
SQL includes several categories of built-in functions:
🔹 a. Date and Time Functions

Used for handling dates and times.

Function Description

CURRENT_DATE Returns the current date

CURRENT_TIME Returns the current time

Function Description

NOW() Returns current date and time

DATEPART() Extracts part of a date (e.g., year, month)

🔹 b. Numeric Functions

Used to perform operations on numeric data.

Function Description

ABS() Absolute value

ROUND(number, d) Rounds to d decimal places

FLOOR() / CEIL() Rounds down / up

POWER(x, y) x raised to power y

🔹 c. String Functions

Used to manipulate strings.

Function Description

UPPER() / LOWER() Converts case

LENGTH() Returns length of string

CONCAT() Concatenates strings

SUBSTRING() Extracts part of a string

🔹 d. Conversion Functions

Used to convert data types.

Function Description

CAST() Converts one data type to another

CONVERT() Similar to CAST, often DB-specific

IMPORTANT QUESTIONS :

1. Define the following terms with suitable examples

[Link] 2. ATTRIBUTE TUPLE 3. RELATIONAL 4. MODEL TABLE

2. Differentiate between the various types of constraints in a relational database

and explain their importance:
Types of Database Constraints

Relational constraints are generally classified into four primary categories:

1. Entity Integrity (Primary Key):

1. Rule: Ensures every row in a table is unique and that the

designated Primary Key is never NULL.

2. Function: Uniquely identifies a record, acting as the primary point

of reference for other tables.

2. Referential Integrity (Foreign Key):

1. Rule: A Foreign Key in one table must either match a value in the
referenced table's Primary Key or be NULL.

2. Function: Maintains logical links between tables, ensuring that a

record (like an "Order") cannot exist without a valid parent record
(like a "Customer").

3. Domain Integrity:

1. Rule: Restricts the values that can be entered into a specific

column based on data types, ranges, or specific formats.

2. Common Sub-types:

o NOT NULL: Forbids empty values in a column.

o CHECK: Validates data against a specific condition (e.g., Age

>= 18 or Salary > 0).

o UNIQUE: Ensures all non-null values in a column are distinct,

such as email addresses.

4. Key Constraints:

1. Rule: Ensures that certain attributes (Super Keys or Candidate

Keys) uniquely identify each row.

2. Function: Provides alternate ways to ensure tuple uniqueness even

if they are not chosen as the Primary Key.

Importance of Constraints

 Prevention of Data Anomalies: Constraints stop the "garbage in,

garbage out" problem by rejecting invalid, duplicate, or logically
inconsistent entries.

 Relational Consistency: They prevent "orphan records," such as a

prescription linked to a non-existent doctor.

 Enforcement of Business Rules: They translate real-world requirements

(e.g., bank balances cannot be negative) into technical safeguards.
  Performance Benefits: Many constraints (like Primary and Unique keys)
automatically create indexes, which significantly speed up data retrieval
and join operations.

 Reduced Manual Effort: Once defined, the database automatically

handles validation, reducing the need for extensive error-checking logic
within the application code.

3. llustrate with examples how the violation of constraints can lead to data
anomalies (Insertion, Update, Deletion anomalies).

iSolation of database constraints—such as primary keys, foreign keys, and NOT

NULL rules—often leads to data anomalies in poorly designed (unnormalized)
tables. These anomalies make it difficult to maintain a reliable and consistent
database.

1. Insertion Anomaly

An insertion anomaly occurs when a new record cannot be added to a database

because some required data is missing, often due to a NOT NULL or Foreign
Key constraint.

 Example: Imagine a table that stores both Student and Department

information. If a new "Physics" department is created but has no students
yet, it cannot be added to the table if the "Student ID" field is a primary
key or has a NOT NULL constraint.

 Constraint Conflict: You cannot record the existence of a department

without first having a student to "anchor" the record.

2. Update (Modification) Anomaly

An update anomaly occurs when data is redundant (stored in multiple places).

Updating one record while others remain unchanged leads to inconsistent data.

 Example: In a table where an employee's address is listed multiple times

because they work on several projects, changing their address in only one
row creates a conflict.

 Constraint Conflict: While the database might not technically "block" the
partial update, it violates the Data Integrity expectation that one real-
world entity (the employee) should have only one state (one address).

3. Deletion Anomaly

A deletion anomaly happens when deleting a record inadvertently removes other

unrelated, valuable data that was bundled into the same table.

 Example: If a table combines Student and Course information and the

only student enrolled in "Advanced Math" drops out, deleting their record
also deletes the only record of that course's existence.
  Constraint Conflict: This often relates to Referential Integrity. If you
delete a parent record (e.g., a customer), a "Cascade Delete" constraint
might automatically wipe out all their orders, even if you needed to keep
those orders for financial history.

4. Write SQL queries to perform the following DML operations:

INSERT data into tables.

UPDATE specific records based on given criteria.

DELETE records from a table using a WHERE clause.

6. Given a simple database schema, write DDL commands to create the

tables including primary and foreign key constraints, NOT NULL
constraints, and data types.

To implement a simple, normalized database schema (like a University

system), you use Data Definition Language (DDL) commands. This
structure prevents the insertion, update, and deletion anomalies discussed
previously by separating entities into their own tables.

Below are the DDL commands to create three related

tables: Departments, Students, and Enrollments.

1. Create the Departments Table

This table acts as a parent table. The DeptID is the Primary Key, ensuring
each department is unique.

SYNTAX

CREATE TABLE Departments (

DeptID INT PRIMARY KEY,

DeptName VARCHAR(50) NOT NULL UNIQUE

2. Create the Students Table

This table includes a NOT NULL constraint on the student's name and
a Foreign Key that links each student to a department.

sYNTAX

CREATE TABLE Students (

StudentID INT PRIMARY KEY,

StudentName VARCHAR(100) NOT NULL,

Email VARCHAR(100) UNIQUE,

DeptID INT,

-- Foreign Key links to Departments table

 CONSTRAINT FK_StudentDept FOREIGN KEY (DeptID)

REFERENCES Departments(DeptID)

);

3. Create the Enrollments Table

This table demonstrates a Composite Primary Key (a combination of two

columns) and multiple Foreign Keys to manage relationships between
students and courses.

SYNTAX:

CREATE TABLE Enrollments (

StudentID INT NOT NULL,

CourseID INT NOT NULL,

EnrollDate DATE DEFAULT CURRENT_DATE,

-- Composite Primary Key: a student can only enroll in a specific course

once

PRIMARY KEY (StudentID, CourseID),

-- Foreign Key ensures the student actually exists

CONSTRAINT FK_EnrollStudent FOREIGN KEY (StudentID)

REFERENCES Students(StudentID) ON DELETE CASCADE

);

Use code with caution.

Key DDL Components Used:

 PRIMARY KEY: Uniquely identifies each record; it cannot be NULL.

 FOREIGN KEY: Enforces referential integrity by ensuring a value in one

table matches a value in another.

 NOT NULL: Prevents a column from having empty values, ensuring

essential data is always present.

 UNIQUE: Ensures all values in a column are distinct, such as email

addresses.

 ON DELETE CASCADE: An optional clause that automatically deletes

child records (like enrollments) if the parent record (a student) is deleted.