DBMS Week 5

DBMS Week 5

By Bobby Liu

Terminology

Relation - Table / File

Tuple - Row / Record

Attribute - Column / Field​

Relational Keys

Candidate Key - Minimal set of attributes to uniquely identify tuple

Composite Keys - Multiple attribute keys

Primary Key - One Candidate key to uniquely identify tuple

Alternate Keys - Candidate keys not selected to be primary key​

Multiple Select

What are the criteria for choosing a primary key? (More than one answer)

• Minimal set of attributes

• Less likely to have its values changed

• Less likely to lose uniqueness in the future

• With fewest characters

• Easier to use from the user's point of view

Foreign key must match candidate key of home relation or wholly null

Referential Integrity

Primary key - no nulls

Entity Integrity

Relational Integrity Rules

Multiple Choice

Selection

• Select tuples in a relation that satisfy selection condition

• Select certain attributes from the relation and discards other attributes

Multiple Choice

Intersection

• Result includes tuples in 2 relations and duplicates are removed

• Result must include tuples in 2 relations

• Result includes tuples in one relation only

Multiple Choice

Outer Join

• Combine tuples with same value in join condition

• Combine tuples and keep all tuples even if there is no matching tuple

• Combine tuples and keep all tuples if there is matching tuples

Cartesian Product

Combine tuples from two relations so that related tuples can be identified