ISBS204 Database Management Systems

INSTRUCTIONS

In groups of 3 or 4, you will design a database management system for a fictional business. You will write a report which must propose an entity relationship diagram (ERD) model and subsequent logical design.

The physical database implementation for the proposed model should be developed with MySQL. You will also present your design to the class.

Business Problem

Online shopping management system:

An online shopping system is a type of electronic commerce that allows customers to buy goods or services over the Internet. It is done via a web browser or a mobile app.

To design and implement the database for online shopping system, you have to consider the following things:

• Customer’s Information: This feature gathers important information about the The gathered information was then used to determine the customer’s orders and transactions. For Customer, information needs customer Id, name, address.
• Shopping Order Management: Saves the customers’ shopping information. It also serves as the admin’s basis for the order of the customer. Information stored: order_ID, customer_ID, date.
• Delivery Management: This allows the sellers to monitor the deliveries to the Information stored: delivery_ID, order_ID, customer_ID, product_ID, date.
• Seller’s Information: This table stores information for every seller. Information stored: seller_ID, product_ID, name,
• Transactions Reports and Payment Management: This feature stores the transactions made by customers. It also includes the information and reports of every transaction, such as: report_ID, customer_ID, Order_ID, product_ID, payment_ID, amount, date. For payment management information stored: payment_ID, Customer_ID,
• Product Management: This table stores information for every product. Information stored: product_ID, product_name,

Each customer does multiple transactions, and each transaction is done by only one customer. Each customer does multiple payment, and each payment is done by only one customer. Each payment has multiple transaction reports and each transaction report have only one payment.

Each shopping order has multiple transaction reports, and each transaction is related to only one shopping order. Each customer can order multiple times and each shopping order is done by only one customer.

Each customer has multiple deliveries, and each delivery is related to only one customer. Each order is related to one delivery and vice versa.

Each product can be delivered multiple times and each delivery is related to only one product. Each product can have multiple sellers and each seller can have multiple products. Each product can have multiple transaction reports and each transaction is related to only one product.

Your task as a database designer are as follows:

1. Write a report to your business development manager and propose an entity relationship model and subsequent logical design. In your report, clearly justify your reason for selecting an entity relationship model and the various components relevant to your database design.
2. Create an ERD using the Crow’s Foot notation for the above business rules given. (Tip: You can use draw.oi for your ERD).

In the ERD, you have to identify all entities, relationship, and cardinalities and provide the reasonable attributes (where necessary) including primary keys and foreign keys.

3. Given your ERD, create a database and relevant tables from your ERD. Create an SQL script for the following scenarios provided below.

(Note you have to insert data into your tables at least 10 rows in each table.)

1. Write a SQL query to print the names of all customers whose name do not start with Sort the result by customer id.
2. List the customer id, name, transaction amount from transaction report table, customers who have made transaction— that is, for the customers who appear in the transaction report Ensure that there no duplicate values in your results.
3. Write a SQL query to join the table shopping order, customers and payment. Show Shopping order_id, Customer Id, name, Payment id, payment date values.
4. Write a query to print the customer’s name, ID, transaction amount, and compute the number of transactions, the average transaction amount for each customer. Sort the result by customer

4. Class Presentation

After creating your ERD and implementing the database design, you must prepare a presentation for the business development manager to discuss the ERD and database

design.

In your presentation, enlighten the business development manager on the future of database system and how this online shopping system can benefit from future trends such as NoSQL, big data, data  mining, data warehouse, and business intelligence.

Given the future trends, you also need to discuss the importance of implementing database security management, backup and recovery management.