Database Normalization Concepts
46 Questions
0 Views

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to lesson

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What is the main purpose of cascading delete in a database?

  • To link unrelated tables
  • To manually delete child records
  • To automatically delete related records in child tables (correct)
  • To prevent deletion of parent records

    • In the first normal form, values in fields can be non-atomic.

    False (B)

    What is a composite primary key?

    A primary key made from two or more fields.

    During normalization, tables are arranged into ______, ______, and ______.

    <p>1NF, 2NF, 3NF</p> Signup and view all the answers

    Match the following normalization forms with their descriptions:

    <p>1NF = Values must be atomic and unique 2NF = Removes partial dependencies 3NF = Removes transitive dependencies Normalization = Organizing data to eliminate redundancy</p> Signup and view all the answers

    What is a primary method to avoid data inconsistency?

    <p>Atomic transactions (C)</p> Signup and view all the answers

    Normalisation is a technique that can help reduce storage requirements.

    <p>True (A)</p> Signup and view all the answers

    What is the role of foreign keys in maintaining referential integrity?

    <p>Foreign keys ensure the relationships between tables remain consistent.</p> Signup and view all the answers

    ______ inconsistencies can lead to problems during data modification, including increased errors in insertion, deletion, or updates.

    <p>Data</p> Signup and view all the answers

    Match the following techniques to their primary purposes:

    <p>Normalisation = Reduce redundancy Cascading updates = Automatically propagate changes Data cleansing = Correct errors in data Referential integrity = Maintain accurate relationships between tables</p> Signup and view all the answers

    Which of the following best describes a database?

    <p>An organized collection of related data (B)</p> Signup and view all the answers

    An information system only consists of databases.

    <p>False (B)</p> Signup and view all the answers

    What are the key properties of transactions known as ACID?

    <p>Atomicity, Consistency, Isolation, Durability</p> Signup and view all the answers

    The main function of a spreadsheet is to ______ data, while a database is primarily for ______ data.

    <p>manipulate, store</p> Signup and view all the answers

    Match the terms to their definitions:

    <p>Atomicity = Ensures all operations are completed or none are completed at all Consistency = A change must retain the overall state of the database Isolation = Changes made by one transaction are not visible to others until completion Durability = Changes are permanently stored in non-volatile storage</p> Signup and view all the answers

    What is the primary purpose of data mining?

    <p>To explore large sets of data in search of patterns (C)</p> Signup and view all the answers

    Which of the following is a technique used for concurrency control?

    <p>Locking (B)</p> Signup and view all the answers

    Concurrency allows multiple users to access and manipulate data simultaneously.

    <p>True (A)</p> Signup and view all the answers

    Fuzzy matching can only be used when data is identical.

    <p>False (B)</p> Signup and view all the answers

    What problem occurs when an update is lost due to being overwritten by another transaction?

    <p>Lost update problem</p> Signup and view all the answers

    What are the two primary techniques used for matching patient records?

    <p>Exact matching and fuzzy matching</p> Signup and view all the answers

    A __________ database organizes data into a tree-like structure.

    <p>hierarchical</p> Signup and view all the answers

    To ensure data integrity, databases use a ______ mechanism which ensures that only one user can update a record at a time.

    <p>locking</p> Signup and view all the answers

    Which of the following is NOT a characteristic of a database?

    <p>Manipulates data (A)</p> Signup and view all the answers

    Match the data mining techniques with their examples:

    <p>Classification = Fraud detection Clustering = Grouping customers Association rules = Finding commonly purchased products Regression = Predicting loan risks</p> Signup and view all the answers

    Which characteristic of a normalized database helps in minimizing data redundancy?

    <p>Minimal data redundancy (A)</p> Signup and view all the answers

    Which implication relates to the accuracy of matches in patient records?

    <p>Enhances the integrity of data (A)</p> Signup and view all the answers

    Privacy concerns arise when personal identifiable information is exposed in data handling.

    <p>True (A)</p> Signup and view all the answers

    Transitive dependencies are allowed in a 3rd normal form database.

    <p>False (B)</p> Signup and view all the answers

    Name one advantage of a network database system.

    <p>Allows multiple relationships between records.</p> Signup and view all the answers

    What does the acronym BLOB stand for in the context of data types?

    <p>Binary Large Object</p> Signup and view all the answers

    The type __________ is typically used for phone numbers in databases, allowing for a numerical starting digit.

    <p>varchar</p> Signup and view all the answers

    Match the following data types with their descriptions:

    <p>Varchar = Varying length string Integer = Whole numbers Float = Approximate fractional numbers Boolean = True or false value</p> Signup and view all the answers

    Which type of update anomalies are significantly reduced in a normalized database?

    <p>Update anomalies (C)</p> Signup and view all the answers

    A database structure can remain largely unchanged when extending new data types in a normalized database.

    <p>True (A)</p> Signup and view all the answers

    What is the primary benefit of organizing data into smaller, manageable tables in a database?

    <p>Improved query performance</p> Signup and view all the answers

    What is a primary disadvantage of using OLAP cubes?

    <p>High cost (D)</p> Signup and view all the answers

    ETL processes are only required for small datasets.

    <p>False (B)</p> Signup and view all the answers

    What does the 'E' in ETL stand for?

    <p>Extract</p> Signup and view all the answers

    Data ______ involves removing inaccuracies and inconsistencies.

    <p>cleansing</p> Signup and view all the answers

    Match the following clustering techniques with their definitions:

    <p>K-means clustering = Partitions n observations into k clusters based on mean distances Hierarchical clustering = Creates a tree of clusters to show their arrangements Density-based clustering = Connects areas of high density into clusters</p> Signup and view all the answers

    Which loading strategy updates data in real-time?

    <p>Trickle loading (A)</p> Signup and view all the answers

    Data mapping involves matching source data fields to destination counterparts.

    <p>True (A)</p> Signup and view all the answers

    What is the purpose of the 'Load' phase in ETL?

    <p>To load processed data into the target system.</p> Signup and view all the answers

    In cluster analysis, ______ analysis partitions a data set into subsets sharing common traits.

    <p>cluster</p> Signup and view all the answers

    In K-means clustering, what does 'initialise' refer to?

    <p>Setting k initial cluster centers (A)</p> Signup and view all the answers

    Study Notes

    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.

    Studying That Suits You

    Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

    Quiz Team

    Related Documents

    Computer Science Database PDF

    Description

    Test your knowledge on database normalization, including cascading deletes, composite keys, and referential integrity. This quiz covers essential definitions and concepts fundamental to database design and management.

    More Like This

    Use Quizgecko on...
    Browser
    Open
    Browser
    Browser