Reference no: EM131353304
Assignment
The goal of this term project iteration is to address in the Term Project. As you begin writing the SQL for these use-cases, you may realize that your design must be altered or enhanced in order to support the data and relationships needed.
1. Make any necessary enhancements and corrections to your business rules, and provide your complete list of business rules here.
A customer must have an account. An account may have one or more customers.
An account must be assigned to a membership program. A membership program may have one or more accounts.
A customer must pay a monthly payment. A monthly payment must be paid by a customer.
An account may have more than one bill. A bill must belong to one account.
An account must have a rental history. A rental history must have an account.
A customer must save zero or more movies to their queue. A queue may have one or more movies saved by a customer.
A customer may rent zero or more movies, and a movie can be rented by many customers.
A customer may rate one or more rented movies. A rented movie may have one or more customer rating.
A movie may have more than one starring actor, and an actor may be in one or more movies.
A movie must have a genres, and a genre must belong to one or more movies.
2. Make any necessary enhancements and corrections to your conceptual ERD, and insert the updated conceptual ERD here.
3. Make any necessary enhancements and corrections to your logical ERD, and insert the updated logical ERD here.
4. From your logical ERD, identify the tables and data needed to address use cases below.. You will first need to write the SQL statements that define the tables and insert data into the tables. Write the SQL that addresses these use cases, and paste the queries and screenshots demonstrating their execution.
A customer requests the titles of all movies that are directed by "George Lucas" or by "Rich Christiano". Write a single query that retrieves this information.
Management requests the names of all currently active customers, as well as the name of the current plan in which each of these customers is enrolled. Write a single query that retrieves this information.
A customer wants to add a movie to their queue so that the newly added movie will be the next movie they receive. Develop a parameterized stored procedure that accomplishes this, then invoke the stored procedure for a customer of your choosing.
4) A customer requests the titles of all the DVD's that he or she has not rented. Write a single query that retrieves this information for a customer of your choosing.
5) A customer cancels their membership and does not return a rented DVD, necessitating that a $25 DVD replacement fee be charged to their account. Develop a parameterized stored procedure that accomplishes this, then invoke the stored procedure for a customer of your choosing.
6) A customer enrolled in the two-at-a-time plan cancels their membership. When a customer cancels their membership, they become inactive, but their DVD queue and rental history remains in the database, in the event they return as a customer. Develop a parameterized stored procedure that accomplishes this, then invoke the stored procedure for a customer of your choosing.
7) Management requests the names of all movies that are currently sold out. A movie is sold out if all copies of the movie are currently rented and not yet returned. Write a single query that retrieves this information for management.
8) Management requests identification of the plan with the most customer enrollees, and for that plan, the name, number of DVDs allowed at one time, and the number of customer enrollees. Write a single query that retrieves this information for management.
9) Management requests the names of all customers, and for each customer, the titles of the movies that they rented multiple times. For each title, management would like to see the number of times it was rented by the customer, only including titles that the customer rented more than once. If a customer has no rentals, or did not rent any movies multiple times, management does not want to see them in the list. Write a single query that retrieves this information for management.
10) Management requests the titles of all movies, and for each movie, the number of different customers that rented the movie. They would like the list to be ordered from the highest number of different rentals to the lowest number. Multiple rentals of the same movie by the same customer only count as one unique rental. Management is interested in the number of different customers that rented the movie, but not whether the same customer rented the same movie more than once. Write a single query that retrieves this information for management.
|
Compute the forecasts for each month
: Compute the forecasts for each month using exponential smoothing, with an initial forecast for January of $1.80. Use a = .1, then a = .3, and finally a = .5. Using MAD, which a is the best
|
|
Derive equation of motion governing the angle of rotation
: The homogeneous semicylinder rolls without slipping. Derive the equation of motion governing the angle of rotation ψ.
|
|
Path of a sperm into the female body to an oocyte
: Trace the path of a sperm out of the male body and the path of a sperm into the female body to an oocyte.
|
|
Human testis produce testosterone
: State which cells in a human testis produce testosterone and its function(s) and state which hormones are produced by a human ovary, and their functions.
|
|
Write single query that retrieves information for management
: If a customer has no rentals, or did not rent any movies multiple times, management does not want to see them in the list. Write a single query that retrieves this information for management.
|
|
Obtain the irr of the project
: Consider a project with the following cash flows -100, 230 and -134 at time 0, 1, and 2, respectively. Obtain the PI (profitability Index) of the project if the cost of capital is 10% 2. Consider a project with the following cash flows -100, 230, and..
|
|
What do you predict he will say about indian removal
: What do you already know about President Jackson s feelings about Indian Removal? What do you predict he will say in this speech?Why does Jackson think the United States was better in 1830 than in 1609? Why does Jackson think that the Cherokee will ..
|
|
Decision variables-constraints and objective function
: Its Christmas and your father gives you $1,000 to invest in stocks and loans. For each dollar invested in stocks yields $0.1 profit, and each dollar invested in a loan yields $0.15 profit. Determine how you can maximize the profit earned on your inve..
|
|
Compare branding and communication strategies
: Conduct a Strengths Weaknesses Opportunities Threats (SWOT) analysis associated with the current marketing communication, identity, and brand position of Plaza Home Health Services.
|