IGNOU CMAD BCS 92 Introduction to Databases Solved Assignment ENGLISH

Please make sure your assignment questions match your current IGNOU session

BCS 092 (January 2026 - July 2026) - ENGLISH

Course Code :

BCS-092

Course Title

Introduction to Databases

Assignment Number

CMAD/092/Assignment/2026

Maximum Marks

25

Last Dates for Submission:

30th April, 2026 (for January session) 31 October 2026 (for July session)

This assignment consists of four questions carrying a total of 25 marks. Answer all questions. You may use illustrations and diagrams to enhance your explanations. Please go through the guidelines regarding assignments given in the Programme Guide for the presentation format. The answer to each part of the question should be confined to about 300 words.

Question 1:

(5 Marks)

Design an ER diagram for a Pharmacy. The pharmacy stocks various medicines. Each medicine is uniquely identified by a Batch Number. The system maintains information about the medicine's name, price, quantity in stock, date of manufacture, and date of expiry. The pharmacy sells these medicines only to the patients who are registered with it. It stores the Patient ID, Name, Address and Phone Number for each the patients. All sales transactions are duly recorded along with the date of the transaction. A single transaction may consist of multiple medicines.

Design an ER diagram for the scenario above. You should clearly list the following:

i) Entities and Attributes.

ii) Relationships and their Cardinality

iii) Key Constraints (Primary/Foreign Keys).

Make and state suitable assumptions, if any.

Question 2:

Design a Relational Schema based on the ER diagram of Question 1. The relations must be at least in 3NF. 

Perform the following tasks for these relations:

a) Identify the Primary key of every relation.

b) Identify the Foreign keys and referential integrity constraints in the relations.

c) Write SQL commands to create the tables with keys and constraints

d) Write SQL commands to enter about five sets of meaningful data in each relation.

Question 3:

(10 Marks)

Answer the following queries using SQL for the database created in Question 2.

0 List the names of all patients whose names begin with the letter "S".

(ii) List the MedicineName for all medicines whose Price is greater than ₹200.

(iii) Find the list of medicines for which the Stock Quantity is very low (less than 10 units).

(iv) List all the transaction details made by the Patient whose ID is "1001".

(v) Calculate the total amount spent by the patient with ID "1001"

(vi) List the names of patients who have made at least one purchase.

(vii) Count the number of separate transactions made by each patient.

(viii) Find the count of different medicines available in the pharmacy. 

(ix) Identify the medicine that has been sold in the largest quantity.

Find the total number of registered patients in the system. (x)

Note: Make suitable assumptions, if any.

Question 4:

What is the need for logical design in the Database Development Life Cycle (DDLC)? Explain the steps for the implementation of a database design.

BCS 092 (January 2025 - July 2025) - ENGLISH

Course Code : BCS-092

Course Title : Introduction to Databases

Assignment Number : CMAD/092/Assignment /2025

Maximum Marks : 25

Last Dates for Submission : 30th April, 2025 (for January session)

30th October, 2025 (for July session)

This assignment has six questions, carrying a total of 25 marks. Answer all questions. You may use illustrations and diagrams to enhance your explanations. Please go through the guidelines regarding assignments given in the Programme Guide for the presentation format. The answer to each part of the question should be confined to about 300 words.

Question 1:Design an ER diagram for a departmental Store. This store sells many products. The store keeps multiple quantities of these products. Each product is uniquely identified by a Product ID. In addition, the store maintains the Product Name, Price, Quantity in stock, Date of production, and Date of expiry of each product. The store sells these products to registered customers of the store. The store maintains the Customer ID, Name, Postal Address with City and Pin code, and Phone number of every customer. All the orders made by different customers are recorded in an order table. An order may consist of many products.

Identify and list the entities, attributes, relationships, cardinality, and key constraints for the description of the store given above. Make and state suitable assumptions.

Question 2:Design the Relational Schema for the E-R diagram you drew for Question 1. The relations must be at least in 3NF. Perform the following tasks using these relations:

a) Identify the domain of the attributes.

b) Identify the Primary key of every relation.

c) Identify the Foreign keys and referential integrity constraints in the relations.

Create the tables with keys and constraints and enter about five sets of meaningful data in cach relation

Question 3:Answer the following queries using SQL. for the database created in Question 2.

(i) List the names of all the customers whose names start with the alphabet "A".

(ii) List the Product name of all the products whose price is over INR 5000.

(ii) Find the list of products whose quantity in stock is less than 5.

(iv) List all orders made by a customer whose ID is "C001".

(v) Find the total amount of order of Order ID "000001".

(vi) List the name of the customers who have Ordered at least once.

(vii) Find the number of orders given by cach customer.

(vi) Find the total number of products in the store.

(ix) List the product that has been ordered the most.

(x) Find the total number of customers of the store.

Note: Make suitable assumptions, if any.

Question 4:Explain the logical design phase of database development of an organisation.

Q: How will I receive the PDF?
A: Immediately after payment, the download link will appear.

Q: Is this hand-written or typed?
A: This is a professional typed computer PDF. You can use it as a reference for your handwritten submission.