Instructions: Read through this entire document before beginning. The other piec
ID: 3834099 • Letter: I
Question
Instructions: Read through this entire document before beginning. The other pieces of information that will assist you are: the Oburg 5K registration data and Oburg Trifold, which has the registration/application form.
Part One. Planning & Designing a database. (15 Points)
Mr. Blahut has asked you to design and create a database that will manage runner applications for the Oburg 5K run, which is scheduled for June 20, 2015. To begin, you must create a word document that has a MS Word table as part of your design. You must first decide what your field names and data types will be for each field. Review the race application/registration form for the field names to be used. You will have 10 Field Names. Use the column headings from the registration data for your field names. The field Bib_No is the primary key. The MS Word document is to be saved as ‘OBurg5K Design’ and attached for credit. Insert your Name in the header and file name in the footer of the document.
1) Analyze what Data Types should be used for each field. You may need to review the Data Types in Access to determine this.
2) The required fields are Last Name, Birth Date, and Gender.
3) A primary key with a Data Type of text is used. A unique bib number is assigned to each runner.
4) Once the design is complete, determine a test record for your test data. This helps when
determining your fields and data types. The name of your database that you will be creating is
ACCESSTest.
5) You must determine an appropriate field size for each field.
An example of the partial word table layout is below:
Field Names
Bib_No
LastName
FirstName
BirthDate
Size
Data Types
Shorttext
Shorttext
Date/Time
Test Data
001
Batko
Timothy
08/02/1987
L
Part Two. Once the design is completed, then it will need to be defined to Access. (45 Points)
1) Create the new table using the Design View. The name of your table is REGISTRATION.
2) Add each of your fields and the Data Type. A description is to be added for each field. The following
will have changes to the field properties.
a) The birth date field is to have an input mask.
b) Appropriate field sizes are to be used for all fields.
3) Save your table.
4) Add your test data using datasheet view to the table to create a test record. This will assure that
your table will accept the data that you have designed it for.
1
Part Three. Building a Form for Data Entry. (10 Points)
1) Create a simple Form using the table that you created. Find a logo to insert and modify the heading of the form to read ‘OBURG 5K Registration’. Give the heading some color.
2) Save the form. Then enter the additional data using your form. Your table will have a total of eight records. Add yourself as one record. Use the data from the ‘Oburg 5K Registration Data’. Verify that all eight records have been entered successfully. You can do this by reviewing your table.
Part Four. Build a Query. (15 Points)
1) 2)
Mr. Blahut would like to know which female registrants ordered a small T-shirt. Build your query based on this criterion.
The only fields he wants to see on this query are:
First Name & Last Name, and shirt size.
Records are sorted by last name in ascending order.
3)
Part Five. Create a Report. (10 Points)
Save your Query and give it an appropriate name.
1) Create a simple report using the query that was created in part four.
2) Use the logo that you used for your form.
3) Use the same format for your Report Title that was used on your form, so that it is consistent.
4) Add your name to the title in the Report.
5) Save the report.
Part Six. Finally, export the data from the table that you created to Excel. (5 Points)
1) Put your name in the left section of the header of the Excel Spreadsheet.
2) Insert the file name in the footer.
3) You must upload the excel spreadsheet to receive credit.
Oburg 5K Registration data.xlsx 12/6/2013 2:28 PM
LastName
FirstName
Bib #
Address
City
State
Gender
Age
shirt size
Birthdate
Worthington
Nicky
1 Morningstar
Branchville
NJ
F
38
M
2/27/1974
Trainor
Joesph
121 Drew Rd
West Milford
NJ
M
23
M
6/2/1989
Staple
Bill
152 Whitney Ave
Hamburg
NJ
M
37
XL
2/11/1975
Shortway
Cherie
23 hemlock Lane
Sussex
NJ
F
51
S
12/7/1960
Nicholson
Bob
27 Charcoal Rd
Sparta
NJ
M
84
XL
5/16/1928
Muilenburg
Barbara
55 Meadow Pond Rd
Newfoundland
NJ
F
18
S
10/22/1993
Kassover
Jeffrey
9 Newton Ave.
Pompton Lakes
NJ
M
70
L
11/5/1941
Field Names
Bib_No
LastName
FirstName
BirthDate
Size
Data Types
Shorttext
Shorttext
Date/Time
Test Data
001
Batko
Timothy
08/02/1987
L
Explanation / Answer
The database design of the runner application have the following table.
RUNNER_USER_TABLE(Bib, FirstName, LastName, Address, City, State, Gender, Age, ShirtSize, BirthDate)
The datatype for each attributes are:
1. Bib (Text)
2. FirstName (Text)
3. LastName (ShortText)
4. Address (Text)
5. City (ShortText)
6. State (ShortText)
7. Gender (ShortText)
8. Age (Integer)
9. ShirtSize (ShortText)
10. BirthDate (Date/Time)
Here in the above table we have following things:
1. We can use Bid as the primary key. We can also use the combination of (FirstName + Address) as the primary key.
2. Bib field will be unique and it will not be null in any case. We can also make this field as AutoNumber if required.
The test records for this database will be as follows:
********Field******* *****TestData******
1. Bib (Text) 001
2. FirstName (Text) Max
3. LastName (ShortText) Smith
4. Address (Text) House no. 230 triangle street
5. City (ShortText) xyz
6. State (ShortText) NewYork
7. Gender (ShortText) M
8. Age (Integer) 24
9. ShirtSize (ShortText) 28
10. BirthDate (Date/Time) 01/05/1989