A nursery wants to keep track of all its products, including plants, fountains, garden hardware (wheelbarrow, shovels
Question:
A nursery wants to keep track of all its products, including plants, fountains, garden hardware (wheelbarrow, shovels etc) and also soil and sand which they sell. They buy all stock from the wholesalers. The management wants to know which staff members have been selling what, and from which wholesaler the products were purchased. There are also times when a customer returns a product for a refund, and such information should be available in the system. The nursery also delivers some items to customers, and they would like to integrate the booking process with the purchasing process. The nursery requires to lodge a business activity for each quarter to the Tax authority (ATO). If there is any inconsistencies or disputes ATO seek clarification from the nursery.
Based on the above scenario
a. Identify all the requirements and categorize them into functional and non-functional.
******b. Draw Context Level and Diagram 0 DFDs. *********
Note : It is important that you state any assumptions you made to justify your model.
for your information :
1. table: Product
ProductId(Primary Key),
ProductType(plants, fountains, garden hardware and Soil NOT NULL),
ProductName(wheelbarrow, shovels etc NOTNULL),
Description
2. table: ProductSale
ProductSaleId(Primary Key),
ProductId(Foreign Key stating which product),
SaleType(Buy/Sell),
EmployeeId (If SaleType is Buy, then it will be wholesalerId or if it is sale StaffMemberId),
ProductPrice,
TransactionDate(For Audit purpose)
Quantity
3. table: Return
ReturnId(Primary Key),
ProductId(Product which is returned; whenever needs to find available products a query of products brought - products sold from ProductSale table + Returns accepted will give remaining stock).
RefundAmount,
RefundStatus(Eg: Initiated, Settled and InProgress),
Quantity(Size of returned product)
4. table: Booking
OrderId(Primary Key)
ProductId(Product which is ordered),
Quantity(Information of required product quantity),
Price(Amount)
Status(OrderPlaced(Means purchase table has been updated with purchase), InDelivery, Delivered)
DeliveryCharges(Amount as this is an extra charge)
CustomerId(For the column EmployeeId in table ProductSale)
Fundamentals of Database Systems
ISBN: 978-0136086208
6th edition
Authors: Ramez Elmasri, Shamkant Navathe