Q.
CS-67 RDBMS LAB (Practical Papers)
A regional centre wants to do monitoring of study centres. For this purpose a databasehas to designed the following :
(i) Keep Information About Study Centres, names of study centre coordinators.
(ii) Programwiseenrorment statusat the various centres.
(iii) Programwiseperformance of students at the various centres.
(iv) Information about facultiesqualification.
(v) Fee submissionstatus(studycentrewise).
(i) Assignment submission status.
Do the following tasks. (Make and justify suitable assumptions,if any) :
Design and implement the-normalized database tables for the requirements listed above.
You must implement the keys, validation checks and constraints including referential constraints'Enter 5 sets of meaningfuldata in each of the tabres.
Design and implement suitablequerieforms /reports for (i) to (vi) above
A regional passport office in Delhi wants to keep records of application forms received, dispatched,pending, rejected and generate different types of reports accordingly. Applicants may be in different categories: children, adults,students,professionals, governmentemployees and diplomats. Diplomats should be issuedpassportson high priority.
Do the following activitiesfor the above.
(i) Design and implement the suitable normalized databasetables considering the above requirements.
You are also requiredto perform integrity checksand show the relationship
among tables and key attributes(foreign key).
Design and implement suitable queries/forms/reports for the above problem statement.
A bank wants to maintain information about their credit card holders. It creates the following
information for the database :
(i) Details of credit card holders (name, dg€,
designation, address).
(ii) Monthwise details of money withdrawal
by card holders.
(iii) Monthwise details of purchasing of items.
(iv) Date of renewal of card.
(v) Preparation of monthly statements.
Do the following (Make and state suitable
assumptions,if any) :
(a) Design and implement the normalizeddatabase tables for the requirementslisted above.
You must implement the key, validation checks and constraints including referential
constraints.Enter 5 sets of meaningfuldata in each of the tables.
(b) Design and irnplement suitablequeriesforms/reports for (i) to (v) above.
The university wants to keep records of studentswho have taken admission in the various programmes. through online mode and generatedifferent types of reports. The university also
wants to keep records of availabilityof bandwidth, computers and the required s/w to access in online resourcesby students. There are different categories of programs : Certificate, Diploma, Graduate and Post-graduateprograms. Students joining the program could be working, non-working, foreigners etc.
Do the following steps for the above problem statement.
(a) Designand implement relationslables consideringthe requirementsas given above.Your design should have normalized tables, integrity checks, relationship among tables and key/foreign key attributes.
(b) Design and implement suitablequeries,ftorms/reports the problems mentioned above.
Electronic media production centre of IGNOU wants to keep the following
information and
generatethe monthly reports :
(il Live telecast(Name of the presenter,Designation,school, Topic) (ii)Programwise telecast (program, School)
(iii) Cancellationof telecast(Program, Name of faculty, Date of cancellation,School)
(iv) Number of queries receivedfrom different regional'centres(Regional
Centre, program, ." Date)
Design and implement the normalizeddatabasetables for the requirements'listed as above.
You must implement keys, validation checks and constraints, including
referential constraints.Enter 5 sets of meaningfr.d data in each of the tables.
Design and implement zuitable queries,/forms/reports for (i) to (iv) above
Students registrafion and eValuation dMsion wants to keep records of examination forms receivedfrom studentsfor both theory and practical, admit card dispatched,pending, rejected and generatedifferent typgs of reports.
Do the following activities for the above :
(il Design and implement the suitable normalized database tables. Considering the above requirements you are also required to perform lntegrity checks and show the relationship among tables and key attributes (foreign key).
(ii) Design and implement suitable querieslorms for the above problem statement
Material publishing and distribution dMsion wants to keep records of printing, dispatch and undeliveredprinted books and assignmentsto study centres, regional centres and directly to students and generate weekly statusreports.
Do the following activities for the above :
(i) Design and implement normalized databasetables considering the above requirements.
You are also requiredto perform integrity checksand show the relationship among tables and key attributes(Foreign key).
Design and implement suitable queries/forms the above problem statement
Project facilitation unit of IGNOU wants to keep records of type of projects (short term, long
term, extemally fuhded etc.) being executedin different schools,names of project coordinators, designation, funding agency, duration of the project, cost of the project, amount sanctioned,/received from agency and generatestatusreport.
Do the following tasks (make and justify suitableassumptions, if any) :
Design and implement the normalizeddatabasetables for the requirementslisted above.
You must implement the keys, validation checks and constraints, including referential constraints.Enter 5 sets of meaningfuldata in each of the tables.
Design and implement suitable queries/orms,/reportsfor the above requirements.
Project evaluation is one of the important activities for BCA,/MCA students.The regional centre maintains the following information about the projects :
The hst of students and projects submitted by them
The lis of evaluators
The albcation of student projects to evaluators
Keeping track of evaluated projects
Design and implement the normalised tables for the requirements listed above.
You rfnrst irnplementst keys, constraints,validation checks and referential actions.
Enter 5 sets of meaningful data in each of the relation/table.
Design and implerhenf five suitable queries/forms/reports for the above.
In a simple financial system of a company,it keeps the pay re cords of its employees using a database System. The Company Has Two types Of employees, managers and assiatants. The Pay Includes basic Pay and Inducements,if also has deductions like PF, income tax etc. The Pay Data is produced for each employee on a monthly income.
Perform the following activities of the Company
(a) Destgn and irnplement the suitable normalised relations/tables that keeps the employee records and and, monthly payroll data. You must include keys, constrains and referential constraints in your implementation
Enter 5 sets of meaningful data in each of the relation/table.
/forms/reports
Design and implement five suitable queries /forms/reports
.
Design and implement five suitablequeries for the problem as above
Make suitable assumptions,if any.
A Bank Offers international Accounts for its customers. The Bank Allows international ATM's withdrawals, electronics fund transwer, pnit Of sale withdrawal and similar services. Bnak Charges a fee (different fee for different services) from the customers.
(a) Design and implement suitable tables/relations to keep track of customers of the bank, services used by them and bank charges that they need to pay in a month. Please Include Keys and Constraints in your implementation.
(b) Enter about 5 sets of meaningful data in each of the table/relation.
(c)Design an implement 5 suitable quries/forms/reports for the requirements as Above.
Make Suitable Assumptions If Any.
A software development company has the following setup :
(1) The company has a set of departments
(i0 Each department has a set of employees and a set of projects.
(iii) An employee can work on any project of any department.
Perform the following activities for the company :
(a) Design and implement suitable relations for the company that keep track of its departments,employees,projects and employeesinvolvement in various projects. Include suitable constraints in your design.
(b) Enter about 3-5 sets of meaningful data in each of the relation.
(c) Design and implement 5 suitable queries/forms/reports for the database created at (a) and (b).
Make suitable assumptions, if any.
A databaseused in an "Ordering System" keeps the information about the customers (who placesthe order), items and the orders. A customer may give several orders and an order may have many items. Each item has a price/unit. Perform the following activities for the requirements as above :
(a) Design and implement suitable relations/tables for the "Ordering System" that keeps track of its customers, their orders and various items. You must include keys, constraints and referential integrity constraints in your implementation.
(b) Enter 3-5 sets of meaningful data in each of the table/relation.
Design and implement five suitable queries/formsf reports for the "Ordering System".
A databasecontains information about salesrepresentatives,salesareas and products.
Each sales representative is responsible for sales in one or more areasand also for sales of one or more products. Perform the following activities for the requirements as above :
(a) Design and implement the tables/relations for the requirements as above. You must include the keys, constraints and validation checksin your implementation.
(b) Enter about 3-5 sets of meaningful data in each of the table/relation. 10
(c) Design and implement 5 suitable queries/forms/reports for the database.
Make suitable assurnptions, if any.
A manufacfuring company uses many small parts to manufacture engineering products like pumps, water purifiers, vacuum cleaners etc. The company purchases the small parts from various vendors. It sellsthe manufactured goods through its dealers.Perform the following activities for the company :
(a) Design and implement suitable relafions/tables for the company to keep track of 2A inventory of parts, the vendor details and supply made by the vendors, and the finished goods inventory with the dealers.Your implementation should include the keys, constraints and referentiai constraints.
(b) Enter 2-3 sets of meaningful data in each of the tables/relations. 10
(c) Design and implement five suitable queries/forms/reports for the company.
Make suitable assumptions, if any
A software selling company has the following set up :
.
The company has a marketing department and a product development
department.
.
Each department has its own set of employees. The company keeps track of its
employee information like name, address,date of joining, date of next promotion,
area of sale (if sales department) or software product involved in (if product
development department).
.
An employee can not be transferred from one department to other and can work
on only one product development or can have only one area of sales.
The performance of all sales department employees is measured by the number
of software products sold in his/her area. The performance of product
development department is not measured.
Perform the following activities for the company :
(a) Design and implement normalised relations for the company as per the
requirements given above. Include suitable keys and constraints in your design.
(b) Enter about 5-8 sets of meaningful data in each of the relation.
(c)Design and implement 5 suitable queries/formsf repofts for the database.
A publishing company keeps track of orders that have been received by it for various
publications. The basic publication of company is the books. Each book has a single author. On the basis of orders, the books are sold to clients, book storesetc. The sales of a book is used to determine the royality (payment) for the author. You may assume here that sales of a book is same as the orders received for that book. Perform the following tasks for the company :
(a) Design and implement the normalised tables/ relations for the requirements given above. You should include the keys, constraints and validation checks in your implementation.
(b) Enter about 5-8 sets of meaningful data in each of the table/relations. (c)Design and implemerit five suitable queries/forms/reports for the company.
A medical agency keeps track of various Doctors and medical stores in a geographical area. It also keepstrack of various medicinessold by medical storeson the prescription of Doctors. One of the key concern of the agency is to determine, the popular medicine brands. Perform the following tasks for the medical agency :
Design and implement the normalised tables/relations for the requirements as given above. You should include the keys, constraints and validation checks in your implementation.
(b) Enter about 5-8 sets of meaningful data in each of the table/relation. (c)Design and implement five suitable queries/forms/reports for the database
A mobile phone company keepstrack of its clients details and their daily use of mobile
phones. In the daily use the company keeps track of called minutes and SMS send by
the clients. This information is primarily used for creating bills. In addition, company
usesthis information to give several offers to its clients. Perform the following activities
for the company :
(a) Design and implement normalised relations/tables for the company. Your implementation should include the keys, constraints and validation checks.
(b) Enter 5-8 sets of meaningful data in each of the tables/relations. L0 (c)Design and implement five suitable queries/reports/forms for the company
Create a VB application to simulate the billing application for a "Fast Food Restaurant" like MC Donald's/Pizza but which sells various fast - food items. Also design the splash screen for this application. Design a user - friendly interface with appropriate controls.
Write an event procedure to generate multiplication - tables (from 1 to 10). Use a grid control to display them.
A publishing company keeps track of orders that have been received by it for various publications. The basic publication of company is the books. Each book has a single author. On the basis of orders, the books are sold to clients, book storesetc. The sales of a book is used to determine the royality (payment) for the author. You may assume here that sales of a book is same as the orders received for that book. Perform the following tasks for the company :
(a) Design and implement the normalised tables/ relations for the requirements given above. You should include the keys, constraints and validation checks in your implementation.
(b) Enter about 5-8 sets of meaningful data in each of the table/relations. (c)Design and implemerit five suitable queries/forms/reports for the company.
A medical agency keeps track of various Doctors and medical stores in a geographical area. It also keepstrack of various medicinessold by medical storeson the prescription of Doctors. One of the key concern of the agency is to determine, the popular medicine
brands. Perform the following tasks for the medical agency :
Design and implement the normalised tables/relations for the requirements as given above. You should include the keys, constraints and validation checks in your implementation.
(b) Enter about 5-8 sets of meaningful data in each of the table/rclation. (c)Design and implement five suitable queries/forms/reports for the database
A mobile phone company keepstrack of its clients details and their daily use of mobile phones. In the daily use the company keeps track of called minutes and SMS send by the clients. This information is primarily used for creating bills. In addition, company usesthis information to give several offers to its clients. Perform the following activities for the company :
(u) Design and implement normalised relations/tables for the company. Your implementation should include the keys, constraints and validation checks.
(b) Enter 5-8 sets of meaningful data in each of the tables/relations.
(c)Design and implement five suitable queries/reports/forms for the company.
A dental department keeps track of dentists and their patients. Each patient is given an appointment of a dentist s/he requests by a staff member. The staff makes sure that these appointments are duly entertained. (You may assume here that bills are not produced by this department.) The information generated in the dental department is used to find the availability of a dentists, regularity of patients etc. The department also takes a feedback from the patients.
Perform the following activities for the dental department :
Design and implement the normalised relations/tables for the requirements given above. Include keys, constraints including referential constraints and validation checks in your implementation.
Enter about 5 - 6 sets of meaningful data in each of the relation/table
(c) Design and implement five suitable queries/forms/reports for the department.
In patient department of a hospital maintains the following information about the patients admitted :
The patient details such as name, phone address, previous medical history etc. The present treatment details which include the medicines and the prescribed dosage to be given to the patients.
The reports of tests conducted on the patients from the date of admission.
The record of dosages of medicines along with time when they were administered to the patients.
Perform the following activities for the hospital as described above :
Design and implement the normalised relations/tables for the requirements given above. Include keys, constraints including referential constraints, and validation checks in your implementation.
Enter about 5 - 6 sets of meaningful data in each of the relation/table. (c)Design and implement five suitable queries/forms/reports for the hospital.
A property dealer maintains the details of the properties and clients as given below :
S/he maintains the list of properties for rent. The information in this regard
includes - property id, address, expected rent, owner contact phone.
In addition, s/he maintains the owner details including address, phone number, specific requirements, if any.
The client details include contact phone, address of client, maximum paying , capacity etc.
The visit of a client to a property is arranged by the dealer and all the information such as date of visit, property-id, etc. are recorded about the visit.
Perform the following activities for the property dealer :
Design and implement the normalised relations/tables for the requirements given above. Include keys, constraints including referential constraints and validation checks in your implementation.
Enter about 5 - 6 sets of meaningful data in each of the relation/table.
(c) Design and implement five suitable queries/forms/reports for the property dealer.
A computer maintenance company keeps the following details about the computers being maintained by them :
The configuration of computer which includes the CPU speed and make, size of RAM and type, DVD drive details if available and make of printer attached to that computer.
The list of licenced software that has been installed on a computer.
The list of users who can use the computers.
They also maintain the log of users who have used a computer.
Perform the following activities for the company :
Design and implement the normalised relations/tables for the requirements given above. Include keys, constraints including referential constraints and validation checks in your implementation.
Enter about 5 - 6 sets of meaningful data in each of the relation/table. 10
(c) Design and implement five suitable queries/forms/reports for hardware
maintenance company.
A travel agency keeps details of its customers using a database system. The travel agency arranges reservations for travel through bus, railways or air for specific destinations desired by the tourists who are its customers. The customers either are regular customers who are paid some discounts or general customers who may be given discount based on the amount of booking. Perform the following tasks for the travel agency :
Design and implement the normalised tables/relations for the requirements as given above. Your implementation should include the keys, constraints and validation checks.
Enter about 5-8 sets of meaningful data in each of the table/relation. 10
(c) Design and implement five suitable queries/forms/reports for the database.
A house building company have many projects across a city. Each project has a number of houses. The company has several customers. The company sells houses to the customer on monthly installments. A customer can take a maximum of one house in any one project of the company.
The montly installment can be paid by the customer in cash or by cheques. The company keeps track of installment defaulters and all the cheques.
Perform the following activities for the company :
Design and implement the normalised relations/tables for the requirements given above. Include keys, constraints including referential integrity constraints, and validation checks in your implementation.
Enter about 5 - 6 sets of meaningful data in each of the relation/ table. Design and implement five suitable queries/ forms/reports for the company.
A town planning office maintains the following information about the residents :
the basic information about the residents-like name, age, qualification, residential
address etc. Each resident is given a unique id.
it keeps track about the relationships among residents specifically head of the family and dependents. it also keeps track of assets of the residents specifically the house and cars owned
by a resident. in addition, it keeps track of work places of the working people in the town. This information may be used to determine the approximate distance one person need to travel for his/her work. Perform the following activities for the town planning office :
Design and implement the normalised relations/tables for the requirements given above. Include keys, constraints (including referential integrity constraints) and validation checks in your implementation.
Enter about 5 - 6 sets of meaningful data in each of the relation/table.
(c) Design and implement the following queries/reports/forms for the office :
A form to enter basic information of the residents.
A query to find the number/list of people travelling from "Maidan Garhi"
to "Railway Station" for work. (Assume "Maidan Garhi" is a residential
locality and "Railway Station" is a work place).
A query to find the number of people who own more than one car.
A list/report of head of the family and their dependents.
A form to enter the asset information of a resident.
A University has several departments (such as Computer Science, Information Technology, department of Basic Sciences etc.). Each department offer several courses.
Each course has credits associated with it. The number of credits of a course may vary
from 2 to 8. The students takes admission to a programme of a department. The
requirements of few programmes are defined in terms of number of credits as under :
Required Number of
Required credits from
Programme Department
Total
Code
Credits from Department other departments
Code
BCA
36
CS
60
96
BSc (IT)
IT
40
96
56
BSc
Basic Science
48
48
96
A student can register for any course of his/her choice, but she/he has to complete the requirements of a programme to complete the degree.
Perform the following tasks for the University :
Design and implement the normalised tables/relations for the requirements given above. Your implementation should include the keys, validation checks and constraints including referential integrity constraints.
Enter about 5 - 6 sets of meaningful data in each of the table/relation. 10 (c) Design and implement five suitable queries/forms/reports for the database.
A student record management system keeps track of the following :
The registration of student in a semester of BCA programme : A student is allowed to register for a semester, if she/he has registered for all the earlier semesters. For example, if a student is wanting to register for 4 th semester of BCA she/he should have duly registered for the 1 st , 2nd and 3rd semesters.
The fee paid for each registration.
The student basic data such as name, father's name, mother's name, address, telephone, email address, etc. is also maintained by the system.
Perform the following activities for the system as above :
Design and implement the normalised relations/tables for the requirements given above. Include keys, validation checks, constraints including referential integrity constraints in your implementation.
Enter about 5 - 6 sets of meaningful data in each of the relation/table. 10
(c) Design and implement the following queries/forms/reports for the database :
A form to enter student's basic data.
A form to enter student's registration data.
A query to find the registration details of a student along with her/his name
and address, given his/her enrolment number.
A query to find the details of the fee submitted by a student.
A report/list of students who have registered for 4 th semester for the current year and semester.
