Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

Database Systems > Normalisation > Flashcards

Normalisation 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 the primary key in the STAFF-BORROWER table?

A

staffNo and bookNo

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
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
10
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
11
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
12
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
13
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
14
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
15
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
16
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
17
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
18
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
19
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
20
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
21
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
22
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
23
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
24
Q

What is an example of a functional dependency from the STAFF-BORROWER table?

A

staffName, staffDept, and grade are functionally dependent on staffNo

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
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
26
Q

What is an example of a functional dependency from STAFF-BORROWER?

A

staffNo → staffName, staffDept, grade

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

What is the primary key of STAFF_BORROWER?

A

(staffNo, bookNo)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
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
29
Q

What is a composite determinant?

A

A determinant that consists of more than one attribute.

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

What are the characteristics of functional dependencies used in normalization?

A
  • One-to-one relationship between determinant and dependent attributes
  • Hold for all time
  • Minimal number of attributes necessary
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

What is normalization?

A

A formal technique for analyzing tables based on their primary key and functional dependencies.

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

What is the First Normal Form (1NF)?

A

A relational table storing information with atomic values and no repeating columns.

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

What does 1NF disallow?

A
  • Composite attributes
  • Multivalued attributes
  • Nested tables/relations
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

What is the purpose of Second Normal Form (2NF)?

A

To eliminate partial dependencies on the primary key.

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

What does full functional dependency (FFD) indicate?

A

B is fully dependent on A if B is functionally dependent on A but not on any proper subset of A.

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

What is the Third Normal Form (3NF)?

A

Must satisfy the rules of 2NF and all columns should not be transitively dependent on the Primary Key.

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

What is an atomic value?

A

A single value at the intersection of each row and column in a table.

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

What is a repeating column?

A

A column that has multiple values for a single record.

39
Q

What is an example of a relation schema that is NOT in 1NF?

A

A table with multivalued attributes or nested relations.

40
Q

What is the primary key (PK) of a table?

A

A unique identifier for each row in the table.

41
Q

How can you identify functional dependencies in a table?

A

By analyzing attributes to see if they describe what the primary key identifies.

42
Q

What happens if an attribute is partially dependent on the primary key?

A

It should be moved to a different table with a copy of the primary key.

43
Q

What is the effect of normalization on database design?

A

It narrows tables to have a single purpose and brings clarity to database design.

44
Q

What is the process to move from 1NF to 2NF?

A

Identify the primary key, determine the table’s purpose, and analyze functional dependencies.

45
Q

What should be done if attributes in a table violate 2NF?

A

Decompose the table to eliminate partial dependencies.

46
Q

What does the notation (A, B) → C signify?

A

Neither A nor B will determine C by itself.

47
Q

What is an example of a table that is in 1NF?

A

A table where each cell contains a single value, with no repeating groups.

48
Q

What is the purpose of decomposing a table in normalization?

A

To reduce redundancy and avoid update anomalies.

49
Q

What is a relation with nested relations?

A

A relation that contains another relation as an attribute.

50
Q

What is an example of a functional dependency in STAFF?

A

staffNo → staffName

51
Q

What does 2NF stand for?

A

Second Normal Form

52
Q

What is a key characteristic of a relation in 2NF?

A

All non-primary key attributes are fully dependent on the primary key.

53
Q

Is R1 in 2NF?

54
Q

Is R2 in 2NF?

55
Q

Why is R2 not in 2NF?

A

Because ProjectName and ProjectLoc are NOT fully dependent on the primary key (StaffNo, ProjectNo).

56
Q

What functional dependency is used to decompose EMPLOYEE_PROJECT into R1 and R2?

57
Q

What does the 2NF version of EMPLOYEE_PROJECT consist of?

A

R1, R2_1, and R2_2

58
Q

What is the primary key for R1?

59
Q

What attributes are included in R2?

A

StaffNo, ProjectNo, Hours, ProjectName, ProjectLoc

60
Q

What is the primary key for R2_1?

61
Q

What is the primary key for R2_2?

A

(StaffNo, ProjectNo)

62
Q

What is the definition of transitive dependency?

A

If A → B and B → C, then C is transitively dependent on A through B.

63
Q

What is the purpose of 3NF?

A

To eliminate transitive dependencies on the primary key.

64
Q

What is an example of a transitive dependency?

A

StaffNo → Grade → Salary

65
Q

What is a defining feature of a table in 3NF?

A

It contains only columns that are non-transitively dependent on the primary key.

66
Q

What do you need to do to move from 2NF to 3NF?

A

Identify and remove transitive dependencies by placing them in a new relation.

67
Q

What is the primary key for STAFF-BORROWER_2?

A

(StaffNo, BookNo)

68
Q

Is STAFF-BORROWER_2 in 3NF?

69
Q

What is the issue with STAFF-BORROWER_2 that prevents it from being in 3NF?

A

FD2 creates a transitive dependency between Salary and StaffNo.

70
Q

What is the primary key for the PAY-SCALE relation?

71
Q

What is the significance of non-loss decomposition?

A

Properly normalized tables can always be combined back without loss of information.

72
Q

What is the first step in normalizing the relation RENT to 2NF?

A

Identify functional dependencies that violate 2NF.

73
Q

What are the primary keys for RENT1 and RENT2?

A

RENT1: propertyNo; RENT2: (custNo, propertyNo)

74
Q

What are the primary keys for RENT2_1 and RENT2_2?

A

RENT2_1: custNo; RENT2_2: (custNo, propertyNo)

75
Q

What is the primary key for the LOAN relation?

A

(StaffNo, BookNo)

76
Q

What functional dependencies are present in the RENT relation?

A

FD1, FD2, FD3, FD4

77
Q

Fill in the blank: A table is in 3NF if it is in 2NF and contains only columns that are ______.

A

non-transitively dependent on the primary key

78
Q

True or False: A relation can be in 2NF but not in 3NF.

79
Q

What is a common issue that can arise in tables containing both grade and salary columns?

A

Inconsistency problems if one is updated and not the other.

80
Q

What does the primary key consist of in the STAFF-BORROWER_3 relation?

81
Q

What is the primary key for the RENT1 relation?

A

propertyNo

This relation includes propertyNo and propertyAddress.

82
Q

What is the primary key for the RENT2_1 relation?

A

custNo

This relation includes custNo and custName.

83
Q

What is the primary key for the RENT2_2 relation?

A

(custNo, propertyNo)

This relation includes custNo, propertyNo, and rentStart.

84
Q

What does 2NF stand for?

A

Second Normal Form

2NF reduces redundancy by ensuring that all non-key attributes are fully functionally dependent on the primary key.

85
Q

What does 3NF stand for?

A

Third Normal Form

3NF requires that all attributes are only dependent on the primary key, eliminating transitive dependencies.

86
Q

What is the purpose of normalization?

A

To reduce redundancy and eliminate update anomalies

Normalization improves data integrity and consistency.

87
Q

Is every 3NF relation also in 2NF?

A

True

Every higher normal form includes all the properties of the previous forms.

88
Q

What is the relationship between normal forms?

A

Each normal form is strictly stronger than the previous one

This hierarchy ensures increased data integrity.

89
Q

What are functional dependencies?

A

Relationships that define how one attribute relates to another

They are key to establishing the structure of normalization.

90
Q

Fill in the blank: Every 2NF relation is in ______.

A

1NF

This indicates that every relation in 2NF must also meet the criteria of 1NF.

91
Q

What is the result of normalizing the RENT relation into 3NF?

A

Three relations: RENT1, RENT2_1, RENT2_2

Each of these relations maintains data integrity and reduces redundancy.

92
Q

What does each normal form provide?

A

A higher level of data integrity

This is achieved by controlling redundancy and eliminating update anomalies.

93
Q

What is the primary key for the relation FD3?

A

propertyNo

This relation includes propertyNo and propertyAddress.

94
Q

What is the primary key for the relation FD4?

A

custNo

This relation includes custNo and custName.

Database Systems (6 decks)

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