Question H: Provide your finished 3NF LDM solution (i.e. entities like \" Entity

Question

Question H:

Provide your finished 3NF LDM solution
        (i.e. entities like "Entity1 ((keydata1,keydata2)(K),nonkey1,nonkey2,...)" and
               relationships like "Verb: Entity1 1:M Entity2" ),
         with one Entity or Reln per line.

=========================================================

The Cinema Guild Theatre Group wants the following collection of 21 data items to be implemented in a database to manage its member theatres in several towns. Provide the 3NF LDM that satisfies these requirements.

Your answer must be the minimum required for a valid Logical Data Model solution. No extraneous entities or relationships. Do not add *any* data items to the 21 items shown. You must label with a name each entity and each relationship (between entities). Check carefully that you have satisfied all constraints A through K.

Data items:

Actor, Address_of_Theatre, Adult_Count, Adult_Price, Child_Count, Child_Price, Date, Director, Movie_Name, Movie_Rating, Movie_Review, Number_of_Screens, Screen_Number, Senior_Count, Senior_Price, Student_Count, Student_Price, Theatre_Name, Theatre_Phone_Number, Time_of_Showing, Town

The following describe key business rules, definitions, requirements and relationships between data items:

A: Theatres can show a "double bill" (2 films for 1 admission price) or a "triple bill" (3 films) or a "festival" (a larger number of films for one price).

B: The film distributor requires each theatre to report the count of each type of ticket sold by showing. A showing is one start of a movie on a particular day at a specific time. Movies shown together (double bill, etc.) have one start time.

C: A given movie may be showing at more than one theatre at the same time.

D: The movie review is a short paragraph quoted from a national magazine. The movie's rating is also based on non-local information.

E: The database needs to track all principal actors in a movie, e.g. co-stars Bette Middler and Carrie Fisher.

F: Theatres sometimes run film festivals, where they show several films starring the same actor. (Don't handle the festival separately, just be sure that your design for individual movies handles it.)

G: Theatres have four classes of admission with separate prices: adults, students, children, and senior citizens.

H: All prices are determined by the time of day and the day of the week (i.e. the date). Therefore, weekend and holiday prices can be different than weekday prices. Prices can be different in each theatres showing the same film at the same time.

I: A theatre is in only one town, but a town can have more than one theatre.

J: Most of the theatres in the theatre group have become multi-screen centers, as specified by the "Number of Screens" data item for each theatre. For each showing, the screen number must be recorded to identify the actual room in which the screening will occur. Date and time alone do not uniquely identify a showing.

K: Assume that a movie has only one director, but that director film festivals can be held (e.g. showing five Alfred Hitchcock or Woody Allen movies).

Question H Solution:

DIRECTOR ( Director(K) )

ACTOR ( Actor(K) )

FILM ( Movie-name(K), Review, Rating )

PRICE ( ( Date, Time, Theatre )(K), Adult-Price, Child-Price, Student-Price, Senior-Price )

SHOWING ( ( Date, Time, Theatre, Screen# )(K), Adult-Count, Child-Count, Student-Count, Senior-Count )

THEATRE ( Theatre(K), Number-of-Screens, Address, Phone )

TOWN ( Town(K) )

contains: TOWN 1:M THEATRE

directs: DIRECTOR 1:M FILM

stars-in: ACTOR M:N FILM

shown-at: FILM M:N SHOWING

price-for: PRICE 1:M SHOWING

shows: THEATRE 1:M SHOWING charges: THEATRE 1:M PRICE

Based on your finished Assignment H 3rd Normal Form logical data model (LDM), create the Relational Physical Data Model (RPDM) that implements your solution.

Identify all foreign keys by marking them clearly ("(FK)").

Note that your creating the RPDM must not change your LDM [unless reading back your RPDM lets you discover errors that were in your LDM (requiring you to fix the LDM)]. The RPDM you submit must be directly derived from the 3NF LDM.

Note that a RPDM does not contain any relationship statements. The RPDM contains only Table definitions. Any relns from the LDM must be implementing in the RPDM using Foreign Keys (FK).

Explanation / Answer

RPDM

Create table TOWN

(

Pincode integer not null,

TownName varchar(30) not null,

Primary key(Pincode)

);

Create table THEATRE

(

TheatreName varchar(20) not null,

Number-of-screens integer,

Address varchar(50),

Phone varchar(15),

Pincode integer not null,

Primary key(TheatreName),

Foreign key (Pincode) references TOWN(Pincode)

);

Create table SHOWING

(

SDate date not null,

STime time not null,

Theatre varchar(20) not null,

Screen# integer not null,

Adult-Count integer ,

Child-Count integer,

Student-Count integer,

Senior-Count integer,

Movie-name varchar(50) not null,

Primary key(SDate,STime,Theatre,Screen#)

Foreign Key (Movie-name ) References FILM(Movie-name)

);

Create table PRICE

(

SDate Date not null,

STime time not null,

Theatre varchar(20) not null,

Adult-Price integer,

Child-Price integer,

Student-Price integer,

Senior-Price integer,

Primary key(SDate,STime,Theatre),

Foreign Key(SDate,STime,Theatre) references SHOWING(SDate,STime,Theatre)

);

Create table FILM

(

Movie-name varchar(50) not null,

Review varchar(500),

Rating integer,

DSSN varchar(20) not null,

ASSN varchar(20) not null,

Primary Key (Movie-name),

Foreign key(DSSN) REFERENCES DIRECTOR(SSN),

Foreign Key (ASSN) REFERENCES ACTOR(SSN)

);

Create table DIRECTOR

(

SSN varchar(20) not null,

DirectorName varchar(30) not null,

Primary Key (SSN)

);

Create table ACTOR

(

SSN varchar(20) not null,

ActorName varchar(30) not null,

Primary Key(SSN)

);

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

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