E-R model HW
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
