lOMoARcPSD|54742790
DBMS Complete Lab File
Database Management System (GL Bajaj Institute of Technology and Management)
Scan to open on Studocu
Studocu is not sponsored or endorsed by any college or university
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Department Of Computer Science & Engineering – Data Science
Database Management System Lab
BCS - 551
Submitted To: Submitted By:
Ms. Name :
Section :
Roll No :
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
INDEX
Date Of
S. No Name Of Experiment Faculty Sign
Experiment
1. Installing Oracle/MYSQL
Creating Entity-Relationship Diagram using case
2.
tools.
Writing SQL statements Using ORACLE/MYSQL
3.
a. Writing basic SQL SELECT statements.
b. Restricting and sorting data.
Displaying data from multiple Tables.
4.
a. Aggregating data using group function
b. Manipulating data
5. Nested queries and constraints and view
6. Creating cursor
7. Creating procedure and functions
8. Creating packages and triggers
Design and implementation of Library Information
9.
System
Design and implementation of Student Information
10.
System
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Experiment No. 01
Objective: Installing MYSQL.
The MySQL Installer provides you with an easy-to-use wizard that helps you to install MySQL
with the following main products:
• MySQL Server
• MySQL Workbench
• MySQL Shell
• MySQL Documentation
• All Available Connectors
To download the MySQL installer, go to the following
link [Link]
Install MySQL Server & related products using MySQL Installer
To install MySQL using the MySQL installer, double-click on the MySQL Installer file and
follow the steps below:
Step 1: Choosing a setup type
In this step, you need to choose the setup type that suits your use case. For tutorial purposes,
you can select the last option which is Custom setup type:
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Step 2. Selecting products
Since we chose the Custom setup type, the MySQL Installer displays available products for us
to select to install.
We’ll install the following products:
● MySQL Server – This is the MySQL Database Server.
● MySQL Workbench – This is the client tool for interacting with the MySQL Database Server
via GUI.
● MySQL Shell – This is an interactive Javascript, Python, or SQL interface supporting
development and administration for the MySQL Server.
To select these products, you click the + icon on the left pane, select the product, and click the
right arrow button.
Here are the paths to the selected products:
● MySQL Servers > MySQL Server > MySQL Server 8.0 > MySQL Server 8.0.34 – x64
● Applications > MySQL Workbench > MySQL Workbench 8.0 > MySQL Workbench 8.0.34
– X64
● Applications > MySQL Shell > MySQL Shell 8.0 > MySQL Shell 8.0.34 – X64
Once you select the products, you click the Next button to continue.
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Step 3. Downloading the selected products
The MySQL Installer will download the selected products from the internet. Please ensure you
have an active internet connection and wait for a few minutes for the download to complete.
After the download is complete, click the Execute button to start the installation.
Step 4. Installing the selected products
The MySQL Installer will install the selected products and this process may some time.
After the installation is complete, click the Next button to proceed to the Product
Configuration.
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Step 5. Configuring the MySQL Server
In this step, configure the MySQL Server. Choose the Development Computer for the
server configuration type, leave the other options as they are, and click the Next button.
Step 6. Choosing an authentication method
It’s recommended to use strong password encryption for authentication, which is the first
option.
Step 7. Entering a password for the root account
Enter a secure password for the root account, which has full administrative privileges..Be
sure to store it safely and use it for connecting to the MySQL Server in the future.
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Step 8. Configuring MySQL Server as a Windows Service
In this step, you can configure the MySQL Server as a Windows service, specify a service
name, and choose whether to start the MySQL Server during the operating system startup.
Step 9. Granting file permissions
In this step, you grant permission to MySQL to access the data directory.
Step 10. Displaying the selected configuration
The MySQL Installer displays a window with the configuration steps. Click
the Execute button to apply the configuration.
Step 11. Completing configuration
After applying the configuration, the MySQL Installer displays the following window to
indicate whether the MySQL Server has been configured successfully.
After completing the configuration, click theNext button to confirm and finish.
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Step 12. Completing installation
The MySQL Installer displays a window to notify you that the installation is
complete. Click the Finish button to close the installer.
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Experiment – 03
Objective: Writing SQL statements Using Oracle / MySQL
a). Writing basic SQL SELECT statements.
The SELECT statement is one of the most commonly used SQL commands. It is used to retrieve
data from a database.
Syntax : SELECT column1, column2, ..., columnN FROM table_name;
OUTPUT :
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
b). Restricting and sorting data.
Restricting Data: The WHERE clause in SQL is used to filter rows based on specified conditions.
Only rows that satisfy the condition are retrieved.
Sorting Data: The ORDER BY clause is used to sort the rows in ascending (ASC, default) or
descending (DESC) order based on one or more columns.
Syntax : SELECT column1, column2, ... FROM table_name WHERE condition ORDER BY
column_name [ASC|DESC];
OUTPUT :
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
c). Manipulating Data using Having Function
The HAVING clause filters groups of data created by the GROUP BY clause based on
conditions applied to aggregate functions like SUM, COUNT, AVG, MIN, MAX.
Unlike the WHERE clause, which operates on individual rows, HAVING operates on groups
after aggregation.
Syntax : SELECT column1, column2, aggregate_function(column3) FROM table_name
GROUP BY column1, column2 HAVING condition;
The UPDATE statement is used to modify existing records in a table. It can update one or more
columns for rows that satisfy the specified condition.
Syntax : UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE
condition;
The DELETE statement is used to remove records from a table. It can delete specific rows based
on a condition or all rows if no condition is specified.
Syntax : DELETE FROM table_name WHERE condition;
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
d). Aggregating data using group function
Aggregation functions in SQL are used to perform calculations on multiple rows of data and
return a single result. These functions are particularly useful when working with grouped data or
performing computations across a table.
The SUM() function calculates the total sum of a numeric column.
Syntax : SELECT SUM(column_name) FROM table_name WHERE condition;
The AVG() function calculates the average (mean) value of a numeric column..
Syntax : SELECT AVG(column_name) FROM table_name WHERE condition;
The MIN() function returns the smallest value in a column.
Syntax : SELECT MIN(column_name) FROM table_name WHERE condition;
The MAX() function returns the largest value in a column.
Syntax : SELECT MAX(column_name) FROM table_name WHERE condition;
The COUNT() function counts the number of rows in a table or the number of rows that match a
specific condition..
Syntax : SELECT COUNT(column_name) FROM table_name WHERE condition;
OUTPUT :
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Experiment – 04
Objective: Displaying data from multiple tables.
In SQL, you can combine data from multiple tables using joins. A JOIN operation retrieves rows
from two or more tables based on a related column between them. The most commonly used types of
joins are:
1. INNER JOIN: Returns rows when there is a match in both tables.
2. LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and the matched
rows from the right table. If there is no match, NULL values are returned for the right table.
3. RIGHT JOIN (or RIGHT OUTER JOIN): Similar to LEFT JOIN, but returns all rows from
the right table.
4. FULL JOIN (or FULL OUTER JOIN): Returns rows when there is a match in either table.
INNER JOIN Syntax : SELECT table1.column1, table2.column2, ... FROM table1 INNER
JOIN table2 ON table1.common_column = table2.common_column;
LEFT JOIN Syntax : SELECT table1.column1, table2.column2, ... FROM table1 LEFT JOIN
table2 ON table1.common_column = table2.common_column;
RIGHT JOIN Syntax : SELECT table1.column1, table2.column2, ... FROM table1 RIGHT
JOIN table2 ON table1.common_column = table2.common_column;
FULL OUTER JOIN Syntax : SELECT table1.column1, table2.column2, ... FROM table1
FULL OUTER JOIN table2 ON table1.common_column = table2.common_column;
OUTPUT:
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Experiment – 05
Objective: Nested queries, constraints and view
A nested query (or subquery) is a query embedded within another query. The subquery can be
used in the SELECT, INSERT, UPDATE, or DELETE statement, and it returns a value or a set of
values that are then used by the outer query.
• A subquery is executed first, and its result is used by the main query.
• Subqueries can be used in WHERE, FROM, SELECT, or HAVING clauses.
• Correlated Subqueries: A subquery that references columns from the outer query.
• Non-Correlated Subqueries: A subquery that does not reference columns from the outer
query
Syntax : SELECT column_name FROM table_name WHERE column_name = (SELECT
column_name FROM another_table WHERE condition);
Constraints are rules applied to the columns in a table to ensure the accuracy and reliability of
the data. The most common types of constraints are:
1. PRIMARY KEY: Uniquely identifies each record in a table.
2. FOREIGN KEY: Ensures the referential integrity between two tables.
3. UNIQUE: Ensures all values in a column are distinct.
4. NOT NULL: Ensures that a column cannot have NULL values.
5. CHECK: Ensures that values in a column satisfy a specified condition.
6. DEFAULT: Provides a default value for a column when no value is specified.
Syntax :
-- PRIMARY KEY constraint
CREATE TABLE table_name ( column_name data_type PRIMARY KEY );
-- FOREIGN KEY constraint
CREATE TABLE table_name ( column_name data_type, FOREIGN KEY (column_name)
REFERENCES another_table(column_name) );
-- UNIQUE constraint
CREATE TABLE table_name ( column_name data_type UNIQUE );
-- NOT NULL constraint
CREATE TABLE table_name ( column_name data_type NOT NULL );
-- DEFAULT constraint
CREATE TABLE table_name ( column_name data_type DEFAULT default_value );
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Views in SQL are virtual tables created by querying one or more tables. A view contains rows
and columns from the underlying tables, and it can be used to simplify complex queries or for
security purposes to restrict access to specific data.
• A view is created using the CREATE VIEW statement.
• A view is not stored as data itself but as a query that gets executed when the view is
accessed.
• Views can be used in SELECT, INSERT, UPDATE, or DELETE statements.
• You cannot index a view, but you can index the underlying tables.
Syntax : -
- Create a View
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE
condition;
-- Use the View
SELECT * FROM view_name;
OUTPUT :
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Experiment – 06
Objective: Creating Cursor
A cursor in SQL is a database object used to retrieve and manipulate data row-by-row from a
result set. Cursors are primarily used in procedural database programming when operations need
to be performed on each row of a query result.
Types of Cursors
1. Implicit Cursor: Automatically created by the SQL engine for single-row queries such
as INSERT, UPDATE, or DELETE.
2. Explicit Cursor: Manually declared by the programmer for handling multiple rows in a
result set.
Cursor Operations
1. Declare: Define the cursor and associate it with a query.
2. Open: Initialize the cursor and execute the associated query.
3. Fetch: Retrieve a row of data from the cursor.
4. Close: Release the cursor after processing.
Syntax :
-- Declare a Cursor
DECLARE cursor_name CURSOR FOR SELECT column1, column2, ... FROM
table_name WHERE condition;
-- Open the Cursor
OPEN cursor_name;
-- Fetch Data
FETCH NEXT FROM cursor_name INTO variable1, variable2, ...;
-- Close the Cursor
CLOSE cursor_name;
OUTPUT :
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Experiment – 07
Objective: Creating Procedures & Functions
Stored Procedures: Precompiled SQL code that performs a specific task. They can execute
multiple SQL statements and return multiple values through output parameters but cannot return
a value directly.
Functions: SQL objects that return a single value. Functions are typically used for computations
or transformations and can be called within SQL statements.
Syntax :
Procedure Syntax
DELIMITER $$
CREATE PROCEDURE procedure_name (IN parameter_name data_type, OUT
parameter_name data_type)
BEGIN
-- SQL Statements
END$$
DELIMITER ;
Function Syntax
DELIMITER $$
CREATE FUNCTION function_name (parameter_name data_type)
RETURNS return_data_type
DETERMINISTIC
BEGIN
-- SQL Statements
RETURN value;
END$$
DELIMITER ;
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
OUTPUT:
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Experiment – 08
Objective: Creating Triggers
A trigger is a special type of stored procedure that automatically executes in response to specific
events on a table or view. Triggers are used to enforce business rules, maintain audit trails, or
automatically update related data in the database.
Types of Triggers
1. Before Trigger: Executes before an operation (INSERT, UPDATE, or DELETE).
2. After Trigger: Executes after an operation.
3. Instead Of Trigger: Used primarily with views (if supported by the database).
Syntax :
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
-- Trigger Logic
END;
OUTPUT :
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Experiment – 09
Objective: Design and implementation of Library Information System
Specification- To create a database with an interface for implementing Library Information System
for an educational organization.
a.) Hardware & Software Design Requirements
S. No. Requirements Configuration
1 System 1
2 OS Windows 7 or higher / Linux
3 Front End C++/Java/Php etc.
4 Back End Oracle 11g/MySql
b.) Steps for Achieving Objective
i. Create the database design with required tables (using SQL) as:
Book_Details(Account_No, author, title, publication, volume, edition, copies, price,
status etc. as required)
Student_Details(stu_name, Account_No, gender, course, enrollment_no, issue_date,
return_date)
Book_Issue(enrollment_no, issue_date, return_date, Account_No, stu_name, course,
author, volume, edition)
Book_Return(enrollment_no, Account_No, return_date, issue_date, fine_amount,
stu_name, course, author, volume, edition)
(Apart from these additional tables/fields can be added as required)
ii. Create the system design with features for student registration, Issuing Book, Returning
Book, Search/view book details, Add/update book details, fine payment as per the
database design created in step i.
iii. Value of fine for books returned late should be as per any chosen criteria.
iv. Basic project should contain required design forms with features to support step.
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
OUTPUT :
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Experiment – 10
Objective: Design and implementation of Student Information System
Specification- To create a database with an interface for implementing student information system
for an educational organization.
a.) Hardware & Software Design Requirements
S. No. Requirements Configuration
1 System 1
2 OS Windows 7 or higher / Linux
3 Front End C++/Java/Php etc.
4 Back End Oracle 11g/MySql
b.) Steps for Achieving Objective
i. Create the database design with required tables (using SQL) as:
Admin (admin_id, admin_name, admin_pass)
Student_Details (roll_no, name, age, address, dob, email_id, phno,
course_enrolled, section, group etc as required)
Department (dept_id, dept_name, head_name etc. as required)
(Apart from these additional tables/fields can be added as required)
ii. Create the system design with features for adding students by admin after login,
students could view and update their details, department could enroll and view
students as per the database design created in step.
iii. Basic project should contain required design forms with features to support step.
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
OUTPUT :
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Experiment – 02
Objective: Creating Entity-Relationship Diagram using Case Tool
An Entity-Relationship Diagram (ER Diagram) is a visual representation of the relationships
between entities in a database. It is a crucial tool in database design, helping to outline the
structure and organization of data. ER diagrams are used to illustrate how different entities (such
as tables in a database) relate to one another, which can help in understanding the overall
database architecture.
Key Components of an ER Diagram :
Entities: These are objects or things in the database that have a distinct existence. They can be
physical objects (like a person or a product) or concepts (like a course or an event). In an ER
diagram, entities are usually represented by rectangles.
Attributes: These are the properties or characteristics of an entity. For example, a "Student"
entity might have attributes like StudentID, Name, Age, and Email. Attributes are typically
represented by ovals connected to their respective entity.
Relationships: These define how two entities are related to each other. For example, a "Student"
might be related to a "Course" through an "Enrollment" relationship. Relationships are
represented by diamonds or lines connecting the related entities.
Cardinality: This indicates the number of instances of one entity that can or must be associated
with each instance of another entity.
Common cardinalities include:
• One-to-One (1:1): One instance of an entity is related to one instance of another entity.
• One-to-Many (1:N): One instance of an entity is related to multiple instances of another
entity.
• Many-to-Many (M:N): Multiple instances of one entity are related to multiple instances
of another entity.
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Downloaded by Nidhi Sharma ([Link]@[Link])
lOMoARcPSD|54742790
Downloaded by Nidhi Sharma ([Link]@[Link])