IGNOU BCSL-34 DBMS Lab - Latest Solved Assignment

Q1. Design and implement a simple database using MS-Access for an Online Retail Store, using fundamental concepts of database management systems such as creating tables, establishing relationships, performing CRUD (Create, Read, Update, Delete) operations, and writing basic SQL queries.

Create a database schema for an online retail store and implement various operations on it. The database will manage information about customers, products, orders, and order details. Use SQL to create tables, establish relationships, and perform queries to manipulate and retrieve data.

I. Create Database Schema:

 Customers Table:
 customer_id (Primary Key, INT, Auto Increment)
 first_name (VARCHAR)
 last_name (VARCHAR)
 email (VARCHAR, Unique)
 phone (VARCHAR)
 address (VARCHAR)
 Products Table:
 product_id (Primary Key, INT, Auto Increment)
 product_name (VARCHAR)
 description (TEXT)
 price (DECIMAL)
 stock_quantity (INT)
 Orders Table:
 order_id (Primary Key, INT, Auto Increment)
 customer_id (Foreign Key, INT)
 order_date (DATE)
 status (VARCHAR)
 OrderDetails Table:
 order_detail_id (Primary Key, INT, Auto Increment)
 order_id (Foreign Key, INT)
 product_id (Foreign Key, INT)
 quantity (INT)
 total_price (DECIMAL)

II. Relationships:

 Each order is placed by one customer.
 Each order can have multiple products
 Each product can be part of multiple orders.

Draw an ER-diagram for this application.

III. Operations:

 CRUD (Create, Read, Update, Delete) Operations
 Insert new records into each table.
 Read/display records from each table.
 Update existing records in each table.
 Delete records from each table.

IV. Write and execute the following SQL Queries:

1. Retrieve all orders along with the customer details who placed the order.
2. Find all products that have been ordered by a specific customer.
3. Retrieve the total sales for each product.
4. Find all customers who have placed at least one order.
5. Retrieve the total quantity of products ordered by each customer.
6. Find all orders and their order details for a specific customer.
7. Retrieve all products along with the total quantity ordered.
8. Find the total revenue generated from orders placed within a specific date range.
9. Retrieve all customers who have ordered a specific product.
10. Find the most frequently ordered product.
11. Retrieve the average order value for each customer.
12. Find all products that have never been ordered.
13. Retrieve the total number of orders placed each month.
14. Retrieve the total number of products ordered in each order.
15. Find the top 5 customers based on total spending.
16. Retrieve all orders placed on a specific date.
17. Find the total number of unique products ordered by each customer.
18. Retrieve the order details for the order with the highest total price.
19. Find the top 3 products based on the total quantity ordered.
20. Retrieve the total sales for each month.
21. Find the customer who has placed the most orders.
22. Retrieve the total number of orders for each status.
23. Find the order with the highest quantity of a single product.
24. Retrieve the customer details for the order with the highest total price.
25. Find the average quantity of products ordered per order.

Note: You must perform the above said activities and also take prints of screenshots of the layouts, sample input and output along with the necessary documentation for this practical question. Assumptions can be made wherever necessary.

Q1. A XYZ-star hotel requires a computerized system to automize its front office operations that support the following functionalities:

• Easy input facility for new data
• Keep details about rooms, tariff, details of occupied rooms, details of unoccupied rooms, check-in/ check-out timings, staff etc..
• Update necessary details about the room-billing, extra facilities used like gym, laundry, STD/ISD calls made etc.
• Query support
• Report generation

Perform the following tasks:

(i) Draw the ER diagram by identifying the entities, relationships and cardinality by using any of the drawing tools like smartdraw, dia, visio, conceptdraw etc.. Follow proper conventions.
(ii) Create suitable database to support/accommodate all the functionalities referred above.
(iii) Using MS-Access, design various forms to support the hotels’ front office operations such as enquiry form, tariff, categories of rooms along with their category, list of vacant rooms, list of occupied rooms, list of unoccupied rooms, room-billing (which includes extra facilities also), restaurant billing, staff details, list of phone numbers, reservations etc..
(iv) Report generation like daily reports of list of vacant rooms, list of occupied rooms, onduty staff list, list of room reservations made etc.