Assignment DES715 2
Gift Registry Portal (GRP) project
Project General Description
Business Functionality
GRP is an online portal system which will allow recipients (someone who wants to receive a gift) to register for various types of events. An event can be, for instance, a wedding, a birthday or a baby shower. Retailers provide a list of products to GRP. A recipient will create a list of gifts they would like to receive based on a retailer's product selection. A registry list can be composed of products from different retailers. This list is available online for the recipient's guests (customers) to view and if they wish to place an order. The created order is sent to the appropriate retailer based on the products on the wish list. The retailer can confirm or to reject the order. Therefore, more than one supplier order can be created to cover a given registry list purchase. For the confirmed order GRP produces an invoice and sends it by e-mail to a Customer. It is also a possible for a customer to order a prepackaged gift basket as an alternative to ordering a product from the wish list. This option is available to a customer directly, with no connection to a wish list. Prepackaged baskets are provided by a single retailer and they can be ordered and paid after confirmation by the customer. GRP provides a payment option of credit card for both recipient and customer. The recipient needs to pay a fee for the wish list service. Customers pay for their order in accordance with the invoice. GRP acts as a single contact point between recipient, customer and retailer in respect to all the operations except shipping. Shipping is arranged by retailer directly to the address provided. The owner of GRP wants to develop a computer system to automate the following operations:
1. Recipient's Subscription Page
Recipient subscribes for the service online and provides payment of a fixed fee by credit card. 2. Registry List creating and support. After the credit card is validated, the recipient's account will be activated and s/he gains access to retailer price lists to create a registry list. The registry list is stored and becomes accessible online for the event guests to determine what gifts are desirable and to show ongoing purchasing activity 3. Price List creating. Retailers submit price lists to GRP for the products they offer. Initially the price list is uploaded via FTP but then the retailer may do changes online to it. GRP supports a catalog of the products that consolidates all the retailer's products that can be sold through the portal. In the catalog the products are identified with SKU, however retailers use their proprietary product ID to create the price lists. The catalog provides detailed information about the product but not the product prices. Each retailer defines its own price for the products to be sold. Retailers may give discounts for some particular types of events for a specified period of time (for example a discount of 10% is given to wedding events for the period April 1 to May 30 by Retailer A) 4. Customer's application form. Customers fill in a form to obtain access to the registry lists of recipients. They are not supposed to pay for any GRP services; however they may become a customer of a retailer if they purchase products from that retailer.
DES715 Assignment 2 5. Purchase Order. Looking through the registry list the customer marks the items they wish to buy (typical shopping cart activity). When they have finished, a purchase order (PO) is created and sent to the appropriate retailer for confirmation. PO is to be stored in GRP database and registry list is to be updated with the status (ordered, paid but not shipped, shipped). 6. Invoices and payments. If the PO is confirmed, GRP creates Invoice and sends it to a Customer. Customer may pay by credit card or may send a check payment. All the payments come to GRP. Database is to support current state of all Invoice payments. GRP sends the payments to Retailers keeping a percent agreed for the service. 7. Prepackaged baskets. Prepackaged baskets have been offered for some particular type of event, for example a "new baby" event, or St. Patrick Day. Retailer selects the event and then, looking through price lists of him selects the products to be included. Total price has been calculated for him. Retailer can change the selection as long as the confirmation has not happened. After confirmation, the basket content has been stored in RDB. The project is to be developed in Windows 2000 environment. GRP database is to be designed and implemented in Relational Database Technology. Specific e-commerce components such as credit cards information storage and shopping cart are to be included as well. The application software design is to be based on COM and object oriented technologies.
Assignment 2 (40 marks). Due April 17, 2009 in class. Continuing with your same team as assignment 1, or with team different members (maximum 3 students), complete the following:
1. Create an object oriented data model (OODM) for Modules 1-7 from the Case Study. The model should show: (10 marks) ADT attributes only, with multiplicity. Relationships: For association, show ADTs based on direction (uni-directional or bidirectional). Show composition and inheritance using MS Visio icons. Aggregation can be shown as an association. Include comments to justify your choice of relationships and direction. 2. Convert your object oriented data model from question 1 into a Rational Rose model for Modules 5-7 from the Case Study. The model should show: (15 marks) Classes with attributes. Relationships (association, aggregation/composition, inheritance). Show relationship direction and multiplicity. Include comments to justify your choice of direction. 3. Create a normalized relational database model in MSVisio for Modules 1-7. Convert your Rose Model from step 2 to a relational structure and add it to your model from assignment #1 (you may change your assignment #1 model based on instructor feedback). Your model should be normalized to 3rd Normal Form and must include the following: (15 marks) All attributes. They must be atomic, single valued with no multi-valued attributes. Primary keys should appear in bold and foreign keys should be denoted with `(FK)' next to them. All relationships and multiplicity must be clearly shown. There should be no M:N relationships. 4. Submit assignment stapled or in a folder with the following information:
DES715 Assignment 2 A cover page showing course code and name, student names and student numbers. Clearly label the assignment as assignment 2. The hard copy of each of the models from steps 1 (MSVisio OODM), 2 (Rational Rose Class Diagram) and 3 (MSVisio Relational Schema).
Business Model Details (complimentary to those in Assignment 1) Business rules and assumptions: - Customer submits his application form for each particular Registry (unique combination Recipient Event) - When a Customer decides to purchase some gift listed in the Registry, Purchase Order (PO) is created. The PO may contain a few lines for different products however all of them must be from the same retailer and must relate to the same Registry - Purchase Order has a unique ID for each particular combination of Registry Retailer Customer - Invoice is created for each PO and for only one PO Data Samples related to the Assignment 2 tasks are provided below.
DES715 Assignment 2 Sample Customer Application Form
Customer personal information
First Name ______________ Last Name _______________ Address Line1 __________________ Address Line 2 _____________________ City __________________ Province _________________ Post Code ______________ Tel Number Day _____________ E-mail _______________________
Recipient ID _________________ Event information
Tel Number Night ___________________
Event Type __________________ Event Date ___________________
Sample Report Registry List Recipient ID Recipient Name Event ROB1 Robert Smith Wedding Number of items requested 2 2 Price 50.45 80.35 Total price 100.90 80.35 181.25
Item Product_ID Retailer Description Tennis Set Sport shoes SFR2301 DRN3351 B B
Recipient ID ANN1 Recipient Name Anne Ramoras Event Birthday Item Product_ID Retailer Description Camera CD Player Tennis Set CMS231 CDD4512 SFR2301 A A B Number of items requested 1 1 1 Price 300.99 299.00 50.45 Total price 300.99 299.00 50.45 650.44
DES715 Assignment 2 Recipient ID ROB2 Recipient Name Robert Smith Event Valentine Day Item Product_ID Retailer Description Camera CMS231 A Number of items requested 1 Price 300.99 Total price 300.99
DES715 Assignment 2 Sample Purchase Order Recipient Name Robert Smith Event Wedding Purchase Order # 12345 Customer Name Bill Custom Retailer A QTY 1 Discount Yes (10%) Price 180.00 Shipping 10.00
Title Sony Sega
SKU BRP123
Unit Price 199.99
Order Total: 190.00 Applicable Taxes: GST: 13.30 PST: 14.40 Grand Total: 217.70 Sample Purchase Order Recipient Name Robert Smith Event Wedding Purchase Order # 12377 Customer Name Jane Small Retailer B QTY 1 1 Discount Yes (10%) Yes (10%) Price 210.65 278.99 Shipping 10.00
Title Leather Jacket Leather Jacket
SKU DDP123 DDP124
Unit Price 233.99 309.95
Order Total: 499.64 Applicable Taxes: GST: 34.97 PST: 39.97 Grand Total: 574.58
DES715 Assignment 2 Sample Invoice Invoice Number I-12377 Purchase Order # 12377 Customer Name Jane Small Retailer B Banking Account 777778888899999 Bank - GREAT OPPORTUNITY Title Leather Jacket Leather Jacket SKU DDP123 DDP124 Unit Price 233.99 309.95 QTY 1 1 Discount Yes (10%) Yes (10%) Price 210.65 278.99 Shipping 10.00
Total: 499.64 Applicable Taxes: GST: 34.97 PST: 39.97 Grand Total: 574.58 Pre-packaged basket Title Baby shower Retailer - A Title Baby cart Baby blanket Baby toys Grand Total Number of items included 1 2 3 Item price 500 20 10 Total per line 500 40 30 570