CS3481 DATABASE MANAGEMENT SYSTEMS LABORATORY

LIST OF EXPERIMENTS:
1. Create a database table, add constraints (primary key, unique, check, Not null), insert rows,
update and delete rows using SQL DDL and DML commands.
2. Create a set of tables, add foreign key constraints and incorporate referential integrity.
3. Query the database tables using different ‘where’ clause conditions and also implement
aggregate functions.
4. Query the database tables and explore sub queries and simple join operations.
5. Query the database tables and explore natural, equi and outer joins.
6. Write user defined functions and stored procedures in SQL.
7. Execute complex transactions and realize DCL and TCL commands.
8. Write SQL Triggers for insert, delete, and update operations in a database table.
9. Create View and index for database tables with a large number of records.
10. Create an XML database and validate it using XML schema.
11. Create Document, column and graph based data using NOSQL database tools.
12. Develop a simple GUI based database application and incorporate all the above-mentioned
features CS3481 Database Management Systems Lab Manual
13. Case Study using any of the real life database applications from the following list
a) Inventory Management for a EMart Grocery Shop
b) Society Financial Management
c) Cop Friendly App – Eseva
d) Property Management – eMall
e) Star Small and Medium Banking and Finance
 Build Entity Model diagram. The diagram should align with the business and
functional goals stated in the application.
 Apply Normalization rules in designing the tables in scope.
 Prepared applicable views, triggers (for auditing purposes), functions for enabling
enterprise grade features.
 Build PL SQL / Stored Procedures for Complex Functionalities, ex EOD Batch
Processing for calculating the EMI for Gold Loan for each eligible Customer.
● Ability to showcase ACID Properties with sample queries with appropriate settings

EX.NO:1 CREATE A DATABASE TABLE, ADD CONSTRAINTS (PRIMARY

DATE: KEY, UNIQUE, CHECK, NOT NULL), INSERT ROWS, UPDATE AND
DELETE ROWS USING SQL DDL AND DML COMMANDS

Aim:
To understand Sql and create database table, add constraints (PRIMARY KEY, UNIQUE,
CHECK, NOT NULL), insert rows, update and delete rows using SQL DDL and DML commands.
SQL
Structured Query Language(SQL) as we all know is the database language by the use of which we can
perform certain operations on the existing database and also we can use this language to create a
database. SQL uses certain commands like Create, Drop, Insert, etc. to carry out the required tasks.
These SQL commands are mainly categorized into five categories as:
DDL – Data Definition Language
DQL – Data Query Language
DML – Data Manipulation Language
DCL – Data Control Language
TCL – Transaction Control Language
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).
DROP: This command is used to delete objects from the database.
ALTER: This is used to alter the structure of the database.
TRUNCATE: This is used to remove all records from a table, including all spaces allocated for the
records are removed.
COMMENT: This is used to add comments to the data dictionary.
RENAME: This is used to rename an object existing in the database.

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. We can define DQL
as follows it is a component of SQL statement that allows getting data from the database and imposing
order upon it. It includes the SELECT statement. This command allows getting the data out of the
database to perform operations with it. When a SELECT is fired against a table or tables the result is
compiled into a further temporary table, which is displayed or perhaps received by the program i.e. a
front-end.
List of DQL:
SELECT: It is used to retrieve data from the database.
DML(Data Manipulation Language):
The SQL commands that deals 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.
UPDATE: It is used to update existing data within a table.
DELETE : It is used to delete records from a database table.
LOCK: Table control concurrency.
CALL: Call a PL/SQL or JAVA subprogram.
EXPLAIN PLAN: It describes the access path to data.

DCL (Data Control Language):

DCL includes commands such as GRANT and REVOKE which mainly deal with the rights, permissions,
and other controls of the database system..
List of DCL commands:
GRANT: This command gives users access privileges to the database.
REVOKE: This command withdraws the user’s access privileges given by using the GRANT command.
TCL (Transaction Control Language):
Transactions group a set of tasks into a single execution unit. Each transaction begins with a
specific task and ends when all the tasks in the group successfully complete. If any of the tasks fail, the
transaction fails. Therefore, a transaction has only two results: success or failure. You can explore more
about transactions here. Hence, the following TCL commands are used to control the execution of a
transaction.
BEGIN: Opens a Transaction.
COMMIT: Commits a Transaction.
ROLLBACK: Rollbacks a transaction in case of any error occurs.
SAVEPOINT: Sets a save point within a transaction.
SET TRANSACTION: Specifies characteristics for the transaction.

Queries to Create Database:

1. mysql> create database red;
Query OK, 1 row affected (0.10 sec)
2. mysql> use red;
Database changed

Queries to Create Database Table:

3. mysql> CREATE TABLE Employee1( Id INT, NAME VARCHAR(30) NOT NULL, Email
VARCHAR(100),Age INT CHECK (Age>= 20), PRIMARY KEY (Id),UNIQUE(Email));
Query OK, 0 rows affected (0.14 sec)

Queries to Insert Value in Table:

4. mysql> INSERT INTO Employee1 values('1','AAA','[email protected]','25');
Query OK, 1 row affected (0.13 sec)
5. mysql> INSERT INTO Employee1 values('2','BBB','[email protected]','23');
Query OK, 1 row affected (0.07 sec)

“CHECK” CONSTRAINT VERIFICATION:

6. mysql> INSERT INTO Employee1 values('3','CCC','[email protected]','2');
ERROR 3819 (HY000): Check constraint 'employee1_chk_1' is violated.

“PRIMARY KEY” CONSTRAINT VERIFICATION:

7. mysql> INSERT INTO Employee1 values('2','CCC','[email protected]','24');
ERROR 1062 (23000): Duplicate entry '2' for key 'employee1.PRIMARY'

7.INSERT INTO Employee1 values('3','CCC','[email protected]','24');

Query OK, 1 row affected (0.05 sec)

8. mysql> INSERT INTO Employee1 values('4','DDD','[email protected]','27');

Query OK, 1 row affected (0.07 sec)
“UNIQUE” CONSTRAINT VERIFICATION:
9.mysql> INSERT INTO Employee1 values('5','DDD','[email protected]','28');
ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'employee1.Email'
‘NULL’ CONSTRAINT VERIFICATION:
10.mysql> INSERT INTO Employee1 values('5',NULL,'[email protected]','28');
ERROR 1048 (23000): Column 'NAME' cannot be null
11.mysql> INSERT INTO Employee1 values('5','EEE','[email protected]','28');
Query OK, 1 row affected (0.03 sec)

SELECT
12.mysql> select * from Employee1;

13.mysql> select NAME from Employee1;

DELETE
14.mysql> delete from Employee1 Where Age='28';
Query OK, 1 row affected (0.01 sec)
DROP COLUMNS:
15. .mysql> ALTER TABLE Employee1 DROP COLUMN Age;
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0

16.mysql> select * from Employee1;

UPDATE
17.mysql> UPDATE Employee1 SET Email='[email protected]' WHERE Id=4;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings:

18. mysql> select * from Employee1;

RESULT:
Thus the table was created in MySQL and the various command has been executed successfully.

EX.NO:2 CREATE A SET OF TABLES, ADD FOREIGN KEY CONSTRAINTS

DATE: AND INCORPORATE REFERENTIAL INTEGRITY
Aim
To Create a set of tables, add foreign key constraints and incorporate referential integrity in mysql.

SQL FOREIGN KEY Constraint

The FOREIGN KEY constraint is used to prevent actions that would destroy links between
tables.A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY
KEY in another table.

The table with the foreign key is called the child table, and the table with the primary key is called the
referenced or parent table.

The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column,
because it has to be one of the values contained in the parent table.

CHECK FOREIGN KEY CONSTRAINT

1. mysql> create database sec;
Query OK, 1 row affected (0.07 sec)

2. mysql> use sec;

Database changed

3. mysql> CREATE TABLE customer ( ID INT NOT NULL , Name varchar(50) NOT NULL, City
varchar(50) NOT NULL, PRIMARY KEY (ID) );
Query OK, 0 rows affected (0.26 sec)

4. mysql> CREATE TABLE contact (ID INT,Customer_Id INT, Customer_Info varchar(50) NOT
NULL, Type varchar(50) NOT NULL, INDEX par_ind (Customer_Id), CONSTRAINT
fk_customer FOREIGN KEY (Customer_Id) REFERENCES customer(ID));
Query OK, 0 rows affected (0.24 sec)
5. mysql> describe customer;

6. mysql> describe contact;

7. mysql> insert into customer values(101,'Shiva','Trichy');
Query OK, 1 row affected (0.09 sec)
8. mysql> insert into customer values(102,'Vishnu','Chennai');
Query OK, 1 row affected (0.02 sec)
9. mysql> insert into customer values(103,'Ram','Madurai');
Query OK, 1 row affected (0.02 sec)
10. mysql> select * from customer;

11. mysql> insert into contact values(1,'Male','Regular');

ERROR 1136 (21S01): Column count doesn't match value count at row 1
12. mysql> insert into contact values(1,101,'Male','Regular');
Query OK, 1 row affected (0.04 sec)
13. mysql> insert into contact values(2,102,'Female','Passerby');
Query OK, 1 row affected (0.05 sec)
14. mysql> insert into contact values(,103,'Male','Regular');
Query OK, 1 row affected (0.05 sec)
15.mysql> select * from contact;

16.mysql> insert into contact values(101,1,'Male','Passerby');

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`sec`.`contact`,
CONSTRAINT `fk_customer` FOREIGN KEY (`Customer_Id`) REFERENCES `customer` (`ID`))

17. mysql> insert into contact values(1,101,'Male','Passerby');

Query OK, 1 row affected (0.02 sec)

18. mysql> insert into contact values(2,102,'Male','Regular');

Query OK, 1 row affected (0.03 sec)

19.mysql> insert into contact values(3,103,'Male','Regular');

Query OK, 1 row affected (0.03 sec

20.mysql> select * from customer;

21.mysql> select * from contact;

Result:
Thus the table was created in MySQL and various command by adding Foreign Key has been
executed successfully.
EX.NO:3 QUERY THE DATABASE TABLES USING DIFFERENT ‘WHERE’
DATE: CLAUSE CONDITIONS AND ALSO IMPLEMENTAGGREGATE
FUNCTIONS.

AIM:

To create the database tables using aggregation functions.

What is Aggregate function in SQL?

★ Aggregate functions help to summarize the large volumes of data.

★ This function can produced a single value for an entire group or table.
★ They operate on sets of rows and return results based on groups of rows.

List of Aggregate Functions

★ COUNT
★ SUM
★ AVERAGE
★ MAX
★ MIN

COUNT() function

The SQL COUNT function returns the number of rows in a table satisfying the criteria specified in the
WHERE clause. It sets on the number of rows or non NULL column values.
SUM() function

The SQL AGGREGATE SUM() function returns the sum of all selected column.

AVG() function

The SQL AVG function calculates the average value of a column of numeric type.It returns the average
of all non NULL values.

MAX() function

The aggregate function SQL MAX() is used to find the maximum value or highest value of a certain
column or expression.

MIN() function

The aggregate function SQL MIN() is used to find the minimum value or lowest value of a column or
expression. This function is useful to determine the smallest of all selected values of a column.
Queries to use Aggregate Function
1. create database third1;
Query OK, 1 row affected (0.21 sec)
2. use third1;
Database changed
3. mysql> create table student1(studentName varchar(25), StudentId int,Age int, Address
Varchar(25), Department varchar(25), Fees int);
Query OK, 0 rows affected (0.12 sec)
4. mysql> describe student1;

5. mysql> insert into student1 values("Priya", 101, 29,"Mayiladuthurai","cse",20000);

Query OK, 1 row affected (0.04 sec)

6. mysql> insert into student1 values("Subha", 102, 28,"Chennai","ece",25000);

Query OK, 1 row affected (0.07 sec)

7. mysql> insert into student1 values("Jai", 103, 30,"Tirunelveli","eee",18000);

Query OK, 1 row affected (0.02 sec)

8. mysql> insert into student1 values("Raja", 104, 29, "Thanjavur", "Mech", 39000);
Query OK, 1 row affected (0.03 sec)

9. mysql> insert into student1 values("Suja", 105, 12, "Thanjavur", "Eighth", 15000);
Query OK, 1 row affected (0.02 sec)
10. mysql> select * from student1;

AGGREGATE FUNCTIONS
11. mysql> select avg(fees) result from student1;

12. mysql> select min(fees) result from student1;

13. mysql> select max(fees) result from student1;

14. mysql> select count(fees) result from student1;

15. mysql> SELECT SUM(fees) FROM student1 WHERE Address="Thanjavur";

16. mysql> select distinct Address from student1;

17. mysql> select count(*) from student1 where age<=20;

18. mysql> select count(*) from student1 where fees>20000;

RESULT
Thus the database creation using aggregation function commands has been completed successfully.
EX.NO :4 QUERY THE DATABASE TABLES AND EXPLORE SUB
DATE: QUERIES AND SIMPLE JOIN OPERATIONS.

AIM:
To create a table and execute Sub queries and Joins.
SUBQUERIES AND JOINS
The JOIN statement is used to join the data of two or more tables and bring out the result as a single
set of records. The joins are very useful when you have a relationship between two tables using the
primary-foreign key.
The sub queries are also used to join the data of two or more tables. A sub query is also called an inner
query or a nested query. A sub query is basically a query inside the query.
Note that, in the JOIN statement, only a single SELECT statement is present with the names of
multiple tables. Whereas, in the sub query, there is another query with the SELECT statement present
apart from the outer SELECT statement. That means, there can be multiple select statements present in
the sub queries.
Sub queries and join statements can be used alternatively. However, sometimes the sub query becomes
the only option to get the result. However, a join statement can be replaced with a very long sub query.
Note that, before the JOIN statement was introduced in MySQL, only sub query was the option to write
the complex logic. That means, everything that we write using the JOIN, can be written using the sub
query.

SUB-QUERIES
1.mysql> create database four;
Query OK, 1 row affected (0.75 sec)
2. mysql> use four;
Database changed
3. mysql> create table product (product_id int primary key, product_name
varchar(25),product_price int);
Query OK, 0 rows affected (0.06 sec)
4. mysql> desc product;

5. mysql> insert into product values(101,'mobile',20000);

Query OK, 1 row affected (0.09 sec)
6. mysql> insert into product values(102,'laptop',50000);
Query OK, 1 row affected (0.02 sec)
7. mysql> insert into product values(103,'Tablet',50000);
Query OK, 1 row affected (0.02 sec)
8. mysql> insert into product values(104,'TV',30000);
Query OK, 1 row affected (0.02 sec)
9. mysql> insert into product values(105,'Speaker',15000);
Query OK, 1 row affected (0.04 sec)
10. mysql> select * from product;

11. mysql> create table sale(sales_id int not null primary key auto_increment,product_id
int,sales_year int,sales_amount int);
Query OK, 0 rows affected (0.10 sec)
12. mysql> desc sale;

13. mysql> insert into sale values(1,101,2022,15000);

Query OK, 1 row affected (0.03 sec)
14. mysql> insert into sale values(2,102,2021,18000);
Query OK, 1 row affected (0.03 sec)
15. mysql> insert into sale values(3,103,2023,16800);
Query OK, 1 row affected (0.03 sec)
16. mysql> insert into sale values(4,104,2010,19000);
Query OK, 1 row affected (0.03 sec)

17. mysql> insert into sale values(5,105,2022,17400);

Query OK, 1 row affected (0.03 sec)
18. mysql> insert into sale values(6,106,2020,15300);
Query OK, 1 row affected (0.05 sec)
19. mysql> select * from sale;

20. mysql> SELECT * FROM product WHERE product_id=(SELECT product_id FROM sale
WHERE sales_amount>=15000 AND product_id=product.product_id);

21. mysql> SELECT * FROM product WHERE product_id=(SELECT product_id FROM sale
WHERE sales_amount>=19000 AND product_id=product.product_id);

22. mysql> SELECT * FROM product WHERE product_id IN (SELECT product_id FROM
sale);

23. mysql> SELECT * FROM product WHERE product_id NOT IN (SELECT product_id
FROM sale);
Empty set (0.00 sec)

JOIN QUERY
24. mysql> SELECT p.product_id,p.product_name,p.product_price FROM product p JOIN
sale ON p.product_id=sale.product_id WHERE sales_amount>=3000;
25. mysql> SELECT DISTINCT p.product_id,p.product_name,p.product_price FROM
product p JOIN sale ON p.product_id=sale.product_id;

26. mysql> SELECT DISTINCT p.product_id,p.product_name,p.product_price FROM

product p JOIN sale s ON p.product_id=s.product_id WHERE s.product_id IS NULL;
Empty set (0.01 sec)

RESULT:
Thus the query to implement Subqueries and joins in database has been successfully executed.
EX.NO 5 QUERY THE DATABASE TABLES AND EXPLORE NATURAL, EQUI
AND OUTER JOINS.

AIM:
To create a table and execute the natural, equi and outer joins.

DESCRIPTION:
JOIN OPERATIONS  INNER JOIN/ NATURAL JOIN/ JOIN:
It is a binary operation that allows us to combine certain selections and a Cartesian product into one
operation. Inner Join

An Inner Join returns only the rows in both tables that match the join condition.

Equi Join
An Equi Join returns all the rows in both tables where the specifiedcolumns are equal.
Syntax of Equi Join
SELECT table1.column1, table2.column2
FROM table1
INNER JOIN table2
ON table1.columnX = table2.columnY;
Natural Join
A Natural Join is a type of Join that matches columns with the same name in both tables.
Syntax of Natural Join
SELECT table1.column1, table2.column2
FROM table1
NATURAL JOIN table2;

Outer Join

An Outer Join in DBMS returns all the rows from one table and the matching rows from the
other table. If there is no match, NULL values are returned for the missing rows.

Left Outer Join

A Left Outer Join in DBMS returns all the rows from the left table and the matching rows
from the right table. If there is no match, NULL values are returned for the missing rows.
Syntax of Left Outer Join
SELECT table1.column1, table2.column2

FROM table1

LEFT JOIN table2

ON table1.columnX = table2.columnY;

Example of Left Outer Join

Again Considering the above two tables:

Query:
To perform a Left Outer Join, we can join the two tables on the ID column.
SELECT Table1.Name, Table2.Address

FROM Table1

LEFT JOIN Table2

ON Table1.ID = Table2.ID;

Right Outer Join

A Right Outer Join returns all the rows from the right table and the matching rows from the
left table. If there is no match, NULL values are returned for the missing rows.
Syntax of Right Outer Join
SELECT table1.column1, table2.column2

FROM table1

RIGHT JOIN table2

ON table1.columnX = table2.columnY;

CREATING TABLES FOR DOING JOIN AND NESTED QUERY OPERATIONS

1. mysql> create database five;
Query OK, 1 row affected (0.23 sec)

2. mysql> use five;

Database changed

3. mysql> create table table1(ID int, Name Varchar(30), Age int);

Query OK, 0 rows affected (0.39 sec)

4. mysql> create table table1(ID int, Name Varchar(30), Age int);

ERROR 1050 (42S01): Table 'table1' already exists

5. mysql> insert into table1 values(1,"Alice",23);

Query OK, 1 row affected (0.08 sec)

6. mysql> insert into table1 values(1,"Bob",28);

Query OK, 1 row affected (0.18 sec)

7. mysql> select * from table1;

8. mysql> insert into table1 values(1,"Charlie",28);

Query OK, 1 row affected (0.03 sec)

9. mysql> insert into table1 values(2,"Bob",28);

Query OK, 1 row affected (0.02 sec)

10. mysql> insert into table1 values(3,"Charlie",30);

Query OK, 1 row affected (0.02 sec)

11. mysql> create table table2(ID int, Address varchar(30),Salary int);

Query OK, 0 rows affected (0.07 sec)

12. mysql> insert into table2 values(2, "NewYork",50000);

Query OK, 1 row affected (0.06 sec)

13. mysql> insert into table2 values(3, "California",75000);

Query OK, 1 row affected (0.03 sec)

14. mysql> insert into table2 values(4, "Texas",59000);

Query OK, 1 row affected (0.03 sec)

15. mysql> select * from table2;

16. mysql> SELECT Table1.Name, Table2.Address FROM Table1 INNER JOIN Table2 ON
Table1.ID = Table2.ID;
17. mysql> SELECT Table1.ID, Table1.Name, Table1.Age, Table2.Address, Table2.Salary FROM
Table1 NATURAL JOIN Table2;

18. mysql> SELECT Table1.Name, Table2.Address FROM Table1 LEFT JOIN Table2 ON
Table1.ID = Table2.ID;

19. mysql> SELECT Table1.Name, Table2.Address FROM Table1 RIGHT JOIN Table2 ON
Table1.ID = Table2.ID;

RESULT
Thus the relationship between databases has been implemented using join operation.

EX.NO 6 FUNCTIONS AND PROCEDURES IN MYSQL

AIM:
To write MYSQL programs that executes the concept of procedures.
DEFINITION:
A procedure or function is a logically grouped set of SQL and PL/SQL statements that perform a
specific task. They are essentially sub-programs.
Procedures and functions are made up of,
• Declarative part
• Executable part
• Optional exception handling part
These procedures and functions do not show the errors.
KEYWORDS AND THEIR PURPOSES REPLACE:
It recreates the procedure if it already exists.
PROCEDURE: It is the name of the procedure to be created.
ARGUMENT: It is the name of the argument to the procedure. Paranthesis can be omitted if no
arguments are present.
IN: Specifies that a value for the argument must be specified when calling the procedure ie. used to
pass values to a sub-program. This is the default parameter.
OUT: Specifies that the procedure passes a value for this argument back to it’s calling environment
after execution ie. used to return values to a caller of the sub-program.
INOUT: Specifies that a value for the argument must be specified when calling the procedure and
that procedure passes a value for this argument back to it’s calling environment after execution.
RETURN: It is the datatype of the function’s return value because every function must return a
value, this clause is required.
SYNTAX:
CREATE
[DEFINER = user]
PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

CREATE
[DEFINER = user]
FUNCTION [IF NOT EXISTS] sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body

proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic: {
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
routine_body:
Valid SQL routine statement
1. mysql> create database six;
Query OK, 0 rows affected (0.60 sec)

2. mysql> use six;

Database changed
3. mysql> delimiter //
4. mysql> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)
-> BEGIN
-> SELECT COUNT(*) INTO cities FROM world.city

-> WHERE CountryCode = country;

-> END//
Query OK, 0 rows affected (0.60 sec)
5. mysql> delimiter ;
6. mysql> CALL citycount('JPN', @cities);
Query OK, 1 row affected (0.35 sec)
7. mysql> SELECT @cities;

8. mysql> CALL citycount('FRA', @cities);

Query OK, 1 row affected (0.00 sec)
9. mysql> SELECT @cities;
10. mysql> CREATE FUNCTION hello (s CHAR(20))
-> RETURNS CHAR(50) DETERMINISTIC
-> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.10 sec)
11. mysql> SELECT hello('world');

Result:
The MYSQL queries to create procedures and functions were executed and their
respective outputs were verified.
EX.NO 7 DCL AND TCL COMMANDS IN MYSQL

AIM:
To create the database creation and execute the DML and TCL Commands

Data Control Language

Data Control Language is used to manage roles, permissions, and referential integrity on the
database.
Here are some commands that come under DCL:

 GRANT

 REVOKE

GRANT

GRANT command is used to give access or permission to specific users.

Syntax:

GRANT object_privileges ON table_name TO user_name1;

REVOKE
REVOKE is used for taking back permission, which is given to the user.
Syntax:
REVOKE object_privileges ON table_name FROM user1, user2,… userN
Transaction Control Language

TCL manages the issues and matters related to the transactions in any database. They are used to rollback
or commit the changes in the database. Here are some commands that come under TCL:

 COMMIT

 ROLLBACK

COMMIT

The COMMIT command is used to save all the transactions to the database.

Syntax:

COMMIT;

ROLLBACK

The rollback command is used to undo transactions that have not already been saved to the database.

Syntax:
ROLLBACK;

1. mysql> CREATE DATABASE SEVEN;

2. mysql> use seven;
Database changed

3. mysql> CREATE TABLE Employee1( Id INT, NAME VARCHAR(30) NOT NULL,

Email VARCHAR(100),Age INT CHECK (Age>= 20), PRIMARY KEY (Id),UNIQUE(Email));
Query OK, 0 rows affected (2.25 sec)

4. mysql> INSERT INTO Employee1 values('1','AAA','[email protected]','25');

Query OK, 1 row affected (0.72 sec)

5. mysql> INSERT INTO Employee1 values('2','BBB','[email protected]','23');

Query OK, 1 row affected (0.17 sec)

6. mysql> INSERT INTO Employee1 values('3','CCC','[email protected]','29');

Query OK, 1 row affected (0.13 sec)

7. mysql> INSERT INTO Employee1 values('4','DDD','[email protected]','27');

Query OK, 1 row affected (0.12 sec)

8. mysql> INSERT INTO Employee1 values('5','EEE','[email protected]','28');

Query OK, 1 row affected (0.14 sec)

9. mysql> select * from Employee1;

10. GRANT SELECT, UPDATE ON Employee1 TO AAA;

Query OK, 0 rows affected (0.00 sec)

11. REVOKE UPDATE ON Employee1 FROM AAA;

Query OK, 0 rows affected (0.00 sec)
12. mysql> delete from Employee1 where ID=5;
Query OK, 1 row affected (0.12 sec)

13. mysql> commit;

Query OK, 0 rows affected (0.00 sec)

14. mysql> ROLLBACK;

Query OK, 0 rows affected (0.00 sec)’

15. mysql> select * from EMployee1;

RESULT:
Thus the queries to perform DCL and TCL were created, executed and their respective
outputs were verified.
EX.NO 8 TRIGGERS IN MYSQL

AIM
To study and implement the concepts of triggers.
DEFINITION
A trigger is a statement that is executed automatically by the system as a side effect of a modification to
the database. The parts of a trigger are,
Trigger statement: Specifies the DML statements and fires the trigger body. It also specifies the table to
which the trigger is associated.
Trigger body or trigger action: It is a PL/SQL block that is executed when The triggering statement is
used.
Trigger restriction: Restrictions on the trigger can be achieved The different uses of triggers are as
follows,
• To generate data automatically
• To enforce complex integrity constraints
• To customize complex securing authorizations
• To maintain the replicate table
• To audit data modifications
TRIGGERS - SYNTAX
CREATE [OR REPLACE] TRIGGER trigger_ nameBEFORE|AFTER
[INSERT,UPDATE,DELETE[COLUMNNAME..]
ON table_name
Referencing[OLDASOLD|NEWAS NEW]
FOREACHROW|FOREACHSTATEMENT [ WHEN Condition]
DECLARE
[declaration_section
variable declarations;constantdeclarations;
]
BEGIN
[executable_section
PL/SQLexecute/subprogram body
] EXCEPTION
[exception_section
PL/SQLExceptionblock ]

1. mysql> create database tr;

Query OK, 1 row affected (0.02 sec)
2. mysql> use tr;
Database changed
3. mysql> CREATE TABLE test1(a1 INT);
Query OK, 0 rows affected (0.06 sec)
4. mysql> CREATE TABLE test2(a2 INT);
Query OK, 0 rows affected (0.25 sec)

5. mysql> CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
Query OK, 0 rows affected (0.08 sec)

6. mysql> CREATE TABLE test4(

-> a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> b4 INT DEFAULT 0
-> );
Query OK, 0 rows affected (0.06 sec)

7. mysql> delimiter |
8. mysql> CREATE TRIGGER testref BEFORE INSERT ON test1
-> FOR EACH ROW
-> BEGIN
-> INSERT INTO test2 SET a2 = NEW.a1;
-> DELETE FROM test3 WHERE a3 = NEW.a1;
-> UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
-> END;
-> |
Query OK, 0 rows affected (0.01 sec)
9. mysql> delimiter ;
10. mysql> INSERT INTO test3 (a3) VALUES
-> (NULL), (NULL), (NULL), (NULL), (NULL),
-> (NULL), (NULL), (NULL), (NULL), (NULL);
Query OK, 10 rows affected (0.04 sec)
Records: 10 Duplicates: 0 Warnings: 0
11. mysql> INSERT INTO test4 (a4) VALUES
-> (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
Query OK, 10 rows affected (0.03 sec)
Records: 10 Duplicates: 0 Warnings: 0
12. mysql> INSERT INTO test1 VALUES
-> (1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.05 sec)
Records: 8 Duplicates: 0 Warnings: 0
13. mysql> SELECT * FROM test1;

14. mysql> SELECT * FROM test2;

15. mysql> SELECT * FROM test3;

16. mysql> SELECT * FROM test4;

RESULT
Thus the Triggers were created, executed and their respective outputs were verified.

EX.NO 9 VIEWS AND INDEXES IN MYSQL

AIM:
To create view and index for database tables with a large number of records in mysql server.
DEFINITION:
 Views Helps to encapsulate complex query and make it reusable.
 A view is nothing more than a SQL statement that is stored in the database with an associated
name. A view is actually a composition of a table in the form of a predefined SQL query.
 A view can contain all rows of a table or select rows from a table. A view can be created from one
or many tables which depends on the written SQL query to create a view.
 Views, which are a type of virtual tables allow users to do the following
 Structure data in a way that users or classes of users find natural or intuitive.
 Restrict access to the data in such away that a user can see and(sometimes) modify exactly what
they need and no more.
 Summarize data from various tables which can be used to generate reports.
INDEX
 Indexes are used to retrieve data from the database more quickly than otherwise. The users
cannot see the indexes. They are just used to speed up searches/queries.
1. mysql> CREATE DATABASE mytest;
Query OK, 1 row affected (0.02 sec)
2. mysql> USE mytest;
Database changed
3. mysql> CREATE TABLE example (
-> col1 INT PRIMARY KEY,
-> col2 INT NOT NULL,
-> col3 INT NOT NULL,
-> col4 VARCHAR(20),
-> INDEX (col2, col3)
-> );
Query OK, 0 rows affected (0.09 sec)
4. mysql> SHOW INDEXES FROM example;
5. mysql> CREATE TABLE example2 (col1 INT PRIMARY KEY, col2 VARCHAR(20),
col3 VARCHAR(20), col4 ARCHAR(20) );
Query OK, 0 rows affected (0.31 sec)
6. mysql> SHOW INDEXES FROM example2;

7. mysql> CREATE INDEX index1 ON example2 (col2,col3);

Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
8. mysql> SHOW INDEXES FROM example2;

9. mysql> create table StudentInformation(Id int, Name varchar(20));

Query OK, 0 rows affected (0.06 sec)
10. mysql> CREATE VIEW view_Student AS SELECT Id,Name from StudInfo;
Query OK, 0 rows affected (0.03 sec)
11. mysql> SHOW CREATE VIEW view_Student;
12. mysql> CREATE TABLE CreatingTableUsingViewStudent AS select Id,Name from
view_Student;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

RESULT
Thus the View and index for database tables has been executed successfully.
EX.NO 10 XML DOCUMENT CREATION AND VALIDATION

Aim
To create a XML database file and Validate the Schema .

Algorithm
Step 1: Start
Step 2:Open MySQL command prompt(version.5.5)
Step 3:Create new database as bookstore and use it.
Step 4:Create XML Schema for data values and load values
Step 5:Validate XML using ExtractValue function.
Step 6:Stop

CREATE TABLE

1. CREATE TABLE person (

person_id INT NOT NULL PRIMARY KEY, fname VARCHAR(40) NULL,
lname VARCHAR(40) NULL,
created TIMESTAMP );

XML FILE PERSON.XML

<list>
<personperson_id="1"fname="Kapek"lname="Sainnouine"/>
<personperson_id="2"fname="Sajon"lname="Rondela"/>
<personperson_id="3"><fname>Likame</fname><lname>Örrtmons</lname></person>
<personperson_id="4"><fname>Slar</fname><lname>Manlanth</lname></person>
<person><fieldname="person_id">5</field><fieldname="fname">Stoma</field>
<fieldname="lname">Milu</field></person>
<person><fieldname="person_id">6</field><fieldname="fname">Nirtam</field>
<fieldname="lname">Sklöd</field></person>
<personperson_id="7"><fname>Sungam</fname><lname>Dulbåd</lname></person>
<personperson_id="8"fname="Sraref"lname="Encmelt"/>
</list>
INSERT VALUES USING LOADXMLDATAFILE
2. LOAD XML LOCAL INFILE 'c:/db/person.xml' //this is ths location of the xml data file INTO TABLE
person
ROWS IDENTIFIED BY '<person>';

3.MySQL>Select * from person;

VALIDATE XML USING EXTRACTVALUE FUNCTION

3. MySQL> SELECT ExtractValue('<?xml version="1.0" encoding="UTF-8"?>

Result
Thus the XML Database schema is created and Validated.
EX.NO 11 CREATE DOCUMENT, COLUMN AND GRAPH BASED
DATA USING NOSQL DATABASE TOOLS.

AIM
To Create Document,column and Graph using NOSQL Tools.
ALGORITHM
Step 1:Start
Step 2:Create Database in MongoDB
Step 3:Create Collection and Document in MongoDB
Step 4:Display all document
Step 5:Stop

Create database in mongodb

>Install Mongodb shell
>Connect with localhost
>Connection string: mongodb://localhost:27017
Create collection in mongodb

1. use <database_name>command

Create document in mongodb

2. mydbnew>db.details.insertOne({"website":"mywebsite"})
Display all documents

3. Mydbnew>Db.details.find()

CREATING CHART USING SAMPLE DATA

PROCEDURE:
Step 1: Log into MongoDB Atlas.
To access the MongoDB Charts application, you must be logged into Atlas

Step 2: Select your desired Atlas project, or create a new project.

If you have an Atlas Project with clusters containing data you wish to visualize,
Step 3: Select the project from the Context dropdown in the left navigation pane.

Step 4: Create an Atlas cluster. The MongoDB Charts application makes it easy to connect
Collections in your cluster as data sources. Data sources reference specific collections and
charts views that you can access in the Chart Builder to visualize the data in those collections
or charts views.

Step 5: Launch the MongoDB Charts application. In Atlas, click Charts in the navigation bar.

Step 6: Choose data from clusters

RESULT:
Thus the Creation of Document, column and graph based data using NOSQL database
tools was successfully created, executed and verified.
EX.NO 12 GUI BASED DATABASE APPLICATIONS

Aim
To develop a program in python to implement the GUI based application
Algorithm
Step 1: Start
Step 2: Import necessary files to perform database operations
Step 3:Design Login Screen with User Name and Password fields. Step 4: Check with appropriate conditions
to login.
Step 5: Stop

PROGRAM
import tkinter as tk import MySQL.connector from tkinter import *
def submitact():
user = Username.get() passw = password.get()
print(f"The name entered by you is {user} {passw}") logintodb(user, passw)
def logintodb(user, passw):
# If password is enetered by the # user
if passw:
db = MySQL.connector.connect(host ="localhost", user = user,
password = passw, db ="College")
cursor = db.cursor()
# If no password is enetered by the # user
else:
db = MySQL.connector.connect(host ="localhost", user = user,
db ="College") cursor = db.cursor()
# A Table in the database
savequery = "select * from STUDENT"
try:
cursor.execute(savequery) myresult = cursor.fetchall()
# Printing the result of the # query
for x in myresult: print(x)
print("Query Executed successfully")

except:
db.rollback() print("Error occurred")

root = tk.Tk() root.geometry("300x300") root.title("DBMS Login Page")

# Defining the first row
lblfrstrow = tk.Label(root, text ="Username -", ) lblfrstrow.place(x = 50, y = 20)
Username = tk.Entry(root, width = 35) Username.place(x = 150, y = 20, width = 100)
lblsecrow = tk.Label(root, text ="Password -") lblsecrow.place(x = 50, y = 50)
password = tk.Entry(root, width = 35) password.place(x = 150, y = 50, width = 100)
submitbtn = tk.Button(root, text ="Login",
bg ='blue', command = submitact) submitbtn.place(x = 150, y = 135, width = 55)
root.mainloop()

Result
Thus the simple GUI application has been created and executed successfully.

EX.NO 13 CASE STUDY USING ANY OF THE REAL LIFE DATABASE

APPLICATIONS - INVENTORY MANAGEMENT FOR A EMART
GROCERY SHOP

AIM:
To create a mini project named Inventory Control System.

DESCRIPTION:
Inventory Control System is a project which allows to maintain the stocks and sell the products and
update the stock.
It has three forms
• Main Menu form
• Stock Form.
• Sales Form
Main Menu Form :
It allows to choose the option whether stock entry or sales entry.
Stock Form:
It allows to enter the product id, product name, quantity, unit price and reorder value.
Sales Form:
It allows to sell the product by choosing the product id and specifying the sales quantity. It checks
whether the sales quantity is less than or equal to available quantity and also checks whether the
remaining quantity after sales is lesser than reorder level. If so, it disallows sales.
The information entered is stored in the database.

DATABASE

TABLES:STOCK TABLE
CREATE TABLE
stock(
Prodid INT PRIMARY KEY, prodname VARCHAR2(50), quantity INT,
unitprice INT, reorder int
);

SALES TABLE
CREATE TABLE
sale(
prodid INT REFERENCES stock(prodid),
prodname VARCHAR2(50), unitprice INT,
50
salesqty INT,
datetime VARCHAR2(50)
);

SAMPLE CODING:

STOCK ENTRY FORM

STOCK ENTRY:
package conn;
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import
javax.swing.JOptionPane;
import oracle.jdbc.OraclePreparedStatement;import oracle.jdbc.OracleResultSet;
public class stockentry extends javax.swing.JFrame {
Connection conn=null; OraclePreparedStatement pst=null; OracleResultSet rs=null;
private void btnInsert_clickActionPerformed(java.awt.event.ActionEvent evt) {
//TODO add your handling code here: try
{

Class.forName("oracle.jdbc.OracleDriver"); Connection conn =

DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE"
, "hemesh", "123");
String sql=” Insert into stock(prodid,prodname,quantity,unitprice,reorder)values(?,?,?,?,?)";
PreparedStatement pst=conn.prepareStatement(sql); pst.setString(1,txt_prodid.getText());
pst.setString(2,txt_prodname.getText()); pst.setString(3,txt_quantity.getText());
pst.setString(4,txt_unitprice.getText()); pst.setString(5,txt_reorder.getText());
pst.execute(); JOptionPane.showMessageDialog(null,"Successfully Inserted");
}
catch (Exception e)
{
JOptionPane.showMessageDialog(null, e);
}
}
private void btnUpdate_clickActionPerformed(java.awt.event.ActionEvent evt) {
//TODO add your handling code here: try
{

Class.forName("oracle.jdbc.OracleDriver"); Connection conn =

DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "hemesh", "123");
String sql="update stock set prodname=?,quantity=?,unitprice=?,reorder=? where prodid=?";
PreparedStatement pst=conn.prepareStatement(sql); pst.setString(1,txt_prodname.getText());
pst.setString(2, txt_quantity.getText()); pst.setString(3, txt_unitprice.getText()); pst.setString(4,
txt_reorder.getText()); pst.setString(5,txt_prodid.getText()); pst.executeUpdate();
JOptionPane.showMessageDialog(null,"Successfully Updated");
}
catch (Exception e)
{
JOptionPane.showMessageDialog(null,e);
}

STOCK SALES FORM

CODING: STOCK SALES

package stock; import
java.sql.Connection;import java.util.Date;
import java.sql.DriverManager; import java.text.SimpleDateFormat;import javax.swing.JOptionPane;
import java.sql.PreparedStatement;import java.sql.ResultSet;
public class stocksale extends javax.swing.JFrame {public stocksale() {
initComponents();additems();
}

private void
{
//TODO add your handling code here:Try
{
Date d = new Date();
SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("dd-MM-yyyy 'at' HH:mm:ss a");
String date = DATE_FORMAT.format(d); int i=Integer.parseInt(txt_salesqty.getText());
Class.forName("oracle.jdbc.OracleDriver") Connectionconn=
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "hemesh", “123");
String sql="update stock set quantity=quantity-'"+i+"' where prodid=?"; PreparedStatement
pst=conn.prepareStatement(sql);

pst.setString(1,jComboBox1.getSelectedItem().toString());pst.executeUpdate();
Stringsql1="Insertintosale(prodid,prodname,unitprice,salesqty,datetime) values(?,?,?,?,?)";
PreparedStatement pst1=conn.prepareStatement(sql1);
pst1.setInt(1,Integer.parseInt(jComboBox1.getSelectedItem().toString())); pst1.setString(2,
txt_prodname.getText());
pst1.setInt(3,Integer.parseInt( txt_unitprice.getText())); pst1.setInt(4,
Integer.parseInt(txt_salesqty.getText())); pst1.setString(5,date);
pst1.execute();
JOptionPane.showMessageDialog(null, "Sucessfully Inserted");
}
catch (Exception e)
{

JOptionPane.showMessageDialog(null, e);
}
}
private void jComboBox1ItemStateChanged(java.awt.event.ItemEvent evt) {
//TODO add your handling code here:try
{
Class.forName("oracle.jdbc.OracleDriver");
Connectionconn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1 521
:XE", "hemesh", "123");
String sql="select * from stock where prodid=?"; PreparedStatement pst=conn.prepareStatement(sql);
pst.setString(1, jComboBox1.getSelectedItem().toString()); ResultSet rs=pst.executeQuery();
if(rs.next())
{
txt_prodname.setText(rs.getString("prodname")
);

txt_unitprice.setText(rs.getString("unitprice")); txt_salesqty.setText(rs.getString("salesqty"));
}
}
}
public void additems()
{
try
{
Class.forName("oracle.jdbc.OracleDriver"); Connectionconn=
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "hemesh", 123"); String
sql="select prodid from stock"; PreparedStatement pst=conn.prepareStatement(sql);ResultSet
rs=pst.executeQuery();
while(rs.next())
{
}
jComboBox1.addItem(rs.getInt("prodid"));
}
}

RESULT:

Thus the mini project of Inventory control system using E-mart grocery shop project has been
successfully completed.