Question 1

HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
 
$$\begin{array} { l l c l } 
\text { ClientID } & \text { ClientName } & \text { CientAgent } & \text { ClientSpouseName } \
\text { C111 } & \text { Tom } & \text { A1 } & \text { Jenny } \
\text { C222 } & \text { Kain } & A 1 & \text { Bill } \
\text { C333 } & \text { Cole } & A 2 & \text { Amy } \
\text { C444 } & \text { Dorothy } & A 2 & \
\text { C555 } & \text { Anay } & A 3 & \text { Amy } \
\text { C666 } & \text { Tina } & A 3 & \text { Matt } \
\text { C777 } & \text { Christina } & A 4 & \text { Mike }
\end{array}$$ AGENT
 $$\begin{array} { l l l l l l } 
\text { AgentID } & \text { AgentName } & \text { AgentArea } & \text { AgentRating } & \text { AgentYearOfHire } & \text { SupervisedBy } \
\text { A1 } & \text { Kate } & 1 & 101 & 1990 & \
\text { A2 } & \text { Amy } & 2 & 92 & 2009 & \text { A1 } \
\text { A3 } & \text { Luke } & 3 & 100 & 1992 & \
\text { A4 } & \text { James } & 3 & 90 & 2010 & \text { A3 }
\end{array}$$ AREA
 $$\begin{array} { l l l } 
\text { ArealD } & \text { AreaName } & \text { AreaHQ } \
1 & \text { East } & \text { Boston } \
2 & \text { West } & \text { San Francisco } \
3 & \text { Central } & \text { Chicago }
\end{array}$$
This database will be used for the following questions citing tables from the HAPPY INSURANCE database.
-Show the SQL statement that will for HAPPY INSURANCE database create and index on the ClientName column of the table CLIENT.

Accepted Answer

The answer of HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
 
\[\begin{array}...

Question 2

HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
 
$$\begin{array} { l l c l } 
\text { ClientID } & \text { ClientName } & \text { CientAgent } & \text { ClientSpouseName } \
\text { C111 } & \text { Tom } & \text { A1 } & \text { Jenny } \
\text { C222 } & \text { Kain } & A 1 & \text { Bill } \
\text { C333 } & \text { Cole } & A 2 & \text { Amy } \
\text { C444 } & \text { Dorothy } & A 2 & \
\text { C555 } & \text { Anay } & A 3 & \text { Amy } \
\text { C666 } & \text { Tina } & A 3 & \text { Matt } \
\text { C777 } & \text { Christina } & A 4 & \text { Mike }
\end{array}$$ AGENT
 $$\begin{array} { l l l l l l } 
\text { AgentID } & \text { AgentName } & \text { AgentArea } & \text { AgentRating } & \text { AgentYearOfHire } & \text { SupervisedBy } \
\text { A1 } & \text { Kate } & 1 & 101 & 1990 & \
\text { A2 } & \text { Amy } & 2 & 92 & 2009 & \text { A1 } \
\text { A3 } & \text { Luke } & 3 & 100 & 1992 & \
\text { A4 } & \text { James } & 3 & 90 & 2010 & \text { A3 }
\end{array}$$ AREA
 $$\begin{array} { l l l } 
\text { ArealD } & \text { AreaName } & \text { AreaHQ } \
1 & \text { East } & \text { Boston } \
2 & \text { West } & \text { San Francisco } \
3 & \text { Central } & \text { Chicago }
\end{array}$$
This database will be used for the following questions citing tables from the HAPPY INSURANCE database.
-Assume a form is created for HAPPY INSURANCE database where a user can perform the following action:
A user highlights value A1 in the first row of the CLIENT table and changes it to A4.
Write the SQL statement that will be issued on the user's behalf based on this action.

Accepted Answer

The answer of HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
 
\[\begin{array}...

Question 3

What is a report?

Accepted Answer

The answer of What is a report?...

Question 4

HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
 
$$\begin{array} { l l c l } 
\text { ClientID } & \text { ClientName } & \text { CientAgent } & \text { ClientSpouseName } \
\text { C111 } & \text { Tom } & \text { A1 } & \text { Jenny } \
\text { C222 } & \text { Kain } & A 1 & \text { Bill } \
\text { C333 } & \text { Cole } & A 2 & \text { Amy } \
\text { C444 } & \text { Dorothy } & A 2 & \
\text { C555 } & \text { Anay } & A 3 & \text { Amy } \
\text { C666 } & \text { Tina } & A 3 & \text { Matt } \
\text { C777 } & \text { Christina } & A 4 & \text { Mike }
\end{array}$$ AGENT
 $$\begin{array} { l l l l l l } 
\text { AgentID } & \text { AgentName } & \text { AgentArea } & \text { AgentRating } & \text { AgentYearOfHire } & \text { SupervisedBy } \
\text { A1 } & \text { Kate } & 1 & 101 & 1990 & \
\text { A2 } & \text { Amy } & 2 & 92 & 2009 & \text { A1 } \
\text { A3 } & \text { Luke } & 3 & 100 & 1992 & \
\text { A4 } & \text { James } & 3 & 90 & 2010 & \text { A3 }
\end{array}$$ AREA
 $$\begin{array} { l l l } 
\text { ArealD } & \text { AreaName } & \text { AreaHQ } \
1 & \text { East } & \text { Boston } \
2 & \text { West } & \text { San Francisco } \
3 & \text { Central } & \text { Chicago }
\end{array}$$
This database will be used for the following questions citing tables from the HAPPY INSURANCE database.
-Assume a search form for HAPPY INSURANCE database is created where a user can perform the following action:
ENTER AREA ID:
ENTER AREA NAME:
ENTER AREA HQ:
ENTER AgentID:
ENTER AgentName:
ENTER AgentArea: 3 (<--- Entered by the user)
ENTER AgentRating:
ENTER AgentYearOfHire:
ENTER SupervisedBY:
(User clicks on the search button upon entering the value above. )
Write the SQL statement that will be issued on the user's behalf based on this action.

Accepted Answer

The answer of HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
 
\[\begin{array}...

Question 5

What are corrective data quality actions?

Accepted Answer

The answer of What are corrective data quality actions?...

Question 6

HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
 
$$\begin{array} { l l c l } 
\text { ClientID } & \text { ClientName } & \text { CientAgent } & \text { ClientSpouseName } \
\text { C111 } & \text { Tom } & \text { A1 } & \text { Jenny } \
\text { C222 } & \text { Kain } & A 1 & \text { Bill } \
\text { C333 } & \text { Cole } & A 2 & \text { Amy } \
\text { C444 } & \text { Dorothy } & A 2 & \
\text { C555 } & \text { Anay } & A 3 & \text { Amy } \
\text { C666 } & \text { Tina } & A 3 & \text { Matt } \
\text { C777 } & \text { Christina } & A 4 & \text { Mike }
\end{array}$$ AGENT
 $$\begin{array} { l l l l l l } 
\text { AgentID } & \text { AgentName } & \text { AgentArea } & \text { AgentRating } & \text { AgentYearOfHire } & \text { SupervisedBy } \
\text { A1 } & \text { Kate } & 1 & 101 & 1990 & \
\text { A2 } & \text { Amy } & 2 & 92 & 2009 & \text { A1 } \
\text { A3 } & \text { Luke } & 3 & 100 & 1992 & \
\text { A4 } & \text { James } & 3 & 90 & 2010 & \text { A3 }
\end{array}$$ AREA
 $$\begin{array} { l l l } 
\text { ArealD } & \text { AreaName } & \text { AreaHQ } \
1 & \text { East } & \text { Boston } \
2 & \text { West } & \text { San Francisco } \
3 & \text { Central } & \text { Chicago }
\end{array}$$
This database will be used for the following questions citing tables from the HAPPY INSURANCE database.
-Assume a data-entry form for HAPPY INSURANCE database is created where a user can perform the following data-entry action:
ENTER AREA ID: 4 (<--- Entered by the user)
ENTER AREA NAME: South (<--- Entered by the user)
ENTER AREA HQ: Atlanta (<--- Entered by the user)
Write the SQL statement that will be issued on the user's behalf based on this action.

Accepted Answer

The answer of HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
 
\[\begin{array}...

Question 7

What is binary search?

Accepted Answer

The answer of What is binary search?...

Question 8

What is a form?

Accepted Answer

The answer of What is a form?...

Question 9

HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
 
$$\begin{array} { l l c l } 
\text { ClientID } & \text { ClientName } & \text { CientAgent } & \text { ClientSpouseName } \
\text { C111 } & \text { Tom } & \text { A1 } & \text { Jenny } \
\text { C222 } & \text { Kain } & A 1 & \text { Bill } \
\text { C333 } & \text { Cole } & A 2 & \text { Amy } \
\text { C444 } & \text { Dorothy } & A 2 & \
\text { C555 } & \text { Anay } & A 3 & \text { Amy } \
\text { C666 } & \text { Tina } & A 3 & \text { Matt } \
\text { C777 } & \text { Christina } & A 4 & \text { Mike }
\end{array}$$ AGENT
 $$\begin{array} { l l l l l l } 
\text { AgentID } & \text { AgentName } & \text { AgentArea } & \text { AgentRating } & \text { AgentYearOfHire } & \text { SupervisedBy } \
\text { A1 } & \text { Kate } & 1 & 101 & 1990 & \
\text { A2 } & \text { Amy } & 2 & 92 & 2009 & \text { A1 } \
\text { A3 } & \text { Luke } & 3 & 100 & 1992 & \
\text { A4 } & \text { James } & 3 & 90 & 2010 & \text { A3 }
\end{array}$$ AREA
 $$\begin{array} { l l l } 
\text { ArealD } & \text { AreaName } & \text { AreaHQ } \
1 & \text { East } & \text { Boston } \
2 & \text { West } & \text { San Francisco } \
3 & \text { Central } & \text { Chicago }
\end{array}$$
This database will be used for the following questions citing tables from the HAPPY INSURANCE database.
-Show the CREATE TABLE statements for HAPPY INSURANCE database for the table CLIENT (assume that non-primary key columns can be optional)with the DELETE SET-TO-NULL and UPDATE CASCADE option for its referential integrity constraint.

Accepted Answer

The answer of HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
 
\[\begin{array}...

Question 10

What are preventive data quality actions?

Accepted Answer

The answer of What are preventive data quality actions?...

Question 11

HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
 
$$\begin{array} { l l c l } 
\text { ClientID } & \text { ClientName } & \text { CientAgent } & \text { ClientSpouseName } \
\text { C111 } & \text { Tom } & \text { A1 } & \text { Jenny } \
\text { C222 } & \text { Kain } & A 1 & \text { Bill } \
\text { C333 } & \text { Cole } & A 2 & \text { Amy } \
\text { C444 } & \text { Dorothy } & A 2 & \
\text { C555 } & \text { Anay } & A 3 & \text { Amy } \
\text { C666 } & \text { Tina } & A 3 & \text { Matt } \
\text { C777 } & \text { Christina } & A 4 & \text { Mike }
\end{array}$$ AGENT
 $$\begin{array} { l l l l l l } 
\text { AgentID } & \text { AgentName } & \text { AgentArea } & \text { AgentRating } & \text { AgentYearOfHire } & \text { SupervisedBy } \
\text { A1 } & \text { Kate } & 1 & 101 & 1990 & \
\text { A2 } & \text { Amy } & 2 & 92 & 2009 & \text { A1 } \
\text { A3 } & \text { Luke } & 3 & 100 & 1992 & \
\text { A4 } & \text { James } & 3 & 90 & 2010 & \text { A3 }
\end{array}$$ AREA
 $$\begin{array} { l l l } 
\text { ArealD } & \text { AreaName } & \text { AreaHQ } \
1 & \text { East } & \text { Boston } \
2 & \text { West } & \text { San Francisco } \
3 & \text { Central } & \text { Chicago }
\end{array}$$
This database will be used for the following questions citing tables from the HAPPY INSURANCE database.
-Show the CREATE TABLE statements for HAPPY INSURANCE database for the table CLIENT (assume that non-primary key columns can be optional)with the DELETE CASCADE option for its referential integrity constraint.

Accepted Answer

The answer of HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
 
\[\begin{array}...

Question 12

HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
 
$$\begin{array} { l l c l } 
\text { ClientID } & \text { ClientName } & \text { CientAgent } & \text { ClientSpouseName } \
\text { C111 } & \text { Tom } & \text { A1 } & \text { Jenny } \
\text { C222 } & \text { Kain } & A 1 & \text { Bill } \
\text { C333 } & \text { Cole } & A 2 & \text { Amy } \
\text { C444 } & \text { Dorothy } & A 2 & \
\text { C555 } & \text { Anay } & A 3 & \text { Amy } \
\text { C666 } & \text { Tina } & A 3 & \text { Matt } \
\text { C777 } & \text { Christina } & A 4 & \text { Mike }
\end{array}$$ AGENT
 $$\begin{array} { l l l l l l } 
\text { AgentID } & \text { AgentName } & \text { AgentArea } & \text { AgentRating } & \text { AgentYearOfHire } & \text { SupervisedBy } \
\text { A1 } & \text { Kate } & 1 & 101 & 1990 & \
\text { A2 } & \text { Amy } & 2 & 92 & 2009 & \text { A1 } \
\text { A3 } & \text { Luke } & 3 & 100 & 1992 & \
\text { A4 } & \text { James } & 3 & 90 & 2010 & \text { A3 }
\end{array}$$ AREA
 $$\begin{array} { l l l } 
\text { ArealD } & \text { AreaName } & \text { AreaHQ } \
1 & \text { East } & \text { Boston } \
2 & \text { West } & \text { San Francisco } \
3 & \text { Central } & \text { Chicago }
\end{array}$$
This database will be used for the following questions citing tables from the HAPPY INSURANCE database.
-Assume a search form for HAPPY INSURANCE database is created where a user can perform the following action:
ENTER AREA ID:
ENTER AREA NAME:
ENTER AREA HQ:
ENTER AgentID:
ENTER AgentName:
ENTER AgentArea:
ENTER AgentRating:
ENTER AgentYearOfHire: 2010 (<--- Entered by the user)
ENTER SupervisedBY: A3 (<--- Entered by the user)
(User clicks on the search button upon entering the values above. )
Write the SQL statement that will be issued on the user's behalf based on this action.

Accepted Answer

The answer of HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
 
\[\begin{array}...

Question 13

What is a trigger?

Accepted Answer

The answer of What is a trigger?...

Question 14

HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
 
$$\begin{array} { l l c l } 
\text { ClientID } & \text { ClientName } & \text { CientAgent } & \text { ClientSpouseName } \
\text { C111 } & \text { Tom } & \text { A1 } & \text { Jenny } \
\text { C222 } & \text { Kain } & A 1 & \text { Bill } \
\text { C333 } & \text { Cole } & A 2 & \text { Amy } \
\text { C444 } & \text { Dorothy } & A 2 & \
\text { C555 } & \text { Anay } & A 3 & \text { Amy } \
\text { C666 } & \text { Tina } & A 3 & \text { Matt } \
\text { C777 } & \text { Christina } & A 4 & \text { Mike }
\end{array}$$ AGENT
 $$\begin{array} { l l l l l l } 
\text { AgentID } & \text { AgentName } & \text { AgentArea } & \text { AgentRating } & \text { AgentYearOfHire } & \text { SupervisedBy } \
\text { A1 } & \text { Kate } & 1 & 101 & 1990 & \
\text { A2 } & \text { Amy } & 2 & 92 & 2009 & \text { A1 } \
\text { A3 } & \text { Luke } & 3 & 100 & 1992 & \
\text { A4 } & \text { James } & 3 & 90 & 2010 & \text { A3 }
\end{array}$$ AREA
 $$\begin{array} { l l l } 
\text { ArealD } & \text { AreaName } & \text { AreaHQ } \
1 & \text { East } & \text { Boston } \
2 & \text { West } & \text { San Francisco } \
3 & \text { Central } & \text { Chicago }
\end{array}$$
This database will be used for the following questions citing tables from the HAPPY INSURANCE database.
-Show the CREATE TABLE statements for HAPPY INSURANCE database for the table AGENT (assume that non-primary key columns can be optional)with the user defined constraint specifying that the agent rating must be between 50 and 150.

Accepted Answer

The answer of HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
 
\[\begin{array}...

Question 15

HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
 
$$\begin{array} { l l c l } 
\text { ClientID } & \text { ClientName } & \text { CientAgent } & \text { ClientSpouseName } \
\text { C111 } & \text { Tom } & \text { A1 } & \text { Jenny } \
\text { C222 } & \text { Kain } & A 1 & \text { Bill } \
\text { C333 } & \text { Cole } & A 2 & \text { Amy } \
\text { C444 } & \text { Dorothy } & A 2 & \
\text { C555 } & \text { Anay } & A 3 & \text { Amy } \
\text { C666 } & \text { Tina } & A 3 & \text { Matt } \
\text { C777 } & \text { Christina } & A 4 & \text { Mike }
\end{array}$$ AGENT
 $$\begin{array} { l l l l l l } 
\text { AgentID } & \text { AgentName } & \text { AgentArea } & \text { AgentRating } & \text { AgentYearOfHire } & \text { SupervisedBy } \
\text { A1 } & \text { Kate } & 1 & 101 & 1990 & \
\text { A2 } & \text { Amy } & 2 & 92 & 2009 & \text { A1 } \
\text { A3 } & \text { Luke } & 3 & 100 & 1992 & \
\text { A4 } & \text { James } & 3 & 90 & 2010 & \text { A3 }
\end{array}$$ AREA
 $$\begin{array} { l l l } 
\text { ArealD } & \text { AreaName } & \text { AreaHQ } \
1 & \text { East } & \text { Boston } \
2 & \text { West } & \text { San Francisco } \
3 & \text { Central } & \text { Chicago }
\end{array}$$
This database will be used for the following questions citing tables from the HAPPY INSURANCE database.
-Assume a form is created for HAPPY INSURANCE database where a user can perform the following action:
A user highlights the first row of the CLIENT table and presses the delete key on the keyboard.
Write the SQL statement that will be issued on the user's behalf based on this action.

Accepted Answer

The answer of HAPPY INSURANCE
Observe the HAPPY INSURANCE DATABASE:
CLIENT
 
\[\begin{array}...

Quiz 6: Database Implementation and Use

What is a report?

What are corrective data quality actions?

What is binary search?

What is a form?

What are preventive data quality actions?

What is a trigger?