PRACTICAL RECORD NOTE BOOK
CS1307 - DATA BASE MANAGEMENT SYSTEM
RECOD NOTE BOOK
ANNA UNIVERSITY
[pic]
DEPARTMENT OF COMPUTER SCIENCE AND INFORMATION TECHNOLOGY
INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY
PANDUR, THIRUVALLUR DIST – 631 203
| |
| |
| |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
| |
| |
|NAME……………………………….. COURSE : |
| |
| |
|REG.NO……………………………… SEMESTER: |
| |
| |
| |
| |
| |
| |
|Head Of the Department Staff-In-Charge |
| |
|Submitted for the practical examination held on……………………….. |
| |
| |
|Internal Examiner External Examiner |
INDEX
|EXP.NO |DATE |PROGRAM |PAGE NO |SIGNATURE |
|1 | |Data definition language | | |
| | |(DDL) command in RDMS | | |
|2 | |Data manipulation language and | | |
| | |DCL command in RDMS | | |
|3 | |High level language extension | | |
|4 | |High level language extension with triggers | | |
|5 | |Procedure and function | | |
|6 | |Embedded SQL | | |
| 7 | |Data base design using Normalization | | |
| 8 | |Design and implementation of Library Information System | | |
|9 | |Design and implementation of Banking System | | |
|10 | |Design and implementation of Pay roll processing | | |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 1 Date: |
|Data Definition Language (DDL) commands in RDBMS |
| |
|AIM: |
|To implement the DDL commands in RDBMS. |
| |
|DDL commands: |
| |
|mand: CREATE TABLE |
|Syntax: |
|CREATE TABLE table_name |
|( |
|Column_name [ owner_name ] table_name |
|); |
| |
|Example Query: |
| |
|CREATE TABLE STUDENT(ROLLNO NUMBER(5)PRIMARY KEY, |
|NAME VARCHAR2(10),DOB DATE, |
|ADDRESS VARCHAR2(15)); |
| |
|mand: ALTER TABLE |
|Syntax: |
|ALTER TABLE[ owner_name ] table_name |
|[ADD column_name datatype attributes] |
|[MODIFY{column_name datatype | column_constraint}] |
| |
|Example Query: |
| |
|ALTER TABLE STUDENT ADD(PERCENTAGE NUMBER(5,2)); |
| |
|mand: DROP TABLE |
|Syntax: |
|DROP TABLE table name; |
| |
|Example Query: |
| |
|DROP TABLE STUDENT; |
| |
| |
|RESULT: |
|Thus the DDL commands in RDBMS are implemented. |
| |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 1 Date: |
|CREATING TABLES |
| |
|STUDENT TABLE |
| |
|SQL> create table student(sno number(8)primary key, sname varchar2(20),fathername varchar2(20),dept varchar2(16),sex char(7),feesdue number(7,2),DOB date);|
| |
| |
| |
|Name |
|Null? |
|Type |
| |
|SNO |
|NOT NULL |
|NUMBER(8) |
| |
|SNAME |
| |
|VARCHAR2(20) |
| |
|FATHERNAME |
| |
|VARCHAR2(20) |
| |
|DEPT |
| |
|VARCHAR2(16) |
| |
|SEX |
| |
|CHAR(7) |
| |
|FEESDUE |
| |
|NUMBER(7,2) |
| |
|DOB |
| |
|DATE |
| |
| |
| |
|EXAM TABLE |
| |
|SQL> create table exam(sno number(8)primary key, sem number(3),noofpapers number(2),exmfees number(7,2)); |
| |
| |
|Name |
|Null? |
|Type |
| |
|SNO |
|NOT NULL |
|NUMBER(8) |
| |
|SEM |
| |
|NUMBER(3) |
| |
|NOOFPAPERS |
| |
|NUMBER(2) |
| |
|EXMFEES |
| |
|NUMBER(7,2) |
| |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 1 Date: |
|MARK TABLE |
| |
|SQL>create table mark(sno number(8)primary key, sem number(3),sub1 number(3),sub2 number(3),sub3 number(3), |
|total number(4),avg number(5,2),status char(4),grade char(4)); |
| |
| |
|Name |
|Null? |
|Type |
| |
|SNO |
|NOT NULL |
|NUMBER(8) |
| |
|SEM |
| |
|NUMBER(3) |
| |
|SUB1 |
| |
|NUMBER(3) |
| |
|SUB2 |
| |
|NUMBER(3) |
| |
|SUB3 |
| |
|NUMBER(3) |
| |
|TOTAL |
| |
|NUMBER(4) |
| |
|AVG |
| |
|NUMBER(5,2) |
| |
|STATUS |
| |
|CHAR(4) |
| |
|GRADE |
| |
|CHAR(4) |
| |
| |
|ALTERING TABLE |
| |
|SQL>alter table mark modify(total number(5),status char(6)); |
| |
|Name |
|Null? |
|Type |
| |
|SNO |
|NOT NULL |
|NUMBER(8) |
| |
|SEM |
| |
|NUMBER(3) |
| |
|SUB1 |
| |
|NUMBER(3) |
| |
|SUB2 |
| |
|NUMBER(3) |
| |
|SUB3 |
| |
|NUMBER(3) |
| |
|TOTAL |
| |
|NUMBER(5) |
| |
|AVG |
| |
|NUMBER(5,2) |
| |
|STATUS |
| |
|CHAR(6) |
| |
|GRADE |
| |
|CHAR(4) |
| |
|DROP TABLE |
| |
|SQL>drop table student; |
|Desc student; |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 2 Date: |
| DATA MANIPULATION LANGUAGE (DML) and DATA |
|CONTROL LANGUAGE (DCL) commands in RDBMS |
| |
|AIM: |
|To implement the DML and DCL commands in RDBMS. |
| |
|DML commands: |
| |
|mand: SELECT |
|Syntax: |
|SELECT[ALL | DISTINCT] select list |
|FROM table_name1[,…table_nameN] |
|[JOIN join_condition] |
|[WHERE search_condition] |
| |
|Example Query: |
| |
|SELECT * FROM STUDENT WHERE ROLLNO=1; |
| |
|mand: INSERT |
|Syntax: |
|INSERT INTO[[database_name]owner]{table_name|view_name} |
|[(column_list)]{[DEFAULT]VALUES|VALUES(value[…])|SELECT |
|Statement} |
| |
|Example Query: |
| |
|INSERT INTO STUDENT VALUES(03,”Ramki”,”2-JUN-1988”, |
|“5,Lakshmi nagar,Chennai-24”,87.5); |
|mand: UPDATE |
|Syntax: |
|UPDATE table_name |
|SET column_name=expression[,…n] |
|WHERE search_condition |
|Example Query: |
| |
|UPDATE STUDENT SET PERCENTAGE=90 WHERE ROLLNO=1; |
| |
|mand: DELETE |
|Syntax: |
|DELETE[FROM] table_name WHERE search_condition] |
| |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 2 Date: |
|Example Query: |
| |
|DELETE FROM STUDENT WHERE ROLLNO=5; |
| |
|DCL commands |
| |
|mand: GRANT |
|Syntax: |
|GRANT permissions ON objects TO account |
| |
|Example Query 1: GRANT INSERT ON employee TO PUBLIC; |
|Example Query 2: GRANT SELECT,UPDATE ON employee to username; |
| |
|mand: REVOKE |
|Syntax: |
|REVOKE permissions ON object FROM account |
| |
|Example Query: |
|REVOKE SELECT ON student FROM username; |
| |
|1. 1. Create a table student with field roll no student name ,dept marks ,Average ,grade |
|and status, DOB address. |
| |
|Name |
|Null? |
|Type |
| |
|ROLLNO |
|NOT NULL |
|NUMBER(5) |
| |
|STUDENTNAME |
| |
|VARCHAR2(15) |
| |
|DEPT |
| |
|VARCHAR2(10) |
| |
|AVERAGE |
| |
|NUMBER(5,2) |
| |
|GRADE |
| |
|VARCHAR2(10) |
| |
|STATUS |
| |
|VARCHAR2(10) |
| |
|DOB |
| |
|DATE |
| |
|ADRESS |
| |
|VARCHAR2(15) |
| |
| |
| |
| |
| |
| |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 2 Date: |
|2. Insert the values for the tables. |
| |
| |
|ROLLNO |
|STUDENTNAME |
|DEPT |
|AVERAGE |
|GRADE |
|STATUS |
|DOB |
|ADRESS |
| |
|1 |
|Sudhir |
|B.tech IT |
|86 |
|Distctn |
|Pass |
|16-JUN-88 |
|82Grace |
| |
|2 |
|Sudesh |
|B.tech IT |
|79.8 |
|Distctn |
|Pass |
|26-DEC-87 |
|42 Thinanur |
| |
|4 |
|Sudharsan |
|Mech |
|77.8 |
|Distctn |
|Pass |
|16-JUN-88 |
|3tiruvallur |
| |
| |
| |
|3. Select the student detail those who are in IT Department. |
| |
|ROLLNO |
|STUDENTNAME |
|DEPT |
|AVERAGE |
|GRADE |
|STATUS |
|DOB |
|ADRESS |
| |
|1 |
|Sudesh |
|B.tech IT |
|79.8 |
|Distctn |
|Pass |
|26-DEC-87 |
|42 Thinanur |
| |
| |
| |
| |
| |
|4. Select the Student those who are not belong to Mech Dept. |
|ROLLNO |
|STUDENTNAME |
|DEPT |
|AVERAGE |
|GRADE |
|STATUS |
|DOB |
|ADRESS |
| |
|1 |
|Sudesh |
|B.tech IT |
|79.8 |
|Distctn |
|Pass |
|26-DEC-87 |
|42 Thinanur |
| |
|3 |
|Thomas |
|Ece |
|80.8 |
|Distctn |
|Pass |
|25-JUN-88 |
|82new |
| |
| |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
|EX NO: Date: |
|5. Select Student detailo from obtain grade Distinc in the Examinaation. |
| |
| |
|ROLLNO |
|STUDENTNAME |
|DEPT |
|AVERAGE |
|GRADE |
|STATUS |
|DOB |
|ADRESS |
| |
|1 |
|Sudhir |
|B.tech IT |
|86 |
|Distctn |
|Pass |
|16-JUN-88 |
|82Grace |
| |
|2 |
|Sudesh |
|B.tech IT |
|79.8 |
|Distctn |
|Pass |
|26-DEC-87 |
|42 Thinanur |
| |
|4 |
|Sudharsan |
|Mech |
|77.8 |
|Distctn |
|Pass |
|16-JUN-88 |
|3tiruvallur |
| |
| |
| |
| |
|6. Select rollno name,mark detail from the table student. |
| |
|ROLLNO |
|AVERAGE |
|GRADE |
|STATUS |
| |
|1 |
|79.8 |
|Distctn |
|Pass |
| |
|2 |
|77.8 |
|Distctn |
|Pass |
| |
|3 |
|80.8 |
|Distctn |
|Pass |
| |
| |
| |
| |
| |
| |
| |
|7. Retrive the name and address of all the studens who are in IT department. |
| |
|ROLLNO |
|AVERAGE |
|GRADE |
|STATUS |
| |
|1 |
|79.8 |
|Distctn |
|Pass |
| |
| |
| |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: Date: |
|8. Update the percentage of the student whose roll no is 1. |
|ROLLNO |
|STUDENTNAME |
|DEPT |
|AVERAGE |
|GRADE |
|STATUS |
|DOB |
|ADRESS |
| |
|1 |
|Sudesh |
|B.tech IT |
|86 |
|Distctn |
|Pass |
|26-DEC-87 |
|42 Thinanur |
| |
|2 |
|Sudharsan |
|Mech |
|77.8 |
|Distctn |
|Pass |
|16-JUN-88 |
|82Grace |
| |
|3 |
|Thomas |
|Ece |
|80.8 |
|Distctn |
|Pass |
|25-JUN-88 |
|82new |
| |
| |
| |
| |
| |
|9. Updte the address of the student in Mech department. |
| |
|ROLLNO |
|STUDENTNAME |
|DEPT |
|AVERAGE |
|GRADE |
|STATUS |
|DOB |
|ADRESS |
| |
|1 |
|Sudesh |
|B.tech IT |
|86 |
|Distctn |
|Pass |
|26-DEC-87 |
|42 Thinanur |
| |
|2 |
|Sudharsan |
|Mech |
|77.8 |
|Distctn |
|Pass |
|16-JUN-88 |
|3 pattabiram |
| |
|3 |
|Thomas |
|Ece |
|80.8 |
|Distctn |
|Pass |
|25-JUN-88 |
|82new |
| |
| |
| |
|10. Delete the student details where rollno=3 |
| |
|ROLLNO |
|STUDENTNAME |
|DEPT |
|AVERAGE |
|GRADE |
|STATUS |
|DOB |
|ADRESS |
| |
|1 |
|Sudesh |
|B.tech IT |
|86 |
|Distctn |
|Pass |
|26-DEC-87 |
|42 Thinanur |
| |
|and Dept. |
|2 |
|Sudharsan |
|Mech |
|77.8 |
|Distctn |
|Pass |
|16-JUN-88 |
|3 pattabiram |
| |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: Date: |
|11. create a table employee with empname salary |
| |
|Name |
|Null? |
|Type |
| |
|EMPNAME |
| |
|VARCHAR2(15) |
| |
|SALARY |
| |
|NUMBER(15) |
| |
|DEPT |
| |
|VARCHAR2(15) |
| |
| |
|EMPNAME |
|SALARY |
|DEPT |
| |
|Sudhir |
|7500 |
|Manager |
| |
|Sudeesh |
|6700 |
|Manager |
| |
|Arjit |
|5500 |
|Supervisor |
| |
|Bai |
|6900 |
|Supervisor |
| |
| |
| |
|12. List the name of the Manager. |
| |
| |
|EMPNAME |
| |
|Sudhir |
| |
|Sudeesh |
| |
| |
| |
|13. List the name of all the employee other than Manager. |
| |
|EMPNAME |
| |
|Arjit |
| |
|Bai |
| |
| |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
|EX NO: Date: |
|14. find the sum of the salary of all employee of Manager department as well as Max and Minimum salary. |
| |
|MAX(SALARY) |
|MIN(SALARY) |
|SUM(SALARY) |
| |
|7500 |
|5500 |
|114900 |
| |
| |
| |
|15. Find the sum of salary of all Employee of manager Department as well as Max salary and Minimum salary. |
| |
| |
|MAX(SALARY) |
|MIN(SALARY) |
|SUM(SALARY) |
| |
|7500 |
|5500 |
|90100 |
| |
|Example Query: |
| |
|DELETE FROM STUDENT WHERE ROLLNO=5; |
| |
|DCL commands |
| |
|mand: GRANT |
|Syntax: |
|GRANT permissions ON objects TO account |
| |
|Example Query 1: GRANT INSERT ON employee TO PUBLIC; |
|Example Query 2: GRANT SELECT,UPDATE ON employee to username; |
| |
|mand: REVOKE |
|Syntax: |
|REVOKE permissions ON object FROM account |
| |
|Example Query: |
|REVOKE SELECT ON student FROM username; |
| |
| |
|RESULT: |
|Thus the DML and DCL commands in RDBMS are implemented |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
|EX NO: 3 Date: |
|HIGH LEVEL LANGUAGE EXTENSION WITH CURSOR |
|AIM: |
|To study the PL/SAQL programs and to write the PL/SAQL programs |
|using cursors. |
| |
|DEFINIYION AND SYNTAX: |
| |
|1. PL/SQL: |
|PL/SQL stands for procedural structural language/SQL. PL/SQL |
|extends SQL by adding control structures found in other structural languages. |
|PL/SQL can be used in the Oracle relational database in the Oracle server and |
|in the client side application development tools. |
| |
|SYNTAX: |
|APL/SQL block can divide into three parts namely, |
|i) Declarative part, |
|ii) Executable part, and |
|iii) Exception handling part. |
|The order is shown below: |
| |
|DECLARE |
|Declarations |
|BEGIN |
|Executable statements |
|EXCEPTION |
|Handlers |
|END; |
| |
|Objects can be declared in declarative part, which can be used in executable |
|part for further manipulation. All procedural statements are included between |
|the BEGIN and END statements. Errors that occur during the exception are handled by the exception handlers. |
| |
|2. ATTRIBUTES: |
|Attributes allows us to refer data types and objects from database. PL/SQL constants and variables have attributes. The following are the types of |
|attributes supported by the PL/SQL, |
|%type |
|%row type |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
|EX NO: 3 Date: |
|%type: |
|%type is used when declaring variables that refer to database columns. |
| |
|SYNTAX: |
|Variablename tablename.Column %type; |
| |
| |
|%rowtype: |
|%rowtype attributes provides a record type that rapresents a row in the tablw. The record can store an entire row of data selected from the table or |
|fetched by a cursor. |
| |
|SYNTAX: |
|Variablename tablename %rowtype; |
| |
|3. CURSORS: |
|Oracle allocates a memory known as the context area for the processing of the SQL statements. A cursor is a pointer or handle to the context area. Through |
|the cursor, a PL/SQL program can control the context area and what happens to it as the statement is processed. |
|The three types of the cursors are |
|Static cursors |
|Dynamic cursors |
|REF cursors |
|Static cursors are the ones whose select statements are known at the compile time. These are further classified into |
|Explicit cursors |
|Implicit cursors |
| |
|/*PL/SQL block to perform arithmetic operations*/ |
|SQL>declare |
|a number; |
|b number; |
|su number; |
|dif number; |
|pro number; |
|div number; |
|begin |
|a:=&a; |
|b:=&b; |
|su:=a+b; |
|dbms_output.put_line(‘The Sum is’||su); |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 3 Date: |
| |
|dif:=a-b; |
|dbms_output.put_line(‘The Difference is’||dif); |
|pro:=a*b; |
|dbms_output.put_line(‘The Product is’||pro); |
|div:=a/b; |
|dbms_output.put_line(‘The Quotent is’||div); |
|end; |
| |
| |
| |
|OUTPUT: |
| |
|Enter value for a: 25 |
|Old 9:a:=&a; |
|New 9:a:=25; |
|Enter value for b: 4 |
|Old 10:b:=&b; |
|New 10:b:=4; |
|The Sum is 29 |
|The Difference is 21 |
|The Product is 100 |
|The Quotent is 6.25 |
| |
|PL/SQL procedure successfully completed. |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
|RESULT: |
|Thus the PL/SQL program using the cursors were executed successfully. |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
|EX NO: 4 Date: |
|HIGH LEVEL LANGUAGE EXTENSIONS WITH TRIGGERS |
| |
|AIM: |
|To study and execute Triggers in RDBMS. |
| |
|DEFINITION & SYNTAX: |
| |
|TRIGGER: |
|A database trigger is stored procedure that is fired when an insert, update or delete statement is issued against the associated table. Database triggers |
|can be used for the following purposes. |
|To generate data automatically. |
|To enforce complex integrity constraints. |
|To customize complex security authorizations. |
|To maintain replicate tables. |
|To audit data modifications. |
| |
|Syntax for creating Triggers: |
|Create or Replace trigger |
|[before/after][insert/update/delete] on |
|[for each statement/for each row] |
|[when ] |
| |
|Types of Triggers: |
|The triggers are classified into the following types based on when they are fired: |
|Before |
|After |
|For each row |
|For each statement (default) |
|INSERT: |
|--create or replace trigger ins1 before insert on emp |
|--begin |
|--raise_application_error(-20001.’you cannot insert a row’); |
|--end; |
|OUTPUT: |
|SQL>insert into emp values(&eid,’&name’,’&dob’,’&addr’,’&sex’,’&dept’,’&salary); |
|insert into emp |
|values(&eid,’&name’,’&dob’,’&addr’,’&sex’,’&dept’,’&salary); |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
|EX NO: 4 Date: |
|ERROR at line 1: |
|ORA-20001: you cannot insert a row |
|ORA-06512: at “SCOTT.ins1”, line 2 |
|ORA-04088: error during execution of trigger ‘SCOTT.ins1’ |
| |
|DELETE: |
|--create or replace trigger del1 before delete on emp |
|--begin |
|--raise_application_error(-20001.’you cannot delete a row’); |
|--end; |
|OUTPUT: |
| |
|SQL>delete from emp where eid=2387; |
|delete from emp where eid=2387; |
| |
|ERROR at line 1: |
|ORA-20001: you cannot delete a row |
|ORA-06512: at “SCOTT.del1”, line 2 |
|ORA-04088: error during execution of trigger ‘SCOTT.del1’ |
| |
|UPDATE: |
|--create trigger upd1 before update on emp for each row |
|--begin |
|--raise_application_error(-20001.’you cannot update a row’); |
|--end; |
|OUTPUT: |
| |
|SQL>update emp set salary=5000 where eid=321; |
|update emp set salary=5000 where eid=321; |
| |
|ERROR at line 1: |
|ORA-20001: you cannot update a row |
|ORA-06512: at “SCOTT.upd1”, line 2 |
|ORA-04088: error during execution of trigger ‘SCOTT.upd1’ |
| |
| |
|RESULT: |
|Thus the usage of trigger were studied and executed in RDBMS. |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 5 Date: |
|PROCEDURES AND FUNCTIONS |
| |
|AIM: |
|To study and execute the procedures and functions in PL/SQL. |
| |
|PROCEDURES: |
| |
|A procedure is a subprogram that performs a specific action. |
|The syntax for creating a procedure is given below. |
|create or replace procedure [parameter list] is |
|; |
|begin |
|(executable statements) |
|[exception] (exception handlers) |
|end; |
| |
|A procedure has two parts namely, specification and body. The procedure |
|specification begins with keyword procedure and ends with procedure name or parameter list. The procedure body begins with keyword is and ends with the |
|keyword end. It can also include declarative, executable and exceptional parts with in the keywords are and end. Syntax to execute a procedure is given |
|below. |
| |
|SQL> exec (parameters); |
| |
|FUNCTIONS: |
| |
|A function is a subprogram that computes a value. The syntax for creating a function is given below. |
|create or replace function [argument] |
|return datatype is |
|(local declaration) |
|begin |
|(executable statements) |
|[exception] |
|(exception handlers) |
|end; |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 5 Date: |
|where arguments can be in, out or inout. |
| |
|Similar to a procedure, a function also has two parts namely, the function specification and the function body. The function specification begins with the |
|keyword function and ends with the return clause. The function body begins with the keyword is and ends with the keyword end. A PL/SQL block can also be |
|included in a function body. |
| |
|create or replace function factorial(n number) return number is |
|fac number; |
|i number; |
|begin |
|fac:=1; |
|for I in 1…n |
|loop |
|fac:=fac*i; |
|end loop; |
|return fac; |
|end; |
|//Function created. |
|declare |
|n number; |
|x number; |
|begin |
|n:=&n; |
|x:=factorial(n); |
|dbms_ouytput.put_line(‘the factorial of ‘||n||’is’||x); |
|end; |
| |
|OUTPUT |
|SQL>/ |
|Enter value for n: 5 |
|Old 5:n:=&n; |
|New 5:n:=5; |
|The factorial of 5 is 120 |
|PL/SQL procedure successfully completed. |
| |
| |
|RESULT: |
|Thus the procedures and functions were studied and executed in PL/SQL. |
| |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 6 Date: |
|EMBEDDED SQL |
| |
|AIM: |
|To write JAVA program to implement embedded SQL. |
| |
|ALGORITHM: |
| |
|Start the program. |
| |
|Create Database using Microsoft Access with the following fields, |
|FIELDS DATATYPE |
| |
|Roll no Number |
|Name Text |
|Dept Text |
|Marks Number |
| |
|Open MS.Access and then select Blank Database. In the File new database dialog give the DBname and save it in C. Then click create button. |
| |
|Select create table in design view option from the dialog box that appears. |
|Add all the fields in the resulting dialog box. |
| |
|Save the table and insert values into it. |
| |
| |
|Next create the DSN by the following steps: |
|Select Administrative Tools option from Control Panel. Then click on Data Source(ODBC), which displays a dialog box named ODBC DataSourceAdministrator in |
|which click Add button. |
|In Create New Data Source dialog box, select Driver do Microsoft Access (*.mdb) and click finish button. |
|Give a suitable Data Source Name and click select button. |
|Select the database name that you have created and click OK. |
|Make sure that the path name of your database appears and finally click OK. |
| |
|Go to command prompt and type the JAVA program. |
| |
|Compile and run the program. |
| |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
|EX NO: 6 Date: |
|PROGRAM: |
| |
|import java.io.*; |
|import java.sql.*; |
|public class jdeg |
|{ |
|Public static void main(String args[])throws IOExcepyion |
|{ |
|BufferedReader br=new BufferedReader(new InputStreamReader(System.in)); |
|String rollno,nam,dep,mark; |
|System.out.println(“Enter the values(rno,name,dept,marks)to insert into table”); |
|rollno=br.readLine(); |
|nam=br.readLine(); |
|dep=br.readLine(); |
|mark=br.readLine(); |
|try |
|{ |
|Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”); |
|Connection con=DriverManager.getConnection(“jdbc:odbc:stu”); |
|st.executeUpdate(“insert into student values(‘“+rollno+”’,’”+nam+”’, |
|’”+dep+”’,’”+mark+”’)”); |
|ResultSet rs-st.executeQuery(“select * from student”); |
|System.out.println(); |
|System.out.println(); |
|System.out.println(“RNO\tNAME\tDEPT\tMARKS”); |
|while(rs.next()) |
|{ |
|System.out.println(rs.getString(“rno”)+”\t”); |
|System.out.println(rs.getString(“name”)+”\t”); |
|System.out.println(rs.getString(“dept”)+”\t”); |
|System.out.println(rs.getString(“marks”)+”\t”); |
|} |
|} |
|catch(Exception e) |
|{ |
|System.out.println(e); |
|} |
|} |
|} |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
|EX NO: 6 Date: |
|OUTPUT: |
| |
|D:\jdk1.3\bin>edit jdeg.java |
|D:jdk1.3\bin>javac jdeg.java |
|D:jdk1.3\bin>java jdeg |
| |
|Enter the values(rno,name,dept,mark)to insert into the table |
|23 |
|Ramki |
|CSE |
|98 |
| |
|Rn Name Dept Mark |
|1 Arun CSE 74 |
|2 Barathi EEE 69 |
|56 Daniel IT 89 |
|67 Hareni ECE 81 |
|23 Ramki CSE 98 |
| |
|D:jdk1.3\bin> |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
|RESULT: |
|Thus a JAVA program to implement embedded SQL has been executed successfully. |
| |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 7 Date: |
|DATABASE DESIGN USING NORMALIZATION AND |
|E-R DIAGRAM |
| |
|AIM: |
|To design database using normalization and E-R Diagram. |
| |
|PROCEDURE: |
|Create a type ‘Address’ to represent composite attribute. |
|Create the table. |
|Insert the value in the table. |
|Draw the E-R diagram for the table. |
|Convert the given table to the normalized form. |
| |
|Converting a table to 1 NF: |
| |
|To convert a table to 1 NF remove all the multi valued & composite attributes from the table. |
| |
|Converting a table to 2 NF: |
| |
|i. Find and remove attributes that are functionally dependent |
|on only a part of the key and not on the whole key. Place them in a different table. |
|ii. Group the remaining attributes. |
|NORMALIZING THE TABLE TO 1 NF: |
| |
|SQL>create table en1 as select eno, sname, sal from employees; |
|Table created. |
| |
|SQL>alter table en1 add primary key(eno); |
|Table altered. |
| |
|SQL>create table en2 as select eno,eadd from employees; |
|Table created. |
| |
|SQL>alter table en2 add foreign key(eno) references en1(eno); |
|Table altered. |
| |
| |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 7 Date: |
|SQL>desc en2; |
| |
|Name Null? Type |
|ENO NUMBER (3) |
|EADD ADDR |
|employee |
| |
| |
|Eno |
|Ename |
|Eadd |
|Sal |
| |
| |
|1 NF |
| |
|emp 1 emp2 |
| |
|Eno |
|eadd |
| |
|Eno |
| |
|Ename |
| |
|sal |
| |
| |
| |
| |
| |
|NORMALIZING THE TABLE TO 2 NF: |
| |
|SQL>create table ep1 as select eno,ename, from empproject; |
|Table created. |
| |
|SQL>alter table ep1 add primary key(eno); |
|Table altered. |
| |
|SQL>create table ep2 as select pno,pname from empproject; |
|Table created. |
| |
|SQL>alter table ep2 add primary key(pno); |
|Table altered. |
| |
|SQL>create table ep3 as select eno,pno,hours from empproject; |
|Table created. |
| |
|SQL>alter table ep3 add constraint e3 primary key(eno); |
|Table altered. |
| |
|SQL>alter table ep3 add constraint e4 unique(pno); |
|Table altered. |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 7 Date: |
| |
|Eno |
|ename |
|Pno |
|pname |
|hours |
| |
| |
| |
|2 NF |
| |
| |
|Ep1 |
| |
| |
|eno |
|ename |
| |
|Ep2 |
| |
|pno |
|pname |
| |
| |
|Ep3 |
| |
|eno |
|Pno |
|hours |
| |
| |
| |
| |
| |
|NORMALIZING THE TABLE TO 3 NF: |
| |
|SQL>create table ed1 as select eno,ename,sal,dno from empdept; |
|Table created. |
| |
|SQL>alter table ed1 add primary key(eno); |
|Table altered. |
| |
|SQL>create table ed2 as select dno,dname from empdept; |
|Table created. |
| |
|SQL>alter table ed2 add primary key(dno); |
|Table altered |
| |
|SQL>alter table ed1 add foreign key(dno) reference ed2(dno); |
|Table altered. |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 7 Date: |
| |
|Eno |
|ename |
|Sal |
|dno |
|dname |
| |
|Empdept |
| |
| |
| |
| |
| |
|3 NF |
| |
|Ed1 |
|Eno |
|Ename |
|sal |
|dno |
| |
| |
| |
| |
|Ed2 |
|Dno |
|Dname |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
|RESULT: |
| |
|Thus the database was designed using E-R diagram and Normailization |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
|EX NO: 8 Date: |
|DESIGN AND IMPLEMENTATION OF LIBRARY INFORMATION SYSTEM |
| |
| |
|AIM: |
| |
|To develop a library information system using Oracle as a back end (data base)and Microsoft Visual Basic as a front end. |
| |
|PROCEDURE: |
|1.Create a book and issue database with the following fields in Oracle namely Bid,Bname,Author,Price,noofcopiesavailable and Bid,Bname,Author,card |
|number,stuname,dateofreturn respectively and insert some record into the database. |
| |
|2.Design the corresponding form with labels,text boxes and command buttons. |
| |
|Form1 (bookdetails |
|Form2 (issuedetails |
| |
|3.Create the back with the front end using DAO method by creating a dsn as follows |
|a.Select administrative tools option from control panel. |
|Then click on data source(ODBC),which displays a dialog box named |
|ODBC DATASOURCES ADMINISTRATOR in which click Add button. |
|b.In Create New Data Source dialog box,select “Microsoft ODBC for |
|ORACLE” and click finish button. |
|c.Give a suitable data source name,user name and server name. |
| |
|4.Perform the required operations like Insert,Delete,Searching and Exit. |
| |
|5.Issue the books according to the no.of copies available. |
| |
|6.Execute the project. |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 8 Date: |
|TABLE DESIGN: |
| |
|Table Name 1:Book |
| |
| |
|NAME |
|TYPE |
| |
|BID |
|NIMBER(6) |
| |
|BNAME |
|VARCHAR2(30) |
| |
|AUTHOR |
|VARCHAR2(20) |
| |
|PRICE |
|NUMBER(8,2) |
| |
|NO OF COPIES AVAILABLE |
|NUMBER(3) |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
|Table Name 2:Issue |
| |
|BID |
|NUMBER(6) |
| |
|BNAME |
|VARCHAR2(30) |
| |
|AUTHOR |
|VARCHAR2(20) |
| |
|CARDNUMBER |
|VARCHAR2(8,2) |
| |
|STUNAME |
|VARCHAR2(8,2) |
| |
|DATEOFRETURN |
|NUMBER(6) |
| |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 8 Date: |
|[pic] |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO:8 Date: |
|[pic] |
| |
| |
|FORM 1: |
| |
|Dim DB As Database |
|Dim RS As recordset |
| |
|Private Sub Form_Load() |
|Set DB =OpenDatabase(“libdsn”,false,false,”ODBC;uid= ;pwd= ;”) |
|Set RS =DB.OpenRecordset(“select * from Book”) |
|Text1.text=RS(0) |
|Text2.text=RS(1) |
|Text3.text=RS(2) |
|Text4.text=RS(3) |
|Text5.text=RS(4) |
|End Sub |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 8 Date: |
|Private Sub CLEAR_click() |
|Text1.text=”” |
|Text2.text=”” |
|Text3.text=”” |
|Text4.text=”” |
|Text5.text=”” |
|End Sub |
| |
|Private sub DELETE_Click() |
|RS.DELETE |
|MsgBox”the record is deleted” |
|RS.MoveNext |
|IfRS.EOF Then |
|MsgBox”no more records” |
|Else |
|Text1.text=RS(0) |
|Text2.text=RS(1) |
|Text3.text=RS(2) |
|Text4.text=RS(3) |
|Text5.text=RS(4) |
|End If |
|End Sub |
| |
|Private Sub EXIT Click() |
|End |
|End Sub |
| |
|Private Sub INSERT_Click() |
|RS .MOVELAST |
|RS.AddNew |
|RS(0)=Text1.Text |
|RS(1) =Text2.Text |
|RS(2)=Text3.Text |
|RS(3)=Text4.Text |
|RS(4)=Text5.Text |
|RS.UPDATE |
|End Sub |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 8 Date: |
|Private Sub MOVEFIRST_Click() |
|RS.MOVEFIRST |
|Text1.text=RS(0) |
|Text2.text=RS(1) |
|Text3.text=RS(2) |
|Text4.text=RS(3) |
|Text5.text=RS(4) |
|End Sub |
|Private Sub SEARCH_Click() |
|1=InputBox(“ENTER THE BID”,”FIND”) |
|RS.FindFirst”[BID]-”&I |
|If RS.NoMatch Then |
|MsgBox”no such records” |
|Else |
|RS.MOVEFIRST |
|Text1.text=RS(0) |
|Text2.text=RS(1) |
|Text3.text=RS(2) |
|Text4.text=RS(3) |
|Text5.text=RS(4) |
|End Sub |
|Private Sub ISSUE_Click() |
|If Val(Text5.Text)>=1 Then |
|Form2.Show |
|Else |
|MsgBox”NO BOOKS AVAILABLE” |
|End If |
|End Sub |
|FORM2: |
|Dim DB As Database |
|Dim RS As recordset |
| |
|Private Sub Form_Load() |
|Set DB =OpenDatabase(“libdsn”,false,false,”ODBC;uid= ;pwd= ;”) |
|Set RS =DB.OpenRecordset(“select * from Book”) |
|Text1.Ttext=Form1.Text1 |
|Text2.Text=Form1.Text2 |
|Text3.Text=Form1.Text3 |
|End Sub |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 8 Date: |
|Private Sub ISSUE_Click() |
|RS.AddNew |
|RS(0)=Text1.Text |
|RS(1) =Text2.Text |
|RS(2)=Text3.Text |
|RS(3)=Text4.Text |
|RS(4)=Text5.Text |
|RS(5)=Text6.Text |
|RS.UPDATE |
|RS.MOVELAST |
|Form1.Text5=val(Form1.Text5)-1 |
|End Sub |
|Private Sub MOVELAST_Click() |
|RS.MOVELAST |
|Text1.text=RS(0) |
|Text2.text=RS(1) |
|Text3.text=RS(2) |
|Text4.text=RS(3) |
|Text5.text=RS(4) |
|End Sub |
| |
|Private sub NEXT_Click() |
|RS.MoveNext |
|IfRS.EOF Then |
|MsgBox”no more records” |
|Else |
|Text1.text=RS(0) |
|Text2.text=RS(1) |
|Text3.text=RS(2) |
|Text4.text=RS(3) |
|Text5.text=RS(4) |
|End If |
|End Sub |
| |
|Private sub PREVIOUS_Click() |
|RS.MovePrevious |
|IfRS.EOF Then |
|MsgBox”no more records” |
|Else |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 8 Date: |
|Text1.text=RS(0) |
|Text2.text=RS(1) |
|Text3.text=RS(2) |
|Text4.text=RS(3) |
|Text5.text=RS(4) |
|End If |
|End Sub |
| |
|Private Sub UPDATE_Click() |
|RS.Edit |
|RS(0)=Text1.Text |
|RS(1) =Text2.Text |
|RS(2)=Text3.Text |
|RS(3)=Text4.Text |
|RS(4)=Text5.Text |
|RS.UPDATE |
|End Sub |
| |
|Private Sub MAINMENU_Click() |
|Form1.Show |
|Form2.Hide |
|End Sub |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
|RESULT: |
|Thus library information system was developed in Visual Basic. |
| |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 9 Date: |
|DESIGN AND IMPLEMENTATION OF BANKING SYSTEM |
| |
| |
|AIM: |
|To develop a banking system using Oracle as s back end(data base) and Microsoft Visual basic6.0 as a front end. |
|PROCEDURE: |
| |
|1.Create a banking database with the following fields in oracle namely,acno,name,address,balance and some record into the database. |
| |
|2.design the corresponding form with labels,text boxes and command buttons. |
| |
|Form1(customer details |
|Form2(withdraw |
|Form3(deposit |
| |
| |
|3.Create the back with the front end using DAO method by creating a dsn as follows |
|a.Select administrative tools option from control panel. |
|Then click on data source(ODBC),which displays a dialog box named |
|ODBCDATASOURCESADMINISTRATOR in which click Add button. |
|b.In Create New Data Source dialog box,select “Microsoft ODBC for |
|ORACLE” and click finish button |
|c.Give a suitable data source name,user name and server name. |
| |
|4.Perform the required operations like Insert,Delete,Searching and Exit. |
| |
|5.Issue the books according to the no.of copies available. |
| |
| |
|6.Execute the project |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
|EX NO: 9 Date: |
|TABLE DESIGN: |
| |
|Table Name :Banking |
| |
| |
|NAME |
|TYPE |
| |
|ACNO |
|NUMBER(6) |
| |
|NAME |
|VARCHAR2(30) |
| |
|ADDRESS |
|VARCHAR2(20) |
| |
|BALANCE |
|NUMBER(8,2) |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
|FORM 1: |
| |
|Dim DB As Database |
|Dim RS As recordset |
| |
|Private Sub Form_Load() |
|Set DB =OpenDatabase(“BANKDSN”,FALSE,FALSE,”ODBC;UID= ;PWD= ;”) |
|Set RS =DB.OpenRecordset(“select * from Bank”) |
|Text1.text=RS(0) |
|Text2.text=RS(1) |
|Text3.text=RS(2) |
|Text4.text=RS(3) |
|End Sub |
| |
|Private Sub CLEAR_click() |
|Text1.text=”” |
|Text2.text=”” |
|Text3.text=”” |
|Text4.text=”” |
|End Sub |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
|EX NO: 9 Date: |
|Private sub DELETE_Click() |
|RS.DELETE |
|MsgBox”the record is deleted” |
|RS.MoveNext |
|IfRS.EOF Then |
|MsgBox”no more records” |
|Else |
|Text1.text=RS(0) |
|Text2.text=RS(1) |
|Text3.text=RS(2) |
|Text4.text=RS(3) |
|End If |
|End Sub |
| |
|Private Sub EXIT Click() |
|End |
|End Sub |
| |
|Private Sub FIRST_Click() |
|RS .MoveFirst |
|Text1.Text=RS(0) |
|Text2.Text=RS(1) |
|Text3.Text=RS(2) |
|Text4.Text=RS(3) |
|End Sub |
| |
|Private Sub INSERT_Click() |
|RS.MoveLast |
|RS.AddNew |
|RS(0)=Text1.Text |
|RS(1) =Text2.Text |
|RS(2)=Text3.Text |
|RS(3)=Text4.Text |
|MsgBox”record is inserted” |
|RS.UPDATE |
|End Sub |
| |
| |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
|EX NO: 9 Date: |
|Private Sub LAST_Click() |
|RS. MoveLast |
|Text1.text=RS(0) |
|Text2.text=RS(1) |
|Text3.text=RS(2) |
|Text4.text=RS(3) |
|End Sub |
| |
|Private Sub NEXT_Click() |
|RS.Move Next |
|If RS.EOF Then |
|MsgBox”no more recfords” |
|Else |
|Text1.text=RS(0) |
|Text2.text=RS(1) |
|Text3.text=RS(2) |
|Text4.text=RS(3) |
|EndIf |
|End Sub |
|Private Sub PREVIOUS_Click() |
|RS.Move Previous |
|If RS.EOF Then |
|MsgBox”no more recfords” |
|Else |
|Text1.text=RS(0) |
|Text2.text=RS(1) |
|Text3.text=RS(2) |
|Text4.text=RS(3) |
|EndIf |
|End Sub |
| |
|Private Sub UPDATE_Click() |
|RS.Edit |
|RS(0)=Text1.Text |
|RS(1) =Text2.Text |
|RS(2)=Text3.Text |
|RS(3)=Text4.Text |
|RS.UPDATE |
|MsgBox”record is inserted” |
|End Sub |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 9 Date: |
|Private Sub FIND_Click() |
|1=InputBox(“ENTER THE ACNO”,”FIND”) |
|rs.FindFirst”[ACNO]=”&I |
|If rs.NoMatch Then |
|MsgBox”no such records” |
|Else |
|Text1.text=RS(0) |
|Text2.text=RS(1) |
|Text3.text=RS(2) |
|Text4.text=RS(3) |
|EndIf |
|End Sub |
| |
|Private Sub WITHDRAW_Click() |
|Form2.Show |
|End Sub |
| |
|Private Sub DEPOSIT_Click() |
|Form3.Show |
|End Sub |
| |
|FORM 2: |
| |
|Private Sub HOMEPAGE_Click() |
|Form1.Show |
|Form2.Hide |
|End Sub |
| |
|Private Sub WITHDRAW_Click() |
|Text2.Text=val(Form1.Text)-Text1.Text |
|Form1.Text4=Text2.text |
|End Sub |
| |
|End |
| |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 9 Date: |
|FORM 3: |
| |
|Private Sub DEPOSIT_Click() |
|Text2.Text=val(Form1.Text)+Text1.Text |
|Form1.Text4=Text2.text |
|End Sub |
| |
| |
|Private Sub HOMEPAGE_Click() |
|Form1.Show |
|Form3.Hide |
|End Sub |
|[pic] |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
|EX NO: 9 Date: |
|[pic] |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 9 Date: |
|[pic] |
| |
| |
| |
| |
|RESULT: |
| |
|Thus banking system was developed in Visual Basic |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 10 Date: |
|DESIGN AND IMPLEMENTATION OF PAYROLL PROCESSING SYSTEM |
| |
|AIM: |
|To develop an employee payroll system using Oracle as a back end (database) and Microsoft Visual Basic6.0 as a front end. |
|PROCEDURE: |
| |
|1.Create a employee database with the following fields in oracle namely Empno,Empname,address,Basicpay and insert some record into the database. |
| |
|2.Design the corresponding form with labels,text boxes and command buttons. |
| |
|Form1(Employee Payroll |
| |
|3.Create the back with the front end using DAO method by creating a DSN as |
|follows, |
|a.Select administrative tools option from control panel. |
|Then click on Data Source(ODBC),which displays a dialog box named |
|ODBC DATASOURCES ADMINISTRATOR in which click Add button. |
|b.In Create New Data Source dialog box,select “Microsoft ODBC for |
|ORACLE” and click finish button |
|. |
|c.Give a suitable data source name,user name and server name. |
| |
|4.Perform the required operations like Insert,Delete,Searching,Modify and Exit. |
| |
|5.Calculate the net salary of the employee. |
| |
|6.Execute the project. |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO:10 Date: |
|TABLE DESIGN: |
|Table Name :Employee |
| |
|NAME |
|TYPE |
| |
|EMPNO |
|NUMBER(6) |
| |
|EMPNAME |
|VARCHAR2(30) |
| |
|DESIGNATION |
|VARCHAR2(20) |
| |
|DA |
|NUMBER(10) |
| |
|HRA |
|NUMBER(10) |
| |
|ADDRESS |
|VARCHAR2(20) |
| |
|TA |
|NUMBER(10) |
| |
|PF |
|NUMBER(10) |
| |
|BASICPAY |
|NUMBER(8,2) |
| |
| |
| |
|LOAN.Text=adodc1.Recordset.Fields(9) |
|ADV.Text= adodc1.Recordset.Fields(10) |
|TD.Text=adodc1.Recordset.Fields(11) |
|GP.Text=adodc1.Recordset.Fields(12) |
|NP.Text=adodc1.Recordset.Fields(13) |
|End Sub |
| |
|PROGRAM TO VIEW: |
|Private sub view_Click() |
|Dim a As Integer |
|On Error Go To err |
|a=InputBox(“enter the empno”) |
|Adodc1.Recordset.MOVEFIRST |
|While Not Adodc1. Recordset.EOF |
|If Adodc1.Recordset.Fields(0)=a Then |
|fillt 1 |
|Exit Sub |
|End If |
|Adodc1.Recordset.MOVENEXT |
|Wend |
|err: |
|MsgBox(“record doesnot exist”) |
|End Sub |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
|EX NO: 10 Date: |
|Private sub MOVENEXT_Click() |
|Adodc1.Recordset.MOVENEXT |
|End Sub |
| |
|Private sub MOVEPREVIOUS_Click() |
|Adodc1.Recordset.MOVEPREVIOUS |
|End Sub |
| |
|PROGRAM TO SAVE: |
|Private sub save_Click() |
|Adodc1.Recordset.Update |
|NAME1.Enabled=False |
|NO.Enabled=False |
|DESIG.Enabled=False |
|B1.Enabled=False |
|HRA.Enabled=False |
|DA.Enabled=False |
|OA.Enabled=False |
|TA.Enabled=False |
|LOAN.Enabled=False |
|ADV.Enabled=False |
|TD.Enabled=False |
|GP.Enabled=False |
|PF.Enabled=False |
|NP.Enabled=False |
|add.Enabled= True |
|del.Enabled= True |
|BASIC.Enabled= True |
|edit.Enabled=True |
|End Sub |
| |
|Fill function |
|Public sub fillt() |
|NO.Text=adodc1.Recordset.Fields(0) |
|NAME.Text= adodc1.Recordset.Fields(1) |
|DESIG.Text=adodc1.Recordset.Fields(2) |
|MONYH.Text=adodc1.Recordset.Fields(14) |
|B1.Text=adodc1.Recordset.Fields(3) |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 10 Date: |
|DA.Text=adodc1.Recordset.Fields(4) |
|HRA.Text= adodc1.Recordset.Fields(5) |
|OA.Text=adodc1.Recordset.Fields(6) |
|TA.Text=adodc1.Recordset.Fields(7) |
|PF.Text=adodc1.Recordset.Fields(8) |
|LOAN.Text=adodc1.Recordset.Fields(9) |
|ADV.Text= adodc1.Recordset.Fields(10) |
| |
|PROGRAM TO ADD: |
| |
|Private sub add_Click() |
|NAME1.Enabled= True |
|NO.Enabled= True |
|DESIG.Enabled= True |
|B1.Enabled= True |
|HRA.Enabled= True |
|DA.Enabled= True |
|OA.Enabled= True |
|TA.Enabled= True |
|LOAN.Enabled= True |
|ADV.Enabled= True |
|TD.Enabled= True |
|GP.Enabled= True |
|PF.Enabled= True |
|NP.Enabled= True |
|save. Enabled=True |
|NO.SetFocus |
|MsgBox”new record is added” |
|add. Enabled=False |
|del.Enabled=False |
|BASIC. Enabled=False |
|edit. Enabled=False |
|End Sub |
| |
|INDIRA INSTITUTE OF ENGINEERING TECHNOLOGY |
|PANDUR, THIRUVALLUR DIST |
| |
|EX NO: 10 Date: |
|PROGRAM TO CALCULATE ALLOWANCES: |
| |
|Private sub B1_LostFocus() |
|If Val(B1.Text) ................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related searches
- definition of practical significance
- practical significance definition
- what is practical significance
- practical significance definition psychology
- practical significance vs statistical
- practical significance statistics definition
- define practical significance
- difference between statistical and practical significance
- practical significance example
- what is practical significance in statistics
- easy statistical and practical significance
- statistical vs practical significance example