Answer all the problems.
Central University runs from its main campus and a number of associated colleges. University has a number of programmes ranging from Diploma (1 year duration), Bachelor’s degree (3 or 4 year’s duration), Post Graduation (2 to 3 years duration) and research degree programmes (2-3 years duration). Structure of each programme is approved centrally. A programme might be offered through main campus or associated college. Students are registered centrally in the programme and college of his/her choice. Fee is also paid centrally yearly. The final data of admission could be obtained by colleges through a query to central database. University has a centralized examination system. It conducts examination of the students in different courses of various programmes. For simplicity you might suppose that the programme of the University only have three compulsory courses in a year. Do the following tasks for the University. Make and state assumptions, if any.
problem1)
Name the entities, their attributes and relationships for description and make the ER-diagram for the University. You might use the concept of keys, aggregation, generalisation, cardinality etc. in a appropriate way.
problem2)
Design the appropriate RDBMS tables for the ER-diagram so created in problem 1. Database design must include keys, foreign keys, constraints and referential integrity constraints.
problem3)
Implement database design which you have created in problem 2 using a RDBMS – our advice is that you select either MySQL or MS ACCESS to do so.
problem4)
Create the following data entry form with appropriate checks for the database so created.
a) Form for Entry of student information such as enrolment number, name, father’s name, date of birth, programme code (it must be from a list of valid programmes), year of programme, and draft number (for fee).
b) Form for entry of examination results of a course submitted by an examiner. It must be noted that examiners are sent printed examination sheet called mark list for each subject. Mark list contains printed enrolment number and name of the students and a blank column for entering the marks. A mark list is sorted in the order of enrolment numbers.
problem5)
Enter at least 4 sets of records in each table. Enter marks directly in the tables except for the data which could be entered using the data entry form created in problem 3.
problem6)
Create the following reports for the database you have created. The reports must have proper headings and page numbers and must include totals, if required.
a) Create list of students of a college. This list must be sorted in the order of programme and enrolment numbers in that programme.
b) Create the attendance list for a subject in a college.
c) Print mark list of a subject that requires to be verified.
d) Create result card for a student.
problem7)
prepare and run the following SQL queries for your database:
a) Determine the details of the programme having maximum number of students.
b) Determine the programme which has the maximum fee. Also find the number of students enrolled in this programme.
c) Determine the student who have either failed the subject Database Management System at least once or has not given the examination for this subject. You might suppose that this subject is taught in BCA 2nd Year and MCA 1st year.
d) Determine the student who has topped in BCA 2nd Year examination.
e) Determine the number of programmes run in each college.
problem8)
Design two views for the database which you have designed and implemented. One view must be for the University Vice Chancellor who is interested in knowing about performance of students in different programme and colleges. The other view is for a student who could view only his/her information. Identify on what tables and what fields these two would be allowed to have access. Implement these views or prepare equivalent queries for the view defining expression using SQL.