Database Management

1.Open Access and create a database called, "Movie Favorites." Create the Film Favorites table in Design view using the structure shown below: Structure of Film Favorites table Field Name VideolID MovieTitle YrRlsd Data Type Field Size Primary KeyDescription Short Text 2 Short Text 25 Short Text 4 Short Text15 Primary Key Name of Movie Year Movie Released Lead Actor's First FirstiName ame ame Short Text25 Lead Actor's Last Name PurchasePrice Price of Movie Save the Film Favorites table Open the Film Favorites table in datasheet view Enter the following information in the Film Favorites Table under the appropriate fields Yr Rlsd First Name 2016your first nameyour last name 2000 1999 1993 1998 1998 1999 1999 VideolD Movie Title Last Name Purchase Price 0 Access 2016 02 Harry Potter 03 Legally Blonde 04 ET 05 Castaway 06 Scream 07 Leaving Normal 08 Titanic 09 Independence Day 1999 10 11 12 Radcliffe Witherspoon Barrymore S29.95 S20.00 se Drew Campbell Tilly Winslet S14.99 S10.00 S32.95 S35.99 S29.95 Neve Will Glenn Mulan Jurassic Park III Men In Black Tarzan 1998 2001 2000 1999 2000 Close O' Neill S29.99 S29.99 S19.99 S49.99 lommy Lee 1e Driver 14 Moulin Rouge cole Kidman Run spell check on the table 7.Sort the table in ascending order by Title of the movie Adjust the columns to best fit the data. Close and save the Film Favorites table. 9 10. Create the Orders table in Design view using the structure shown below: Structure of Orders table Field Name Data Short Text Short Text CustomerFirstName Short Text CustomerLastName Short Text Short Text Field Primarv Key Description Size CustID Primary Key Movie ID First Name of Customer es VideolD 15 20 Last Name of Customer MovieTitle 25 Name of Movie PurchasePrice Currency Number Price of Movie Number of items ordered Quantity 11.Save the Orders table 12. Open the Orders table in datasheet view Enter the following information in the Orders Table under the appropriate fields Purchase PriceQty S65 29.95 $32.95 S9.99 Customer Customer CustID VideolID First Name Last Name Movie Title 01 02 John 07 Mary 11 Belle yourfirst name your last nameAccess 2016 21 25 27 Smith Jackson Stevens Harry Potter Leaving Norma Jurassic Park III l 13. Run spell check on the table 14. Sort the table in ascending order by Title of the movie 15.Adjust the columns to best fit the data. 16. Close and save the Orders table 17. Define a one-to-many relationship between the Film Favorites table and the Orders table using the VideoID field. Select the Enforce Referential Integrity option and the Cascade Update Related Fields option for the relationship Save the one-to-many relationship 18. 19. Using the Film Favorites table, create a query for all movies released after 1997 and costing>S20.00. Show the Movie Title, Year Released, and Purchase Price 20.Adjust the columns to best fit the data. 21.Save this query as Movies Released After 1997 22. Close the query 23. Modify the existing Movies Released After 1997 query for movies released before 1997 24. 25. d costing $20.00 Use Save Object As to save this query as Movies Released Before 1997 Close the query 26. In the Film Favorites table, create a query to find all records in which the Movie Title field starts with the letter M. Show all fields and sort in ascending order by Last Name Run and save the query as MMovies. (Wildcard query) Close the query 27. Create a parameter query LastName field value that the user specifies. Show the MovieTitle, FirstName, and LastName fields. Save the query as Parameter and then close the query. Open the Parameter query and insert the last name, Smith. Run the query and close without saving. from the Film Favorites table that selects the records for a 28. Create a query from the Film Favorites table that shows all records that displays only the MovieTitle, LastName, FirstName, and Purchase Price fields. Sort in descending order by Purchase Price. Use the Top Values property to select the top 25% of the records. Run the querv, and save it as Top Values. Close the querv. 29. Using both the Film Favorites and Orders table, create a query that displays the VideoID MovieTitle from the Film Favorites table and the CustomerFirstName and CustomerLastName from the Orders table. Sort in ascending order by MovieTitle. Run and save the query as JoinedQuery. Close the query 30 Define a validation rule for the Quantity field in the Orders table that accepts quantities that are values between 1 and 20. Enter the message: Value must be between 1 and 20 inclusive so it appears if a user enters an invalid Quantity field value. Save the Orders table 31. In the Orders table, add a Long Text field named CustomerComments as the last field in the Orders table. Set the Caption property to Customer Comments and the Text Format property to Rich Text. Save the table. View the Orders table in Datasheet view and add the following comment for customer John Smith: Extremely satisfied with Customer Support. Widen the Customer Comments column. Save and close the table 32. Using the Report Wizard, create a custom report based on the JoinedQuery. On the report, display the MovieTitle, CustomerFirstName and CustomerLastName. Accept the defaults. Add the title, Movie Customers. Close the Report. 33. Use the Form tool to create a form for the Orders table 34. Name the form "Orders." 35.Close the Orders Form. 36. Use the Report tool to create a report based on the Film Favorites table. 37.Name the report "Film Favorites." 38. Close the Film Favorites Report. 39. Exit Access and submit the Movie Favorites database through the Assignment Dropbox in Blackboard. **NOTE: In this database, you should have the following objects:** Tables: Film Favorites Orders Queries: Movies Released After 1997 Movies Released Before 1997 MMovies Parameter Top Values JoinedQuery Form: Orders Reports: Film Favorites Movie Customers 1.Open Access and create a database called, "Movie Favorites." Create the Film Favorites table in Design view using the structure shown below: Structure of Film Favorites table Field Name VideolID MovieTitle YrRlsd Data Type Field Size Primary KeyDescription Short Text 2 Short Text 25 Short Text 4 Short Text15 Primary Key Name of Movie Year Movie Released Lead Actor's First FirstiName ame ame Short Text25 Lead Actor's Last Name PurchasePrice Price of Movie Save the Film Favorites table Open the Film Favorites table in datasheet view Enter the following information in the Film Favorites Table under the appropriate fields Yr Rlsd First Name 2016your first nameyour last name 2000 1999 1993 1998 1998 1999 1999 VideolD Movie Title Last Name Purchase Price 0 Access 2016 02 Harry Potter 03 Legally Blonde 04 ET 05 Castaway 06 Scream 07 Leaving Normal 08 Titanic 09 Independence Day 1999 10 11 12 Radcliffe Witherspoon Barrymore S29.95 S20.00 se Drew Campbell Tilly Winslet S14.99 S10.00 S32.95 S35.99 S29.95 Neve Will Glenn Mulan Jurassic Park III Men In Black Tarzan 1998 2001 2000 1999 2000 Close O' Neill S29.99 S29.99 S19.99 S49.99 lommy Lee 1e Driver 14 Moulin Rouge cole Kidman Run spell check on the table 7.Sort the table in ascending order by Title of the movie Adjust the columns to best fit the data. Close and save the Film Favorites table. 9 10. Create the Orders table in Design view using the structure shown below: Structure of Orders table Field Name Data Short Text Short Text CustomerFirstName Short Text CustomerLastName Short Text Short Text Field Primarv Key Description Size CustID Primary Key Movie ID First Name of Customer es VideolD 15 20 Last Name of Customer MovieTitle 25 Name of Movie PurchasePrice Currency Number Price of Movie Number of items ordered Quantity 11.Save the Orders table 12. Open the Orders table in datasheet view Enter the following information in the Orders Table under the appropriate fields Purchase PriceQty S65 29.95 $32.95 S9.99 Customer Customer CustID VideolID First Name Last Name Movie Title 01 02 John 07 Mary 11 Belle yourfirst name your last nameAccess 2016 21 25 27 Smith Jackson Stevens Harry Potter Leaving Norma Jurassic Park III l 13. Run spell check on the table 14. Sort the table in ascending order by Title of the movie 15.Adjust the columns to best fit the data. 16. Close and save the Orders table 17. Define a one-to-many relationship between the Film Favorites table and the Orders table using the VideoID field. Select the Enforce Referential Integrity option and the Cascade Update Related Fields option for the relationship Save the one-to-many relationship 18. 19. Using the Film Favorites table, create a query for all movies released after 1997 and costing>S20.00. Show the Movie Title, Year Released, and Purchase Price 20.Adjust the columns to best fit the data. 21.Save this query as Movies Released After 1997 22. Close the query 23. Modify the existing Movies Released After 1997 query for movies released before 1997 24. 25. d costing $20.00 Use Save Object As to save this query as Movies Released Before 1997 Close the query 26. In the Film Favorites table, create a query to find all records in which the Movie Title field starts with the letter M. Show all fields and sort in ascending order by Last Name Run and save the query as MMovies. (Wildcard query) Close the query 27. Create a parameter query LastName field value that the user specifies. Show the MovieTitle, FirstName, and LastName fields. Save the query as Parameter and then close the query. Open the Parameter query and insert the last name, Smith. Run the query and close without saving. from the Film Favorites table that selects the records for a 28. Create a query from the Film Favorites table that shows all records that displays only the MovieTitle, LastName, FirstName, and Purchase Price fields. Sort in descending order by Purchase Price. Use the Top Values property to select the top 25% of the records. Run the querv, and save it as Top Values. Close the querv. 29. Using both the Film Favorites and Orders table, create a query that displays the VideoID MovieTitle from the Film Favorites table and the CustomerFirstName and CustomerLastName from the Orders table. Sort in ascending order by MovieTitle. Run and save the query as JoinedQuery. Close the query 30 Define a validation rule for the Quantity field in the Orders table that accepts quantities that are values between 1 and 20. Enter the message: Value must be between 1 and 20 inclusive so it appears if a user enters an invalid Quantity field value. Save the Orders table 31. In the Orders table, add a Long Text field named CustomerComments as the last field in the Orders table. Set the Caption property to Customer Comments and the Text Format property to Rich Text. Save the table. View the Orders table in Datasheet view and add the following comment for customer John Smith: Extremely satisfied with Customer Support. Widen the Customer Comments column. Save and close the table 32. Using the Report Wizard, create a custom report based on the JoinedQuery. On the report, display the MovieTitle, CustomerFirstName and CustomerLastName. Accept the defaults. Add the title, Movie Customers. Close the Report. 33. Use the Form tool to create a form for the Orders table 34. Name the form "Orders." 35.Close the Orders Form. 36. Use the Report tool to create a report based on the Film Favorites table. 37.Name the report "Film Favorites." 38. Close the Film Favorites Report. 39. Exit Access and submit the Movie Favorites database through the Assignment Dropbox in Blackboard. **NOTE: In this database, you should have the following objects:** Tables: Film Favorites Orders Queries: Movies Released After 1997 Movies Released Before 1997 MMovies Parameter Top Values JoinedQuery Form: Orders Reports: Film Favorites Movie Customers