Chapter 3 The relational database models

Primary Key

Attribute or combination of attributes that uniquely identifies a row

Entity Integrity

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 Purpose - Each row will have a unique identity, and foreign key values can properly reference primary key values. Example - No invoice can have a duplicate number, nor can it be null; in short, all invoices are uniquely identified by their invoice number.

Keys

Consist of one or more attributes that determine other attributes •Ensure that each row in a table is uniquely identifiable •Establish relationships among tables and to ensure the integrity of the data

Dependencies

Determination •State in which knowing the value of one attribute makes it possible to determine the value of another -Establishes the role of a key -Based on the relationships among the attributes •Functional dependence: value of one or more attributes determines the value of one or more other attributes -Determinant: attribute whose value determines another -Dependent: attribute whose value is determined by the other attribute •Full functional dependence: entire collection of attributes in the determinant is necessary for the relationship

Secondary key

Key used strictly for data retrieval purposes

What does a relational database enable

Relational database model enables logical representation of the data and its relationships •Logical simplicity yields simple and effective database design methodologies •The logical view is facilitated by the creation of data relationships based on a logical construct called a relation

Summary

Tables are the basic building blocks of a relational database •Keys are central to the use of relational tables -Each table row must have a primary key •Although tables are independent, they can be linked by common attributes The relational model supports several relational algebra functions •A relational database performs much of the data manipulation work behind the scenes •Once you know the basics of relational databases, you can concentrate on design

Null

absence of any data value -Unknown attribute value, known but missing attribute value, or inapplicable condition

key attribute

attribute that is a part of a key

Referential Integrity

every reference to an entity instance by another entity instance is valid Purpose - It is possible for an attribute not to have a corresponding value, but it will be impossible to have an invalid entry; the enforcement of the referential integrity rule makes it impossible to delete a row in one table whose primary key has mandatory matching foreign key values in another table. Example - A customer might not yet have an assigned sales representative (number), but it will be impossible to have an invalid sales representative (number)

Super key

key that can uniquely identify any row in the table

Composite key

key that is composed of more than one attribute

Canidate Key

minimal superkey

Foreign key

primary key of one table that has been placed into another table to create a common attribute

Be able to

•After completing this chapter, you will be able to: •Describe the relational database model's logical structure •Identify the relational model's basic components and explain the structure, contents, and characteristics of a relational table •Use relational database operators to manipulate relational table contents •Explain the purpose and components of the data dictionary and system catalog •Identify appropriate entities and then the relationships among the entities in the relational database model •Describe how data redundancy is handled in the relational database model •Explain the purpose of indexing in a relational database

Relational Set Operator' Union & Union Compatible

•Combines all rows from two tables, excluding duplicate rows •Union-compatible: tables share the same number of columns, and their corresponding columns share compatible domains

Data Dictionary

•Description of all tables in the database created by the user and designer

Ways to handle Nulls

•Flags - Special codes used to indicate the absence of some value •Constraints - NOT NULL constraint: placed on a column to ensure that every row in the table has a value for that column - UNIQUE constraint: restriction placed on a column to ensure that no duplicate values exist for that column

Many-to-many (M:N)

•Implemented by creating a new entity in 1:M relationships with the original entities •Composite entity (i.e., bridge or associative entity): helps avoid problems inherent to M:N relationships-Includes the primary keys of tables to be linked

Joins

•Joins allow information to be intelligently combined from two or more tables •Natural join: links tables by selecting only the rows with common values in their common attribute •Equijoin: links tables on the basis of an equality condition that compares specified columns of each table •Theta join: links tables using an inequality comparison operator •Inner join: only returns matched records from the tables that are being joined •Outer join: matched pairs are retained and unmatched values in the other table are left null -Left outer join: yields all of the rows in the first table, including those that do not have a matching value in the second table -Right outer join: yields all of the rows in the second table, including those that do not have matching values in the first table

One-to-many (1:M)

•Norm for relational databases

One-to-one (1:1)

•One entity can be related to only one other entity and vice versa

Indexes

•Orderly arrangement to logically access rows in a table -Index key: index's reference point that leads to data location identified by the key -Unique index: index key can have only one pointer value associated with it •Each index is associated with only one table -The index key can have multiple attributes

System Catalog

•System data dictionary that describes all objects within the database

Data redundancy

•The relational database facilitates control of data redundancies through use of foreign keys -Common attributes that are shared by tables •To be controlled except the following circumstances: -Sometimes data redundancy must be increased to make the database serve crucial information purposes -Sometimes data redundancy exists to preserve the historical accuracy of data

Relational Set Operator Select( Restricted)

•Unary operator that yields a horizontal subset of a table

Relational Set Operator Project

•Unary operator that yields a vertical subset of a table

Relational Set Operator Divide

•Uses one double-column table as the dividend and one single-column table as the divisor •Output is a single column that contains all values from the second column of the dividend that are associated with every row in the divisor

Relational Set Operator Product

•Yields all possible pairs of rows from two tables

Relational Set Operator Difference

•Yields all rows in one table that are not found in the other table •Tables must be union-compatible to yield valid results

Relational Set Operator Intersect

•Yields only the rows that appear in both tables •Tables must be union-compatible to yield valid results