Assignment 3:
Microsoft Access Forms and XML Documents
Due Date: Saturday, May 25 by 6:00 pm.
Late Policy: This assignment will be accepted up to one (1) day late.
Assignments submitted after May 25 at 6:00 PM but before May 26 at 6:00 AM will be deducted 10% of the total grade.
Assignments submitted after May 26 at 6:00 AM but before May 26 at 6:00 PM will be deducted 25% of the total grade.
Assignments submission will be closed at 6:00 PM on May 26 and no assignments can be submitted to OWL after that time.
Project 1: Microsoft Access Form
Create a form based on the Agent Data you created in Assignment Two (2).
Make a copy of your assignment two (2) database (youraccountname_Business.accdb) and rename it
to: youraccountname_Offices.accdb
Use the database created in Assignment Two (2) to complete this project.
The input form should have a format similar to (but does not have to be ‘exactly’ like) the following:
Create a new form named “Agents” that meets the following specifications:
a) Form must contain only the five fields from the Agent table shown above in the example.
b) Create the form so that the fields appear in the same order as shown above.
c) Changethetaborderofthecontrolssothattheusertabsthroughthecontrolsfromlefttoright,
top to bottom.
d) The Office ID field MUST be a Combo Box (see video in Forums for instructions).
e) Set the Enabled property of the AgentID to: No. This is because the user does not input or
change this value.
f) Set the font color of AgentID to red, center and bold the text in the box and show the border around the AgentID field.
g) Change the font color of the Agent ID label to red.
h) Use a different background and font color for the two Name (Last and First) fields.
i) Make the border color of the two Name fields the same color as the text color of these two
fields.
j) Change the way the phone number is displayed by using an Input Mask (do NOT use Format).
k) Insertaformheaderwiththetitle“AgentList”andany‘appropriate’image.
(Any image will do. You can use your imagination as to what is ‘appropriate’ for the List…)
l) Arrange the three major fields on the input form in an appropriate manner so they can be
placed inside a rectangular box. Make the Special Effect of this rectangle as: Sunken.
m) Using the Properties of the Form, change the Record Selectors option to: No.
(This will remove the bar on the left side of the Form)
NOTE: The assignment has been changed from first postin to include the requirement of the Office ID Combo Box.
NOTE: Using the form (NOT the datasheet – the TAs will test using the Form only) add a new Agent data to the database.
.
Complete the above as required saving the form in “youraccountname_Offices.accdb”. .
Project 2: Correct and Fill in an XML Document and XML Schema
You realize the importance of being able to share data with others that produce a similar product.
To this end you want a mechanism to send and receive data regarding companies that provide the materials you use in your business. This mechanism is obviously going to be the eXtendable Markup Language (XML).
The data you want to track is about the companies that make and/or supply these materials along with a list of the materials associated with each company.
This will contain information on the companies (their name and location) and will list the materials they make or supply.
After you are finished correcting and populating the XML, it will be used in Project Three below to import the data directly into your MS Access database.
As a starting point, you are provided with an XML file and associated XSD Schema file. These files are named:
MaterialsBlank-withErrors.xml
Materials-withErrors.xsd
These files (as their names suggest) are blank and contain errors.
You must correct the errors, and then fill in xml file with data appropriate to your specific business.
There are five (5) errors in total between the two files. You are to find these errors and correct them. (note: this is a good exercise to practice for the final exam as it will contain similar activities)
As a hint, the first error that you must correct is that the files are missing their root element. You must fill this in before proceeding with correcting the other errors.
To add the root, you will use the XML syntax to your XML document of:
where rootName is the name of the root you come up with for your XML document and yourFileName is the name of the actual XSD Schema document (you will be changing the name of the XSD Schema file to include your UWO user name as specified in the submission directions below).
SO: If your root name in your design was Niblick and you saved the XML Schema file as jdoe3_Materials.xsd then you would have the following line in your XML:
Notice this substituted Niblick for rootName and jdoe3_Materials.xsd for yourFileName.xsd
And then, you must add the root as the first element in your XSD schema file using the line of:
where rootName is the name of the root of your XML document.
(hint: this element is of complex type – make sure your schema reflects this fact)
Once the root is included in your XML and XSD, then you can proceed to find the other four (4) errors.
These errors will be comprised of two (2) typographical errors and two (2) omissions (things missing), some in the XML and/or in the XSD.
You really must find these and correct them on your own. If you get stuck, do NOT ask someone for help because they will just tell you where the error is and what the correction is, then although your assignment will be completed, you will not have the practice or experience when it comes to the exam. The points on the exam will weigh much more towards your final grade.
Suggestion on how to approach the assignment:
(1) – fill in the root in both the XML and the XSD schema.
(2) – fill in data for one company and one company only, using at least two examples of materials. (3) – run this through the XML validator until all four (4) errors are found and corrected.
(you will have to fill in the data in order to test and validate)
(4) – copy and paste the one correct and valid company in XML for the other three required examples. (5) – fill in the data for the other three companies (must be different from the first)
(6) – validate the completed XML and XSD to ensure no errors propagated by the copy and paste.
You will make up the data that will be in the final XML file, but here is an example of what is required for the assignment.
Any data that is approximately correct will suffice. Do not overthink the data. Just make it something so the fields will not be empty during the import.
Example Data (you cannot use these)
(preference ORDER is 3 (third) on the list – not necessarily the third entry in XML) (Company Name) – XYZ Company
(Company ID) – 37
(Company City) – Sarnia
(Company Province) – Ontario
(Material Brand Name) – Gold Braid Wonder Stitching (Material Identification) – 002535
note: this material ID must come from your existing assignment two MATERIAL table you will need this in order to relate the tables in your Access database
(Associated Company ID) – 37
(Material Brand Name) – Grey Flannel Fluce (Material Identification) – 125214
(Associated Company ID) – 37
(Material Brand Name) – Blue Velvet Smoothy (Material Identification) – 125214
(Associated Company ID) – 37
(Company Phone Number) – 9055552387
This means that in the XML document, fill in the line:
with data
– example:
You must build an XML file on the specifications above and make up the data for four (4) fictional companies. At least one company must have more than one material associated (i.e. one company has three materials, another has five, and another has only one).
Your XML document must be a FLAT text file (NOT a Word Document (.doc) not a Text (.txt) Document ) saved and submitted as an XML file (it MUST be a file with the extension .xml). Files with a .doc or .txt extension will lose major marks.
You MUST use a flat text editor (either: Brackets –or- NotePad++) to edit your XML data document and XSD Schema document.
IMPORTANT :
You must identify yourself in the XML documents (XML and XSD). Somewhere towards the top in the actual submitted XML and XSD files you must include:
– your first and last name
– your Western ID (see below for a description of your Western ID) – your student number
– the Date you completed the XML document
Your name and student Number and Student ID MUST appear in both (XML and XSD) documents or the TAs will not grade this project and you will receive a zero (0) on this project
– NO EXCEPTIONS.
NOTE: Your XML file must validate, otherwise it cannot be uploaded into MS Access as required in Project 3. You will lose major marks if it does not validate. In past semesters students expressed confusion over this requirement. It must be valid as per the validation web site referenced below in these instructions.
If you are unclear about the ‘must validate’ requirement, please contact me and I will be glad to give a further definition. I do not want the students to be unclear or feel that this has not been specified in advance.
You must use the XMLValidation site below. It provides more details on error messages: http://www.xmlvalidation.com – it does not matter if another site says it is valid. This site only.
Your XML must be valid as per this web site. Run your XML file and Schema through this (ensure you check the ‘Validate against external XML schema’ box on this page).
If this web site shows errors on your XML or Schema, you will lose almost all of the marks. Again, contact me, or leave a message in the FORUMS in OWL if this is unclear.
An XML file that is not valid is not usable. This is the reason for this requirement. ‘Close enough for partial marks’ is a high school concept. In business, a file is either valid or it is useless.
NOTE: It must be valid even after you include your name, ID and date above.
Your XML file MUST still be syntactically correct and MUST still validate after you have included your identification above. If you do not identify yourself in the XML documents you will receive a zero (0) for that project. (hint: you can NOT use tags – your ID is NOT data.)
Note: Tutorials on HTML and XML are available at http://www.w3schools.com. The XML file name must be youraccountname_Materials.xml
For example: jdoe3_Materials.xml
The XSD Schema file name must be youraccountname_Materials.xsd For example: jdoe3_Materials.xsd
Project 3: Upload your XML Document into your MS Access Database
Upload the “youraccountname_Materials.xml ” file into your MS Access database that you used in Project Two (youraccountname_Materials.accdb) using the XML data import utility.
Use the built in XML Import Utility found under the External Data tab.
Use the Relationships utility in Database tools to create a relationship between the data and then enforce referential integrity with this tool.
You MUST create a relationship between the data and then you MUST enforce referential integrity with this tool.
You will first create a relationship between the Company and the MaterialSource tables using the company ID as the Primary/Foreign key match.
note: you must first indicate the Primary Key of the imported COMPANY table in MS Access using the Design View of the table before you can enforce integrity.
Then you MUST enforce referential integrity on this new relationship.
Project 4: Information Systems Questions about Your Company
Create a one page MS Word document and complete the following questions pertaining to the business you described in Assignment One (1).
Each answer must be comprehensive (more than one sentence). Each answer requires at least four sentences. The entire Project 5 should be at least approximately 600 words. It is expected that some thought and explanation is included in this section.
1.) Identify which of Porter’s Five Forces might influence your company the most and why.
2.) Which competitive strategy (Cost + Differentiation) does your company position itself any why.
3.) Identify one source of data you will use to aid in the marketing of your product. Where are you going to get this data from, and how do you plan on using it?
The format of this document should be identical to format you used in Assignment One (1).
Place your name, followed by the company name at the top.
Fill in the required information after.
At the end of the document, include your name, Student number and Western ID (the first part of your Western email (i.e. if your email is – dernt373@uwo.ca your ID will be – dernt373)
Formatting is not important as long as the document is easy to follow:
This document must be a Word file saved and submitted as a .doc (or .docx) file
The name must be a combination of your Western Account Name and the name of your company. The file name must be youraccountname_companyname_A3.doc (or .docx)
– example (from above) dernt373_MaggicSoftware_A3.docx
Submission Instructions:
You must upload and submit, via the CS1032 Web Site in the Assignment Section, the following four
(4) files:
youraccountname_Materials.xml
youraccountname_Materials.xsd
youraccountname_Offices.accdb (.mdb for Access 2003 or earlier) youraccountname_companyname_A3.docx (.doc for Word 2003 or earlier)
(remember to check your file names. They MUST match the required conventions above.)