| 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)
|