Chapter 2: Relational database

Composite/Bridge Entity

used to break up M:N relationships and includes as foreign keys the primary keys of the linked tables.

Product

yields all possible pairs of rows from two tables

Difference

yields all rows in table 1 not found in table 2

Project

yields all values for selected attributes

Intersect

yields only the rows that appear in both tables

Select

yields values for all rows found in a table

Composite Key

▪ A key that contains two or more attributes ▪ For unique identification of a row, often it is necessary to use a composite key to identify each record uniquely

Surrogate Key

▪ A unique, made up value that is added to a relation to serve as the primary key ▪ Surrogate key values have no meaning to business users

Candidate Key

▪ An attribute (or combination of attributes) that is able to help uniquely identify a row ▪ This key should be able to become the primary key of a table

System catalog

▪ Contains metadata ▪ Detailed system data dictionary that describes all objects within the database, such as tables, indexes, views, stored procedures, etc.

Unique index

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

Indexes

▪ Orderly arrangement to logically access rows in a table

Primary Key Notation

▪ We will underline the primary key(s) as an identification to indicate the unique key shown in the example below.

Index key

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

Data dictionary

▪Provides detailed accounting of all tables found within the user/designer-created database ▪ Contains (at least) all the attribute names, data types, and characteristics for each table in the system ▪ Contains metadata

Primary Key

- A candidate key chosen to uniquely identify all other attribute values in any given row ▪ If you know the value of the primary key, you will be able to uniquely identify a single row ▪ The attribute you choose to be your Primary Key cannot contain null entries

All values in a column:

- Are values of the same attribute - Must conform to the same data format

Each table must have an identifier that uniquely identifies each row within the table :

- Value from a single attribute/column - Combined values from two or more attributes/columns

For a table to be considered a relation:

1. A table is perceived as a two-dimensional structure composed of rows and columns 2. Each table row 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 row/column intersection 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 a combination of attributes that uniquely identifies each row

Null value

A marker that a value does not exist for a field and is differentiated from a value that has been missed being entered by a user.

Foreign Key

A primary key of one table that appears as an attribute in another table and acts to provide a logical relationship between the two tables

Entity Integrity Requirement

All primary key entries are unique, and no part of a primary key may be null.

Secondary Key

An attribute or combination of attributes used strictly for data retrieval purposes.

Single-Values is also referred to as

Atomic

M:N relationship

Cannot be implemented as such in the relational model Can be changed into two 1:M relationships

The Relational Model

Developed by E. F. Codd (IBM) in 1970, the relational model represented a major breakthrough for both users and developers

Characteristics of a Relation: Each row is ___

Distinct All values in cells taken together cannot repeat

Notation for a Relation

ELATION_NAME(Column1, Column2, Column3, ..., ColumnN) STUDENT(Gnumber, LastName, FirstName, Email)

Characteristics of a Relation

Each column represents an attribute and has a distinct name

Entity Integrity Purpose

Each row will have a unique identity, and foreign key values can properly reference primary key values

Order of columns is immaterial

If columns are reordered, resulting two table will still be considered "identical"

Order of rows is immaterial

If rows are reordered, resulting table should still be considered "identical"

A Key's role is based on the principle of determination

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

A ____ is one or more attributes that determine other attributes

Key

1:1 relationship

One entity related to only one other entity Should be rare in any relational database design

1:M relationship

One-to-many Norm for relational databases

Relation

Two-dimensional table that has special characteristics

Join

allows information to be combined from two or more tables

Union

combines all rows from two tables, excluding duplicate rows

If a key consist of two or more attributes, it is called a _____

composite key

relational algebra

defines the theoretical way of manipulating table contents using relational operators

A properly designed relational database exhibits ________ and __________ that describe requirements for primary and foreign keys.

entity and referential

Referential Integrity

every value of a foreign key must match a value of an existing primary key

relational schema (or relational diagram)

graphical representation of entities, attributes, and relationships

Natural (inner) Join

links tables by selecting rows with common values in common attributes

Outer Join

lists rows from one of the tables even when there are no marching values in other table. Missing values in the second table are to NULL

A surrogate key is often created:

o To replace a composite primary key (3 or more columns) o Primary Key has privacy/security implications o Primary Key column is difficult to verify (or control for quality)

Characteristics of a Relation: All entries in a table are __________.

single-valued

Entity (Table, File)

something of importance to a user that needs to be represented in a database An entity represents one theme or topic about which data is being store