CAVARA OF ALL DABA

Question 1

What are the steps in Conceptual Data Model Development?

Answer 1

1. Determine End User Views, Outputs, & Transaction Requirements
   - End User Views: Data entry, reports, analysis.
   - Outputs: Standard reports, ad-hoc reports, real-time dashboards.
   - Transactions: Insertions, updates, deletions.
2. Determine Entity Types (e.g., Customer, Product).
3. Identify Relationship Types (1:1, 1:N, M:N).
4. Identify Attributes (e.g., CustomerID, Name).
5. Determine Attribute Domains (e.g., Email format).
6. Determine Keys (Candidate, Primary, Alternate).
7. Draw ER Models (entities, relationships, attributes).
8. Check for Redundancy (normalize to 3NF).
9. Validate Model Against User Transactions.
10. Review with Users for feedback.

Question 2

How to achieve 1NF?

Answer 2

1. Eliminate repeating groups (one value per cell).
2. Ensure atomic values (no lists/sets in a field).
3. Define a primary key.

Example:
- Before: Orders(OrderID, [Product1, Product2])
- After: OrderItems(OrderID, ProductID)

Question 3

Steps to reach 2NF?

Answer 3

1. Already in 1NF.
2. Remove partial dependencies (non-key attributes depend on full PK).

Example:
- Before: Orders(OrderID, ProductID, CustomerName)
- CustomerName depends only on OrderID (not the full PK OrderID+ProductID).
- After: Split into Orders(OrderID, CustomerName) and OrderItems(OrderID, ProductID).

Question 4

How to get to 3NF?

Answer 4

1. Already in 2NF.
2. Remove transitive dependencies (non-key attributes depend only on PK, not other non-key fields).

Example:
- Before: Employees(EmpID, Dept, DeptLocation)
- DeptLocation depends on Dept (not directly on EmpID).
- After: Split into Employees(EmpID, Dept) and Departments(Dept, DeptLocation).

Question 5

When is a table in BCNF?

Answer 5

1. Already in 3NF.
2. Every determinant (left side of a functional dependency) is a superkey.

Example:
- Before: Enrollments(StudentID, Course, Professor)
- If Professor → Course, but Professor isn’t a superkey.
- After: Split into Professors(Professor, Course) and Enrollments(StudentID, Professor).

Question 8

Key rule for 4NF?

Answer 8

1. Already in BCNF.
2. No multi-valued dependencies (except trivial ones).

Example:
- Before: Employees(EmpID, Skill, Language)
- Skills and languages are independent (but stored redundantly).
- After: Split into EmpSkills(EmpID, Skill) and EmpLanguages(EmpID, Language).

Question 9

Final step to 5NF?

Answer 9

1. Already in 4NF.
2. No join dependencies (can’t split further without losing data).

Example:
- Before: Projects(Project, Employee, Tool)
- If associations only make sense when all 3 are combined (e.g., “Alice uses Hammer for Project X”).
- After: Keep as-is (if decomposing loses meaning).

Question 10

Signs of poor normalization?

Answer 10

• Repeating groups (violates 1NF).
• Updating one record requires changing many (violates 2NF/3NF).
• Redundant data (e.g., storing CustomerName in every order).

Question 11

Database Configuration

Steps to configure a database server.

Answer 11

1. Hardware: CPU, RAM, storage (SSD/HDD).
2. Software: OS, DBMS (e.g., PostgreSQL), monitoring tools.
3. Install DBMS: Follow vendor guidelines.
4. Post-install: Configure users, permissions, backups.

Question 12

Database Testing

Reasons for database testing

Answer 12

-Ensure data integrity.
- Optimize performance.
- Validate security and compliance.
- Verify CRUD operations.

Question 13

Database Testing
Steps in Test Case Design

Answer 13

1. Define scenarios (valid/invalid data).
2. Specify preconditions.
3. Write test steps.
4. Define expected results

Question 14

Database Security

Threats to database security

Answer 14

• SQL injection.
• Unauthorized access.
• Insider threats.
• Physical theft.

Question 15

Threats to database security

Mitigation measures

Answer 15

• Encryption (data at rest/transit).
• Multi-factor authentication.
• Regular patching.
• Activity monitoring.

Question 17

Data Migration

Steps in the Data Migration Process.

Answer 17

1. Plan (scope, timeline).
2. Assess data quality.
3. Map fields (source → target).
4. Select tools (ETL, replication).
5. Test migration.
6. Validate post-migration.

Question 18

Data Migration

Risks in data migration

Answer 18

-Data loss/corruption.
- Downtime.
- Compatibility issues.

Question 19

Backup & Recovery

Factors affecting RTO and RPO.

Answer 19

• Backup frequency.
• Backup type (full/incremental).
• Infrastructure speed.
• Data size.

Question 20

Backup & Recovery

Types of recovery techniques

Answer 20

Mirroring (real-time copies).
- Log-based recovery (replay transactions).
- Checkpointing (snapshots).

Question 21

Concurrency Control

Problems without concurrency control

Answer 21

• Lost updates.
• Dirty reads.
• Non-repeatable reads.

Question 22

Concurrency Control

How does timestamp-based protocol work?

Answer 22

• Assign unique timestamps to transactions.
• Older transactions proceed first; conflicts trigger rollbacks.

Question 23

Deadlocks

Conditions for deadlock

Answer 23

1. Mutual exclusion.
2. Hold and wait.
3. No preemption.
4. Circular wait.

Question 24

Deadlocks

Deadlock handling strategies

Answer 24

• Prevention (break one condition).
• Avoidance (Banker’s algorithm).
• Detection and recovery (abort transactions).

Question 25

Deadlock

Deadlock handling strategies

Answer 25

• Prevention (break one condition).
• Avoidance (Banker’s algorithm).
• Detection and recovery (abort transactions).

Question 26

Database Maintenance Types of database maintenance

Answer 26

- Backup/recovery. - Performance tuning. - Data integrity checks. - Archiving. - Security audits.

Question 27

Database Maintenance Steps to Communicate Maintenance To Users**.

Answer 27

1. Notify in advance. 2. Explain impact (downtime). 3. Provide updates. 4. Post-maintenance summary.

Question 28

**Why Normalize?**

Answer 28

- No duplicate data. - No update/delete anomalies. - Faster queries. - Easier to modify schema.