C170 Data Management Applications Notes 2021/2022 already graded A+

C170 Data Management Applications Notes Lesson 1 - Conceptual Models A Logical View of Data - Placing the DBMS between the application and the database eliminates most of the file system’s inherent limitations. - The relational model enables you to view data logically rather than physically. Tables and Their Characteristics - The logical view of the relational database is facilitated by the creation of data relationships based on a logical construct known as a relation. - A table is perceived as a two-dimensional structure composed of rows and columns, and is called a relation. - As far as the table’s user is concerned, a table contains a group of related entity occurrences, or an entity set. - The characteristics of a relational table are: 1. A table is perceived as a two dimensional structure composed of rows and columns. 2. Each table row (tuple) represents a single entity occurrence within the entity set. 3. Each table column represents an attribute, and each column has a distinct name. 4. Each intersection of a row and column represents a single data value. 5. All values in a column must conform to the same data format. 6. Each column has a specific range of values known as the attribute domain. 7. The order of the rows and columns is immaterial to the DBMS. 8. Each table must have an attribute or combination of attributes that uniquely identifies each row. Keys - A key consists of one or more attributes that determine other attributes. - Determination is that state in which knowing the value of one attribute makes it possible to determine the value of another. lOMoARcPSD| - Functional dependence means that the value of one or more attributes determines the value of one or more other attributes. - The attribute whose value determines another is called the determinant. - The attribute whose value is determined by the other attribute is called the dependent. - Full functional dependence is used to refer to functional dependencies in which the entire collection of attributes in the determinant is necessary for the relationship. lOMoARcPSD| Types of Keys - A composite key is a key composed of more than one attribute. - An attribute that is part of a key is called a key attribute. - A superkey is a key that can uniquely identify any row in the table — in other words, it functionally determines every attribute in the row. - A candidate key is a minimal superkey without any unnecessary attributes, based on a full functional dependency. - Entity integrity is the condition in which each row in the table has its own unique identity. - All of the values in the primary key must be unique - No key attribute in the primary key can contain a null. - A foreign key (FK) is the primary key of one table that has been placed into another table to create a common attribute. - Referential integrity is the condition in which every reference to an entity instance by another entity instance is valid. - A secondary key is defined as a key that is used strictly for data retrieval purposes. Relational Algebra - Relational algebra defines the theoretical way of manipulating table contents using relational operators. Formal Definitions and Terminology - A relvar is a variable that holds a relation. The table structure is properly called a relvar, and the data in the structure would be a relation. The relvar is a container (variable) for holding relation data, not the relation itself. - A relvar has two parts: - the heading contains the names of the attributes - the body contains the relation lOMoARcPSD| Relational Set Operators - The relational operators have the property of closure; the use of relational algebra operators on existing relations produces new relations. - SELECT (Restrict): - SELECT, also known as RESTRICT, is referred to as a unary operator because it only uses one table as input. - - PROJECT - PROJECT yields all values for selected attributes. It is also a unary operator, accepting only one table as input. - - UNION - UNION combines all rows from two tables, excluding duplicate rows. To be used in the UNION, the tables must have the same attribute characteristics. The lOMoARcPSD| columns and domains must be compatible, when they are said to be union-compatible. - - INTERSECT - INTERSECT yields only the rows that appear in both tables. - The tables must be union-compatible to yield valid results. - - DIFFERENCE - DIFFERENCE yields all rows in one table that are not found in the other table; that is, it subtracts one table from the other. - The tables must be union-compatible to yield valid results. - - PRODUCT - PRODUCT yields all possible pairs of rows from two tables. - lOMoARcPSD| - JOIN - JOIN allows information to be intelligently combined from two or more tables. JOIN is the real power behind the relational database, allowing the use of independent tables linked by common attributes. - The CUSTOMER and AGENT tables will be used to illustrate several types of joins., - - A natural join links tables by selecting only the rows with common values in their common attributes - First, a PRODUCT of the tables is created. - Second, a SELECT is performed to yield only the rows for which the AGENT_CODE values are equal - - A PROJECT is performed to yield a single copy of each attribute, eliminating duplicate columns. - - The final outcome of a natural join yields a table that does not include unmatched pairs and provides only the copies of the matches. - If no match is made between the table rows, the new table does not include the unmatched row. - The column on which the join was made, occurs only once in the new table. - If the same AGENT_CODE were to occur several times in the AGENT table, a customer would be listed for each match. lOMoARcPSD| - An equijoin links tables on the basis of an equality condition that compares specified columns of each table. The outcome of the equijoin does not eliminate duplicate columns, and the condition used to join the tables must be explicitly defined. - The equijoin takes its name from the equality comparison operator used in the condition. If any other comparison operator is used, the join is called a theta join. - Each of the following is an inner join, which only returns matched records from the tables that are being joined. - In an outer join, the matched pairs would be retained, and any unmatched values in the other table would be left null. - A left outer join yields all of the rows in the CUSTOMER table, including those that do not have a matching value in the AGENT table. - - A right outer join yields all of the rows in the AGENT table, including those that do not have matching values in the CUSTOMER table. - - Outer joins are especially useful when you are trying to determine what values in related tables cause referential integrity problems.