Question 1 (Compulsory)

a) Entity Types and Constraints (20 Marks)

1. Entity Types:

- Weak Entity Types: Cannot be uniquely identified by its attributes alone and relies on a "strong" or
owner entity.

- Strong Entity Types: Can be uniquely identified by its attributes alone.

2. Entity Sets:

- A collection of similar types of entities.

- E.g., Students, Courses in a university database.

3. Keys:

- Primary Key: Unique identifier for a record.

- Foreign Key: Attribute that creates a relationship between two tables.

- Candidate Key: An attribute, or set of attributes, that uniquely identifies a record.

- Super Key: A set of one or more columns that can uniquely identify a row in a table.

4. Relationship Constraints:

- Cardinality: Defines the number of instances of one entity that can be associated with one
instance of another entity.

- One-to-One (1:1): A single entity instance in one entity class is related to a single entity instance
in another entity class.

- One-to-Many (1:N): A single entity instance in one entity class is related to multiple entity
instances in another entity class.

- Many-to-Many (M:N): Multiple instances in one entity class are related to multiple instances in
another entity class.

5. Participation Constraints:

- Total Participation: Every entity in the entity set must participate in at least one relationship in
that relationship set.

- Partial Participation: Some entities may not participate in any relationship in that relationship set.
b) Characteristics of Databases (5 Marks)

1. Data Abstraction:

- Levels:

- Physical Level: How the data is stored.

- Logical Level: What data is stored.

- View Level: How the data is presented to the users.

2. Data Independence:

- Logical Data Independence: The capacity to change the conceptual schema without having to
change external schemas or application programs.

- Physical Data Independence: The capacity to change the internal schema without having to
change the conceptual schema.

c) SQL Aggregate Functions and Operations (5 Marks)

- Aggregate Functions: COUNT, SUM, AVG, MIN, MAX.

- Group By Clause: Groups rows sharing a property so aggregate functions can be applied to each
group.

- Having Clause: Used to filter groups based on aggregate functions.

- String Operations: CONCAT, LENGTH, SUBSTRING, REPLACE, etc.

```sql

SELECT department, COUNT(*)

FROM employees

GROUP BY department

HAVING COUNT(*) > 10;

```

d) Pitfalls in Relational Database Design (5 Marks)

- Normalization:

- Functional Dependencies: Relationship between attributes.

- Normalization Forms: 1NF, 2NF, 3NF, BCNF to reduce redundancy and avoid anomalies.

e) SQL Commands (5 Marks)

- DDL (Data Definition Language): CREATE, ALTER, DROP.

- DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE.

- DCL (Data Control Language): GRANT, REVOKE.

- TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT.

Question 2 (Any 4)

a) DBMS vs. File System (10 Marks)

1. DBMS:

- Handles data more securely.

- Supports ACID properties.

- Provides multi-user access.

2. File System:

- Less complex, suitable for smaller applications.

- Lack of security and data integrity mechanisms.

b) Deadlock Handling (10 Marks)

- Deadlock Prevention: Ensuring the system does not enter a deadlock state (e.g., Resource
Ordering).

- Deadlock Detection: Identifying deadlock and recovering (e.g., Wait-for Graph).

- Deadlock Recovery: Preempting resources or rolling back transactions to resolve deadlocks.

Question 3

a) ER and EER Diagrams (10 Marks)

1. ER Diagram: Entity-Relationship model to represent data and relationships.

2. EER Diagram: Enhanced ER model, includes additional concepts like specialization, generalization,
and aggregation.

Example Diagram:

 (Note: Provide a specific diagram

relevant to a common database schema)

b) Relational Algebra Operators (10 Marks)

- Select (σ): σ condition (Relation)

- Project (π): π attribute-list (Relation)

- Union (∪): Relation1 ∪ Relation2

- Set Difference (−): Relation1 − Relation2

- Cartesian Product (×): Relation1 × Relation2

- Rename (ρ): ρ new-name (Relation)

Question 4

a) SQL Queries (10 Marks)

1. First Normal Form (1NF): Ensure each column contains atomic values and each column contains
values of a single type.

2. Second Normal Form (2NF): Meet all requirements of 1NF and every non-key attribute is fully
functional dependent on the primary key.

3. Third Normal Form (3NF): Meet all requirements of 2NF and all attributes are functionally
dependent only on the primary key.

4. Boyce-Codd Normal Form (BCNF): Every determinant is a candidate key.

Question 5

a) Concurrency Control (10 Marks)

- Lock-based Protocols: Using locks to control access (e.g., Two-Phase Locking).

- Timestamp-based Protocols: Transactions ordered by their timestamps to ensure serializability.

b) Transaction Management (10 Marks)

1. Transaction States: Active, Partially Committed, Committed, Failed, Aborted.

2. ACID Properties:

- Atomicity: All operations in a transaction are completed or none.

- Consistency: The database remains in a consistent state before and after the transaction.

- Isolation: Intermediate transaction results are not visible outside the transaction.

- Durability: Once a transaction is committed, it will remain so, even in the event of a system failure.

3. Transaction Control Commands: COMMIT, ROLLBACK, SAVEPOINT.

Question 6 (Any 4)

a) Generalization, Specialization, Aggregation (5 Marks)

- Generalization: The process of extracting shared characteristics from two or more classes.

- Specialization: Defining a new subclass from an existing class.

- Aggregation: A relationship where a class is made up of one or more classes.

b) Log-based Recovery (5 Marks)

- Undo/Redo Logs: Track changes so they can be reversed (undo) or reapplied (redo) during recovery.

c) Views in SQL (5 Marks)

- Views: Virtual tables representing subsets of data from one or more tables.

```sql

CREATE VIEW employee_view AS

SELECT employee_id, first_name, last_name, department

FROM employees

WHERE department = 'Sales';

```

d) Database Administrator Role (5 Marks)

- Duties: Database installation, configuration, upgrading, administration, monitoring, and

maintenance.

e) Types of Attributes (5 Marks)

- Simple: Cannot be divided further (e.g., Age).

- Composite: Can be divided into smaller sub-parts (e.g., Address).

- Single-Valued: Holds a single value for an entity (e.g., Social Security Number).

- Multi-Valued: Can hold multiple values (e.g., Phone Numbers).

This covers the major aspects of the paper. For specific diagrams and more detailed explanations, a
textbook or specific DBMS resources should be referenced.