Submit Answers to Each of These Problems in a Word Document by the Due Date Above. You

LSP 121-405

Homework 1

Due: Thursday, September 24, by 11:59 pm

50 points

Submit answers to each of these problems in a Word document by the due date above. You MUST type your name at the top of your Word answer document. Answer files with no names will be given a 2 point deduction.

1. (20 points) Normalization Design Problems (do NOT perform in Access)

Normalize each of the following datasets by splitting them into multiple tables whererepeating fieldsare placed into a separate table with a foreign key. For each dataset, show how many tables should be used, give an appropriate name for each table and list the fields (and their data types) that would be in each of the resulting tables. Circle the primary key, and put the initials FK (for foreign key) next to each foreign key.

1. Personnel Database

Employee ID

First Name

Last Name

Address

City

State

Zip

Annual Income

Insurance Provider Name

<the following fields repeat 1-n times

Phone Number

Type of Phone (home, cell, work, etc.)

1. Daycare Database

Parent ID

Parent Last Name

Parent First Name

Parent Address

ParentCity

ParentState

Parent Zip

<the following fields repeat 1-n times

Phone Number

Type of phone

<the following fields repeat 1-n times

Child Name

Child Age

1. Cable Television Customer Database

Customer ID

Name

Address

City

State

Zip

Home Phone

Start (start date of service)

End (ending date of service)

<the following fields repeat 1 to n times>

Premium Channel (HBO, Showtime, etc.)

Premium Charge

1. (30 points) Implementation of a Relational Database in Access

You will now implement the Cable Television Customer Database you designed in part 1(c) above using Access.

Hint: if your Premium Channel Info table looks something like the following:

ID (foreign key)

Premium Channel 1

Premium Charge 1

Premium Channel 2

Premium Charge 2

then you are not doing it correctly. This second table should not have multiple fields for Premium Channels and Premium Charges – only one each. If a customer has signed up for multiple Premium Channels, then we will create multiple records, one for each Premium Channel and associated Premium Charge.

Don’t forget that the ID in the second table is not a primary key. It is instead a foreign key (Access does not identify foreign keys – only we do). But there will be a relationship between ID in the first table and ID in the second table.

Now you can create your tables in Access and enter the data. The data for this first table exists as an Excel spreadsheet (Cable Customers.xls, on the QRC website under Excel Files) so you can import it into your Access table if you don’t feel like typing it in by hand. After the import, you should verify that the data has been entered correctly.

Data records to be entered into the Cable Customer Info table:

ID / Name / Address / City / State / Zip / Phone / Start / End
1 / Smith / 3382 Armitage / Chicago / IL / 60043 / 773 455-0923 / 4/5/2002
2 / Jones / 5665 Wilbur / Des Plaines / IL / 60061 / 847 443-0221 / 9/1/1998
3 / Chen / 342 Oak / Park Ridge / IL / 60032 / 847 333-6753 / 4/5/1990
4 / Shah / 5544 Susan / Oak Park / IL / 60631 / 708 339-0222 / 1/1/2000 / 1/1/2008
5 / Purcel / 4322 Orchard Ct / Des Plaines / IL / 60061 / 847 332-0932 / 5/1/2002
6 / Roberts / 601 Stewart / Lombard / IL / 60133 / 773 566-9123 / 10/1/2001
7 / Stein / 565 Packard / Oak Park / IL / 60632 / 708 445-0922 / 4/1/2005
8 / Leninger / 100 Ford / Chicago / IL / 60033 / 312 566-0933 / 12/10/1997
9 / Walsh / 12233 Park / Oak Park / IL / 60632 / 708 366-7618 / 5/9/2000

If you used the import function to enter the data into the table, then make sure the fields have the correct data types. All fields except Start Date and End Date should be text. If you missed this option during the import, go back into the table design and change them to the appropriate types.

The following data should go into a second Access table. Each customer has the premium channels as listed. You will have to first create this table (the Premium Channel Info table) by hand (in Design mode), and then enter this data by hand. Note: Do Not enter the customer’s last name into this second table. Use the customer last name to figure out what his/her ID is, and then enter the ID into this second table.

NamePremium Channels Subscribed To

SmithHBO, Showtime

JonesHBO, StarZ, MovieZ

Chennone

ShahHBO, HBO2, Showtime, Dogz

PurcelShowtime

RobertsStarZ, MovieZ

SteinHBO

LeningerStarZ, History, MovieZ

WalshShowtime

Use the following information to enter the Premium Charge information

HBO costs $5.95 per month

Showtime cost $5.95 per month

MovieZ cost $4.95 per month

HBO2 cost $4.95 per month

StarZcost $3.95 per month

Dogz,cost $3.95 per month

History cost $3.95 per month

Hint: Jones should have three records in the Premium Channel table – one each for HBO, StarZ, MovieZ.

5-point Bonus: You will get a 5-point extra credit bonus if you correctly place the Premium channel names (HBO, Showtime, etc.) and charges into a separate Premium Channel Lookup table so that each Premium channel name only needs to be typed once even though it is used multiple times in the Premium Channel Table. This is not required to earn full credit for this assignment.

You will now need to create appropriate relationships between the tables you have created in Access so that the correct records will be associated between tables. Note that Access will create relationships automatically for you if you give the primary key and foreign key exactly the same name and type in theassociated tables.

Paste a copy of the contents of Design View and Datasheet View for all tables into your Homework answers Word document.

Finally, perform the following queries. As each query is performed, copy and paste the query results into your Word document. DO NOT hand type the results into your Word document.

1. Display Name, Address, City and Phone for all customers living in Des Plaines, sorted alphabetically by Name. (If you don’t recall how to set up this query, then either review the overhead lecture notes or read the handout Introduction to Database Systems). Note that you only need to include one table in your query – the Cable Customer Info table. As a general rule, do not include any extra tables into a query if you are not going to use the fields from that table.

2. Display Name, Address, City, and Phone for all customers that have HBO. Sort alphabetically by Name. For this query, you will need both tables in the query. Is there a line connecting ID in the first table to ID in the second table? If not, you do not have a relationship between the two tables and this query will not return the correct results.

3. Display Name and Phone (sorted alphabetically by Name) of those customers with either HBO or Showtime.

4. Display Name and service Start date for those customers that have a start date earlier than 1/1/2000 (use the form <1/1/2000).