Lesson 3- Relational Model Key Constraints Flashcards
What are keys used for?
- Used to uniquely identify an individual record of data
- Used to ensure the integrity, efficiency and proper structure of the data
Keys help?
- Help uniquely identify records (rows) in a table
- Enforce relationships between tables
- Optimize database operations like searching, sorting and filtering
What is a super key?
A combination of one or more attributes that uniquely identifies a row in a database table
What are two attributes of super key?
- Beyond the minimum need for uniqueness (non-minimal)
- It must always guarantee uniqueness for the entire table (uniqueness)
What is a candidate key?
A candidate key is a minimal superkey that can uniquely identify each row in a table
A table can have multiple candidate keys but one of them is chosen as _______
The primary key
Describe three features of candidate keys
- Unique for every row, with no redundant columns
- Minimal
- Can have more than one candidate key in a table
Every candidate key and primary key is a superkey but not every superkey is a _______
Candidate key
What is a primary key?
A column or a set of columns in a table that uniquely identifies each row in that table
Describe some characteristics of primary keys
- No two rows can have the same values in the primary key column
- It cannot contain NULL values
- Must be unique for each record
- Can be a single attribute (simple key)
- A combination of attributes (composite key)
- There is only one primary key per table
What is an alternate/secondary key?
An alternate key is any candidate key that is not chosen as the primary key (its an alternate option for uniquely identifying rows
What is a characteristic of an alternate key?
Exists when a table has multiple candidate keys
What is a composite key?
A type of candidate key that consists of two or more columns
What is a unique key?
A unique key is a column or set of columns that ensures all values in a column (or set of columns) are unique across rows, but is can contain NULL values (unlike a primary key) = can be a single column or a combination of columns
A unique key allows _______
Allows for NULL vaalues, but all non-NULL values must be unique
Superkey vs composite key
- Superkey= Any set of one or more attributes that uniquely identifies a row, can be a single attribute or a combination of attributes, may contain unecessary attributes (not minimal)
- Composite key= A candidate key that consists of two or more attributes, always a combination of two or more attributes, must be minimal with no extra attributes
What is a foreign key?
A column or a set of columns in one table that references the primary key of another table
What does a foreign key do?
- It establishes and enforces a relationship between the two tables
- It creates a link between two tables by ensuring that the value in the foreign key column corresponds to an existing value in the primary key column of the referenced table
- Used to enforce referential integrity
What is the primary key and foreign key for this example?
What are referential integrity constraints used for?
Used to establish rules for referential keys
Two types of reference table are dealt with?
- Referencing table that holds the primary key
- Referenced table that holds the foreign key
