DBMS Notes - Basics & Normalization

                              DBMS Notes - Basics & Normalization

Introduction to DBMS

Recommended Books:

- DBMS by Raghurama Krishnan

- DBMS by Navathe

What is DBMS?

    Database: Collection of related data.  

   Example : Student information.

  DBMS : Software used to manage and access data efficiently. It acts as an interface between users and database files.

   Flat File System (without DBMS):

- Users directly manage files (`f1, f2, ..., fn`) on disk.

- Useful only when database is  small.

  Limitations of Flat File System:

1. Too complex to manage application programs.

2. High I/O cost to access data.

3. Low degree of concurrency.

4. Difficult to maintain different levels of security.

5. Difficult to maintain non-redundant data.

Advantages of DBMS:

- Supports  data independence.

- Users can access data using SQL without knowing storage details.

- Reduces data redundancy.

- Better indexing → faster access.

- Higher concurrency control.

- Uses Views (virtual tables) for security.

- Easy to maintain non-redundant data through Normalization.

 Relational Model (Codd’s Rules)

- Proposed by E.F. Codd.

- Data must be in tabular format (rows & columns).

- No duplicate rows.

Key Terms

Relational Schema: Structure/Definition of a table.  

Example: `Student(sid, sname, dob)`

Arity / Degree: Number of attributes (columns) in a table.

Cardinality: Number of rows (records) in a table.

Types of keys

1. Candidate Key: Minimal set of attributes that can uniquely identify each record.

2 .Primary Key: One candidate key chosen as main identifier. Cannot be NULL.

3 .Alternate Key: Candidate keys that are not chosen as primary key (can be NULL).

4 .Super Key: Any set of attributes that can uniquely identify records (includes candidate keys + extra attributes).

5 .Prime Attribute: Attribute that is part of any candidate key.

6 .Non-Prime Attribute: Attribute that is not part of any candidate key.

7 .Foreign Key (Referential Key): Used to create relationship between two tables. It references Primary Key or Alternate Key of another table.

3: Normalization

 Why Normalization?

To eliminate/reduce redundancy in database tables.

Problems due to Redundancy (Database Anomalies):

1. Insertion Anomaly

2. Deletion Anomaly

3. Update Anomaly

These cause inconsistency in the database.

Functional Dependency (FD)

- X → Y: If X determines Y.

- If two tuples have same value for X, they must have same value for Y.

Types of Functional Dependencies:

1. Trivial FD: `X → Y` where Y is subset of X.

2. Non-Trivial FD: Y is not subset of X.

3. Semi-Non-Trivial FD

Armstrong’s Axioms:

- Reflexivity

- Transitivity

- Augmentation

- Union

- Decomposition

Attribute Closure (X⁺): Set of all attributes that can be determined by X.