Q: 1) Consider the following entities and their relationships
Emp (emp_no,emp_name,address,phone,salary)
Dept (dept_no,dept_name,location)
Emp-Dept are related with one-many relationship Create a RDB in 3NF for the above and solve following Using above database write a PHP script which will print a salary statement in the format given below, for a given department. (Accept department name from the user). Deparment Name : _________________
HTML File:
<html>
<body>
<form action="a5a1.php" method="POST">
Enter Department name : <input type="text" name="dept_name" /></br>
<input type="submit"/>
</form>
</body>
</html>
PHP Function:
NOTE: PHP function is saved as "a5a1.php"
<?php
$dept_name = $_POST['dept_name'];
$con = pg_connect( "dbname=test" );
if( !$con ) {
echo 'Connection not established';
exit;
}
$dept_insert = "insert into dept values(1,'HR','1st Floor') ,(2, 'Comp sci', '2nd floor'),(3,'Maths','2nd
Floor'),(4,'Electronics','3rd Floor')";
$res = pg_query($con,$dept_insert);
$emp_insert = "insert into employee values( 1, 'abc','Pune','7894356','20000',1),( 2, 'abc','Pune','7894356','10000', 1),( 3,
'abc','Pune','7894356','30000',3),( 4, 'abc','Pune','7894356','120000',2)";
$res1 = pg_query($con,$emp_insert);
$strQuery = "select max(salary), min(salary), sum(salary) from employee where d_no in(select d_no from dept where
dname = '$dept_name')";
$result = pg_query($con,$strQuery);
$row = pg_fetch_row($result);
?>
<html>
<body>
<table>
<tr>
<th>Maximum salary</th>
<th>Minimum salary</th>
<th>Sum</th>
</tr>
<tr>
<td><?php echo $row[0];?></td>
<td><?php echo $row[1];?></td>
<td><?php echo $row[2];?></td>
</tr>
</table>
</body>
</html>
Emp (emp_no,emp_name,address,phone,salary)
Dept (dept_no,dept_name,location)
Emp-Dept are related with one-many relationship Create a RDB in 3NF for the above and solve following Using above database write a PHP script which will print a salary statement in the format given below, for a given department. (Accept department name from the user). Deparment Name : _________________
HTML File:
<html>
<body>
<form action="a5a1.php" method="POST">
Enter Department name : <input type="text" name="dept_name" /></br>
<input type="submit"/>
</form>
</body>
</html>
PHP Function:
NOTE: PHP function is saved as "a5a1.php"
<?php
$dept_name = $_POST['dept_name'];
$con = pg_connect( "dbname=test" );
if( !$con ) {
echo 'Connection not established';
exit;
}
$dept_insert = "insert into dept values(1,'HR','1st Floor') ,(2, 'Comp sci', '2nd floor'),(3,'Maths','2nd
Floor'),(4,'Electronics','3rd Floor')";
$res = pg_query($con,$dept_insert);
$emp_insert = "insert into employee values( 1, 'abc','Pune','7894356','20000',1),( 2, 'abc','Pune','7894356','10000', 1),( 3,
'abc','Pune','7894356','30000',3),( 4, 'abc','Pune','7894356','120000',2)";
$res1 = pg_query($con,$emp_insert);
$strQuery = "select max(salary), min(salary), sum(salary) from employee where d_no in(select d_no from dept where
dname = '$dept_name')";
$result = pg_query($con,$strQuery);
$row = pg_fetch_row($result);
?>
<html>
<body>
<table>
<tr>
<th>Maximum salary</th>
<th>Minimum salary</th>
<th>Sum</th>
</tr>
<tr>
<td><?php echo $row[0];?></td>
<td><?php echo $row[1];?></td>
<td><?php echo $row[2];?></td>
</tr>
</table>
</body>
</html>
1 Comments
ReplyDeleteConsider the following entities and their relationships. Create a RDB in 3 NF with
appropriate data types and Constraints. [15 Marks]
Property (pno, desc, area, rate)
Owner (owner_name, addr, phno)
The relationship between owner and Property is One to Many.
Constraint: Primary key, rate should be > 0
Consider the above tables and execute the following queries:
1. Display area of property whose rate is less than 100000.
2. Give the details of owner whose property is at “Pune” .
Q4. Consider the above tables and execute the following queries: [25 Marks]
1. Display area wise property details.
2. Display property owned by 'Mr.Patil' having minimum rate.
3. Delete all properties from “pune” owned by “Mr. Joshi”.
4. Update the phone Number of “Mr. Joshi” to 9922112233 who is having
property at “Uruli Kanchan”.
5. Delete column address from Owner table.