Menu Close

TYBCS PHP Assignment 5 – PHP Database (PostgreSQL)

tybcs php assignment 5 image

TYBCS PHP Assignment 5

TYBCS PHP Assignment 5 -Set A

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).

HTML FILE

<html> <head> <title> A5SETA1 </title> </head> <body> <form action="a5a1.php" method="POST"> Enter the department name : <input type="text" name="dname"> <br><br> <input type="submit" name="submit" value="Submit"> </form> </body> </html>
Code language: HTML, XML (xml)

PHP file saved as “a5a1.php”

<?php $dep=$_POST['dname']; $db=pg_connect("host=localhost user=test_user dbname=tybcs01"); if(!$db) { die("Some error occured..."); } else { echo"Successfully connected to the database <br><br>"; $query="select max(salary),min(salary),sum(salary) from Emp where dept_no in (select dept_no from Dept where d_name='".$dep."'"; $result=pg_query($db,$query); if(!$result) { die("Some error occured..."); } while($row=pg_fetch_row($result)) { echo '<table border=1> <tr> <th>Maximum Salary</th> <th>Minimum Salary</th> <th>Sum of Salary</th> </tr> <tr> <td>'.$row[0].'</td> <td>'.$row[1].'</td> <td>'.$row[2].'</td> </tr> </table>'; } } pg_close($db); ?>
Code language: PHP (php)

Q: 2) Consider the following entities and their relationships Doctor (doc_no, doc_name, address, city, area) Hospital (hosp_no, hosp_name, hosp_city) Doctor and Hospital are related with many-many relationship. Create a RDB in 3 NF for the above and solve following Using above database, write a PHP script which accepts hospital name and print information about doctors visiting / working in that hospital in tabular format.

HTML file

<html> <head> <title> A5SETA2 </title> </head> <body> <form action="a5a2.php" method="POST"> Enter the Hospital name : <input type="text" name="hname"> <br><br> <input type="submit" name="submit" value="Submit"> </form> </body> </html>
Code language: HTML, XML (xml)

PHP file saved as “a5a2.php”

<?php $hname=$_POST['hname']; $db=pg_connect("host=localhost user=test_user dbname=tybcs01"); if(!$db) { die("An error occured..."); } else { echo "Successfully connected to the database <br><br>"; $query="select * from Doctor where doc_no in (select doc_no from d_h where hosp_no in (select hosp_no from hospital where hosp_name='".$hname."'))"; $result=pg_query($query); if(!$result) { die("An error occured..."); } echo '<table> <tr> <th>Doc. No</th> <th>Doc. Name</th> <th>Doc. Address</th> <th>City</th> <th>Area</th> </tr>'; while($row=pg_fetch_row($result)) { echo ' <tr> <td>'.$row[0].'</td> <td>'.$row[1].'</td> <td>'.$row[2].'</td> <td>'.$row[3].'</td> <td>'.$row[4].'</td> </tr> </table>'; } } pg_close($db); ?>
Code language: PHP (php)

TYBCS PHP Assignment 5 – SET B

Q: 1) Considerer the following entities and their relationships project(pno integer, p_name char(30), ptype char(20),duration integer) employee (eno integer, e_name char (20), qualification char (15), joindate date) The relationship between project – employee: M-M, with descriptive attributes as start_date (date), no_of_hours_worked (integer). Using above database write a script in PHP to accept a project name from user and display information of employees working on the project.

HTML file

<html> <head> <title> A5SETAB1 </title> </head> <body> <form action="a5b1.php" method="POST"> Enter the Project name : <input type="text" name="pname"> <br><br> <input type="submit" name="submit" value="Submit"> </form> </body> </html>
Code language: HTML, XML (xml)

PHP file saved as “a5b1.php”

<?php $pname=$_POST['pname']; $db=pg_connect("host=localhost user=test_user dbname=tybcs01"); if(!$db) { die("An error occured..."); } else { echo "Successfully connected to the database <br><br>"; $query="select * from employee where emp_no in(select emp_no from e_p where p_no in(select p_no from projetc where pname='".$pname."'))"; $result=pg_query($query); if(!$result) { die("An error occured..."); } echo '<table> <tr> <th>Eno</th> <th>Ename</th> <th>Join Date</th> <th>Qualifications</th> </tr>'; while($row=pg_fetch_row($result)) { echo '<table> <tr> <td>'.$row[0].'</td> <td>'.$row[1].'</td> <td>'.$row[2].'</td> <td>'.$row[3].'</td> </tr>'; } } pg_close($db); ?>
Code language: PHP (php)

Q: 2) Consider the following entities and their relationships student (sno integer, s_name char(30), s_class char(10), s_addr char(50)) teacher (tno integer, t_name char (20), qualification char (15),experience integer) The relationship between student-teacher: m-m with descriptive attribute subject. Using above database write a script in PHP to accept a teacher name from user and display the names of students along with subjects to whom teacher is teaching.

HTML file

<html> <head> <title> A5SETAB2 </title> </head> <body> <form action="a5b2.php" method="POST"> Enter teacher's name : <input type="text" name="tname"> <br><br> <input type="submit" name="submit" value="Submit"> </form> </body> </html>
Code language: HTML, XML (xml)

PHP file saved as “a5b2.php”

<?php $tname=$_POST['tname']; $db=pg_connect("host=localhost user=test_user dbname=tybcs01"); if(!$db) { die("An error occured..."); } else { echo "Successfully connected to the database <br><br>"; $query="select sname,subject from student,s_t where sno in(select sno from s_t where tno in(select tno from teacher where tname='".$tname."'))"; $result=pg_query($query); if(!$result) { die("An error occured..."); } echo '<table> <tr> <th>Sname</th> <th>Subject</th> </tr>'; while($row=pg_fetch_row($result)) { echo' <tr> <td>'.$row[0].'</td> <td>'.$row[1].'</td> </tr>'; } echo '</table>'; } pg_close($db); ?>
Code language: PHP (php)

SET C

Q: 1) Consider the following entities and their relationships Movie (movie_no, movie_name, release_year) Actor (actor_no, name) Relationship between movie and actor is many – many with attribute rate in Rs. Create a RDB in 3 NF for the above and solve following Using above database, write PHP scripts for the following:(Hint: Create HTML form having three radio buttons)

a) Accept actor name and display the names of the movies in which he has acted.

b) Insert new movie information.

c) Update the release year of a movie. (Accept the movie name from user)

HTML File

<html> <head> <title> A5SETC1 </title> </head> <body> <form action="a5c1.php" method="POST"> <input type="radio" name="rad" value="1"> Enter Actor Name : <input type="text" name="aname"> <br><br><br> <input type="radio" name="rad" value="2"> Enter Movie Information: <br><br> Movie name : <input type="text" name="mname"> <br> <br> Release Year : <input type="text" name="ryear"> <br><br> <br> <input type="radio" name="rad" value="3"> Update Movie Release Year <br><br> Enter Moive Name: <input type="text" name="mname"> <br><br> Enter updated year: <input type="text" name="ryear"> <br><br> <input type="submit" name="submit" value="Submit"> </form> </body> </html>
Code language: HTML, XML (xml)

PHP file saved as “a5c1.php”

<?php //Assuming all relations exist in the database $db=pg_connect("host=localhost user=test_user dbname=tybcs01"); if(!$db) { die("An error occured..."); } else { echo "Successfully connected to the database <br><br>"; $ch=$_POST['rad']; switch($ch) { case '1': $aname=$_POST['aname']; $query="select movie_name from movie where movie_no in(select movie_no from movie_actor where act_no in(select act_no from actor where act_name='".$aname."'))"; $result=pg_query($query); if(!$result) { echo "Some error occured"; break; } while($row=pg_fetch_row($result)) { echo ''.$row[0].''; echo "<br>"; } break; case '2': $mname=$_POST['mname']; $ryear=$_POST['ryear']; $query="insert into movie values(DEFAULT, '".$mname."', '".$ryear."')"; $result=pg_query($query); if(!$result) { echo "Some error occured"; break; } echo "Inserted new record successfully <br><br>"; break; case '3': $mname=$_POST['mname']; $ryear=$_POST['ryear']; $query="update table movie set ryear='".$ryear."' where mname='".$mname."'"; $result=pg_query($query); if(!$result) { echo "Some error occured"; break; } echo "Updated information successfully"; break; } pg_close($db); } ?>
Code language: PHP (php)

Q: 2) Considerer the following entities and their relationships Student (Stud_id,name,class) Competition (c_no,c_name,type) Relationship between student and competition is many-many with attribute rank and year. Create a RDB in 3NF for the above and solve the following. Using above database write a script in PHP to accept a competition name from user and display information of student who has secured 1 st rank in that competition.

HTML file

<html> <head> <title> A5SETC2 </title> </head> <body> <form action="a5c2.php" method="POST"> Enter competition name: <br> <input type="text" name="cname"> <br><br> <input type="submit" name="submit" value="Submit"> </form> </body> </html>
Code language: HTML, XML (xml)

PHP file saved as “a5c2.php”

//Assuming all relations exist in the database <?php $db=pg_connect("host=localhost user=test_user dbname=tybcs01"); if(!$db) { die("An error occured..."); } else { echo "Successfully connected to the database <br><br>"; $cname=$_POST['cname']; $query="select * from student where stud_id in(select stud_id from student_competetion where rank=1 and c_no in(select c_no from competetion where c_name='".$cname."'))"; $result=pg_query($query); if(!$result) { die("Some error occured"); } while($row=pg_fetch_row($result)) { echo ''.$row[0].' <br>'; //id echo ''.$row[1].' <br>'; //name echo ''.$row[2].' <br>'; //class } } pg_close($db); ?>
Code language: PHP (php)

If you have any questions in the codes or any suggestions to improve it, feel free to comment below.

Please note: You should use these codes just to refer and not just blatantly copy paste. You will not learn how to program in the later way.

If you are using these codes on your websites please see that you give source link as this website. Its not necessary but still I would be glad if you choose to do so.

Check out solutions to Assignment 4 here : https://wintrysphere.me/tybcs-php-assignment-4/

Learn PHP at tutorialspoint : https://www.tutorialspoint.com/php/index.htm

1 Comment

  1. Pingback:TYBCS System Programming Practice MCQs - wintrysphere

Leave a Reply

Your email address will not be published. Required fields are marked *