NAME:
AQIB MEHMOOD
CLASS:
BSCS-4A
ROLL NO:
242201005
SUBJECT:
ADVANCE DBMS
ASSIGNMENT:
01
Part A: Conceptual Understanding
Q1. Explain the following terms with examples:
a) Database: An organized collection of structured data typically stored electronically.
o Example: A University Student Record System.
b) DBMS: Software used to manage, retrieve, and store data in a database.
o Example: Oracle or MySQL.
c) Primary Key: A unique identifier for a record in a table; it cannot contain null values.
o Example: Student_ID in a student table.
d) Surrogate Key: A unique identifier that is system-generated and has no business meaning.
o Example: An Auto_Increment ID column in a database.
Q2. ACID Properties in a Banking System
ACID ensures reliability in database transactions:
Atomicity: The entire transaction happens at once or not at all. If a transfer fails halfway, the
money is returned to the sender.
Consistency: Data must move from one valid state to another. Total money across accounts
remains the same after a transfer.
Isolation: Transactions occurring at the same time do not interfere with each other.
Durability: Once a transaction is committed, it remains saved even during a system crash.
Q3. Relationship Differences
One-to-Many (1:M): A single record in Table A relates to multiple records in Table B.
o Example: One Department has many Students.
Many-to-Many (M:M): Multiple records in Table A relate to multiple records in Table B.
o Example: Many Students can enroll in many Courses.
Q4. Limitations of Traditional RDBMS
1. Scalability: Traditional RDBMS are mostly designed for vertical scaling (bigger servers), which
is expensive.
2. Schema Rigidity: They require a fixed structure, making it hard to store unstructured data like
social media posts.
3. Performance: As data volume grows into terabytes, query performance often slows down
significantly.
Part B: Application & Tool Usage
Q5. Advanced DBMS Understanding
a) Need for Advanced DBMS: To handle "Big Data," high-speed transactions, and global
availability that traditional systems cannot support efficiently.
b) Replication & Fragmentation:
o Data Replication: Storing copies of data on different servers (e.g., a backup server in another
city).
o Data Fragmentation: Breaking a database into smaller parts and storing them on different
nodes (e.g., storing "North" region data on Server 1 and "South" on Server 2).
c) Horizontal Scaling: Adding more machines (nodes) to a system to share the load. It is
difficult in RDBMS because maintaining ACID consistency across multiple servers is complex.
Q6. Short Case Study: Banking System
a) Recommended Type: Advanced DBMS (Distributed or Cloud-based RDBMS).
b) Suggested Solutions:
1. Database Sharding (Horizontal Scaling): To handle many users and large data.
2. Data Replication: To prevent data loss during a crash.
c) Role of ACID: It ensures that even with high traffic and crashes, financial transactions are
never lost or partially completed, maintaining 100% data integrity.