Assignment #5 – XLM J && Assignment #6

| November 17, 2015

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

Get a 5 % discount on an order above $ 150
Use the following coupon code :
2018DISC
Application: Web Prototype Outline
computer Security#6830

Category: Homework Help

Our Services:
Order a customized paper today!