Q.
Question 1:
The material distribution division of a University like IGNOU maintains the inventory of its printed material stock. The printed material may be of two forms − (1) material that is applicable for a specific time period such as the News Letters, Assignments, Programme Guides, Prospectus etc. and (2) printed course material shelf life of which may be of few years subject to a maximum of 5 years. The materials are despatched to the students based on the student’s enrolment to various subjects. Programme guide is sent to the students only once and assignments are sent for those subjects that have been opted by the students. A student may enrol only in one programme at a time.
(a) Draw the EER diagram for the University warehouse showing all related entities, relationship, aggregation, generalisation and specialisation. (5 Marks)
(b) Create the normalised tables till 5 NF, 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 any University Warehouse). (5 Marks)
(c) Draw the class diagram for the warehouse of the University and relate it to database design as done in part (b). (5 Marks)
(d) Create an embedded SQL query to find out the list of all the publications of the University under various heads like Newsletter, Assignment, Programme Guide, Course material subjects etc. (2 Marks)
(e) List the contents of the catalogue considering that the system has been developed using a commercial database management system? (3 Marks)
Question 2
(a) Consider the following relations: (6 Marks)
Supplier (su_id, su_name, su_address, su_status)
Part (pa_ id, pa_name, pa_reorderlevel, pa_stock)
Supplyofpart (su_id, pa_id, dateofsupply, quantityofsupply, su_price)
You may assuming that su_status is either “Preferred” or NULL.
Write the SQL command(s) to find the total number of each part supplied by the each of the preferred supplier.
Now, assume that there are 200 supplier records, 500 part records and 100000 Supplyofpart records. Also assume that in a block of data 5 supplier records or 10 part records or 20 supplyofpart records can be accommodated. Find the minimum cost for the query given above if Block Nested- Loop join is performed. Also find the query evaluation plan for the given query. Make and state assumptions, if any.
(b) Consider the following concurrent transactions in a Banking situation:
• Updating the account balance of an account due to a withdrawal
• Updating the account balance of an account due to a deposit
• Updating all the account balances with the amount of bank interests/charges etc.
• Reading of account balance of a person and making the statement for it.
Which of the protocols (locking or timestamp based) will be preferred by you for this system? Justify your answer. Explain the protocol that has been selected by you. Also suggest a logging scheme for these transactions.
(5 Marks)
(c) Consider the relations given in part (a) of this question. Design two user views – Supplier and the System Manager. The Supplier is allowed to view the data that only pertains to its supplies. The System Manager can read all the data and change information about the suppliers and parts. Design the suitable access control and use SQL to grant the desired access rights to the users. Make suitable assumptions, if any. (3 Marks)
(d) Assume that the transactions as given in part (b) of this question are part of the banking database that has been implemented as a distributed database. Explain how will the system commit a transaction? Also explain the process of detection of deadlock for such a system. Make and state suitable assumptions about the data distribution. (6 Marks)
Question 3:
(a) A University like IGNOU maintains a list of student project guides and the students working under them for the projects of MCA programmes. The guide has a basic qualification and work experience. A guide is allowed to take a maximum of 5 students in a semester. Design a suitable Object oriented database management system with the help of ODL. Also write a query that finds the number of students working under a guide in the present semester. (5 Marks)
(b) Create a semi-structured data using XML for defining the details of teachers of a University (assume a maximum of 5 teachers). You may include the fields – ID, Name, Qualifications starting from graduation, work experience, and published materials. Create a suitable DTD for this document. (5 Marks)
(c) Consider a data warehouse of the students of a University that contains the following schema - Enrolment (Year, Regional Centre, Programme, Number of students). Create the star schema for the data warehouse with some sample data. Assuming a suitable database design of the student database, identify a suitable ETL process (along with queries, if required) to create the data for the data warehouse. (5 Marks)
(d) What is data mining? Explain the usefulness of classification, clustering and association rule mining with the help of an example each in the context of data mining. Consider the following structure of student data – Programme (MCA, MBA, BED, MSC), Age (21-25, 26-30, 31-35, 36-40), Overall Grade (A, B, C, D) and Study centre attendance (Regular, Not Regular). Create a sample training data for the structure above and use this data to create a decision tree using ID3 algorithm for classification. Make suitable assumptions. (5 Marks)
Question 4:
(a) Explain the following with appropriate examples. (10 Marks)
i) Knowledge Database
ii) Mobile Database
iii) Digital Libraries
iv) Spatial Database
(b) Explain the following in the context of PostgreSQL (5 Marks)
i) Indexes
ii) Repeatable Read
iii) ctid and its use
iv) Programming interface
v) Backend server processes
(c) Explain the following in the context of Oracle (5 Marks)
i) System Global Area
ii) Control files
iii) Clusters
iv) Recovery Manager
v) Materialised views
