Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

CMPG 311 > Chapter 6: Normalisation > Flashcards

Chapter 6: Normalisation Flashcards

(48 cards)

Start Studying
1
Q

Normalisation

A

Evaluating and correcting table structures to minimise data redundancies
-reduces data anomalies
-Assigns attributes to tables based on determination

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Different normal forms

A

First normal form (1NF)
Second normal form (2NF)
Third normal form (3NF)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Structural view of normal forms

A

Higher normal forms are better than lower one

Properly designed 3NF structures meet the requirement of 4NF

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Denormalisation

A

Produces a lower normal form

-Results in increased performance, and greater data redundancy

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

The need for normalisation

A

Gives analyses on relationships among entities
-Determines if the structure can be improved with normalisation
-Improves existing data structure and creates an appropriate database design.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

The objective is…

A

That each table conforms to the concept of well-formed relations

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Well-formed relations

A

-Each table represents a single subject
-Each row/column intersection contains only one value.
-No data item is unnecessarily stored in more than one table
-All nonprime attributes in a table are dependent on the primary key
-Each table has no insertion, update or delete anomalies

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

The normalization process…

A

Ensures that all tables are in at least 3NF
-Higher forms are not that common in business

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Normalization works on…

A

One relation at a time
-Identifies the dependencies of a relation (table)
-Progressively breaks the relation up into a new set of relations

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

1NF characteristics

A

Table format, no repeating groups and PK identified

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

2NF characteristics

A

1NF and no partial dependencies

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

3NF characteristics

A

2NF and no transitive dependencies

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Boyce-codd normal form (BCNF)

A

Every determinant is a candidate key (special case of 3NF)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

4th Normal form characteristics

A

3Nf and no independent multivalued dependencies

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Functional dependence

A

The attribute B is fully functionally dependent on the attribute A if each value of A determines one and only one value of B

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Functional dependence (generalized definition)

A

Attribute A determines attribute B (that is, B is functionally dependent on A) if all of the rows in the table that agree in value for attribute A also agree in value for attribute B

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Fully functional dependence (composite key)

A

If attribute B is functionally dependent on a composite key A but not on any subset of that composite key, then B is fully functionally dependent onA

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Partial dependency

A

Functional dependence in which the determinant is only part of the primary key
-Assumption: one candidate key
-straight forward
-Easy to identify

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Transitive dependency

A

Attribute is dependent on another attribute that is not part of the primary key
-More difficult to identify among a set of data
-Occur only when a functional dependence exists among non-prime attributes

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Repeating groups

Study These Flashcards
A

Group of multiple entries of the same type can exist for any single key attributes occurrence
-Reduces data redundancies

21
Q

Steps to convert to first normal form

Study These Flashcards
A

-Eliminate the repeating groups
-Eliminate attributes with more than one value
-Identify the primary key
-Identify all dependencies

22
Q

Dependency diagram

Study These Flashcards
A

Depicts all dependencies found within a given table structure

-Helps get an overview of all relationships among table attributes

-Makes it less likely that an important dependency will be overlooked

23
Q

1NF describes (in tabular format)

Study These Flashcards
A

-All key attributes are defined

-There are no repeating groups in the table

-There are no attributes with more than one value

-All attributes are dependent on the primary key

24
Q

All relational tables…

Study These Flashcards
A

Satisfy 1NF requirements

25
Conversion from 1NF to 2NF
-Make new tables to eliminate partial dependencies -Reassign corresponding dependent attributes
26
Table is in 2NF
-Is in 1NF -Includes no partial dependencies
27
Conversion to 2NF occurs...
Only when the 1NF has a composite primary key -If the 1NF has a single-attribute primary key, then the table is automatically in 2NF
28
Conversion from 2NF to 3NF
-Make new tables to eliminate transitive dependencies -Reassign correspond dependent attributes
29
Table is in 3NF when...
-It is in 2NF -Contains no transitive dependencies
30
Normalisation is valuable because...
Its use helps to eliminate data redundancies
31
Atomic attribute
Cannot be further subdivided
32
Atomicity
Characteristic of an atomic attribute
33
Granularity
Level of detail represented by the values stored in a table's row
34
Normalisation benefits
-Evaluate PK assignments and naming conventions -Refine attribute atomicity -Identify new attributes and new relationships -Refine primary keys as required for data granularity -Maintain historical accuracy and evaluate using derived attributes
35
Surrogate key considerations
This is used when there is no natural primary key -A system defined attribute key is made -Typically numeric value which is incremented with each row
36
Normalisation should be part of the design process
Proposed entities must meet the required normal form before table structures are created
37
Principles and normalisation procedures to be understood to redesign and modify databases
-ERD is created through an iterative process -Normalisation focuses on the characteristics of specific entities
38
Why we denormalise tables
When we normalise a table, we typically increase the amount of tables we work with (due to table decomposition). This then takes additional operations and processing logic, reducing the speed
39
Defects of unnormalised tables
-Data updates are less efficient because tables are larger -Indexing is more cumbersome -No simple strategies for creating virtual tables known as views
40
Data-modelling checklist: Business rules
-Document and verify all business rules with end users -Ensure precise, clear and simple business rule descriptions -Justify, date and sign off on each business rule and identify the sources.
41
Data-modelling checklist: Naming conventions
All names should be limited in length (database-dependent size)
42
Entity names should...
-Be nouns that are familiar but short and meaningful to the business. -Document abbreviations, synonyms and aliases for each entity. -Be unique within the model -(for composite entities) include a combination of abbreviated names of the entities, linked through the composite entity
43
Attribute names
-Be unique -Entity name abbreviation as a prefix -Descriptive of the characteristics -Should use suffixes for PK -Should not be a reserved word -No special characters or spaces
44
Relationship names
Should be verbs that indicate the nature of relationship
45
Entities
-Represent a single subject -Each entity should represent a unique entity instances -All entities should be 3NF or higher -Granularity of the entity instance should be clearly defined -PK should be clearly defined and support the selected data
46
Attributes
-Be atomic -Document default values, constraints, synonyms and aliases -Derived attributes should be identified and include sources -Should not be redundant unless this is required for transaction accuracy, performance or maintaining a history -Non-key attributes must be fully dependent on PK attribute
47
Relationships should...
-Clearly identify relationship participants -Clearly define participation, connectivity, and document cardinality.
48
ER model
-Validate against processes: inserts, updates and deletions -Evaluate where, when, and how to maintain a history -Not contain redundant relationships -Minimise redundancy to ensure single-place updates -Confor to minimal dat rule

CMPG 311 (7 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions