Chapter  Lab -Designing a database then querying it using joins

Purpose:

The purpose of this assignment is to continue to get you used to designing a database. Your database design will now take into account indexes, views, checks, cascading constraints, and different join types.

You do also have 2 debug assignments. I Strongly suggest doing these first.

Overall Description:

Congratulations! You have earned a contract to build the database system for the Cinemark at McCandless Crossing. Fortunately for you, your users know Exactly what data they want to keep track of and it's not crazy.Create a new workspace in APEX for this system (your debugs can also be run in this new workspace)

Cinemark wants to keep track of:

Customer data: First Name, Last Name, Address, Email, and total amount spent in the theatre in the last year (can be hardcoded into the database)

Movie data: Title, Date Released, Date Removed, Overall Rating (should only accept 1,2,3,4, or 5)

They also want to keep track of which customers have seen which movies.

It is your job to design the database and answer questions that the sales department has.

o The sales department wants to see which movies have had the most attendance. Generate a list of all movies, regardless of whether or not the movies have been seen at all, ordered by how many people have seen the movie DESC. Put this information in a view.

o They also want see which customers are their biggest movie attendees. Generate a customer list, ordered by the count of movies the customer has seen. Keep in mind some of your customers haven't seen any movies...they should still be on this list. Put this information into a view

o The sales department wants all of the data from the two queries above in a third, giant, query that gives them all of the information.

Put this information into a view

What to do (Detail):

- Think about the data you have to capture. Keep in mind that the list above is only the data that the customer wants to capture...you might need more than 2 tables (hint: you will likely need more than two tables because there will be a junction table involved)

- Draw out your design and map out the foreign keys, primary keys etc...

- Write a script that creates the database. If you decide to create cascading constraints. Specify in your document why you either did or did not decide to create cascading constraints. You are required to create at least 2 indexes, and a check on the overall rating of the movie.

- Insert data as specified above into the database. To test your design, you will be generating the test data. Data should include

o 20 customers

o 10 movies

o 15 customers who have seen two or more movies

o 2 customers who have seen one movie

o 3 customers who haven't seen any movies

o 2 movies who have not been seen by any customers

- Create the three views specified above for the sales department

o The sales department wants to see their most successful movies. Generate a list of all movies, regardless if they have had any customers or not. Put this information in a view

o They also want to know the data on customers regardless of what movies they, have or have not, been to. Put this information into a view

o The sales department wants all of the data from the two queries above in a third, giant, query that gives them all of the information. Put this information into a view

Deliverable

TWO scripts

1: A script that contains the creation of your database, constraints, indexes, and adds the data

2: A script that contains your view creations

A one-page document explaining why you made the choices you did for your database design and the data that you populated the database with. Explain your index choice. Also, explain your query design and why or why not you decided to cascade constraints and why your information for the sales department is in a view.Debug One

What the script is supposed to do: Creates a table that is supposed to keep track of Product information. The system is only supposed to accept "Tools" "Kitchen" "Sales" and "Other" as a type

Debug Two

What the script is supposed to do: Creates three tables for a pet store (includes a junction table. The marketing department would like to see a customer list and how much the customer has spent all time in the database. Make sure the list shows all customers, regardless of whether or not they have spent any money.

Attachment:- html.rar