Netaji Subhas University of

Technology
DBMS Final Lab Report

NAME: SAMRIDHI KANWAR

ROLL NO: 2022UCM2342
BRANCH: MATHEMATICS AND
COMPUTING

1
2
 INDEX

S.No. Page No. Topic

1 3 Data Definition Language (DDL) and Data Manipulation Language

(DML) commands of base tables and views.

2 5 High level programming language extensions.

3 7 XAMPP Installation Steps and create CSAI database with Sailor-

Boats- Reserves Tables.

4 15 Connecting XAMPP with Python

5 17 Answering Queries considering a given relational schema using

SQL and relational algebra.

6 19 Decomposing a Table, providing all Functional dependencies and

answering queries related to the data.

7 21 Normalizing and Decomposing a given table.

8 23 Installing Java and connecting with XAMPP.

9 30 Designing and decomposing a table to normalize it.

10 31 Creating a design to BCNF implement the task in JAVA.

3
Exercise 1:
Data Definition Language (DDL) and Data Manipulation Language (DML) commands of
base tables and views.

1a) DATA DEFINITION LANGUAGES (DDL) COMMANDS Of

Base Tables and Views.

ANS) A Data Definition Language (DDL) statement is used to

define the database structure or schema.
1. CREATE: The `CREATE` statement is used to create new
database objects, such as tables, views, indexes, or schemas.
2. ALTER: The `ALTER` statement is used to modify the
structure of existing database objects, like adding or
dropping columns from a table or changing the data type of
a column.
3. DROP: The `DROP` statement is used to delete existing
database objects, which could be tables, indexes, or other
schema elements.
4. TRUNCATE: The `TRUNCATE` statement is used to remove
all data from a table while keeping the table structure intact.
5. RENAME: The `RENAME` statement is used to rename
Existing database objects. DDL is typically used by database
administrators and developers to design and maintain the
database structure, ensuring data integrity, enforcing
constraints, and optimizing database performance.

1b)DATA MANIPULATION LANGUAGE (DML) OF BASE TABLES

AND VIEWS 3
ANS) Data manipulation language allows the users to query
and manipulate data in existing schema in object. It allows
4
following data to insert, delete, update and recovery data in
schema object.
1. SELECT: The `SELECT` statement is used to retrieve data
from one or more tables. It allows you to specify the
columns you want to retrieve, apply filters, sort the results,
and perform various operations on the data.
2. INSERT: The `INSERT` statement is used to add new rows
or records to a table. You specify the values to be inserted
into each column of the table.
3. UPDATE: The `UPDATE` statement is used to modify
existing data in a table. You can specify which records to
update and the new values for the specified columns.
4. DELETE: The `DELETE` statement is used to remove rows
or records from a table. You can specify criteria to determine
which records should be deleted. DML commands are
essential for day-to-day operations involving database
records. They are commonly used by application developers,
analysts , and end-users to interact with the data stored in a
database, ensuring data retrieval, modification, and deletion
as needed.

5
Exercise 2:
High level programming language extensions.

Aim: To implement PL/SQL program using control structures, procedures

and functions.
a) CONTROL STRUCTURE: Introduction: An interactive control statement is
used when we want to repeat the execution of one or more statements for
specified number of times.
b) PROCEDURES:
❖ A procedure is a block that can take parameters (sometimes referred to
as arguments) and be invoked.
❖ Procedures promote reusability and maintainability. Once validated, they
can be used in number of applications.
❖ A procedure has two parts:
1. The specification
2. The body.
The Specification:
❖ The procedure specification begins with the keyword PROCEDURE and
ends with the Procedure_Name or a Parameter_List.
❖ Parameter declarations are optional. Procedures that take no parameters
are written without parentheses. The Body:
❖ The procedure body begins with the keyword IS (or AS) and ends with the
keyword END followed by an optimal procedure name.
❖ The procedure body has three parts: 1. A Declarative part. 2. An
Executable part. 3. An Exception-handling part (Optional).
❖ The declarative part contains local declarations, which are placed
between the keywords IS and BEGINS.
❖ The keyword DECLARES, which introduces declarations in an anonymous
PL/SQL block, is not used.
❖ The executable part contains statements, which are placed between the
keywords BEGIN, and EXCEPTION (or END).

(c) FUNCTIONS:
❖ A function is a program that might perform an action and does return a
value. The function is a subprogram that computes a value.
6
❖ Like a procedure, a function has two parts: 1. The specification 6 2. The
body The Specification:
❖ The function specification begins with the keyword FUNCTION and ends
with the RETURN clause, which specifies the data type of the return value.
❖ Parameter declaration are optional. Functions that take no parameters
are written without parentheses. The Body:
❖ The function body begins with the keyword IS (or AS) and ends with
keyword END followed by an optional function name.
❖ The function body has three parts: 1. A Declarative part. 2. An Executable
part. 3. An Exception-handling part (Optional).
❖ The declarative par contains local declarations, which are placed between
the keywords IS and BEGIN.
❖ The keyword DECLARES is not used.
❖ The executable part contains statements, which are placed between the
keywords BEGIN, and EXCEPTION (or END).

7
Exercise 3:
XAMPP Installation Steps and create CSAI database with Sailor-Boats- Reserves Tables.

Step 1: Install XAMPP Installer from https://sourceforge.net/projects/xampp/.

Step 2: Install XAMPP using the installer.

8
9
Step 3: In the XAMPP control panel, start MySQL and Apache.

Step 4: Go to phpMyAdmin.

10
Step 5: Create a new Database and name it ‘CMCT Database’.

Step 6: After creating the database, Create three tables namely ‘Sailors’, ‘Boats’ and
‘Reserves’. After creating each table, enter respective attributes provided by the
instructor.

11
12
13
Step 7: Now select the SAILORS table and select the column (here, sid). Then select
primary to set primary key. Set bid as primary key in the BOATS table.

14
Step 8: Now go to the relation view and set bid and sid in reserves table as foreignkeys.

15
Exercise 4:
Connecting XAMPP with Python

1. Python in XAMPP

16
Output in XAMPP:

2. Getting data of table created in XAMPP in python:

17
Exercise 5:
Answering Queries considering a given relational schema using SQL and relational
algebra.

18
19
Exercise 6:
Decomposing a Table, providing all Functional dependencies and answering queries
related to the data.

20
21
Exercise 7:
Normalizing and Decomposing a given table.

22
23
Exercise 8:
Installing Java and connecting with XAMPP.

XAMPP CONNECTION WITH JAVA

STEP 1: INSTALL APACHE IDE, JAVA Connector JAR File, JAVA Development Kit (JDK)

STEP 2: OPEN THE APACHE INSTALLER AND INSTALL APACHE. AFTER INSTALLING GO TO FILE
-> NEW PROJECT -> JAVA WITH ANT -> JAVA APPLICATION

STEP 3: IN LIBRARIES OF CURRENT PROJECT, ADD CONNECTOR JAR FILE.

STEP 4: Copy the code for connection, display, insertion and deletion and make the
appropriate changes such as changing the name of your database, name of attributes etc.
1) Connection -> Establishing JAVA – XAMPP CONNENCTION
package job;
import java.sql. *;
import java.util.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class JDBC {

public static void main(String[] args) {
// creates Connection object
Connection conn1 = null;
try {

24
 // connect way #1
String url1 = "jdbc:mysql://localhost:3306/cmct";
String user = "root";
String password = "";
conn1 = DriverManager.getConnection(url1, user, password);
if (conn1 != null) {
System.out.println("Connected to the database cmct");
}
}
catch (SQLException ex) {
System.out.println("An error occurred. Maybe user/password is invalid");
ex.printStackTrace();
}
finally {
if (conn1 != null) {
try {
conn1.close();
}
catch(Exception ex) {
ex.printStackTrace();
}
}
}
}
}
CONNECTION SUCCESSFUL !

2)DISPLAY 01 -> Displaying The sailors name and their rating

CODE : (DISPLAY01)

package jdbc;
import java.sql.*;

25
 import java.util.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import com.mysql.jdbc.Statement;

public class Display01 {

public static void main(String[] args) {
Connection con = null;
Statement statement = null;
try {
HashMap hm = new HashMap<>();
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/cmct-database", "root", "");
statement = (Statement) con.createStatement();
String sql;
sql = "select * from sailors";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
hm.put(resultSet.getString("SNAME"), resultSet.getInt("SID"));
}
System.out.println(hm);

} catch (Exception e) {
e.printStackTrace();
}
}
}

DISPLAY 01 -> Displaying the whole sailors table

3)
CODE : (DISPLAY02)
package jdbc;
import java.sql.*;
import java.util.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.sql.ResultSet;
import java.util.ArrayList;

26
import java.util.HashMap;

public class Display02 {

static final String DB_URL = "jdbc:mysql://localhost/cmct-database";
static final String USER = "root";
static final String PASS = "";
static final String QUERY = "SELECT SID, SNAME, RATING, DOB FROM sailors";

public static void main(String[] args) {

// Open a connection
try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement();
){

ResultSet rs = stmt.executeQuery(QUERY);
while(rs.next()){
//Display values
System.out.print("SID: " + rs.getInt("SID"));
System.out.print(", Rating: " + rs.getInt("Rating"));
System.out.print(", Sname: " + rs.getString("Sname"));
System.out.println(", DOB: " + rs.getString("DOB"));
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

INSERT -> Inserting a new record {21, 'Zara', 6, '2023-10-13'} in sailors

4)
CODE : (INSERTION)
package jdbc;
import java.sql.*;
import java.util.*;
import java.sql.Connection;
import java.sql.DriverManager;

27
 import java.sql.SQLException;
import java.util.Properties;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

public class Insert {

static final String DB_URL = "jdbc:mysql://localhost/cmct-database";
static final String USER = "root";
static final String PASS = "";

public static void main(String[] args) {

// Open a connection
try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement();
){
// Execute a query
System.out.println("Inserting records into the table...");
String sql = "INSERT INTO sailors VALUES (21, 'Zara', 6, '2023-10-13')";
stmt.executeUpdate(sql);

System.out.println("Inserted records into the table...");

} catch (SQLException e) {
e.printStackTrace();
}
}
}

After inserting the record Zara

28
5) DELETE -> Deleting a record with SID = 10
CODE : (DELETION)
package jdbc;
import java.sql.*;
import java.util.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

public class Delete {

static final String DB_URL = "jdbc:mysql://localhost/cmct-database";
static final String USER = "root";
static final String PASS = "";
static final String QUERY = "SELECT SID, SNAME, RATING, DOB FROM sailors";

public static void main(String[] args) {

// Open a connection
try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement();
){
String sql = "DELETE FROM sailors " +
"WHERE SID = 10";
stmt.executeUpdate(sql);
ResultSet rs = stmt.executeQuery(QUERY);
while(rs.next()){
//Display values

29
 System.out.print("SID: " + rs.getInt("SID"));
System.out.print(", Rating: " + rs.getInt("Rating"));
System.out.print(", Sname: " + rs.getString("Sname"));
System.out.println(", DOB: " + rs.getString("DOB"));
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Before deleting the record :

After deleting the record where SID = 10

30
Exercise 9:
Designing and decomposing a table to normalize it.

31
Exercise 10:
Creating a design to BCNF implement the task in JAVA.

Suppose there are 15

Employees
Table Used: Employee:
NAM FATHER EMP. DOB SEX MOTHE CITY STREE STAT
E _NAME _No R T E
TONGU
E
Sekar Moorth 101 2/2/80 Male Hindi Delhi Clive St Delhi
y
Ajith Arjun 102 23/9/81 Male English Bangl MG St KA
or e
Anith Arun 103 30/10/7 Fema Tamil Chenn KKnaga TN
a 5 le ai r
Kows Maridas 104 20/1/87 Fema Telugu Hydra Port st AP
ba d
i s le

Table Used: Car Parking

EMP. Car- P-
_No nam arkin
e area
101 Nano A

102 Maruti- B
SWIFT
103 Benz C

104 Nano A

32
Table Used: Salary

EMP. Salar Basi DA HR

_No y- c A
Scale
101

102

103

104

Q1. List out employee information, who comes in

Benz Car and having a particular salary scale.

C O D E :

import java.sql.Connection;

import

java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

33
public class EmployeeCarQueries {

34
 public static void main(String[] args) {

String jdbcUrl = "jdbc:mysql://carcompany ";

String username = "anmol";

String password = "12345678";

try (Connection connection =

DriverManager.getConnection(jdbcUrl, username,

password)) {

String query = "SELECT e.NAME,

e.FATHER_NAME, e.EMP_No, e.DOB, e.SEX,

e.MOTHER_TONGUE, e.CITY, e.STREET, e.STATE " +

"FROM Employee e " +

"JOIN Car_Parking cp ON e.EMP_No

= cp.EMP_No " +

"JOIN Salary s ON e.EMP_No =

s.EMP_No " +

"WHERE cp.Car_name = 'Benz' AND

s.Salary_Scale = 'Scale3';";

35
 PreparedStatement statement =

connection.prepareStatement(query);

ResultSet resultSet =

statement.executeQuery();

System.out.println("Employees who come

in a Benz Car with Scale3 salary:");

while (resultSet.next()) {

System.out.println("Name: " +

resultSet.getString("NAME"));

System.out.println("Father's Name: " +

resultSet.getString("FATHER_NAME"));

System.out.println("Employee No: " +

resultSet.getInt("EMP_No"));

System.out.println("DOB: " +

resultSet.getString("DOB"));

36
 System.out.println("Sex: " +

resultSet.getString("SEX"));

System.out.println("Mother Tongue: " +

resultSet.getString("MOTHER_TONGUE"));

System.out.println("City: " +

resultSet.getString("CITY"));

System.out.println("Street: " +

resultSet.getString("STREET"));

System.out.println("State: " +

resultSet.getString("STATE"));

System.out.println();

} catch (SQLException e) {

e.printStackTrace();

Q2: List out Car name whose owner draws a DA

37
of particular amount.

CODE:

import java.sql.Connection;

import

java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class CarQueries {

public static void main(String[] args) {

String jdbcUrl = "jdbc:mysql://your-database-

url-here";

String username = "anmol";

String password = "12345678";

try (Connection connection =

DriverManager.getConnection(jdbcUrl, username,

38
password)) {

39
 String query = "SELECT cp.Car_name " +

"FROM Car_Parking cp " +

"JOIN Salary s ON cp.EMP_No =

s.EMP_No " +

"WHERE s.DA = 10000;"; // Replace

10000 with the specific DA amount

PreparedStatement statement =

connection.prepareStatement(query);

ResultSet = statement.executeQuery();

System.out.println("Car names owned by

employees with DA of 10000:");

while (resultSet.next()) {

System.out.println(resultSet.getString("Car_name"

));

40
 }

} catch (SQLException e) {

e.printStackTrace();

41