INDEX

Sl Assignment name Page no Signature

no.
1 Consider the following Relational Schema:
CUSTOMER (cust_id, cust_name, annual_revenue)
SHIPMENT (shipment_id, cust_id, weight, truck_id, destination)
Foreign Key Destination references CITY
TRUCK (truck_id, driver_name)
CITY (city_name, population)
Create the database and insert sufficient no of records Write SQL commands to do the
following:
A. i) Delete only those cities from the database whose population fewer than 5000 and
at the same time delete them form SHIPMENT table.
ii) List the cities in the database having largest and smallest populations.
iii) List the names and populations of the cities that have received shipments weights
over 10pounds.
B. i) Convert the weight of every shipment in kilograms by dividing the weight by 2.2.
ii) List all the customers having over Rs. 2,50in annual revenue who have sent
Shipments weighing less than hundred pounds.
iii) List all minimum weight of packets sent by the cities with population over 5,000.
2 Consider the following Relational schema:
Borrower (b_name, b_id);
Book (book_id, title, author, subject);
Borrows (book_id, b_id, date_of_issue, date_of_return);
Create table through appropriate sql commands. Define all integrity constraints and enter
sufficient data . Write SQL commands for the following queries:
i)Give all the books on physics and mathematics.
ii)Find out all the title and author of all the books issued by the borrower where b_id is
B001.
iii) Find out the names of the borrower who have issued books written by C.J.Date
3 Consider the following Relational Schema:
STUDENT (student_id, student_name)
IS_QUALIFIED (faculty_id, course_id, date_qualified)
FACULTY (faculty_id, faculty_name)
SECTION (section_id, course_id)
COURSE (course_id, course_name)
IS_REGISTERED (student_id, section_id, semester)
Create the database and insert records. Define all integrity constraints Write SQL
commands to do the following:
i)Display the course_id and course_name for all the courses with an ISM prefix.
ii)Display the student name for all students enrolled in section 2714 of ISM 4212.
iii)List all the students who weren’t enrolled in any course during semester I-2008.
4 Consider the following Relational schema:
Sailors (s_id, s_name, rating)
Boats (b_id, b_name, color)
Reserves (s_id, b_id, day)
Bid must be between 10and 30 and color must be RED, GREEN, BLUE, YELLOW Create the
database and insert records. Define all integrity constraints Write SQL commands to do the
following:
i) Find the name of the sailors who have reserved both a red and green boats.
ii) Find the colors of boats reserved by Rahim.
5 Consider the following Relational Schema:
EMPLOYEE (emp_id, emp_name, street, city)
WORKS (emp_id, company_name, salary)
[Salary range- 10,00to 1, 00,000]
COMPANY (company_name, city)
[Company_name must be ABC, PQR or XYZ]
MANAGER (mgremp_id, manager_name)
Create the database and insert records . Define all integrity constraints Write SQL
commands to do the following:
i)Find the names, street address and cities of residence of all employees who works for
ABC company and earn more than Rs10,000/-
ii)Find all employees who do not work for ABC company.
6 Consider the following relational schema:
EMPLOYEE (emp_id, e_name, address, salary, d_no)
DEPARTMENT (d_name, d_number, mgrempid)
PROJECT (p_name, p_number, p_location, d_num) Create the database and insert
sufficient no of records. Define all integrity constraints Write SQL commands to do the
following:
 i)List the name of employees whose salary is greater than the salary of all the employee in
the department D005
ii)Show the resultant salaries if every employee working on the product X project is given a
1percent rise.
iii) Retrieve all employees in department D005 whose salary is between 200 and 5000.
7 Consider the following Relational schema:
Student (s_num, s_name, major, lavel, age)
Class (c_name, time_schedule, room, faculty_id)
Enrolled (s_num, c_name)
Faculty (faculty_id, ename, dept_id)
Create table through appropriate sql commands. Define all integrity constraint and enter
sufficient data . Write SQL commands for the following queries:
i)Find the name of faculty members who teach in every room in which some class is taught.
ii) Print the level and the average age for that level.
8 Consider the following Relational schema:
Salesperson (ssn, name, startyear, dept_no)
Trip (ssn, fromcity, tocity, departure_date, return_date, trip_id)
Expense (trip_id, account, amount)
Create the database through appropriate SQL command. Define all integrity constraints
Insert sufficient number of records. Write SQL commands for the following queries:
i)Give the details of trips that exited rupees 2000 in expenses.
ii)Print the ssn of the salesman who trip from howrah.
iii)Print the total trip expenses in earned by the salesman with ssn 145268547
9 Design a 'Stadium Booking System that maintains the following database:
stadium (sid, name, event, type, capacity, price)
organiser (oid, org-name, type-of-org)
booking (bid, sid, oid, date-of-booking)
Now perform the following operation using PHP:
(i) Display all the records from table stadium in tabular format.
(ii) Delete the records from table "Stadium" whose price is greater than 10,000.

10 Design a 'Cab allotment System that maintains the following database:

Cab (cnu, model, colour, purchase-date)
driver (did, dname, phone, rating, age)
alloted-in (cno, did, datel)
[Rating should be between 0 and 5)
Now perform following operations using PHP:
(i) Insert a record in the table "Cab" through PHP.
(ii) Display all the records of table "Cab" through PHP.

11 Design a 'Sales Tracking System with the following database:

item (ino, iname, type, price, colour)
cust (cid, cname, phone, city)
sales (sales-id, ino, cid, sales-date)
Create the above tables in MySQL with following and other integrity constraints:
ino must be set to value 100 and it will be autoincremented by 1.
Now perform the following operations using PHP:
(i) Change the phone no. of customer having cid = COO1
(ii) Display all customers from Kolkata and Durgapur who have purchased a dish
washer in SEP, 22 through PHP script
12 Design a 'ART GALLERY SYSTEM' with following database:
Gallery (gid, gname, capacity, city)
artists (aid, aname, age, rank)
reserved (gid, aid, date reserve)
Now perform the following operation using PHP:
Design form to insert the record in 'Artists' table and insert record in the table
'Artists' through form.

13 Design a 'Flight Reservation System' with following database:

flights (fl-no, from 1, to 1, distance, price)
aircraft (a-id, a-name, cruising-range)
emp (e-id, e-name, salary)
certified (e-id, a-id)
Now perform the following operations through PHP:
Design a login table to take user name and password. Show all records of the 'emp' table
for an authentic user. A user is authenticated if the user name & password exists in the
MySQL database 'Login' table.