Write SQL for a TV Guide Module 1: Design a database schema to hold information

Question

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

Explanation / Answer

HI..

1.

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

Book

Title = 'Gone with the Wind'

Author = 'Margaret Mitchell'

ISBN = '978-1416548898'

Cat

Name = 'Phoebe'

Color = 'Gray'

TailLength = 9 'inch'

Beer Bottle

Volume = 500 'ml'

Color = 'Green'

This is how tables may look like:

Entity

EntityID Name Description

1 'Book' 'To read'

2 'Cat' 'Fury cat'

3 'Beer Bottle' 'To ship beer in'

.

PropertyType

PropertyTypeID Name IsTrait Description

1 'Height' 'NO' 'For anything that has height'

2 'Width' 'NO' 'For anything that has width'

3 'Volume' 'NO' 'For things that can have volume'

4 'Title' 'YES' 'Some stuff has title'

5 'Author' 'YES' 'Things can be authored'

6 'Color' 'YES' 'Color of things'

7 'ISBN' 'YES' 'Books would need this'

8 'TailLength' 'NO' 'For stuff that has long tails'

9 'Name' 'YES' 'Name of things'

.

Property

PropertyID EntityID PropertyTypeID

1 1 4 -- book, title

2 1 5 -- book, author

3 1 7 -- book, isbn

4 2 9 -- cat, name

5 2 6 -- cat, color 6 2 8 -- cat, tail length

7 3 3 -- beer bottle, volume

8 3 6 -- beer bottle, color

.

Measurement

PropertyID Unit Value

6 'inch' 9 -- cat, tail length

7 'ml' 500 -- beer bottle, volume

.

Trait

PropertyID Value

1 'Gone with the Wind' -- book, title

2 'Margaret Mitchell' -- book, author

3 '978-1416548898' -- book, isbn

4 'Phoebe' -- cat, name

5 'Gray' -- cat, color

8 'Green' -- beer bottle, color

2.Write the SQL statements needed to create this schema and relations

Write the SQL statements required to create the preceding relations, including appropriate versions of all primary and

foreign key integrity constraints

CREATE TABLE Emp ( eid INTEGER, ename CHAR(10), age INTEGER, salary REAL, PRIMARY KEY (eid);

CREATE TABLE Works ( eid INTEGER, did INTEGER, pcttime INTEGER, PRIMARY KEY (eid, did), FOREIGN KEY (did) REFERENCES Dept, FOREIGN KEY (eid) REFERENCES Emp, ON DELETE CASCADE

CREATE TABLE Dept ( did INTEGER, budget REAL, managerid INTEGER , PRIMARY KEY (did), FOREIGN KEY (managerid) REFERENCES Emp, ON DELETE SET NULL

THANKS

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.