TABLE OF CONTENTS

Serial Name of Program Signature Remarks

No.
1 Introduction to relational database
management system.

2 Introduction to structure query

language.

3 To illustrate data definition language

commands in RDBMS.

4 To illustrate data manipulation

language command in RDBMS
5 To illustrate create table command in
sql.

6 To illustrate insert values command in

sql.

7 To illustrate select table command in

sql.

8 To illustrate update table command in

sql.

9 To illustrate alter command in sql.

10 To illustrate delete command in sql.

11 To illustrate drop table command in
sql.

12 To illustrate difference between delete

& drop table command in sql.
.

13 To illustrate rename command in sql.

14 To illustrate integrity constraint in sql.

15 To illustrate relational model in DBMS

16 To illustrate Aggregate Functions in

SQL.

17 Introduction to Entity Relationship

Model in Database Management
System
18 Database design using Entity
Relationship Model.

19 Draw an ER diagram for Banking

system.

20 Draw an ER diagram for Library

Management system.
 1.
Introduction to relational database management system
RELATIONAL DATABASE MANAGEMENT SYSTEM INTRODUCTION

A relational database management system (RDBMS) is a collection of programs and

capabilities that enable IT teams and others to create, update, administer and otherwise interact
with a relational database. RDBMS store data in the form of tables, with most commercial
relational database management systems using Structured Query Language (SQL) to access the
database. However, since SQL was invented after the initial development of the relational
model, it is not necessary for RDBMS use.

The RDBMS is the most popular database system among organizations across the world. It
provides a dependable method of storing and retrieving large amounts of data while offering a
combination of system performance and ease of implementation.

An RDBMS is a type of database management system (DBMS) that stores data in a row-based
table structure which connects related data elements. An RDBMS includes functions that
maintain the security, accuracy, integrity and consistency of the data. This is different than the
file storage used in a DBMS.
 2.
Structure Query Language
SQL Introduction

SQL stands for “Structured Query Language” and can be pronounced as “SQL” or “sequel –
(Structured English Query Language)”.

It is a query language used for accessing and modifying information in the database. IBM first
developed SQL in 1970s. Also it is an ANSI/ISO standard. It has become a Standard Universal
Language used by most of the relational database management systems (RDBMS). Some of the
RDBMS systems are: Oracle, Microsoft SQL server, Sybase etc. Most of these have provided
their own implementation thus enhancing its feature and making it a powerful tool.

Few of the sql commands used in sql programming are SELECT Statement, UPDATE
Statement, INSERT INTO Statement, DELETE Statement, WHERE Clause, ORDER BY
Clause,.

In a simple manner, SQL is a non-procedural, English-like language that processes data in groups
of records rather than one record at a time. Few functions of SQL are:

• store data
• modify data
• retrieve data
• modify data
• delete data
• create tables and other database objects
• delete data

Types of SQL statements

There are three basic types of SQL statements:

• Data definition language (DDL) statements

• Data manipulation language (DML) statements
 • Data Control Language (DCL) statements.
•
DATA TYPES OF SQL

1. CHAR : This data type is used to store character strings values of fixed length. The size in
brackets determines the number of characters the cell can hold. The maximum number of
characters (i.e. the size) this data type can hold is 255 characters. Syntax is CHAR(SIZE)
Example is CHAR (20)

2. VARCHAR : This data type is used to store variable length alphanumeric data. The maximum
this data type can hold is 4000 characters. One difference between this data type and the CHAR
data type is ORACLE compares VARCHAR values using non-padded comparison semantics
i.e. the inserted values will not be padded with spaces. Syntax is VARCHAR(SIZE) Example
is VARCHAR (20) OR VARCHAR2 (20)

3. NUMBER : The NUMBER data type is used to store numbers (fixed or floating point).

Syntax is NUMBER (P, S) Example is NUMBER (10, 2)

4. DATE : This data type is used to represent data and time. The standard format id DD-MM-
YY as in 13-JUL-85. To enter dates other than the standard format, use the appropriate
functions. Date Time stores date in the 24-hour format. By default, the time in a date field is
12:00:00 am, if no time portion is specified. The default date for a date field is the first day of
the current month.
Syntax is DATE
 3.
DDL (Data Definition Language)
DDL or Data Definition Language actually consists of the SQL commands that can be used to
define the database schema. It simply deals with descriptions of the database schema and is used
to create and modify the structure of database objects in the database. DDL is a set of SQL
commands used to create, modify, and delete database structures but not data. These commands
are normally not used by a general user, who should be accessing the database via an application.
List of DDL commands:
• CREATE: This command is used to create the database or its objects
(like table, index, function, views, store procedure, and triggers). EX-
Create table student(
• DROP: This command is used to delete objects from the database.
EX-Drop table student;
• ALTER: This is used to alter the structure of the database. EX-Alter
table student
• TRUNCATE: This is used to remove all records from a table,
including all spaces allocated for the records are removed. EX-
Truncate table student;
• COMMENT: This is used to add comments to the data dictionary.
• RENAME: This is used to rename an object existing in the database.
EX-Rename table students to candidates;

4.
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. It is the component
of the SQL statement that controls access to data and to the database. Basically, DCL statements
are grouped with DML statements.
List of DML commands:
• INSERT: It is used to insert data into a table. EX-Insert into student
• UPDATE: It is used to update existing data within a table. EX-
Update student
• DELETE: It is used to delete records from a database table. EX-
Delete from students where student marks=95
• LOCK: Table control concurrency.
• CALL: Call a PL/SQL or JAVA subprogram.
• EXPLAIN PLAN: It describes the access path to data.
•

5.
DRAW AN ER MODEL FOR BANKING SYSTEM.
 6.
DRAW AN ER DIAGRAM FOR LIBRARY MANAGEMENT
SYSTEM.

7.
SQL Constraints

Constraints are used to limit the type of data that can go into a table.

Constraints can be specified when a table is created (with the CREATE TABLE statement) or after
the table is created (with the ALTER TABLE statement).
We will focus on the following constraints:

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
 • CHECK
• DEFAULT

SQL NOT NULL Constraint

The NOT NULL constraint enforces a column to NOT accept NULL values.

The NOT NULL constraint enforces a field to always contain a value. This means that you cannot
insert a new record, or update a record without adding a value to this field.

SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a
column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it.

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values. A primary key column cannot contain NULL values.

Each table should have a primary key, and each table can have only ONE primary key.
 8.
To illustrate relational model in DBMS

The relational model represents the database as a collection of relations. A relation is nothing but a
table of values. Every row in the table represents a collection of related data values. These rows in
the table denote a real-world entity or relationship.
The table name and column names are helpful to interpret the meaning of values in each row.
The data are represented as a set of relations.

Some popular Relational Database management systems are:

• DB2 and Informix Dynamic Server - IBM

• Oracle and RDB – Oracle
• SQL Server and Access – Microsoft

9.
Aggregate functions in SQL

SQL is excellent at aggregating data the way we might in a pivot table in Excel. We will use
aggregate functions all the time, so it's important to get comfortable with them. The functions
themselves are the same ones you will find in Excel or any other analytics program. We'll cover
them individually in the next few lessons. Here's a quick preview:

• COUNT counts how many rows are in a particular column.

• SUM adds together all the values in a particular column.
• MIN and MAX return the lowest and highest values in a particular column, respectively.
• AVG calculates the average of a group of selected values.
 10.
INTRODUCTION TO ENTITY RELATIONSHIP MODEL IN
DBMS

The Entity Relational Model is a model for identifying entities 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.
The Entity Relationship Diagram explains the relationship among the entities present in the
database. ER models are used to model real-world objects like a person, a car, or a company
and the relation between these real-world objects. In short, the ER Diagram is the structural
format of the database.

Why Use ER Diagrams In DBMS?

• ER diagrams are used to represent the E-R model in a database, which makes
them easy to be converted into relations (tables).
• ER diagrams provide the purpose of real-world modelling of objects which makes
them intently useful.
• ER diagrams require no technical knowledge and no hardware support.
• These diagrams are very easy to understand and easy to create even for a naive
user.
• It gives a standard solution for visualizing the data logically.

11.
Database Design using Entity Relationship Model

Relational Model Concepts

1. Attribute: Each column in a Table. Attributes are the properties which define a relation.
e.g., Student_Rollno, NAME, etc.
2. Tables – In the Relational model the, relations are saved in the table format. It is stored
along with its entities. A table has two properties rows and columns. Rows represent
records and columns represent attributes.
3. Tuple – It is nothing but a single row of a table, which contains a single record.
 4. Relation Schema: A relation schema represents the name of the relation with its attributes.
5. Degree: The total number of attributes which in the relation is called the degree of the
relation.
6. Cardinality: Total number of rows present in the Table.
7. Column: The column represents the set of values for a specific attribute.
8. Relation instance – Relation instance is a finite set of tuples in the RDBMS system.
Relation instances never have duplicate tuples.
9. Relation key - Every row has one, two or multiple attributes, which is called relation key.
10. Attribute domain – Every attribute has some pre-defined value and scope which is known
as attribute domain

Operations in Relational Model

Four basic update operations performed on relational database model are

Insert, update, delete and select.

 • Insert is used to insert data into the relation
• Delete is used to delete tuples from the table.
• Modify allows you to change the values of some attributes in existing tuples.
• Select allows you to choose a specific range of data.

What is ER Modeling?

Entity Relationship Modeling (ER Modeling) is a graphical approach to database design. It uses
Entity/Relationship to represent real world objects.

12.
Difference between Delete, Drop and Truncate commands in Sql

The DELETE command deletes one or more existing records from the table in
the database. The DROP Command drops the complete table from the database.
The TRUNCATE Command deletes all the rows from the existing table, leaving
the row with the column names.
 13.
CREATE A TABLE
To create a new table within a database, we use the SQL CREATE TABLE statement

Syntax:

CREATE TABLE <table name>

<table element>,

<table element>,
.
.
.
);
Example:
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Age INT
);
 14.
Insert values into Table

INSERT INTO `table_name` is the command to add data into a table .

Syntax
:
Insert into <table_name> values (‘<value>’, <value> , ............. );

Example:

INSERT INTO Students (StudentID, FirstName, LastName, Age)

VALUES (1, 'Alice', 'Johnson', 22),
(2, 'Bob', 'Smith', 24);

15.
SELECT TABLE COMMAND

Retrieving Data from Table

1. A Select statement is a SQL statement that begins with the word "select."
2. Select statements are used to retrieve data from SQL tables.
3. An asterisk after the word "select" means retrieve all fields (columns).
4. The name of the table from which you are retrieving data is specified in the
From clause

Syntax:

Select * from <table name>

Retrieve some specific data.

Select <table element > from <table name> where <condition>

Example
 16.
UPDATING THE CONTENTS OF A TABLE: -

The update command is used to change or modify data values in a table. The verb UPDATE in
SQL is used to either all the rows from a table or a selected set of rows from a table.

UPDATING ALL ROWS:- The update statement updates columns in the existing table’s rows
with new values .The SET clause indicates which column data should be modifying and the new
values that they should hold. The WHERE CLAUSE specifies which rows should be updated.
Otherwise all table rows are updated. Example:

Update student set student marks = ‘95’ where student ID =35;

 17.
Alter Table
The SQL ALTER TABLE command is used to add, delete or modify columns in an existing

table.

Syntax:

ALTER TABLE Students

ADD Email VARCHAR(100);

18.
Delete Record (Rows) from Table:
The SQL DELETE Query is used to delete the existing records from a table.

You can use the WHERE clause with a DELETE query to delete the selected rows, otherwise all
the records would be deleted.

Syntax

The basic syntax of the DELETE query with the WHERE clause is as follows −

DELETE FROM table_name

WHERE [condition];

Example: DELETE FROM Students

WHERE StudentID = 2;
 19.
Drop table
Syntax:

Drop table <table name>

Example:

Drop table student;

The DROP TABLE statement removes a table and its data permanently from the database.
 20.
TO ILLUSTRATE RENAME COMMAND IN SQL

RENAMING TABLES: - Oracle allows renaming of tables. The rename operation is done
atomically, which means that no other thread can access any of the tables while the rename
process is running.

Syntax: - RENAME <Table name> to <New Tablename>

Example: - SQL> rename student to candidates;

21.
Integrity Constraints

Integrity constraints ensure the accuracy and consistency of data within a database. They enforce r
ules on the data and help maintain its quality. Here are some key types:

Types of Integrity Constraints

1. Primary Constraints: Ensure each row in a table is unique. No null values allowed.
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(50),
 LastName VARCHAR(50)
);

2. Foreign Key Constraints: Enforce a link between the data in two tables. It maintains
referential integrity.

CREATE TABLE Enrollments (

EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

3. Unique Constraint: Ensure all the value in a column are unique.

CREATE TABLE Teachers (
TeacherID INT PRIMARY KEY,
Email VARCHAR(100) UNIQUE
 );

4. Not Null Constraint: Ensures a column cannot have null values.

CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100) NOT NULL
);
 22.
Aggregate functions in SQL

1. COUNT(): Counts the number of rows that match a specified condition.

SELECT COUNT(*) FROM Students;

2. SUM(): Adds up the values of a numeric column.

SELECT SUM(Age) FROM Students;

3. AVG(): Calculates the average value of a numeric column.

SELECT AVG(Age) FROM Students;

4. MAX(): Returns the maximum value in a numeric column.

SELECT MAX(Age) FROM Students;

5. MIN(): Returns the minimum value in a numeric column.

SELECT MIN(Age) FROM Students;

Outputs:

• COUNT()

• SUM()
• AVG()

• MAX()

• MIN()