Access Database Assignment
jalqattan
assignment_9_2014_11_01_2.docxAssignment 9
BSIS 105, Fall 2014
Access Database Assignment
In this assignment you will deal with using the Microsoft Access database system. You will be using the Highway_Patrol.accdb that I have placed on Blackboard. This database has already been created and some data has already been entered into the system. You should copy this file onto your system and save it in a secure location so that when you make changes they can be saved to your own file.
In this assignment you will be doing the following tasks:
1. You will examine the entity/relationship diagram (ERD) and in writing explain what information the database is holding (not the specific data, but in words what the business application is and how that data can be used).
2. Add data to the database.
3. Examine a query written against the database.
4. Write an additional query.
Assignment Details
Part 1 – You are to examine the entity/relationship diagram (ERD) for the Highway_Patrol.accdb database and describe in business terms (not technical terms) what this database structure is designed to accomplish, who would add data to it (think about each entity) and how an officer would use it. In addition, discuss the minimum information an officer would need to issue a traffic citation.
Part 2 – You are to add the following data to the database. For each case state the minimum data the office needs to issue the citation.
Officer Barker with badge number 777 issued citation number 17 on 11/30/2014 at 13:08 to driver Miller with driver’s license A09342 driving vehicle Ford Escort with license number 3JMN5466 for the driving offense of Failure to Stop with a cost of $436. (Note: Be sure to enter the time in the format 13:08:00)
Officer Jones with badge number 444 issued citation number 18 on 11/30/2014 at 14:37 to driver Able with driver’s license A19829 driving vehicle Ford Mustang with license number 3BUM1111 for the driving offense of Speeding2 with a cost of $328.
Officer Thompson with badge number 555 issued citation number 19 on 11/30/2014 at 15:37 to driver Rose with driver’s license A54203 driving vehicle Chevy Malibu with license number 2JIL9632 for the driving offense of Failure to Stop with a cost of $436. (Note: If you enter this specific record directly, you will get an error. This error should not occur in actual practice. You must figure out why you got the error, correct that and then state in a sentence in your solution as to why the error occurred and how you fixed it. Then explain why this error should never occur in reality.)
You should print out a copy of the Citation table to show that you added these items.
Part 3 – I have created Query1 for you to examine. First run the query and show the results of the query. Then explain how the database was able to get this information. That is, explain what tables the system used and how the tables were matched together to produce this output.
Part 4 – You are to create a query (name it Query2) using the query by example tool (QBE). The query should contain the following data in this order:
Officer Badge Number
Officer Name
Citation Offense
Fine Amount
This data should be sorted in ascending order of the badger number. You should print out a copy of the results of running this query and include that in your assignment.
Assignment Submission
You are to hand in a printout of your MSWord file with answers to the above questions and you must submit that electronically. You must also submit your copy of the database electronically. The due date is 3:30pm on December 9, 2014.
1
