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.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
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…
I need help building an ERD in Crow's Foot notation with the following (If possible show me any many-many and optional/mandatory relationship) :
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
- this is an ER Diagram in database question pleasearrow_forwardA 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_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_forward
- Consider 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_forwardIn 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_forward
- Swing 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_forwardExplain 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_forwardDistinguish between the ways in which functional dependencies may be utilized to express the following: • Between the entity sets student and teacher, there is a one-to-one relationship set that exists.• There is a many-to-one connection set between the entity setsstudent and teacher, which is defined asarrow_forward
- Draw an ER diagram and then map it into a relational schema(database) for the following scenario.• For each MEMBER we keep track of the unique MemdID, a well as Name, Zip, and the Datethe membership was paid.• For each MEMBERSHIP type we keep track of the unique Mid, as well as MName and Price• For each PASS CATEGORY we keep track of the unique PassCatID, as well as PCName andPrice• For each ONE DAY PASS we keep track of the unique PassID and Date• For each MERCHANDISE item we keep track of the unique MrchID, as well as Name andPrice• For each sale TRANSACTION we keep track of the unique Tid and Date• Each member pays for exactly one membership type; each membership type has at leastone member but can have many members.• Each member can buy many day passes but does not have to buy any, each day pass wasbought by exactly one member• Each day pass belongs to exactly one pass category; a pass category can have manyindividual day passes issued for it but does not have to have any. • Each…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_forwardTurn the following ER Diagram into a collection of relation schemas. Identify as many of the constraints as possible. Note: Courses is a weak entity set, and Number is a discriminator because it can be used to differentiate different courses once the department Code is given. Courses Depts Number: int. Title: varchar(30) Credit: int Code: char(4) Name: varchar(20) Budget: int GivenBy TaughtBy Instructors WorksIn ID: int FName: varchar(20) LName: varchar(20)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