Database Normalization Concepts

Table of Contents

• A.1 Basic Concepts
  • 1.1 Data and Information: Data is a collection of raw facts, while information is data put into context.
  • 1.2 Information System and Database: A database is a structured collection of related data within a larger information system. Databases are distinct from spreadsheets.
  • 1.3 Need for Databases: Databases are crucial for efficient data organization, integrity, security, and scalability.
  • 1.4 Transactions, States, and Updates: Transactions are logical units of work, encompassing operations that either succeed completely or not at all. States track database conditions, and updates modify the data.
  • 1.5 Database Transaction Properties (ACID): Atomicity, Consistency, Isolation, and Durability ensure reliable transactions.
  • 1.6 Concurrency: Techniques like locking and time-stamping manage simultaneous access to data.
  • 1.7 Queries and Updates: Queries retrieve data; updates modify it. Database systems use specialized languages like SQL to interact with data.
  • 1.8 Data Validation and Verification: Data validation checks if data follows predefined rules and constraints; verification ensures data accuracy.

A.2 The Relational Database Model

• 2.1 DBMS and RDBMS

  • Database Management System (DBMS): A collection of programs for organizing, storing, modifying, and retrieving data.
  • Relational Database Management System (RDBMS): A type of DBMS that uses relational structures.

• 2.2 Functions and Tools of a DBMS

  • Data modelling, storage, retrieval, concurrency control, and data integrity are DBMS functionalities. Backup and recovery are also critical.

• 2.3 Data Security

  • Data security, including authentication, controlling access, encryption, backups, and tracking activities, is essential.

• 2.4 Schema

  • Schemas define a database's logical structure – how data is organized. Conceptual, logical, and physical schemas detail the overall layout, the software-specific aspects, and physical storage details respectively. Data dictionaries describe data elements.

• 2.5 Conceptual, Logical, and Physical Schema

  • Conceptual Schema: high-level view of data's organization.
  • Logical Schema: detailed, software-specific view of the data structures.
  • Physical Schema: details the physical storage implementation.

• 2.6 Data Dictionary

  • Contains metadata about data, such as meaning, usage, and relationships. A vital reference tool for a database.

• 2.7 Data Definition Language

  • SQL sub-set used to define and alter database structures, enforce data integrity, and control access.

• 2.8 Importance of Data Modeling

  • Data modeling provides structural blueprints, supporting data consistency and analysis. Crucial for efficient and understandable design.

• 2.9 Database Terms

  • Database terminology includes key concepts such as tables, records, fields, primary keys, secondary keys, and foreign keys.

• 2.10 Relationships Within Database

  • Database relationships are detailed (One-to-one, One-to-many, Many-to-many); cardinality describes uniqueness and relationships between data attributes.

• 2.11 Issues with Redundant Data

  • Redundant data causes inconsistencies, making data maintenance cumbersome. Storage requirements increase.

• 2.12 Referential Integrity

  • Rules for maintaining consistent relationships between database tables. Foreign keys are used to enforce such relationships.

• 2.13 Normalization

  • The process of organizing data in a database to reduce redundancy and increase data integrity. Various normal forms (1NF, 2NF, 3NF) achieve various degrees of normalization.

• 2.14 Characteristics of a Normalized Database

  • Minimal data redundancy, consistent data, and reduced update anomalies are hallmarks of well-designed normalized databases.

• 2.15 Different Data Types

  • Data types used in databases include words (VARCHAR, CHAR), numbers (integers, floating-point, decimals), binary data (BLOB), date/time, currency, and objects.

• 2.16 Entity-Relationship Diagram (ERD)

  • Diagrams showing entities, attributes, and relationships in a database.

• 2.17 Constructing a 3NF Database

  • Demonstrating the design stages in creating a normalized and well-organized database using a 3NF model.

• 2.18 Views

  • Subset of a database based on queries. Frequently used queries can be saved as database views.

• 2.19 Simple vs Complex Queries

  • Simple queries involve single tables and simple fields; complex queries involve multiple tables and complex operations.

• 2.20 Methods to Construct a Query

  • Methods include graphical user interfaces (e.g., visual querying) and query languages like SQL.

• A.3 Further Aspects of Database Management

  • 3.1 Database Administrator (DBA)

    • The DBA is responsible for organizing and maintaining a database's infrastructure, including physical storage, security, performance, and user management.

  • 3.2 Interaction with End-Users

    • User interaction with the database through specialized tools, interfaces (command lines, web interfaces, and mobile apps), and query languages are discussed.

  • 3.3 Database Recovery

    • The process of restoring a database to a consistent state after failure or errors, along with different types of data loss and recovery methods(e.g. log recovery, point-in-time recovery, rollback).

  • 3.4 Integrated Database Systems Function

    • Centralised, unified data management encompassing data collection, integration, cleaning, storage, and efficient access through tools.

  • 3.5 Use of Database

    • Examples of database usage, such as stock control (inventory management), are provided.

  • 3.6 Privacy of Personal Data and Responsibilities

    • The ethical and legal responsibilities of organizations caring for data are discussed; legal and social aspects governing data are discussed.

  • 3.7 Data Matching

    • The process of detecting pieces that belong to the same entity from different data sets.

  • 3.8 Data Mining

    • Analyzing large data sets to identify patterns/trends; this includes techniques and applications.

  • A.4 Further Database models and characteristics

    • 4.1 Database models: These sections detail hierarchical, network, and relational database models.

    • 4.2 Objectoriented vs Relational Databases: The features and differences in data definition and manipulation are examined.

    • 4.3 Data Warehousing: Concepts such as data warehousing, data mart creation, and ETL processes are covered. The strategic considerations are examined.

    • 4.6 Data Updates in Data Warehousing: Methods to manage real-time updates to data warehouses are explored.

    • 4.7 Evaluation and Data Warehousing: Pros and cons of data warehousing are detailed, including costs, complexity, maintenance, and benefits (e.g., data consistency, improved decision making).

    • 4.8 Need for ETL (Extract, Transform, Load): Data preparation processes for improved data quality and consistency are introduced.

    • 4.9 ETL for Data Cleaning: The ETL process steps for data preparation are detailed (extract, transform, loading); these steps ensure data consistency. Error handling and data quality checks are included.

    • 4.10 Discovering Patterns Using Data Mining - Various clustering methods and techniques are detailed including K-means and hierarchical clustering.

    • 4.11 Evaluation of Data Mining - Case studies including fraud detection, targeting marketing, and social impacts are discussed.

    • 4.12 Predictive Modeling - A statistical technique used to forecast future outcomes based on historical data; pros, cons, and concepts are investigated.

    • 4.13 Database Segmentation - Process of splitting a database into subsets/groups; techniques like behavioural and value-based segmentation are investigated.

    • 4.14 Link Analysis - Identifies relationships and connections between data points, mapping complex networks. Models like matrix models are detailed.

    • 4.15 Deviation Detection - Detecting unusual data points and deviations using statistical methods and techniques.

    • Miscellaneous: This section covers various aspects not directly related to a specific model area like constraints on types of relationships.