Chapter 14, Electronic Commerce, Databases, and Personal Privacy Video Solutions, Invitation to Computer Science

Problem 1

Find an example of what you consider an excellent retail website. Comment on
a. The use of color and white space.
b. The ease of navigation.
c. The taxonomy.
d. Whether the site displays its privacy policy.
e. Whether the site displays a security assurance.
f. Your experience walking through the online purchase process (of course, cancel before you commit to the final purchasel). Are you in control and informed at each step?

Check back soon!

Problem 2

Find an example of what you would consider a poor retail website. Use the same list as for Exercise 1 and note the differences you find.

Check back soon!

Problem 3

Depending on your web browser, you may be able to locate a folder or a file on your machine that contains cookies. Look through the folder or open the file. List references to three websites you have visited.

Check back soon!

02:03

Problem 4

Using the Employees table of Figure 14.6, what is the result of the following $\mathrm{SQL}$ query?
SELBCT * FROM Employees
WHERE HoursWorked $<100$;

Adriano Chikande

Numerade Educator

02:18

Problem 5

Write an $S Q L$ query that retrieves first and last names and pay rate, ordered by PayRate, from the Employees table of Figure 14.6.

Adriano Chikande

Numerade Educator

Problem 6

Using the Employees table of Figure 14.6 and the InsurancePolicies table of Figure 14.7 , what is the result of the following SQL query? (The \# marks allow the date to be treated numerically.)
SELBCT ID, PlanType
FROM Employees, InsurancePolicies
WHERE Birthdate $>1 / 01 / 1980 \mathrm{H}$
AND ID = EmployeeID;

Check back soon!

04:12

Problem 7

Using the Employees table of Figure 14.6 and the InsurancePolicies table of Figure 14.7, write an SQL query that retrieves first and last names, hours worked, and insurance plan types for all employees who have worked fewer than 100 hours.

Kris Bright

Numerade Educator

00:38

Problem 8

Figure 14.8 describes the attributes in an Insurancepians table. Write some possible tuples for this table.

Jessica Wooten

Numerade Educator

04:12

Problem 9

Assuming the existence of an InsurancePlans table as described in Figure 14.8, write an SQL query that retrieves the employee first and last name, insurance plan type, and monthly cost for John Kay/s insurance.
The following information applies to Exercises 10-14.
You are working for the ABC Clinic, a small medical clinic that is open 7 days per week. The clinic would like to generate a reminder telephone call to clinic patients the day before their scheduled appointments. A vendor has a software application that will do this, given the appropriate data from the clinic database: You have been asked to investigate how to obtain the necessary data from the clinic's relational database. You are to begin with a small test database.
The following figure shows the entities in the clinic database, namely doctors, patients, and clinic apoointments.

Kris Bright

Numerade Educator

08:26

Problem 10

Write three $S Q L$ queries to display the full contents of each of the three tables.

Anas Venkitta

Numerade Educator

View

Problem 11

Now write a query to return the information used by the reminder program. The query must take a date and return the doctor's first and last name, the patient's first and last name and both phone numbers, and the appointment time, so it can produce a voice message of the form: "<Patient's name> has an appointment with doctor <Doctor's name> at <Appointment time> on <Appointment date>." Write the query to obtain data for all patients for February 28,2015 . (Hint: Your query will need two Join operations. Also, there are attributes in both the Doctor and Patient tables with the same names. You will need to qualify them with their table names, for example Doctor. FirstName.) Show the results of your query.

James Kiss

Numerade Educator

Problem 12

Someone has suggested that if the clinic is going to remind the patients, they should also remind the doctors. The query must take the doctor's ID and a date and return the doctor's first and last name and the appointment time so it can produce a voice message of the form:

Check back soon!

Problem 13

What would it take to allow the patient to call in, give his or her patient ID, select a date using the touch pad on the phone, and have the system tell him or her by voice the time of the appointment on the date specified along with the doctor's name? The query must take the date and PatientID and return the doctor's first and last name, the patient's first and last name, and the appointment time and date so it can produce a voice message of the form:
" $<$ Patient's name> has an appointment with $<$ Doctor's name> on <Appointment date> at <Appointment times."
Write the query for PatientID PA3 on 2/27/2015. Show the results of your query.

Check back soon!

00:33

Problem 14

The following decision tree was obtained by data mining from 10,000 medical records concerning factors that may influence the incidence of a certain disease. The leaves of the decision tree reflect a low/high propensity to develop the disease where a low incidence is $<50 \%$ of the individuals in that classification, and a high incidence is $\geq 50 \%$. The percentage is shown along with the lowihigh classification. Based on this decision tree, what would you conclude about the relative weight of age, obesity, or high blood pressure as a risk factor for developing this disease?

Erika Bustos

Numerade Educator