Lesson 3- Relational Model Key Constraints

Question 1

What are keys used for?

Answer 1

• Used to uniquely identify an individual record of data
• Used to ensure the integrity, efficiency and proper structure of the data

Question 2

Keys help?

Answer 2

• Help uniquely identify records (rows) in a table
• Enforce relationships between tables
• Optimize database operations like searching, sorting and filtering

Question 3

What is a super key?

Answer 3

A combination of one or more attributes that uniquely identifies a row in a database table

Question 4

What are two attributes of super key?

Answer 4

1. Beyond the minimum need for uniqueness (non-minimal)
2. It must always guarantee uniqueness for the entire table (uniqueness)

Question 5

What is a candidate key?

Answer 5

A candidate key is a minimal superkey that can uniquely identify each row in a table

Question 6

A table can have multiple candidate keys but one of them is chosen as _______

Answer 6

The primary key

Question 7

Describe three features of candidate keys

Answer 7

1. Unique for every row, with no redundant columns
2. Minimal
3. Can have more than one candidate key in a table

Question 8

Every candidate key and primary key is a superkey but not every superkey is a _______

Answer 8

Candidate key

Question 9

What is a primary key?

Answer 9

A column or a set of columns in a table that uniquely identifies each row in that table

Question 10

Describe some characteristics of primary keys

Answer 10

• 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

Question 11

What is an alternate/secondary key?

Answer 11

An alternate key is any candidate key that is not chosen as the primary key (its an alternate option for uniquely identifying rows

Question 12

What is a characteristic of an alternate key?

Answer 12

Exists when a table has multiple candidate keys

Question 13

What is a composite key?

Answer 13

A type of candidate key that consists of two or more columns

Question 14

What is a unique key?

Answer 14

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

Question 15

A unique key allows _______

Answer 15

Allows for NULL vaalues, but all non-NULL values must be unique

Question 16

Superkey vs composite key

Answer 16

• 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

Question 17

What is a foreign key?

Answer 17

A column or a set of columns in one table that references the primary key of another table

Question 18

What does a foreign key do?

Answer 18

• 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

Question 19

What is the primary key and foreign key for this example?

Answer 19

Question 20

What are referential integrity constraints used for?

Answer 20

Used to establish rules for referential keys

Question 21

Two types of reference table are dealt with?

Answer 21

• Referencing table that holds the primary key
• Referenced table that holds the foreign key