Place Order

DBA 120 – Database Programming

 

FINAL PROJECT – Part 2

DBA 120 – Database Programming I

 

  1. Your end goal is to create a relational database using MYSQL containing three or more tables as well as other database objects (queries, forms, reports) that will be specified in Part 3 or Part 4. Using the data collected in part one, you should create one or more tables that will be the original table(s). This table should include the data that logically goes together (ex. Title, Year Released, Cost, Lead (Male and/or Female) Actor, Director, and Rating, or Media Type, Length, Close Caption, and Location, etc.)
  2. As with all the examples covered in our textbook you will need a field for your Primary Key such as Movie ID (or you could use the Bar Code as the Movie ID) and in my example I would need a foreign key to create a relationship between my tables Media ID, Director ID, or Actor ID.
  3. Be sure that each table has a relationship with at least one other table in the database. Use the normalization process discussed in your textbook to ensure each of your tables are in third normal form.
  4. Save the file as your lastnameEntertainment (example: hollowayEntertainment). This part of your Final Project is due 11:59 pm Monday, November 20, 2023 (will be accepted through 11:59 pm November 30, 2023), submit the final database in Moodle by clicking the Week 14 link; then clicking the link Final Project – Part 2 (100 points). Contact your instructor if you have any questions.

FINAL PROJECT – Part 3

DBA 120 – Database Programming I

 

  1. This is the last part of your Final Project; the deliverables (both must be submitted):
  • A .sql file (or text file) with all of your SQL commands.
  • A word file (.docx file) with a MySQL workbench screenshot for each task. This includes your code and the result. You must organize them in the order of the tasks below and include the task number for each.
  1. If necessary, modify your primary table’s structure so the Author/Director’s name is separated into two columns (fields) First Name and Last Name.
  2. Write a query:
  • to display all titles with a release date prior to the year 2010 from your primary table; list the following three columns (fields) Title, Author/Director, and Year.
  • to display the total length (in pages/minutes) of all titles in your primary table; then display the average length (in minutes/pages) of all titles in your primary table.
  • to display the recently released titles (years 22-23) from your primary table; list the Year, Author/Director, Length, and Binding/Media Type.
  1. Create a table called Ratings (if not created already) consisting of the following fields (columns) RatingID, Rating, Comment/Reason. Create an inner join between your primary table and the Ratings table.
  2. Write a subquery to display all titles with a rating of R (or the equivalent); list the title, media type/binding, length, rating, and comment/reason.
  3. Create a view for all titles with a horror genre; list the title, binding/media type, and length.
  4. Create an index for your primary table.
  5. Use concatenation to display the author/directors last name, a comma, a space, first name, and the title; display the results in A-Z order by name.
  6. Create a trigger to display all fields for any record added to the table released in 2024.
  7. Submit the Final Project – Part 3; lastnameEntertainment.sql and lastnameEntertainment.docx (example: hollowayEntertainment.sql and hollowayEntertainment.docx). This part of your Final Project is due 11:59 pm Friday, December 1, 2023 (will be accepted through 11:59 pm December 11, 2023), submit the final documents in Moodle by clicking the Week 15 link; then clicking the link Final Project – Part 3 (100 points). Contact your instructor if you have any questions.