Information Systems

1. Analyze the spreadsheet shown in Figure 5-15 and list all of the problems that occur when trying to track the assignment of sheet music using this spreadsheet.
• The first 6 entries to the spreadsheet seem easy to track however they do not show which member is using which sheet of music
• After the first 6 rows all the musical works are just listed and you cannot tell who is responsible for each sheet
• One of the sheet music is duplicated
• There are no keys to relate the members and the music
• Information is listed in the wrong columns

2. The following two tables could be used to store the data in Figure 5-15 in a database:
• Redraw the data in Figure 5-15 into this two-table format.
Choir Member Table
Member ID Last Name First Name Email Phone Part
1 Ashley Jane JA@somewhere.com
703.555.1234 Soprano
2 Davidson Kaye KD@somewhere.com
703.555.2236 Soprano
3 Ching Kam Hoong KHC@overhere.com
703.555.2236 Soprano
4 Menstell Lori Lee LLM@somewhere.com
703.555.1237 Soprano
5 Corning Sandra SC2@overhere.com
703.555.1234 Soprano
6 Wei Guang GW1@somewhere.com
703.555.9936 Soprano
7 Dixon Eleanor ED@thisplace.com
703.555.12379 Soprano
8 Duong Linda LD2@overhere.com
703.555.8736 Soprano
9 Lunden Haley HL@somewhere.com
703.555.0836 Soprano
10 Utran Diem Thi DTU@somewhere.com
703.555.1089 Soprano

Musical Work Table
Musical ID NameOfWork Composer Part CopyNumber Member ID
1 B-Minor Mass J.S Bach Soprano Copy 7 1
2 Requiem Mozart Soprano Copy 17 2
3 9th Symphony Chorus Beethoven Soprano Copy 9 3
4 B-Minor Mass J.S Bach Soprano Copy 11 4
5 Requiem J.S Bach Soprano Copy 19 5

• Select primary keys for the ChiorMember and MusicalWork tables.
i. The primary keys for the ChiorMember table should be Member ID and theprimary key for the MusicalWork should be Musical ID

• The two tables are not integrated; they do not show who has checked out which music. Add foreign key columns to one of the tables to integrate the data.
i. The foreign key to integrate the two tables is the Member ID column in the MusicalWorkTable. This way when someone checks out or borrows a sheet music they can link it to who the student is. This connects the two tables and integrates the data.
• This two-table design does not eliminate the potential for data integrity problems that occur in the spreadsheet. Explain why not.
i. This design does not eliminate the potential for data integrity problems because who ever enters the information has to make sure it is all entered correctly. There is always error potential therefore whoever uses the table must be careful. ii. Also, there must be a separate table that has all of the information linked together.
3. A three table database design for the data in the spreadsheet in Figure 5-15 is as follows:
• Redraw the data in Figure 5-15 into this three-table format.
Member ID Last Name First Name Email Phone Part
1 Ashley Jane JA@somewhere.com
703.555.1234 Soprano
2 Davidson Kaye KD@somewhere.com
703.555.2236 Soprano
3 Ching Kam Hoong KHC@overhere.com
703.555.2236 Soprano
4 Menstell Lori Lee LLM@somewhere.com
703.555.1237 Soprano
5 Corning Sandra SC2@overhere.com
703.555.1234 Soprano
6 Wei Guang GW1@somewhere.com
703.555.9936 Soprano
7 Dixon Eleanor ED@thisplace.com
703.555.12379 Soprano
8 Duong Linda LD2@overhere.com
703.555.8736 Soprano
9 Lunden Haley HL@somewhere.com
703.555.0836 Soprano
10 Utran Diem Thi DTU@somewhere.com
703.555.1089 Soprano

Musical ID NameOfWork Composer Part CopyNumber Member ID
1 B-Minor Mass J.S Bach Soprano Copy 7 1
2 Requiem Mozart Soprano Copy 17 2
3 9th Symphony Chorus Beethoven Soprano Copy 9 3
4 B-Minor Mass J.S Bach Soprano Copy 11 4
5 Requiem J.S Bach Soprano Copy 19 5

Member ID First Name Last Name Name of Work Musical ID
1 Jane Ashley B-Minor Mass 1
2 Kaye Davidson Requiem 2
3 Kam Hoong Ching 9th Symphony Chorus 3
4 Lori Lee Menstell B-Minor Mass 4
5 Sandra Corning Requiem 5

• Identify which columns are primary keys for each of these tables.
i. The primary keys are the Member ID and the Musical ID
• The foreign keys are already in place; identify which columns are foreign keys and which relationships they represent.
i. The Musical ID in the third table is the foreign ID.
• Does this design eliminate the potential for data integrity problems that occur in the spreadsheet? Why or why not?
i. Yes, because it links the members with the music sheets. If their name is linked the exactly which sheet of music they have out then there is no way for data integrity problems.
4. Assume you manage the choir and you foresee two possibilities:
• Keep the spreadsheet, but create procedures to reduce the likelihood of data integrity problems.
• Create an Access database and database application for the three-table design.
Describe the advantages and disadvantages of each of these possibilities. Recommend one of these two possibilities and justify your recommendation.
For the spreadsheet option, some of the advantages would be that it would be easier for most people to use. The user would be able to see clearly which student has which music. The disadvantages of using a spreadsheet is that whoever enters the information must be extremely careful when entering the information because it is very easy to mess up. Also, since there are so many tables and you have to look back at the previous ones to see who has what it can get a little confusing. If we decided on the database application the advantages would be that it is very easy to enter information and you can look up one individual at a time or all at once. It lays out the information very clearly. However the disadvantages would be that Access is very confusing and not many people know how to use it correctly