CS8481-DATABASE MANAGEMENT SYSTEM

Dharmapuri – 636 703

LAB MANUAL
Regulation : 2017

Branch : B.E. - CSE

Year & Semester : II Year / IV Semester

CS8481-DATABASE MANAGEMENT SYSTEM

LABORATORY

1
CS8481-DATABASE MANAGEMENT SYSTEM

ANNA UNIVERSITY CHENNAI

REGULATION -2017
CS 8481 – DATABASE MANAGEMENT SYSTEMS LABORATORY

LIST OF EXPERIMENTS:

1. Data Definition Commands, Data Manipulation Commands for inserting, deleting,

updating and retrieving Tables and Transaction Control statements

2. Database Querying – Simple queries, Nested queries, Sub queries and Joins

3. Views, Sequences, Synonyms

4. Database Programming: Implicit and Explicit Cursors

5. Procedures and Functions

6. Triggers

7. Exception Handling

8. Database Design using ER modeling, normalization and Implementation for any

application

9. Database Connectivity with Front End Tools

10. Case Study using real life database applications

TOTAL: 60 PERIODS

2
CS8481-DATABASE MANAGEMENT SYSTEM

INDEX

SIGNATURE
S.NO DATE NAME OF THE EXPERIMENTS OF THE REMARKS
STAFF

DATA DEFINITION COMMANDS,

1 DATA MANIPULATION
COMMANDS

2 DATABASE QUERING

3 VIEWS,SWQUENCES,SYNONYMS

IMPLICIT AND EXPLICIT

4
CURSORS

5 PROCEDURES AND FUNCTIONS

6 TRIGGERS

7 EXCEPTION HANDLING

8 ER MODELING

DATABASE CONNECTIVITY EITH

9
FONT END TOOLS

3
CS8481-DATABASE MANAGEMENT SYSTEM

DATABASE MANAGEMENT SYSTEM

OVERVIEW OF DBMS

A database is simply an organized collection of related data, typically stored on

disk, and accessible by possibly many concurrent users. A database management system
(DBMS) is system software for creating and managing databases. The DBMS provides
users and programmers with a systematic way to create, retrieve, update and manage
data.
Some of the Database software is Oracle RDBMS, IBM DB2, Microsoft SQL
Server, MySQL, Microsoft Access, FileMaker etc.

DATA TYPES

The following table contains some of the data types that are frequently used.

DATA TYPES DESCRIPTION

Number(size) : It can contain letters, numbers and special characters.

Number(p,s) : Number having precision p and scale s.

Varchar(size) : Holds a varying length of string (can contains letters, numbers and special

characters)

Char(size) : Holds a fixed length of string (can contains letters, numbers and special

characters)

Text : Holds a string with a maximum length of 255 characters

Date() : A date format: YYYY-MM-DD

Datetime() : A date and time combination: YYYY-MM-DD HH:MM:SS

DDL COMMANDS

1. The Create Table Command: - it defines each column of the table uniquely. Each
column has minimum of three attributes, a name , data type and size.
Syntax:
Create table <table name> (<col1> <datatype>(<size>),<col2> <datatype><size>));
Ex:create table emp(empno number(4) primary key, ename char(10));

4
CS8481-DATABASE MANAGEMENT SYSTEM

2.Modifying the structure of tables.

a) Add new columns
Syntax:
Alter table <tablename> add(<new col><datatype(size),<new col>datatype(size));
Ex:alter table emp add(sal number(7,2));

3. Dropping a column from a table.

Syntax:
Alter table <tablename> drop column <col>;
Ex:alter table emp drop column sal;

4. Modifying existing columns.

Syntax:
Alter table <tablename> modify(<col><newdatatype>(<newsize>));
Ex:alter table emp modify(ename varchar2(15));

5. Renaming the tables

Syntax:
Rename <oldtable> to <new table>;
Ex:rename emp to emp1;

6. truncating the tables.

Syntax:
Truncate table <tablename>;
Ex:trunc table emp1;

7. Destroying tables.
Syntax:
Drop table <tablename>;
Ex:drop table emp;

5
CS8481-DATABASE MANAGEMENT SYSTEM

DML COMMANDS

DML commands are the most frequently used SQL commands and is used to
query and manipulate the existing database objects. Some of the commands are Insert,
Select, Update, Delete.
Insert Command This is used to add one or more rows to a table. The values are
separated by commas and the data types char and date are enclosed in apostrophes. The
values must be entered in the same order as they are defined. Select Commands It is used
to retrieve information from the table. It is generally referred to as querying the table. We
can either display all columns in a table or only specify column from the table.
Update Command It is used to alter the column values in a table. A single column
may be updated or more than one column could be updated.
Delete command After inserting row in a table we can also delete them if required.
The delete command consists of a from clause followed by an optional where clause.

DCL COMMANDS

The DCL language is used for controlling the access to the table and hence
securing the database. DCL is used to provide certain privileges to a particular user.
Privileges are rights to be allocated. The privilege commands are namely, Grant and
Revoke. The various privileges that can be granted or revoked are, Select Insert Delete
Update References Execute All.

GRANT COMMAND:

It is used to create users and grant access to the database. It requires

database administrator (DBA) privilege, except that a user can change their password. A
user can grant access to their database objects to other users.

6
CS8481-DATABASE MANAGEMENT SYSTEM

REVOKE COMMAND:

Using this command , the DBA can revoke the granted database
privileges from the user.

TCL COMMAND

COMMIT: command is used to save the Records.

ROLL BACK: command is used to undo the Records.
SAVE POINT command is used to undo the Records in a particular transaction.

7
CS8481-DATABASE MANAGEMENT SYSTEM

EX NO: 1

DATE:

DATA DEFINITION COMMANDS, DATA MANIPULATION COMMANDS

AIM:

To create a database and how to perform the operations.

ALGORITHM:

Step 1: Create a table using sql query

Step 2: Enter the input in the created table

Step 3: To retrieve a data from table use select command

Step 4: The output will be displayed based on the query

PROGRAM:

DDL (DATA DEFINITION LANGUAGE) COMMAND:

1. CREATE

2. ALTER

3. DROP

4. TRUNCATE

5. COMMENT

6. RENAME

1.Creation of database and table:

mysql>create database db;

Query OK, 0 rows affected (0.14 sec)
mysql>use db;
Database changed

8
CS8481-DATABASE MANAGEMENT SYSTEM

2. Create the table:

SQL>CREATE TABLE EMP (EMPNO NUMBER (4),ENAME VARCHAR2 (10),

DESIGNATIN VARCHAR2 (10), SALARY NUMBER (8, 2));

Table created.

3.View the table structure:

SQL> DESC EMP;

Name Null? Type

--------------- ----------------

EMPNO NUMBER(4)

ENAME VARCHAR2(10)

DESIGNATIN VARCHAR2(10)

SALARY NUMBER(8,2)

4. Alter the Table:

SQL>ALTER TABLE EMP MODIFY EMPNO NUMBER (6);

Table altered.

SQL> DESC EMP;

Name Null? Type

--------------- ----------------

EMPNO NUMBER(6)

ENAME VARCHAR2(10)

DESIGNATIN VARCHAR2(10)

SALARY NUMBER(8,2)

SQL> ALTER TABLE EMP ADD QUALIFICATION VARCHAR2(6);

Table altered.

9
CS8481-DATABASE MANAGEMENT SYSTEM

SQL> DESC EMP;

Name Null? Type

--------------- ----------------

EMPNO NUMBER(6)

ENAME VARCHAR2(10)

DESIGNATIN VARCHAR2(10)

SALARY NUMBER(8,2)

QUALIFICATION VARCHAR2(6);

SQL>ALTER TABLE EMP ADD (DOB DATE, DOJ DATE);

Table altered.

5. Remove/Drop the Column:

SQL> ALTER TABLE EMP DROP COLUMN QUALIFICATION;

Table altered.

SQL> ALTER TABLE EMP DROP (DOB, QUALIFICATION);

Table altered

6. Rename the Table:

SQL> ALTER TABLE RENAME EMP TO EMPLOYEE;

Table altered

7. Truncate the Table:

SQL> Truncate table EMP;

8. Drop the table:

SQL> drop table employee;

10
CS8481-DATABASE MANAGEMENT SYSTEM

DML (DATA MANIPULATION LANGUAGE)

1. SELECT

2. INSERT

3. DELETE

4. UPDATE

1. Insert a record from an existing table:

SQL>INSERT INTO EMP VALUES (101,'NAGARAJAN','LECTURER',15000);

1 row created.

2. Insert A Record Using Substitution Method:

SQL> INSERT INTO EMP

VALUES(&EMPNO,'&ENAME','&DESIGNATIN','&SALARY');

Enter value for empno: 102

Enter value for ename: SARAVANAN

Enter value for designatin: LECTURER

Enter value for salary: 15000

1 row created.

3. Display the records from employee.

SQL> SELECT * FROM EMP;

EMPNO ENAME DESIGNATIN SALARY

---------- ------------ ---------------- ---------
101 NAGARAJAN LECTURER 15000

11
CS8481-DATABASE MANAGEMENT SYSTEM

4. Update the Table:

SQL> UPDATE EMP SET SALARY=16000 WHERE EMPNO=101;

1 row updated.

5. Delete the row from Table:

SQL> DELETE EMP WHERE EMPNO=103;

1 row deleted.

RESULT:

The database was created using the various commands.

12
CS8481-DATABASE MANAGEMENT SYSTEM

PRACTICE EXERCISES:

1. Create a table called EMP with the following structure.

Name Type

EMPNO NUMBER(6)

ENAME VARCHAR2(20)

JOB VARCHAR2(10)

DEPTNO NUMBER(3)

SAL NUMBER(7,2)

Allow NULL for all columns except ename and job.

2. Add a column experience to the emp table. experience numeric null allowed.

3. Create a table using NOT NULL and VARCHAR constraints.

4. Create dept table with the following structure.

Name Type
---------- ------------
DEPTNO NUMBER(2)

DNAME VARCHAR2(10)

LOC VARCHAR2(10)

5. Drop a column experience to the emp table.

6 .Truncate the emp table and drop the dept table

7. Alter a table for student mark list

8.Delete a table created for hospital management

9.Rename a table created for library management system.

10.Modify a table for payroll management system

13
CS8481-DATABASE MANAGEMENT SYSTEM

VIVA QUESTIONS:

1. Define a database system.

2. What are the categories of SQL command?

3. What is Data Storage - Definition Language?

4. What is Query evaluation engine?

5. What is DDL Interpreter?

6. What is durability in DBMS?

7. What is called metadata in context of DBMS ? Give a simple example.

8. Define the "integrity rules"?

9. In context of DBMS and its utilizing programs how you can explain the difference

between Data and Information ?

10. What is a query?

14
CS8481-DATABASE MANAGEMENT SYSTEM

EX NO: 2

DATE:
DATABASE QUERYING

AIM:
To create a database using Nested Queries, Sub Queries and different joins.

ALGORITHM:
Step 1: Create a table using sql query

Step 2: Enter the input in the created table

Step 3: To get related data from different tables use joins Query

Step 4: The output will be displayed based on the joins query.

PROGRAM:

SQL> CREATE TABLE CUSTOMERS (ID INT NOT NULL, NAME VARCHAR
(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY
DECIMAL (18, 2), PRIMARY KEY (ID));

SQL> INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

15
CS8481-DATABASE MANAGEMENT SYSTEM

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)

VALUES (6, 'Komal', 22, 'MP', 4500.00 );

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 |

1. Sub Queries:

SQL> SELECT * FROM CUSTOMERS WHERE ID IN (SELECT ID FROM

CUSTOMERS WHERE SALARY > 4500);

ID | NAME | AGE | ADDRESS | SALARY |

+---- ---------- ----- --------- ----------
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |

2. Sub Queries with Update Command:

SQL> UPDATE CUSTOMERS SET SALARY = SALARY * 0.25 WHERE AGE IN
(SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27);
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-------+-----------------+------------+
| 1 | Ramesh | 35 | Ahmedabad | 125.00 |
|2| Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 2125.00 |
| 6 | Komal | 22 | MP | 4500.00 |

3. Sub Queries with Delete Command:

SQL> DELETE FROM CUSTOMERS WHERE AGE IN (SELECT AGE FROM
CUSTOMERS_BKP WHERE AGE >= 27 );

ID | NAME| AGE | ADDRESS | SALARY |

+----+--------+-----+---------------+----------+
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 6 | Komal | 22 | MP | 4500.00 |

16
CS8481-DATABASE MANAGEMENT SYSTEM

4. Joins:
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

INNER JOIN

SQL> SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS

INNER JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
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

17
CS8481-DATABASE MANAGEMENT SYSTEM

LEFT JOIN
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

RIGHT JOIN:
SQL> SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
RIGHT JOIN ORDER ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
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

FULL JOINS:
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 | Hardi k | 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:0

18
CS8481-DATABASE MANAGEMENT SYSTEM

SELF JOINS:
SQL> SELECT a.ID, b.NAME, a.SALARY FROM CUSTOMERS a,
CUSTOMERS b WHERE a.SALARY < b.SALARY;

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

RESULT:

The database was created for relating between databases using the joins and sub
queries.

19
CS8481-DATABASE MANAGEMENT SYSTEM

PRACTICE EXERCISES:
1.Display the employee details, departments that the departments are same in both the

emp and dept.

2.Display the employee details, departments that the departments are not same in both the

emp and dept.

3.Display the Student name and grade by implementing a left outer join.

4.Display the Student name, register no, and result by implementing a right outer join.

5.Display the Student name register no by implementing a full outer join.

6.Write a query to display their employee names

7 .Display the details of those who draw the salary greater than the average salary.

8.Display the details of library management system by implementing the inner and outer

join

9.Display the details of hospital management system by implementing inner join right

outer join

10.Display the details of Hotel management system by implementing left outer join, Inner

join.

20
CS8481-DATABASE MANAGEMENT SYSTEM

VIVA QUESTIONS:

1.What is the use of sub queries?

2.Are the resulting relations of PRODUCT and JOIN operation the same?

3.What do you mean by Correlated sub query?

4.What are PL/SQL cursor exceptions?

5.How can you generate debugging output from PL/SQL?

6.Write SQL Query to display current date?

7.Difference between a query and a statement in SQL?

8.What is the difference between JOIN and UNION?

9.Define tuple Variable?

10.What is a record at a time?

21
CS8481-DATABASE MANAGEMENT SYSTEM

EX.NO:3

DATE:

VIEWS,SEQUENCES,SYNONYMS
AIM:
To create a database using Views.

ALGORITHM:

Step 1: Create a table using sql query

Step 2: Enter the input in the created table

Step 3 To retrieve a data from table use select command

Step 4: The output will be displayed based on the query

PROGRAM:

SQL> desc emp;

Name Null? Type

----------- - ------------ -------

EMPNO NOT NULL NUMBER (4)

ENAME NOT NULL VARCHAR2 (30)
JOB VARCHAR2 (10)
MGR NUMBER (4)
HIRE DATE DATE
SAL NUMBER (7,2)
DEPTNO NUMBER (2)

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO

3737 Priya Officer 7777 07-MAR-09 51000 7
4545 Priya Secretary 3337 09-JUN-09 21000 20
7575 Karthi Manager 3337 07-JUL-07 75000 20
5352 Retish Secretary 5555 09-JUN-09 20000 8
5332 Rocky Assist 5555 08-JAN-08 19500 7
6544 Santh Officer 3337 23-JAN-09 19000 8

6 rows selected.

22
CS8481-DATABASE MANAGEMENT SYSTEM

SQL> desc emp;

Name Null? Type

------------ ------------- ---------------
EMPNO NUMBER(4)
PH_NO NUMBER(8)

SQL> select * from emp;

EMPNO PH_NO
-------- --------
3737 225301
4545 485565
6544 789663
7575 896652
6555 987777

CREATE VIEW

View created from more than one table leading to ‘Read Only’ view.

SQL> create view view1 as

2 select emp.ename,empp.ph_no

3 from emp,empp

4 where emp.empno=empp.empno;

View created.

SQL> select * from view1;

ENAME PH_NO
------------ ------

Priya 225301

priya 485565

santh 789663

Karthi 896652

23
CS8481-DATABASE MANAGEMENT SYSTEM

SEQUENCES

Syntax: Sequence.NEXTVAL

Sequence.CURRVAL

CREATE SEQUENCE emp_sequence

INCREMENT BY 1

START WITH 1

NOMAXVALUE

NOCYCLE

CACHE 10;

MODIFYING THE SEQUENCE

ALTER SEQUENCE emp_sequence

INCREMENT BY 10

MAXVALUE 10000

CYCLE

CACHE 20;

DROPPING THE SEQUENCE

DROP SEQUENCE order_seq;

24
CS8481-DATABASE MANAGEMENT SYSTEM

SYNONYMS

Creating Synonyms

CREATE PUBLIC SYNONYM public_emp FOR jward.emp;

DROPPING SYNONYMS

DROP SYNONYM emp;

DROP PUBLIC SYNONYM public_emp;

RESULT:

The employee database was created and viewed using the various commands.

25
CS8481-DATABASE MANAGEMENT SYSTEM

QUERIES:

1. The organization wants to display only the details of the employees those who are

ASP. (Horizontal portioning).

2. The organization wants to display only the details like empno, empname, deptno,

deptname of the employees. (Vertical portioning).

3. Display all the views generated for payroll system

4. Execute the DML commands on the view created.

5. Drop a view for Railway reservation system

6. Delete View for Timetable managament

7. Create View from single relation known as ‘Updatable’ view for hospital

management

8. Update View only on the updatable views for student mark list.

9. Create view for Personal information system

10. Create and update views for hotel management system

26
CS8481-DATABASE MANAGEMENT SYSTEM

VIVA QUESTIONS:

1.Can you update the data in a SQL VIEW?

A VIEW in SQL is created by joining one or more tables. When you update
record(s) in a view, it updates the records in the underlying tables that make up the SQL
View. So, yes, you can update the data in a SQL VIEW providing you have the proper
privileges to the underlying SQL tables.

2.Does the SQL View exist if the table is dropped from the database?

Yes, in Oracle, the SQL VIEW continues to exist even after one of the tables (that
the SQL VIEW is based on) is dropped from the database. However, if you try to query
the SQL VIEW after the table has been dropped, you will receive a message indicating
that the SQL VIEW has errors. If you recreate the table (the table that you had dropped),
the SQL VIEW will again be fine.

3. What is normalization and why is it important?

4. What are some situations where you would de-normalize data?

5. What is a transaction and why is it important?

27
CS8481-DATABASE MANAGEMENT SYSTEM

EX.NO:4

DATE:

IMPLICIT AND EXPLICIT CURSORS

AIM:
To create a database program using implicit and explicit cursors.

ALGORITHM:
Step 1: Start a program

Step 2: Enter the input in the created table

Step 3: If there is exception throw it otherwise normal execution

Step 4: Stop the program.

PROGRAM: IMPLICIT CURSORS

Select * from customers;

ID NAME AGE ADDRESS SALARY

1 RAMESH 19 Ahmedabad 2000.00

2 KHILAN 22 Delhi 1500.00

3 kaushik 33 Kolkata 2000.00

4 komal 35 Mumbai 8500.00

The following program will update the table and increase the salary of each customer by
500 and use the SQL%ROWCOUNT attribute to determine the number of rows affected.

DECLARE

total_rows number(2);

BEGIN

UPDATE customers

SET salary = salary + 500;

28
CS8481-DATABASE MANAGEMENT SYSTEM

IF sql%notfound THEN

dbms_output.put_line('no customers selected');

ELSIF sql%found THEN

total_rows := sql%rowcount;

dbms_output.put_line( total_rows || ' customers selected ');

END IF;

END;
4 customers selected

PL/SQL procedure successfully completed.

If you check the records in customers table, we will find that the rows have been updated
−
Select * from customers;

ID NAME AGE ADDRESS SALARY

1 RAMESH 19 Ahmedabad 2500.00

2 KHILAN 22 Delhi 2000.00

3 kaushik 33 Kolkata 2500.00

4 komal 35 Mumbai 9000.00

29
CS8481-DATABASE MANAGEMENT SYSTEM

PROGRAM: EXPLICIT CURSORS

SYNTAX : CURSOR cursor_name IS select_statement;

DECLARE
c_id customers.id%type;
c_name customerS.No.ame%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;

dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);

END LOOP;
CLOSE c_customers;
END;
/
OUTPUT:

1 Ramesh Ahmedabad
2 Khilan Delhi
3 kaushik Kolkata
4 Komal Mumbai

RESULT;

Thus the Databse program using implicit and explicit cursor has been created and
executed successfully.

30
CS8481-DATABASE MANAGEMENT SYSTEM

PRACTICE EXERCISES

1.Write a PL/SQL script to display the following series of numbers: 99,96,93……9,6,3?

2.What are the ways on commenting in a PL/SQL code?

3.Write a program that shows the usage of WHILE loop to calculate the average of user

entered numbers and entry of more numbers are stopped by entering number 0?

4.Create a PL/SQL blocks having header or named bloacks.

VIVA QUESTIONS:

1.What is PL SQL ?

2.Differentiate between % ROWTYPE and TYPE RECORD.

3.Explain uses of cursor.

4.Show code of a cursor for loop.

5.Explain the uses of database trigger.

6.What are the two types of exceptions.

7.Show how functions and procedures are called in a PL SQL block.

31
CS8481-DATABASE MANAGEMENT SYSTEM

EX.NO:5

DATE:

PROCEDURES AND FUNCTIONS

AIM:

To write a PL/SQL query to create a procedure and functions

ALGORITHM:

Step 1: Create a table using sql query

Step 2: Enter the input in the created table

Step 3: To get related data using procedure and functions.

Step 4: The output will be displayed based on the joins query.

SQL> desc student;

Name Null? Type

REGNO NUMBER(4)
NAME VARCHAR2(20)
MARK1 NUMBER(3)
MARK2 NUMBER(3)
MARK3 NUMBER(3)
MARK4 NUMBER(3)
MARK5 NUMBER(3)

SQL> select * from student;

REGNO NAME MARK1 MARK2 MARK3 MARK4 MARK5

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

32
CS8481-DATABASE MANAGEMENT SYSTEM

Creation of Procedures

SQL> set serverout on;

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

regno name mark1 mark2 mark3 mark4 mark5 total average

101 priya 78 88 77 60 89 392 78.4
102 karthi 99 77 69 81 99 425 85

103 karthipriya 100 90 97 89 91 467 93.4

PL/SQL procedure successfully completed.

RESULT:

Thus the program for creation of procedure is executed successfully.

33
CS8481-DATABASE MANAGEMENT SYSTEM

PRACTICE EXERCISES:

1. Write a procedure to add an amount of Rs.1000 for the employees whose salaries

is greater than 5000 and who belongs to the deptno passed as an argument?

2. Write a PL/SQL block to update the salary of the employee with a 10% increase

3. Write a procedure to find the salary of the employee who is working in the deptno

20(to be passed as an argument?

4. Write a procedure to find the nature of job of the employee whose deptno is 20(to

be passed as an argument) ?

5. Write a PL/SQL block to obtain the department name of the employee who Works

for deptno 30?

6. Write a PL/SQL program that check whether given no is Armstrong number or

not?

7. Writing PL/SQL block for checking whether given number is prime or not?

8. Writing PL/SQL block for generating Palindrome?

9. Writing PL/SQL block for checking whether given number is prime or not?

10. Write a PL/SQL program that check whether given year is leap year or not?

34
CS8481-DATABASE MANAGEMENT SYSTEM

VIVA QUESTIONS:

1. Write the general syntax to create procedure?

2. How to execute a stored procedure?

3. How to pass parameters in PL/SQL?

4. Write a general syntax to pass IN parameter, OUT parameter, IN-OUT parameter?

5. What are the two parts of procedure?

6. How to know the last executed procedure?

7. What is the restriction on cursor variables?

8. What are the advantages of stored procedure?

9. How to avoid cursors. What to instead of cursors and in what case to do so ?

10. What will happen after commit statement?

35
CS8481-DATABASE MANAGEMENT SYSTEM

EX.NO:6

DATE:

TRIGGERS

AIM:
To write a PL/SQL query to create triggers..

ALGORITHM:

Step 1: Create a table using sql query

Step 2: Enter the input in the created table

Step 3: The output will be displayed based on the query

Step 4: Stop the program

SQL> desc phonebook;

Name Null? Type

PHONE_NO NOT NULL NUMBER (6)
USERNAME VARCHAR2 (13)
DOORNO VARCHAR2 (5)
STREET VARCHAR2 (13)
PLACE VARCHAR2 (13)
PINCODE CHAR (6)

SQL> select * from phonebook;

PHONE_NO USERNAME DOORN STREET PLACE PINCOD

25301 priya 390 main street SIT colony 659002
25401 murthy 39D9 MS bhavan sai nagar 689002
25701 karthi 7 jay nagar chennai 600002

36
CS8481-DATABASE MANAGEMENT SYSTEM

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

address varchar2(100);
2 begin

3 select username||','||door no ||','||street ||','||place||','||Pincode into address from

phonebook

4 where phone_no=phone;

5 return address;

6 exceptions

7 when no_data_found then return 'address not found';

8 end;

9 /

Function created.

SQL> declare

2 address varchar2 (100);

3 begin

4 address:=find address(25301);

5 dbms_output.put_line (address);

6 end;

Priya,390,main street,SIT colony,659002

PL/SQL procedure successfully completed.

37
CS8481-DATABASE MANAGEMENT SYSTEM

SQL> declare

2 address varchar2(100);

3 begin

4 address:=findaddress(25601);

5 dbms_output.put_line(address);

6 end;

Address not found

PL/SQL procedure successfully completed.

RESULT:
Thus the program for creation of triggers and functions is executed successfully.

38
CS8481-DATABASE MANAGEMENT SYSTEM

PRACTICE EXERCISES:

1. Write a pl/sql function to swap two numbers without taking third variable?

2. Write a pl/sql function to swap two numbers by taking third variable?

3. Write a pl/sql function to find the larger of two numbers?

4. Write a pl/sql function to find the total and average of 6 subjects and display the

grade?

5. Write a pl/sql function to find the sum of digits in a given number?

6. Write a pl/sql function to display the number in reverse order?

7. Write a pl/sql function to check whether the given number is prime or not?

8. Write a pl/sql function to find the factorial of a given number?

9. Write a function to find the salary of the employee who is working in the deptno

20(to be passed as an argument).?

10. Write a TRIGGER to ensure that DEPT TABLE does not contain duplicate of null

values in DEPTNO column?

11. Write a pl/sql code block to calculate the area of a circle for a value of radius

varying from 3 to 7.Store the radius and the corresponding values of calculated

area in an empty table named areas, consisting of two columns radius & area?

12. Write a pl/sql code block that will accept an account number from the user, check

if the users balance is less than minimum balance, only then deduct rs.100/- from

the balance this process is fired on the act table?

39
CS8481-DATABASE MANAGEMENT SYSTEM

VIVA QUESTIONS:

1. Define Triggers.

2. What are triggers? How many triggers you can have on a table? How to invoke a

trigger on demand?

3. Describe triggers features and limitations.

4. Syntax for viewing, dropping and disabling triggers

5. Determine how to use the inserted and deleted pseudo tables.

6. Explain how to apply cascading referential integrity in place of triggers. ?

7. Explain trigger classes i.e. instead of and after trigger. ?

8. What are the instances when triggers are appropriate?

9. What is meant by function?

10. Does the function return any value?

40
CS8481-DATABASE MANAGEMENT SYSTEM

EX.NO:7

DATE:

EXCEPTION HANDLING

AIM:
To Write a PL/SQL Block for handling all types of Exceptions

ALGORITHM:

Step 1: Create a table using sql query

Step 2: Enter the input in the created table

Step 3: : If there is exception throw it otherwise normal execution

Step 4: Stop the program

PROGRAM:
DECLARE
c_id customers.id%type:=8;
c_name customers.name%type;
c_addr customers.address%type;
BEGIN

SELECT name,address INTO c_name,c_addr

FROM customers
WHERE id=c_id;
DBMS_OUTPUT.PUT_LINE(‘Name:’|| c_name);

DBMS_OUTPUT.PUT_LINE(‘Address:’|| c_addr);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line(“no such customer!”);
WHEN others THEN

dbms_output.put_line(“Errror!”);
END;

41
CS8481-DATABASE MANAGEMENT SYSTEM

OUTPUT:

No such customer

PL/SQL procedure successfully completed

NOTE:

The above program displays the name and address of a customer whose ID is given.

Since there is no customer with ID value 8 in our database, the program raises the run-
time exception. NO_DATA_FOUND, which is captured in EXCEPTION block

RESULT:
Thus the program for PL/SQL block that handles all types of exception is
successfully executed.

42
CS8481-DATABASE MANAGEMENT SYSTEM

PRACTICE EXERCISES:

1. How to handle user defined exceptions for customer database?

2. General PL/SQL statements can be used in the Exception Block for employee

database

3. How to handle Named System Exception for customer database?

4. How to handle Unnamed System Exception for employee database?

5. How to handle RAISE-APPLICATION-ERROR for student database?

6. How to handle Exception Raised in Declarations for employee database?

7. Write a PL/SQL program that use conditional statements and arise exceptions?

8. Write PL/SQL program to handle TOO_MANY_EXCEPTION error?

9. Write PL/SQL program to handle ZERO_DIVIDE error?

10. Write PL/SQL program to use explicit cursor?

VIVA QUESTIONS:

1. How to continue execution of statements after an exception?

2. What are all the types of named system exceptions?

3. What are all the types of unnamed system exceptions?

4. What are the steps to be followed to use user-defined exceptions?

5. Write the general syntax for Raise application?

6. What is Exception Handling?

7. Draw the structure of exception handling.

8. Write the types of exception handling ?

9. How to access explicit cursor?

10. What is meant by PL/SQL records?

43
CS8481-DATABASE MANAGEMENT SYSTEM

EX.NO:8

DATE:

ER MODELING

ER DIAGRAM:

Chen Notation

Order OrderItems
-OrderNum(id) 1 has
N -ItemNum(id)
-OrderDate items -PartNum
-SalesPerson -Quality
-Cost

o ORDER (OrderNum (key), OrderDate, SalesPerson)

o ORDERITEMS (OrderNum (key)(fk) , ItemNum (key), PartNum, Quantity, Cost)

o In the above example, in the ORDERITEMS Relation: OrderNum is the Foreign

Key and OrderNum plus ItemNum is the Composite Key.

Customer Order
-CustomerID(id) 1 N -OrderNum(id)
places
-Name -Orderdate
-Address -Salesperson
-..

In the ORDER Relation: OrderNum is the Key.

Representing Relationships

• 1:1 Relationships. The key of one relation is stored in the second relation. Look at

example queries to determine which key is queried most often.

• 1:N Relationships.

Parent - Relation on the "1" side. Child - Relation on the "Many" side.

44
CS8481-DATABASE MANAGEMENT SYSTEM

• Represent each Entity as a relation.

Copy the key of the parent into the child relation.

• CUSTOMER (CustomerID (key), Name, Address, ...)

ORDER (OrderNum (key), OrderDate, SalesPerson, CustomerID (fk))

• M:N Relationships. Many to Many relationships can not be directly implemented

in relations.

• Solution: Introduce a third Intersection relation and copy keys from original two

relations.

Chen Notation

Supplier Component
N M
-SupplierID(id) supplies
-CompID(id)
-FirmName -Description
-Address -…
-…

•SUPPLIER (SupplierID (key), FirmName, Address, ...) COMPONENT (CompID (key),

Description, ...) SUPPLIER_COMPONENT (SupplierID (key), CompID (key))

•Note that this can also be shown in the ER diagram. Also, look for potential added
attributes in the intersection relation.

RESULT:

Thus the ER Database design using E-R model and Normalization was
implemented successfully.

45
CS8481-DATABASE MANAGEMENT SYSTEM

EX.NO:9

DATE:

DATABASE CONNECTIVITY WITH FRONT END TOOLS

AIM:
To Design and Implement Database Connectivity.

ALGORITHM:

Step 1 : Design the interface

Step 2 : Set properties of the controls (Objects)

Step 3 : Write the event procedures

46
CS8481-DATABASE MANAGEMENT SYSTEM

DESIGNING THE INTERFACE

47
CS8481-DATABASE MANAGEMENT SYSTEM

DATABASE IN MS ACCESS

48
CS8481-DATABASE MANAGEMENT SYSTEM

FORMS WHILE EXECUTION

49
CS8481-DATABASE MANAGEMENT SYSTEM

50
CS8481-DATABASE MANAGEMENT SYSTEM

CODING:

Dim db As Database
Dim rs As Recordset
Private Sub cmdADD_Click()
rs.AddNew
DtPickerDOJ.Enabled = True
txtEmpID.Enabled = True
txtFirstName.Enabled = True
txtLastName.Enabled = True
txtAddress.Enabled = True
txtDOJ.Enabled = True
txtSalary.Enabled = True
txtEmpID.SetFocus
txtEmpID.Text = ""
txtFirstName.Text = ""
txtLastName.Text = ""
txtAddress.Text = ""
txtDOJ.Text = Date
txtSalary.Text = ""
txtHRA.Text = ""
txtDA.Text = ""
txtTA.Text = ""
txtPF.Text = ""
txtGrossPay.Text = ""
txtNetPay.Text = ""
cmdEdit.Enabled = False

51
CS8481-DATABASE MANAGEMENT SYSTEM

cmdSubmit.Enabled = False
cmdCalculate.Enabled = False
End Sub

Private Sub cmdCalculate_Click()

If txtSalary.Text = "" Then
MsgBox "SALARY FIELD IS EMPTY", vbCritical, "Employees project"
txtSalary.SetFocus

Else
txtHRA.Text = 0.15 * Val(txtSalary.Text)
txtDA.Text = 0.17 * Val(txtSalary.Text)
txtTA.Text = 0.13 * Val(txtSalary.Text)
txtPF.Text = 0.05 * Val(txtSalary.Text)
txtGrossPay.Text = Val(txtHRA.Text) + Val(txtDA.Text) + Val(txtTA.Text) +
Val(txtSalary.Text)
txtNetPay.Text = Val(txtGrossPay.Text) - Val(txtPF.Text)
cmdSubmit.Enabled = True
cmdADD.Enabled = False
End If
End Sub

Private Sub cmdDelete_Click()

Dim msgboxres As VbMsgBoxResult
If rs.RecordCount = 0 Or txtEmpID.Text = "" Then
MsgBox "No records to delete", vbCritical, "Employees Project"
Else
msgboxres = MsgBox("Do you really want to Delete?", vbCritical + vbOKCancel,
"Confirmation")

52
CS8481-DATABASE MANAGEMENT SYSTEM

If msgboxres = vbOK Then

Cancel = False
rs.Delete
MsgBox "RECORD DELETED", vbCritical, "Employees Project"
ClearALL
Else
Cancel = True
End If
'rs.MoveNext
End If
End Sub

Private Sub cmdEdit_Click()

rs.Edit
DtPickerDOJ.Enabled = True
txtEmpID.Enabled = True
txtFirstName.Enabled = True
txtLastName.Enabled = True
txtAddress.Enabled = True
txtDOJ.Enabled = True
txtSalary.Enabled = True
rs("FIRSTNAME") = txtFirstName.Text
rs("LASTNAME") = txtLastName.Text
rs("ADDRESS") = txtAddress.Text
rs("DATEOFJOINING") = txtDOJ.Text
rs("SALARY") = txtSalary.Text
rs("HRA") = txtHRA.Text

53
CS8481-DATABASE MANAGEMENT SYSTEM

rs("DA") = txtDA.Text
rs("TA") = txtTA.Text
rs("PF") = txtPF.Text
rs("GROSSPAY") = txtGrossPay.Text
rs("NETPAY") = txtNetPay.Text
End Sub

Private Sub cmdExit_Click()

Dim msgboxres As VbMsgBoxResult
msgboxres = MsgBox("Do you want to really exit?", vbCritical + vbOKCancel,
"Confirmation")
If msgboxres = vbOK Then
Cancel = False
End
Else
Cancel = True
End If
End Sub
Private Sub cmdFirst_Click()
rs.MoveFirst
cmdPrev.Enabled = False
cmdFirst.Enabled = False
cmdNext.Enabled = True
cmdLast.Enabled = True
cmdEdit.Enabled = True
GetData
End Sub

54
CS8481-DATABASE MANAGEMENT SYSTEM

Private Sub cmdLast_Click()

rs.MoveLast
cmdFirst.Enabled = True

cmdPrev.Enabled = True
cmdNext.Enabled = False

cmdLast.Enabled = False
cmdEdit.Enabled = True
GetData
End Sub

Private Sub cmdNext_Click()

cmdEdit.Enabled = True
cmdFirst.Enabled = True
cmdPrev.Enabled = True
rs.MoveNext
If rs.EOF = True Then
cmdNext.Enabled = False
cmdLast.Enabled = False
MsgBox "LAST RECORD", vbInformation, "Employees Project"
rs.MoveLast
Else
GetData
End If
End Sub

55
CS8481-DATABASE MANAGEMENT SYSTEM

Private Sub cmdPrev_Click()

cmdEdit.Enabled = True
cmdFirst.Enabled = True
cmdNext.Enabled = True
cmdLast.Enabled = True
rs.MovePrevious
If rs.BOF = True Then
cmdFirst.Enabled = False
cmdPrev.Enabled = False
MsgBox "FIRST RECORD", vbInformation, "Employees Project"
rs.MoveFirst
Else
GetData
End If
GetData
End Sub

Private Sub cmdReport_Click()

DataReport1.Show
End Sub

Private Sub cmdSubmit_Click()

On Error GoTo errhandler2
rs("EMPID") = txtEmpID.Text
rs("FIRSTNAME") = txtFirstName.Text
rs("LASTNAME") = txtLastName.Text
rs("ADDRESS") = txtAddress.Text

56
CS8481-DATABASE MANAGEMENT SYSTEM

rs("DATEOFJOINING") = txtDOJ.Text
rs("SALARY") = txtSalary.Text
rs("HRA") = txtHRA.Text
rs("DA") = txtDA.Text
rs("TA") = txtTA.Text
rs("PF") = txtPF.Text
rs("GROSSPAY") = txtGrossPay.Text
rs("NETPAY") = txtNetPay.Text
If txtLastName.Text <> "" And txtFirstName.Text <> "" And txtAddress.Text <> "" And
txtDOJ.Text <> "" And txtSalary.Text <> "" Then
On Error GoTo ErrHandler
rs.Update
MsgBox "RECORD SUBMITTED SUCCESSFULLY", vbInformation, "Employees
Project"
ClearALL
DisableFields
cmdEdit.Enabled = False
Else
errhandler2:
MsgBox "FIELDS CANNOT BE BLANK OR CLICK CALCULATE TO CALCULATE
THE GROSS AND NET VALUES", vbCritical, "Employees Project"
End If
Exit Sub
ErrHandler:
MsgBox "EMPLOYEE ID ALREADY EXISTS", vbCritical, "Employees Project"
End Sub
Private Sub DtPickerDOJ_Change()
txtDOJ.Text = DtPickerDOJ.Value

57
CS8481-DATABASE MANAGEMENT SYSTEM

End Sub
Private Sub Form_Load()
Set db = OpenDatabase("C:\DBFiles\EMP_DB.mdb", opendynaset)
Set rs = db.OpenRecordset("Employees")
GetData
End Sub

Private Sub txtAddress_Change()

cmdSubmit.Enabled = True
End Sub
Private Sub txtDOJ_Change()
cmdSubmit.Enabled = True
End Sub

[Private Sub txtEmpID_Change()

cmdSubmit.Enabled = True
End Sub

Private Sub txtEmpID_KeyPress(KeyAscii As Integer)

If (KeyAscii < 48 Or KeyAscii > 57) Then
KeyAscii = 0
MsgBox "PLEASE ENTER ONLY NUMBERS", vbCritical, "Employees Project"
End If
End Sub

Private Sub txtFirstName_Change()

cmdSubmit.Enabled = True
End Sub

58
CS8481-DATABASE MANAGEMENT SYSTEM

Private Sub txtLastName_Change()

cmdSubmit.Enabled = True
End Sub

Private Sub txtSalary_Change()

If txtSalary.Text <> "" Then

Cmd Calculate.Enabled = True

End If
End Sub

Public Sub ClearALL()

Dim ctrl As Control
For Each ctrl In Me.Controls
If TypeOf ctrl Is TextBox Then ctrl.Text = ""
Next
End Sub
Public Sub GetData()
If rs.RecordCount <> 0 Then
txtEmpID.Text = rs("EMPID")

txtFirstName.Text = rs("FIRSTNAME")
txtLastName.Text = rs("LASTNAME")
txtAddress.Text = rs("ADDRESS")
txtDOJ.Text = rs("DATEOFJOINING")
txtSalary.Text = rs("SALARY")
txtHRA.Text = rs("HRA")

59
CS8481-DATABASE MANAGEMENT SYSTEM

txtDA.Text = rs("DA")
txtTA.Text = rs("TA")
txtPF.Text = rs("PF")
txtGrossPay.Text = rs("GROSSPAY")
txtNetPay.Text = rs("NETPAY")
txtEmpID.Enabled = False
txtFirstName.Enabled = False
txtLastName.Enabled = False
txtAddress.Enabled = False
txtDOJ.Enabled = False
txtSalary.Enabled = False
txtHRA.Enabled = False
txtDA.Enabled = False
txtTA.Enabled = False
txtPF.Enabled = False
txtGrossPay.Enabled = False
txtNetPay.Enabled = False
cmdSubmit.Enabled = False
cmdCalculate.Enabled = False
DtPickerDOJ.Enabled = False
Else
txtEmpID.Enabled = False
txtFirstName.Enabled = False
txtLastName.Enabled = False
txtAddress.Enabled = False

60
CS8481-DATABASE MANAGEMENT SYSTEM

txtDOJ.Enabled = False
txtSalary.Enabled = False
txtHRA.Enabled = False
txtDA.Enabled = False
txtTA.Enabled = False
txtPF.Enabled = False
txtGrossPay.Enabled = False
txtNetPay.Enabled = False
cmdSubmit.Enabled = False
cmdCalculate.Enabled = False
End If
End Sub

Private Sub txtSalary_KeyPress(KeyAscii As Integer)

If (KeyAscii < 48 Or KeyAscii > 57) Then
KeyAscii = 0
MsgBox "PLEASE ENTER ONLY NUMBERS", vbCritical, "Employees Project"
End If
End Sub

61
CS8481-DATABASE MANAGEMENT SYSTEM

Public Sub DisableFields()

txtEmpID.Enabled = False
txtFirstName.Enabled = False
txtLastName.Enabled = False
txtAddress.Enabled = False
txtDOJ.Enabled = False
txtSalary.Enabled = False
cmdADD.Enabled = True
cmdCalculate.Enabled = False
cmdSubmit.Enabled = False
End Sub

RESULT:

Thus the design and implementation of payroll processing system using SQL, VB
was successfully done.

62