E-R model HW

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