HW1 Solution

The solution below stores the actors and producers in a single table named "People". You might be tempted to create 2 different tables, one for actors and one for producers. However, this is not a good idea since it leads to duplication of data for actors who are also producers. The solution below includes an Entity Relationship Diagram(ERD) and a list of table descriptions below the ERD

Entity Relationship Diagram (ERD)

Table Descriptions

If there is more than one primary key field in a table then the primary key is known as a "composite primary key" which is composed of all the primary key fields in the table.

If there is more than one foreign key field in a table then the different foreign key fields may refer to different tables.

Table Name: Movies
Field Name Primary Key (P)
Foreign Key(F)		 Related Table(s)
(only enter this for foreign key fields)
MovieId P  
Title    
Year    
RatingId F Ratings


Table Name: Theaters
Field Name Primary Key (P)
Foreign Key(F)		 Related Table(s)
(only enter this for foreign key fields)
TheaterId P  
Name    
TicketPrice    
Street    
City    
State    
ZipCode    


Table Name: People
Field Name Primary Key (P)
Foreign Key(F)		 Related Table(s)
(only enter this for foreign key fields)
PersonId P  
Fname    
Lname    


Table Name: Ratings
Field Name Primary Key (P)
Foreign Key(F)		 Related Table(s)
(only enter this for foreign key fields)
RatingId P  
Rating    


Table Name: ActorInMovie
Field Name Primary Key (P)
Foreign Key(F)		 Related Table(s)
(only enter this for foreign key fields)
MovieId P, F1 Movies
PersonId P, F2 People
StarOrSupport    
Salary    


Table Name: MovieInTheater
Field Name Primary Key (P)
Foreign Key(F)		 Related Table(s)
(only enter this for foreign key fields)
MovieId P, F1 Movies
TheaterId P, F2 Theaters
NumTicketsSold