MIS 6326: Chapter 3, The Relational Data Model

Primary Key

A designated candidate key; cannot contain null values

Candidate Key

A minimal superkey; that is, a key that does not contain a subset of attributes that is itself a superkey.

CREATE TABLE Statement

CREATE TABLE Student ( StdNo CHAR(11), StdFirstName VARCHAR(50), StdLastName VARCHAR(50), StdCity VARCHAR(50), StdState CHAR(2), StdZip CHAR(10), StdMajor CHAR(6), StdClass CHAR(6), StdGPA DECIMAL(3,2) )

Foreign Key

Column(s) whose values must match the values in a candidate key of another table

Superkey

Column(s) with unique values

Summarize Operator

Decision-making operator Compresses groups of rows into calculated values Simple statistical (aggregate) functions Not part of original relational algebra

Restrict

Do not permit action on the referenced row

Self-Referencing Relationships

Foreign key that references the same table. Represents relationships among members of the same set. not common, but important in specialized situations. (I.e. employee table that has manager relationship)

Referential Integrity (Definition)

Foreign keys must match candidate key of source table. Foreign keys can be null in some cases. In SQL, foreign keys associated with primary keys

Referenced Rows

Foreign keys reference rows in the associated primary key table. Enrollment rows refer to Student and Offering

Referential Integrity

Foreign keys. Values of a column in one table match values in a source table. Ensures valid references among tables

Outer Join Overview

Join excludes non matching rows Preserving non matching rows is important in some business situations Outer join variations" Full outer join One-sided outer join

Divide Operator

Match on a subset of values Suppliers who supply all parts Faculty who teach every IS course Specialized operator Typically applied to tables representing M-N relationships

Natural Join Operator

Most common join operator Requirements Equality matching condition Matching columns with the same unqualified names Remove one join column in the result Usually performed on PK-FK join columns

Join Operator

Most databases have many tables Combine tables using the join operator Specify matching condition Can be any comparison but usually = PK = FK most common join condition Relationship diagram useful when combining tables

Entity Integrity (Definition)

No two rows with the same primary key value. No null values in any part of a primary key

Cascase

Perform action on related rows

Entity Integrity

Primary keys. Each table has column(s) with unique values. Ensures entities are traceable

Extended Cross Product

Produces excessive data. an operator that builds a table consisting of all combinations of rows from each of the two input tables

Record-Oriented

Record type, file, record, field

Set-Oriented

Relation, Tuple, Attribute

Union Compatibility

Requirement for the traditional set operators Strong requirement Same number of columns Each corresponding column is compatible Positional correspondence Apply to similar tables by removing columns first

M-N Relationships

Rows of each table are related to multiple rows of the other table. Not directly represented in the relational model. Instead, use two 1-M relationships and a table.

Default

Set foreign keys to a default value

Null value

Special value meeting unknown or inapplicable

Table-Oriented

Table, Row, Column

Nullify

only valid if foreign keys accept null values