DATABASE MANAGEMENT SYSTEM

Answer: (ii) is much more data independence than some other database models

Explanation: The relational model provides high data independence. Data is stored in tables and logical/physical independence is better than hierarchical/network models where explicit pointers relate records. Primary keys are required and relationships are implicit via keys, not explicit pointers. Tables are two-dimensional, not many dimensions.

Answer: (iv) DDL

Explanation: DDL (Data Definition Language) is used for structure operations like CREATE, ALTER, DROP, TRUNCATE. DML is for data manipulation (SELECT, INSERT, UPDATE, DELETE). Query is a DML SELECT. Relational schema is the design itself, not the language.

Answer: (ii) One teacher can have many classes

Explanation: One-to-many (1:N) means one instance of entity A relates to many instances of entity B, but not vice versa. Here, one teacher teaches many classes, but a class is taught by one teacher (in this example). Option (i) is also 1:N but phrased as "One class may have many teachers" which would be many-to-one from other side. Canonical 1:N example is teacher -> classes. Many-to-many would be many teachers to many classes.

Answer: (ii) Primary key

Explanation: Primary key cannot be NULL and must be unique - entity integrity constraint. Unique key can have one NULL (in many DBMS) but cannot duplicate. Super key is any set of attributes that uniquely identifies row, may include extra attributes. Foreign key can be NULL (if relationship optional) - referential integrity allows NULL.

Answer: (i) It is a type of system software (most exam expects (ii) or (iv) - see note)

Explanation: DBMS is system software that manages databases, provides abstraction over OS. Some classifications call it application software because it runs on OS, but academically it is considered system software / middleware between OS and applications. Given options, system software (i) is closest. Some keys mark (iv) Both system and application as correct because DBMS exhibits both properties - system for DB operations, application for users. Mention both views.

Answer: (iv) Meta Data

Explanation: Metadata is data about data - describes structure, schema, types, constraints, provenance. Examples: data dictionary, schema info, column types. Directory is file listing, Warehouse is integrated storage for analytics, Sub Data is not standard term.

Answer: (iv) Google

Explanation: MySQL, Microsoft Access, IBM DB2 are RDBMS products. Google is a company/search engine, not a DBMS product (though it uses Bigtable/Spanner internally, Google itself is not marketed as DBMS). Hence Google is not example of DBMS.

Answer: (iii) Entity set

Explanation: Entity set is collection of entities of same type sharing same properties/attributes. Example: Student entity set with attributes rollno, name. Relation set is not standard term, Tuples are single rows/instances, Entity relation model is ER diagram, not a set.

Five Responsibilities of Database Manager (DBMS / DBA responsibilities):

1. Schema Definition: Define logical/physical schema, storage structure, access method via DDL. If not done: no consistent structure, data inconsistency, no integrity.

2. Storage Structure & Access Method Definition: Choose indexing, hashing, partitioning. Failure: poor performance, high I/O, slow queries.

3. Granting Authorization: Provide privilege controls (GRANT/REVOKE) for data access. Failure: security breach, unauthorized access/modification.

4. Routine Maintenance: Backup, recovery, monitoring, tuning, reorganization. Failure: data loss on failure, degraded performance, no recovery.

5. Integrity Constraint Specification: Define constraints (primary key, foreign key, check). Failure: invalid data, inconsistency, referential violations.

Additional: Transaction management, concurrency control, defining security. Each missing responsibility leads to data anarchy, redundancy, inconsistency, security compromise, and failure recovery inability.

Database Characteristics vs File System:

Database Characteristics:

• Self-describing (catalog/metadata)
• Data abstraction & independence (physical/logical)
• Concurrent multi-user access with ACID
• Controlled redundancy, data integrity constraints
• Security, backup/recovery, views

File System vs DBMS Differences:

1. Redundancy: File system stores duplicate data in separate files leading inconsistency; DBMS controls redundancy via normalization.
2. Inconsistency: File updates need multiple files, easy inconsistency; DBMS single repository ensures consistency.
3. Data Dependency: File system program depends on file format (structural dependence); DBMS provides data independence.
4. Integrity: File system enforces in application code; DBMS enforces declarative constraints.
5. Atomicity/Concurrency: File system no transaction/ACID; DBMS ensures atomic, isolated concurrent transactions, locking.
6. Security: File OS-level permissions only; DBMS GRANT/REVOKE fine-grained.
7. Recovery: File system manual backup; DBMS log-based recovery, crash recovery.
8. Sharing: File limited; DBMS multi-user with views.

Example: Banking file system storing customer in two files vs DBMS one Customer table ensures single update propagates, concurrent withdrawals handled via locking.

Three Level Architecture (ANSI/SPARC):

1. External/View Level (highest): Individual user views - subset of database tailored for user group. Hide irrelevant data. Example: Student view shows grades for adviser, Accounts view hides grades. Provides logical data independence via view mapping.

2. Conceptual/Logical Level: Community view - entire database structure - entities, relationships, attributes, constraints, semantics. DBA defines using conceptual schema (ER + relational). Example: Tables Student, Course, Enroll with relationships. Physical independence maintained - conceptual unaffected by physical changes.

3. Internal/Physical Level: How data physically stored - files, indexes, hashing, blocks, storage allocation, compression. Managed by OS.

Mappings: External-Conceptual mapping translates view query to conceptual; Conceptual-Internal mapping translates conceptual operations to physical storage access.

Advantages: Data independence (physical and logical), security (views), easier DBA management. Diagram: Three layers with mappings between, users at top accessing views, physical storage at bottom.

Using 2D array int A[10][20] as example:

(i) Three levels of data abstraction:

• Physical level: How array stored in memory - row-major contiguous bytes, address = base + (i*20 + j)*sizeof(int), disk blocks. User unaware.
• Logical level: How we conceptualize - 2D matrix A[i][j] with rows/cols, data types, operations. Programmer works here.
• View level: Specific view - e.g., user sees only upper triangle or specific rows via view/projection, hiding rest. Different users see different subsets.

Example mapping to DB: physical = storage structures, logical = tables schema, view = user-specific virtual tables.

(ii) Schema vs Instances:

• Schema: Overall definition/structure - e.g., int A[10][20]; defines dimensions, type, constraints. Analogous to CREATE TABLE. Does not change frequently (logical schema). Physical schema is storage details.
• Instances: Actual data content at a point in time - e.g., values stored: A[0][0]=5, A[0][1]=7 ... snapshot. Changes frequently on updates. Like table rows at time t.

If array reinitialized, schema same but instances change.

E-R Diagram for Car Insurance Company:

Entities:

• Customer (attributes: cust_id PK, name, address, phone)
• Car (attributes: car_id PK, model, year, color, reg_no)
• Accident (attributes: acc_id PK, date, location, damage_amount) - weak or regular, depends on design; accident identified by car + date.

Relationships:

• Owns: Customer (1) -- (N) Car, total participation on Car side (each car owned by one customer), cardinality one-to-many, key of Customer included in Car as foreign key if mapped.
• Involved/Has: Car (1) -- (N) Accident, zero to many (0..*), each car may have zero or many accidents, each accident belongs to one car. Accident could be weak entity with Car as owner, partial key acc_id or date.

If accident is weak: Car --< Accident (double diamond), accident PK = car_id + acc_no.

Constraints: Customer owns at least one car, Car owned by exactly one Customer, Accident associated with exactly one Car.

Diagram: Rectangles for Customer, Car, Accident; Diamonds for Owns and Had; Lines with cardinalities (1:N). Show primary keys underlined, weak entity double rectangle, identifying relationship double diamond. No image URL required - textual description suffices for construction.

Database: Organized collection of related, integrated data stored to serve multiple applications with minimal redundancy, shared and managed by DBMS. Includes data plus metadata.

Importance in Modern Software Systems:

• Centralized data management avoids duplication, ensures consistency
• Efficient query processing (SQL, indexing) for applications (web, mobile,ERP)
• Concurrency, transaction support (ACID) for multi-user systems like banking, e-commerce
• Security, authorization, privacy for sensitive data
• Scalability, handling big data, distributed access, cloud
• Integration across microservices via single source of truth
• Analytics, BI, ML pipelines depend on curated database
• Recovery, durability prevents loss

Examples: E-commerce orders DB powers catalog/cart/payments; social network stores graphs; IoT sensor DB enables real-time monitoring. Without DBMS, file-based chaos, no integrity.

Strong vs Weak Entity Set:

• Strong Entity: Exists independently, has primary key. Represented by single rectangle. Example: Customer (cust_id), Car (car_id).

• Weak Entity: Cannot be uniquely identified by own attributes alone, depends on strong entity, no PK, has partial key (discriminator). Existence dependent, total participation. Represented by double rectangle, identifying relationship double diamond. Example: Accident depending on Car, Dependent depending on Employee, Loan Payment depending on Loan.

Difference table: Strong has PK, existence independent, single rectangle; Weak needs owner, partial key + owner PK forms PK, double rectangle, always total participation.

E-R example: Employee (strong) -- Employs -- Dependent (weak, attributes: dep_name (partial key), age, relationship). Dependent PK = emp_id + dep_name. Relationship is identifying, cardinality 1:N, double lines from weak to relationship.

DDL and DML with Examples:

DDL (Data Definition Language): Defines/modifies schema structure. Auto-commit, no rollback. Commands:

• CREATE TABLE Student(roll INT PRIMARY KEY, name VARCHAR(50));
• ALTER TABLE Student ADD age INT;
• DROP TABLE Student;
• TRUNCATE TABLE Student;
• CREATE INDEX idx_name ON Student(name);

DML (Data Manipulation Language): Manipulates data within structures. Needs COMMIT. Commands:

• SELECT * FROM Student WHERE age >20;
• INSERT INTO Student VALUES(1,'Amit',21);
• UPDATE Student SET age=22 WHERE roll=1;
• DELETE FROM Student WHERE roll=1;

DDL acts on metadata, DML on instances. DDL changes schema, DML queries/modifies rows.

Disadvantages of File Processing Systems:

• Data Redundancy and Inconsistency: Same data duplicated across files, updates require multiple places, inconsistency.
• Data Dependence: Programs tied to file format, change in structure requires program change.
• Difficulty in Accessing Data: Need custom programs for each query, no declarative query language.
• Data Isolation: Scattered files, different formats hard to integrate.
• Integrity Problems: Constraints enforced in application code, not centrally, error-prone.
• Atomicity Problems: No transaction support, partial updates on failure leave inconsistent state.
• Concurrent Access Anomalies: No locking, simultaneous updates lost update problem.
• Security Problems: Difficult fine-grained authorization, OS-level only.
• Recovery Difficult: No log-based recovery, difficult to restore after crash.

Example: University storing student data in separate files for library, fees, academics leads to all above issues which DBMS solves.

Three Levels of Data Abstraction:

1. Physical Level: Lowest - how data stored physically - files, blocks, indexes, hashing, compression. Hidden from users.

2. Logical/Conceptual Level: What data stored, relationships, constraints, semantics. DBA defines schema, e.g., tables Student(id, name). Provides logical independence.

3. View/External Level: User-specific views - subset of logical level, hides irrelevant data, provides security. Example: Faculty view shows student grades, Student view hides.

Benefits: Data abstraction achieves independence - physical changes without affecting logical, logical changes without affecting views - easing application development, security.