Basically you will be completeing several ER Models. I will provide all the video lectures, the entire textbook, and powerpoint slides from the class. It can be however long you want, just saitisfy the questions.

HW#1 Listed

1. Complete Chapter 5, Exercise 1

Give two instances of the InsuranceCompany entity in the E-R model of the

healthcare database in Figure 5.19.

2. Complete Chapter 5, Exercise 3

Give two instances of the Order relationship in the E-R model of the healthcare

database in Figure 5.19.

3. Give one (total, not one for each) E-R model that satisfies Chapter 5 Exercises 5 – 8

Exercise 5

A list of database application projects is available at http://www.dssbooks.com/

web/ProjectsManual.html. Project 48 describes a database application for a movie

theater. The movie theater database should include data about the following:

• Customers with their ID, name, address, phone number, and e-mail

• Employees with their ID, name, date of hire, employment history, and salary,

where employment history is a multi-valued attribute

• Producers with their ID, name, contact information (address, phone number,

and e-mail), and current balance

• Movies with their ID, title, year of production, rating, description, awards won,

and actors, where awards won and actors are multi-valued attributes

• Showrooms with their name, location, and capacity

When a show of a movie is assigned to a showroom, the database records the

date and time of the show and the total number of tickets available for the show.

When a customer purchases ticket(s) for a show of a movie, the database records

the ticket number, date, unit price, number of tickets purchased, and amount

paid. The number of tickets purchased for a show decreases the total number

of tickets available for the show. When a customer signs up as a member to

buy tickets online, the member’s login name and password are recorded. When

the movie theater purchases movie(s) from a producer, the database records

the transaction number, purchase price, purchase date, payment due date, and

amount due.

The manager of the movie theater queries the database to obtain certain information.

The following is a list of data queries:

Conceptual Data Modeling 77

1. Financial information

a. Create a query that presents monthly revenues from ticket sales, monthly

expenses from salaries, monthly expenses from movie purchases, and

monthly earnings during the current year.

b. Create a query that presents yearly revenues, salary expenses, and movie

expenses during the current year.

2. Create a query that lists the five best movies of the current year based on the

number of awards won.

3. Create a query that lists the five most expensive movies of the current year.

4. Create a query that displays the total number of tickets sold per movie in

decreasing order of tickets sold.

5. Create a query that presents the average room usage per showroom in the current

year.

6. Create a query that lists the 100 most preferred customers based on the amounts

that the customers spent in the movie theater.

7. Create a query to present information about the producer that the movie theater

did the most business during the current year.

8. Create a query that prompts for a date and a movie title and then presents the

number of tickets available per show for the movie on that date.

9. Create a query that prompts for a movie title and then presents the weekly

show schedule of the movie and the total number of tickets available per show.

10. Create a query that prompts for a customer name and presents information

about the tickets purchased by the customer during the current month.

Given the above requirements for the movie theater database, use an E-R diagram

to describe the Customer, the Employee, the Producer, the Movie, and the

Showroom entities, along with their attributes. Give two instances of each entity.

Exercise 6

Given the requirements of the movie theater database in Exercise 5, use an E-R diagram

to describe all entities and relationships in this database without the maximum

and minimum cardinalities of each relationship.

Exercise 7

Add the maximum and minimum cardinalities to each relationship in the E-R

model produced in Exercise 6.

Exercise 8

Create superclass and subclass entities, if needed, in the E-R model produced in Exercise 7