COSC 3380 FINAL EXAM QUESTIONS
WITH VERIFIED ANSWERS
\.Informal Guidelines (Measure of DB Quality) - ANSWERS✔--Making sure that the
semantics of the attributes is clear in the schema
-Reducing the redundant information in tuples
-Reducing the NULL values in tuples
-Disallowing the possibility of generating spurious tuples
\.Normalization - ANSWERS✔--analyzing schemas based on their FDs and primary
keys
-achieves 'desirable' properties:
(1) minimizing redundancy
(2) minimizing the insertion, deletion, and update anomalies
\.Denormalization - ANSWERS✔--storing the join of higher normal form relations
as a base relation
-is in a lower normal form
\.dependency preservation property - ANSWERS✔-After decomposition, each FD
is still represented (desirable)
, \.lossless join property (nonadditive join) - ANSWERS✔-After decomposition,
there is no problem of spurious tuple generation (must have)
\.First Normal Form (1NF) - ANSWERS✔--nonprime attributes depend on a key (no
duplicate keys/rows)
-cells contain atomic values (indivisible datapoint)
\.Second Normal Form (2NF) - ANSWERS✔--nonprime attributes are functionally
dependent on the whole key (no partial dependency)
\.Third Normal Form (3NF) - ANSWERS✔--no nonprime attribute is transitively
dependent on the primary key (nonprime determines nonprime)
-Transitive dependence is the case where primary key X determines Z, and
attributes Z determine Y, so X determines Y, but attributes Z are not candidate keys
or subsets of any primary key. (basically fat tables)
\.SQL Aggregate Functions - ANSWERS✔--COUNT, MIN, MAX, SUM, AVG
\.GROUP BY clause - ANSWERS✔--groups aggregates by a certain attribute
-creates one extra group for NULL values
\.HAVING Clause - ANSWERS✔--filters applied on resulting groups after GROUP BY
clause
WITH VERIFIED ANSWERS
\.Informal Guidelines (Measure of DB Quality) - ANSWERS✔--Making sure that the
semantics of the attributes is clear in the schema
-Reducing the redundant information in tuples
-Reducing the NULL values in tuples
-Disallowing the possibility of generating spurious tuples
\.Normalization - ANSWERS✔--analyzing schemas based on their FDs and primary
keys
-achieves 'desirable' properties:
(1) minimizing redundancy
(2) minimizing the insertion, deletion, and update anomalies
\.Denormalization - ANSWERS✔--storing the join of higher normal form relations
as a base relation
-is in a lower normal form
\.dependency preservation property - ANSWERS✔-After decomposition, each FD
is still represented (desirable)
, \.lossless join property (nonadditive join) - ANSWERS✔-After decomposition,
there is no problem of spurious tuple generation (must have)
\.First Normal Form (1NF) - ANSWERS✔--nonprime attributes depend on a key (no
duplicate keys/rows)
-cells contain atomic values (indivisible datapoint)
\.Second Normal Form (2NF) - ANSWERS✔--nonprime attributes are functionally
dependent on the whole key (no partial dependency)
\.Third Normal Form (3NF) - ANSWERS✔--no nonprime attribute is transitively
dependent on the primary key (nonprime determines nonprime)
-Transitive dependence is the case where primary key X determines Z, and
attributes Z determine Y, so X determines Y, but attributes Z are not candidate keys
or subsets of any primary key. (basically fat tables)
\.SQL Aggregate Functions - ANSWERS✔--COUNT, MIN, MAX, SUM, AVG
\.GROUP BY clause - ANSWERS✔--groups aggregates by a certain attribute
-creates one extra group for NULL values
\.HAVING Clause - ANSWERS✔--filters applied on resulting groups after GROUP BY
clause
