Modern Database Management
13th Edition
ISBN: 9780134792293
Author: Hoffer
Publisher: PEARSON
expand_more
expand_more
format_list_bulleted
Concept explainers
Videos
Textbook Question
Chapter 9, Problem 9.40PAE
Simplified Automobile Insurance Company would like to add a Claims dimension to its star schema (see Problem and Exercise 9-39). Attributes of Claim are ClaimID, ClaimDescription, and ClaimType. Attributes of the fact table are now PolicyPremium, Deductible, and MonthlyClaimTotal.
a. Extend the star schema from Problem and Exercise 9-39 to include these new data.
b. Calculate the estimated number of rows in the fact table, assuming that the company experiences an average of 2,000 claims per month.
Expert Solution & Answer
Want to see the full answer?
Check out a sample textbook solution
Students have asked these similar questions
Want in detail
I'm having trouble with this question with my data modeling class.
"
Scenario:
Jonny is appointed as an intern position, which might lead to a junior database architect role in TechTrend Corporation (TTC). Jonny is assigned to redesign two legacy tables related to "Suppliers," to be normalization compliant. The following business rules are provided.
Business Rules:
1. Suppliers can be local or national, thus can have multiple locations.
2. Any location can supply items to TechTrend.
3. Any location manager can have a separate pricing deal with TechTrend, based on production process specific to that location and localization efficiency.
4. Each location has a contact person assigned to TechTrend.
5. Traditionally, TechTrend dealt with a singular location, at the most two. With rapid growth in business, the TTC officials are finding better leverage possibilities to negotiate beneficial terms with suppliers in multiple locations.
6. The DB administrator has specifically requested Johnny…
Make sure the SQL script file can be run successfully in MySQL and show the outcome of the code on MySQL
Chapter 9 Solutions
Modern Database Management
Ch. 9 - Prob. 9.1RQCh. 9 - Prob. 9.2RQCh. 9 - Prob. 9.3RQCh. 9 - List the five major trends that necessitate data...Ch. 9 - Prob. 9.5RQCh. 9 - Prob. 9.6RQCh. 9 - Prob. 9.7RQCh. 9 - Prob. 9.8RQCh. 9 - Prob. 9.9RQCh. 9 - Prob. 9.10RQ
Ch. 9 - List four objectives of derived data.Ch. 9 - Prob. 9.12RQCh. 9 - Prob. 9.13RQCh. 9 - Explain the pros and cons of logical data marts.Ch. 9 - Prob. 9.15RQCh. 9 - What is a helper table, and why is it often used...Ch. 9 - Describe the characteristics of a surrogate key as...Ch. 9 - Prob. 9.18RQCh. 9 - What is the purpose of conformed dimensions for...Ch. 9 - Prob. 9.20RQCh. 9 - Prob. 9.21RQCh. 9 - Prob. 9.22RQCh. 9 - Prob. 9.23RQCh. 9 - Prob. 9.24RQCh. 9 - Prob. 9.25RQCh. 9 - Describe the current key trends in data...Ch. 9 - Discuss the benefits of offering data warehousing...Ch. 9 - Prob. 9.28RQCh. 9 - List six typical characteristics of reconciled...Ch. 9 - Prob. 9.30RQCh. 9 - Prob. 9.31RQCh. 9 - Prob. 9.32RQCh. 9 - Prob. 9.33RQCh. 9 - Describe some field-level and record-level data...Ch. 9 - Prob. 9.35PAECh. 9 - Prob. 9.36PAECh. 9 - Prob. 9.37PAECh. 9 - Prob. 9.38PAECh. 9 - You are to construct a star schema for Simplified...Ch. 9 - Simplified Automobile Insurance Company would like...Ch. 9 - Prob. 9.41PAECh. 9 - Prob. 9.42PAECh. 9 - Prob. 9.43PAECh. 9 - Prob. 9.45PAECh. 9 - Prob. 9.46PAECh. 9 - Prob. 9.47PAECh. 9 - Prob. 9.48PAECh. 9 - Prob. 9.49PAECh. 9 - Prob. 9.50PAECh. 9 - Prob. 9.51PAECh. 9 - Prob. 9.52PAECh. 9 - Prob. 9.53PAECh. 9 - Prob. 9.54PAE
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Similar questions
- The model above needs some improvement. Where you can apply all the normalization rules to attributes. Furthermore, you also need to explain entities and relations between them, and the purpose of attributes by showing why and how they exist ?.arrow_forwardProblem A: A retail chain of pharmacies, Prescriptions-R-X that sells prescription drugs has hired your team of data analyst to design its database. Here's the information that your team gathers after discussions with managers from Prescriptions-R-X: Patients are identified by an SSN, and their names, addresses, and ages must be recorded. Doctors are identified by an SSN. For each doctor, the name, specialty, and years of experience must be recorded. Each pharmaceutical company is identified by name and has a phone number. For each drug, the trade name and formula must be recorded. Each drug is sold by a given pharmaceutical company, and the trade name identifies a drug uniquely from among the products of that company. If a pharmaceutical company is deleted, you need not keep track of its products any longer. Each pharmacy has a name, address, and phone number. Every patient has a primary physician. Every doctor has at least one patient. Each pharmacy sells several drugs and has a…arrow_forwardCreate an Entity-Relationship Model for the Queen Anne Curiosity Shop The first assignment for the class project is to create an entity-relationship model of the Queen Anne Curiosity Shop database. This assignment requires you to apply the knowledge gained from study of chapter 4 of the textbook. You will create the entity-relationship model using the ERDPlus database modeling tool. The Queen Anne Curiosity Shop database contains the following tables: CUSTOMER (CustomerID, LastName, FirstName, Address, City, State, ZIP, Phone, EmailAddress) EMPLOYEE (EmployeeID, LastName, FirstName, Phone, EmailAddress) VENDOR (VendorID, CompanyName, ContactLastName, ContactFirstName, Address, City, State, ZIP, Phone, Fax, EmailAddress) ITEM (ItemID, ItemDescription, PurchaseDate, ItemCost, ItemPrice, VendorID) SALE (SaleID, CustomerID, EmployeeID, SaleDate, SubTotal, Tax, Total) SALE_ITEM (SaleID, SaleItemID, ItemID, ItemPrice) Note that the above table descriptions follow the textbook convention:…arrow_forward
- A) Consider the following Schema of Shaheen Airline Pakistan; they want you to generate reports by using the following Retired Employee Information Database. Retired Employee (eid:int name:varchar(25), age:int, pension:int) Duration_Work(eid:int, did:int, dname:varchar(25), work_to,work_from,percent_time:DATETIME) Department_Worked(did:int dname:varchar(25), depart_budget:long int, manager:int) Design ERD for the above scenario 1) What are the ages of the retired employees who have pension more than Rs 1 Lac? 2) Find the names of the retired employee who serves the department more than 30% of time of their life and have a budget of over Rs 10 Lac. 3) Find the name of the employee who exactly serves one department only in their whole life? 4) Find the name of the departments who have an/are retired to be employee(s) having age between 50 to 60 only? 5) Find the name of the retired employee who serves only "Cargo Department" or who serves the department more than half of their life time…arrow_forwardManufacturerA manufacturing company produces products. The following product information is stored: product name, product ID and quantity on hand. These products are made up of many components. Each component can be supplied by one or more suppliers. The following component information is kept: component ID, name, description, suppliers who supply them, and products in which they are used.Assumptions• A supplier can exist without providing components.• A component does not have to be associated with a supplier.• A component does not have to be associated with a product. Not all components are used in products.• A product cannot exist without components. Construct an ERD table to show how you would track this information.arrow_forwardA college course may have one or more scheduled sections or may not have a scheduled section. Attributes of COURSE include Course ID, Course Name, and Units. Attributes of SECTION include Section Number and Semester ID. Semester ID is composed of two parts: Semester and Year. Section Number is an integer (such as 1 or 2) that distinguishes one section from another for the same course but does not uniquely identify a section. How did you model SECTION? Why did you choose this way versus alternative ways to model SECTION?arrow_forward
- A data model is a collection of data description constructs. The main purpose of a data modeling is to assist in the understanding of the meaning (semantics) of the data and facilitate communication about the information requirements. Discuss FIVE (5) evaluation criteria for a good data model. [Hint: As a database designer, you have to interview customers, you are required to include questions you may ask to customers in your answers]arrow_forwardI need help building an ERD in Crow's Foot notation with the following (If possible show me any many-many and optional/mandatory relationship) :arrow_forwardGiven these business rules, create the Entity Relationship Diagram, include relationships and cardinalities. A bookstore is modeling its data. On one part, we have the READERS. Readers have a registration ID, name composed by first name and last name, mailing address and list of topics of interest (cooking, fiction, etc). The books available for readers will be stored with title, year of publication, isbn code and a list of topics that the book is tagged for. Authors are also to be stored with an authorID, name composed by first name, middle name and last name, mailing address and date of birth. One book can have multiple authors, but must have at least one. One author can write multiple books, one book or even be registered as an author without authoring any books. A customer can own multiple books and only starts being a costumer after buying her/his first book. I need help understanding how this works and how I put it togetherarrow_forward
- Design the database as a logical ERD showing attributes and multiplicities (suggest you use IE Notation in Oracle Data Modeler) called Movie. No need to include physical data types. It should contain an ID, Title, Year Released, Genre (e.g., sci-fi, comedy, thriller, etc.), Rating (e.g., G, PG, R, etc.), Format (e.g., VHS, DVD, MP3, etc.), a free-form Comments field, plus the main cast and crew members. Add the following entry plus at least two of your own: Title: Star Trek Year: 2009 Genre: Sci-Fi Rating: PG Format: DVD Director: J. J. Abrams Starring: Chris Pine (Capt. Kirk), Zachary Quinto (Mr. Spock) Zoe Saldana (Uhura), Karl Urban (Bones McCoy) What normal form is this corrected design? Why? Can it handle a movie being coded for multiple Genres? Does it have minimal data changes if a Genre name was changed (e.g., Thriller becomes Suspense)?arrow_forwardConsider the following Schema of Shaheen Airline Pakistan; they want you to generate reports by using the following Retired Employee Information Database. Retired Employee (eid:int, name:varchar(25),age:int, pension:int) Duration_Work(eid:int, did:int, dname:varchar(25), work_to,work_from,percent_time:DATETIME) Department_Worked(did:int, dname:varchar(25), depart_budget:long int, manager:int) Design ERD for the above scenario 1) What are the ages of the retired employees who have pension more than Rs 1 Lac? 2) Find the names of the retired employee who serves the department more than 30% of time of their life and have a budget of over Rs 10 Lac. 3) Find the name of the employee who exactly serves one department only in their whole life? 4) Find the name of the departments who have an/are retired to be employee(s) having age between 50 to 60 only? 5) Find the name of the retired employee who serves only “Cargo Department” or who serves the department more than half of their life…arrow_forwardCreate an ER model related to the following entities and relationships: Department, Student, Professor, Course, Section, Grade, Major. (For attributes inside each table, you only need to insert several reasonable attributes. No need to have a comprehensive list. For many-to-many relationship, you also need to insert one extra table to handle the many-to-many relationship. The final database design will have more tables than the number of entities listed above.) A given department can offer many courses. A given course can be offered by only one related department. A given course can have many related sections. A given professor can teach many sections. A given section can only be related to one course and taught by one professor. A given professor can have appointment with different departments A given department can have many professors. A given department can offer several majors. A given major can be offered by only one related department. A given student can have several majors. A…arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
Fundamentals of Information SystemsComputer ScienceISBN:9781337097536Author:Ralph Stair, George ReynoldsPublisher:Cengage Learning
Principles of Information Systems (MindTap Course...Computer ScienceISBN:9781305971776Author:Ralph Stair, George ReynoldsPublisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Fundamentals of Information Systems
Computer Science
ISBN:9781337097536
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781305971776
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Enhanced Entity Relationship Model; Author: Data Science Center;https://www.youtube.com/watch?v=ocQUtXPumdQ;License: Standard YouTube License, CC-BY