Module 3: SQL DML (Data Manipulation Language), Physical Data Organization
SQL DML
SQL Data Manipulation Language (DML) is used for managing data within database objects such as tables. It includes commands like SELECT, INSERT, UPDATE, and DELETE.
Example: To update a student’s grade level in a database, you might use the following SQL DML command:
UPDATE students SET grade_level = 11 WHERE student_id = 123;
Queries on Single and Multiple Tables
Queries can be executed on single or multiple tables to retrieve specific information. Single-table queries involve one table, while multi-table queries (joins) involve two or more tables.
Example: To find the names of students who are enrolled in a specific class, a multi-table query would be used:
SELECT students.name FROM students
JOIN enrollments ON students.student_id = enrollments.student_id
WHERE enrollments.class_id = 'MATH101';
Nested Queries
Nested queries, or subqueries, are queries within queries. They allow for complex data retrieval operations.
Example: To find students who have the same grade level as a particular student, you might use a nested query:
SELECT name FROM students
WHERE grade_level = (SELECT grade_level FROM students WHERE name = 'John Doe');
Aggregation and Grouping
SQL provides functions to perform calculations on sets of rows, returning a single value. Grouping allows for aggregation across multiple groups of rows.
Example: To calculate the average grade of students in each grade level, you would use:
SELECT grade_level, AVG(test_score) FROM students
GROUP BY grade_level;
Views, Assertions, Triggers
- Views are virtual tables created by a query.
- Assertions are conditions that must always be true in the database.
- Triggers are procedures that are automatically executed in response to certain events on a particular table.
Example: A trigger could be set to automatically update the number of available seats in a class when a new enrollment is recorded.
SQL Data Types
SQL data types define the type of data that can be stored in a column of a table. They include numeric, string, date/time, and more.
Example: In a table storing employee information, the employee ID might be an integer, the name a string, and the hire date a date data type.
Physical Data Organization
This refers to how data is physically stored in a database, which can affect performance and storage efficiency.
Example: Data might be stored in heap files, where records are inserted in no particular order, or sorted files, where records are kept sorted based on a key.
Heap Files, Indexing
- Heap Files: An unordered set of records, stored in no particular order.
- Indexing: A data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure.
Example: An index might be created on the student ID column of the students table to speed up queries searching for students by their ID.
B-Trees & B±Trees
These are types of index structures that help maintain sorted data and allow searches, sequential access, insertions, and deletions in logarithmic time.
Example: A B-tree might be used to index the student names in a database, allowing for quick alphabetical searches.
Extendible Hashing
Extendible hashing is a type of hashing that can grow and shrink dynamically, which helps in evenly distributing the entries across the buckets.
Example: Extendible hashing might be used to distribute student records across different buckets based on their student ID.
Indexing on Multiple Keys
Creating indexes on multiple columns can speed up queries that test conditions on these columns.
Example: An index on both the grade level and test score columns of the students table could speed up queries that look for students within a certain grade level and score range.
Module 4: Normalization
Introduction to Normalization
Normalization is a systematic approach of decomposing tables to eliminate data redundancy (repetition) and undesirable characteristics like Insertion, Update, and Deletion Anomalies. It is a multi-step process that puts data into tabular form by removing duplicate data from the relational tables.
Example: If a database table stores both customer details and their order details, normalization can separate this into two tables: one for customers and one for orders, linked by a customer ID.
Functional Dependency
Functional dependency (FD) is a relationship that exists when one attribute uniquely determines another attribute. If A determines B, then all rows in the table that agree on the value of A also agree on the value of B.
Example: In a table of employee records, the employee ID (A) functionally determines the employee’s name (B), because each ID is associated with exactly one name.
Armstrong’s Axioms
Armstrong’s Axioms are a set of rules used to infer all the functional dependencies on a relational database. They include reflexivity, augmentation, and transitivity.
Example: Using Armstrong’s Axioms, if we know that employee ID determines the name, and the name determines the email, we can infer that employee ID also determines the email.
First, Second, Third, and Boyce Codd Normal Forms
These normal forms are rules for database normalization that must be followed to reduce redundancy and dependency.
- First Normal Form (1NF): Eliminates duplicate columns from the same table and creates separate tables for each group of related data.
- Second Normal Form (2NF): Removes subsets of data that apply to multiple rows of a table and places them in separate tables.
- Third Normal Form (3NF): Requires that all the attributes in a table are dependent only on the primary key.
- Boyce-Codd Normal Form (BCNF): A stronger version of the third normal form that requires every determinant to be a candidate key.
Example: To bring a table into 3NF, you might remove a non-primary attribute that does not depend on the primary key, such as a manager’s name that is dependent on the department rather than the employee.
Lossless Join and Dependency Preserving Decomposition
- Lossless Join Decomposition: Ensures that when a database is normalized, no data is lost, and original data can be retrieved by joining the tables.
- Dependency Preserving Decomposition: Ensures that all functional dependencies are still represented after decomposition.
Example: When decomposing a table into two tables, one for employees and one for departments, a lossless join would ensure that joining these two tables on the department ID would result in the original table.
Posts
Hello, my name is Jack Sparrow. I'm a 50 year old self-employed Pirate from the Caribbean. 
