Important Questions of DBMS: -

1. Define the terms Super Key, Candidate Key, and Primary Key
• Super Key: A set of one or more attributes that uniquely identify a tuple in a relation. It may
contain extra attributes that are not necessary for uniqueness.
• Candidate Key: A minimal super key, meaning it has no redundant attributes and still uniquely
identifies each record. A table can have multiple candidate keys.
• Primary Key: A candidate key chosen as the unique identifier for a table. It must be unique and
not null.
Example:
For a student table with attributes (StudentID, Name, Email, Phone),
• Super Key: {StudentID}, {StudentID, Email}, {StudentID, Phone}
• Candidate Key: {StudentID}, {Email} (since both are minimal and unique)
• Primary Key: {StudentID} (selected as the main unique identifier)

2. Define Schema and Instance in the Context of a Database. How Do They Differ?
• Schema: The structure of a database, including tables, attributes, data types, and relationships. It is
defined at the time of database design and remains mostly unchanged.
• Instance: The actual data stored in the database at a given moment in time. It changes frequently
as data is inserted, updated, or deleted.
Difference:
• Schema is static, while instance is dynamic.
• Schema defines structure, while instance represents content at a specific time.
Example:
• Schema: Student (StudentID, Name, Email, Phone)
• Instance: {101, "Alice", "alice@example.com", "1234567890"}

3. Define and Differentiate Between Entity Integrity and Referential Integrity

• Entity Integrity: Ensures that every table has a primary key and that the primary key values are
unique and not null. This prevents duplicate or missing records.
• Referential Integrity: Ensures that a foreign key in one table references a valid primary key in
another table. It prevents orphan records.
Example:
• Entity Integrity: A StudentID cannot be null or duplicated in the Student table.
• Referential Integrity: If CourseID in the Enrollment table is a foreign key referring to the
Course table, every CourseID must exist in the Course table.
4. Describe Relational Algebra and Its Significance in Relational Databases
Relational Algebra is a formal query language used to retrieve and manipulate data in relational
databases. It consists of operations such as:
• Selection (σ): Filters rows based on a condition.
• Projection (π): Selects specific columns from a table.
• Union (∪): Combines results from two tables.
• Intersection (∩): Retrieves common records from two tables.
• Difference (-): Finds records present in one table but not in another.
• Join (⨝): Combines related data from multiple tables.
Significance:
• Provides a foundation for SQL query processing.
• Helps optimize query execution.
• Ensures mathematical precision in database operations.

5. Generalization and Aggregation in the ER Model

• Generalization: The process of combining similar lower-level entities into a higher-level entity.
o Example: Car and Truck entities can generalize into a Vehicle entity.
• Aggregation: The process of treating a relationship as an entity itself.
o Example: A Manages relationship between Employee and Project can be treated as an
entity if it has attributes like "DateAssigned".
ER Representation:
• Generalization uses triangle notation with an upward arrow.
• Aggregation connects relationships with an oval box.

6. Transforming an ER Diagram into Relational Tables

1. Convert each entity into a table.
2. Convert attributes into columns.
3. Assign primary keys.
4. Convert relationships into foreign keys.
Example:
• ER Model: Employee (EmpID, Name) → Works_On (EmpID, ProjectID) → Project (ProjectID,
ProjectName)
• Relational Tables:
o Employee(EmpID PK, Name)
 o Project(ProjectID PK, ProjectName)
o Works_On(EmpID FK, ProjectID FK)

7. Roles and Responsibilities of a Database Administrator (DBA)

A DBA manages database performance, security, and recovery.
Responsibilities:
• Security Management: Implement user access control and encryption.
• Performance Optimization: Indexing and query tuning.
• Backup & Recovery: Prevents data loss.
• Database Updates: Ensures data consistency.
Tools:
• SQL Server Management Studio (SSMS)
• Oracle Enterprise Manager
• MySQL Workbench

8. Functional Dependencies and Normalization

• Functional Dependency: If A → B, knowing A means we can determine B.
• Normalization: Organizing data to eliminate redundancy.
Normal Forms:
• 1NF: No repeating groups (Atomic data).
• 2NF: No partial dependency (Every non-key attribute depends on the whole primary key).
• 3NF: No transitive dependency (Non-key attributes should not depend on other non-key
attributes).
Example:
• 1NF: No multiple phone numbers in one cell.
• 2NF: If a table has (StudentID, CourseID, StudentName), remove StudentName to another table.
• 3NF: Remove derived attributes like Age (computed from DOB).

9. Mapping Binary 1:1 and M:N Relationship Types

• 1:1 Relationship: One-to-one relationship (e.g., Employee → Office).
o Implemented by storing a foreign key in either table.
• M:N Relationship: Many-to-many relationship (e.g., Student → Course).
o Implemented using a junction table with foreign keys.
10. Weak Entity
An entity that depends on a strong entity for existence and lacks a primary key.
• Example: A Dependent entity in an Employee-Dependent relationship.
11. Concept of Keys in Databases
• Primary Key: Uniquely identifies records.
• Foreign Key: References primary key of another table.
• Candidate Key: Minimal unique key.
• Super Key: Any key that uniquely identifies a row.
12. Physical vs Logical Data Independence
• Physical: Changes in storage do not affect application logic.
• Logical: Changes in schema do not affect applications.
13. Disadvantages of File System Over DBMS
• Data redundancy
• Lack of concurrency control
• No security
• No data consistency
14. Four Functions of DBA
• Security
• Backup & recovery
• Performance tuning
• User access management
15. Three-Schema Architecture
• Internal Level: Physical storage.
• Conceptual Level: Logical design.
• External Level: User views.
Mappings ensure data consistency.
16. Types of Data Models
• Hierarchical
• Network
• Relational
• Object-oriented