Unit 1: Database Management System (DBMS) (12 Hours)

1.1 Introduction to Data, Database, Database System, DBMS

• Data: Raw facts and figures without context. For example, numbers, dates, and strings.
• Database: A structured collection of data. It allows for easy retrieval and management of data.
• Database System: A system for creating and managing databases. It includes hardware, software, data, procedures, and users.
• DBMS (Database Management System): Software that interacts with users, applications, and the database itself. It helps in data management, storage, and retrieval. Examples include MySQL, Oracle, and Microsoft SQL Server.

1.2 Field, Record, Objects, Primary Key, Alternate Key, Candidate Key

• Field: The smallest unit of data in a database; represents a single attribute. For example, "Name" in a student database.
• Record: A collection of related fields that represent a single entity. For example, a student record might include fields for ID, name, and grade.
• Object: An instance of a class in object-oriented databases. It contains data (attributes) and methods (functions).
• Primary Key: A unique identifier for a record in a table. No two records can have the same primary key value (e.g., Student ID).
• Alternate Key: A field that can uniquely identify a record but is not the primary key (e.g., email address).
• Candidate Key: A field or combination of fields that can uniquely identify a record in a table. One of the candidate keys is chosen as the primary key.

1.3 Advantages of Using DBMS

• Data Abstraction: Simplifies complex data management.
• Data Integrity: Ensures accuracy and consistency of data.
• Data Security: Protects data from unauthorized access.
• Reduced Redundancy: Minimizes data duplication by centralizing data storage.
• Data Sharing: Allows multiple users to access and manipulate the data simultaneously.
• Backup and Recovery: Facilitates data backup and restoration processes in case of data loss.

1.4 DDL (Data Definition Language) and DML (Data Manipulation Language)

• DDL (Data Definition Language): Used to define and manage all database structures, including tables, schemas, and indexes. Common DDL commands include:
  • CREATE: To create a new table or database.
  • ALTER: To modify an existing database object.
  • DROP: To delete a table or database.
• DML (Data Manipulation Language): Used to manipulate data within the database. Common DML commands include:
  • INSERT: To add new records to a table.
  • UPDATE: To modify existing records in a table.
  • DELETE: To remove records from a table.
  • SELECT: To query and retrieve data from the database.

1.5 Database Model: Network Model, Hierarchical Model, Relational Database Model

• Network Model: Represents data as a graph, allowing multiple relationships among records. It uses pointers to link data. Suitable for complex relationships.

• Hierarchical Model: Organizes data in a tree-like structure with parent-child relationships. Each child has only one parent, making it easy to navigate, but inflexible for complex relationships.

• Relational Database Model: Organizes data into tables (relations) consisting of rows and columns. It allows for easy data manipulation and retrieval using SQL (Structured Query Language). Most widely used due to its flexibility and power.

1.6 Concept of Normalization: 1NF, 2NF, 3NF

• Introduction to Database Normalization

  • Definition: Database normalization is the process of organizing data into tables in such a way that reduces redundancy and improves data integrity.

  • Why Normalize?

    • To eliminate duplicate data
    • To ensure data dependencies make sense
    • To prevent anomalies during data operations (insertion, deletion, update)

  ---

  Key Objectives of Normalization

  • Data Integrity: Ensure that data remains accurate and consistent over time.
  • Data Redundancy Reduction: Remove unnecessary duplicate data.
  • Efficiency: Streamline query performance and storage usage.

  ---

  Anomalies in Unnormalized Data

  • Insertion Anomalies: Inability to add data due to absence of other data.
  • Update Anomalies: Changes to data require updates in multiple places.
  • Deletion Anomalies: Deleting data leads to unintentional loss of other related data.

  ---

  First Normal Form (1NF)

  • Criteria:

    • Eliminate repeating groups.
    • Each column must contain atomic (indivisible) values.

  • Example Before 1NF:

    Student ID	Name	Subjects
    1	Alice	Math, Physics
    2	Bob	Chemistry, Biology

  • After 1NF:

    Student ID	Name	Subject
    1	Alice	Math
    1	Alice	Physics
    2	Bob	Chemistry
    2	Bob	Biology

  ---

  Slide 6: Second Normal Form (2NF)

  • Criteria:

    • Must be in 1NF.
    • Remove partial dependencies (no non-primary key attribute depends on a part of a composite primary key).

  • Example Before 2NF:

    Student ID	Course ID	Course Name	Instructor
    1	101	Math	Dr. A
    2	102	Chemistry	Dr. B

  • After 2NF (Separate into two tables):

    Table 1: Student-Course

    Student ID	Course ID
    1	101
    2	102

    Table 2: Course-Details

    Course ID	Course Name	Instructor
    101	Math	Dr. A
    102	Chemistry	Dr. B

  ---

  Slide 7: Third Normal Form (3NF)

  • Criteria:

    • Must be in 2NF.
    • Remove transitive dependencies (no non-primary key attribute depends on another non-primary key attribute).

  • Example Before 3NF:

    Course ID	Instructor	Instructor Phone
    101	Dr. A	123-456
    102	Dr. B	234-567

  • After 3NF:

    Table 1: Course

    Course ID	Instructor
    101	Dr. A
    102	Dr. B

    Table 2: Instructor

    Instructor	Instructor Phone
    Dr. A	123-456
    Dr. B	234-567

1.7 Centralized Vs. Distributed Database

• Centralized Database: All data is stored and managed in a single location or server. It provides better control, but can be a single point of failure and may lead to performance issues during heavy load.

• Distributed Database: Data is stored across multiple locations, which can be physically or logically dispersed. It improves reliability and availability but requires complex synchronization and management.

1.8 Database Security

• Database Security: Protecting the database from unauthorized access, data breaches, and other security threats. Key aspects include:
  • User Authentication: Ensuring only authorized users can access the database.
  • Access Control: Defining user roles and permissions to restrict access to sensitive data.
  • Encryption: Protecting data by converting it into a coded format to prevent unauthorized access.
  • Backup and Recovery: Implementing measures to back up data regularly and restore it in case of data loss or corruption.
  • Auditing: Monitoring database activity to detect and respond to suspicious behavior.