DUMMY
Database System Assessment Guide Assessment 1 - Case Study This project is the main assessment of this subject. It is worth 20% of your total assessment. You are required to design a database system for BitStream, a media services provider that offers subscription-based TV streaming service. All work is to be completed individually. If parts or all of the source code you submit is not your own work, you will receive ZERO. Scenario You are contracted by BitStream to develop a management system for the TV streaming services the company provides. Your first task is to design a database structure for this System. Specifically, the database would need to be able to record the following things: ● Customers information and account details. This includes customer’s subscription full details and payment details. Customer’s address needs to be recorded as well. (Hint: you need more than 2 tables) ● Movies: information about every movie available for streaming must be recorded. A Movie has many actors and may have more than one director. A movie may have more than one genre, for example, it can have Drama and Adventure Genres. Movie information needed to be displayed in the system includes Title, description, Release year, Language, Genre, classification, main actors, director, and reviews (hint: reviews should be a separate entity). Record information if the movie is based on a real story or a book. ● TV Series: information about every series available for streaming must be recorded. Series has many episodes and may have more than one season. A TV series has many actors and may have more than one director; information needed to be displayed in the system includes Title, description, Release year, Language, Genre, classification, main actors, director, No. of seasons and reviews (hint: reviews should be a separate entity). Record information if the movie is based on a real story or a book. ● TV Series Season: information about the series seasons; this table should be related to TV series and episodes tables. ● Episode: Details about each episode for each season. ● Actors: information about the actors, each actor may have acted in Movies or TV series or both. Information about the actor that needed to be displayed in the system includes first name, last name, gender, dob, AKA name (if any) description, awards. ● Director: information about the Director, each director may have directed more than one Movies or TV series. Information about the director that needed to be displayed in the system includes first name, last name, gender, dob, AKA name (if any) description, awards. ● Awards: Information about awards categories. 1|Page Database System – Assessment Guide Database System Assessment Guide Assessment 1 - Case Study Note: Nomination for awards should also be recorded for actors, directors, movies and TV Series. Task Your task is to develop an entity-relationship diagram, relational model, and data dictionary for the scenario. Specifically, you must follow these rules: ER Diagram: ● Create an entity-relationship diagram using any drawing software of your choice. ● Your ERD must include all entities mentioned in the scenario. You are free to add more if you wish. ● Each entity must have at least 3 attributes excluding keys and the ones explicitly mentioned in the scenario. ● Note: remember to convert any many to many (M:N) relationships into composite entities. ● Note: remember to add in your primary and composite keys for all entities in your diagram. Relational Model ● Convert your completed ERD to relational model using Excel or LibreOffice Calc. ● Make sure your relational model is in 3rd normal form. Note: you don't need all the previous forms evident, just 3rd normal form. ● Include a minimum of 5 tuples of data in each table. ● Note: remember to add in all your foreign keys at this stage. Hint: The foreign key is added to the table that sits on the Many to the One side. So many something should relate (foreign key) to the one thing. Data Dictionary ● Create a data dictionary for all your attributes using Microsoft Excel or LibreOffice Calc. ● Your data dictionary must include the following: o Field name, type, and source table (table the attribute is from) o Description o Minimum and maximum length o Format o Range What to submit Submit your assignment via canvas. Each section is due at different times (Check Canvas): ● 1 x High-quality image (ERD) ● 1 x MS Excel file (relational model) ● 1 x MS Excel file (data dictionary) Due date Refer to Canvas. Late penalty applied, refer to AIT late submission policy. 2|Page Database System – Assessment Guide