2023 McMurry University COIS 3380 Management Info Sys Summer 2015 Mr Brozovic Assignment 5 XLM | Assignment Collections

McMurry University COIS-3380, Management Info Sys
Summer 2015 (Mr. Brozovic)
Assignment #5 – XLM J
Complete the step-by-step Access exercise in XLM J, following the additional instructions in this
document. You may want to view the author’s lecture slides which provide a number of extra
pointers. The PowerPoint lecture can be downloaded from the textbook’s Web site. However,
do NOT download and submit the author’s solution for XLM J – it has a number of differences.
Doing so will result in a grade of 0 for this assignment!
The following items supplement the step-by-step instructions from textbook XLM J:
• Page 480 – Bill of Material table: Decimal places property for Concrete Type must be 0.
Field size property for Unit must be single instead of long integer and decimal places must
be auto. Set all 3 fields as required.
• Page 481 – define all remaining relations (table designs) using info from Figure J.8. Ignore
the “optional for our purposes” comments. Set all integrity constraints as stated with the
following additional guidance. For the Customer Phone field, set the field size to 10 and
then set the input mask by clicking the … button at the right side of the input mask
property box to bring up the Input Mask Wizard and select Phone Number. For the Raw
Material ID fields in both the Raw Material and the Bill of Material tables, set the format
property to > which will automatically convert lowercase letters to uppercase.
• Page 483 – after adding all relations to the relationship palette and before adding any
relationships, rearrange the tables as shown in Figure J.11 by dragging their title bars
around. Also, resize the table windows so that all field names are fully displayed.
• Page 484 – define all relationships as shown in Figure J.11. Mistakes can be deleted or
edited by right-clicking on the relationship link. Adjust table positions in the relationship
pane as needed to clearly show the relationships without lines crossing each other or
table boxes.
• Pages 485-487 – enter all remaining data from Figure J.1 paying attention to the
instructions in the textbook for populating the tables with data. After entering the data
into a table, “best-fit” each column in the datasheet view by double-clicking the right
border of the field name cell (you should have a double-headed arrow pointer) and save
the changes to the layout when you close the table. Add one more Customer record with
customer data: 9999, “McMurry University”, (325) 793-6824, with your first name or
nickname and last name as the Customer primary contact. Add one more Employee
record with employee data: 999999999, your last and first name or nickname, and a hire
date of 7/7/2014.
• Page 488 – save the simple query with the name suggested.
• Page 489 – to add selection criteria to an existing query, modify a copy of the previously
saved query. Right-click the desired query name and select copy. Then right-click in same
area and select paste. Rename copy of query by adding “with Criteria” to end of existing
name. Right-click new copy and select Design View. Modify with criteria and save. Close
and reopen query and notice that criteria has changed to “400 or >400” on a single line.
Another equivalent criteria would be to enter “>=400”.
McMurry University COIS-3380, Management Info Sys
Summer 2015 (Mr. Brozovic)
• Page 490 – In the query table display area, resize all tables to properly display all field
names and rearrange the tables to show the relationships without lines crossing each
other or tables. Save the query with the name “Orders”.
• Pages 490-491 – Save the practice queries with names “Practice 1” … “Practice 8”. Resize
and rearrange tables as in previous step.
• Pages 492-493 – Save the report with the recommended name then modify the report to
move the Customer name column over so that it is aligned under the word “Report”. Save
the changes to the report when prompted.
• Page 494 – Create the described query and save it with the recommended name. Resize
and rearrange tables as in previous steps in order to properly view field names and
relationships. Your result should display 18 lines.
• Pages 494-496 – report orientation (step 8) must be landscape (not portrait). The report
style in step 9 is a left-over from Access 2007 and does not exist in Access 2010. Instead,
you would apply a Theme while in design view, but is not required for this exercise – the
default theme is “Office”.
• Pages 496-499 – Report design view – adjust items as described in text and below.
o See the sample report (Assign5XLM-JReport.pdf) from the Moodle assignment page
for correct version of “revised” report. It’s much easier to view than the author’s
textbook figures.
o General instructions: To align and resize items, use shift+click to select multiple items
then use options from the arrange menu. For centered items to align properly with
their headings, the heading and data items must be aligned together and should be
same width.
o Page & Concrete Type headers – make sure column labels and data are fully displayed
when you print preview the report. Easiest way to get the 2-line headers is to use
shift-enter where you want the line to break. Center headers and details only for
“Concrete Type”, “Unit”, and the two “ID” fields.
o Concrete Type footer – move unit subtotal under and centered with corresponding
data. It should be red and bold along with its label which is repositioned.
o Move items to top of their report sections and then “shrink” each section by moving
its bottom edge up to the items – report must fit on one page with the default margins
of ¼ inch.
• Page 499 – Save basic form as “Order Form” when prompted for a name.

==============

McMurry University COIS-3380, Management Info Sys
Summer 2015 (Mr. Brozovic)
Assignment #6
Soccer League Database – Part 1
Most applications that create a product, like a document, allow you to create and work with the
item and name it later when you save or exit. Databases must be named and placed in the
desired folder when they are first created. The only time you will be prompted to save something
is when designing or modifying database objects (e.g., tables, forms, queries, and reports) but
NOT when you are adding or changing data or when you exit Access.
Create a new (blank) Microsoft Access (2007/2010/2013) database named “Soccer”. Create the
following tables with the structures shown with the first field of each table as the primary key.
Fields with validation rules must have suitable validation text. Field properties listed below must
be set as indicated. To obtain help about a field property, click within the property box and press
the F1 key – read the help related to the property and use the examples provided.
Teams table:
Field Name Data Type Field Properties
Team ID Number Primary key, field size = Integer, no decimal places, required
Validation rule requires it to be >= 0
(Note: team 0 is for “unassigned” coaches & players)
Team Name Text Field size = 15, required, cannot be 0 length
Color Text Field size = 15, required, cannot be 0 length
Mascot Text Field size = 15
Gender Text Field size = 1, required, cannot be 0 length, force uppercase
Validation rule allows only:
F, M, or C (Note: female-only, male-only, or coed)
Age Bracket Number Field size = Byte, required, no decimal places, default value 0
Validation rule allows only the values 0, 6, 8, 12, 16, 20
Players table: (to be followed by the common fields listed below)
Field Name Data Type Special Properties
Player ID AutoNumber Primary key
Gender Text Field size = 1, required, cannot be 0 length, force uppercase
Validation rule allows only: F or M (Note: female or male)
DOB Date/Time Format = Short Date, required, use short date input mask
Ins Signed Yes/No Required, default value is No
Team ID Number Field size = Integer, default value = 0, required, no decimal places
Validation rule requires it to be >= 0
Note: 0 indicates not assigned to a team (info only – not a rule)
Plus the common fields listed below
McMurry University COIS-3380, Management Info Sys
Summer 2015 (Mr. Brozovic)
Coaches table: (to be followed by the common fields listed below)
Field Name Data Type Special Properties
Coach ID AutoNumber Primary key
License Text Field size = 1, required, cannot be 0 length, force uppercase
Validation rule allows only: A, B, C, or N (non-licensed)
Team ID Number Field size = Integer, required, default value = 0, no decimal places
Validation rule requires it to be >= 0
Note: 0 indicates not assigned to a team (info only – not a rule)
Plus the common fields shown below
Common fields added to tables: Coaches and Players (this is NOT a separate table!)
Field Name Data Type Special Properties
Last Name Text Field size = 15, required, cannot be 0 length
First Name Text Field size = 15, required, cannot be 0 length
MI Text Field size = 1, force uppercase, use letter input mask
Street Text Field size = 25, required, cannot be 0 length
City Text Field size = 15, required, cannot be 0 length
State Text Field size = 2, required, cannot be 0 length
Force uppercase, use input mask to allow only letters
Zip Code Text Field size = 9, required, cannot be 0 length
Use zip code mask
Home Phone Text Field size = 10, required, cannot be 0 length
Use phone number mask
Alt Phone Text Field size = 10, use phone number mask
After creating each of the above tables, perform the following additional tasks:
1. Create a default or basic form for each table by selecting the table and using the “Form”
button on the Create ribbon. After Access creates the form, save it with the default
name, and then open it in design view and make sure labels and text boxes are sized
properly. Short fields shouldn’t have large text boxes. Both labels and textboxes must
be large enough to properly display entire contents. The form must have a title at the
top. You can receive BONUS points by making the form more appealing or useful.
2. Use the appropriate form to enter the sample data shown on the next page into the
appropriate table. Note: the primary key ID numbers will be automatically generated
except for the Teams table. If you can’t enter the data properly, you may have an error
in the validation rule for that field.
McMurry University COIS-3380, Management Info Sys
Summer 2015 (Mr. Brozovic)
Teams:
TeamID TeamName Color Mascot Gender AgeBracket
1 Herders Red Mustang M 16
2 Wildcatters Gold Jaguar F 8
3 Raptors Blue Falcon F 12
4 Pounders Purple Dalmatian C 6
Coaches:
Coach
ID License
Team
ID Last Name First Name MI Street City State Zip Code Home Phone Alt Phone
1 N 0 Your Last Name Your First Name 123 Main St Abilene TX 79697-0968 (325)793-6824 (325)793-3800
2 B 3 Coleman Amelia N 1400 Sayles Blvd Abilene TX 79605-1234 (325)793-1758 (325)692-2015
3 A 1 Hernandez Renaldo F 1234 Campus St Abilene TX 79603-2587 (325)793-3722 (325)690-7065
4 C 2 Walker James W 9876 Indian Ct Abilene TX 79606-3546 (325)793-3456 (325)694-5318
Players:
Player
ID Gender DOB
Ins
Signed
Team
ID Last Name First Name MI Street City State Zip Code Home Phone Alt Phone
1 F/M 7/18/1995 N 0 Your Last Name Your First Name 123 Main St Abilene TX 79697-0968 (325)793-6824 (325)793-3800
2 M 12/22/2003 Y 0 Jackson Jeff K 3476 S 27th St Abilene TX 79606-2954 (325)793-9443 (325)691-3579
3 M 3/16/2002 N 0 Fuentes David M 526 Tribe Ave Abilene TX 79605-4567 (325)793-8620 (325)695-7412
4 F 6/7/2006 Y 0 Tansek Christina M 2344 George Dr Abilene TX 79603-9876 (325)793-7827 (325)692-8520