Course Code  CS-15
Course Title Relational Database Management System
Assignment Number  MCA(6)-15/project/03
Maximum Marks  15
Last Date of Submission   31st October, 2003

This is Project Assignment consisting of one problem. Each subsection carries equal weightage. You may use illustrations and diagrams to enhance explanations. Please go through the guidelines regarding assignment given in the Starter kit for the format of presentation.

Question 1:  A departmental store has many braches in a city. The store manages its inventory using a distributed database management system, however, all the purchases are made centrally from a Procurement Office. The following information may be stored in the Inventory Management System of the store: (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.)
Table Name Description
Store Brach  It includes branch code, branch name, branch address, list of items being sold by a branch
Inventory   It may be keeping information about each item code, item name, quantity in stock etc. This information is stored branch wise.  Each branch will have separate minimum stock and reorder level.
Purchase Order It includes details on various purchase orders being given from the central locations. The goods are received centrally checked for quality and distributed to various stores as per the requirements.

Assume that only following two applications exist

(a)     Keeping    track of requirements of various branches. The application automatically cheeks the inventory of
     all the branches, in case the quantity in stock at a branch falls below the minimum stock the order equivalent to reorder level is generated. Such data is collected from all branches, collated and Purchase Orders are issued. Such activities are made on weekly basis.

(b)     It evaluates the performance of each branch of the store and defines and redefines the minimum stock level and reorder quantity.

Design  a distributed database assuming that the store presently have four branches. 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)