COSC 3380 Final Exam With Complete Answers
Informal Guidelines (Measure of DB Quality) - ANSWER -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 - ANSWER -analyzing schemas based on their FDs and primary
keys
-achieves 'desirable' properties:
(1) minimizing redundancy
(2) minimizing the insertion, deletion, and update anomalies
Denormalization - ANSWER -storing the join of higher normal form relations as a
base relation
-is in a lower normal form
dependency preservation property - ANSWER After decomposition, each FD is
still represented (desirable)
lossless join property (nonadditive join) - ANSWER After decomposition, there is
no problem of spurious tuple generation (must have)
First Normal Form (1NF) - ANSWER -nonprime attributes depend on a key (no
duplicate keys/rows)
-cells contain atomic values (indivisible datapoint)
Second Normal Form (2NF) - ANSWER -nonprime attributes are functionally
dependent on the whole key (no partial dependency)
Third Normal Form (3NF) - ANSWER -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 - ANSWER -COUNT, MIN, MAX, SUM, AVG
GROUP BY clause - ANSWER -groups aggregates by a certain attribute
-creates one extra group for NULL values
, HAVING Clause - ANSWER -filters applied on resulting groups after GROUP BY
clause
What happens to NULL values in aggregate functions - ANSWER they are
ignored in calculation
SOME - ANSWER -SOME returns true if at least one element in the collection is
true
ALL - ANSWER -ALL returns true if all the elements in the collection are true
VIEW - ANSWER -single table derived from other tables called defining tables
-a virtual table that is not necessarily population
-not stored in the hard-disk
-can be used in queries
-always up-to-date
Natural Join - ANSWER -no join condition
-implicit EQUIJOIN condition for each pair of attributes with the same name from
R and S
Inner Join - ANSWER -default join type
-Tuple is included only if match exists
Left Outer Join (Right Outer Join is opposite) - ANSWER -every tuple in the left
table appears in the result
-if no match then it is padded with NULL values for attributes of the right table
Full Outer Join - ANSWER -left and right outer join combined
_Null values in every column of the table without a matching row
Assertion - ANSWER -specifies additional types of constraints outside the scope
of built-in constraints
-specify a query that selects any tuples that violates a desired condition
Trigger - ANSWER -specifies automatic actions that dbms will perform on
certain events and conditions
-consists of an Event, Condition, and Action
4 Threats To Database Security - ANSWER -loss of integrity (improper
modification)
-loss of availability (cannot access db)
-loss of confidentiality (unauthorized disclosure)
4 Types of Prevention - ANSWER -access control (users)
-inference control (individual info inaccessible)
Informal Guidelines (Measure of DB Quality) - ANSWER -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 - ANSWER -analyzing schemas based on their FDs and primary
keys
-achieves 'desirable' properties:
(1) minimizing redundancy
(2) minimizing the insertion, deletion, and update anomalies
Denormalization - ANSWER -storing the join of higher normal form relations as a
base relation
-is in a lower normal form
dependency preservation property - ANSWER After decomposition, each FD is
still represented (desirable)
lossless join property (nonadditive join) - ANSWER After decomposition, there is
no problem of spurious tuple generation (must have)
First Normal Form (1NF) - ANSWER -nonprime attributes depend on a key (no
duplicate keys/rows)
-cells contain atomic values (indivisible datapoint)
Second Normal Form (2NF) - ANSWER -nonprime attributes are functionally
dependent on the whole key (no partial dependency)
Third Normal Form (3NF) - ANSWER -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 - ANSWER -COUNT, MIN, MAX, SUM, AVG
GROUP BY clause - ANSWER -groups aggregates by a certain attribute
-creates one extra group for NULL values
, HAVING Clause - ANSWER -filters applied on resulting groups after GROUP BY
clause
What happens to NULL values in aggregate functions - ANSWER they are
ignored in calculation
SOME - ANSWER -SOME returns true if at least one element in the collection is
true
ALL - ANSWER -ALL returns true if all the elements in the collection are true
VIEW - ANSWER -single table derived from other tables called defining tables
-a virtual table that is not necessarily population
-not stored in the hard-disk
-can be used in queries
-always up-to-date
Natural Join - ANSWER -no join condition
-implicit EQUIJOIN condition for each pair of attributes with the same name from
R and S
Inner Join - ANSWER -default join type
-Tuple is included only if match exists
Left Outer Join (Right Outer Join is opposite) - ANSWER -every tuple in the left
table appears in the result
-if no match then it is padded with NULL values for attributes of the right table
Full Outer Join - ANSWER -left and right outer join combined
_Null values in every column of the table without a matching row
Assertion - ANSWER -specifies additional types of constraints outside the scope
of built-in constraints
-specify a query that selects any tuples that violates a desired condition
Trigger - ANSWER -specifies automatic actions that dbms will perform on
certain events and conditions
-consists of an Event, Condition, and Action
4 Threats To Database Security - ANSWER -loss of integrity (improper
modification)
-loss of availability (cannot access db)
-loss of confidentiality (unauthorized disclosure)
4 Types of Prevention - ANSWER -access control (users)
-inference control (individual info inaccessible)
