Database Systems: Design, Implementation, and Management PDF

Database Systems: Design, Implementation, and Management Ninth Edition Chapter 3 The Relational Database Model  Objectives In this chapter, students will learn: – That the relational database model offers a logical view...

Database Systems: Design, Implementation, and Management Ninth Edition Chapter 3 The Relational Database Model  Objectives In this chapter, students will learn: – That the relational database model offers a logical view of data – About the relational model’s basic component: relations – That relations are logical constructs composed of rows (tuples) and columns (attributes) – That relations are implemented as tables in a relational DBMS Database Systems, 9th Edition 2  Objectives (cont’d.) – About relational database operators, the data dictionary, and the system catalog – How data redundancy is handled in the relational database model – Why indexing is important Database Systems, 9th Edition 3  A Logical View of Data Relational model – View data logically rather than physically Table – Structural and data independence – Resembles a file conceptually Relational database model is easier to understand than hierarchical and network models Database Systems, 9th Edition 4  Tables and Their Characteristics Logical view of relational database is based on relation – Relation thought of as a table Table: two-dimensional structure composed of rows and columns – Persistent representation of logical relation Contains group of related entities (entity set) Database Systems, 9th Edition 5 Database Systems, 9th Edition 6 Database Systems, 9th Edition 7  Keys Each row in a table must be uniquely identifiable Key is one or more attributes that determine other attributes Key’s role is based on determination – If you know the value of attribute A, you can determine the value of attribute B Functional dependence – Attribute B is functionally dependent on A if all rows in table that agree in value for A also agree in value for B Database Systems, 9th Edition 8 Database Systems, 9th Edition 9  Keys (cont’d.) Unique Key – Works as an alternative to Primary Key to hold unique values. – Key features Even though unique key is similar to the primary key in a table, it can accept a single NULL value unlike the primary key. It cannot have duplicate values. It can also be used as a foreign key in another table. A table can have more than one Unique column. Database Systems, 9th Edition 10  Keys (cont’d.) Primary Key – Key that particularly or unique identify a table. – Key features Even though unique key is similar to the primary key in a table, it can accept a single NULL value unlike the primary key. It cannot have duplicate values. It can also be used as a foreign key in another table. A table can have more than one Unique column. Database Systems, 9th Edition 11  Keys (cont’d.) Primary Key vs. Unique Key – Key a primary key is a key that uniquely identifies each record in a table but cannot store NULL values. In contrast, a unique key prevents duplicate values in a column and can store NULL values Database Systems, 9th Edition 12  Keys (cont’d.) Foreign Key – Combines or links columns between table whose values match the values of a Primary Key column in another table. – Used to establish relationships between two tables – Also called referencing key Database Systems, 9th Edition 13  Keys (cont’d.) Foreign Key – We can create a Foreign Key on a MySQL table using the CONSTRAINT... FOREIGN KEY... REFERENCES keyword in the CREATE TABLE statement. Below is a sample syntax Database Systems, 9th Edition 14  Keys (cont’d.) Composite key – A combination of two or more than two columns in a table which allows us to identify each row of the table uniquely – Primary key or super key can be called composite key when combined to another attribute or column Database Systems, 9th Edition 15  Keys (cont’d.) Superkey – Any key that uniquely identifies each row – Can be one or more attributes which can uniquely identify a row in a table Database Systems, 9th Edition 16  Keys (cont’d.) Nulls – No data entry – Not permitted in primary key – Should be avoided in other attributes – Can represent: An unknown attribute value A known, but missing, attribute value A “not applicable” condition Database Systems, 9th Edition 17  Keys (cont’d.) Nulls (cont’d.) – Can create problems when functions such as COUNT, AVERAGE, and SUM are used – Can create logical problems when relational tables are linked Database Systems, 9th Edition 18 Database Systems, 9th Edition 19 Database Systems, 9th Edition 20  Keys (cont’d.) Referential integrity – Refers to the accuracy and consistency of data within a database relationship. FK contains a value that refers to an existing valid tuple (row) in another relation. – Data are linked between 2 or more tables with the help of Primary Key and Foreign Key Database Systems, 9th Edition 21  Primary Key vs. Foreign Key Database Systems, 9th Edition 22  What is the problem with below referenced tables? Database Systems, 9th Edition 23  Keys (cont’d.) Secondary key / Alternate Key / Candidate Key – Is uniquely identify rows of a table – Carries unique information and value. – Has a potential to be a primary key, as it has the same properties but, only one primary key is allowed – An alternate key – Key used for faster data retrieval purposes – There can be more than one candidate keys in Database Systems, 9th Edition 24 a table and they can never be empty  Keys (cont’d.) Relational Database Key Key Type Definition Superkey Can be one or more attributes which can uniquely identify a row in a table Composite Key A combination of two or more than two columns in a table which allows us to identify each row of the table uniquely Primary Key key is a key that uniquely identifies each record in a table but cannot store NULL values Secondary Key An alternate or candidate key as a primary key Foreign Key Combines or links columns between table whose values match the values of a Primary Key column in another table Database Systems, 9th Edition 25  Integrity Rules Many RDBMs enforce integrity rules automatically Safer to ensure that application design conforms to entity and referential integrity rules Designers use flags to avoid nulls – Flags indicate absence of some value Database Systems, 9th Edition 26 Database Systems, 9th Edition 27  Relational Set Operators Relational algebra – Defines theoretical way of manipulating table contents using relational operators – Use of relational algebra operators on existing relations produces new relations: SELECT DIFFERENCE PROJECT JOIN UNION PRODUCT INTERSECT DIVIDE Database Systems, 9th Edition 28 Database Systems, 9th Edition 29 Database Systems, 9th Edition 30 Union Database Systems, 9th Edition 31 Intersection Database Systems, 9th Edition 32 Difference Database Systems, 9th Edition 33 Product R S RXS A 1 A 1 B 2 C 2 D 3 D 3 F 4 Database Systems, 9th Edition 34 Product R S RXS A 1 A 1 A1A1 B 2 C 2 A1C2 D 3 D 3 A1D3 F 4 B2A1 B2C2 B2D3 D3A1 D3C2 D3D3 F4A1 F4C2 F4D3 Database Systems, 9th Edition 35  Relational Set Operators (cont’d.) Natural Join – Links tables by selecting rows with common values in common attribute(s) Equijoin – Links tables on the basis of an equality condition that compares specified columns Theta join – Any other comparison operator is used Outer join – Matched pairs are retained, and any unmatched values in other table are left null Database Systems, 9th Edition 36  Relational Set Operators (cont’d.) Join Operation Combining two or more tables or relations based on some conditions, specified in the Selection Process Cartesian Product + Selection Process or combining two tables with selection process 1. Inner Join – represented by the following ❖ Joining tables or relations with the output that satisfy the matching criteria are included in the output table. ❖ Discards row/s of either table that do not match any row of the other table ❖ The rest are excluded in the table ❖ Types of Inner Join a. Natural Join b. Equijoin Join c. Theta join Database Systems, 9th Edition 37  Relational Set Operators (cont’d.) Symbol – Theta & Equi are almost the same with difference in the predicate Database Systems, 9th Edition 38  Relational Set Operators (cont’d.) Join Operation Combining two or more tables or relations based on some conditions, specified in the Selection Process Cartesian Product + Selection Process or combining two tables with selection process 2. Outer Join – ❖ Result includes unmatched rows of one of the tables, or of both tables ❖ Matching and non-matching values will appear in the output ❖ Matching is based on the join condition ❖ Types of Outer Join Left Outer Join Right Outer Join Full Outer Join Database Systems, 9th Edition 39  Relational Set Operators (cont’d.) Join Operation – Inner Join a.Theta join - Any other comparison operator is used ❖ A general case of join operation ❖ Also known as conditional join ❖ Used to join two or more relations on some conditions ❖ Take the following conditional operators =, , Database Systems, 9th Edition 40  Relational Set Operators (cont’d.) Database Systems, 9th Edition 41  Relational Set Operators (cont’d.) Join Operation – Inner Join a.Equi join - Links tables on the basis of an equality condition that compares specified columns ❖ A special case of theta operation or conditional join ❖ When theta join uses only equivalence, it becomes Equi Join ❖ Used to join two or more relations on some conditions a. Syntax: b. Is the predicate Database Systems, 9th Edition 42  Relational Set Operators (cont’d.) Equijoin - Sample Database Systems, 9th Edition 43  Relational Set Operators (cont’d.) Equijoin - Solution Database Systems, 9th Edition 44  Relational Set Operators (cont’d.) Join Operation – Inner Join a.Natural join - Links tables by selecting rows with common values in common attribute(s) ❖ A special case of Equi Join ❖ Can be performed only if there is a common attribute ❖ The name and domain of the attributes must be the same ❖ Does not use any comparison operator ❖ Natural Join = Cartesian Product + Selection + Projection ❖ Does not do concatenation like Cartesian Product a. Denoted by b. Example: Database Systems, 9th Edition 45  Relational Set Operators (cont’d.) Natural Join – Sample Database Systems, 9th Edition 46  Relational Set Operators (cont’d.) Natural Join – Solution Database Systems, 9th Edition 47  Relational Set Operators (cont’d.) Outer join – Matched pairs are retained, and any unmatched values in other table are left null – Output includes unmatched rows of the tables, or of both tables. – Matching is based on the join condition. – Types of Outer Join Left Outer Join Right Outer Join Full Outer Join Database Systems, 9th Edition 48  Relational Set Operators (cont’d.) Join Operation - Outer Join a.Left Outer Join ❖ Result includes unmatched rows of one of the tables, or of both tables ❖ Matching and non-matching values will appear in the output ❖ Matching is based on the join condition ❖ Types of Outer Join Database Systems, 9th Edition 49  Relational Set Operators (cont’d.) Sample – Left Outer Join ❖ Left side of the table take more weight in this relation Database Systems, 9th Edition 50  Relational Set Operators (cont’d.) Solution – Left Outer Join Database Systems, 9th Edition 51  Relational Set Operators (cont’d.) Sample & Solution– Right Outer Join ❖ Right side of the table takes more weight here Database Systems, 9th Edition 52  Relational Set Operators (cont’d.) Sample & Solution – Full Outer Join ❖ Matching & non-matching rows from both the tables will appear Database Systems, 9th Edition 53 Database Systems, 9th Edition 54 Database Systems, 9th Edition 55 Database Systems, 9th Edition 56 Database Systems, 9th Edition 57 Database Systems, 9th Edition 58  The Data Dictionary and System Catalog Data dictionary – Provides detailed accounting of all tables found within the user/designer-created database – Contains (at least) all the attribute names and characteristics for each table in the system – Contains metadata: data about data System catalog – Contains metadata – Detailed system data dictionary that describes all objects within the database Database Systems, 9th Edition 59 Database Systems, 9th Edition 60  Relationships within the Relational Database 1:M relationship – Relational modeling ideal – Should be the norm in any relational database design 1:1 relationship – Should be rare in any relational database design Database Systems, 9th Edition 61  Relationships within the Relational Database (cont’d.) M:N relationships – Cannot be implemented as such in the relational model – M:N relationships can be changed into 1:M relationships Database Systems, 9th Edition 62  The 1:M Relationship Relational database norm Found in any database environment Database Systems, 9th Edition 63 Database Systems, 9th Edition 64  The 1:1 Relationship One entity related to only one other entity, and vice versa Sometimes means that entity components were not defined properly Could indicate that two entities actually belong in the same table Certain conditions absolutely require their use Database Systems, 9th Edition 65 Database Systems, 9th Edition 66  The M:N Relationship Implemented by breaking it up to produce a set of 1:M relationships Avoid problems inherent to M:N relationship by creating a composite entity – Includes as foreign keys the primary keys of tables to be linked Database Systems, 9th Edition 67 Database Systems, 9th Edition 68 Database Systems, 9th Edition 69 Database Systems, 9th Edition 70 Database Systems, 9th Edition 71 Database Systems, 9th Edition 72  Data Redundancy Revisited Data redundancy leads to data anomalies – Can destroy the effectiveness of the database Foreign keys – Control data redundancies by using common attributes shared by tables – Crucial to exercising data redundancy control Sometimes, data redundancy is necessary Database Systems, 9th Edition 73 Database Systems, 9th Edition 74  Indexes Orderly arrangement to logically access rows in a table Index key – Index’s reference point – Points to data location identified by the key Unique index – Index in which the index key can have only one pointer value (row) associated with it Each index is associated with only one table Database Systems, 9th Edition 75 Database Systems, 9th Edition 76  Codd’s Relational Database Rules In 1985, Codd published a list of 12 rules to define a relational database system – Products marketed as “relational” that did not meet minimum relational standards Even dominant database vendors do not fully support all 12 rules Database Systems, 9th Edition 77  Summary Tables are basic building blocks of a relational database Keys are central to the use of relational tables Keys define functional dependencies – Superkey – Candidate key – Primary key – Secondary key – Foreign key Database Systems, 9th Edition 78  Summary (cont’d.) Each table row must have a primary key that uniquely identifies all attributes Tables are linked by common attributes The relational model supports relational algebra functions – SELECT, PROJECT, JOIN, INTERSECT UNION, DIFFERENCE, PRODUCT, DIVIDE Good design begins by identifying entities, attributes, and relationships – 1:1, 1:M, M:N Database Systems, 9th Edition 79