The Relational Database Model

1. Keys

1.1. Each row in a table must be uniquely identifiable

1.2. Composite key

1.2.1. Composed of more than one attribute

1.3. Key attribute

1.3.1. Any attribute that is part of a key

1.4. Superkey

1.4.1. Any key that uniquely identifies each row

1.5. Candidate key

1.5.1. A superkey without unnecessary attributes

1.6. Nulls

1.6.1. No data entry

1.6.2. Not permitted in primary key

1.6.3. Should be avoided in other attributes

1.6.4. Can represent:

1.6.4.1. An unknown attribute value

1.6.4.2. A known, but missing, attribute value

1.6.4.3. A “not applicable” condition

1.6.5. Can create problems when functions such as COUNT, AVERAGE, and SUM are used

1.6.6. Can create logical problems when relational tables are linked

2. Relationships within the Relational Database

2.1. 1:M relationship

2.1.1. Relational modeling ideal

2.1.2. Should be the norm in any relational database design

2.2. 1:1 relationship

2.2.1. Should be rare in any relational database design

2.3. M:N relationships

2.3.1. Cannot be implemented as such in the relational model

2.3.2. M:N relationships can be changed into 1:M relationships

3. Integrity Rules

3.1. Many RDBMs enforce integrity rules automatically

3.2. Safer to ensure that application design conforms to entity and referential integrity rules

3.3. Designers use flags to avoid nulls

3.3.1. Flags indicate absence of some value

4. Relational Set Operators

4.1. Relational algebra

4.1.1. Defines theoretical way of manipulating table contents using relational operators

4.1.1.1. Use of relational algebra operators on existing relations produces new relations:

4.1.1.1.1. PROJECT

4.1.1.1.2. UNION

4.1.1.1.3. INTERSECT

4.1.1.1.4. DIFFERENCE

4.1.1.1.5. JOIN

4.1.1.1.6. PRODUCT

4.1.1.1.7. DIVIDE

4.1.2. Relational Set Operators

5. Characteristic of a relational table

5.1. a table is perceived as a 2-D structure composed of rows and columns

5.2. Each table row (tuple) represents a single entity occurrence within the entity set

5.3. Each table column represent an attribute and each column has a distinct name

5.4. each row/column intersection represents a single data value

5.5. all values in a column must conform to the same data format

5.6. each column has a specific range of values known as the attribute domain

5.7. the order of the rows and column is immaterial to the DBMS

5.8. each table must have an attribute or a combination of attributes that uniquely identifies each row