User Account

All Pages

01a_ImportCasting.xlsx, Database Management System

Assignment Help:
01a_Broadway_LastFirst_yyyy-mm-dd
Capstone Exercises

You work as a production assistant in the office of the Green Man Theatre. One of your duties is to maintain the company database. You will create and edit tables, queries, forms, and reports as well as maintain and back up the database. A database has already been created, so you decide to explore it.
Navigate among the Objects in an Access Database

You will begin by opening the Broadway database and examining the tables and other objects. You will also make one change in the Actors table.

Open the database 01a_Broadway and save the database as 01a_Broadway_LastFirst.

Open each table and examine the data.

Close all the tables except the Actors table.

Sort the Actors table by Last Name in ascending order.

Find Stacie Henderson’s record.

Replace the last name Henderson with Tandbell.

Remove the Sort.

Save and close the Actors table.

Open the Actors from NY query.

Switch to Design view. Modify the State criterion so the query displays all the actors who are not from NY.

Run and save the query (use Save Object As) and rename as Actors Not from NY. Close the query.

Create a Casting Table

The database contains tables for theaters, shows, and actors. However, there is no efficient way to keep track of which actors performed in which shows. You will create a Casting table that will contain the show, the actor, and the weekly pay.

Create a table using Table Design view.

Add field names and data types as listed below.
Field Name Data Type
ShowID Number
ActorID Number
WeeklyPay Currency

Set ShowID and the ActorID together as the primary key.

Save the table as Casting and close the table.

Import Excel Data into the Casting Table

The theater has been storing casting data in an Excel spreadsheet. You will import this spreadsheet data directly into the Casting table.

Append the data in Sheet1 of the 01a_ImportCasting Excel file into the Casting table. If an error message displays, click Yes.

Open the Casting table and examine the data.

Verify the primary key is set correctly by changing the last record’s ActorID from 12 to 9. Click the record directly above and Access will display the message The changes you requested to the table were not successful because they would create duplicate values… Undo the change to ActorID.

Close the table.

Establish Table Relationships

Table relationships help users extract data more efficiently. Referential integrity will help eliminate common data entry mistakes. You will set two new relationships and enforce referential integrity.

Open the Relationships window and add the Casting and Actors tables.

Set a relationship between the Shows and Casting tables and set Enforce Referential Integrity.

Set a relationship between the Actors and Casting tables and set Enforce Referential Integrity.

Make sure all table data and join lines are visible.

Save and close the Relationships window.

Create a Casting Query

You need to post the casting roll for each new show. You will create a casting roll for the latest show using a query.

Create a query based on the Casting table. Include only the ShowID and ActorID fields.

Add criteria to display all the actors who performed in ShowID 3, which is Jersey Boys.

Run the query.

Save the query as Jersey Boys Cast.

Close the query.

Copy and Modify an Existing Query

The previous query does not show the name of the show or the name of the actor. You will copy the previous query and revise it to add the missing data.

Copy the Jersey Boys Cast query and paste it using the name Jersey Boys Cast Enhanced. Open the Jersey Boys Cast Enhanced query in Design view.

Add the Actors and Shows tables into the new query.

Modify the query so that the ShowName displays as the first column and the ShowID field does not display in the query results.

Replace the ActorID field with the LastName and FirstName fields.

Run the query. Verify the accuracy of the data.

Save and close the query.

Use a Calculated Field to Forecast Daily Revenue

The producers asked you for the daily estimated revenue for each show, based on a sold-out house. You will create a query and use a calculated field to provide this information.

Create a new query based on the Shows and Theaters tables.

Include the ShowName, TheaterName, SeatingCapacity, and TicketPrice fields.

Save the query as Estimated Daily Revenue.

Use the Expression Builder to create the DailyRevenue calculated field in the fifth column. Multiply the field SeatingCapacity by TicketPrice to create the calculated field.

Close the Expression Builder, run the query, and then manually verify whether the calculated field values are correct.

Save and close the query.

Use Functions in a Query

The producers ask you to modify and improve the Estimated Daily Revenue query. You will copy the existing query, rename it, and then add the required changes. The new query will require a Totals query so you can look up the total actors’ salaries for each show.

Create a Totals query from the Casting table that can be used to look up the total actors’ salaries for each show. Add the ShowID and WeeklyPay fields. Use the Total row to group by ShowID and sum the WeeklyPay. Run the query and save the query as Show Salaries. Close the query.

Copy the Estimated Daily Revenue query and paste it using the name Estimated Daily Revenue and Expenses.

Modify the Estimated Daily Revenue and Expenses query to add a Daily Rent calculated field to the sixth column using DailyRent: [WeekRentalFee]/7. Change the format property to Currency. Run the query and verify whether the calculated field is working.

Switch to Design view and add a Daily Salaries calculated field to the seventh column using the following expression:

DailySalaries: DLookUp(“SumOfWeeklyPay”,“Show Salaries”,“ShowID= ” & [ShowID])/7

The DLookup() function directs Access to look up a value in the Show Salaries query you created in Step a. Change the format property to Currency.

Run the query and manually verify whether the calculated field values are correct. Save and close the query.

Copy the Estimated Daily Revenue and Expenses query and paste it using the name ­Estimated Net Income. To the new query, add another calculated field for ­DailyNet. Daily Net is defined as Revenue minus Expenses (DailyRent and DailySalaries). Add another calculated field for TotalNet. Total Net is defined as Daily Net times the ­number of days the show runs. Total Net requires the DateDiff() function, as shown: TotalNet: [DailyNet]*DateDiff(“d”,[StartDate],[EndDate]).

Run the query and verify the calculated fields are correct.

Save and close the query.

Create a Form Using a Form Tool

You need to create a form to make it easier to add new actors. The same form will function as a maintenance screen for existing actors.

Create a form based on the Actors table using the Form tool.

Resize the width of the text box controls so they are about half the original size.

Remove the subform.

Change the title label to Enter and Edit Actor Data.

Save the form as Main Actors.

Add yourself as an actor using the new form. Include your real name and make up the rest of the data.

Close the form.

Create a Report Using the Report Wizard

You need to create a report that lists all the actors grouped by show. The report should also show the weekly pay for each actor and a sum for each show, as well as display a sum for the weekly pay overall.

Create a simple query that will be used to create a report. Include the Casting, Shows, and Actors tables. Add the necessary fields based on the description above.

Name the query Casting Summary. Close the query.

Create a report based on the Casting Summary query using the Report Wizard. Answer the Report Wizard’s questions using the description above. Sort by Last Name and name the report Casting by Show.

Switch to Layout view and delete the summary for the ShowName box displayed in the ShowName footer. Preview the report.

Switch to Design view and move the Sum label and Sum box up to the top of the ShowName footer. Reduce the height of the ShowName footer by one-half. Increase the width of the Grand total field to display the value including the $ sign. Preview the report.

Save the report. Close the report.

Add yourself as ActorID 20 to the Jersey Boys show with a weekly pay of 1250 in the Casting table and preview the report again.

Close the report.

Back Up the Database

An Access database needs to be maintained regularly. As a part of your daily routine, you will back up the database.

Back up the database in the location where you save your student files. When naming the file, include today’s date.

Save and close all open database objects. Based on your instructor’s directions, submit 01a_Broadway_LastFirst.

Related Discussions:- 01a_ImportCasting.xlsx

Concatenation operator-data manipulation language, Concatenation operator ...

Concatenation operator Example : Printing name and job as one string as column name employees: SELECT ENAME||JOB "EMPLOYEES" FROM EMP;

gaps in sequence values can happen when-sequences, Gaps in sequence values...

Gaps in sequence values can happen when: -     A rollback happens that is when a statement changes are not accepted. -     When the system crashes -     When a sequence i

Schema, difference between internal schema and external schema in DBMS

difference between internal schema and external schema in DBMS

Explain the aggregation implies concurrency, Explain the Aggregation implie...

Explain the Aggregation implies concurrency Concurrency actually is: This in objects can be identified by the way they change state. Present objects can change state independen

Entity relationship diagram, The Enhanced Entity Relationship Diagram (ERD)...

The Enhanced Entity Relationship Diagram (ERD) produced with a drawing or case tool. Your EERD must use a ''standard'' notation style such as Crows Foot or Chen. Please note that t

Map an E-R diagram to a relational schema, I need to Map an E-R diagram to ...

I need to Map an E-R diagram to a relational schema

Using column aliases-data manipulation language, Using Column aliases E...

Using Column aliases Example: To print column names as NAME and ANNUAL SALARY SELECT ENAME "NAME", SAL*12 "ANNUAL SALARY" FROM EMP;

Eliminate repeating groups, Eliminate Repeating Groups The problem is, ...

Eliminate Repeating Groups The problem is, "Find out the list of employees, who knows DB2". For this problem we require to perform an awkward scan of the list looking for re

What is equi join, What is equi join? Equi Join - It generates all th...

What is equi join? Equi Join - It generates all the combinations of tuples from two relations which satisfy a join condition along with only equality comparison (=).

Gui interfaces, Draw the database using the ER approach and then make the t...

Draw the database using the ER approach and then make the tables accordingly. Populate the tables so that every table have at least 10 tuples. Then using Java and SQL, execute the

Write Your Message!

Captcha
Order Assignment

Featured Services

Order Now

Popular Subjects

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

Submit Assignment

Academics

Major Subjects

Majors

Get In Touch

whatsapp: +91-977-207-8620

Phone: +91-977-207-8620

Email: [email protected]

TERMS & POLICIES

HELP & SUPPORT

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd