Database Design and Keys

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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