ISDS 3110 Final Exam Guide Questions
& Answers 2024/2025
Pattern Match Search Condition - *LIKE / NOT LIKE* - ANSWERS*%*
- Represents any sequence of zero or more characters (wildcard)
*_*(underscore)
- Represents any single character
Subqueries - ANSWERSA query within a query
*Multi-Table Join with Selection Predicates* example.
List the fname and lname of clients who are registered in branches in 'London'. - ANSWERS*SELECT
DISTINCT* c.fName, c.lName *FROM* Client c, Registration r, Branch b *WHERE* c.clientNo = r.clientNo
*AND* r.branchNo = b.branchNo *AND* b.city = 'London'
Motivation for Normalization - ANSWERSProblem 1. Data Redundancy
Problem 2. Update Anomalies
Update Anomaly - ANSWERSRelations that contain redundant information may potentially suffer from
update anomalies
Types of Update Anomalies - ANSWERS- *Insertion* anomaly
- *Deletion* Anomaly
, - *Modification* Anomaly
Solution to Avoid Update Anomalies - ANSWERSDecomposition
Decomposition must satisfy 2 properties: - ANSWERS1. *lossless-join* property
- enables us to find any instance of the original relation from corresponding instances in the smaller
relations
2. *Dependency Preservation* Property
- Enables us to enforce a constraint on the original relation by enforcing some constraint on each of the
smaller relations
Purpose of Normalization - ANSWERS*Normalization* is a *database design technique* for *producing*
a *set of suitable relations* that support the data requirements of an enterprise
Characteristics of a suitable set of relations - ANSWERSa. *The minimal number of attributes* necessary
to support the data requirements of the enterprise
b. attributes with a close logical relationship are found in the same relation
c. *Minimal data redundancy* with each attribute represented only once with the important exception
of attributes that form all or part of foreign keys
Benefits of Normalization - ANSWERSa. Easier for the end user to access and maintain the data
b. Take up minimal storage space on the computer
Functional Dependencies - ANSWERSIf *A* and *B* are attributes of relation R
*B* is *functionally dependent* on *A* (denoted A -> B) If each value of *A* in R is *associated* with
*exactly one *value of *B* in R
& Answers 2024/2025
Pattern Match Search Condition - *LIKE / NOT LIKE* - ANSWERS*%*
- Represents any sequence of zero or more characters (wildcard)
*_*(underscore)
- Represents any single character
Subqueries - ANSWERSA query within a query
*Multi-Table Join with Selection Predicates* example.
List the fname and lname of clients who are registered in branches in 'London'. - ANSWERS*SELECT
DISTINCT* c.fName, c.lName *FROM* Client c, Registration r, Branch b *WHERE* c.clientNo = r.clientNo
*AND* r.branchNo = b.branchNo *AND* b.city = 'London'
Motivation for Normalization - ANSWERSProblem 1. Data Redundancy
Problem 2. Update Anomalies
Update Anomaly - ANSWERSRelations that contain redundant information may potentially suffer from
update anomalies
Types of Update Anomalies - ANSWERS- *Insertion* anomaly
- *Deletion* Anomaly
, - *Modification* Anomaly
Solution to Avoid Update Anomalies - ANSWERSDecomposition
Decomposition must satisfy 2 properties: - ANSWERS1. *lossless-join* property
- enables us to find any instance of the original relation from corresponding instances in the smaller
relations
2. *Dependency Preservation* Property
- Enables us to enforce a constraint on the original relation by enforcing some constraint on each of the
smaller relations
Purpose of Normalization - ANSWERS*Normalization* is a *database design technique* for *producing*
a *set of suitable relations* that support the data requirements of an enterprise
Characteristics of a suitable set of relations - ANSWERSa. *The minimal number of attributes* necessary
to support the data requirements of the enterprise
b. attributes with a close logical relationship are found in the same relation
c. *Minimal data redundancy* with each attribute represented only once with the important exception
of attributes that form all or part of foreign keys
Benefits of Normalization - ANSWERSa. Easier for the end user to access and maintain the data
b. Take up minimal storage space on the computer
Functional Dependencies - ANSWERSIf *A* and *B* are attributes of relation R
*B* is *functionally dependent* on *A* (denoted A -> B) If each value of *A* in R is *associated* with
*exactly one *value of *B* in R
