Design a database for a fictitious xyz-online cellphone

Question

Your assignment is to design a database for a fictitious XYZ-Online Cellphone Store. The system will allow customers to buy unlocked cellphones directly online (no contract is required). You will design the database, insert some sample data, and implement a set of required features. Each feature will be implemented as one Oracle PL/SQL procedure (you can create sub-procedures that will be called by the main procedure for that feature). You do NOT need to write a graphic user interface. You also need to provide statements to execute your procedures. The instructor and TA will run the statements you provided to test your program.  

You can make the following assumptions in this project.

1. The system needs to store data about phones, customers (you can assume that each customer needs to register with the system), customer's orders, and shipping methods.
2. A customer needs to register to use the system. The username will be the customer's email address and the customer also needs to provide a password. The customer also needs to provide personal information such as address and contact phone numbers.
3. The system needs to store the following information about a cellphone: brand (e.g., Apple, Samsung), model (e.g., iPhone 6), price, operating system (Android, iOS, Windows), screen size, camera resolution, network (2G, 3G, 4G), RAM size, storage size, battery (talk time), color, and the number of this type of phones in stock. Each type of cellphone also has a product ID as unique identifier (this ID is for the same type of phones, not IMEI).
4. The main feature of the system is to allow customers to search for phones, order cellphones, and check shipping status.
5. Each order contains order date, product ID of the phone in the order, quantity, shipping method, shipping date, shipping status (not shipped yet, shipped, delivered), total due, and payment method (including credit card number and expiration date).
6. The shipping mode includes regular and express. The cost of regular shipping is $5 and the cost of express shipping is $20.

Features:

1. New customer registration: create a new customer (with name, email address as user name, password, home address, phone number). The system needs to check whether the email address exists in the database. If so (the customer already registered), the procedure will tell the customer he or she has already registered. Otherwise the system needs to create a new customer in the database and store provided information. The procedure needs to print out a message indicating the result (registration is successful, or registration fails for what reason).

2. Allow a customer to search for cellphones in a given price range. The input is the price range and the procedure prints out a list of cellphones within the price range and also displays brand, model, price, operating system, screen size, camera resolution, network, RAM size, storage size, battery, and color, and the number of that type of phones in stock.

3. Allow a customer to place an order for a cell phone. The input is the Product ID of the phone, quantity, shipping method, credit card number and expiration date. The procedure will first check whether the product ID exists and whether there is enough in stock (i.e., quantity <= number in stock). If not, please print out that there is not enough in stock and return. Otherwise, the procedure creates a new order and computes total due, which equals (price of phone*quantity + shipping cost)*1.06 (assuming 6% sales tax). Each order will have a unique order ID. Please print out the order date (you can use sysdate), order ID, model of the phone being ordered, quantity, shipping mode, and total due. Please also update the number of this type of phones in stock accordingly (by subtracting the order quantity from the current number in stock).

4. Update the shipping status of an order. The input is the order ID and new shipping status. Please also update the shipping date if the shipping status is updated from 'not shipped yet' to 'shipped' (you can use sysdate as shipping date).

5. Allow a customer to check the shipping status of an order. The input is the order ID and the procedure prints out the shipping status and shipping date (if available). Please handle the case when the order ID is invalid.

There will be 3 deliverables:

Deliverable 1- Project Management Schedule.

•  
  • Include a timeline showing each phase of your project with its tasks and time duration, for the entire effort.
  • Tasks should include system design, populating tables, writing code, testing and debugging code, creating demo script, writing documentation, etc. Smaller milestones shall be set for deliverable 2 and 3.
  • This deliverable will be graded based on whether these items are included and whether the schedule is reasonable (e.g., enough time is left for testing and debugging).

 Deliverable 2- Design Document which includes the following:

•  
  • ER diagram of the database. You don’t have to follow exact notations of ER diagram, but need to show tables, columns, primary keys, and foreign key links.
  • SQL statements to create database tables and to insert some sample data (at least 5 rows per table).
  • Specification for each required feature. The specification should include a description of input parameters and output (usually screen outputs or changes to some tables). You don’t need to implement any of these features at this point.