Chapter 3

Outer joins can help pinpoint what causes

*referential integrity* problems: when foreign key values do not match the primary key values in the related table(s)

Inner join

- A join that returns matched records from the tables being joined - The difference with Natural Join is that you have to specify the joining columns and it skips step 3 (PROJECT), so it returns both joining columns.

Candidate key

- A superkey without unnecessary attributes, i.e., a minimal superkey - STU_NUM, STU_LNAME is superkey but not a candidate key-STU_NUM is candidate key - STU_LNAME, STU_FNAME, STU_PHONE may be a candidate key

System Catalog

- Also contains metadata - Detailed system data dictionary that describes all objects within the database --> data dictionary + other information, such as user authorizations and access privileges.

Null: Can represent different meaning, such as:

- An unknown attribute value - A known, but missing, attribute value - A "not applicable" condition ****Problem is we may not know the exact meaning

Superkey

- Any key that uniquely identifies each row. - A superkey functionally determines all of the entity's attributes. - STU_NUM-STU_NUM, STU_LNAME-STU_NUM, STU_LNAME, STU_INIT-STU_NUM, + any other attribute

M:N relationships

- Cannot be implemented as such in the relational model - An M:N relationship can be changed into two 1:M relationships

Referential Integrity

- FK column contains values that match PK values in table to which it is related - Many RDBMs enforce integrity rules automatically

Index key

- Index's reference point - Points to data location identified by the key

Outer join

- Inner join + - Returns all the matched records from the tables being joined, - Plus it returns the unmatched records from one of the two tables.

Natural Join

- Links tables by selecting rows with common values in common attribute(s) - A 3-stage process: 1.) PRODUCT 2.) SELECT rows where the common attributes (join columns) have the same value 3.) PROJECT on results of (2) to eliminate duplicate attributes (columns)

Controlled redundancy

- Makes the relational database work - Tables within the database share common attributes: • Enables tables to be linked together

Data Dictionary

- Provides detailed accounting of all tables found within the database - Contains (at least) all the attribute names and characteristics for each table in the system - Contains metadata: data about data

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 - Sometimes means that entity components were not defined properly - Could indicate that two entities actually belong in the same table - Certain conditions require their use

Foreign Key

An attribute in one table whose values must either match the primary key in another table or be null

Composite index

An index key that has multiple attributes

Key attribute

Any attribute that is part of a key

Null: Can create problems when using functions such as

COUNT, AVERAGE, and SUM

Composite key

Composed of more than one attribute; - E.g., STUDENT table without STU_NUM. Combination of LNAME, FNAME, INIT, and PHONE are likely to be unique

Relational algebra

Defines theoretical way of manipulating table contents using relational operators

Entity Integrity

Entity has a unique identifier with no nulls

composite entity (bridge entity)

Includes as foreign keys the primary keys of tables to be linked

Unique index

Index in which the index key can have only one pointer value (row) associated with it

Secondary Key

an attribute (or combination of attributes) used strictly for data retrieval purposes

A table can have many indexes, but each index is

associated with only one table

Null: Should be avoided in other

attributes

Tables are the what of a relational database

basic building blocks

Tables are linked by

common attributes

Avoid problems inherent to M:N relationship by creating a

composite entity (bridge entity)

Data redundancy leads to

data anomalies; - Can destroy the effectiveness of the database

Key's role is based on

determination; - If you know the value of attribute A, you can look up or determine the value of attribute B - STU_NUM --> STU_LNAME, STU_FNAME, ...

Functional dependence: Attribute B is functionally dependent on attribute A if

each value in column A determines one and only one value in column; - B-STU_PHONE is functionally dependent on STU_NUM

Relational database model is ______ to understand than hierarchical and network models

easier

To avoid nulls, Designers use

flags

Keys define. . . - Superkey - Candidate key - Primary key - Secondary key - Foreign key

functional dependencies

The relational model supports relational algebra . . . • SELECT • PROJECT • JOIN • INTERSECT UNION • DIFFERENCE • PRODUCT • DIVIDE

functions

Each row in a table must be uniquely

identifiable

Flags

indicate absence of some value

Use of relational algebra operators on existing relations produces

new relations: • SELECT • DIFFERENCE • PROJECT • JOIN • UNION • PRODUCT • INTERSECT • DIVIDE

Full functional dependence:Attribute B is functionally dependent on a composite key A but

not on any subset of A.

Key

one or more attributes that determine other attributes

Null: Not permitted in ______ key

primary

The DBMS automatically creates an index on the (it is refered to as what)

primary key of a table; - It is a unique index.

Table: Contains group of

related entity occurrences (entity set)

Null: Can create logical problems when

relational tables are linked

Logical view of relational database is based on

relations (synonym for tables)

Multiple occurrences of values not redundant when

required to make the relationship work

Table: two-dimensional structure composed of

rows and columns

Each table row must have a primary key that •Good design begins by identifying entities, attributes, and relationships-1:1, 1:M, M:N

uniquely identifies all attributes

Redundancy exists only when there is what of attribute values

unnecessary duplication

Keys are central to the

use of relational tables

Foreign keys control data redundancies by

using common attributes shared by tables; - Crucial to exercising data redundancy control

Right outer join

yields all rows from AGENT table, including those that do not have a matching value in the CUSTOMER table

Left outer join

yields all rows from CUSTOMER table, including those that do not have a matching value in the AGENT table

UNION

• Combines all rows from two tables, excluding duplicate rows. • The tables must be *union-compatible*: - have the same degree (# of columns) - columns must be of the same type - column domains (range of permissible values) must be compatible

DIVIDE

• Divisor - single-column table • Dividend - two-column table • Tables have a common column (CODE) • Yields a single column with the values from the dividend table rows where the value of the common column in both tables match

JOIN

• JOIN combines data from two or more tables • It is the real power behind the relational database • Tables are linked by a common attribute

SELECT

• SELECT all rows in a table that satisfy a given condition. • Results in a horizontal subset of the table

Primary key

a candidate key selected as the primary means of identifying rows in a relation; - Cannot contain Null entries

relational schema

a textual representation of the database tables.

Null

No data entry

Index

Orderly arrangement used to logically (and quickly) access rows in a table

Table

Structural and data independence

Relational model

View data logically rather than physically

PRODUCT

• Yields all possible rows from two tables • Also known as the *Cartesian product*

DIFFERENCE

• Yields all rows in one table that are not found in the other table. • Subtracts one table from the other. • The tables must be union-compatible

PROJECT

• Yields all values for selected attributes • Results in a vertical subset of the table

INTERSECT

• Yields only the rows that appear in both tables. • The tables must be union-compatible