-User_Table

1. user_id: number(8), primary key

2. user_name: varchar2(80), not null

3. city: varchar2(80)

- User_Phone_#

1. user_id: number(8)

2. phone_#: number(8)

Primary key(user_id, phone_#)

-Customer

1. cid: number(8), primary key

2. credit_card_#: number(8)

3. birthday: date

(For your information, you can use TO_DATE to insert date to corresponding attribute. For example, TO_DATE('2019/01/01', 'yyyy/mm/dd')

-Merchant

1. mid: number(8), primary key

2. account_#: number(8)

-Order_Table

1. order_id:number(8), primary key

2. order_date: date

3. price: number(15), not null

4. cid: number(8), not null

5. mid: number(8)

6. discount_percent: number(3) , check(discount_percent between 0 and 100)

-Bill

1. order_id : number(8)

2. bill_id :number(8)

3. amount_of_payment: number(8)

4. payment_date: date

5. delivery_type: varchar2(20), not null, check (delivery_type in ('Physical', 'Non_Physical'))

6. payment_method: varchar2(30)

primary key(order_id, bill_id)

-Order_Item_List

1. order_id : number(8)

2. pid :number(8)

primary key(order_id, pid)

-Physical_Delivery

1. order_id: number(8)

2. bill_id: number(8)

3. delivery_id: number(8)

4. deliveryman: varchar2(80)

5. date_of_dispatching: date

6. date_of_confirmation: date

primary key(order_id, bill_id, delivery_id)

-Physical_Delivery_Item_List

1. order_id: number(8)

2. bill_id: number(8)

3. delivery_id:number(8)

4. pid:number(8)

primary key(order_id, bill_id, delivery_id, pid)

-Item

1. pid: number(8), primary key

2. item_description: varchar2(80), not null

3. price: number(15)

4. item_category: varchar2(80)

5. mid: number(8), not null

Part 2. Write SQL Queries (60%)

Part 2. Write the following SQL queries. If your scripts return error(s) or incorrect output, you will get zero mark for corresponding queries. You can write SQL scripts to insert some artificial data into tables for your own testing. The insert scripts are not graded and are not required to submit.

1. Find user_id and user_name who are both customer and merchant. (5%)

2. Find all pid of items that were bought by the customer with cid = 2 and the delivery type is 'Physical' and sort pid in an ascending order. (5%)

3. Find the top 2 cid of customers in terms of the longest waiting time with ties (i.e. the difference between the date of confirmation in Physical Delivery table and the payment date in Bill table) and the corresponding maximum waiting time. The output can be more than two rows because there can be ties for the 1^stor 2^ndplace. (15%)

Hints: You have to calculate maxium_waiting_time for each cutomer and you can use fetch first 2 rows with ties after order by maximum_wating_time desc to get the top 2 records with ties.

4. Find the pid of item that was bought (i.e. after payment) for the largest number of times by all customers. (10%)

5. Find the credit card numbers and corresponding sum of payment of top 2 customers in terms of the amount of payment in the bill they made. (10%)

Hints: You can use fetch first 2 rows with ties after sorting in a descending order.

6. Find the delivery man (or men) who delivered items to the customer with name ='A' and sort the delivery man (or men) alphabetically. (15%)