25 May CREATE THE REMAINING TABLE OBJECT, AUTHORS, USING THE FIELDS SHOWN IN THE ER DIAGRAM.
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
Rachel Green Subject Area
Business Hourly Wage
$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.
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.
About Writedemy
We are a professional paper writing website. If you have searched a question and bumped into our website just know you are in the right place to get help in your coursework. We offer HIGH QUALITY & PLAGIARISM FREE Papers.
How It Works
To make an Order you only need to click on “Order Now” and we will direct you to our Order Page. Fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Are there Discounts?
All new clients are eligible for 20% off in their first Order. Our payment method is safe and secure.