Modern Database Management
13th Edition
ISBN: 9780134773650
Author: Hoffer
Publisher: PEARSON
expand_more
expand_more
format_list_bulleted
Concept explainers
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 solutionStudents have asked these similar questions
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
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 ?.
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
- Problem 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_forwardA) 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_forward
- ManufacturerA 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 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_forwardQ4: Design a database for an airline. The database must keep track of customers and their reservations, flights and their status, seat assignments on individual flights, and the schedule and routing of future flights. Your design should include a set of relational schemas, and a list of constraints, including primary-key and foreign-key constraints. Please note that an E-R diagram is not required.arrow_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
- Topic: Database Design - Entity Relationship Modeling (Please answer the below question in 250 words or more). Thank you! It is known that we should avoid redundant data. Let’s say you have a Product Table with the cost of an item, and an Order table that has information about orders from customers. What would be the benefits to also storing the item's cost in the Order table?arrow_forwardConsider applications “Q1”, “Q2”, “Q3”, and “Q4” as shown. These applications work on the table STUDENTS defined as “STUDENTS (RNO, NAME, ADDRESS, SEM, MARKS),” where RNO is the primary key column of the table. Q1: Select NAME from STUDENTS where MARKS = 100; Q2: Select MARKS from STUDENTS; Q3: Select SEM from STUDENTS where ADDRESS = ‘RWP’; Q4: Select NAME from STUDENTS where SEM = 6;Perform vertical fragmentation?frequency matrixCalculate usage matrix, Attribute affinity matrix, and Clustered affinity matrix?arrow_forwardDatabases and Database Users exercise: Figure 1.2 is on image Consider Figure 1.2. a.) If the name of the ‘CS’ (Computer Science) Department changes to ‘CSSE’ (Computer Science and Software Engineering) Department and the corresponding prefix for the course number also changes, identify the columns in the database that would need to be updated. b.) Can you restructure the columns in the COURSE, SECTION, and PREREQUISITE tables so that only one column will need to be updated? Figure 1.2 is on imagearrow_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 LearningDatabase 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 LearningA Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
- Fundamentals of Information SystemsComputer ScienceISBN:9781337097536Author:Ralph Stair, George ReynoldsPublisher:Cengage LearningPrinciples 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