Database Technologies - Collection 5

Superkey

Any combination of columns that, when taken together, will always point to one specific row

• A set of attributes with whose help you can find all other attributes of a relation

• If a functional dependency includes all fields of a table, the determinant of that FD is a Superkey

Examples (in an Employee roster):

• {EmployeeID}

• {SocialSecurityNumber}

• {EmployeeID, FirstName} —> First Name is redundant, but still works

Candidate Key

A minimal super key with no redundant attributes!

• It must contain unique values, no null values

• May have multiple attributes

• Should contain minimum fields to ensure uniqueness

Composite Key

Primary key that is comprised of two or more columns, when a single column is not sufficient enough to identify a row!

• Example: NetID and RUID

Foreign Key

One or more columns, usually in a different table, that exist in at least in one record of the column to which the foreign key refers!

• Primary Key from a foreign table is the Foreign Key in the local table!

• Example: Cookie Monster:, Elmo:, Ernie: —> one column: Muppet Performers (foreign key) // —> Muppet Performers (primary Key): Age, Education, Gender, Date of Starting Career

No

Is NULL zero?

No

Is NULL an empty string?

Unique Field

Means that every non-null row for that column is unique; no two rows contain the same values!

Unique

The values within Foreign Keys don't have to be unique, but they must refer to a field in a foreign table that is:

• Example: Some differently identified employees under a Department might work in the same sub-department, but as the Primary Key, in the foreign table, each sub-department will be uniquely referred to!

Artificial Key / Surrogate Key

A column that is NOT generated from database data, as the DBMS generates a unique identifier for you. They are frequently utilized as primary keys.

• The value is normally auto-generated and guaranteed to be unique!

• Example: 101, 102, 103 ..

Subset

Means every element in one set is also contained in another

• Symbol: ⊆

• If A = {1, 2} and B = {1, 2, 3} then A ⊆ B

Proper subset

A subset that CANNOT be equal to the original set.

• Symbol: ⊂

• If set A is not a proper subset of set B, then that means set B must have at least one item that is not in set A

Are there proper subsets in a Candidate Key? NO!

{A}, {B}, {A, B}. and {}

Problem: What are the subsets of {A, B}?

Yes, Yes, No

Problem: You have the following functional dependencies: B → ACD, ACD → B, A → D

• Is B a Superkey?

• Is ACD a Superkey?

• Is A a Superkey?

Super, candidate, candidate

Remember: Every primary key is a _______ and a ______ key. However, not every super key is a _________ key.

Primary Key

The Candidate Key that the database designer CHOOSES to be the main, official, and most convenient unique identifier for the table.

• One column that you've designated as the official way to look up a row.

• Unique, not null, and simple

Alternative Key

Any Candidate Key that has not been chosen as the Primary Key!

Non-Key

Any field that does not serve as a candidate, primary, alternative, or foreign key!

Data Tables

Store Subject Data!

Subset Tables

Stores extended Subject Data for an existing Data Table, serving as a specific type of the Data Table it is associated with!

• EXAMPLE: An “EMPLOYEE” Data Table associated with two ______ Data Tables

  • FULL_TIME_EMPLOYEE

  • PULL_TIME_EMPLOYEE

Validation Tables

Tables that store an enumerated list of valid data values used to populate Fields in other Data Tables.

• Mechanism to enforce Business Rules

• For example, a Validation table could store the allowed states to which a company will ship products to!

Linking Tables / Intersection Tables

Provide a way of

Associative Table

A specific type of Linking table which has Attributes. For example, a PERSON buys a Product (relationship). An Associative Table may identify a time Attribute as to when this relationship occured.