(Solved): CIS505 Assignment #1 Data Modelling ...

HW1: Data Modelling (Total Points: 30)

Individual assignment

Name:

Guidelines:

• Please note that this assignment contains THREE problems  
• Please name your file  HW1_lastname_firstname (e.g., HW1_Chen_Peiyu.doc), and submit your file to canvas (following appropriate hw submission link) by the specified deadline. Please double check your homework submission to ensure you upload the right file before you submit it.

Problem 1:

You have been asked to develop a data model for TV Guide.  Consider the following business rules:

•             There are many channels on TV

•             Each channel broadcasts many shows. A show may be broadcasted on different channels.

•             Some shows are broadcast multiple times over the course of a week.  (i.e. Showtime might show American Dad Monday at 6:30 PM on PBS and Tuesday at 2:30 PM on PBS)

•             A show will have multiple actors

•             An actor has a specific role in each show and can be in multiple shows (i.e. Amanda Schull is Katrina Bennett on Suits and Dr. Railly on 12 Monkeys).

Task

Your task is to study the business objectives and model the data and relationships to support these objectives. You must create an Entity-Relationship Model. This should be a high-level model, meaning, unless necessary for understanding, you do not need to specify additional attributes for your entities and relationships other than the ones described or implied above. You do need to identify all entities, relationships, attributes and cardinalities according to the description above. These specifications might not be as clear or as formally defined as might be needed, but you are the data modeler, you can see through this, and when you can't, you write your assumptions and proceed anyway. It is important that your assumption does not violate the provided description above or the reality. And if you need to make assumptions, ensure you document them clearly at the bottom of your diagram.

Instructions:

1. Please identify the entities and use these entities in your diagram.
2. Your diagram should reflect the entities you identified as well as relationships, cardinality and attributes (please underscore the unique identifier) mentioned in the text. The attributes should be at the generic level that expedite search (e.g., it is best to break name into last name and first name).   
3. The notation in your diagrams must be consistent with the “crows-feet” ER models we covered in class.
4. You must create your diagrams electronically (they can NOT be hand-drawn). You can use any tools (draw.io, erdplus.com, or even Word and PowerPoint) to create the diagrams.
5. Please paste your diagram or screenshot of your diagram here on this assignment. 

Problem 2.  The following is an E-R diagram for a flight reservation system. Please convert the E-R diagram to database schema using a software tool (e.g., MySQL Workbench or Microsoft Access). You can export the schema as png or pdf, and paste your diagram on this assignment.

Problem 3. Given the following relation:

AppSales(AppID, AppName, DeveloperID, DeveloperName, DeveloperCountry, PlatformID, PlatformName, ReleaseDate, Price, UnitsSold)
 

Assumptions:

• Each app has a unique ID, which is used across platforms. AppName may not be unique.
• Each app has one and only one developer. Each developer has a unique ID and unique country. Different developers may have the same name. One developer may have many apps.
• Assume each app can be released to different platforms, which are identified by PlatformID. One app’s release times and prices on different platforms can be different.

1. 1. Why is the above relation a bad design? (You should be able to answer this question in just a couple sentences.)
   2. Please decompose the relation.