LSP 121 Activity 3 Relational Database and Queries This activity will give you practice at creating Access tables, performing: Normalization, Creating primary and Foreign keys. Given below are the fields for a database for cable television subscribers. The fields are not normalized. In particular, you should pay attention to the repeating premium channels and their associated charges. Cable Television Subscribers ID (make this a short text field and make it the Primary Key) Name Address City State Zip Phone Start (start date of service - make sure type is Date/Time) End (ending date of service, if any - likewise Date/Time) The following fields repeat 1 to n times: 1. Premium Channel (HBO, Showtime, etc.) 2. Premium Charge On a separate sheet of paper or on the back of one of these pages, hand-write the field names for two normalized tables using the above field names. Warning: Do NOT create any tables in Access at this point! You should have two tables: I. II. The first, which we will call Cable Customer Info, contains the fields ID down through End, and The second, which we will call Premium Channel Info, should contain the Premium Channels and Premium Charges. Don't forget to include a foreign key in the Premium Channel Info table. 1 Note: 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. You should instead be creating a table with: 1. ID (Foreign key) 2. Premium Channel. 3. Premium Charge. 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. Things will be easier if you spell ID in the two tables exactly the same and make the two IDs the same type. 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. This is a default relationship, since ID appears in both tables (same spelling), and they have the same data type. Start working on Access The data for the first table exists as an Excel spreadsheet (Cable Customers.xls, on the QRC website under Excel Files) so you can import it into Access if you don't feel like creating the table by hand and then typing in all the records. After the import, you should verify that the data has been entered correctly. Data records to be entered into the Cable Customer Info table. Note: 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 and End (dates) should be short 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. You will have to first create this table (let's call it the Premium Channel Info table) by hand (in Design mode), and then enter the data by hand. Each customer has the premium channels as listed. 2 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. Example: Smith should have two records in this second table. The first record indicates HBO and the cost $5.95, and the second record indicates Showtime and its cost $5.95. Do not put both HBO and Showtime in the same record. Name Smith Jones Shah Purcel Roberts Stein Leninger Walsh Premium Channels Subscribed To HBO, Showtime HBO, StarZ, MovieZ HBO, HBO2, Showtime, Dogz Showtime StarZ, MovieZ HBO StarZ, History, MovieZ Showtime HBO costs $5.95 per month Showtime cost $5.95 per month MovieZ cost $4.95 per month HBO2 cost $4.95 per month StarZ cost $3.95 per month Dogz, cost $3.95 per month History cost $3.95 per month So your first two records in this second table should look like: ID 1 1 Premium Channel HBO Showtime Premium Charge 5.95 5.95 Again, note that Customer ID in the second table is NOT a primary key. It can't be, for as you can see, the first two records have the same value for Customer ID, which is a violation of a Primary Key. Instead, Customer ID in this second table is a foreign key. At this point, call over your instructor to make sure your tables are correct. If they are, then paste a copy of the contents of both tables into a 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. Sort alphabetically by Name. (If you don't recall how to set up this query, then either review 3 the overhead lecture notes or read the handout Introduction to Database Systems found on my website under Week 1 Lecture Notes, Activities, and Homework.) 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>