Database Design and Keys

Questions and Answers

Match the following types of constraints with their definitions:

Primary Key = Uniquely identifies each record in a table Foreign Key = Links rows in one table to rows in another table Not Null = Column cannot have a null value Unique = Ensures all values in a column are different

Match the following integrity mechanisms with their purpose:

Entity Integrity = Ensures each table has a primary key Referential Integrity = Ensures foreign keys point to valid rows Domain Integrity = Ensures values in a column are of a defined type User-defined Integrity = Enforces rules specific to business requirements

Match each indexing strategy with its characteristic:

Clustered Index = Data is stored in the same order as the index Non-Clustered Index = Separate from the actual data storage Unique Index = No duplicate values are allowed in indexed columns Full-text Index = Optimized for searching large text entries

Match the following SQL commands with their actions:

CREATE TABLE = Defines a new table structure ALTER TABLE = Modifies an existing table structure DROP TABLE = Deletes a table and its data INSERT INTO = Adds a new record to a table Signup and view all the answers

Match the following terms related to primary keys with their features:

Composite Key = Primary key made up of two or more columns Surrogate Key = A unique identifier not derived from application data Natural Key = A key derived from the data itself Simple Key = Primary key consisting of a single column Signup and view all the answers

Match each constraint type with its requirements:

Primary Key Constraint = Unique identifier for records, cannot be null Foreign Key Constraint = Links to a primary key in another table Check Constraint = Validates values in a column against a condition Default Constraint = Provides a default value for a column when omitted Signup and view all the answers

Match each scenario with the type of constraint it illustrates:

A person's ID in favorite_food must exist in the person table = Foreign Key Constraint A column that allows duplicate values = No Constraint A table must always have a value for a critical column = Not Null Constraint Control on food preferences, avoiding repetition = Unique Constraint Signup and view all the answers

Match the following keywords with their related functions in SQL:

NOT NULL = Prevents NULL values in a column PRIMARY KEY = Designates a unique identifier for records FOREIGN KEY = Establishes a link between tables DEFAULT = Specifies a value when none is provided Signup and view all the answers

Match the following data integrity mechanisms with their descriptions:

Primary Key = Uniquely identifies a row in a table Foreign Key = Enforces a link between two tables Unique Constraint = Ensures all values in a column are different Check Constraint = Ensures that all values in a column meet a specific condition Signup and view all the answers

Match the types of constraints with their functions:

Primary Key Constraint = Prevents duplicate records in a table Foreign Key Constraint = Maintains referential integrity between tables Unique Key Constraint = Allows only unique values in a specific column Check Constraint = Validates the values in a column against a condition Signup and view all the answers

Match the indexing strategies with their purposes:

Clustered Index = Determines the physical order of data Non-Clustered Index = Creates a separate structure for search Full-Text Index = Optimizes text searches within string columns Bitmap Index = Efficiently handles columns with a limited number of distinct values Signup and view all the answers

Match the following terms related to primary keys:

Surrogate Key = A unique identifier not derived from application data Natural Key = A key that is derived from the actual data Compound Key = A primary key consisting of multiple columns Unique Identifier = A value that ensures the uniqueness of a record Signup and view all the answers

Match the types of relationships associated with foreign keys:

One-to-One = Each record in one table corresponds to one in another One-to-Many = A record in one table can relate to multiple records in another Many-to-One = Multiple records in one table can relate to a single record in another Many-to-Many = Records in one table can relate to multiple records in another table Signup and view all the answers

Match the concepts related to data integrity with their definitions:

Referential Integrity = Ensures that relationships between tables remain consistent Domain Integrity = Ensures the validity of data within a domain Entity Integrity = No primary key value may be null User-Defined Integrity = Custom rules defined by the user to meet specific business needs Signup and view all the answers

Match the following indexing methods with their descriptions:

Unique Index = Prevents duplicate values in indexed columns Composite Index = An index on multiple columns Filtered Index = An index that is applied to a subset of data Spatial Index = Optimizes queries for geographical data Signup and view all the answers

Match the following terms with their characteristics:

Primary Key = Must contain unique values and cannot be null Foreign Key = May contain duplicates and can reference a primary key in another table Unique Key = Ensures that all values in a column are unique, but can be null Check Constraint = Validates specific conditions on data being entered Signup and view all the answers

Match the following types of constraints with their definitions:

Primary Key = A unique identifier for a record in a table Foreign Key = A field that links to a primary key in another table Unique Constraint = Ensures all values in a column are different Check Constraint = Validates the values in a column against a specified condition Signup and view all the answers

Match the following data integrity mechanisms with their purposes:

Entity Integrity = Ensures that every table has a primary key Referential Integrity = Ensures that foreign key values correspond to existing primary keys Domain Integrity = Validates that values meet specific rules within a column User-Defined Integrity = Business rules applied to enforce data quality Signup and view all the answers

Match the following indexing strategies with their benefits:

Single-Column Index = Improves search speed by indexing a single column Composite Index = Combines multiple columns to enhance query performance Full-Text Index = Optimizes searches within large text data Unique Index = Prevents duplicate values within a column while speeding up searches Signup and view all the answers

Match the following foreign key constraints with their conditions:

Cascade Delete = Automatically deletes child records when a parent record is removed Restrict = Prevents deletion of parent records if related child records exist Set Null = Changes the foreign key value to null if the referenced parent record is deleted No Action = Leaves foreign key enforcement to be executed by the database Signup and view all the answers

Match the following primary key constraints with their characteristics:

Must be Unique = No two records can have the same primary key value Cannot be NULL = A primary key must always have a value Single or Composite = Can consist of one column or multiple columns Auto-Increment = Allows automatic generation of numeric primary key values Signup and view all the answers

Match the following data modification SQL statements with their functions:

INSERT = Adds new records into a table UPDATE = Modifies existing records in a table DELETE = Removes records from a table SELECT = Retrieves records from a table Signup and view all the answers

Match the following statements to their associated concepts:

Data Integrity = Ensures the accuracy and consistency of data Cross-Referencing = The relationship between different tables or data sets Normalization = Organizing data to reduce redundancy Foreign Key Constraint = Links tables based on related data Signup and view all the answers

Match the following components of an INSERT statement with their roles:

Table Name = Identifies where to add the new data Column Names = Specifies which fields to populate with data Values = The actual data that will populate the specified columns Not Null Constraint = Ensures certain columns cannot have a null value upon insertion Signup and view all the answers

Study Notes

Table Structure and Constraints

The maximum number of columns in a table varies by server; Microsoft SQL Server allows up to 1,024 columns.
Table row limits are primarily determined by physical storage and maintainability rather than strict database server limits.
Each table in a relational database has a primary key, uniquely identifying each row, such as a customer ID (cust_id).

Primary Key Options

Primary keys can be either natural keys (using existing data) or surrogate keys (generated unique identifiers).
Using first and last names as a primary key is risky due to potential duplicates; cust_id is a safer choice.
Primary keys should remain immutable; changes can complicate data integrity.

Foreign Key Relationships

Tables can include foreign keys to establish associations, such as linking cust_id in an account table to customers.
Redundant data in tables, like cust_id and product_cd in account tables, helps navigate relationships between data.

Handling Null Values

Null indicates the absence of a value when information is not applicable, unknown, or in an empty state.
Columns can be designated as "not null" to enforce data entry requirements.

Creating Tables

Example used: creating a favorite_food table, with a composite primary key (person_id, food) and foreign key constraint linking to the person table.
Foreign keys ensure only valid relationships between tables, preventing orphaned records.

SQL Data Statements

Once tables are established, the four primary SQL data statements are: insert, update, delete, and select.
The insert statement requires specifying the target table, the columns for data entry, and the values to populate those columns.
It is not necessary to provide values for every column if some are defined as nullable.

Generating Numeric Keys

Numeric primary keys can be generated by manually incrementing the highest existing value or letting the database server generate them automatically.

Studying That Suits You

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

Description

This quiz covers essential concepts in database design, focusing on table structure, primary keys, and foreign key relationships. Gain a deeper understanding of how to uniquely identify rows and establish associations between tables in a relational database. Perfect for students delving into database management.