Design a database for a Cellphone Store. The system will allow customers to buy

Question

Design a database for a 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.

Assumptions (Please read carefully)

You can make the following assumptions in this project.

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.

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.

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).

The main feature of the system is to allow customers to search for phones, order cellphones, and check shipping status.

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).

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.

What needs to be completed for deliverable 1

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.

Explanation / Answer

create table customers(email varchar(30), password varchar(20), address varchar(30), contact number);

create table cellphone(ID varchar(10), brand varchar(20), model varchar(15), price number, os varchar(15), scr_size number, cam_reso varchar(20), network varchar(3), ram_size varchar(15), storage_size varchar(10), battery varchar(10), color varchar(10), stock number);

create table order(ord_date date, ID varchar(10), qty number, ship_mtd varchar(20), ship_date date, ship_stat varchar(10), tot_due number, pay_method varchar(10));

PL/SQL Block for feature 1

declare
   email customers.email%Type;
   pass customers.password%Type;
   add customers.address%Type;
   con customers.contact%Type;
begin

   insert into customers values(email, pass, add, con);
end;

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.