Pearson eText for Modern Database Management -- Instant Access (Pearson+)
13th Edition
ISBN: 9780137305940
Author: Jeffrey Hoffer, Ramesh Venkataraman
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
Assume that you are a database specialist for Kulliyyah of Information and Communication Technology (KICT). You are required to create a conceptual data model for Abu Hurairah Club. The following are the data requirements gathered:
The Abu Hurairah Club has a few animal shelters. Each shelter is identified by an identification number and has an address, a phone number, and a set of days on which it is open. A shelter is managed by one employee.
An employee has an identification number, name, address, phone number, and a pay rate.
A volunteer has an identification number, name, address, phone number, and a meal allowance.
A person, who works at a shelter, is either an employee or a volunteer, but not both. A person may work at more than one shelter for some regular set of days each week.
An animal is housed at one shelter. Each animal has an identification number that is unique to its shelter, a name, a birth date, and a species.
3. StayWell also rents out properties on a weekly basis to students attending summer school
in the Seattle area. Design a database to meet the following requirements, using the short-
hand representation and a diagram of your choice.
a.
For each student renter, list his or her number, first name, middle initial, last name,
address, city, state, postal code, telephone number, and e-mail address.
b.
For each property, list the office number, property address, city, state, postal code,
square footage, number of bedrooms, number of floors, maximum number of persons
that can sleep in the unit, and the base weekly rate.
C.
For each rental agreement, list the renter number, first name, middle initial, last name,
address, city, state, postal code, telephone number, start date of the rental, end date
of the rental, and the weekly rental amount. The rental period is one or more weeks.
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…
Chapter 9 Solutions
Pearson eText for Modern Database Management -- Instant Access (Pearson+)
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
- Assume that you are a database specialist for Kulliyyah of Information and Communication Technology (KICT). You are required to create a conceptual data model for Abu Hurairah Club. The following are the data requirements gathered: The Abu Hurairah Club has a few animal shelters. Each shelter is identified by an identification number and has an address, a phone number, and a set of days on which it is open. A shelter is managed by one employee. An employee has an identification number, name, address, phone number, and a pay rate. A volunteer has an identification number, name, address, phone number, and a meal allowance. A person, who works at a shelter, is either an employee or a volunteer, but not both. A person may work at more than one shelter for some regular set of days each week. An animal is housed at one shelter. Each animal has an identification number that is unique to its shelter, a name, a birth date, and a species. Based on the requirements from a, b, c, d and e, draw…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_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_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) 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_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_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_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
- Consider 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? S1 S2 S3 S4 Q1 1 0 2 0 Q2 0 4 3 0 Q3 0 0 4 0 Q4 3 0 0 0 frequency matrix Calculate usage matrix, Attribute affinity matrix, and Clustered affinity matrix? Calculate Split point?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_forwardConsider the following schema: Suppliers(sid: integer, sname: string, address: string)Parts(pid: integer, pname: string, color: string)Catalog(sid: integer, pid: integer, cost: real) The key fields are underlined, and the domain of each field is listed after the field name. Therefore sid is the key for Suppliers, pid is the key for Parts, and sid and pid together form the key for Catalog. The Catalog relation lists the prices charged for parts by Suppliers. Write the following queries in relational algebra, tuple relational calculus, and domain relational calculus: 1. Find the names of suppliers who supply some red part.2. Find the sids of suppliers who supply some red or green part.4. Find the sids of suppliers who supply some red part and some green part.5. Find the sids of suppliers who supply every part. Write the above in SQL queries.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
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
Enhanced Entity Relationship Model; Author: Data Science Center;https://www.youtube.com/watch?v=ocQUtXPumdQ;License: Standard YouTube License, CC-BY