Admission procedure in a University is as follows:
An advertisement is issued giving essential qualifications for computer science programmes , the last date for receipt of application, and fee to be enclosed with the application. A clerk in the Registrar’s office checks the received applications to see if mark sheet and fee are enclosed and sends valid applications to the concerned academic department. The department checks the application in detail and decides the applicants to be admitted, those to be put in the waiting list, and those to be rejected. Appropriate letters are sent to the Registrar’s office which intimates the applicant.
(a) Give DFDs corresponding to the above problem.
(b) Draw the Enhanced-ER diagram for the admission procedure
in university showing all
entities, relationship, aggregation, generalization, and specialization.
(c) Create the normalized tables till 5NF, highlighting, all
the different types of dependencies including Multi-valued and
join dependencies (if your design does not have any such dependency,
then you must explain the MVD and JD with the help of an example
situation related to admission procedure in the University.
Question 2:
(a) Draw the UML class diagram for the problem stated in Q1 above and relate it to database design as done in part (b).
(b) Create an embedded SQL query to find out the number of
applicants for MCA
Programme in the age group of 22-30 years.
(c) Create an embedded SQL query to find out the number of girl candidates for BCA Programme.
Question 3:
(a) Consider the following relations:
Faculty (F_id, F-name, F-designation) [The table contains
several tuples having f-id as primary key]
Patient (P_id, P_name, p_address, date of admission) [The table
contains several tuples and is stored on P_id]
Consultation date (C_date, F_id, P_id)
Write the following queries expressed in relational algebraic form:
(i) Find the details of the entire faculty whose designation
is a senior doctor and
have got consultation date in July 2007.
(ii) List the details of all patients who have got consultation
dates fixed between
15th August to Sept. 2007.
(b) Write a trigger that restricts allocating consultation date on weekend and holidays.
(c) Consider a constraint – The value of the age field of a patient coming to the hospital should not less than 15. Write an assertion for the above statement.
Question 4:
(a) Differentiate between OODBMS and Relational Database Management
System.
(b) Describe three main types of XML documents.
(c) What are triggers, cursors and Alerts? What are their requirements?