Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: THEend8_
INFO20003 Semester 1, 2021
Assignment 1: ER Modelling
Due: 3rd April 2021 10:00am
Netflix is an American content platform and production company headquartered in Los Gatos,
California with approximately 200 million users.
Netflix not only hosts the entertainment content but also has entered in content-production industry
by producing blockbusters such as House of Cards, Stranger Things, and The Crown. Netflix is a
data-driven company relying heavily on data to make informed decisions about content creation and
delivery.
Your team has been asked to design a MySQL database to store information about the viewing
history of users, and the details of the content they have watched or plan to watch. With this data,
Netflix can make better decisions and ultimately make users happier with their service. The following
specifications have been provided to your team to assist in your design.
Subscription
Netflix offers a variety of plans to meet a user’s needs. Users must first subscribe to a plan to create
a Netflix user account, and gain access to the service. One user can purchase a subscription that
can be shared with friends and family, each of whom will receive their own user account. This will
result in multiple user accounts associated with the same subscription. Separate user accounts help
Netflix to effectively divide the interest of the users: each will receive individualized suggestions for
shows and have their own list of favourite shows (distinct from the other users sharing the same
subscription). Each user has a nickname, and a profile avatar photo.
Subscriptions can be to three different plan types currently with different monthly rates for each of
these: “basic”, “standard” or “premium”, but more plans might be added in future. Some plans can
stream in HD, and others cannot. The plan a user chooses will also determine the number of devices
that they can watch Netflix on at the same time. E.g., for “basic”, “standard” and “premium” plan
subscriptions, 1, 2 and 4 users can watch simultaneously, respectively (but again, these values
might change in future). For each subscription, the number of users currently watching Netflix should
be tracked, so that if more users try to simultaneously watch than are allowed an error can be shown.
For each subscription, Netflix keeps track of the purchaser’s contact details such as their first name,
last name, address, up to two phone numbers and a unique email address tied to the subscription.
The date of purchase for a subscription should also be stored. You must store the country where
the user who purchased the subscription is residing and will be using Netflix, as the Netflix library
differs by country. Since it is a subscription, payment details also need to be stored for the recurring
deduction. Netflix wants to record the account name (e.g., ‘Farah Khan’), 16-digit credit card number
(e.g., ‘0198 2345 3435 8822’), and the expiry month and year (e.g., 01, 2024). These details can be
updated at any time, but the history of the changes does not need to be kept. The date at which the
next payment is due and the date of payment of all past payments associated with that subscription
and their amount need to be stored.
When a subscription is cancelled, we need to record the date that the subscription ended. User
accounts associated with this subscription are not removed, and the subscription details are not
removed from the database. When a subscription is cancelled the credit card details are deleted
permanently, but the remaining information for the subscription stays. In this system, storing a user
accounts’ viewing history from past subscriptions is not necessary (see the ‘user profiles’ section for
more on user viewing histories).
If a user later decides that they again want access to Netflix after their subscription was cancelled
(using the same email address they used for the past subscription), a new subscription is created
with a new purchase date and purchaser details, and the users from the old subscription are
transferred to the new subscription. If a subscription is upgraded or downgraded, the existing
subscription is effectively ‘cancelled’, and a new subscription is created in a similar manner.
Shows
Netflix hosts two types of Shows: Films and Series. Along with the title of the show, Netflix may also
store few keywords such as witty, humorous, inspiring, teen drama, Netflix original, etc for each
show. There is also a more standard maturity rating given to each show: Suitable for General
Audiences (G), Parental Guidance suggested (PG), Recommended for mature Audiences (M), Not
suitable for ages 14 and under (MA 15+), and Restricted to ages 18 and over (R 18+). These ratings
and keywords help users make informed choices about the content to watch. The maturity rating
level can also be set up for a user profile. Profiles with a set maturity level will only show titles that
do not exceed the selected maturity rating level.
For films, the date of release, and name of the film studio need to be stored. For series, the creators
name (assume only one), date of first episode’s release and date of final episode’s release (if the
series is finished) need to be accessible. Series have one or more seasons, each of which have one
or more episodes. A season may contain a name (which may be empty, in which case it is displayed
to users as “season X”). An episode must have the date of release stored, and a title (which may
similarly be empty).
This database will not store the actual media objects, but rather a link to the media objects. Each
film or episode is linked to one media object. For each media object, store the URL link, duration in
minutes, and size in GB.
Netflix may decommission older shows. In such cases, the date that the show is available until is
stored and displayed to the users.