Question

Create first draft for online shopping database (SQL):
Things to do:

Identify the entities

Identify the attributes

Constraints

Relations

First Normal form

Second Normal Form

Third Normal Form

Test Query to:

Add a new item as a seller

Query for total number of sold item in past month

Query for total income in US DOLLARS

Answer 1

Background explanation:

Database:

A database is a coherent collection of data with some ingerent meaning, designed, built and populated woth data for a specific purpose. In the given case, it is an online shopping database.

Database Management System:

A Database Management System or DBMS is the software system that allows users to define, create and maintain a database and provides controlled access to the data. In the given case, the control to the database is done by SQL.

SQL:

SQL which stands for Structured Query Language, is a domain specific language used in programming and designed to manage data held in a relational data stream management system.

First draft for the online shopping database:

The initial draft for an online shopping database using SQL is designed through the following steps while emphasising on all the detailed requirements as given in the question, however it is worthwhile to mention that this might not be the final and complete model for the entire online shopping DBMS:

A. Identifying the entities:

The major entities of online shopping DBMS are identified as below:

1. The Market (M)

2. The Products (P)

3. The customers or Buyers (B)

4. The suppliers or Sellers (S)

5. The Shopping Cart (C)

6. The Order (O)

7. The Payment (P)

8. The Delivery (D)

9. The customer review or Feedback (F)

10. The discount or Rebate (R)

It is ssumed that the delivery of the product is arranged by the seller directly to the buyer through a courier of their choice and so courier is not part of this module.

Identifying the attributes:

The major attributes for each of the above are identified as follows:

1. The Market (M): the buyer id, seller id, products id, rebate id, shopping cart id, order id, payment id, delivery id

2. The Products (P): product id, name, price, rebate, seller, buyer (optional)

3. The customers or Buyers (B): buyer id,login password, name, address, zip/pin code, email id, mobile no

4. The suppliers or Sellers (S):seller id,login password, name, address, zip/pin code, email id, mobile no

5. The Shopping Cart (C):cart id, buyer id, product id, price, rebate, date, time

6. The Order (O): order id, buyer id, cart id, product id, price, rebate, date, time

7. The Payment (P): payment id, order id, buyer id, seller id, date, time

8. The Delivery (D): delivery id, order id, buyer id, seller id, date, time

9. The customer review or Feedback (F):feedback id, buyer id, product id, comment, date, time

10. The discount or Rebate (R): rebate id, product id, seller id, buyer id  (optional)

The Constraints:

The constraints of the dbms design are listed below:

1. All the attributes listed above are identified by their unique integer identifier or id which can not be null or void

2. The buyers, sellers, products and rebate have to be registered in the market before they start to operate

3.The delivery of the product is arranged by the seller directly to the buyer through a courier of their choice and so courier is not part of this module

4. The customers can optionally provide feedback for any specific product after purchase and delivery

The relations:

The relationships among the attributes are shown in the following ER diagram:

Market

Buyer Product Seller

            

Shopping Cart   Order    Payment Delivery

     

   MarketFeedback Buyer

First Normal Form:

The First Normal form or 1 NF is the property in the relation of a dbms whereby each attribute contains only indivisible atomic values and the value of each attribute contains only a single value from that domain. In this case each of the attributes listed above can be uniquely identified by their respective id values as their primary key and hence the dbms is in 1NF.

Second Normal Form:

The Second Normal Form or 2 NF is the property in the relation of a dbms whereby it is in 1 NF and all non key attributes are functionally dependent on the primary key. In this case,  the dbms is in 1NF and each of the non key attributes listed above are functionally dependent on their primary key or id and hence the dbms is in 2NF.

Third Normal Form:

The Third Normal Form or 3 NF is the property in the relation of a dbms whereby it is in 2 NF and all non prime attributes have no transitive functional dependency. In this case,  the dbms is in 2 NF and there is no transitive functional dependency of the non prime attributes listed above and hence the dbms is in 3 NF.

The 3 test queries can be implemented as folows:

1. Add a new item as a seller:

Syntax: INSERT INTO product

new product id, product name, price, rebate, seller id

2. Query for total number of sold item in past month:

Syntax: SELECT COUNT ( seller id ) FROM order

WHERE seller id = my_id AND date = last month

3. Query for total income in US DOLLARS:

Syntax: SELECT SUM ( price-rebate ) FROM order

WHERE seller id = my_id

This concludes the discussion and brief outline of all the subparts of the question on online shopping DBMS and additional attributes like delivery tracking, multiple payment options and gateways etc can be builton top of this module in the future to make it more robust.