Font size
WorksheetsNormalization
Total questions: 65
Worksheet time: 34mins
An entity is represented by what in a database?
Table
Row
Column
Cell
An attribute is represented by what in a database?
Table
Row
Column
Cell
The purposes of normalization are :
To ensure that the anomalies doesn’t occur
To reduce data or storage redundancy
To ensure easy for record searching/finding
All the above
"Occurs when an attribute is functionally dependent on another non-key attribute."
The above statement is referring to :
Full Functional Dependency
Partial Functional Dependency
Transitive Dependencies
A table is in 2NF if the table is in 1NF and what other condition is met?
There are no functional dependencies.
There are no null values in primary key fields.
There are no repeating groups.
There are no attributes that are not functionally dependent on the relation's primary key.
When you normalize a relation by breaking it into two smaller relations, what must you do to maintain data integrity?
Remove any functional dependencies from both relations
Assign both relations the same primary key field(s)
Create a primary key(s) for the new relation
Which of these are required for a relational database to meet 3rd normal form?
Must meet 2nd normal form
All the non-key attributes must depend on another non-key attribute
All the non-key attributes must not depend on another non-key attribute
Must meet 4th normal form
A 2NF table can exhibit ____ dependency.
partial
complete
transitive
indirect
Identify the types of Data Anomalies:
Add, Delete and Save anomalies
Insert, Add and Save anomalies
Update, Save and Insert anomalies
Insert, Delete and Update anomalies
Consider the relation scheme R = {D, E, F, G, H, I, J, K, L, M, N} and the set of functional dependencies {{E, F} -> {G}, {F} -> {I, J}, {E, H} -> {K, L}, K -> {M}, L -> {N} on R. What is the key for R?
{E, F}
{E, F, H}
{D, E, F, H}
{E, F, H, K, L}
Relation R has eight attributes ABCDEFGH. F = {CH -> G, A -> BC, B -> CFH, E -> A, F -> EG} is a set of functional dependencies (FDs) so that F+ is exactly the set of FDs that hold for R. How many candidate keys does the relation R have?
5
3
4
6
In a schema with attributes A, B, C, D and E following set of functional dependencies are given
{A -> B, A -> C, CD -> E, B -> D, E -> A}
Which of the following functional dependencies is NOT implied by the above set?
CD -> AC
BC -> CD
AC -> BC
BD -> CD
Expand BCNF
(a)
Which one of the following is a wrong informal design guidelines for relational schema?
Semantics of the Attributes
Reducing the Redundant Value in Tuples
Reducing Null values in Tuples
Allowing spurious Tuples.
If A->B holds and Y is attribute set, then AY->BY also holds. Name this rule
(a)
if A->B holds and B->C holds,
then A->C also holds.Name this rule
(a)
• If there are more than one attributes in primary key and ‘A’ be a non prime key attribute. If ‘A’ is not dependent upon all prime key attributes then ------exists.
(a)
In any relation R, if for a particular value of X, Y has more than one value.Name this functional dependency
(a)
In any relation R, X -> Y is ------------ if Y is a subset of X.
(a)
A relation is in second normal form if it is in 1NF and all non prime key attributes must be (a) dependent upon primary key attributes
For R(A,B,C,D), the set of FDs F(A-> B, B-> C, C-> A). Which of the following is/are the candidate key(s)
AD
ABC
A
D
For R(A,B,C, D), the set of FDs F(AB-> B, BC-> C, CD-> A). Which of the following is a candidate key
ACD
ABC
BC
BCD
Which of the following FD is redundant. For R(A,B,C), the set of FDs F(A-> AB, B-> C, A->B, C->B, B->A).
A->AB
B->C
B->A
None of the above
For R(A,B,C), the set of FDs F(A-> B, B-> C). Which of the following is NOT a SUPER key
ABC
AC
BC
AB
Following is the example of which axiom If X -> Y, then XZ -> YZ also XZ -> Y
Reflexive
Augmentation
Transitive
Union
Following is the example of which axiom If X -> YZ, then X -> Y and X -> Z
Reflexive
Transitive
Decomposition
Union
What are the prime attributes for the R = {A,B,C,D,E} with set of Fds Z={D -> E , CE-> A}
BCD
AE
CDE
CE
What are the NON-prime attributes for the R = {A,B,C,D,E,F,G} with candidate keys ABC, DF, ACG
E
A
ABC
DF
What are the prime attributes for the R = {A,B,C,D,E,F,G} with candidate keys ABC, DF, ACG
ABCD
ABCDF
ABCDFG
E
What are the NON-prime attributes for the R = {A,B,C,D,E,F,G} with candidate keys DCF & GCF
AB
ABE
C
CF
Which of the following statements is true regarding functional dependencies?
All attributes must be functionally dependent on a candidate key
Every attribute is functionally dependent on the primary key
Functional dependencies can exist without a primary key
Functional dependencies are only applicable in 1NF
Which functional dependency types is/are not present in the following dependencies?
Empno -> EName, Salary, Deptno, DName
DeptNo -> DName
EmpNo -> DName
Full functional dependency
Partial functional dependency
Transitive functional dependency
Both B and C
Which functional dependency types is/are not present in following dependencies?
StaffNo, BranchNo -> StaffName, BranchName, Position, DOB
StaffNo -> StaffName, Position, DOB
BranchNo -> BranchName
Full functional dependency
Partial functional dependency
Transitive functional dependency
Both B and C
A BCNF:
is always both loss less join and dependency preserving
always loss less join but may dependency preserving
may not loss less join but always dependency preserving
None of these
What is a composite primary key?
A primary key that can be duplicated in the same table
An attribute that is a primary key in another table
A foreign key that is a primary key in more than one other table
A primary key made up from two or more attributes
When you normalize a relation by breaking it into two smaller relations, what must you do to maintain data integrity?
Remove any functional dependencies from both relations
Assign both relations the same primary key field(s)
Create a primary key(s) for the new relation
Collection of tables
Entity
Attribute
Table
Database
Relationship
Collection of records
Entity
Attribute
Table
Database
Relationship
BCNF is
when the table has no partial dependencies
when every determinant in a table is a candidate key.
when the table is in 1st Normal Form
when there are no repeating groups of attributes
What is a lossless-join decomposition?
A way to simplify database queries
A technique to combine multiple databases
A method to ensure no information is lost during decomposition
A process that loses data during decomposition
Which of the following is a disadvantage of normalization?
Increases data redundancy
Can lead to complex queries
Improves data integrity
Enhances database performance
Given the following two statements:
S1: Every table with two single-valued attributes is in 1NF, 2NF, 3NF and BCNF.
S2: AB→C,D→E,E→C is a minimal cover for the set of functional dependencies AB→C,D→E,AB→E,E→C. Which one of the following is CORRECT?
S1 is TRUE and S2 is FALSE.
Both S1 and S2 are TRUE.
S1 is FALSE and S2 is TRUE.
Both S1 and S2 are FALSE.
Which of the following is TRUE?
Every relation in 3NF is also in BCNF
A relation R is in 3NF if every non-prime attribute of R is fully functionally dependent on every key of R
Every relation in BCNF is also in 3NF
No relation can be in both BCNF and 3NF
A relation is in ___ if it is in Boyce Codd normal form and does not have any multivalued dependencies.
1NF
2NF
3NF
4NF
Which of the following conditions for each nontrivial dependency of function X on function Y for a relation to be in third normal form.
A super key is X.
Y is a prime attribute.
Either A or B
None of the above
Which type of anomaly occurs when adding new data to a database causes unintended changes to existing data?
A) Insertion anomaly
B) Deletion anomaly
C) Update anomaly
D) Redundancy anomaly
Which normal form is designed to eliminate insertion, deletion, and update anomalies?
A) First Normal Form (1NF)
B) Second Normal Form (2NF)
C) Third Normal Form (3NF)
D) Boyce-Codd Normal Form (BCNF)
A university database contains a relation `StudentCourses` with attributes `(StudentID, CourseID, Grade)`. The database administrator notices that when a student drops a course, it leads to student's record is completely removed. Which type of anomaly is observed in this scenario?
A) Insertion anomaly
B) Deletion anomaly
C) Update anomaly
D) Dependency anomaly
An e-commerce database maintains a relation `OrderDetails` with attributes `(OrderID, ProductID, Quantity)`. When a product's quantity changes, the database administrator notices that this information requires modifying multiple records, leading to inconsistencies. Which type of anomaly is evident here?
A) Insertion anomaly
B) Deletion anomaly
C) Update anomaly
D) Dependency anomaly
In a manufacturing database, a relation `ProductInventory` has attributes `(ProductID, WarehouseID, Quantity)`. The database designer realizes that adding a new product to the inventory without assigning it to any warehouse is not allowed due to missing warehouse ID. Which type of anomaly is being prevented in this case?
A) Insertion anomaly
B) Deletion anomaly
C) Update anomaly
D) Dependency anomaly
A database system uses a relation `EmployeeSalary` with attributes `(EmployeeID, Salary)`. The database administrator notices that changing an employee's salary in multiple records every year is cumbersome and error-prone. Which type of anomaly is associated with this scenario?
A) Insertion anomaly
B) Deletion anomaly
C) Update anomaly
D) Dependency anomaly
A library database maintains a relation `BookLoans` with attributes `(BookID, MemberID, DueDate)`. When a member returns a book, the database administrator observes that deleting the corresponding record affects the book and member details. Which type of anomaly is evident here?
A) Insertion anomaly
B) Deletion anomaly
C) Update anomaly
D) Dependency anomaly
Let R(A,B,C,D) be a relation schema and F = {A->BC, AB->D, B->C} be the set of functional dependencies defined for R. Which will represent the closure of attribute set {B}?
(A) (A,C,D)
(B) (B,C)
(C) (A,B,C)
(D) (C,B)
(E) (B)
Following are the functional dependencies for relation R(PQRSTU):
PQ->RS, ST->U, R->T, U->S, PT->R and T->P
Which of the following is TRUE?
(A) (PQ)+ = (PRST)
(B) (ST)+ = (PQRSTU)
(C) (QT)+ = (PQRSTU)
(D) (PQ)+ = (PQRTU)
Considering the following relation and given dependencies:
(i) B->D
(ii) A-> C
(iii) ABC->D
(Iv) ABD->C
Which of the following FD is not correct:
(A) (i) and (ii)
(B) (iii) only
(C) (iv) only
(D) (ii) and (iv) only
Consider the following functional dependency set
F = {A->B, BC->E, ED->A, EF->G, E->F}
What will be the closure of (AC)?
(A) (A,B,C,D,E,F,G)
(B) (A,B,D,E,F)
(C) (A,B,C,E)
(D) (A,B,C,E,F,G)
For the functional dependencies 𝐴→𝐷A→D, 𝐵→𝐶B→C, and 𝐶→𝐴C→A, what is the closure of {𝐵}?
A. {𝐵}
B. {𝐵,𝐶}
C. {𝐵,𝐶,𝐴}
D. {𝐵,𝐶,𝐴,𝐷}
Given 𝐴𝐵→𝐶AB→C, 𝐶→𝐷C→D, and 𝐷→𝐴D→A, what is the closure of {𝐴,𝐵}?
A. {𝐴,𝐵,𝐶}
B. {𝐴,𝐵,𝐶,𝐷}
C. {𝐴,𝐵}
D. {𝐴,𝐵,𝐷}
Consider a relation scheme R = (A, B, C, D, E, H) on which the following functional dependencies hold: {A–>B, BC–> D, E–>C, D–>A}. What are the candidate keys of R?
(a) AE, BE
(b) AE, BE, DE
(c) AEH, BEH, BCH
(d) AEH, BEH, DEH
Which of the following is a non-trivial functional dependency?
A) `{A} → {A}`
B) `{A, B} → {A}`
C) `{A} → {}`
D) `{A} → {B}`
Which property does every functional dependency (FD) satisfy?
A) Augmentation
B) Reflexivity
C) Transitivity
D) Symmetry
If `{A → B}` and `{B → C}` are given, which property of FD can be applied to derive a new dependency?
A) Reflexivity
B) Decomposition
C) Transitivity
D) Symmetry
If the relation `R` has 100 tuples and each combination of values `{A, B}` uniquely determines `C`, how many distinct values of `D` can exist in the relation?
A) 1
B) 10
C) 100
D) 1000
Consider a relation `Employee(EmpID, EmpName, DeptID, Salary)` with the following functional dependencies:
- `{EmpID} → {EmpName, DeptID, Salary}`
How many attributes are in the closure of attribute set `{EmpID}`?
A) 1
B) 2
C) 3
D) 4
E) None
Consider the relation X(P,Q,R,S,T,U) with the following set of functional dependencies F = { {P,R} -> {S,T}, {P,S,U}->{Q,R} ) which of the following is trivial functional dependency in F+, where F+ is closure of F?
(A) {P,R} -> {S,T}
(B) {P,R} -> {R,T}
(C) {P,S} -> {S}
(D) {P,S,U} -> {Q}
