Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

Database Systems > Normalisation (Week 12 Test) > Flashcards

Normalisation (Week 12 Test) Flashcards

1
Q

What is the purpose of normalization?

A

To group attributes into relations to minimize data redundancy and reduce file storage space required by base relations

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

What are the three reasons to normalize a database?

A
  • Minimize duplicate data
  • Minimize or avoid update anomalies
  • Simplify queries to the database
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is redundancy in the context of databases?

A

Storing information redundantly, which wastes storage, decreases performance, and complicates data maintenance

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

What is an update anomaly?

A

Problems that arise from redundant information in tables, which can lead to inconsistent data

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

What is an insertion anomaly?

A

Occurs when a new record cannot be added without including redundant data or violating entity integrity

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

What is a deletion anomaly?

A

Occurs when deleting a record inadvertently removes important data about other entities

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

What is a modification anomaly?

A

Occurs when changes to data must be made in multiple places due to redundancy, risking inconsistency

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

What is a functional dependency?

A

A relationship where one attribute determines another; if A is known, B can be uniquely identified

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

How do you recognize the quality of relations design?

A

By identifying functional dependencies and ensuring the tables meet normalization requirements

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

What are the most commonly used normal forms?

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

What is the lossless-join property?

A

Ensures that any instance of the original table can be identified from corresponding instances in the smaller tables

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

What is the dependency preservation property?

A

Ensures that a constraint on the original table can be maintained by enforcing some constraint on each of the smaller tables

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

What is the process of normalization?

A

A formal technique for analyzing tables based on their primary keys and functional dependencies to decompose unsatisfactory tables

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

What is a normal form?

A

A condition using keys and functional dependencies of a relation/table to determine whether a relation schema is in a particular normal form

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

What are the higher normal forms beyond 3NF?

A
  • Boyce–Codd Normal Form (BCNF)
  • 4NF
  • 5NF
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

True or False: Most of the time, you can stop at 3NF for good database design.

A

True

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

Fill in the blank: The technique of normalization involves a set of ______ to test individual tables.

A

rules

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

What is the main goal of decomposing a larger table into smaller tables?

A

To eliminate redundancy and prevent update anomalies

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

What happens if updates are not carried out in a poorly structured table?

A

The database may become inconsistent

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

How can one find functional dependencies in a relation?

A

By understanding the meaning of attributes and checking if one attribute consistently determines another

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

What is the determinant in a functional dependency?

A

An attribute or a group of attributes on the left-hand side of the functional dependency arrow

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

What is the impact of data redundancy on performance?

A

It decreases performance due to the increased effort needed for searching, sorting, and filtering data

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

What is a functional dependency?

A

An attribute or a group of attributes on the left-hand side of the functional dependency arrow.

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

What does the notation A → B imply in functional dependencies?

A

A determines B

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
What is a composite determinant?
A determinant that consists of more than one attribute.
26
What are the characteristics of functional dependencies used in normalization?
* One-to-one relationship between determinant and dependent attributes * Hold for all time * Minimal number of attributes necessary
27
What is normalization?
A formal technique for analyzing tables based on their primary key and functional dependencies.
28
What is the First Normal Form (1NF)?
A relational table storing information with atomic values and no repeating columns.
29
What does 1NF disallow?
* Composite attributes * Multivalued attributes * Nested tables/relations
30
What is the purpose of Second Normal Form (2NF)?
To eliminate partial dependencies on the primary key.
31
What does full functional dependency (FFD) indicate?
B is fully dependent on A if B is functionally dependent on A but not on any proper subset of A.
32
What is the Third Normal Form (3NF)?
Must satisfy the rules of 2NF and all columns should not be transitively dependent on the Primary Key.
33
What is an atomic value?
A single value at the intersection of each row and column in a table.
34
What is a repeating column?
A column that has multiple values for a single record.
35
What is an example of a relation schema that is NOT in 1NF?
A table with multivalued attributes or nested relations.
36
What is the primary key (PK) of a table?
A unique identifier for each row in the table.
37
How can you identify functional dependencies in a table?
By analyzing attributes to see if they describe what the primary key identifies.
38
What happens if an attribute is partially dependent on the primary key?
It should be moved to a different table with a copy of the primary key.
39
What is the effect of normalization on database design?
It narrows tables to have a single purpose and brings clarity to database design.
40
What is the process to move from 1NF to 2NF?
Identify the primary key, determine the table's purpose, and analyze functional dependencies.
41
What should be done if attributes in a table violate 2NF?
Decompose the table to eliminate partial dependencies.
42
What does the notation (A, B) → C signify?
Neither A nor B will determine C by itself.
43
What is an example of a table that is in 1NF?
A table where each cell contains a single value, with no repeating groups.
44
What is the purpose of decomposing a table in normalization?
To reduce redundancy and avoid update anomalies.
45
What is a relation with nested relations?
A relation that contains another relation as an attribute.
46
What does 2NF stand for?
Second Normal Form
47
What is a key characteristic of a relation in 2NF?
All non-primary key attributes are fully dependent on the primary key.
48
Is R1 in 2NF?
Yes
49
Is R2 in 2NF?
No
50
Why is R2 not in 2NF?
Because ProjectName and ProjectLoc are NOT fully dependent on the primary key (StaffNo, ProjectNo).
51
What functional dependency is used to decompose EMPLOYEE_PROJECT into R1 and R2?
FD2
52
What does the 2NF version of EMPLOYEE_PROJECT consist of?
R1, R2_1, and R2_2
53
What is the definition of transitive dependency?
If A → B and B → C, then C is transitively dependent on A through B.
54
What is the purpose of 3NF?
To eliminate transitive dependencies on the primary key.
55
What is a defining feature of a table in 3NF?
It contains only columns that are non-transitively dependent on the primary key.
56
What do you need to do to move from 2NF to 3NF?
Identify and remove transitive dependencies by placing them in a new relation.
57
Is STAFF-BORROWER_2 in 3NF?
No
58
What is the issue with STAFF-BORROWER_2 that prevents it from being in 3NF?
FD2 creates a transitive dependency between Salary and StaffNo.
59
What is the significance of non-loss decomposition?
Properly normalized tables can always be combined back without loss of information.
60
What is the first step in normalizing the relation RENT to 2NF?
Identify functional dependencies that violate 2NF.
61
What functional dependencies are present in the RENT relation?
FD1, FD2, FD3, FD4
62
Fill in the blank: A table is in 3NF if it is in 2NF and contains only columns that are ______.
non-transitively dependent on the primary key
63
True or False: A relation can be in 2NF but not in 3NF.
True
64
What is a common issue that can arise in tables containing both grade and salary columns?
Inconsistency problems if one is updated and not the other.
65
What does 2NF stand for?
Second Normal Form ## Footnote 2NF reduces redundancy by ensuring that all non-key attributes are fully functionally dependent on the primary key.
66
What does 3NF stand for?
Third Normal Form ## Footnote 3NF requires that all attributes are only dependent on the primary key, eliminating transitive dependencies.
67
What is the purpose of normalization?
To reduce redundancy and eliminate update anomalies ## Footnote Normalization improves data integrity and consistency.
68
Is every 3NF relation also in 2NF?
True ## Footnote Every higher normal form includes all the properties of the previous forms.
69
What is the relationship between normal forms?
Each normal form is strictly stronger than the previous one ## Footnote This hierarchy ensures increased data integrity.
70
What are functional dependencies?
Relationships that define how one attribute relates to another ## Footnote They are key to establishing the structure of normalization.
71
Fill in the blank: Every 2NF relation is in ______.
1NF ## Footnote This indicates that every relation in 2NF must also meet the criteria of 1NF.
72
What does each normal form provide?
A higher level of data integrity ## Footnote This is achieved by controlling redundancy and eliminating update anomalies.

Database Systems (7 decks)

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