Description
Project 1: Microsoft Access You have been asked by a Computer Repair Company to create a database that can be used to track their customers. You are to create a table in Access based on the following specifications.
a) Create a new blank database with the filename “youraccountname_customers.accdb”. b) Create a new table called “Customers” with the following fields:
Field Name
Data Type
Field Description
Field Size
Requirements
CustomerID Autonumber Primary Key Long Integer
Required
Lastname Text 50 Required Firstname Text 25 Required Phone Text Format: (123)456-9999 15 Required with Input Mask Address Text 30 City Text 30 Province Text 2 Display in Uppercase only PostalCode Text Format: A1B 2C3 7 Input Mask Warranty Yes/No RepairDate Date/Time Short Date Format RepairType Text Values: Hardware, Software, Virus, Battery, Peripherals 15 Use Lookup with Combo Box, set default to “Hardware” Model Text Values: D (Desk Top), L (Laptop), T (Tablet), C (Compact), P (Phone) 1 Use Validation Rule Make Text 30 RAM (Gigabytes) Number Integer Required (0 for devices with no RAM) Estimate Currency 10 Set default value to 0 and format with 2 decimal places
c) CustomerID is to be the primary key. It should be an AutoNumber data type and the Format should be XX0000, where “XX” are your initials and each time a record is added to the table the primary key will automatically produce a CustomerID number that begins with your initials followed by a four digit number. d) Set the Required property to Yes for the Lastname, Firstname, Phone and RAM.
e) Set an Input Mask for the Customer’s Phone number in the form (123)456-9999. f) Set an Input Mask for the Customer’s Postal Code in the form A1B 2C3. g) Use the Format property so that the Province is always displayed in uppercase. h) Specify Repair Type using Lookup with a Combo Box and specify “Hardware”, “Software”, “Virus”, “Battery”, “Peripherals” as the values to be listed. Set the default to “Full Service”. i) Use a Validation Rule for the Model of the device. Allowing only “D” (Desk Top), “L” (Laptop), “T” (Tablet), “C” (Compact) or “P” (Phone). Create an appropriate message as the Validation Text to explain that these are the only values allowed. j) For Estimate, set a default value to 0 and set a currency format with 2 decimal places. k) Set appropriate field sizes for all fields as shown above. l) Enter one record into the completed table, using your name as the customer and appropriate values for the other fields. Complete the above as required saving the database as “youraccountname_customers.accdb” and attach the file to your submission.
Project 2: Microsoft Access You have been asked by the Computer Repair Company to create an input form for the database. Use the database created in Project 1 to complete this project. The input form should have a format similar to the following:
Create a new form named “Customers” that meets the following specifications: a) Form must contain every field from the Customers table. b) Create the form so that the fields appear in the following order: CustomerID, Repair Date, Firstname, Lastname, Make, Model, Warranty, Repair Type, RAM, Estimate, Address, City, Province, PostalCode, Phone (as shown above) c) Change the tab order of the controls so that the user tabs through the controls from left to right, top to bottom. d) Set the tab stop property of the CustomerID to No because the user does not input this value. e) Use a different background colour for the labels of the required fields (CustomerID, Lastname, Firstname, Phone and RAM) and emphasize that these fields are required by including a note at the bottom of the form to explain the coloured fields. f) Insert a form header with the title “Your Name Computer Repair” and an appropriate image. g) Format the input form in an appropriate manner (placement and size of fields) h) Using the form add a second customer to the database using your instructor’s name as the customer. Complete the above as required saving the database in “youraccountname_customers.accdb”. Attach the database containing the table and form, complete with two records to your submission.
Graphic Your Name Computer Repair Image
Customer ID: Repair Date: First Name: Last name:
Make: Model: Warranty:
Repair Type: RAM (Gigs): Estimate:
Address: City: Province: Postal Code: Phone:
*coloured fields indicate a required field
Project 3: ER Diagram This project requires you to create a database design. Your design will be documented in a set of Entity-Relationship diagram using the representation as shown in the lecture materials. Draw a set of Entity-Relationship diagrams to model the following scenario.
An electronics manufacturer wants to track the activities of its various service departments, the technicians working in the service departments and the devices brought in for service along with their owners.
For the service departments, your design must be able to store:
• the service department’s unique department number • the service department’s address • the service department’s phone number
For the technicians, your design must be able to store:
• the technicians’ unique employee number • the technicians’ first name • the technicians’ last name • the technicians’ area of specialty
For the devices brought in for service, your design must be able to store:
• the electronic device’s unique Serial Number • the electronic device’s make • the electronic device’s model • the electronic device’s year
For the device owners, your design must be able to store:
• the device owner’s phone number (unique) • the device owner’s first name • the device owner’s last name
Finally, you need to store the following relationships:
• A service department will have many technicians working in it, but a technician will only be assigned to work in one service department. All service departments will have technicians working in the department. Not all technicians hired by the electronics manufacturer will work in a service department. Technicians may work in other departments which are not being modeled at this time. • All service departments will have a Service Department Manager. The Service Department Manager will be one of the technicians working in the department. A manager will be the Service Department manager for only one Service Department. • When a device arrives at the service department a technicians will always be assigned to work on that device and depending on the nature of the service additional technicians may also be assigned. A technician may be assigned to work on any number of devices and depending on the technicians’ area of expertise the technician may not always be assigned to work on an electronic device. While a device is being worked on in the service department the date when the device arrived in the department will be maintained.
• Device owners may own more than one device that may require service. All devices brought in for service will be owned by only one individual.
You will create your answer to this project in a Microsoft Word document named youraccountname_ER_diagrams.docx or as an image in JPEG format in a file named youraccountname_ER_diagrams.jpg
To create your ER Diagram you can do one of the following:
• Hand draw your diagram and scan it in using a scanner and save it as a jpeg file with the name: youraccountname_ER_diagram.jpg • Use a graphics editor to create your diagram and save it in JPEG format with the name: youraccountname_ER_diagram.jpg • Draw the diagram using MS Word and save it in youraccountname_ER_diagram.docx
Attach the file ‘youraccountname_ER_diagram.docx’ or ‘youraccountname_ER_diagram.jpg’ to your submission.
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).
1.) Does your company use a database to store its data? – if yes, then list a couple of things it stores. – if no, then why not (what is the benefit/logic behind not storing any data)?
2.) Does your company use any mobile devices other than cell phones? – if yes, describe what devices and their uses. – if no, explain what is there about your company that does not require mobile devices.
3.) What is the name of your company’s website? – was it readily available, or did you need to purchase it from a third party? (i.e. ImaDunsull.com was used by a florist but we purchased it for our use…) – or – we used ImaDunsullDimensionalServices.com because it was available?
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 was – derntwis@uwo.ca your ID would be – derntwis) 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.doc (or .docx)
Submission Instructions:
You must upload and submit, via the CS1032 course website in WebCT, the following files:
• youraccountname_customers.accdb (for later versions) (.mdb for Access 2003) • youraccountname_ER_diagram.docx (for later versions) (.doc for Word 2003) or youraccountname_ER_diagram.jpg • youraccountname_companyname.docx or youraccountname_companyname.doc
NOTE: the ER diagram MUST be readable. – if in doubt, show it to a friend and ask if they can read it. if they cannot, then the TA will not be able to either….