CSE 581 Introduction to Database Management Systems Fall 2021 1 Project 2: A Target Competitor! Your job is to create a shopping database for a Target competitor, which has in store and online sales. An initial list of suggested information to be included in this database is listed below. But more shall be added as you deem necessary. Several business considerations shall also be considered such as each online order has a single shipping address and can contain multiple products from one or more warehouses. Multiple warehouses may have the same product and the originating warehouse for a shipment is chosen based on the shipping time and cost, which can be determined by the zip codes of the originating warehouse and the shipping address. Suppliers may also have multiple products in various warehouses. Stores can also place orders to deliver products from warehouses to stores for in store purchases. Product quantity in stores and warehouses need also to be updated based on transactions. Customers: Customer ID, name, username, email, phone numbers (home, cell, business), address book (several shipping/billing addresses), credit card information Online orders, in-store purchases, returns Wishlist Reviews (date, product, score, text) Products: Product ID, product name, description, price, customer ratings (text, score, date) Suppliers: Supplier ID, name, address, phone, fax, email, webpage Products information (product, number of products available, unit price) Warehouses: Warehouse ID, address, phone, fax, email Stored products (product, number in stock, number on the way, number in return) Stores: Store ID, address, phone, fax, email, webpage Products (product, price, quantity) Online Orders/Returns: Order ID, status (ready, shipped, delivered, returned), order date, order items (product, quantity, unit price, total price) Shipping service (USPS, FedEx, UPS), shipping address Shipping fare (depending on the address between the warehouse and the shipping address) Expected shipping date, actual shipping date, any shipping information CSE 581 Introduction to Database Management Systems Fall 2021 2 Technical Requirements: A. Design 1) Please determine the information to store (please see the next page for some suggestions), 2) E/R diagram (please use draw.io, Visual Paradigm, Vertabelo, MS Visio, or any similar tool), 3) business logics and transactions to consider, and how to incorporate them into the design, 4) and business reports to consider. B. Implementation 1) Please use descriptive naming for all the objects and items in your database using CamelCase naming standard, 2) normalize your design into its 3rd normal form, 3) create your schema and tables with columns, primary keys, foreign keys, proper data types, nullabilities, and necessary relationships and constraints, 4) determine potential integrity and security issues, 5) create 4 views, 4 stored procedures, 4 user defined functions, 4 triggers, and 4 transactions, 6) and include 4 scripts to create users with various security levels, passwords, and roles. Please note that you are expected to code by yourself! No auto-generated SQL is accepted! C. Testing 1) Please populate your database with meaningful test data (for about 4 customers, 4 stores, etc.), 2) And demonstrate your database’s reliability and logic through several scenarios and transactions to test your views, stored procedures, functions, scripts, and transactions. Please use nontrivial scenarios and transactions! D. Report Please include the following sections in a single PDF or DOC file: a) Cover page: a descriptive title, your information, a short abstract. b) Design: your introduction, design considerations, E/R diagram, database objects. c) Implementation: SQL source codes with your descriptive comments. d) Testing: testcases with your descriptive comments. e) Conclusions: your final analysis and remarks. f) Appendix: screenshots from SQL Server for codes, testing, etc. Please review the grading rubric for the project report on Blackboard! All reports that adhere to basic standards of grammar and spelling, and section/page formatting will have a base score of 15 points.
