Write SQL for a TV Guide

Module 1:

Design a database schema to hold information for a TV guide.

Call the database TVGuide. Each TV show must have a title, a description, a classification (G, PG, M, MA, or R) and an optional star-rating from 0 to 5 stars. Each TV show airs at one or more specific time slots throughout the week, on a single channel.

You must actually give a design for the database. That is the implicit design from your code is not enough. Explanation (other than circular) of the choice of tables is a better answer than just a bare list of tables and attributes.

Hint: since the star-rating is optional, this should not be dumped in the same table with mandatory information such as the title or description. Avoid creating null fields for this exercise. That is, TV shows are entities, but the star rating is not a property of the TV show it is another entity to which the TV show has a relationship. What is the multiplicity of this relation? Do we need a separate table for this entity and the relation? Or will just one table server the dual purpose? A time slot is also an entity. And there is a relationship between time slot and TV show. What are the properties of a time slot? Is channel a property? Describe your arguments in no more than 300 words.

Module 2:

Write the SQL statements needed to create this schema and relations. (copy commands here)-no screenshots

Module 3:

Using SQL INSERT statements; enter 5 test data into your database, including at least 5 shows spread across at least 3 channels.

Module 4:

Devise and test a SQL SELECT statement to list the title and description (only) for all the shows classified G or M, and rated 4 stars or more.

Hint: this select will most likely have to consult multiple tables.

Module 5:

Alter your database so that it can store information about regions. Specifically, your database should now keep track of a list of regions, and which shows are broadcast in which regions.

Hint: Region is not a property or attributes but it is an entity. Justify how the region could be a crucial relation to shows, slots, or channels?

(Include any notes you think will help explain your choices.)

Module 6:

Create Stored procedure called : display5StarFilms which will accept parameter for rating with value 5

Submit:

A MS-Word file, include your name in the name of the file. For example, if your name is 'name' then call the file 'nameWorkshop5.doc'.

Include exported database script which you have created