A departmental store maintains inventory of goods about its sections. Each section can sell a number of items. An item sold by one section may be part of other section also. The departmental store has a number of registered buyers. Some of the buyers, however, may not be registered. A buyer purchases one or more items and a number of such items. A buyer gets a bill for the purchases s/he made. A buyer can purchase many times in a month. A buyer may buy several times even in a single day. A buyer who had made purchase of more than Rs10,000/- in a month, is given a status of preferred buyer. The preferred buyer is given a discount of 5% on all purchases on all the purchases made in the next month. The store acquires all the items from a stockiest. Each item has an item code. All acquisition of items are recorded. The cost price of an item of a different acquisition may be different. The selling price of an item is decided by the cost price of that item, handling overheads and profit margin. For all the items the store presently uses them as fixed quantity.
Perform the following tasks for the store. Please make and state assumptions, if any.
1) List the entities, their attributes and relationships for the description and make an ER-diagram for the Store. You may use the concept of keys, aggregation, generalisation, cardinality etc. in a proper way.
2) Design the suitable RDBMS tables for the ER-diagram so created in question 1. The database design should include keys, foreign keys, constraints and referential integrity constraints.
3) Implement the database design that you have created in question 2 using a RDBMS – our advice is that you choose either MySQL or MS ACCESS to do so.
4) Create the following data entry forms with suitable checks for the database so created.
a) Create a form to enter all the information about the items being purchased by a buyer on a particular sale.
b) Create a form to register a new buyer.
5) Enter at least 4 sets of records in each table. Enter marks directly in the tables except for the data that can be entered using the data entry form created in question 3
6) Create the following reports for the database you have created. The reports should have proper headings and page numbers and should include totals, if needed.
a) Create bill of a purchase.
b) Create a price list of items.
c) Create the list of preferred buyers.
d) Create the detailed list of purchases made by a single buyer.
7) Write and run the following SQL queries for your database:
a) Find the details of the buyers who have made purchases of more than 15,000 in a month..
b) Find the price of the costliest item and the names of all the buyers who have bought it.
c) Find the item details of the item that has been sold the most.
d) Find the difference in number of average buyers on weekends and the number of average buyers on weekdays.
e) Find the stockiest form whom maximum number of items are acquired
8) Design two views for the database that you have designed and implemented. One view should be for the Manager of the Departmental Store who is interested in knowing about the performance (quantity sold) of various items in various sections. The other view is for a buyer who can view his/her purchases made from the stores. Identify on what tables and what fields these two will be allowed to have access. Implement these views or write equivalent queries for the view defining expression using SQL.
Tricky J!!!
No comments:
Post a Comment