Buad283: Assignment 02 – Microsoft Access
Scenario: You have been asked to create a Microsoft Access database management system for an educational publishing company. The company employs a staff of nearly 50 people, including publishers, administrators, researchers, and salespeople, but contracts the development of each media title to an outside author. The business rules and table relationships are as follows: • A publisher (which is simply an employee with the job title “Publisher”) manages one or more media titles, controlling its budget, sales forecasts, and production schedules. • An editor (an employee with the title “Editor”) can manage many authors, approving their contracts, workflow, and deadlines. Each author, however, reports to a single editor. • Each media title is written by a single author; there are no co-author teams. • All of the entities (tables), attributes (fields), and relationships appear in the ER diagram below. You do not need to add any additional elements to your Access database entities beyond what appears in this ER diagram. Figure 1. ER diagram for use in constructing an Access database Using this ER diagram as your guide, you will modify an existing Access database in the following steps. Once completed, upload the resulting database file into the Moodle dropbox. Publisher Editor Note that the field “Topic” in Media is equivalent to “SubjectArea” in Authors. Steps: Perform the following steps to create this database management application. (Note: You may use either Access 2010 or Access 2013 to complete this assignment.) 1. Download the AccessDB.accdb database file from Moodle. Open the database file and browse its two table objects, Media and Employees, to familiarize yourself with the data. 2. Open the Media table in Design View. Ensure that the Code field is set as the Primary Key (PK). Then, add two new fields for the Foreign Keys (FKs), as shown in the ER diagram above. These FK fields must use the Number data type, as they will be used to connect to the AutoNumber ID fields in the other two tables. 3. Ensure that all Short Text data types are 50 characters in size and that all Number data types are set to Long Integer. Save your changes and return to Datasheet View. 4. Use the Find and Replace command to replace all instances of “BD” in the Media field column with “Blu-ray”. Widen the columns (as necessary) to display their headings and data, and then apply an alternate row colour of “Orange, Accent 2, Lighter 80%.” 5. Add a Field Validation Rule to the Media field column so that only the following values are acceptable: Blu-ray, CDROM, DVD, VHS, and WEB. Add a Field Validation Message that describes the available options. Sort the datasheet by Topic into ascending order and then close it, saving your changes. 6. Open the Employees table in Design View. Rename the AutoNumber ID field to “Employee ID” and confirm that it is the Primary Key (PK). As in the Media table, ensure that all Short Text data types are 50 characters in size, but do not adjust the Number data type fields. Save your changes and return to Datasheet View. 7. Set the datasheet font to Cambria, adjust the column widths (if necessary), and sort the datasheet by Surname, into ascending order. Close the datasheet, saving your changes. 8. Create the remaining table object, Authors, using the fields shown in the ER diagram. Specify an AutoNumber field called “Author ID” for the Primary Key. Add the required Foreign Key to the table object as a Number data type and ensure that the Short Text fields do not exceed 50 characters in size. 9. Add the following record data to the Authors table, but remove the “0” that appears in the Employee ID field for each record. Save and then close the table once completed. Author’s Name Subject Area Hourly Wage Rachel Green Business $45.00 Ross Geller Geography $60.00 Monica Geller Science $40.00 Chandler Bing Math $55.00 Joey Tribbiani Computing $35.00 Phoebe Buffay Geography $45.00 10. Using the Relationships command on the Database Tools tab, establish the one-to-many relationships shown in the ER diagram. Enforce referential integrity for each relationship, but do not select the Cascade Update and Cascade Delete options. When finished, save and close the Relationships window. 11. Create three form objects, named frmAuthors, frmEmployees, and frmMedia, that will help users input and modify data in their respective tables. Remove the AutoNumber primary key fields from the frmAuthors and frmEmployees forms, but leave the Code field on the frmMedia form. Modify the titles of each form to reflect their contents (e.g., “Author Data Form”) and apply some basic formatting of your choice to the forms. Lastly, enter a “Copyright (c) your name” text label in the footer area of each form. 12. Replace the three Foreign Key fields on the frmAuthors and frmMedia forms with combo boxes that retrieve values from their respective tables. In the wizard, select the ID field, Surname, and Given fields for display in the combo boxes. Sort the combo boxes by Surname and hide the ID key column when asked. Remember to store the value in the appropriate Foreign Key field and, lastly, delete the original text boxes and labels from the form. Save and close all form objects. 13. Create a Select query, entitled qryMedia Titles, which displays the Title, Media, Grade, PubYear, and Cost columns from the Media table (in that order). Sort this query by ascending Grade and then, enter a criteria to display only those products that are DVDs published since 2006 (inclusive of 2006). Add a Totals row to the resulting datasheet that calculates the average Cost of the returned titles. (Note: Do not create a Totals query.) 14. Create a Parameter query, entitled qryEmployees, which includes all fields from the Employees table, except for the Employee ID, and then sort the listing first by department and then by surname. Use a criteria filter to display only those employees hired between January 1st, 2000 and December 31st, 2006. Create a parameter filter which prompts the user to enter a job title (e.g., Researcher) in order to filter the datasheet result. 15. Create a portrait, letter-sized report, entitled rptMedia by Topic, which displays a listing of the media products. Design and format the report using the wizard, as follows: • Select the Topic, Title, Media, PubYear, and Cost fields from the Media table. • Group the data by Topic and sort the contents of each grouping by Title. • Add a summary calculation to the report for the average cost of each Topic group. • Apply bold to the Topic titles (e.g., Business) and the Avg Cost calculations. • Edit the title of the report to read: Published Titles by Topic and make sure that all of the data and column titles are sized to display fully in Print Preview mode. Important: Close all the open windows (inside of the Access work area) and then exit (close) Access before uploading your database file into Moodle.