CS 34800: Information Systems

Project 2: Database Design

Due 11:59pm EDT Wednesday, 12 October 2016

(Part A: Due 11:59pm EDT Monday, 3 October 2016)

Introduction

Description of the Database and Requirements

Requirements

1. The database should keep the information about casting directors (a team of people who are in charge of auditioning actors for roles in movies and for finalizing an actor for a particular role), movies, roles, auditions and actors (who audition for a role).
2. Each role is managed by one casting director who is reponsible for selecting an actor for that role.
3. Each actor can audition for one role in a movie (they can audition for multiple movies at a time) except when it is an animated movie. Animated movies have voice-only roles. In such cases, it is possible for an actor (e.g., Mel Blanc) to audition for multiple roles in the same movie. Each audition and its outcome must be recorded.
4. Each movie can have several open roles, and the open roles will be closed when actors have been cast for the roles.

Queries Supported

1. List the movies produced by it (the production company).
2. List all roles (open/closed) within each movie.
3. List the names of casting directors working for the production company.
4. List the names of casting directors associated with a movie.
5. For each movie, identify the roles in the movie that a casting director is responsible for.
6. For each movie, list the actors finalized for the movie.
7. Identify the movies that have open roles, and for each such movie, list all open roles.
8. See the portfolio of each actor, i.e., list of movies and roles that the actor auditioned for and the ones he was selected to/rejected from.
9. List the movies and roles that an actor is currently a part of.
10. List the casting directors that an actor has communicated with or is communicating with currently.

Your Assignment

PART A (30 points)

Design an E-R diagram for the scenario specified above. Please also indicate the notation you use, e.g., the particular symbols for entities and relationships. (You can submit Part A by 3rd October 2016 and get 5 points.)

PART B (65 points)

1. (10 points) Convert the E-R model of Part A to its corresponding relational model.
2. (10 points) For each of the relations in the database,
   
   1. Specify the functional dependencies that characterize the schema of the relation based on the requirements (A - D) above.
   2. Determine if the schema is in 3NF with respect to the functional dependencies specified in (a). If not, convert it to be in 3NF.
3. (10 points) Implement and populate data into the normalized database, i.e., write SQL statements to create the tables and insert data.
4. (30 points) Ensure your implementation can support at least the queries mentioned in Q1 - Q10 and provide SQL statements that satisfy these queries. (Based on your insert statements, the TAs should be able to insert additional data and query the database). Please mention additional queries that your implementation is able to support.
5. (5 points) Please include SQL statements to drop the tables.

What to submit

You are required to submit four files:
1. In a pdf, submit Part A and Part B (B1, B2).
2. Submit Part B3 in a sql script named b3_your_career_login.sql. (This should set up the database).
3. Submit Part B4 in a sql script named b4_your_career_login.sql. (This should execute the queries).
4. Submit Part B5 in a sql script named b5_your_career_login.sql. (This should drop the tables you created).

TESTING: Before submitting, please make sure that your sql scripts execute without any error, i.e., on typing "@p1_your_career_login" at the SQL*Plus prompt, there should not be any errors. If you are unable to finish any part, please mention in the pdf.

Turning in your files

1. (5 points) Upload the E-R design for Part A by 3rd October, 2016.
2. Please create a single zip file that contains the four files as mentioned. Please turn in the project by uploading the zip file in Blackboard (click on Project 2 and Attach File).