Slip no. 11. Consider the following entities and their relationship
Emp( e_no, ename, address, phone, salary)
Dept(d_no, dname, location)
Emp-Dept related with many-one relationship.
Create a RDB in 3NF for above and solve the following.

Using above database write a script in PHP which will
a) Insert Employee records and Department records into respective tables.
B) Print a salary statement in the format given below, for a given Department.
[Hint : create a HTML form to accept Department name form user]

Maximum salary
Minimum salary
Sum salary

HTML file :
<form action=slip11_2.php method="get">
Enter Employee No. :<input type=text name=e_no><br>
Enter Employee Name :<input type=text name=e_nm><br>
Enter Employee Address :<input type=text name=e_addr><br>
Enter Employee Phone No. :<input type=text name=e_ph><br>
Enter Employee Salary :<input type=text name=e_sal><br>
Enter Dept No. :<input type=text name=d_no><br>
Enter Dept Name :<input type=text name=d_nm><br>
Enter Dept Location :<input type=text name=d_loc><br>
<input type=radio name="op" value="1" >Insert

Enter Dept Name :<input type=text name=d_name><br>
<input type=radio name="op" value="2" >Display

PHP file :
                $con_string="host = localhost dbname=bcs_slips port=5432  user=postgres password=nrc";
                $con = pg_connect($con_string);

                $op = $_GET['op'];
                                case 1 : $e_no = $_GET['e_no'];
                                                $e_nm = $_GET['e_nm'];
                                                $e_addr = $_GET['e_addr'];
                                                $e_ph = $_GET['e_ph'];
                                                $e_sal = $_GET['e_sal'];
                                                $d_no = $_GET['d_no'];
                                                $d_nm = $_GET['d_nm'];
                                                $d_loc = $_GET['d_loc'];
                                                $q = "insert into dept values($d_no,'$d_nm','$d_loc')";
                                                $q1 = "insert into emp values($e_no,'$e_nm','$e_addr',$e_ph,$e_sal,$d_no)";
                                                echo "Details of Employye are inserted ";
                                case 2 : $d_name = $_GET['d_name'];
                                                $q = "select MAX(emp_sal),MIN(emp_sal),SUM(emp_sal) from dept,emp where dept_name='$d_name' and emp.dept_no = dept.dept_no;";
                                                $rs = pg_query($con,$q) ;
                                                                                echo "<table align=center border=1>";
                                                                                echo "<tr><td>Maximum Sal</td><td>Minimum Sal</td><td>Sum Sal</td></tr>";
                                                                                echo "<tr><td>$row[0]</td><td>$row[1]</td><td>$row[2]</td></tr>";
                                                                                echo "</table>";

No comments:

Post a comment

Note: only a member of this blog may post a comment.