WiL Activity 5:

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.

ERD Relationships

TVShow / StarRating:

TVShow has zero to one (optional) relationship with StarID. i.e. A TVShow can be given a 0 rating or any number between 0-5.

StarRating has one to many relationship with TVShow.

Region / TVShow:

Region have one to many relationship with TVShow. Region can broadcast many TVShows.

TVShow have one to only one (mandatory) relationship with Region. TVShow cant exist without Region.

TimeSlot / TVShow:

TimeSlot have one to only one (mandatory) relationship with TVShow. TVShow has to be aired atleast at one TimeSlot in order be valid.

TVShow have one to many relationship with TimeSlot. TVShow can be aired at one or many other TimeSlot available.

Module 2:

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

CREATE TABLE [StarRating]

(

StarID INT,

PRIMARY KEY (StarID)

)

CREATE TABLE [TimeSlot]

(

TimeSlotID INT,

AirTimeStart TIME,

AirTimeEnd TIME,

PRIMARY KEY (TimeSlotID)

)

CREATE TABLE [TVShow]

(

TVShowID INT,

TitleName VARCHAR (50),

Description VARCHAR (50),

Classification VARCHAR (50),

Channel INT,

PRIMARY KEY (TVShowID),

FOREIGN KEY (StarID) References StarRating (StarID),

FOREIGN KEY (TimeSlotID) References TimeSlot (TimeSlotID),

)

Module 3:

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

INSERT INTO TVGuide Values

(1, Fringe, Science-Fiction, PG, 10),

(2, Mentalist, Police-Thriller, G, 9),

(3, Prison Break, Action, MA, 8),

(4, Serie Rose, Dorcel TV, R, 7),

(5, Moonlight, Drama, M, 6);

INSERT INTO StarRating Values

(0),

(1),

(2),

(3),

(4),

(5);

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.

SELECT TitleName, Description, StarID,

FROM TVGuide

WHERE StarID in StarID = 4;

WHERE classification ('G', 'M')

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.

ALTER TABLE [TVGuide]

ADD RegionName VARCHAR (50),

ADD FOREIGN KEY (RegionID) References Region (RegionID),

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

CREATE PROCEDURE display5StarFilms

@StarID INT

AS

SELECT *

FROM TVGuide

WHERE StarRating = @StarID > 5

EXEC display5StarFilms

Activity 6: Enhance the TV Guide database To complete this worksheet, you will need your TV guide database from worksheet 5. Insert test data as required. Write SQL SELECT queries to retrieve the following information from your database. If your database does not include the information requested here then modify your database to include the information in a natural manner and then extract it. Prelude: Include in the code for this worksheet the code that creates the TVGuide database. Module 1: Display a list of all the show names, along with the definition in which they are aired Hint: 'definition' as in 'high definition' or 'low definition' (how clear the picture is). See the earlier hints, and remember to build the selection one step at a time. This is a simple selection from the TVShows and quality tables. Module 2: Display a list of all the shows, along with the regions and timeslots in which they air Hint: combine the tables for shows, regions, and slots. Module 3: Display the number of shows aired on a particular channel on a particular day of the week. (You pick the channel and the day, but it should still work if I change the day or channel). Hint: you will need a select statement that selects show title, channel name, and slot day from the appropriate tables. In the workshop example that would be the TVShows table, the TVChannels table and the TVSlots table. But, the connection between all these is the Scheduled relation table. So you will also need that. After that, the main thing is to make the IDs that turn up in each table match. And then select only rows with the given day and name. It is strongly recommended that you build this a step at a time, combine two tables and make certain that the result is correct before adding another table and before cutting down what the selection actually shows. Module 4: Display the average star rating for shows aired on a particular channel. (You pick the channel. But, it should still work if I change the channel). Hint: See the hint for Module 2. In this case you need the Scheduled table and the table that contains the star rating. Select all the appropriate star ratings, and then average them. Remember that star ratings are optional not every show has one. Make certain all the IDs match correctly. Then use the AVG (*) function. Module 5: Create 5 stored procedures: Each of stored procedures will receive a parameter