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

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:1Create a database table, add constraints (primary key, unique, check, Not null), insert rows,

update and delete rows using SQL DDL and DML commands.

Aim :

To Create a database table using SQL queries(DDL and DML commands).

Procedure
Create a database
Create a table
Insert the values into table
USE ALTER,DELETE and UPDATE Command

Creating a Database

CREATE DATABASE Test;

Creating a Table

CREATE TABLE <TABLE_NAME> (column_name1 datatype1, column_name2 datatype2, column_name3

datatype3, column_name4 datatype4);
SQL: ALTER command

alter command is used for altering the table structure, such as,

 to add a column to existing table

 to rename any existing column
 to change datatype of any column or to modify its size.
 to drop a column from the table.

ALTER Command: Add a new Column

ALTER TABLE table_name ADD(column_name datatype);

ALTER Command: Add multiple new Columns

ALTER TABLE table_name ADD( column_name1 datatype1, column-name2 datatype2, );

ALTER Command: Add Column with default value

ALTER TABLE table_name ADD( column-name1 datatype1 DEFAULT some_value);

ALTER Command: Modify an existing Column

ALTER TABLE table_name modify(column_name datatype);

ALTER Command: Rename a Column

ALTER TABLE table_name RENAME old_column_name TO new_column_name;

ALTER Command: Drop a Column

ALTER command can also be used to drop or remove columns. ALTER TABLE

table_name DROP(column_name);

DROP TABLE table_name

TRUNCATE query

TRUNCATE TABLE tablename;

RENAME query
 RENAME command is used to set a new name for any existing table. RENAME TABLE

old_table_name to new_table_name;

DML COMMAND

Using INSERT SQL command

Data Manipulation Language (DML) statements are used for managing data in database. DML
commands are not auto-committed. It means changes made by DML command are not permanent
to database, it can be rolled back.

INSERT command

Insert command is used to insert data into a table. Following is its general syntax,

INSERT INTO table_name VALUES(data1, data2, ...)

Insert value into only specific columns

INSERT INTO student(id, name) values(value, value);

The above SQL query will only insert id and name values in the newly inserted record.

Insert NULL value to a column

Both the statements below will insert NULL value into age column of the student

table.

INSERT INTO student(id, name) values(value, value);

Or,
INSERT INTO Student VALUES(102,'Alex', null);

The above command will insert only two column values and the other column is set to null.

S_id S_Name age

101 Adam 15
102 Alex

Insert Default value to a column

INSERT INTO Student VALUES(103,'Chris', default)
S_id S_Name age
101 Adam 15
102 Alex
103 chris 14

Suppose the column age in our tabel has a default value of 14.

Also, if you run the below query, it will insert default value into the age column, whatever the
default value may be.

INSERT INTO Student VALUES(103,'Chris')

UPDATE command

UPDATE command is used to update any record of data in a table.

UPDATE table_name SET column_name = new_value WHERE some_condition;

WHERE is used to add a condition to any SQL query,

student_id name age

101 Adam 15
102 Alex
103 chris 14
UPDATE student SET age=18 WHERE student_id=102;

S_id S_Name age

101 Adam 15
102 Alex 18
103 chris 14

In the above statement, if we do not use the WHERE clause, then our update query will update
age for all the columns of the table to 18.

Updating Multiple Columns

We can also update values of multiple columns using a single UPDATE statement.

UPDATE student SET name='Abhi', age=17 where s_id=103;

The above command will update two columns of the record which has s_id 103.

s_id name age

101 Adam 15
102 Alex 18
103 Abhi 17

UPDATE Command: Incrementing Integer Value

UPDATE student SET age = age+1;

DELETE command
DELETE FROM table_name;

s_id Name age

101 Adam 15

102 Alex 18

103 Abhi 17

Delete all Records from a Table

DELETE FROM student;

DELETE FROM student WHERE s_id=103;

The above command will delete the record where s_id is 103 from the table student.

S_id S_Name age

101 Adam 15
102 Alex 18
SQL - SELECT Query

The SQL SELECT statement is used to fetch the data from a database table which returns this
data in the form of a result table. These result tables are called result-sets.

Syntax

The basic syntax of the SELECT statement is as follows −

SELECT column1, column2, columnN FROM table_name;

Here, column1, column2... are the fields of a table whose values you want to fetch. If you want to
fetch all the fields available in the field, then you can use the following syntax.

SELECT * FROM table_name;

Example
Consider the CUSTOMERS table having the following records −
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+

The following code is an example, which would fetch the ID, Name and Salary fields of the
customers available in CUSTOMERS table.

SQL> SELECT ID, NAME, SALARY FROM CUSTOMERS;

This would produce the following result –

+----------+----------+
| ID | NAME | SALARY |
+----+----------+----------+
| 1 | Ramesh | 2000.00 |
| 2 | Khilan | 1500.00 |
| 3 | kaushik | 2000.00 |
| 4 | Chaitali | 6500.00 |
| 5 | Hardik | 8500.00 |
| 6 | Komal | 4500.00 |
| 7 | Muffy | 10000.00 |
+----+----------+----------+

If you want to fetch all the fields of the CUSTOMERS table, then you should use the following
query.

SQL> SELECT * FROM CUSTOMERS;

This would produce the result as shown below.
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
RESULT:
Thus the above DML and DDL commands are executed successfully.

VIVA QUESTIONS:
1. Differentiate DDL and DML commands
2. What is the use of select command?
3. Difference between alter and update command
4. How will you delete a column from table?
5. Difference between drop and truncate command
6. Explain different ways to insert rows in a table.
Ex.No:2 Foreign Key And Referential Integrity Constraints

Aim

To create a set of tables and add foreign key and referential integrity constraints.

Procedure

Step 1:Start

Step 2:Create Table Department and Employee with necessary Step 3:Add Foreign

Key constraints in department table by altering it.

Step 4:Check referential integrity constraints by perform any operation.

Step 5: Stop

Department

CREATE TABLE Department( Id INT

PRIMARY KEY, Name
NVARCHAR(50));
-- Insert some test data in Department Table Insert into
Department values (10, 'IT'); Insert into Department
values (20, 'HR'); Insert into Department values (30,
'INFRA');

Employees

CREATE TABLE Employees( Id INT

PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
DepartmentID INT
);

-- Adding the Foreign Key Constraint

ALTER TABLE Employees ADD FOREIGN KEY (DepartmentId) REFERENCES
Department(Id);

-- Insert some test data in Employees Table

INSERT into Employees VALUES (101, 'Anurag', 10); INSERT
into Employees VALUES (102, 'Pranaya', 20); INSERT into
Employees VALUES (103, 'Hina', 30);

Delete from Parent Table

DELETE FROM Department WHERE Id = 10;

Output

SQL> DELETE from Department where Id=10;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constrai

nt fails (`db2`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`Departm entID`)

REFERENCES `department` (`Id`))

RESULT:

Thus the above queries were executed successfully.

VIVA QUESTIONS:

1. List out different types of keys in DBMS?

2. What is the use of Primary key?
3. How you will identify a primary key?
4. Can primary key contain Null value.
5. Define foreign key.
6. List out some benefits of using primary ke and foreign key.
Ex.No: 3 Queries with Where Clause and Aggregate Functions.
Aim

To write queries using WHERE clause and Aggregate Functions.

Procedure
Step 1: Start

Step 2: Write queries using different WHERE Clause

Step 3:Write queries for Aggregate functions like count,avg,min,max

Step 4:stop

WHERE Clause

Syntax:

SELECT * from CUSTOMERS;

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+

SELECT STATEMENT, WHERE THE SALARY IS GREATER THAN 2000

select id, name, salary

from customers
where salary > 2000;
+----+----------+----------+
| ID | NAME | SALARY |
+----+----------+----------+
| 4 | Chaitali | 6500.00 |
| 5 | Hardik | 8500.00 |
| 6 | Komal | 4500.00 |
| 7 | Muffy | 10000.00 |
+----+----------+----------+
UPDATE STATEMENT
UPDATE CUSTOMERS
set AGE = 30
where AGE = 25;

SELECT * from CUSTOMERS;

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 30 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 30 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
WHERE CLAUSE WITH IN OPERATOR

Select * from customers where name in ('khilan', 'hardik', 'muffy');

+----+--------+-----+---------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+--------+-----+---------+----------+
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+--------+-----+---------+----------+

WHERE CLAUSE WITH NOT IN OPERATOR

select * from customers

where age not in (25, 23, 22);

+----+--------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+--------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+--------+-----+-----------+----------+

AGGREGATE FUNCTIONS

Consider a table named "Product_Mast" that contains the following data.

PRODUCT COMPANY QTY RATE COST

Item1 Com1 2 10 20

Item2 Com2 3 25 75

Item3 Com1 2 30 60

Item4 Com3 5 10 50

Item5 Com2 2 20 40

Item6 Cpm1 3 25 75

Item7 Com1 5 30 150

Item8 Com1 3 10 30

Item9 Com2 2 25 50

Item10 Com3 4 30 120

COUNT FUNCTION
Syntax
select count(*) from product_mast;
Output:
10
SUM FUNCTION
Syntax
select sum(cost) from product_mast;
Output:
670

AVG FUNCTION
Syntax
select avg(cost) from product_mast;
Output:
67.00

MAX FUNCTION
Syntax
select max(rate) from product_mast;
Output:
30

MIN FUNCTION
Syntax
select min(rate) from product_mast;

Output:
10

RESULT
Thus the queries were executed successfully.

VIVA QUESTIONS:
1. What is the use of where clause?
2. What is the use of count function?
3. What is the use of sum function?
4. How will you find the small and large value in a table?
5. How will you find the average value in a table?
Ex no 4: Query the Database Tables and Explore Sub Queries and Simple Join Operations.

Aim:
To create a sub queries and simple join operations.

Procedure
Step 1: Start

Step 2: Write queries using Sub Queries

Step 3:Write queries for Simple Join Operations.

Step 4:stop

SQL - Using Joins

The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is
a means for combining fields from two tables by using values common to each.
Consider the following two tables −
Table 1 − CUSTOMERS Table
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Table 2 − ORDERS Table
+-----+---------------------+-------------+--------+
|OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3| 3000 |
| 100 | 2009-10-08 00:00:00 | 3| 1500 |
| 101 | 2009-11-20 00:00:00 | 2| 1560 |
| 103 | 2008-05-20 00:00:00 | 4| 2060 |
+-----+---------------------+-------------+--------+

SQL> SELECT ID, NAME, AGE,

AMOUNT FROM CUSTOMERS,
ORDERS
WHERE CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
+----+----------+-----+--------+
| ID | NAME | AGE | AMOUNT |
+----+----------+-----+--------+
| 3 | kaushik | 23 | 3000 |
| 3 | kaushik | 23 | 1500 |
| 2 | Khilan | 25 | 1560 |
| 4 | Chaitali | 25 | 2060 |
+----+----------+-----+--------+

SQL - INNER JOINS

The most important and frequently used of the joins is the INNER JOIN. They are also referred
to as an EQUIJOIN.

Syntax

SELECT table1.column1, table2.column2...

FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

Example
Consider the following two tables.
Table 1 − CUSTOMERS Table is as follows.
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+

Table 2 − ORDERS Table is as follows.

+-----+---------------------+-------------+--------+
| OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3| 3000 |
| 100 | 2009-10-08 00:00:00 | 3| 1500 |
| 101 | 2009-11-20 00:00:00 | 2| 1560 |
| 103 | 2008-05-20 00:00:00 | 4| 2060 |
+-----+---------------------+-------------+--------+

Now, let us join these two tables using the INNER JOIN as follows −
SQL> SELECT ID, NAME,
AMOUNT, DATE FROM
CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
This would produce the following result.
+----+----------+--------+---------------------+
| ID | NAME | AMOUNT | DATE |
+----+----------+--------+---------------------+
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
+----+----------+--------+---------------------+
SQL - LEFT JOINS
Syntax
The basic syntax of a LEFT JOIN is as follows.
SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;
Example
Consider the following two tables,

Table 1 − CUSTOMERS Table is as follows.

+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Table 2 − Orders Table is as follows.
+-----+---------------------+-------------+--------+
| OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3| 3000 |
| 100 | 2009-10-08 00:00:00 | 3| 1500 |
| 101 | 2009-11-20 00:00:00 | 2| 1560 |
| 103 | 2008-05-20 00:00:00 | 4| 2060 |
-----+---------------------+-------------+--------+
SQL> SELECT ID, NAME,
AMOUNT, DATE FROM
CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
+----+----------+--------+---------------------+
| ID | NAME | AMOUNT | DATE |
+----+----------+--------+---------------------+
| 1 | Ramesh | NULL | NULL |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
| 5 | Hardik | NULL | NULL |
| 6 | Komal | NULL | NULL |
| 7 | Muffy | NULL | NULL |
+----+----------+--------+---------------------+

SQL - RIGHT JOINS

The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the
left table. This means that if the ON clause matches 0 (zero) records in the left table; the join will
still return a row in the result, but with NULL in each column from the left table.

Syntax
The basic syntax of a RIGHT JOIN is as follow.
SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;
Table 1 − CUSTOMERS Table is as follows.
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Table 2 − ORDERS Table is as follows.
+-----+---------------------+-------------+--------+
|OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3| 3000 |
| 100 | 2009-10-08 00:00:00 | 3| 1500 |
| 101 | 2009-11-20 00:00:00 | 2| 1560 |
| 103 | 2008-05-20 00:00:00 | 4| 2060 |
+-----+---------------------+-------------+--------+
Now, let us join these two tables using the RIGHT JOIN as follows.
SQL> SELECT ID, NAME,
AMOUNT, DATE FROM
CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
This would produce the following result −
+------+----------+--------+---------------------+
| ID | NAME | AMOUNT | DATE |
+------+----------+--------+---------------------+
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+
SQL - FULL JOINS
The SQL FULL JOIN combines the results of both left and right outer joins.
Syntax
The basic syntax of a FULL JOIN is as follows −
SELECT table1.column1, table2.column2...
 FROM table1
FULL JOIN table2
ON table1.common_field = table2.common_field;
Table 1 − CUSTOMERS Table is as follows.
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
+----+----------+-----+-----------+----------+
Table 2 − ORDERS Table is as follows.
+-----+---------------------+-------------+--------+
|OID | DATE | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 | 3| 3000 |
| 100 | 2009-10-08 00:00:00 | 3| 1500 |
| 101 | 2009-11-20 00:00:00 | 2| 1560 |
| 103 | 2008-05-20 00:00:00 | 4| 2060 |
+-----+---------------------+-------------+--------+
Now, let us join these two tables using FULL JOIN as follows.
SQL> SELECT ID, NAME,
AMOUNT, DATE FROM
CUSTOMERS
FULL JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
+------+----------+--------+---------------------+
| ID | NAME | AMOUNT | DATE |
+------+----------+--------+---------------------+
| 1 | Ramesh | NULL | NULL |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
| 5 | Hardik | NULL | NULL |
| 6 | Komal | NULL | NULL |
7 | Muffy | NULL | NULL |
| 3 | kaushik | 3000 | 2009-10-08 00:00:00 |
| 3 | kaushik | 1500 | 2009-10-08 00:00:00 |
| 2 | Khilan | 1560 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 |
+------+----------+--------+---------------------+

RESULT:
Thus the queries were executed successfully.

VIVA QUESTIONS:
1. Define Join.
2. List out different types of join
3. Write the syntax for inner join.
4. Difference between inner and outer join.
5. Use of full join.
Ex.No :5 Natural Join, Equi Join And Outer Join

Aim

To write a query to perform natural join , equi join and outer join.

Procedure

Step 1: Start

Step 2:Create table with necessary attributes .

Step 3: Perform natural join,equi join and outer join operations with queries.

Step 4: Stop

Syntax:

Table-1: department

create table department(dept_name varchar(20),manager_name varchar(255));

insert into department(dept_name,manager_name) values ( "it", "rohan");
insert into department(dept_name,manager_name) values ( "sales", "rahul");
insert into department(dept_name,manager_name) values ( "hr", "tanmay");
insert into department(dept_name,manager_name) values ( "finance", "ashish");
insert into department(dept_name,manager_name) values ("marketing", "samay");
select * from employee;
Output :

EMP_ID EMP_NAME DEPT_NAME

1 Sumit Hr

2 Joel It

3 Biswa Marketing

4 Vaibhav It

5 Sagar Sales

Table-2: mployee –

Create Table employee(EMP_ID int,EMP_NAME Varchar(20),DEPT_NAME Varchar(255));

insert into employee(emp_id, emp_name, dept_name) values (1, "sumit", "hr");
insert into employee(emp_id, emp_name, dept_name) values (2, "joel", "it");
insert into employee(emp_id, emp_name, dept_name) values (3, "biswa", "marketing");
insert into employee(emp_id, emp_name, dept_name) values (4, "vaibhav", "it");
insert into employee(emp_id, emp_name, dept_name) values (5, "sagar", "sales");
select * from department;
Output :
DEPT_NAME MANAGER_NAME

It Rohan

Sales Rahul

Hr Tanmay

Finance Ashish

Marketing Samay

Natural Join :

select *from employee natural join department;

Output :

EMP_ID EMP_NAME DEPT_NAMEMANAGER_NAME

1 Sumit Hr Tanmay

2 Joel It Rohan

3 Biswa Marketing Samay

4 Vaibhav It Rohan

5 Sagar Sales Rahul

EQUI JOIN

SQL> select * from category;

CATEGORY_ID CATEGORY_NAME

1 Mobiles

2 Laptops

3 Laptops

4 Cameras

5 Gaming
select * from product;

CATEGORY_ID PRODUCT_NAME

1 Nokia

1 Samsung

2 HP

2 Dell

3 Apple

4 Nikon

Null Playstation

SQL> select *
from product join category
on product.category_id = category.category_id;
CATEGORY_ID PRODUCT_NAME CATEGORY_ID CATEGORY_NAME
----------- --------------------- ----------- --------------------- --------------------- ---------------------
1 Nokia 1 Mobiles
1 Samsung 1 Mobiles
2 HP 2 Laptops
2 Dell 2 Laptops
3 Apple 3 Tablet
4 Nikon 4 Cameras

6 rows selected.

OUTER JOIN

BASIC SYNTAX FOR LEFT JOIN:

select table1.column1, table2.column2....
from table1
leftjoin table2
on table1.column_field = table2.column_field;
CUSTOMER TABLE:

ID NAME AGE SALARY

1 Aryan 51 56000

2 Arohi 21 25000

3 Vineet 24 31000

4 Ajeet 23 32000

5 Ravi 23 42000

ORDER TABLE:
 O_ID DATE CUSTOMER_ID AMOUNT

001 20-01-2012 2 3000

002 12-02-2012 2 2000

003 22-03-2012 3 4000

004 11-04-2012 4 5000

sql select id, name, amount,date

from customer
left join order
on customer.id = order.customer_id;

ID NAME AMOUNT DATE

1 Aryan Null Null

2 Arohi 3000 20-01-2012

2 Arohi 2000 12-02-2012

3 Vineet 4000 22-03-2012

4 Ajeet 5000 11-04-2012

5 Ravi Null Null

SQL RIGHT JOIN

sql> select id,name,amount,date
from customer
right join order
on customer.id = order.customer_id;

ID NAME AMOUNT DATE

2 AROHI 3000 20-01-2012

2 AROHI 2000 12-02-2012

3 VINEET 4000 22-03-2012

4 AJEET 5000 11-04-2012

SYNTAX FOR FULL OUTER JOIN:

select * from table1 full outer join table2 on table1.column_name = table2.column_name;

TABLE_A
 A M

1 m

2 n

4 o

TABLE_B

A N

2 p

3 q

5 r

RESULTING TABLE

A M A N

2 n 2 p

1 m - -

4 o - -

- - 3 q

- - 5 r

RESULT

Thus the queries were executed successfully.

VIVA QUESTIONS:
1. Define Join.
2. What is the use of Equi join?
3. Define Natural Join.
4. Difference between inner and outer join.
5. Use of full join.

Ex.No:6 Procedure and Functions

Aim :
To write a SQL block to display the student name, marks whose average mark is above

60%.

Algorithm

STEP 1:Start

STEP 2:Create a table with table name stud_exam

STEP 3:Insert the values into the table and Calculate total and average of each student

STEP 4: Execute the procedure function the student who get above 60%.

STEP 5: Display the total and average of student

STEP 6: End

Setting Serveroutput On:

SQL> SET SERVEROUTPUT ON

Program:

Procedure Using Positional Parameters:

SQL> SET SERVEROUTPUT ON

SQL> CREATE OR REPLACE PROCEDURE PROC1 AS 2
BEGIN
3 DBMS_OUTPUT.PUT_LINE('Hello from procedure...');
4 END;
5/

Output

Procedure created.

SQL> EXECUTE PROC1

Hello from procedure...

PL/SQL procedure successfully completed.

SQL> create table student(regno number(4),name varchar2)20),mark1 number(3), mark2

number(3), mark3 number(3), mark4 number(3), mark5 number(3));

Table created
SQL> insert into student values (101,'priya', 78, 88,77,60,89); 1

row created.
SQL> insert into student values (102,'surya', 99,77,69,81,99);

1 row created.
SQL> insert into student values (103,'suryapriya', 100,90,97,89,91);

1 row created.
SQL> select * from student;

regno name mark1 mark2 mark3 mark4 mark5

101 priya 78 88 77 60 89
102 surya 99 77 69 81 99
103 suryapriya 100 90 97 89 91

SQL> declare
2 ave number(5,2);
3 tot number(3);
4 cursor c_mark is select*from student where mark1>=40 and mark2>=40 and
5 mark3>=40 and mark4>=40 and mark5>=40;
6 begin
7 dbms_output.put_line('regno name mark1 mark2 mark3 mark4 mark4 mark5 total
8 average');
9 dbms_output.put_line(' ');
10 for student in c_mark
11 loop
12 tot:=student.mark1+student.mark2+student.mark3+student.mark4+student.mark5;
13 ave:=tot/5;
14 dbms_output.put_line(student.regno||rpad(student.name,15)
15 ||rpad(student.mark1,6)||rpad(student.mark2,6)||rpad(student.mark3,6)
16 ||rpad(student.mark4,6)||rpad(student.mark5,6)||rpad(tot,8)||rpad(ave,5));
17 end loop;
18 end;
19 /

OUTPUT

regno name mark1 mark2 mark3 mark4 mark5 total average

101 priya 78 88 77 60 89 393 79

102 surya 99 77 69 81 99 425 85
103 suryapriya 100 90 97 89 91 467 93

PL/SQL procedure successfully completed.

Functions

Aim To write a Functional procedure to search an address from the given database.

Procedure

STEP 1: Start

STEP 2: Create the table with essential attributes.

STEP 3: Initialize the Function to carryout the searching procedure..

STEP 4: Frame the searching procedure for both positive and negative searching.

STEP 5: Execute the Function for both positive and negative result .

STEP 6: Stop

SQL> create table phonebook (phone_no number (6) primary key,username varchar2(30),doorno
varchar2(10),
street varchar2(30),place varchar2(30),pincode char(6));

Table created.

SQL> insert into phonebook values(20312,'vijay','120/5D','bharathi street','NGO

colony','629002');

1 row created.

SQL> insert into phonebook values(29467,'vasanth','39D4','RK bhavan','sarakkal vilai','629002');

1 row created.

SQL> select * from phonebook;

PHONE_NO USERNAME DOORNO STREET PLACE PINCODE

20312 vijay 120/5D bharathi street NGO colony 629002

29467 vasanth 39D4 RK bhavan sarakkal vilai 629002

SQL> create or replace function findAddress(phone in number) return varchar2 as address

varchar2(100);
begin
select username||','||doorno ||','||street ||','||place||','||pincode into address from phonebook where
phone_no=phone;
return address;
exception
when no_data_found then return 'address not found';
end;
/

Function created.

SQL>declare
2 address varchar2(100);
3 begin
4 address:=findaddress(20312);
5 dbms_output.put_line(address);
6 end;
7/
Output

Vijay,120/5D,bharathi street,NGO colony,629002

Result

Thus the PL/SQL procedure successfully completed.

VIVA QUESTIONS:
1. Define PL/SQL.
2. Define procedure.
3. Use of select command
4. Define function.
5. Define procedure.
Ex.No:7 DCL And TCL Commands

Aim

To write a query to perform DCL and TCL commands.

Procedure

Step 1: Start

Step 2: Create table with necessary attributes.

Step 3: Perform DCL query like GRANT and REVOKE

Step 4: Perform TCL like SAVEPOINT,ROLLBACK and COMMIT. Step 5:

Stop.

DCL COMMANDS

GRANT

GRANT privilege_name ON object_name TO {user_name |PUBLIC |role_name} [WITH

GRANT OPTION];

MySQL> GRANT SELECT ON employee TO

user1;Command Successfully Completed REVOKE

REVOKE privilege_name ON object_name FROM {user_name |PUBLIC |role_name}

MySQL> REVOKE SELECT ON employee FROM

user1;Command Successfully Completed

TCL(TRNSACTION CONTROL LANGUAGE) SQL>

SAVEPOINT S1;
Savepoint created.

SQL> SELECT * FROM EMP;

EMPNO ENAME DESIGNATIN SALARY

101 NAGARAJAN LECTURER 16000

102 SARAVANAN ASST. PROF 16000
104 CHINNI HOD, PROF 45000

SQL> INSERT INTO EMP VALUES(105,'PARTHASAR','STUDENT',100);

1 row created.

SQL> SELECT * FROM EMP;

EMPNO ENAME DESIGNATIN SALARY

105 PARTHASAR STUDENT 100

101 NAGARAJAN LECTURER 16000
102 SARAVANAN ASST. PROF 16000
104 CHINNI HOD, PROF 45000
ROLL BACK

SQL> ROLL BACK S1;

Rollback complete.

SQL> SELECT * FROM EMP;

EMPNO ENAME DESIGNATIN SALARY

101 NAGARAJAN LECTURER 16000

102 SARAVANAN ASST. PROF 16000
104 CHINNI HOD, PROF 45000

COMMIT

SQL> COMMIT;

Commit complete.

Result

Thus the queries were executed successfully.

VIVA QUESTIONS:
1. List out the command under DCL.
2. List out commands under TCL.
3. Explain the use grant and revoke command
4. What is the use of Rollback command.
5. Purpose of save point.
Ex.No:8 Creation of Database Triggers

Aim

To create database triggers using PL/SQL code.

Procedure

STEP 1: Creates a trigger for insertion of each row.

STEP 2: Declare a cursor which contains the roll number field
STEP 3: Before insertion check of the roll number already exists in the
table STEP 4: If it exists raise an application earror and display “roll no
exists”.
STEP 5: Else perform insertion

Syntax
create or replace trigger trigger name [before/after] {DML
statements} on [table name] [for each row/statement]
begin

exception
end;

PROGRAM
SQL>create table poo(rno number(5),name
varchar2(10)); Table created.
SQL>insert into poo values
(01.‟kala‟); 1 row created.
SQL>select * from poo;

RNO NAME
------ ----------
1 kala
2 priya

SQL>create or replace trigger pool before insert on poo for each row
2 declare
3 rno poo.rno%type
4 cursor c is select rno from
poo; 5 begin
6 open c;
7 loop;
8 fetch c into rno;
9 if:new.rno=rno then
10 raise_application_error(-20005,‟rno already exist‟);
 11 end if;
12 exit when c%NOTFOUND
13 end loop;
14 close c;
15 end;
16 /
Trigger created.

OUTPUT
SQL>insert into poo
values(01,‟kala‟) Insert into poo
values (01,‟kala‟)
*
ERROR at line1:
ORA-20005:rno already exist
ORA-06512:”SECONDCSEA.POOL”,line 9
ORA-04088:error during execution at trigger “SECONDCSEA.POOL”

Result:
Thus the PL/SQL blocks are developed for triggers and the results are verified.

VIVA QUESTIONS:
1. Define Trigger.
2. What is the use of Begin and End statement?
3. Define Block.
4. How will you create a database?
5. Write the syntax for creating trigger.
Ex.No:9 Views And Index

Aim

To execute and verify the SQL commands for Views and Indexes.

Procedure

STEP 1: Start

STEP 2: Create the table with its essential attributes.

STEP 3: Insert attribute values into the table.

STEP 4: Create the view from the above created table.

STEP 5: Execute different Commands and extract information from the View.

STEP 6: Stop.

CREATION OF TABLE

SQL> CREATE TABLE EMPLOYEE (

EMPLOYEE_NAMEVARCHAR2(10),
EMPLOYEE_NONUMBER(8),
DEPT_NAME VARCHAR2(10),
DEPT_NO NUMBER (5),DATE_OF_JOIN DATE);

Table created.

TABLE DESCRIPTION
SQL> DESC EMPLOYEE;

NAME NULL? TYPE

EMPLOYEE_NAME VARCHAR2(10)
EMPLOYEE_NO NUMBER(8)
DEPT_NAME VARCHAR2(10)
DEPT_NO NUMBER(5)
DATE_OF_JOIN DATE

CREATION OF VIEW
SQL> CREATE VIEW EMPVIEW AS SELECT
EMPLOYEE_NAME,EMPLOYEE_NO,DEPT_NAME,DEPT_NO,DATE_OF_JOIN FROM
EMPLOYEE;
view created.

DESCRIPTION OF VIEW

SQL> DESC EMPVIEW;

NAME NULL? TYPE

EMPLOYEE_NAME VARCHAR2(10)
EMPLOYEE_NO NUMBER(8)
DEPT_NAME VARCHAR2(10)
DEPT_NO NUMBER(5)
DISPLAY VIEW

SQL> SELECT * FROM EMPVIEW;

EMPLOYEE_N EMPLOYEE_NO DEPT_NAME DEPT_NO

RAVI 124 ECE 89

VIJAY 345 CSE 21
RAJ 98 IT 22
GIRI 100 CSE 67

INSERTION INTO VIEW

SQL> INSERT INTO EMPVIEW VALUES ('SRI', 120,'CSE', 67,'16-NOV-1981');

1 ROW CREATED.

SQL> SELECT * FROM EMPVIEW;

EMPLOYEE_N EMPLOYEE_NO DEPT_NAME DEPT_NO
RAVI 124 ECE 89
VIJAY 345 CSE 21
RAJ 98 IT 22
GIRI 100 CSE 67
SRI 120 CSE 67

SQL> SELECT * FROM EMPLOYEE;

EMPLOYEE_N EMPLOYEE_NO DEPT_NAME DEPT_NO DATE_OF_J

RAVI 124 ECE 89 15-JUN-05

VIJAY 345 CSE 21 21-JUN-06
RAJ 98 IT 22 30-SEP-06
GIRI 100 CSE 67 14-NOV-81
SRI 120 CSE 67 16-NOV-81

DELETION OF VIEW

DELETE STATEMENT
SQL> DELETE FROM EMPVIEW WHERE EMPLOYEE_NAME='SRI';

SQL> SELECT * FROM EMPVIEW;

EMPLOYEE_N EMPLOYEE_NO DEPT_NAME DEPT_NO

RAVI 124 ECE 89

VIJAY 345 CSE 21
RAJ 98 IT 22
GIRI 100 CSE 67

UPDATE STATEMENT:

SQL> UPDATE EMPKAVIVIEW SET EMPLOYEE_NAME='KAVI' WHERE

EMPLOYEE_NAME='RAVI';

1 ROW UPDATED.

SQL> SELECT * FROM EMPKAVIVIEW;

EMPLOYEE_N EMPLOYEE_NO DEPT_NAME DEPT_NO

KAVI 124 ECE 89

VIJAY 345 CSE 21
RAJ 98 IT 22
GIRI 100 CSE 67
DROP A VIEW:

SQL>DROP VIEW EMPVIEW;

VIEW DROPED

CREATE INDEX
MySQL> CREATE DATABASEindexes;Query OK, 1 row affected (0.01 sec)USE indexes;
Database changed
MySQL>CREATE TABLE employees (employee_id int, first_name varchar(50), last_name
varchar(50),device_serial varchar(15),salary int ); Query OK, 0 rows affected (0.00 sec)
INSERT INTO employees VALUES

(1, 'John', 'Smith', 'ABC123', 60000), (2, 'Jane', 'Doe', 'DEF456', 65000),

(3, 'Bob', 'Johnson', 'GHI789', 70000), (4, 'Sally', 'Fields', 'JKL012', 75000),

(5, 'Michael', 'Smith', 'MNO345', 80000), (6, 'Emily', 'Jones', 'PQR678', 85000),

(7, 'David', 'Williams', 'STU901', 90000), (8, 'Sarah', 'Johnson', 'VWX234', 95000),

(9, 'James', 'Brown', 'YZA567', 100000);

Query OK, 9 rows affected (0.010

sec) Records: 9 Duplicates: 0

Warnings: 0

MySQL>CREATE INDEX salary ON employees(salary);

Mqsql>EXPLAIN SELECT * FROM employees WHERE salary = 100000;

+ + + + + + + + + + +

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered
| 1 | SIMPLE | employees | NULL | ref | salary | salary | 5 | const | 1 | 100.00 |
+ + + + + + + + + + +

1 row in set, 1 warning (0.00 sec)

Result
Thus views and indexes created successful.

VIVA QUESTIONS:

1. Define View.
2. What is the purpose of using Index?
3. What is the use of drop command?
4. How will you delete views?
EX.NO:10 XML DATABASE 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

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

LOAD XML LOCAL INFILE 'c:/db/person.xml' //this is ths location of the xml

data file INTO TABLE person

ROWS IDENTIFIED BY '<person>';
 OUTPUT

MySQL>Select * from person;

VALIDATE XML USING EXTRACTVALUE FUNCTION

MySQL> SELECT

ExtractValue('<?xml version="1.0" encoding="UTF-8"?>

Result

Thus the XML Database is created and Validated.

VIVA QUESTIONS:
1. Define XML.
2. Define XML Schema.
3. What is the purpose of XML?
4. How will you create a XML Database?
5. How will you validate the XML Database?
EX.NO:11 CREATING DOCUMENT, COLUMNS & GRAPH USING NOSQL

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

output:
Create collection in mongodb

use <database_name> command

OUTPUT:

Create document in mongodb

mydbnew>db.details.insertOne({"website":"mywebsite"})
Output:

Display all documents

Db.details.find()
 Output

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 asdata 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

OUTPUT

Result

Thus the Document and Graph is created.

VIVA QUESTIONS:

1. What are the columns called in NOSQL?

2. What type of graph is NOSQL?
3. How documents are stored in NOSQL?
4. What is the use of Graph database?
5. What is use of Mongodb?
 EX.NO:12 SIMPLE GUI APPLICATION USING DATABASE

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.connectorfrom
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()

Output:

Result

Thus the GUI application program executed successfully.

VIVA QUESTIONS:

1. What is a GUI application?

2. What is database and why it is essential in software development?
3. How will you establish a connection between GUI application and database?
4. How will you handle errors in GUI?
5. Describe the steps involved in displaying data from a database in a GUI
 application.
EX.NO:13 CASE STUDY USING REALTIME DATABASE APPLICATIONS

Aim
To study bank management system using realtime database applications.

ER diagram of Bank Management System

ER diagram is known as Entity-Relationship diagram. It is used to analyze to structure of the

Database. It shows relationships between entities and their attributes. An ER model provides a
means of communication.

ER diagram of Bank has the following description :

 Banks are identified by a name, code, address of main office.

 Bank have Customer

 Banks have branches.

 Branches are identified by a branch_no., branch_name, address.

 Customers are identified by name, cust-id, phone number, address.

 Customer can have one or more accounts.

 Accounts are identified by account_no., acc_type, balance.

 Customer can avail loans.

 Loans are identified by loan_id, loan_type and amount.

 Account and loans are related to bank’s branch.

Entities and their Attributes are :

 Bank Entity : Attributes of Bank Entity are Bank Name, Code and Address.
Code is Primary Key for Bank Entity.
 Customer Entity : Attributes of Customer Entity are Customer_id, Name, Phone
Number and Address.
Customer_id is Primary Key for Customer Entity.
 Branch Entity : Attributes of Branch Entity are Branch_id, Name and Address.
Branch_id is Primary Key for Branch Entity.
 Account Entity : Attributes of Account Entity are Account_number, Account_Type and
Balance.
Account_number is Primary Key for Account Entity.
 Loan Entity : Attributes of Loan Entity are Loan_id, Loan_Type and Amount.
Loan_id is Primary Key for Loan Entity.

This bank ER diagram illustrates key information about bank, including entities such as
branches, customers, accounts, and loans. It allows us to understand the relationships between
entities.
ER Diagram of Bank Management System :

Relationships are :

 Bank has Branches => 1 : N

One Bank can have many Branches but one Branch can not belong to many Banks, so
the relationship between Bank and Branch is one to many relationship.

 Branch maintain Accounts => 1 : N

One Branch can have many Accounts but one Account can not belong to many
Branches, so the relationship between Branch and Account is one to many
relationship.

 Branch offer Loans => 1 : N

One Branch can have many Loans but one Loan can not belong to many Branches, so
the relationship between Branch and Loan is one to many relationship.

 Account held by Customers => M : N

One Customer can have more than one Accounts and also One Account can be held
by one or more Customers, so the relationship between Account and Customers is
many to many relationship.
  Loan availed by Customer => M : N
(Assume loan can be jointly held by many Customers).
One Customer can have more than one Loans and also One Loan can be availed by one
or more Customers, so the relationship between Loan and Customers is many to many
relationship.

NORMALIZATION PROCESS
Database normalization is a stepwise formal process that allows us to decompose
database tables in such a way that both data dependency and update anomalies are minimized. It
makes use of functional dependency that exists in the table and primary key or candidate key in
analyzing the tables. Normal forms were initially proposed called First Normal Form
(INF), Second Normal Form (2NF), and Third Normal Form (3NF). Subsequently, R, Boyce, and
E. F. Codd introduced a stronger definition of 3NF called Boyce-Codd Normal Form. With the
exception of 1NF, all these normal forms are based on functional dependency among the
attributes of a table. Higher normal forms that go beyond BCNF were introduced later such as
Fourth Normal Form (4NF) and Fifth Normal Form (5NF). However, these later normal forms
deal with situations that are very rare.

TRIGGERS

CREATE TRIGGER update_account AFTER INSERTON transactionsBEGIN

UPDATE accounts a SETa.balance=
(CASE WHEN new.withdrawal=1 THEN a.balance-new.amount ELSE
a.balance+new.amountEND) WHERE a.id = new.accountID;
END;
pseudocode, Represents

 If the transaction is a deposit, add the money

 If the transaction is a withdrawal, check if it is discretionary
 If it is discretionary, remove from the balance and the allowance remaining
 If it is not, remove only from the balance.

ACID properties in DBMS

To ensure the integrity and consistency of data during a transaction (A transaction is a unit of
program that updates various data items, read more about it here), the database system
maintains four properties. These properties are widely known as ACID properties.

Atomicity

This property ensures that either all the operations of a transaction reflect in database or
none. The logic here is simple, transaction is a single unit, it can’t execute partially. Either it
executes completely or it doesn’t, there shouldn’t be a partial execution.

Let’s take an example of banking system to understand this:

Suppose Account A has a balance of 400$ & B has 700$. Account A is transferring 100$ to
Account B.

This is a transaction that has two operations

a) Debiting 100$ from A’s balance
b) Creating 100$ to B’s balance.

Let’s say first operation passed successfully while second failed, in this case A’s balance would
be 300$ while B would be having 700$ instead of 800$. This is unacceptable in a banking
system. Either the transaction should fail without executing any of the operation or it should
process both the operations. The Atomicity property ensures that.

There are two key operations are involved in a transaction to maintain the atomicity of the
transaction.

Abort: If there is a failure in the transaction, abort the execution and rollback the changes made
by the transaction.

Commit: If transaction executes successfully, commit the changes to the database.

Consistency

Database must be in consistent state before and after the execution of the transaction. This
ensures that there are no errors in the database at any point of time. Application programmer is
responsible for maintaining the consistency of the database.

Example:
A transferring 1000 dollars to B. A’s initial balance is 2000 and B’s initial balance is 5000.
Before the transaction:
Total of A+B = 2000 + 5000 = 7000$

After the transaction:

Total of A+B = 1000 + 6000 = 7000$

The data is consitendct before and after the execution of the transaction so this example
maintains the consistency property of the database.

Isolation

A transaction shouldn’t interfere with the execution of another transaction. To preserve the
consistency of database, the execution of transaction should take place in isolation (that means
no other transaction should run concurrently when there is a transaction already running).

For example account A is having a balance of 400$ and it is transferring 100$ to account B & C
both. So we have two transactions here. Let’s say these transactions run concurrently and both
the transactions read 400$ balance, in that case the final balance of A would be 300$ instead of
200$. This is wrong.

If the transaction were to run in isolation then the second transaction would have read the
correct balance 300$ (before debiting 100$) once the first transaction went successful.

Durability

Once a transaction completes successfully, the changes it has made into the database should
be permanent even if there is a system failure. The recovery-management component of
database systems ensures the durability of transaction.

STORED PROCEDURE

CREATE PROCEDURE [bank].[GetTransactions]

-- Add the parameters for the stored procedure here
@AccountID int = 0,
@StartDate datetime =
0, @EndDate datetime =
0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT
statements. SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT * from bank.Transactions
WHERE AccountID = @AccountID AND [Date] BETWEEN @StartDate AND @EndDate
END

Second, here's the EXEC statment:

EXEC bank.GetTransactions
@AccountID = 100000,
@StartDate = '4/1/2007',
@EndDate = '4/30/2007'
Result:

Thus the bank management system using realtime database applications was
successfully studied.

VIVA QUESTIONS:

1. What is meant by real time applications?

2. Can you provide examples of real time database applications commonly used today?
3. Explain the user interface in bank management system.
4. How does a real time database application handle transactions and account updates in a
bank management system?
5. Discuss the security measures and protocols used.