4.11 Boyce-Codd normal form//4.12 Applying normal form

0.0(0)
studied byStudied by 0 people
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
Card Sorting

1/7

encourage image

There's no tags or description

Looks like no tags are added yet.

Study Analytics
Name
Mastery
Learn
Test
Matching
Spaced

No study sessions yet.

8 Terms

1
New cards

candidate key

  • all column values are unique and a 'candidate' for primary key.

  • A candidate key is a simple or composite column that is unique and minimal.

  • A table can have multiple candidate keys.

  • Minimal

    • Minimal means all columns are necessary for uniqueness.

    • The smallest set of attributes for unique identification(candidate key), promoting efficiency.

    • Primary key usually is unique and minimal.

    • e.g. choosing only one column with unique values for primary key, instead of a composite column.

2
New cards

non-key

A non-key column is a column that is not a possible candidate key.

3
New cards

third normal form

  • A table is in third normal form when all non-key columns depend on the key, the whole key, and nothing but the key.

  • A table is in third normal form if, whenever a non-key column A depends on column B, then B is unique.

4
New cards

Boyce-Codd normal form

  • Learn Boyce-Codd Normal Form (BCNF)

  • Improved version of 'third normal form'.

  • While 'third normal form' applies to all 'non-key' columns(which will allow occasional redundancy), Boyce-Codd normal form applies to ALL columns(which eliminates this redundancy).

  • Boyce-Codd normal form eliminates all redundancy arising from functional dependence.

  • all dependencies(functional dependency) are on unique columns.

  • In a Boyce-Codd normal form table, if column A depends on column B, then B must be unique.

  • Boyce-Codd normal form is ideal for tables with frequent inserts, updates, and deletes.

5
New cards

Trivial Dependencies

  • When the columns of A are a subset of the columns of B, A always depends on B. Ex: FareClass depends on (FlightCode, FareClass). These dependencies are called trivial.

6
New cards

Normalization

  • Normalization eliminates redundancy by decomposing a table into two or more tables.

  • redundancy is eliminated with normalization, the last step of logical design.

7
New cards

depends on

  • Column A depends on column B when each B value is related to at most one A value. A and B may be simple or composite columns.

8
New cards

Denormalization

  • Denormalization means intentionally introducing redundancy by merging tables, to eliminate JOIN queries.