Database Management Systems (RDBMS) & Microsoft Fabric SQL

Explore the entity-relationship model and its diagrams to define entities and relationships, and learn database blueprints, attributes, and participation and cardinalities.

Define an entity as a data object and explain how a table holds records that represent it, distinguishing strong entities with primary keys from weak entities that rely on relationships.

Understand relationships as associations between entities, shown by the diamond symbol; distinguish strong and weak relationships and recognize weak entities with double diamonds in examples like employee and organization.

Define the degree of a relationship by counting participating entities, covering unary, binary, ternary, and n-ary relations with examples like manager-employee and employee works for department.

Explore the four relationship cardinalities - one-to-one, one-to-many, many-to-one, and many-to-many - through practical examples like person and passport, customer orders, student and college, and student projects.

Identify partial and total participation in entity relationships by examining minimum and maximum cardinality to determine who participates and to what extent.

Learn about functional dependency and its role in normalization. Identify determinants and dependent attributes through example-driven explanations of when dependencies hold.

This example demonstrates attribute closure by iteratively adding derived attributes to a set until no new attributes appear and consecutive iterations converge.

Explore the applications of attribute closure to derive additional functional dependencies, identify candidate keys and superkeys, and obtain irreducible (canonical/minimal) sets for normalization.

Apply the candidate key search by testing attribute subsets; observe that each individual attribute can be a candidate key, and the pair b and c also qualifies.

Identify candidate keys in a relation by evaluating attribute subsets and computing their closures to determine all attributes. Reveal how specific subsets yield a single candidate key.

Identify candidate keys in two examples by analyzing how attributes determine all attributes, noting prime attributes and functional dependencies, and showing multiple candidate keys.

Explore the equivalence of two sets of functional dependencies in a relation, using example 2 from application 3, and determine how to verify when two dependency sets are identical.

This lecture presents example 3 on the equivalence of functional dependencies, showing how to derive attributes like B and C from a given set and why they are not equivalent.

Present an algorithm for irreducible sets and the minimal cover of functional dependencies, detailing decomposition, essential attribute tests, and closure computation.

Explains deriving the canonical form of a relation through stepwise decomposition, checking essential attributes, analyzing functional dependencies, and obtaining minimal sets by considering closures and subsets.

Define first normal form by ensuring all table cells hold atomic, single-valued attributes, removing multi-valued and composite attributes, and restructuring data like multiple phone numbers into separate records.

Learn second normal form by ensuring every non-key attribute fully depends on the primary key, removing partial dependencies, and decomposing tables into patient and drug relations to reduce redundancy.

Apply third normal form by eliminating partial dependencies and reducing redundancy. This minimizes insertion and deletion anomalies by dividing data into related tables with appropriate keys.

Examine third normal form through several examples, identify primary keys and non-key attributes, resolve partial dependencies, and decompose a relation into smaller tables to achieve 3nf.

Decompose a complex relation to third normal form by identifying keys, analyzing partial and full functional dependencies, and creating separate tables for dependencies, keys, and non-key attributes.

Analyze a third normal form example by identifying candidate keys and prime attributes, and verify second and third normal forms via functional and partial dependencies.

Learn BCNF, a stricter form than 3NF where every determinant is a superkey. Note how functional dependencies and candidate keys shape relations and reduce redundancy.

Master the select statement structure and evaluation order, retrieving data from tables using from and where, then applying group by, having, and order by, plus distinct and set operators.

Master select statements to retrieve data from the EMV table, choose specific columns or use *, control display order, and handle nulls, salaries, and commissions.

Explore the where clause introduction by filtering records with conditions, selecting specific columns or all columns, and using operators like equals to extract department 10 results.

Explore how to use the where clause operators in SQL queries, including greater than, less than, equals, and not equal, with practical examples for salaries and job categories.

Explore how to use the where clause with and, or, and not to filter employees by salesmen status, salary and department, including negations and combinations.

Explore aggregate functions with count: use count(*), count(column), and count(distinct column) to count records, handle nulls, and remove duplicates in SQL queries.

Explore how the group by clause works with select statements to group rows by department numbers and apply aggregate functions such as count, sum, average, min, and max.

Explore how the group by clause handles multiple columns, forming distinct groups by department number and job, and apply aggregates such as count, min, max, and average on each group.

Apply set manipulation operators to relations by enforcing equal attribute counts, compatible corresponding domains, and automatic duplicate elimination for union, intersection, and minus.

Explore union, intersection, and minus set operators in relational queries, learn the compatibility rules, and see how duplicates are handled across depositor and borrower data.

Understand how joins merge data from multiple tables using a common field, and how a Cartesian product or cross join yields row pairs before conditions create inner and outer joins.

Discover how joins in relational databases combine data from two or more tables, focusing on theta (conditional) joins and the role of cartesian products and filtering conditions.

Explore equi joins in relational databases by joining student and course tables on the equality condition to retrieve rule numbers and associated student names.

Explore self joins that connect a table to itself to reveal manager relationships, using an employee table and proper join conditions.

Explore outer join concepts, including left outer join, right outer join, and full outer join, and learn how they return both matching and nonmatching records.

learn how a full outer join combines left and right tables to return all records, including unmatched ones from both sides, showing matching and non-matching rows.

Practice sql queries to list clerks using select *, filter by department number, and order by department ascending and job descending; also display distinct job titles in descending order.

Learn to write select statements to retrieve data from EMV table, using star to select all columns or specific columns, and grasp primary and foreign keys, nulls, and department relations.

Explore relational algebra and calculus as the theoretical foundation for representing database queries, and learn core operators—projection, selection, union, cartesian product, joins, rename, and division.

Master set manipulation operators in database management systems: union, intersection, and difference; ensure matching attribute counts and compatible domains, and note that duplicates are eliminated by default.

this lecture explains the set difference operator in relational algebra, showing how to derive records in one relation but not in another using minus.

Use the rename operator to assign a new name to a relation or to its attributes, creating aliases for expressions.

Explore joins in relational algebra, from Cartesian product and conditional selection to inner and outer joins, combining data across two or more relations.

Explore conditional joins, or theta joins, by matching student and course tables on a specific condition using comparison operators, producing related results without a Cartesian product.

Learn the outer join concept, comparing inner and outer joins, and examine left, right, and full outer joins, showing how matching and nonmatching rows appear in the output.

Illustrate the right outer join in relational algebra by returning all right-table records, matching with the left where possible, and showing non-matching left rows as null.

Examine the division operator in relational algebra, its proper-subset requirement, and how it yields attributes a minus b, illustrated by faculty teaching all courses.

Demonstrate the division operator in relational algebra through two examples, linking publisher and category and showing customers with a loan across all branches.

Explore the concept of a transaction as a single logical unit of work, illustrated by transferring funds, and learn how serial versus concurrent execution affects data consistency and conflict resolution.

Define schedules as transaction operations; contrast serial schedules, where one transaction completes before the next, with concurrent schedules, where transactions progress simultaneously while preserving consistency and ACID properties.

Analyze how concurrent schedules cause the last update problem and inconsistent data, illustrating a right trade conflict and its impact on final values in a transaction.

Contrast cascading rollbacks with cascadeless recoverable schedules to illustrate how a failure can trigger others to rollback, while cascadeless schedules prevent propagation by preserving committed data.

Describe strict schedules in databases, where a read or write on a data item proceeds only after the transaction commits or aborts, ensuring access to committed or original values.

Practice identifying transaction properties such as dirty reads, uncommitted data, cascade versus cascadeless schedules, and recoverable versus not recoverable and strict versus non-strict schedules by tracing commit and abort.

Explore how serial schedules count equals three factorial, yielding six possibilities, and learn a formula for non-serial schedules, with an example yielding 56 non-zero schedules.

Examine conflict serializable schedules through examples 2 and 3, analyzing read and write operations on data items x and y across two transactions to determine serializability.

Explore how to combine conditions with and, or, and not to filter records in SQL queries. Learn how these operators control which records display.

Analyze how a primary index partitions 30,000 records into 3,000 data blocks and 3,000 index records into 45 index blocks, reducing search steps from about 12 to 6–7.

Analyze spanned and unspanned storage strategies for records. Spanned uses multiple blocks for variable-length data; unspanned keeps one block per record for fixed-length data, trading space for speed.

A sparse index stores index records for only a subset of data items and points to blocks, while a dense index creates a record for every item for faster lookups.

Indexing enables fast retrieval of database records using a two-column index (primary/candidate key and block pointer); it covers single-level and multilevel indexes, including primary, clustered, and secondary types.

Create a cluster index by grouping identical values into clusters and assigning one index record per distinct value, stored across data pages and index pages per table.

Explore how secondary indexing handles 30,000 records, calculates index records per block, and estimates total index blocks, contrasting with primary indexing in terms of blocks accessed.

Explore the B-tree introduction as a height-balanced, multiway index structure for multilevel indexing, generalizing binary search trees, with leaf nodes at the same level and internal nodes holding keys.

Examine the B-tree node structure, defining order as the maximum number of children and detailing how keys, block pointers, and data pointers map to physical memory addresses.

Derive the B-tree node order by computing how many block pointers, keys, and data pointers fit within the block size, considering memory for block pointers, keys, and data pointers.

Determine the B-tree order for a 512-byte block with 9-byte keys, 6-byte block pointers, and 7-byte record pointers, then estimate index entries across three levels.

Explore the B-tree deletion process through case-based handling, including deleting from leaves, borrowing from left or right siblings to maintain minimum keys, and merging with the parent.

Learn how a B+ tree splits leaf and internal nodes during insertions, moving the middle element to the apex and dividing keys into left and right sides.

Explore how a B+ tree handles insertion, including leaf and internal node splits, middle-element promotion to higher levels, and pointer updates.

Determine the B+ tree order for leaves and internal nodes from block size, key size, and pointers, then compute maximum entries across three levels, illustrating capacity changes.