Database Design for Relational Databases using SQL Server

Explore how a database acts as a collection of data organized for rapid search and retrieval, typically structured data stored in a computer, with accessibility and accuracy boosting usefulness.

Identify the four main database types: flat files, no sql databases, data warehouses, and relational databases. Learn how these types relate to relational design in SQL Server.

Understand how flat files store data in large, unnormalized tables with many columns, highlighting ease of viewing but challenges in updating, data redundancy, and inconsistencies over time.

Explore NoSQL databases, also known as not only SQL databases, which store and retrieve data beyond tabular relations, including graph and key-value types used in big data and real-time apps.

Optimize reporting and analysis with data warehouses, a specialized database designed for historical data sourced from relational databases and kept separate from an organization's operational database.

Explore relational databases and relational database management systems, using SQL to query and manage data. Learn to enforce data integrity, minimize redundancy, and create tables for efficient storage.

Explore the distinction between logical design and physical design for relational databases, using SQL scripts and entity relationship diagrams to map tables, attributes, and relationships, with a logical design first.

Learn common table terminology, including columns, column headers, field names, and attributes. Understand that rows have no fixed order and that order by sorts them within the relational model.

To follow along, secure the SQL Server Express edition and a database interface such as SQL Server Management Studio, and learn where to download them in the next video.

Install Microsoft SQL Server Express 2017 and SQL Server Management Studio (SSMS), download from Microsoft, accept the license terms, use the default install location, and restart after installation.

Create and query a simple table in SQL Server, including creating a test database, defining an employees_test table, and performing insert, select, update, delete operations, with basics of transactions.

Enforce data integrity in relational databases by applying constraints to tables. Explore primary key, foreign key, not null, unique, and check constraints to ensure accuracy and consistency.

Define null values as unknown information representing the absence of data. Explain that nulls are not zeros, empty strings, or the word null, and are not equal to anything.

Prevent null value errors in SQL Server by using not null constraints on mandatory numeric columns and, when needed, replacing nulls with zeros using the ISNULL function in calculations.

Explore handling nulls in SQL Server through creating tables, inserting nulls, and using the ISNULL function, while enforcing not null constraints and examining nulls in filters and aggregates.

Identify and enforce a primary key constraint on a column or set of columns to ensure values are unique and not null, selecting one candidate key as the primary key.

Learn how composite primary keys use multiple columns for a table's primary key, ensuring uniqueness and not null, while recognizing their maintenance challenges and their use in many-to-many relationships.

Explore natural keys in relational databases, from parks with real-world data and unique names to composite keys using supplier name, product name, and order date time as candidate keys.

Describe surrogate keys as system-generated columns that uniquely identify rows, using employee ID as an example, and note ISBN was created as a surrogate key for books in 1970.

preserve referential integrity by using surrogate keys that never change, unlike natural keys that may, avoiding cascading updates when country names change while the country id stays fixed.

Establish solid primary key practices by enforcing a unique key for each row to enable efficient joins, avoid changing keys, and use surrogate keys with unique constraints on natural keys.

Compare primary key and unique constraints: a primary key is unique and not null and creates a unique index, while unique constraints are multiple and may allow nulls.

Apply check constraints to enforce per-column conditions, such as quantity > 0 or color in ('red','blue','yellow','black','white'), within SQL Server. Remember that check constraints cannot reference other tables or include subqueries.

Explore how indexes are data structures that speed up searches, why they are external to table, and how poor indexing can slow inserts, with a preview of the unique index.

Learn how unique indexes enforce unique values on columns or column combinations, act like a unique constraint, and improve performance for queried columns.

Learn how to create primary key constraints and unique constraints in SQL Server, including in-line and out-of-line definitions, sequences, and global unique identifiers.

Identify the three relationship types—one-to-many, many-to-many, and one-to-one—between entities, with examples like cars and car parts, guests and hotel rooms, and countries and capital city.

Identify relationships between entities by asking two perspective questions about one-to-many possibilities, illustrated with employees and departments (one-to-many) and doctors and patients (many-to-many), then link to relational database implementation.

Explore one-to-many relationships in relational databases by linking the departments table as the parent to the employees table as the child, using a foreign key constraint on department id.

Explore one-to-many relationships between parent and child tables (departments and employees), enforce referential integrity with foreign keys, and balance null and not null constraints on the department id.

Explore one-to-many relationships and why avoiding duplication of department names prevents inconsistency, then use a view like EmployeesDept to join employees and departments and display department names with employee data.

Apply foreign key constraints to enforce referential integrity in one-to-many relationships by placing the foreign key on the child table and referencing the parent's primary key.

Learn how to implement one-to-many relationships in SQL Server by linking departments parent table to employees child table with a foreign key, enforce not null, and build a view.

One-to-one relationships link an entity to zero or one instance, with attributes in the same table and a child table enforcing the link via primary and foreign keys.

Master one-to-one relationships in SQL Server by linking the employees table to a compensation table with primary and foreign keys, join on employee_id, and use a view to restrict access.

Model many-to-many relationships using an association (linking) table with a composite primary key on docID and patID and two foreign keys to doctors and patients.

Explore self-referencing relationships by using a foreign key to link a manager id to an employee id within the same table, preventing errors from invalid references.

Explore self-referencing relationships in an employees table, joining to itself to reveal each manager, and use a left outer join to include all employees.

Trace data normalization from Ted Codd's 1970 relational model to IBM, and learn how organizing attributes and tables minimizes data redundancy through primary and foreign keys.

Learn how normalization eliminates update, insert, and deletion anomalies and reduces restructuring as new data types are introduced.

Identify update, insert, and delete anomalies from poorly structured instructor and classes data, and learn how normalization with a linking table resolves many-to-many relationships and supports ERD diagrams.

Explore data normalization from the first four normal forms, their interdependencies, and why the fourth and fifth forms are seldom used, with functional dependencies and keys.

Master functional dependencies, where an attribute or set uniquely determines another, illustrated by student id determining first name and building name and conference room determining room capacity for data normalization.

Explore trivial functional dependencies, where the right-hand side is a subset of the left-hand side determinant, so the dependency always holds, illustrated by department id and employee id examples.

Explore functional dependencies in a four-attribute table by identifying non-trivial FDs, showing A functionally determines C and D, while B and C do not; analyze {B,C} and discuss real-world caveats.

A candidate key uniquely identifies each row, may be a single attribute or irreducible set, and a table may have many candidate keys but one primary key.

Understand how candidate keys are unique and irreducible, while super keys are unique but not minimal. The supplier, product, and order date time subset shows irreducibility in a practical example.

Identify key attributes that appear in at least one candidate key and distinguish non-key attributes that do not participate in candidate keys, as in the car part orders example.

Understand first normal form by enforcing atomic values, same-type columns, no duplicates. Address nulls and multi-valued fields with parent and child tables, using composite primary key and a foreign key.

Explore multi-valued and multi-type fields and how they hinder analysis. Normalize to first normal form by separating values and creating stations and linking tables with surrogate keys.

Explore first normal form in SQL Server by transforming a parks table into normalized parks and park_weeds, illustrating many-to-many relationships. Use unpivot and union all to populate the park_details view.

Demonstrates second normal form by ensuring non-key attributes depend on whole candidate key. Examples show splitting into student and subject tables to remove partial dependencies.

Demonstrates partial dependencies in a table with a composite primary key and normalizes to second normal form by splitting into students, subjects, and a linking student_grades2 table.

Define transitive dependencies as where a data element functionally determines B and B functionally determines C, showing C depends on A and guiding removal to reach third normal form.

Explore third normal form by identifying functional dependencies, removing transitive dependencies, and moving attributes into related tables, such as customers and invoices, advancing from second normal form.

See how to apply third normal form by normalizing golf tournament data, moving date of birth to the players table, and using foreign keys to join winners.

Explore Boyce-Codd normal form (BCNF), a stricter standard than third normal form, where every determinant is a superkey, shown via a meeting room bookings example and normalization into related tables.

Understand normal forms: first normal form needs a candidate key; second normal form requires non-key attributes depend on whole key; third normal form and BCNF require nothing but the key.

Understand multi-valued dependencies and fourth normal form, where X determines multiple values of Y and Z, and separate course books from lecturers to reduce redundancy.

Explore fourth normal form by removing multivalued dependency through splitting a single table into uni_courses2, course_books, and course_lectures, creating primary keys, and enabling joins for a summary view.

Normalize the library database by converting book_checkouts_report into a normalized book_checkouts table with a composite primary key and foreign keys, and learn to populate it from the old report.

Unpivot the book_checkouts_report, replace book titles with isbn, and design a composite primary key with due_date, plus foreign keys to library customers and books.

Decompose the PROJECT_INFO table into normalized relational tables by identifying entities, relationships, and keys, then create parent, child, and linking tables with primary and foreign keys in SQL Server.

Identify entities in the project_info table: projects, employees, and departments. Create tables and a many-to-many linking table for employees and projects; apply a one-to-many foreign key from departments to employees.

Choose appropriate data types for attributes to constrain column values and support table design, including character, number, and date types in SQL Server.

Adopt consistent naming conventions for relational databases: use plural table names, singular column names, and underscores; give meaningful names and align foreign key names with primary keys.

Explore SQL Server naming conventions for tables, columns, views, and constraints, including length limits, allowed characters, camel case, and collations, with practical demos using UniCourses and course_books.

Use indexes to speed up select statements on one or more columns, stored as a separate data structure, and understand trade-offs in storage, writes, and cardinality and selectivity.

Explore index types in SQL Server, including non-clustered, clustered, unique, and filtered indexes, and learn practical guidelines for designing efficient relational database indexes.

Learn how composite indexes use the leftmost column to enable index seeks, and how including non-key columns with include can create covering indexes for queries.