MODERN DATABASE MGMT.(PEARSON CHANNEL)
13th Edition
ISBN: 9780136709299
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…
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.
Chapter 9 Solutions
MODERN DATABASE MGMT.(PEARSON CHANNEL)
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
- A 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_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_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
- 1) The manager of an international company would like to know the quantity sold for each product and foreach store by day, week, month, and year. For each store, the manager would like to know its correspondingcity and country.You have to design a star schema to achieve the goal of this manager.2) If you think that some of your proposed dimension tables in Q1, are not normalized, you have to convertyour star schema into snowflake schema to normalize these dimension tables.arrow_forwardHere is the database design for the personnel data: employee(id, name, address) employeeMap(id, confidentialId) empSalary(confidentialId, salary) The role 'Clerk' would be granted SELECT, UPDATE, DELETE to employee table and granted SELECT to empSalary table. While the clerk can see salary information for any statistical analysis (e.g. average salary computation), he has no idea which employee the salary is for. The role 'Administrator' is granted rights on all three tables; using the confidentialId in the employeeMap table, she has access to salary data for individual employees too. A SQL query that the administrator can use to access all employee fields is: select e.id, e.name, e.address, es.salary from employee e inner join employeeMap m on e.id = m.id inner join empSalary s on m.confidentialId = s.confidentialId; Can you draw a database schema?arrow_forwardDraw an ERD containing the Transaction and Item entities. When a transaction is made, the date (TransDate) and type (TransType) together with TransNo (primary key) must be recorded. For an item, add attributes of ItemNo (primary key), ItemType, and RawCost. A transaction may include multiple items and an item may be included in multiple transactions. When an item is included in a transaction, Quantity and TransPrice of the item must be recorded.arrow_forward
- Question 6 V Saved Problem 4-7 will use the same database. Let a database application be Model - ( Supplier, Products, Supplier_information, Project), Which contains the information that you will buy products (Coke, Apples, .) from suppliers (Walmart, K-mart,) Multiple products will be used in the Projects (basket ball team, volley ball team, ). Product costs are the main factors for selecting a supplier. Supplier information contains additional information about a supplier (Emergeney contact person, emergency contact phone, This Supplier_information will be updated from time to time; for example, Emergency contact person can be changed. The business rule required the database to keep all of the old Emergency contact person. What is the relationship between Supplier and Project? One-to-many relationship Many-to-many relationshiparrow_forwardConsider the following relational schema: Staff (staffNo, name, dept, skillCode) Skill (skillCode, description, chargeOutRate) Project (projectNo, startDate, endDate, budget, projectManagerStaffNo) Booking (staffNo, projectNo, dateWorkedOn, timeWorkedOn) Where the relation Staff contains staff details, Skill contains descriptions of skill codes (e.g. programmer, Analyst, Manager, etc.) and the charge out rate per hour for that skill. Project contains project details and Booking contains details of the date and the number of hours that a member of staff worked on a project. The primary keys are underlined. Formulate the following queries using SQL: a) List all skills with a charge out rate greater than 60 per hour, in alphabetical order of description. [3] b) List all staff with the skill description ‘Programmer’ who work in the ‘Special Projects’ department. c) How many staff have the skill ‘Programmer’? d) List the average charge out rate…arrow_forward+ Converting schema model from ER model, an additional table is made in case of many to many relationship. What problem will arise if you not create the additional table? Explain the issue with an example.arrow_forward
- Leslie’s Auto Sales has a relational database with which it maintains data on its salespersons, its customers, and the automobiles it sells. Each of these three entity types has a unique attribute identifier. The attributes that it stores are as follows: Salesperson Number (unique), Salesperson Name, Salesperson Telephone, Years with Company Customer Number (unique), Customer Name, Customer Address, Value of Last Purchase From Us Vehicle Identification Number (unique), Manufacturer, Model, Year, Sticker Price Leslie’s also wants to keep track of which salesperson sold which car to which customer, including the date of the sale and the negotiated price. Construct an ERD for Leslie’s Auto Sales. Crow’s Foot Database Notation is required.arrow_forwardRead the following business rules carefully and use common sense when necessary to come up with a good database design as follows: 1- First, develop an ER model. Make sure to use meaningful entity names and relations. 2- Translate ER model into a set of tables, relations and constraints. 3- Label appropriate columns as primary keys and foreign keys. Business rules: A patient can make many appointments with one or more doctors in the clinic, and a doctor can accept appointments with many patients. However, each appointment is made with only one doctor and one patient. Emergency cases do not require an appointment. However, for appointment management purposes, an emergency is entered in the appointment book as “unscheduled.” If kept, an appointment yields a visit with the doctor specified in the appointment. The visit yields a diagnosis and, when appropriate, treatment. With each visit, the patient’s records are updated to provide a medical history Each patient visit…arrow_forwardDraw an Entity Relationship Diagram (ERD) based on the flat files that have been supplied. Your ERD must incorporate all the entities and attributes supplied, as well as establishing the relationships between the entitiesarrow_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