Source: https://sozai.app/transcript/youtube-video-transcript-443/ Explore the history, principles, and design of relational databases, including normalization, relational algebra, and data integrity. ## Key Takeaways - Relational databases separate logical data queries from physical storage details, enabling independent evolution. - Normalization up to the third normal form is a good practice to ensure data integrity and reduce redundancy. - Relational algebra operations like join and filter are fundamental for building complex queries. - Data integrity is enforced through keys and constraints, which also improve application reliability. - Indexes and surrogate keys optimize performance and flexibility without impacting application logic. ## What the video covers - The history of modern databases began in 1970 with Edgar Codd's relational data model separating data retrieval from physical storage. - Relational algebra provides mathematical operations like join, filter, projection, and aggregation to manipulate data sets called relations. - SQL is a declarative language that specifies what data to retrieve without describing how to execute the query. - Entity-relationship diagrams help design relational databases by modeling tables and their relationships. - Database normalization aims to reduce redundancy and improve data integrity through normal forms, especially up to the third normal form. - Primary keys, foreign keys, uniqueness constraints, and null checks enforce data integrity in relational databases. - Indexes improve query performance without requiring changes to application code or query syntax. - Surrogate keys are useful for handling changing business keys and supporting multiple simultaneous records. - Normalization trade-offs include balancing data integrity with query performance, sometimes favoring denormalization. - The DBMS optimizer generates efficient query execution plans independent of the application queries. ## Chapters 1. 00:00 Introduction to the History of Modern Databases 2. 01:44 Relational Algebra and Its Operations 3. 04:49 Entity-Relationship Diagrams and Database Design 4. 06:15 Database Normalization: First and Second Normal Forms 5. 07:37 Surrogate Keys and Third Normal Form 6. 09:01 Higher Normal Forms and Practical Considerations 7. 10:33 Data Integrity and Constraints in Relational Databases Answers ## Questions about this video What is the significance of Edgar Codd's 1970 article? Edgar Codd's 1970 article introduced the relational data model, which separated data retrieval from physical storage details, revolutionizing database design and enabling independent evolution of DBMS and applications. Why is normalization important in relational databases? Normalization reduces data redundancy and ensures data integrity by organizing tables so that columns depend appropriately on primary keys, with the third normal form being sufficient for most tasks. How do indexes improve database performance? Indexes store sorted references to records based on specific fields, allowing faster searches without changing application queries or code, and DBMS developers can optimize index structures independently. ## Full Transcript — Download SRT & Markdown 00:03 Speaker A The history of modern databases begins in 1970, when Edgar Codd wrote an article about the relational data model. Codd proposed separating data retrieval from the physical details of data storage. The application sends a query in a declarative language to the 00:24 Speaker A storage, describing what data it needs. The DBMS returns the data in response to this query. The application knows nothing about disk storage order, file formats, compression or encryption algorithms, or auxiliary data structures. The DBMS creator can release a new version of the system and 00:48 Speaker A implement, for example, new file formats. This will not affect the query text generated by the application in any way. DBMS and applications can evolve independently of each other. If an application needs to search records by a certain field and it is slow, one 01:07 Speaker A can create an auxiliary search object: an index. The index stores a list of references to records, sorted by the value of the required field. The application knows nothing about the internal structure of the index. DBMS developers can improve the index 01:25 Speaker A structure over time. The application will not need to be refactored. There is no need to modify application code when creating an index. The query remains the same. Today, this separation of DBMS and application functions seems commonplace to us. But 01:44 Speaker A before 1970, data stores were more like file systems than modern databases. At the time, Codd's article was revolutionary. Codd proposed describing data operations using a mathematical language called relational algebra. 02:07 Speaker A Relational algebra gets its name from the word "relation." Relations. A relation describes a connection between objects. For example, a "chess match" relation describes the link between one player who played white and another who played black. A record of one specific 02:26 Speaker A game is called a tuple. The players' names are attributes of the tuple. Other attributes include the start time of the match, its type, and the result. 02:36 Speaker A Relations are sets of tuples. In practice, relations are called tables, tuples are rows, and attributes are columns. SQL is used to describe the data required by applications. It is a declarative language that describes what data to retrieve, but does not 02:56 Speaker A describe the algorithm to calculate it. Relational algebra defines operations on relations. An example operation is the join. This operation takes two relations as input and outputs a new relation whose tuples are pairs of tuples from the input relations. The 03:18 Speaker A set of pairs is determined by the join condition. The join is a key operation in relational DBMS. Join allows building complex queries by matching data from dozens, hundreds, or even thousands of tables. Another example is the filter operation. This operation 03:38 Speaker A takes one relation as input, i.e., one set of tuples, and outputs a subset of the input relation. The subset is determined by the filtering condition. 03:50 Speaker A A third example is projection. If filtering reduces the number of tuples, or rows, then projection reduces the number of attributes, or columns. A fourth example is aggregation. This is an opportunity to calculate a single tuple or a group of tuples from a set 04:10 Speaker A of tuples that contain certain aggregates of the input set, such as the number of rows or the sum of a column. Other examples of operations include joining relations, intersection, renaming attributes, and so on. The SQL language describes what data an 04:28 Speaker A application requests, while the DBMS determines the most optimal query execution plan. The calculation of an optimal plan is handled by a DBMS component called the optimizer. The optimizer can generate different execution plans for the same query depending on the data set. 04:49 Speaker A Entity-relationship diagrams are used for designing relational databases. In an entity diagram, rectangles with lists of columns are tables, and the lines between them represent relationships between rows of different tables. Different line ending shapes define how many records in one table 05:13 Speaker A can correspond to a single record in another. This is how a logical data model is formed. The physical model may differ from the logical one. For example, to implement a many-to-many relationship between two tables in a database, you must create a third, 05:30 Speaker A intermediate table. The goal of database schema design is normalization. There are several degrees of data normalization, known as normal forms. 05:45 Speaker A Data is said to be in the first normal form if every value in every column has an independent meaning, and no part of that value has an independent meaning. 05:58 Speaker A For instance, an email column has independent meaning if only one email address is stored in that column for each row. If a single row can hold multiple addresses in that column, then the normalization rule requires moving the emails into a separate table and 06:15 Speaker A creating a one-to-many relationship. The second normal form means that all columns in a table are irreducibly dependent on the primary key. What does "irreducible" mean? An example. A table of chess games. The primary key is composed of three columns. To determine 06:35 Speaker A , for example, the result of a specific game, one must find the record of that game using the values of all three key columns. This is an irreducible dependency. The result column is irreducibly dependent on the key. 06:51 Speaker A However, to determine the name of the white player, it is enough to use only a subset of the primary key columns, specifically the one column for the white player's ID. That is, the player's name depends only on the 07:05 Speaker A player ID, and not on the other columns of the key. If a column's value depends only on a subset of the primary key's columns, then the column is said to be reducibly dependent on the primary key. 07:21 Speaker A To move to the second normal form, you must move player names into a separate table. This solves two problems at once. The first problem is the convenience of updating data. In the second normal form, you only need to change one row 07:37 Speaker A in the players table to update a player's name. The second problem: the ability to save information about a player before their first game. In this example, it is also useful to add a surrogate primary key to the table. 07:53 Speaker A This is a more reliable option. It will allow, for instance, a single pair of players to play multiple games simultaneously. Furthermore, a surrogate key is necessary in cases where a business key value might change. The third normal form means that all 08:10 Speaker A columns in a table depend non-transitively on the primary key. In the chess match example, the value of the description and result column is determined by the match result column value, while the result of a specific match is determined by the primary key 08:25 Speaker A of that match. The result description column depends transitively on the primary key. To remove the transitive dependency, you need to move the result descriptions into a separate reference table. Thus, in transitioning to the third normal form, we went from one 08:45 Speaker A table to three. Sometimes such a transition is inconvenient, as it slows down the joining of large arrays of rows, and it is more efficient to store data in a denormalized form. However, it is better not to violate normalization without good reason. 09:01 Speaker A There are other, higher forms of data normalization: fourth, fifth, and sixth. It is a good design practice to use at least the third normal form. This is sufficient for most typical tasks. 09:15 Speaker A Relational databases, beyond the ability to join tables, provide the ability to control data integrity via foreign keys. For example, if there is a one-to-many relationship in the data schema, creating a foreign key will protect the application from errors. 09:33 Speaker A You will not be able to delete a record from the main table if records in the subordinate table reference it. Other frequently used methods of data integrity control are primary key constraints, uniqueness constraints, and null checks. There can be only one 09:51 Speaker A primary key constraint in a table, but there can be many uniqueness constraints. For each such constraint, the DBMS creates an index. 10:10 Speaker A relational systems: MySQL, Postgres, SQLite, DB2, SQL Server, Oracle. A relational database provides great flexibility in managing data structure and building queries. The price of such flexibility is the need to keep all data on a single machine. A query might 10:33 Speaker A not contain a filter by primary key values, which means that when data is distributed across several nodes, there is no way to determine which nodes hold the requested data. Furthermore, joining records from different nodes can create unpredictably high network 10:50 Speaker A load. A relational database scales poorly. Increasing database power is only possible through vertical scaling by moving to more powerful hardware. 11:03 Speaker A Horizontal scaling, that is, distributing the database across several servers, is impossible in a classic relational system. Therefore, the next stage in the evolution of data storage is NoSQL databases. To distribute records across multiple data nodes, you can represent records as a 11:27 Speaker A set of key-value pairs and define a function that calculates the node address based on the key value. Then, when reading a record by key, you can immediately access the required node of the system. How can you determine from 11:43 Speaker A a key value which of the several servers stores the corresponding key-value pair? One of the methods is consistent hashing. A good hash function produces different hashes for similar keys. Also, a good hash function distributes hash values uniformly across the set of all 12:04 Speaker A possible values. Examples of such functions are MD5 and SHA-256. Let's represent the set of hash function values as a ring and divide it into equal intervals corresponding to the number of data servers. Then, the interval number corresponds to the 12:21 Speaker A server number. To retrieve data, the client application simply calculates the hash value and determines which interval that value falls into. The application then contacts the correct SQL storage server directly. All this logic is implemented under the hood within a software library, which can 12:43 Speaker A usually be downloaded from the NoSQL system's website. In the case of a KV store, an application developer can work, for example, with an interface described in the Java Caching API. The application accesses the cache as if the data were in local memory, while 13:00 Speaker A the storage library fetches the data from the correct node. The Java Caching API is an analog to an SQL interface. 13:09 Speaker A Of course, it is much simpler and more limited in its capabilities than SQL. However, this software interface allows you to work with distributed storage. 13:20 Speaker A Examples of distributed cache systems include Apache Ignite and Hazelcast. A cache interface does not allow, for example, data projection or reading only a part of a value. If complex processing of a value is required, you can pass executable code from the 13:36 Speaker A application to the storage, which is not always convenient. But a key-value store allows for the parallel processing of thousands of records across multiple nodes, which is impossible in a classic relational system. A KV system knows nothing about the structure of a value and cannot 13:56 Speaker A validate the data schema. Also, KV systems lack built-in data projection tools to read only part of the attributes rather than the entire record. Therefore, the next stage of database evolution is document-oriented storage. Document-oriented DBMS are KV DBMS where the value has a document 14:20 Speaker A structure in one of the popular formats : JSON, XML, BSON, etc. Unlike relational DBMS, there is no requirement that all records must have the same structure. A single document collection can contain records of different structures. For example, in 14:38 Speaker A one customer collection, you can store records for both individuals and legal entities. Because these DBMS do not require a unified document structure, such systems are called semi-structured . Knowing the structure, a document-oriented DBMS can return only part of the document fields in response 14:59 Speaker A to a query or calculate an aggregate over those fields. Document-oriented DBMS do not support joins in the way they exist in relational systems. For example, to implement a one-to-many relationship, there are three options. 15:16 Speaker A The first option is to place the subordinate documents inside the main document. Then you can get the join result in a single request. The second option is to place only the identifiers of the subordinate documents inside the main document. In this case, you need 15:34 Speaker A to execute two requests to perform a join. The first request fetches the main records from the DBMS, and the second fetches the corresponding subordinate ones. And the third option is to place the identifiers of the main documents inside the subordinate 15:48 Speaker A documents. Also, having a document structure allows you to build indexes for searching by document attributes. 15:57 Speaker A Document storage formats evolved in parallel with database development. The oldest document format is XML. An XML document is a text file where data elements are marked with tags. The tag name is enclosed in angle brackets. XML allows you to describe any 16:17 Speaker A semi-structured data, reference documents from other documents, and define schemas for XML document validation. The drawbacks of XML are high data redundancy and the complexity of parsing XML documents. Since it is an old format, many relational DBMSs have built-in support for XML documents 16:37 Speaker A , where the document text can be stored in a table column. A more popular format is JSON. JSON emerged in web applications as a subset of the JavaScript language, but it is now often used in data stores both for 16:53 Speaker A representing documents and for describing queries in NoSQL systems. JSON is a more compact format than XML, but it still contains significant redundancy. Popular document-oriented DBMS MongoDB uses its own internal binary format, BSON, to store documents . This format is more compact than JSON 17:17 Speaker A and has a wider set of data types. In the relational database Postgres, one can work with a column value as a document. For this, there is the JSONB type. Postgres can build indexes on the fields of such documents. And in 17:33 Speaker A queries, you can filter documents by attributes and select only a portion of the attributes. Another binary format that develops JSON ideas is MessagePack . It is used, for example, in Tarantool . All these formats store data schema details, such as field names, inside 17:52 Speaker A the documents. This affects data redundancy. For data transfer between systems, binary formats like Protocol Buffers and Thrift are also used. 18:03 Speaker A Protocol Buffers do not store field names in the document, and use variable-length data types for storing numbers. These formats can also be applied in data stores. For example, in the key-value DBMS Apache Ignite, one can store values in Protocol Buffers 18:19 Speaker A format as a set of bytes. If you need to read only part of a value, you can send processing code to the Apache Ignite server, which will have knowledge of the document structure. 18:31 Speaker A Another branch of key-value system evolution includes systems with a wide-column architecture. Each record has a schema and consists of a set of columns. A record key can consist of several fields. The key consists of two parts. The first few fields of the key 18:56 Speaker A form the partition key, and the remaining fields form the clustering key. The partition key determines on which server the record will be placed. 19:06 Speaker A The meaning of the partition key is the same as in a regular distributed system . The clustering key determines the sort order of all records with the same partition key within a server. For example, if we store customer orders in 19:23 Speaker A Cassandra, the customer ID can be the partition key. The customer ID determines on which server the customer's orders are placed, and the order timestamp can be the clustering key. Then, all orders of a single customer will be stored on the server 19:43 Speaker A in order of timestamps, and it will be easy to select all customer orders for a specific time interval in forward or reverse chronological order. This will be a single sequential data read operation. The Cassandra database uses a consistent hashing algorithm based on 20:01 Speaker A MD5 to distribute records across servers. The MD5 hash of the partition key is calculated. Each Cassandra server stores a specific range of hash values. Cassandra supports the query language CQL, or Cassandra Query Language. It resembles simple SQL queries, but Cassandra is not a 20:22 Speaker A relational system. A column-family store does not allow table joins. This limitation can be partially bypassed using collection types supported by the same query language. Of Cassandra. If you need, for example, to store a list of emails for a single user record, 20:41 Speaker A this can be done by creating an email column and assigning it a list type. 20:51 Speaker A With the rise of social networks and the growth of data volumes, representing data as graphs has become relevant. A graph is a collection of vertices and the connections between them. For example, a record about a social network user. can be represented 21:06 Speaker A as a graph vertex, and the connection between two users as an edge between two vertices. There can be vertices of different types in a graph. One and the same vertex can have multiple types at once. Just like a record in a 21:20 Speaker A relational database, a graph vertex can have a set of properties. Graph edges describe different types of relationships between entities. An edge also has a type and properties. There can be multiple edges between a pair of vertices if two entities are linked by 21:37 Speaker A different relationships. For example, two people can be both friends on a social network and colleagues at work. 21:48 Speaker A Graph data models help solve tasks such as finding all of a person's friends, finding all friends of friends, or finding more complex connections between objects. Graph DBMS provide declarative languages for writing queries against a graph. The result of 22:07 Speaker A a query can be a graph, a list of vertices, or a table. The application areas for graph systems are diverse: social network analysis, building knowledge bases, product recommendations, and identifying production or financial chains. 22:23 Speaker A Examples of graph DBMS: Neo4j, OrientDB , and AnzoGraph. Topics: relational databases Edgar Codd relational algebra SQL database normalization entity-relationship diagrams data integrity indexes surrogate keys DBMS optimizer Study this video - [Flashcards from this video](https://sozai.app/tools/ai-flashcard-generator/?from=transcript&id=75109) - [Quiz on this video](https://sozai.app/tools/quiz-generator/?from=transcript&id=75109) Free, made by AI from this transcript. No signup. --- This is the markdown twin of https://sozai.app/transcript/youtube-video-transcript-443/ — the same content, without the markup. Published by SozAI (https://sozai.app). Reuse and quotation are allowed with attribution and a link back. Machine-readable index: https://sozai.app/llms.txt · data API: https://sozai.app/api/