Question 1:
(a) Consider the following relations in the context of a retail
shop:
Customer (C_code, C_Name, C_Phone)
Product (P_Code, P_Desc, P_Price)
Call (Ca-id, Ca_Code, P_Code, Ca_Result, Ca_Date)
Write the appropriate SQL commands for the following:
(i) Find the name of customers to whom a call has been made.
(ii) Find the total number of calls made to the customer whose name
is
‘Smith’.
(iii) List C_Name and P_Desc of the calls made today.
(iv) Find the product details of those products whose price is above
Rs.5000/-
(b) A university maintains database table about the Faculty and the
Students.
(7 Marks)
FACULTY {f_id, f_name, subject_id)
STUDENT (S_id, S_name, S_course, S-dob)
MARKS (S_id, subject_id, marks)
Constraint: One faculty teaches only one subject.
(i) Identify suitable access rights for the faculty, students, and
database administrator
(ii) Create the access matrix for (i) above.
(iii) Write the query modification statement for the students.
Question 2:
(a) Explain the three problems of concurrent transaction in RDBMS with suitable examples.
(b) What is a timestamp? How does the system generate timestamps? Explain the timestamp ordering protocol for concurrency control.
(c) Explain the different time components involved in accessing a
disk block.
(5 Marks)
(d) “The decomposition of a relation into BCNF is always dependency
preserving.” Is this statement true? Give reasons for your answer.
Question 3:
Prepare an E-R diagram for your study center showing all the entities, the associations like the aggregation, specialization and generalization. Assumptions can be made wherever necessary.
Question 4:
Discuss the basic techniques to implement the recovery mechanism
in database system.
Question 5:
Describe the following with one example each.
(i) Serialibililty
(ii) Secondary index and B-tree
(iii) Multivalued dependency
(iv) Authorization Grant Tree
(v) Deadlock
CS-15 Relational Database Management Systems
Assignment Code : MCA (6)-15/Project/2007
Note : This is a Project. Answer all the questions. You may use illustrations
and diagrams to enhance explanations.
Question 1: XYZ Company is a Car production company. It has four factories and many distribution outlets spread across the country. The company keeps online records of the sales of its cars. It also creates batched Production schedules on the basis of the sales of the car. The car production is done at a nearest factory so that the cost of transfer of cars to distribution centre is minimized. Each factory also maintains its inventory. The company maintains a distributed database management system keeping all the above information. The following information may be stored in the Distribution and Inventory Management System of the Company:
(Please note that the following description may lead to un-normalized relations, normalize them wherever required. You may also add more tables as per your analysis.)
| Data store Name | Description |
| Distribution Outlets | It includes outlet code, outlet name, outlet address, list of car models, sales made for each model etc. |
| Factory | It may be keeping information about each car inventory items (Model wise). It includes item code, item name, model in which this item is used, quantity used in a single Car unit, and quantity in stock, time taken to produce a batch of 100 cars, etc. This information is stored factory wise. Each factory will have separate minimum stock and reorder level based on its capacity. |
| Batched Order | It includes details on overall sales of Cars at various outlets, all such sales are put together to generate a consolidated orders for factories. On the basis of such orders the factories make Cars. |
Assume that only following two applications exists:
(a) Keeping track of status of Inventory so that the delays in production of Cars can be minimised. Proper reorder levels should be kept so that a given order of cars is completed with full assembly load in desired time.
(b) It evaluates the performance of each distribution outlet of the
Company.
Design a distributed database assuming that at present the company
has only 10 outlets. Also assume statistics, which justifies your
design.
Your design should include:
(i) The global schema, fragmentation schema and allocation schema.
(ii) SQL commands/ application code for above queries/applications.
(iii) How the response for application 1 and 2 will be generated?
Assuming these are global queries, explain how various fragments will
be combined to generate the query response.
(iv) Implement the database at least using a centralized database
management system (make suitable adjustments in your design).