Relational Database Design Principles
41 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 role does a primary key serve in a relational database table?

  • To store large volumes of data
  • To allow for duplicate entries
  • To ensure each entry is distinct (correct)
  • To link tables through relationships

    • Which relationship type is characterized by one row in a first table being associated with many rows in a second table?

  • Many to Many Relationship
  • Self-Referencing Relationship
  • One to One Relationship
  • One to Many Relationship (correct)

    • What is the purpose of a foreign key in a relational database?

  • It establishes a relationship between two tables (correct)
  • It defines the primary key in a table
  • It categorizes data types for entries
  • It serves as an identifier for records within the same table

    • Which of the following must be true for a primary key?

    <p>It must be unique and non-null</p> Signup and view all the answers

    What notation indicates a one-to-many relationship in a schema diagram?

    <p>1-∞</p> Signup and view all the answers

    How is a many-to-many relationship typically established in a relational database?

    <p>Through a joining or linking table</p> Signup and view all the answers

    Which of the following is an example of enforcing referential integrity?

    <p>Making sure that every foreign key has a valid reference</p> Signup and view all the answers

    What does the Crow’s Foot notation represent in database relationships?

    <p>It identifies cardinality in relationships</p> Signup and view all the answers

    When creating a table in a database, which constraint ensures that a column cannot contain NULL values?

    <p>NOT NULL</p> Signup and view all the answers

    In modifying a database, which command is used to change existing values in a table?

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

    Which of these SQL commands is used to remove a table from a database?

    <p>DROP TABLE tableName</p> Signup and view all the answers

    What is a composite primary key?

    <p>A primary key made of two or more columns</p> Signup and view all the answers

    Which SQL command is used to insert new data into a table?

    <p>INSERT INTO tableName VALUES (...)</p> Signup and view all the answers

    When defining a foreign key in SQL, how is it typically represented?

    <p>FOREIGN KEY (column1) REFERENCES parentTable(primaryKey)</p> Signup and view all the answers

    What happens to the child records when the ON DELETE CASCADE option is used?

    <p>The child records are deleted.</p> Signup and view all the answers

    Which data type has the best precision for storing decimal numbers?

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

    What is the format for storing dates in SQL?

    <p>YYYY-MM-DD HH:MM:SS</p> Signup and view all the answers

    What does ROLLBACK do in a transaction?

    <p>Reverts all changes made during the transaction.</p> Signup and view all the answers

    Which of the following is NOT an ACID property?

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

    What is the primary advantage of using a distributed database?

    <p>Increased performance and resilience.</p> Signup and view all the answers

    How does the SET TRANSACTION ISOLATION LEVEL command affect SQL transactions?

    <p>It determines how other transactions can interact with this one.</p> Signup and view all the answers

    Which SQL command allows data to be populated from one table into another?

    <p>INSERT INTO SELECT</p> Signup and view all the answers

    Which statement about text data types is accurate?

    <p>TEXT is the most flexible without a defined limit.</p> Signup and view all the answers

    What isolation level allows a transaction to see uncommitted changes from other transactions?

    <p>Read uncommitted</p> Signup and view all the answers

    If a user wanted to delete a record from a parent table, what constraint must be checked?

    <p>No child table can reference the parent record.</p> Signup and view all the answers

    What is the primary function of a Transactional Control Language?

    <p>To ensure operations are executed in a structured manner.</p> Signup and view all the answers

    To what does fragmentation in a distributed database refer?

    <p>Relations are partitioned into distinct segments.</p> Signup and view all the answers

    What is a significant disadvantage of storage fragmentation?

    <p>Inaccessibility of data during network failure</p> Signup and view all the answers

    Which RAID level offers redundancy by mirroring the original data?

    <p>RAID Level 1</p> Signup and view all the answers

    What is a key advantage of storage replication?

    <p>Improved data availability</p> Signup and view all the answers

    In the context of distributed databases, which of the following is part of the CAP theorem?

    <p>Consistency, Availability, Partitioning</p> Signup and view all the answers

    Which feature of RAID Level 5 enhances data safety?

    <p>Distribution of parity data</p> Signup and view all the answers

    What is a common issue associated with performing updates in storage replication?

    <p>Complexity in concurrency control</p> Signup and view all the answers

    How does sharding improve database performance?

    <p>By allowing parallel programming across partitions</p> Signup and view all the answers

    What happens if one drive fails in a RAID Level 0 configuration?

    <p>Data loss occurs without redundancy</p> Signup and view all the answers

    What does the '$gte' operator signify in PyMongo queries?

    <p>Greater than or equal to</p> Signup and view all the answers

    Which RAID configuration distributes parity data to enhance reliability?

    <p>RAID Level 5</p> Signup and view all the answers

    What is one of the main advantages of using RAID in distributed databases?

    <p>Enhanced data redundancy and performance</p> Signup and view all the answers

    When using the find() method in PyMongo, what does it return?

    <p>All documents matching a query</p> Signup and view all the answers

    What is a potential consequence of using storage fragmentation?

    <p>Inaccessibility of certain nodes during failure</p> Signup and view all the answers

    What best describes the role of collections in MongoDB?

    <p>Groups of related documents</p> Signup and view all the answers

    Study Notes

    Relational Database Design Principles

    • Relational databases structure data in interconnected tables.
    • Key components include tables, rows, columns, relationships, primary keys, and foreign keys.
    • Primary keys uniquely identify records in a table.
    • These keys are unique, non-null (not empty), immutable (unchanging), and minimal.
    • Primary keys can be single columns or composite (multiple columns).
    • Foreign keys link tables by referencing primary keys in other tables.
    • Relationships between tables can be one-to-many, many-to-many, or one-to-one.
    • One-to-many: One record in one table can be related to many records in another.
    • Many-to-many: One record in one table can be related to many in another, vice versa.
    • One-to-one: One record in each table is related to only one in the other.

    Schema Diagram Notation

    • 1 represents "one" and ∞ represents "many" in relationships.
    • Arrow notation connects many to one side.
    • Crow's foot notation similarly indicates many side.

    Building a Relational Database

    • Identify needed tables with defined columns, data types, and primary/foreign keys.
    • Determine relationships between tables.

    Database Design Decisions

    • Avoid incomplete representation and unnecessary redundancy.
    • Store information in smallest logical units (atomicity).
    • Primary keys should be unique and non-null for each table.
    • Foreign keys should be valid references to primary keys.

    Representing Relationships

    • One-to-many relationships involve linking a table with foreign keys to another table's primary key.
    • Many-to-many relationships use a linking table to connect the related tables.
    • One-to-one relationships use the same primary key in both related tables.

    Data Types

    • Integers: Vary in size from TINYINT to BIGINT, indicating storage capacity.
    • Decimals/Floats: Use floating-point representation for approximate decimal values.
    • Text: Employ character encodings like ASCII, UTF-8, for various text types.
    • Crucial to choose the most appropriate data types based on intended data.

    Modifying Databases

    • UPDATE changes existing values.
    • DELETE removes rows.
    • DELETE syntax with conditions for foreign-key constraints to preclude data loss.
    • ON DELETE CASCADE automatically deletes related rows in child tables when a parent row is deleted
    • ON DELETE SET NULL sets foreign keys referenced to NULL in child tables

    SQL Transactions

    • Transactions ensure data accuracy by treating related operations as a single unit.
    • Atomicity: All operations succeed or none do.
    • Consistency: Data remains valid throughout a transaction.
    • Isolation: Transactions do not interfere with each other.
    • Durability: Data changes survive transaction.
    • Transaction commands: BEGIN TRANSACTION, COMMIT, ROLLBACK.

    Distributed Databases

    • Databases are replicated or spread across multiple computers for enhanced functionality.
    • Distributed database models include sharding and replication.
    • Scalability, performance, and resilience benefits. -Data can be larger. -Data distributed to multiple sites.
    • Properties of distributed databases: fragmentation, replication, resilience, and fault tolerance.

    Storage Replication

    • Redundant data backups enhance data availability in case of failures.
    • Advantages include data availability, parallelism, reduced data transfer.
    • Disadvantages include increased cost of updates, and complex concurrency control.

    Redundant Array of Independent Disks (RAID)

    • RAID levels provide data redundancy, and performance improvements.

    Studying That Suits You

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

    Quiz Team

    Related Documents

    CS 217-0 Final PDF

    Description

    Explore the key principles of relational database design, focusing on the structure of data in interconnected tables. Learn about primary keys, foreign keys, and various types of relationships between tables. This quiz will test your knowledge of schema diagrams and notation used in relational databases.

    More Like This

    Use Quizgecko on...
    Browser
    Open
    Browser
    Browser