Scribd
Upload
20 views22 pages

Payroll System Database Design Guide

1. The document discusses various topics related to database design and query processing for a payroll processing system, including: 2. Applying normalization to reduce data redundancy and improve data integrity. 3. Implementing integrity constraints using triggers to enforce business rules.

Uploaded by

Srilekha
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
20 views22 pages

Payroll System Database Design Guide

1. The document discusses various topics related to database design and query processing for a payroll processing system, including: 2. Applying normalization to reduce data redundancy and improve data integrity. 3. Implementing integrity constraints using triggers to enforce business rules.

Uploaded by

Srilekha
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

1.

Payroll Processing System- database design using E-R diagrams


[Link] Normalization on Payroll Processing System.

package threads;

import [Link];
import [Link].*;

public class normal {


public static void main(String args[])
{
try {
//2NF
Connection
con=[Link]("jdbc:postgresql://localhost:5432/adbms",
"postgres","cse");
String s="create table dept as (select did,dname from emp_dept)";
PreparedStatement p=[Link](s);
[Link]();
String t="create table emp as (select eid,ename,did,sal from emp_dept)";
p=[Link](t);
[Link]();
//3NF
String st="create table emp1 as (select eid,pid from employee)";
p=[Link](st);
[Link]();
String tr="create table proj as (select pid,sal from employee)";
p=[Link](tr);
[Link]();
}
catch(Exception e) {
[Link]();
}
}
}

Output:
2NF:
3NF:
3. Integrity Constraints Enforcement, high level language with triggers on payroll
processing systems.

[Link]

accept x integer prompt 'enter val:'


create or replace trigger trig1
after insert on emp
FOR EACH ROW
DECLARE
a integer;
BEGIN
a:=&x;
insert into asg value(:[Link],(MOD(:[Link],a)));
END;
/

[Link]

accept x integer prompt 'enter val:'


create or replace trigger trig2
after insert on asg
FOR EACH ROW
DECLARE
b integer;
BEGIN
b:=&x;
insert into sal values(:[Link],(:[Link]+b));
END;
/

Output:

For a=5 and b=1000,


4. Implement query Processing Algorithm : Nested Loop Join.

package javaapplication8;
import [Link].*;
public class nloop {
public static void main(String args[]){
try{ int id1=0,id2=0,amt=0;
[Link]("[Link]");
Connection
con=[Link]("jdbc:postgresql://localhost:5432/dbms","p
ostgres","cse");
PreparedStatement p1=[Link]("select * from
cust");
ResultSet r1=[Link]();
PreparedStatement p2=[Link]("select * from
bill",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet r2=[Link]();
PreparedStatement p3=[Link]("insert into njoin
values(?,?,?)");
while([Link]()){
id1=[Link](1);
String cname=[Link](2);
[Link]();
while([Link]()){
id2=[Link](1);
amt=[Link](2);
if(id1==id2){
[Link](1,id1);
[Link](2,cname);
[Link](3,amt);
[Link]();
}

}
}
}
catch(Exception e){
[Link]();
}
}
}
Output:-
5. Implement query Processing Algorithm : Merge Join.

import [Link].*;
public class join {
public static void main(String args[]){
try{
[Link]("[Link]");
Connection
con=[Link]("jdbc:postgresql://localhost:5432/dbms","p
ostgres","cse");
PreparedStatement p1=[Link]("select * from
user3;",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
PreparedStatement p2=[Link]("select * from
user4;",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
PreparedStatement p3=[Link]("insert into
user5(id,name) values(?,?);");
ResultSet rs1=[Link]();
ResultSet rs2=[Link]();
boolean y=true;
int c=0;
[Link](); [Link]();
while(y){
if(c==1) if(![Link]()) break;
if(c==2) if(![Link]()) break;
int j=[Link](1);
int k=[Link](1);
[Link](j+" "+k);
if(j<k){
String t=[Link](2);
[Link](1,j);
[Link](2,t);
[Link]();
//if(![Link]()) y=false;
//else [Link]();
c=1;

}
else{
String l=[Link](2);
[Link](1,k);
[Link](2,l);
[Link](2);
[Link]();
//if(![Link]()) y=false;
// else [Link]();
c=2;
}
}
//[Link](j);
if(c==1) [Link]();
if(c==2) [Link]();
while([Link]()){
int j=[Link](1);
String t=[Link](2);
[Link](1, j); [Link](2,t);
[Link]();
}
while([Link]()){
int j=[Link](1);
String t=[Link](2);
[Link](1, j); [Link](2,t);
[Link]();
}
}
catch(Exception e){
[Link]();
}
}
}
Output:-
6. Implement query Processing Algorithm : Hash Join.

public class test2 {


public static void main(String args[]){
try{ int id=0,h=0;
[Link]("[Link]");
Connection
con=[Link]("jdbc:postgresql://localhost:5432/dbms","p
ostgres","cse");
PreparedStatement p1=[Link]("select count(*)
from tab1");
ResultSet r1=[Link]();
[Link]();
int count=[Link](1);
//insert into hashtable
PreparedStatement p2=[Link]("select * from
tab1");
r1=[Link]();
PreparedStatement p3=[Link]("insert into hash
values(?,?,?)");
while([Link]()){
id=[Link](1);
String name=[Link](2);
h=id%(count+1);
[Link](1,id);
[Link](2,name);
[Link](3, h);
[Link]();
}
p1=[Link]("select * from tab2");
ResultSet r2=[Link]();
p2=[Link]("select * from
hash",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
r1=[Link]();
p3=[Link]("insert into hjoin values(?,?,?)");
while([Link]()){
id=[Link](1);
String name=[Link](2);
h=id%(count+1);
[Link]();
while([Link]()){
int h1=[Link](3);
String n=[Link](2);
if(h1==h){
[Link](1,id);
[Link](2,n);
[Link](3,name);
[Link]();
break;
}
}

}
}
catch(Exception e){
[Link]();
}
}
}

Output:-
7. Image storage and retrieval in MySql database.

//img in byte format


package javaapplication8;
import [Link].*;
import [Link].*;

public class JavaApplication8 {

public static void main(String[] args) {


// TODO code application logic here
try{
[Link]("[Link]");
Connection con=[Link](
"jdbc:postgresql://localhost:5432/dbms","postgres", "cse" );
//here sonoo is database name, root is username and password
[Link]("database connected");
File file = new File("C:\\[Link]");
FileInputStream fis = new FileInputStream(file);
PreparedStatement ps = [Link]("INSERT INTO s VALUES (?,?)");
[Link](1,1);
[Link](2, fis, (int)[Link]());
[Link]();
[Link]();
[Link]();
PreparedStatement pt = [Link]("SELECT img FROM s");
//[Link](1, "[Link]");
ResultSet rs = [Link]();
while ([Link]()) {
byte[] imgBytes = [Link](1);
[Link](imgBytes);
// use the data in some way here
}
[Link]();
[Link]();
[Link]("database connected");
[Link]();
}catch(Exception e){ [Link](e);}
}

}
Output:
8. Transaction Processing activities application program execution and concurrent
Processing.

package threads;
import [Link].*;
public class table {
synchronized void add(int n){//synchronized method
try {
Connection
con=[Link]("jdbc:postgresql://localhost:5432/dbms","postgres","
cse");
[Link]("connected");
PreparedStatement p=[Link]("select balance
from account where id=1");
ResultSet r=[Link]();
[Link]();
int amount=[Link](1);
amount+=n;
p=[Link]("Update account set balance=?
where id=1");
[Link](1,amount);
[Link]();
}
catch(Exception e) {
[Link]();
}

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

table obj = new table();//only one object


thread1 t1=new thread1(obj);
thread2 t2=new thread2(obj);
[Link]();
[Link]();
}
}

class thread1 extends Thread{


table t;
thread1(table t){
this.t=t;
}
public void run(){
[Link](100);
}
}
class thread2 extends Thread{
table t;
thread2(table t){
this.t=t;
}
public void run(){
[Link](-500);
}
}

Output:

Before

After

You might also like