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 (A)</p> Signup and view all the answers

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

<p>1-∞ (D)</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 (A)</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 (D)</p> Signup and view all the answers

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

<p>It identifies cardinality in relationships (B)</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 (D)</p> Signup and view all the answers

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

<p>UPDATE (C)</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 (A)</p> Signup and view all the answers

What is a composite primary key?

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

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

<p>INSERT INTO tableName VALUES (...) (A)</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) (A)</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. (D)</p> Signup and view all the answers

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

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

What is the format for storing dates in SQL?

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

What does ROLLBACK do in a transaction?

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

Which of the following is NOT an ACID property?

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

What is the primary advantage of using a distributed database?

<p>Increased performance and resilience. (B)</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. (A)</p> Signup and view all the answers

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

<p>INSERT INTO SELECT (C)</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. (C)</p> Signup and view all the answers

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

<p>Read uncommitted (D)</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. (B)</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. (C)</p> Signup and view all the answers

To what does fragmentation in a distributed database refer?

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

What is a significant disadvantage of storage fragmentation?

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

Which RAID level offers redundancy by mirroring the original data?

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

What is a key advantage of storage replication?

<p>Improved data availability (B)</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 (B)</p> Signup and view all the answers

Which feature of RAID Level 5 enhances data safety?

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

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

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

How does sharding improve database performance?

<p>By allowing parallel programming across partitions (D)</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 (C)</p> Signup and view all the answers

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

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

Which RAID configuration distributes parity data to enhance reliability?

<p>RAID Level 5 (A)</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 (D)</p> Signup and view all the answers

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

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

What is a potential consequence of using storage fragmentation?

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

What best describes the role of collections in MongoDB?

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

Flashcards

Relational Database

A structured data set organized into tables with relationships between them.

Primary Key

A unique identifier for each row in a table.

Foreign Key

A column in one table referencing a primary key in another.

One-to-Many Relationship

One record in a table links to many records in another.

Signup and view all the flashcards

Many-to-Many Relationship

Many records in one table can link to many in another.

Signup and view all the flashcards

One-to-One Relationship

One record in a table links to only one in another.

Signup and view all the flashcards

Entity Integrity

Primary key must be unique and not null in each table.

Signup and view all the flashcards

Referential Integrity

Foreign key references must point to an existing primary key.

Signup and view all the flashcards

Atomicity

Storing information in its smallest logical parts.

Signup and view all the flashcards

update statement

Used for changing values within a table.

Signup and view all the flashcards

delete statement

Used for removing rows in a table.

Signup and view all the flashcards

Composite Primary Key

Two or more columns working together to uniquely identify a row.

Signup and view all the flashcards

Schema Diagram

Visual representation of database tables and relationships.

Signup and view all the flashcards

Linking Table

A table used to create many-to-many relationships.

Signup and view all the flashcards

Cardinality

Number of times an entity can be related to another.

Signup and view all the flashcards

ON DELETE CASCADE

Deletes child table rows referencing a deleted parent table row.

Signup and view all the flashcards

ON DELETE SET NULL

Sets foreign key values to NULL in child table for a deleted parent row.

Signup and view all the flashcards

Data Types

Choose data types that are appropriate for data, efficient, and simple.

Signup and view all the flashcards

Integer Types

TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT; each has a specific range.

Signup and view all the flashcards

FLOAT

32-bit floating-point type; approximate decimal values, avoid for precise numbers.

Signup and view all the flashcards

DOUBLE

64-bit floating-point type; more precise decimal values.

Signup and view all the flashcards

ASCII

Character encoding using 1 byte per character.

Signup and view all the flashcards

UTF-8

Variable-length character encoding, supports Unicode characters (1-4 bytes).

Signup and view all the flashcards

SQL Text Types

CHARACTER, VARCHAR, NCHAR, NVARCHAR, TEXT; each with a maximum length.

Signup and view all the flashcards

SQL Datetime

YYYY-MM-DD HH:MM:SS format, sortable chronologically.

Signup and view all the flashcards

SQL Transactions

Treat a series of operations as one unit (Atomicity, Consistency, Isolation, Durability).

Signup and view all the flashcards

BEGIN TRANSACTION

Starts a new transaction.

Signup and view all the flashcards

COMMIT

Permanently saves changes from a transaction.

Signup and view all the flashcards

ROLLBACK

Cancels a transaction, restoring the database.

Signup and view all the flashcards

Distributed Database

Data stored across multiple computers.

Signup and view all the flashcards

Distributed Storage

Storing data across multiple machines, offering larger capacity and usually better reliability.

Signup and view all the flashcards

Storage Fragmentation

Splitting data into segments or partitions, stored across multiple machines.

Signup and view all the flashcards

Sharding

A method of data fragmentation for distributed databases, storing data in partitions called arrays.

Signup and view all the flashcards

Arrays (in sharding)

Segments or partitions that hold data in a distributed storage system.

Signup and view all the flashcards

Storage Replication

Creating multiple copies of data across different locations.

Signup and view all the flashcards

RAID

Redundant Array of Independent Disks; a system for data redundancy and performance improvement in distributed systems.

Signup and view all the flashcards

RAID Level 0

No data redundancy; data is lost if a drive fails.

Signup and view all the flashcards

RAID Level 1

Data mirroring, offering a single backup of data.

Signup and view all the flashcards

RAID Level 5

Data redundancy with parity disks, balancing data distribution and backup.

Signup and view all the flashcards

CAP Theorem

A trade-off in distributed databases among consistency, availability, and partitioning.

Signup and view all the flashcards

MongoDB

A document-oriented database.

Signup and view all the flashcards

Collections (MongoDB)

Groups of documents in MongoDB.

Signup and view all the flashcards

Documents (MongoDB)

Basic unit of data in MongoDB, stored using JSON format.

Signup and view all the flashcards

Pymongo

Python library for interacting with MongoDB databases.

Signup and view all the flashcards

Querying (MongoDB)

Selecting and retrieving documents from a MongoDB collection using filters.

Signup and view all the flashcards

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