Modern Database Management
13th Edition
ISBN: 9780134773650
Author: Hoffer
Publisher: PEARSON
expand_more
expand_more
format_list_bulleted
Concept explainers
Videos
Textbook Question
Chapter 9, Problem 9.39PAE
You are to construct a star schema for Simplified Automobile Insurance Company (for a more realistic example, Kimball, 1996b). The relevant dimensions, dimension attributes, and dimension sizes are as follows:
| InsuredParty |
Attributes: InsuredPartylD and Name. There is an average of two insured parties for each policy and covered item. |
| Coverageltem |
Attributes: CoverageKey and Description. There is an average of 10 covered items per policy |
| Agent |
Attributes: AgentID and AgentName. There is one agent for each policy and covered item. |
| Policy |
Attributes: PolicylD and Type. The company has approximately 1 million policies at the present time. |
| Period |
Attributes: DateKey and FiscalPeriod. |
Facts to be recorded for each combination of these dimensions are PolicyPremium, Deductible, and NumberOfTransactions.
- Design a star schema for this problem. See Figure 9-10 for the format you should follow.
- Estimate the number of rows in the fact table, using the assumptions stated previously.
- Estimate the total size of the fact table (in bytes), assuming an average of 10 bytes per field.
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
Create an ER Model for the following requirements:
A car has a make, model and year.
A registration has a county and date.
A person has an address and an age.
A car may be owned by a person.
A car may be registered to an owner.
Explain your reasoning behind 1 choice you made between in the ER diagram in question 1. For example, why choose entity vs relationship, or weak entity vs ternary relationship, or use/disuse of an aggregation, or similar choices.
Consider 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.
Create an E-R model for the following scenario.
Show entity names, primary keys, attributes for each entity, relationships between the entities and
cardinality.
Lonestar Car Dealer is a leading car dealership in US. The dealership sells both new and used cars,
and it has a service facility as well. There are a couple of requirements that the database must meet.
Those are as follows.
Each car is sold by a salesperson. One car sale has only one salesperson. But one salesperson may
sell many cars.
Customers buy one or many cars. But single sale can have only one customer (No multiple
customers for a given car sale).
The salesperson writes one invoice for each car he/she sells. Customer is issued this invoice.
A customer may be just a service center customer and does not need to be a car buyer.
One service ticket per each car is issued when it is reached to the service center.
The car a service history is maintained for each of the cars serviced. Car has a unique serial number.
One or more…
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
- A well-structured database starts from an effective Entity Relationship Diagram (ERD). For this part of the question, you will need to sketch an ERD for the following gym scenario: A member must have a member ID, name and contact number, can be enrolled in one or more gym session. A gym session can be enrolled in by one or more members. Each gym session is identified by a session ID, and has a name, date and instructor details (Instructor ID). An instructor has an instructor ID, name and contact number, and must only be assigned to one gym session. A gym session can have one or more instructors assigned in it.” *show entities, relationship types and cardinalities. Crow’s foot model notations recommended.arrow_forwardConsider the following entity relationship diagram. Booking Employee Room Building • Booking ID • Employee ID • Building ID • Room ID • First Name • Room ID P • Name Description • Employee ID • Name Last Name • Date • Campus Zone • Email Address • Capacity • Start Time • Map Coordinates Phone • Bookable • End Time For each statement below, use the drop-down box to indicate whether it is true (answer 1) or false (answer2), according to the design decisions encoded in the ERD. Every booking is associated with exactly one employee. ✓ Every employee is associated with at most one booking. ✓ A building may contain no rooms that are recorded in the database. Each room may have many bookings. 1. True ✓ A room must have at least one booking. 2. False ✓ A booking can be associated with many employees. An employee may have no phone number recorded. ✓ A room must have its capacity recorded. ✓ A building is uniquely identified by its map coordinates.arrow_forwardPlease draw an EER diagram for the bank DB system. It requires to design & plan & document a bank system. Your EER model must include supertype and subtypes inheritance associations based on your initial ER model. Relationship constraints and completeness rules (discussed in the EER module) must be included. Requirements: Metadata: define what attributes (data) represent an object (i.e. account type) and data properties (data types, valid range of values, required vs. optional, etc.) Entities (objects): Account owner, dependent (joint family), Checking account, Savings account, Money Market account, loan account, or other banking objects, online account, etc. (suggestion: Define at least 10 entities) Relationships: customer deposit or withdraw money with account, and more. Identify the possible entity sets and their attributes, the relationships among the attributes.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_forwardConsider the following dimensions, dimension attributes, and dimension sizes for Farm Coop-Insurance: Member(member_ID, Name, Address). On average, there are two members for each policy and item covered by the policy. InsuredItem(Item_ID, Description, Coverage_Type). There is an average of ten covered items per policy. CoopOffice(Office_ID, Address, Manager_name). Each policy is registered and managed by only one office of the cooperative. Policy(Policy_ID, Type). The company has approximately one million policies at the present time. Approximately five percent of these policies experience some change each month. Period(Date_Key, Fiscal_Period). The length of the fiscal period is one month. The decision system reports are supposed to be based on five years of data. Claim(Claim_ID, Claim_Description, Claim_Type). The facts to be recorded for each combination of these dimensions are Policy_Premium, Deductible, and Monthly_Claim_Total. Design a star schema for this problem. Using the…arrow_forwardHow to convert a weak entity set into a strong entity set?arrow_forward
- In order to be used as a primary key, which of these characteristics must an attribute possess with respect to a single instance of the entity? There must be a maximum of one attribute instance. There must be many instances of the attribute. There must be at most one entity instance that each attribute instance describes. There must be at least one instance of the attribute. There must be zero or more instances of the attribute.arrow_forwardSwing Jazz Club ERD Problem Follow the Simple Procedure for ERD diagramming to design an ERD using the following user provided information. Remember the ERD will eventually turn into the design for a database from a top down view. Make sure you resolve any many to many conflicts. Label an identifier or primary key and all pertinent attributes given. If an attribute is not readily apparent from the user information, then make one up that makes sense for the particular entity. Therefore, all entities should end up with at least an identifier and another attribute that makes sense. You may use any of the approaches shown by the book, though the ER-Model and Crow’s foot are the most common. Make sure you show entities, relationships, cardinality, and optionality. Swing Jazz Club The Swing Jazz Club teaches swing and jazz dancing and offers both private and group lessons. Swing Jazz charges $60 dollars per hour for a private lesson and $10 dollars per hour for a group lesson.…arrow_forwardWhat are the differences between weak and strong entity sets?arrow_forward
- Explain how functional dependencies may be used to indicate: • The entity sets student and teacher have a one-to-one relationship set.• Between entity setsstudent and teacher, there is a many-to-one connection set.arrow_forward1. Consider the following relations:Student (snum: integer, sname: string, major: string, level: string, age: integer)Class (name: string, meets at: string, room: string, fid: integer)Enrolled (snum: integer, cname: string)Faculty (fid: integer, fname: string, deptid: integer)The meaning of these relations is straightforward; for example, Enrolled has onerecord per student-class pair such that the student is enrolled in the class. Level is atwo character code with 4 different values (example: Junior: A Level etc)Write the following queries in SQL. No duplicates should be printed in any of theanswers.i. Find the names of all Juniors (level = A Level) who are enrolled in a classtaught by Prof. Kwizeraii. ii. Find the names of all classes that either meet in room R128 or have five ormore Students enrolled.iii. Find the names of all students who are enrolled in two classes that meet atthe same time.arrow_forwardDraw an EER diagram for the following problem using this text's EER notation, the Visio notation, or the subtypes inside supertypes notation, as specified by your instructor. A nonprofit organization depends on a number of different types of persons for its successful operation. The organization is interested in the following attributes for all of these persons: SSN, Name, Address, City /State/Zip, and Telephone. Three types of persons are of greatest interest: employees, volunteers, and donors. Employees have only a Date Hired attribute, and volunteers have only a Skill attribute. Donors have only a relationship (named Donates) with an Iten1 entity type. A donor must have donated one or more items, and an item may have no donors or one or more donors. There are persons other than employees, volunteers, and donors who are of interest to the organization so that a person need not belong to any of these three groups. On the other hand, at a given time a person may belong to two or more…arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
Database System ConceptsComputer ScienceISBN:9780078022159Author:Abraham Silberschatz Professor, Henry F. Korth, S. SudarshanPublisher:McGraw-Hill Education
Starting Out with Python (4th Edition)Computer ScienceISBN:9780134444321Author:Tony GaddisPublisher:PEARSON
Digital Fundamentals (11th Edition)Computer ScienceISBN:9780132737968Author:Thomas L. FloydPublisher:PEARSON
C How to Program (8th Edition)Computer ScienceISBN:9780133976892Author:Paul J. Deitel, Harvey DeitelPublisher:PEARSON
Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781337627900Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
Programmable Logic ControllersComputer ScienceISBN:9780073373843Author:Frank D. PetruzellaPublisher:McGraw-Hill Education
Database System Concepts
Computer Science
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:McGraw-Hill Education
Starting Out with Python (4th Edition)
Computer Science
ISBN:9780134444321
Author:Tony Gaddis
Publisher:PEARSON
Digital Fundamentals (11th Edition)
Computer Science
ISBN:9780132737968
Author:Thomas L. Floyd
Publisher:PEARSON
C How to Program (8th Edition)
Computer Science
ISBN:9780133976892
Author:Paul J. Deitel, Harvey Deitel
Publisher:PEARSON
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781337627900
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Programmable Logic Controllers
Computer Science
ISBN:9780073373843
Author:Frank D. Petruzella
Publisher:McGraw-Hill Education
Enhanced Entity Relationship Model; Author: Data Science Center;https://www.youtube.com/watch?v=ocQUtXPumdQ;License: Standard YouTube License, CC-BY