A Central University runs from its main campus and a number of associated colleges. The University has several 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). The structure of each programme is approved centrally. A programme may be offered through the main campus or associated college. The students are registered centrally in a programme and college of his/her choice. The fee is also paid centrally yearly. The final data of admission can be obtained by the colleges through a query to the central database. The University has a centralized examination system. It conducts the examination of the students in various courses of various programmes. For simplicity you may assume that a programme of the University only have three compulsory courses in a year. Perform the following tasks for the University. Please make and state assumptions, if any.
Question 1: List the entities, their attributes and relationships for the description and make an ER-diagram for the University. You may use the concept of keys, aggregation, generalisation, cardinality etc. in a proper way. (10 Marks)
Question 2: Design the suitable RDBMS tables for the ER-diagram so created in question 1. The database design should include keys, foreign keys, constraints and referential integrity constraints. (10 Marks)
Question 3: Implement the database design that you have created in question 2 using a RDBMS – our advice is that you choose either MySQL or MS ACCESS to do so. (10 Marks)
Question 4: Create the following data entry form with suitable 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 should 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 should be noted that the examiners are sent printed examination sheet called mark list for each subject. The 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. (10 Marks)
Question 5: Enter at least 4 sets of records in each table. Enter marks directly in the tables except for the data that can be entered using the data entry form created in question 3. (10 Marks)
Question 6: Create the following reports for the database you have created. The reports should have proper headings and page numbers and should include totals, if needed.
a) Create the list of students of a college. This list should be sorted in the order of programme and enrolment numbers in that programme.
b) Create an attendance list for a subject in a college.
c) Print the mark list of a subject that needs to be verified.
d) Create the result card for a student. (10 Marks)
Question 7: Write and run the following SQL queries for your database:
a) Find the details of the programme having the maximum number of students.
b) Find the programme that has the maximum fee. Also find the number of students enrolled in this programme.
c) Find the student who have either failed the subject Database Management System at least once or has not given the examination for this subject. You may assume that this subject is taught in BCA 2nd Year and MCA 1st year.
d) Find the student who has topped in BCA 2nd Year examination.
e) Find the number of programmes run in each college. (20 Marks)
Question 8: Design two views for the database that you have designed and implemented. One view should be for the University Vice Chancellor who is interested in knowing about the performance of students in various programme and colleges. The other view is for a student who can view only his/her information. Identify on what tables and what fields these two will be allowed to have access. Implement these views or write equivalent queries for the view defining expression using SQL. (20 Marks)