SNHU DAD 220 FINAL PAPER QUESTIONS AND SOLUTIONS
2025/2026 RATED A+
✔✔What are the Normal Forms - ✔✔Normal forms are numbered, first normal form
through fifth normal form. An additional normal form, named Boyce-Codd normal form,
is considered an improved version of third normal form.
✔✔First normal form - ✔✔all non-key columns must depend on the primary key
✔✔What does dependence mean in an ERD? - ✔✔each instance of a dependent entity
always relates to an instance of another entity
Called: Existence dependence
✔✔What does dependence mean in a relational model? - ✔✔each value of a column
relates to at most one value of another column
Called: Functional dependence
✔✔second normal form - ✔✔all non-key columns must depend on the whole primary
key.
In other words, a non-key column cannot depend on part of a composite primary key.
✔✔Third normal form - ✔✔All non-key columns depend on the key, the whole key, and
nothing but the key.
✔✔In what situations is redundancy more acceptable? - ✔✔In a database used
primarily for reporting, changes are infrequent and redundancy is acceptable. In fact,
redundancy can be desirable in reporting databases, as processing is faster and
queries are simpler. For this reason, reporting databases may contain tables that, by
design, are not in third normal form.
✔✔Denormalization - ✔✔intentionally introducing redundancy by merging tables.
Denormalization eliminates join queries and therefore improves query performance.
✔✔Aggregate Functions in SQL - ✔✔Used to summarize information from multiple
tuples into a single-tuple summary
✔✔Some common aggregat functions - ✔✔COUNT() - Counts the number of rows
retrieved by a SELECT statement.
MIN() - Finds the minimum value in a group.
MAX() - Finds the maximum value in a group.
SUM() - Sums all the values in a group.
AVG() - Finds the arithmetic mean (average) of all the values in a group.
, ✔✔GROUP BY clause does what? - ✔✔groups rows with identical values into a set of
summary rows
One or more columns are listed after GROUP BY, separated by commas.
GROUP BY clause returns one row for each group.
Each group may be ordered with the ORDER BY clause.
GROUP BY clause must appear before the ORDER BY clause and after the WHERE
clause (if present).
✔✔What does the numeric function ABS(n) do? - ✔✔Returns the absolute value of n
✔✔What does the numeric function LOG(n) do? - ✔✔REturns the natural logarithm of n
✔✔What does the numeric function POW(x,y) do? - ✔✔Returns x to the power of y
✔✔What does the numeric function RAND() do? - ✔✔Returns a random number
between 0 (inclusive) and 1 (exclusive)
✔✔What does the numeric function ROUND(n, d) do? - ✔✔Returns n rounded to d
decimal places
✔✔What does the numeric function SQRT(n) do? - ✔✔returns the square root of n
✔✔What does the string function CONCAT(s1, s2, ...) do? - ✔✔Returns the string that
results from concatenating the string arguments
ex. SELECT CONCAT('Dis','en','gage');
Returns 'Disengage'
✔✔What does the string function LOWER(s) do? - ✔✔Returns the lowercase s
✔✔What does the string function REPLACE(s, from, to) do? - ✔✔Returns the string s
with all occurtences of from replaced with to
ex. SELECT REPLACE("This and that", "and", "or");
returns 'This or that'
✔✔what does the string function SUBSTRING(s, pos, len) do? - ✔✔Returns the
substring from s that starts at position pos and has length len
ex. SELECT SUBSTRING("Boomerang", 1, 4);
returns 'Boom'
2025/2026 RATED A+
✔✔What are the Normal Forms - ✔✔Normal forms are numbered, first normal form
through fifth normal form. An additional normal form, named Boyce-Codd normal form,
is considered an improved version of third normal form.
✔✔First normal form - ✔✔all non-key columns must depend on the primary key
✔✔What does dependence mean in an ERD? - ✔✔each instance of a dependent entity
always relates to an instance of another entity
Called: Existence dependence
✔✔What does dependence mean in a relational model? - ✔✔each value of a column
relates to at most one value of another column
Called: Functional dependence
✔✔second normal form - ✔✔all non-key columns must depend on the whole primary
key.
In other words, a non-key column cannot depend on part of a composite primary key.
✔✔Third normal form - ✔✔All non-key columns depend on the key, the whole key, and
nothing but the key.
✔✔In what situations is redundancy more acceptable? - ✔✔In a database used
primarily for reporting, changes are infrequent and redundancy is acceptable. In fact,
redundancy can be desirable in reporting databases, as processing is faster and
queries are simpler. For this reason, reporting databases may contain tables that, by
design, are not in third normal form.
✔✔Denormalization - ✔✔intentionally introducing redundancy by merging tables.
Denormalization eliminates join queries and therefore improves query performance.
✔✔Aggregate Functions in SQL - ✔✔Used to summarize information from multiple
tuples into a single-tuple summary
✔✔Some common aggregat functions - ✔✔COUNT() - Counts the number of rows
retrieved by a SELECT statement.
MIN() - Finds the minimum value in a group.
MAX() - Finds the maximum value in a group.
SUM() - Sums all the values in a group.
AVG() - Finds the arithmetic mean (average) of all the values in a group.
, ✔✔GROUP BY clause does what? - ✔✔groups rows with identical values into a set of
summary rows
One or more columns are listed after GROUP BY, separated by commas.
GROUP BY clause returns one row for each group.
Each group may be ordered with the ORDER BY clause.
GROUP BY clause must appear before the ORDER BY clause and after the WHERE
clause (if present).
✔✔What does the numeric function ABS(n) do? - ✔✔Returns the absolute value of n
✔✔What does the numeric function LOG(n) do? - ✔✔REturns the natural logarithm of n
✔✔What does the numeric function POW(x,y) do? - ✔✔Returns x to the power of y
✔✔What does the numeric function RAND() do? - ✔✔Returns a random number
between 0 (inclusive) and 1 (exclusive)
✔✔What does the numeric function ROUND(n, d) do? - ✔✔Returns n rounded to d
decimal places
✔✔What does the numeric function SQRT(n) do? - ✔✔returns the square root of n
✔✔What does the string function CONCAT(s1, s2, ...) do? - ✔✔Returns the string that
results from concatenating the string arguments
ex. SELECT CONCAT('Dis','en','gage');
Returns 'Disengage'
✔✔What does the string function LOWER(s) do? - ✔✔Returns the lowercase s
✔✔What does the string function REPLACE(s, from, to) do? - ✔✔Returns the string s
with all occurtences of from replaced with to
ex. SELECT REPLACE("This and that", "and", "or");
returns 'This or that'
✔✔what does the string function SUBSTRING(s, pos, len) do? - ✔✔Returns the
substring from s that starts at position pos and has length len
ex. SELECT SUBSTRING("Boomerang", 1, 4);
returns 'Boom'
