Part 1 (35 points)

Draw an ER diagram containing the Book and Review entity types connected by a 1:M relationship between Book and Review. Choose an appropriate relationship name. Define minimum cardinalities so that all reviews are for exactly one book, but a book may never have been reviewed. For the Book entity type, add attributes ISBN(primary key), Author, and Title. For the Review entity type, add attributes ReviewID (primary key), ReviewDate, ReviewerID, and Comment.

Then extend the ERD with the Chapter entity type and a 1:M inferential relationship between Book and Chapter. Chapter is a weak entity. Define minimum cardinalities so that a book must have at least one chapter, and each chapter belongs to one book. For the Chapter entity type, add attributes ChapterNumber (primary key), ChapterTitle, and ChapterText.

Then extend the ERD with a self referencing M:N relationship named Cites for the Review entity type. A review can be cited by many other reviews, and a review cite many other reviews. Not all reviews are cited, and not all reviews cite other reviews.

Part 2 (35 points)

Create an extended E-R diagram from the following narrative:

The federal government plans to give money to small businesses, local governments, and colleges for various projects. We need to develop a database to keep track of this spending.

· Each project has a unique ID, a title, a purpose, a dollar amount, a start date, and an end date.

· Each project has a series of status reports that must be filed. Each status report is for just one project. Each status report has a date, money spent so far, and a comment. The dates of the status reports for a given project are all unique.

· Each project is to be executed by a single organization. An organization can execute many projects.

· An organization can have many employees, and an employee can work for more than one organization.

· We keep track of every employee's social security number, name, date of birth, payment, address, city, state, and zip code.

· All organizations have a unique id number, name, address, city, state, and zip code.

· For a small business, we also keep track of their corporation status and date of incorporation.

· For a college, we also keep track of the number of faculty and the number of students.

· For a local government, we also keep track of their population and their organization-level (state, county, city, etc.)

· An organization can be something else instead of a small business, college, or local government. But no organization is both a small business and a college, a small business and a local government, or a college and a local government.

Part 3 (30 points)

Convert the following E-R diagram into tables.

Play(PlayID, Title, Language, DateWritten, AuthorID)

Monologue(PlayID, MonoNumber, Length, Act, Scene, Character)

Audition(PlayID, MonoNumber , ActorID, AuditionTime, AuditionDate, Rating)

Actor(ActorID, Gender, DOB, Name)

Author(AuthorID, Name, DOB, Nationality)

Theater(TheaterID, TheaterName, City, State)

Performance(TheaterID, PlayID, StartDate, EndDate)