Q1. Answer the following questions from Block 1:
- (a)) What are the limitations of file-based systems? How does a database approach resolve those limitations? Also, explain the physical architecture of a database management system (DBMS). (300 words)
- (b)) Explain the following terms in the context of a relational model with the help of one example of each: (i) Key constraints (ii) Domain constraints (iii) Candidate key (iv) Select operation (v) Project Operation (vi) Equijoin Operation (vii) Set Difference operation (viii) Referential Integrity constraint (300 words)
- (c)) A departmental store maintains the inventory of all the items that it sells using a database system. In addition, this system is used for keeping a record of the sales of items to its registered customers. This database is used to find the following details by the staff of the departmental store: List of the items that are out of stock. List of customers and the items purchased by them. List of all the items in the store. Draw an ER diagram for the departmental store. Specify key attributes and constraints on each entity type and on each relationship type. Note any unspecified requirement and make appropriate assumptions to make the specification complete. (300 words)
- (d)) Design normalised tables in 3NF for the ER diagram drawn in part (c), with the required integrity constraints. (300 words)
- (e)) Explain the role of the primary index in a database system. Also, compare the primary indexes with the secondary indexes. What are clustering Indexes? Explain with the help of an example. (300 words)
Key Points:
- File systems suffer from redundancy, inconsistency, and poor data integrity; DBMS resolves these via centralization and ACID properties.
- DBMS physical architecture involves Storage Manager, Query Processor, and Transaction Manager for data handling.
- Relational model constraints: Key (PK), Domain (data type/range), and Referential Integrity (FK to PK).
- Relational algebra: Select (filter rows), Project (select columns), Equijoin (match common attributes), Set Difference (find unique tuples).
Answer: This comprehensive response addresses fundamental concepts of Database Management Systems (DBMS) from MCS-207 Block 1. It begins by outlining the inherent limitations of traditional file-based systems, such as data redundancy and inconsistency, and elaborates on how the database approach effectively resolves these challenges through features like data integrity, security, and controlled access. The physical architecture of a DBMS, detailing its core internal components, is also explained. Subseq...