MODI INSTITUTE OF TECHNOLOGY

An Engineering College Approved by AICTE & Affiliated to RTU

Nayagaon, Rawatbhata Road, Kota – 324010,
Website: www.mitkota.com Email: [email protected]

DEPARTMENT OF COMPUTER SCIENCE AND

ENGINEERING

LAB MANUAL

Programme (UG/PG) : UG
Semester : IV
Course Code : 4CS4-22
Lab Title : Database Management System Lab

Guided By:

Manoj Kumar Ameria

(HOD, Department of Computer Science and Engineering)
 Database Management System Lab
COURSE OBJECTIVES:
1. To explain basic database concepts, applications, data models, schemas and instances.
2. To demonstrate the use of constraints and relational algebra operations. IV. Describe the basics of SQL
and construct queries using SQL.
3. To emphasize the importance of normalization in databases.
4. To facilitate students in Database design
5. To familiarize issues of concurrency control and transaction management.

COURSE OUTCOMES:
At the end of the course the students are able to:
1. Apply the basic concepts of Database Systems and Applications.
2. Use the basics of SQL and construct queries using SQL in database creation and interaction.
3. Design a commercial relational database system (Oracle, MySQL) by writing SQL using the system.
4. Analyze and Select storage and recovery techniques of database system.
 Table of Contents
S.No Name of Experiment Page No
Design a Database and create required tables. For e.g.
1 1
Bank, College Database
2 Apply the constraints like Primary Key, Foreign key, NOT 2
NULL to the tables.
Write a SQL statement for implementing
3 3-4
ALTER,UPDATE and DELETE.
4 Write the queries to implement the joins. 5-7

5 Write the query for implementing the following 8-9

functions: MAX (), MIN (), AVG () and COUNT ().
6 Write the query to create the views. 10

7 Perform the queries for triggers. 11-12

8 Perform the following operation for demonstrating the 13

insertion , updation and deletion
9 Using the referential integrity constraints. 14

10 Write the query for creating the users and their role. 15

11 Project on Any Management System

 HARDWARE AND SOFTWARE REQUIREMENTS

Hardware Requirements: RAM – 1 GB Processor – Dual core / core2 duo.

Software Requirements: Mqsql- It is one of the most used database management software
which processes high-speed data. It supports the transaction queries like rollback and commits
which helps to recover the data at the time of any transaction error. It supports operating systems
like Windows and Linux. For MySQL, languages like C and C++ are used.
 Introduction about Database Management Systems

A database management system (or DBMS) is essentially nothing more than a computerized data-
keeping system. Users of the system are given facilities to perform several kinds of operations on such a
system for either manipulation of the data in the database or the management of the database structure
itself. Database Management Systems (DBMSs) are categorized according to their data structures or
types.
There are several types of databases that can be used on a mainframe to exploit z/OS®: inverted list,
hierarchic, network, or relational.
Mainframe sites tend to use a hierarchical model when the data structure (not data values) of the data
needed for an application is relatively static. For example, a Bill of Material (BOM) database structure
always has a high level assembly part number, and several levels of components with subcomponents.
The structure usually has a component forecast, cost, and pricing data, and so on. The structure of the
data for a BOM application rarely changes, and new data elements (not values) are rarely identified. An
application normally starts at the top with the assembly part number, and goes down to the detail
components.
Hierarchical and relational database systems have common benefits. RDBMS has the additional,
significant advantage over the hierarchical DB of being non-navigational. By navigational, we mean that
in a hierarchical database, the application programmer must know the structure of the database. The
program must contain specific logic to navigate from the root segment to the desired child segments
containing the desired attributes or elements. The program must still access the intervening segments,
even though they are not needed.
The remainder of this section discusses the relational database structure.
What structures exist in a relational database?
Relational databases include the following structures:
Database
A database is a logical grouping of data. It contains a set of related table spaces and index spaces.
Typically, a database contains all the data that is associated with one application or with a group of
related applications. You could have a payroll database or an inventory database, for example.
Table
A table is a logical structure made up of rows and columns. Rows have no fixed order, so if you retrieve
data you might need to sort the data. The order of the columns is the order specified when the table was
created by the database administrator. At the intersection of every column and row is a specific data
item called a value, or, more precisely, an atomic value. A table is named with a high-level qualifier of
the owner's user ID followed by the table name, for example TEST.DEPT or PROD.DEPT. There are
three types of tables:
A base table that is created and holds persistent data. A temporary table that stores intermediate query
results
A results table that is returned when you query tables.
 Program / Source Code-1

Objective: Design a Database and create required tables. For e.g. Bank, College Database

CREATE DATABASE
Bank; BEGIN
TRANSACTION;

/* Create a table called NAMES */

CREATE TABLE STUDENT(S_Id integer PRIMARY KEY, S_Name text, S_CITY);

/* Create few records in this table */

INSERT INTO STUDENT
VALUES(1,'Tom','AJMER'); INSERT INTO
STUDENT VALUES(2,'Lucy','KOTA'); INSERT
INTO STUDENT VALUES(3,'Frank','JAIPUR');
INSERT INTO STUDENT
VALUES(4,'Jane','DELHI'); INSERT INTO
STUDENT VALUES(5,'Robert','MUMBAI');
COMMIT;

/* Display all the records from the

table */ SELECT * FROM
STUDENT;

1
 Program / Source Code-2

Objective: Apply the constraints like Primary Key, Foreign key, NOT NULL to the tables.

CREATE TABLE DEPT ( DNAME VARCHAR(10) NOT NULL,

DNUMBER INTEGER NOT NULL,

MGRSSN CHAR(9), MGRSTARTDATE CHAR(9),

PRIMARY KEY (DNUMBER), UNIQUE

(DNAME), FOREIGN KEY (MGRSSN)

REFERENCES EMP(SSN));

2
 Program / Source Code-3
Objective: Write a SQL statement for implementing ALTER,UPDATE and DELETE.

BEGIN TRANSACTION;
/* Create a table called NAMES */
CREATE TABLE student( sid int,

sname varchar(255), scity varchar(255), sage int

);
/* insert data into table */
INSERT INTO student VALUES(101,"AMIT","AJMER",26);

insert into student

values(102,"manish","kota",35); insert into

student values(103,"ankit","jaipur",40);

/* show the table */

select *

from student;

/* Alter table */ ALTER TABLE student add sweight int;

/* insert data into table */

INSERT INTO student VALUES(101,"AMIT","AJMER",26,90);

insert into student values(102,"manish","kota",35,100); insert into student

values(103,"ankit","jaipur",40,75);

/* show the table */

select *from student;

3
/* UPDATE table */
UPDATE student

set

sname='RAM',scity='n

ewyork' where

sid='101';

/* show the table */

select *

from student;

/*DELETE IS USED delete particular Record*/

DELETE from

student where

sid='102';

/* show the table */

select * from student;

4
 Program / Source Code-4

Objective: Write the queries to implement the joins.

A SQL Join statement is used to combine data or rows from two or more tables based
on a common field between them. Different types of Joins are:
 INNER JOIN
 LEFT JOIN
 RIGHT JOIN
 FULL JOIN
Consider the two tables below:

Student

StudentCourse

5
The simplest Join is INNER JOIN.
INNER JOIN: The INNER JOIN keyword selects all rows from both the tables as
long as the condition satisfies. This keyword will create the result-set by combining
all rows from both the tables where the condition satisfies i.e value of the common
field will be same.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
INNER JOIN table2

6
ON table1.matching_column = table2.matching_column;
table1: First table.
table2: Second table
matching_column: Column common to both the tables.
This query will show the names and age of students enrolled in different courses.
SELECT StudentCourse.COURSE_ID, Student.NAME, Student.AGE FROM Student
INNER JOIN StudentCourse
ON Student.ROLL_NO = StudentCourse.ROLL_NO;
utput:

7
 Program / Source Code-5

Objective: Write the query for implementing the following functions: MAX (), MIN (), AVG ()

Students-Table

AVG(): It returns average value after calculating from values in a numeric column.
Syntax:
SELECT AVG(column_name) FROM table_name;
Queries:
1. Computing average marks of students.
2. SELECT AVG(MARKS) AS AvgMarks FROM Students;
Output:
AvgMarks

80

MAX(): The MAX() function returns the maximum value of the selected column.
Syntax:
SELECT MAX(column_name) FROM table_name;
Queries:
1. Fetching maximum marks among students from the Students table.
2. SELECT MAX(MARKS) AS MaxMarks FROM Students;
Output:
MaxMarks

95

8
MIN(): The MIN() function returns the minimum value of the selected column.
Syntax:
SELECT MIN(column_name) FROM table_name;
Queries:
1. Fetching minimum marks among students from the Students table.
2. SELECT MIN(MARKS) AS MinMarks FROM Students; Output:

MinMarks

50

9
 Program / Source Code-6
Objective: Write the query to create the views.
CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
The following SQL creates a view that shows all customers from Brazil:
CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'Brazil';
We can query the view above as follows:
SELECT * FROM [Brazil Customers];

10
 Program / Source Code-7

Objective: Perform the queries for triggers.

Trigger: A trigger is a stored procedure in database which automatically invokes whenever a

special event in the database occurs. For example, a trigger can be invoked when a row is
inserted into a specified table or when certain table columns are being updated.
Syntax:
create trigger [trigger_name]
[before | after]
{insert | update | delete}
on [table_name]
[for each row]
[trigger_body]
Given Student Report Database, in which student marks assessment is recorded. In such schema,
create a trigger so that the total and average of specified marks is automatically inserted
whenever a record is insert.
Here, as trigger will invoke before record is inserted so, BEFORE Tag can be used.

| Field | Type | Null | Key | Default | Extra |

+ + + + + + +
| tid | int(4) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
| subj1 | int(2) | YES | | NULL | |
| subj2 | int(2) | YES | | NULL | |
| subj3 | int(2) | YES | | NULL | |
| total | int(3) | YES | | NULL | |
| per | int(3) | YES | | NULL | |
+ + + + + + +

SQL Trigger to problem statement.

11
create trigger stud_marks
before INSERT
on
Student
for each row
set Student.total = Student.subj1 + Student.subj2 + Student.subj3, Student.per

12
 Program / Source Code - 8
Objective: Perform the following operation for demonstrating the insertion, updation and deletion.

we will insert 2 rows into the students table, one for each student:

INSERT INTO Students(StudentId, StudentName, DepartmentId, DateOfBirth)

VALUES(11, 'Ahmad', 4, '1997-10-12');

INSERT INTO Students VALUES(12, 'Aly', 4, '1996-10-12');

In the following UPDATE statement, we will update the DepartmentId for the Student with
StudentId = 6 to be 3:

UPDATE Students
SET DepartmentId = 3
WHERE StudentId = 6;

In the following statement, we will delete two students with StudentId 11 and 12:

DELETE FROM Students WHERE StudentId = 11 OR StudentId = 12;

13
 Program / Source Code - 9
Objective: Using the referential integrity constraints

Another example of Referential Integrity is Employee and Department relationship. If we

have dept_id as foreign key in Employee table than by using referential integrity constraints
we can avoid creating Employee without department or non existing department.

In short Referential Integrity makes primary key foreign key relationship viable. Let's first
create Employee and Department table with primary key, foreign key and referential Integrity
constraints.

CREATE TABLE Department (dept_id INT NOT NULL,

dept_name VARCHAR(256),
PRIMARY KEY (dept_id)) ENGINE=INNODB;

CREATE TABLE Employee (emp_id INT NOT NULL,

emp_name VARCHAR(256),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
ON DELETE CASCADE) ENGINE=INNODB;

Above SQL statements will create both Department and Employee table. dept_id is now
foreign key in Employee table.

In this SQL, while creating foreign key we have specified ON DELETE clause which tells,
what needs to done when a record from parent table is deleted. CASCADE referential action
allows to delete or update all matching rows from child table, after deleting a record in
parent table. This way Refrential Integrity preserve data integrity of relationship.

14
 Program / Source Code - 10
Objective: Write the query for creating the users and their role.
Solution:- standard SQL syntax for creating how to create users,

CREATE USER username IDENTIFIED BY password

IDENTIFIED WITH auth_plugin

CREATE ROLE creates a set of privileges which may be assigned to users of a database. Once
a role is assigned to a user, (s)he gets all the Privileges of that role. By creating and granting
roles, best means of database security can be practiced.

SQL Syntax:
CREATE ROLE role_name [WITH ADMIN {CURRENT_USER | CURRENT_ROLE}]

Parameters:

Name Description

role_name A name to identify the role.

Explanation:

With the above syntax, a role with role_name is created and immediately assigned to the
current user or the currently active role is passed on to other users. The default usage is WITH
ADMIN CURRENT_USER.

15