Database for the "Online Movies and download " Pty Ltd

Objectives of the unit: Demonstrate evidence of the knowledge set and skill set required by the database designer

Assignment Tasks: Read the following case study scenario and complete the associated tasks.

Scenario:

Industry background

There are a large number of Movies Online operators where customers can stream thousands of hours of movies and TV shows on laptop, tablet or mobile, or big screen via Google Chromecast.

Most "streaming" working on monthly payment system with no exit fees. The "streaming" market is divided up into a variety of sectors, by childrens age range, by customers age, and categories. For example kids movies are kept in separate sections so they can't access other genres. Many of these streaming apps and websites are highly developed to be attractive to customers and easy to access, select and continue watching. Also providing the personalized list of movies for each customer. Other streaming services providers allow user to rent or buy movie for a length of time and small fee. All we need is to ensure that our design is not breakable and user can easy manipulate using search, back, forward, play, pause options. Only registered users can use this service.

"Online Movies "

The "Online Movies " brand was established in 2012 by group of enthusiasts. The company initially rent out a small range of DVD and provided local streaming services. These streaming services proved to be very popular and company got funding from a group of stakeholders from Netblix. By 2014, within 2 years of starting "Online Movies ", the founders were encouraged to see "Online Movies " streaming orders rose by 200%. By this stage the company had grown considerably, and had annual sales of almost $2 million. "Online Movies " has seen its sales revenue grow by this Q2 (2015/2016) fiscal year by 16% in the year ended 31 October 2015 and predictions of 18% in the year to 31 December 2015.

Movies On-line's product range and serviced age groups Our Company currently has a relatively big range of 150 movies aimed at only 3 age kids groups. These are the pre-school age group of 3 to 6 year olds and the next age group of 6 to 12 year olds and 12-18. There is also adult group from 18 years. Movies Online products are primarily streaming services. Online Movies currently adding around 5 totally new movies each week. It also enhances certain aspects of some of its other products to refresh their appearance and features. It also has a range of movies that rent consistently well and have not changed substantially for a few years. Online Movies rent out movies are between $2 and $8. These movies are the latest Hollywood movies.

Streaming of movies

Movies Online has its own in-house team of designers who are involved in preparing for streaming movies that are unique, innovative and latest in production. The classification of new movies (and others) is split into four stages. Firstly, the design stage involves the design team downloading a new movie. In the second stage the deployment team will test and deploy new movie for streaming, then in stage three the sales and marketing team promote the product via various media outlets. The final stage is where the management team determines the price of movie to rent or to release in section for free to watch to members only.

Movies Online key requirements

Traditionally all communication between team members is via email on locally secured networks within Movies Online Headquarters. The design team use a file sharing systems and software development and collaboration tools which have proved successful. Recently the research and development teams working on a new product was hindered by the lack of coordination between the sales and marketing teams during the deploying of a new movie. As a result the marketing team were unable to leverage the full potential of the new movie and the sales campaign was significantly compromised with a loss in revenue. The design team has requested a new approach to the collaboration system which coordinates all the teams and recommended a single database which will be more flexibility and increase sharing of data and information.

The areas of concern are:

Inventory control

Sales and marketing updates

Promotional campaigns

Sales statistics

Development schedules

Download selected movie

The initial analysis has suggested the following entities: Inventory, Sales, Rent Movies, Members, Suppliers, Streaming, Customers and Marketing. The Movies Online Company requires a database that will collect and maintain data to assist in the management of the whole stream/develop/manufacture/sales systems.

Note >>>This is not an exhaustive list and there may be duplicates, it is a suggestion for a possible starting point towards your solution. You should add/edit and delete entities to suit your solution. <<<<

Assessment Tasks

In order to secure this project you are required to prepare a report that will be used to demonstrate a database solution. Using the information from the Online Movies scenario complete the following tasks and create a database prototype for demonstration to the management of Movies Online.

Task 1:

Describe the requirements and list the major entities and their associated attributes (1-st,2nd and 3nf).

Task 2:

Prepare a conceptual design. This section should provide an ER diagram indicating one-to-one, one-to-many or many-to-many relationships.

Task 3:

Prepare a logical design in 3rd Normal Form indicating all required tables with primary and composite key. Ensure that all attributes have a data type and size.

Task 4:

Create a database in SQL and write SQL statements for the creation of each table and relationships based on the 3rd Normal Form (Task 3). Populate the tables with sample data (several records per table).

Task 5:

Describe the implementation of a possible User Interface and how the database that you have designed works.

Task 6:

Create SQL code for the following queries:

a. Sales reports;

i. by age group,

ii. by gender

b. Inventory reports

i. by genre (category),

ii. by price range, All your tables should be inserted with at least 4 rows of data inserted before any queries were made

iii. most profitable movies (highest to lowest),

iv. total sales by month and year,

v. retail sales (most successful)

c. Streaming reports:

i. by length

ii. by time of day

d. Downloading reports

i. By length

ii. By time of day

e) Create StoredProcedures:

1. GetTotalSales that displays the salesperson name as well as their total sales.

2. Create a stored procedure called GetTotalSalesGreaterThanFive that displays the online movie name as well as their total movie download for all movie producers who have had more than one sale this year.

3. Create Stored procedure called FindtheMovieProducer which accepts @ProducerName VARCHAR parameter and filters results by movie category

4. Create Stored procedure called FindtheMovieForKids which accepts @ProducerName VARCHAR parameter and filters results by kids age.

Marking criteria:

Task 1 1^st2nd and 3nf [10 marks]

Task 2 ER Diagram [10 marks]

Task 3 Table Design in 3rd NF [10 marks]

Task 4 DB and Table SQL [40 marks]

Task 5 UI Design [10 marks]

Task 6 SQL reports [20 marks]

Thank you.