Database Management Ch 3

Characteristics of Relational Table*

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.

tuple

in the relational model, a table row

domain

used to organize and describe an attribute's set of possible values

key*

one or more attributes that determine other attributes

- If you know the value of attribute A, you can determine the value of attribute B

• Role is based on determination A → B

functional dependence*

an attribute is functionally dependent on a composite key but not on any subset of the key

• Attribute B is functionally dependent on A if all rows in table that agree in value for A also agree in value for B

• The value of one or more attributes determines the value of one or more attributes

simple key

one attribute

composite key

a key composed of more than one attribute

key attribute

any attribute that is a part of the key

superkey

any key that uniquely identifies each row (determines every other attribute in that row)

candidate key

a superkey without unnecessary attributes (minimal superkey)

primary key

the candidate key chosen to be the unique identifier of each entity

determinant

any attribute in a specific row whose value directly determines other values in that row

dependent

an attribute whose value is determined by another attribute

candidate key (FORMAL DEF)*

attribute(s) K of table R is a candidate key for R if it satisfies the following two time-independent properties

- Uniqueness

No two record occurrences of R have the same value for K

- Minimality

If K is composite, then no component of K can be eliminated without destroying the uniqueness of the property

entity integrity*

each entity has a unique value in a primary key, AND NO key attribute in the primary key can contain a null value

The property of a relational table that guarantees each entity has a unique value in primary key and that they key has no null values

• Tables represent real-world entities, which are distinguishable

Entity representatives (records) must be distinguishable

• Null is never unique → Null can never be apart of primary key, since key has to be unique

null*

- The absence of any value (attribute values)

- Not permitted in a primary key

- Should be avoided in other attributes

- Can represent an unknown attribute value, a known but missing attribute value, or a "not applicable" condition

- Can create problems when functions such as COUNT, AVG, and SUM are used

- Can create logical problems when relational tables are linked

SHOULD BE AS RARE AS POSSIBLE

foreign keys*

a field (or combination of fields) of one table, R2, whose values are required to match those of the primary key of some table R1

- The converse is not a requirement

- Represents a reference to the record occurrence containing the matching primary key

- Any field (or combination of fields) can conceivably be a foreign key

composite foreign key*

has to have a table with a matching composite key

referential integrity rule*

the database must NOT contain any unmatched foreign key values

- Foreign keys must match primary keys or be null

- Foreign key rules and referential integrity concepts are defined in terms of one another

foreign key rules (delete)

(purple sheet examples)

- Restricted: not allowed to delete a supplier record unless shipment records are deleted

- Cascades: when you delete S1, the associated records are deleted

- Nullify: cannot contain null - part of the primary key

foreign key rules (update)

- Restricted: can't change S1 unless you change all S1

- Cascades: automatically updates S1 to new value

- Nullify: nullify

foreign key (FORMAL DEF)*

Let R2 be a base table. Then a foreign key in R2 is a subset of the set of fields of R2, say FK, such that

- There exists a base relation R1 with a candidate key CK, and

- For all time, each value of FK in the current value of R2 is either null or is identical to the value of CK in some record occurrence in the current value of R1

A foreign key is usually either entirely null or entirely non-null

secondary key

a key used strictly for data retrieval purposes

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 to the same table

M:N (M:M) relationship

CANNOT be directly implemented in the relational model

- Implemented by breaking it up to produce a set of 1:M relationships

- Avoid problems by creating a composite entity

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

data dictionary

• compiles all of the metadata about the data elements in the data model

• Provides detailed accounting of all tables found within the user/designer-created database

• Contains the data definition as well as their characteristics and relationships

indexes

• orderly arrangement to logically access rows in a table (pointers) (speeds up searches)

• Index key: points to the data location identified by the key (used to speed up and facilitate data retrieval )

• Each is associated only with one table

• Every primary key has one to be defined on it by default

Relational Algebra

• Formal term for a field

• SQL is based off this

Closure

• Use of relational algebra operators on existing relations produces new relations

• A property of relational operators that permits the use of relational algebra operators on existing tables (relations) to produce new relations

RESTRICTION/SELECTION

• To extract ROWS

• This is the WHERE of relational algebra

PROJECTION

• To extract COLUMNS

• This is the SELECT of relational algebra

JOIN (Cross Product)

• To combine relations together

• This is the FROM of relational algebra

projection, restriction, join

An SQL statement is A _______, of A _______, of A _________

System catalog

• A detailed system data dictionary that describes all objects in a database

• Contains metadata

Unique Index

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