Comprehensive Database Administration and SQL Study Guide
Fundamentals of Relational Databases and Data Storage
Database systems are structured methods for storing, organizing, and retrieving data efficiently. Central to relational databases is the concept of structured storage and predefined relationships.
Data Storage Structures:
Tables: The primary location where data is stored in a relational database. A table is defined as a collection of rows and columns that stores data in a structured format.
Rows (Records): A row represents a single entry or a unique record within a table. Each row holds information for one specific entity (e.g., a specific student in a school database).
Columns (Attributes/Fields): A column is a vertical collection of data representing a specific attribute of an entity (e.g., "FirstName" or "StudentID").
Unique Metadata: In a database table, each column name must be unique. While individual values within a field are not always required to be unique (unless constrained), every row in a table must generally be unique to ensure data integrity.
Relational Definitions:
Required Elements: Defining a column requires at least a Name and a Data Type.
Storage Allocation: The amount of storage space allocated to a value in a column is defined by its data type.
SQL Server Data Types
Choosing the correct data type is essential for data integrity, precision, and minimizing storage overhead.
Integer (Whole Number) Types:
: Used to store Boolean values (, , ).
: Stores whole numbers ranging from to . It is the most economical choice for very small ranges.
: Stores whole numbers from to .
/ : Stores whole numbers from to .
Approximate and Fixed-Point Numeric Types:
/ : Used for approximate numeric values with floating-point precision.
/ : Used for exact numeric values. (precision) is the total number of digits; (scale) is the number of digits after the decimal point.
Example: can store a value like (total 4 digits, 1 decimal), but cannot store (5 digits total).
: Specifically designed to run financial functions against charged amounts, providing appropriate precision for currency.
Character and String Types:
: A fixed-length string. If the data is shorter than the size, it is padded with spaces. Ideal for data of a constant length, such as account numbers with a specific format (e.g., one letter and four digits).
: A variable-length string. It only uses storage space for the actual characters entered plus a small overhead. Ideal for names or descriptions of varying lengths.
/ : Unicode versions of string types, used to record product names or data in different languages and special characters.
: A legacy type used for very large fixed-length or variable-length character data.
Date and Time Types:
: Stores only the date ( to ).
: Range from to with a precision of milliseconds.
: An extension of that supports a larger date range ( to ) and higher fractional precision (up to 7 digits).
: Range from to with precision to the nearest minute. Seconds values of or less are rounded down to the nearest minute, while or more are rounded up.
The Relational Model and Keys
Relationships and constraints ensure that data remains consistent and identifiable.
Primary Key: A constraint that uniquely identifies each row in a table. It cannot contain values. Tables can have a single column primary key or a Composite Primary Key, which is a combination of two or more columns used to uniquely identify a record (e.g., joining two IDs in a mapping table).
Foreign Key: An attribute in one table that points to a Primary Key in another table. It enforces Referential Integrity, ensuring that a record cannot be created with a link to a non-existent parent record (e.g., an employee cannot be assigned to a department that does not exist).
A table can contain multiple foreign keys.
A value in a foreign key indicates no relationship for that specific record.
Relationship Types:
One-to-One (): e.g., An employee and their specific passport record.
One-to-Many (): e.g., A teacher who can teach multiple classes, but each class has only one teacher.
Many-to-Many (): e.g., Students enrolling in multiple courses, and courses having multiple students. This usually requires a junction table.
Constraints: Rules or limits placed on columns to ensure data validity (e.g., , , , ).
Structured Query Language (SQL) Categories
SQL (Structured Query Language) is the standard language for managing relational databases. Its primary implementation in SQL Server is T-SQL (Transact-SQL).
Data Definition Language (DDL): Used to add, remove, and modify the structure of the database.
: Create objects like databases, tables, or views.
: Modify existing database structures (e.g., adding a column to a table).
: Remove objects entirely, including definitions, data, indexes, and permissions.
: Removes all rows from a table without logging individual row deletions, but preserves the table structure.
Data Manipulation Language (DML): Used to retrieve and modify the data within the structures.
: Retrieves rows from a table.
: Adds new rows of data.
: Modifies existing data.
: Removes one or more records.
General Syntax Rules:
SQL keywords are not case sensitive (though often capitalized for readability).
Semicolons () are used to separate individual SQL statements.
The command is used in SQL Server to terminate and execute a batch of statements.
Advanced Selection and Data Retrieval
Filtering and Logic:
: Returns rows that meet a specific condition.
: Logical operators to combine or exclude parameters.
: Pulls records based on a range.
: Used for pattern matching (e.g.,
LIKE '%chocolate%'finds string containing that word).: Specifically checks for the absence of data.
Formatting Results:
: Sorts the result set. Use for ascending (default) and for descending (highest to lowest).
: Filters out duplicate values from the output.
: Specifies the number or percentage of records to return (e.g.,
SELECT TOP 10).: Used to create aliases for columns or tables.
Concatenation: Using the plus () operator to combine strings (e.g.,
FirstName + ' ' + LastName). If any part of the concatenation is , the entire result may return .
Case Logic:
Statement: Allows for conditional logic within a query.
Syntax:
CASE WHEN condition THEN result ELSE alternative END.
Aggregate Functions and Grouping
Aggregate functions perform calculations on a set of values and return a single value.
Common Functions:
: Returns the number of rows in a table or group.
: Calculates the total of a numeric column.
: Calculates the average.
: Returns the highest or lowest value.
Clauses:
: Groups rows that have the same values into summary rows (required when using aggregates with non-aggregated columns).
: Similar to , but used to filter results after they have been grouped by an aggregate function.
Joins and Set Operators
Joins: Combine columns from two or more tables based on a related column.
: Returns records that have matching values in both tables.
: Returns all records from the left table and the matched records from the right table. Records in the left table with no match in the right table will show for the right table's columns.
(Cross Join): Operates on tables without a join condition, returning every possible combination of rows.
Set Operators:
: Combines the results of two or more queries into a single set, excluding duplicates.
: Returns only rows that appear in both result sets.
: Returns rows from the first query that do not appear in the second query.
Subquery: A query nested inside another query (e.g., in a clause) to further filter results.
Database Design and Normalization
Normalization is the process of organizing data to reduce redundancies and dependencies.
Normal Forms:
First Normal Form (1NF): Table has no repeating groups and contains a primary key.
Second Normal Form (2NF): Meets 1NF and ensuring that no non-prime attribute is functionally dependent on a part of a composite key.
Third Normal Form (3NF): Meets 2NF and ensures every non-prime attribute is non-transitively dependent on the primary key (no "columns not dependent on the primary key").
Fourth Normal Form (4NF): Every multivalued dependency is eliminated.
Fifth Normal Form (5NF): Every join dependency is eliminated.
Denormalization: The intentional process of combining tables to introduce redundancy, typically performed to improve read performance and simplify complex queries.
Database Objects: Indexes, Views, and Procedures
Indexes: Structures used to improve the performance of statements.
Clustered Index: Physically sorts the data in the table based on the indexed column. A table can have only one clustered index.
Non-clustered Index: A separate structure from the table that contains pointers to the data. Improves search speed without physically reordering the table.
Views: A virtual table created by a query.
Purpose: To limit access to specific rows/columns, simplify complex joins, or present a tailored view of data.
If a base table is updated or deleted, the view's result set reflects those changes (or becomes empty/invalid).
Stored Procedures: A named group of SQL statements that can be executed as a unit.
Advantages: Improved performance (pre-compiled), complex logic storage, and security.
Users require the permission to run them.
Functions:
Scalar Functions: Return a single value.
Table-Valued Functions: Return a result set (table).
Difference: Unlike procedures, functions must return a value and cannot contain certain transaction types.
Security, Backup, and Maintenance
Authentication and Access:
: Enables a new person to authenticate to the database.
Roles: A set of permissions assigned to users (e.g.,
db_ownerfor full access).Permissions:
: Allows access.
: Removes previously granted access.
: Explicitly prohibits access.
: Allows a user to delegate their permissions to others.
Backup Strategies:
Full Backup: A complete copy of the entire database.
Differential Backup: Copies only data that has changed since the last full backup.
Incremental (Transaction Log) Backup: Backs up data modified since the last backup of any kind (full or incremental).
Simple vs. Full Recovery Model: The simple model provides more work-loss exposure but fewer log file backups than the full model.
Administrative Details:
File Extensions:
: Primary data file.
: Transaction log file.
: Secondary data file.
Ports: SQL Server default ports are and .
Cascade Delete: A feature where deleting a row in a parent table automatically deletes corresponding rows in child tables (Referential Integrity).
Transactions: Ensure data integrity. If a transaction fails (e.g., 40 logic steps out of 100 fail), the entire operation is rolled back, and no rows are actually deleted/modified.