Modern Database Management
13th Edition
ISBN: 9780134773650
Author: Hoffer
Publisher: PEARSON
expand_more
expand_more
format_list_bulleted
Concept explainers
Question
Chapter 9, Problem 9.37PAE
Program Plan Intro
(a)
To design:
A star schema for the given information.
Program Plan Intro
(b)
To estimate:
The number of rows in the fact table.
Program Plan Intro
(c)
To estimate:
The total size of the fact table assuming average 15 bytes for each field.
Program Plan Intro
(d)
To find:
Another way to change the design of this data mart.
Program Plan Intro
(e)
To propose:
A design for star schema to allow the slow changes in the entities over the time.
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
Given the information package below, create the dimensional model having a star schema data design. Include the Primary keys (PK) and the Foreign Keys(FK) as needed.
Develop an Entity Relationships Diagram (ERD) based on the relational schemas in the 3NF for the starting relational schema in un-normalized form presented below.
UNF:Order(OrderID, OrderDate, CustID, CustName, CustPhone, CCNum, CCExpDate, CCBank, BnkContName, BnkContPhone, CustEmail, OrderIP, SiteRefFrom, ShipStreet, ShipCity, ShipSt, ShipZip, OrderLineNum, ItemID, ItemName, ItemDesc, ItemQtyOrdered,ItemListPrice, ItemSalePrice, ItemQtyShip, ShipCharge, Tax, TotalDue)
Consider the following Staff relational schema:
STAFF (STAFFNO, NAME, DOB, GENDER, DOJ, DESIGNATION,
BASIC_PAY, DEPTNO)
GENDER must take the Value ‘M’ or ‘F’
DEPT (DEPTNO, NAME)
SKILL (SKILL_CODE, DESCRIPTION, CHARGE_OUTRATE)
STAFF_SKILL (STAFFNO , SKILL_CODE)
PROJECT (PROJECTNO, PNAME, START_DATE, END_DATE,
BUDGET,PROJECT_MANAGER_STAFFNO)
WORKS (STAFFNO, PROJECTNO, DATE_WORKED_ON,
IN_TIME,OUT_TIME)
i. The primary keys are underlined. Identify the foreign keys and draw schema
diagram
ii. Create the above mentioned tables and populate the tables
iii. Note: Read all questions and populate values accordingly.
iv. Include the constraints as mentioned above.
v. Develop a SQL query to list the details of staff who earn less than the
basic pay of all staff.
vi. Create a view that keeps track of DeptNo, DeptName and number of staff
in each department.
vii. Develop a SQL query to list the details of staff who have more than three
skills.
viii. Create an index on StaffNo…
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
- Part 2: Relational data model. Take a subset of the ideas from the conceptual model you constructed in Part 1 and design a simple relationship model similar to the ones we discussed in Module 2, Video 4 Your model should have at least 5 tables You should include at least 20 attributes, or fields, in your model (20 total across all tables, not per table) Your model should be normalized Identify the primary key in each table, and state whether it is a natural or surrogate key For each relationship between tables, identify any foreign keys needed to define the relationship For each table, identify what type of system or systems you think the data might come from, like those we discussed in Module 1, Video 6.arrow_forwardThe many-to-many relationship between entities is not supported by which data model? Please explain the model's advantages and disadvantages.arrow_forwardA derived attribute is a Illustrate. What are the benefits and drawbacks of storing derived attributes?arrow_forward
- Create 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_forwardYou have been asked to design and implement a database for CTU, which includes 3 entities (tables), as follows: Students Faculty Courses Part 1 Create a Word document that includes the following: Identify at least 3 attributes for each entity, including the following: Student's entity should have an attribute for the program the student is enrolled in. Faculty should have an attribute identifying their rank. Properly identify all of the primary keys (use solid underlines). Create an entity relationship diagram (ERD) from the entities. You may use SmartDraw, Visio, Erwin, or even Word to draw the diagram. Take a screenshot (no need if you use Word), and insert it into the Word document. Part 2 Use Microsoft Access to implement the database that you designed in Part 1, and include the following: Create the Student, Faculty, and Course tables in Access. Add a few instances in each table. Create 2 queries for the following objectives: Find the student names for all students who are in the…arrow_forwardMake an Entity relationship diagram and use the Erdplus and apply these requirments Using the Erdplus(use it to make this diagram, the answer should be as a Diagram) Q) Create an entity-relationship diagram (ERD) using min-max notation based on the following datarequirements of the University database:1. The university is organized into colleges, and each college has a unique number, unique name, and a particularfaculty member who is the dean of the college. Each college administers a number of academic departments.2. Each department has a unique name, a unique code number, and a particular faculty member who chairs thedepartment. We need to keep track of the start date when that faculty member began chairing the department.In addition, each department may have several faculties and must be administered by one college.3. A department offers several courses, each of which has a unique course name, a unique code number, a courselevel (Level: this can be coded as 1 for freshman level, 2…arrow_forward
- You have been asked to design part of a database for a hospital. Thehospital managers provided you with an informal description of theirdata which you can find on the next page in Figure 1.(a) Provide an Enhanced Entity-Relationship Model for the hospital’s data model (Figure 1) in form of a diagram in Chen notation. Identify special attributes (including keys) in the diagramand include participation and cardinality constraint annotationsfor your relationships.List and explain any additional assumptions you think you haveto make because the specification was unclear or incomplete.Note that there should not be many such extra assumptions.Marking CriteriaThe following criteria will be used to mark your model:• extent to which your model is correct, minimal (no featuremodelled twice), complete and expressive• correct use of Chen notation (including special attributes)• correct cardinality and participation constraints (includingnotation)• readability and presentation of your answer. You…arrow_forwardExplain why an index is useful only if there is sufficient variety in the values of an attribute?arrow_forwardConsider the E-R diagram for a university database as shown in Figure 1. Detect the strong identifying entitysets.(a) classroom(b) course(c) section(d) prereq(e) timeslotarrow_forward
- Using the ER diagram for Supplier and Part entities provided (attached here), convert the ERD logical design into a physical database using Microsoft Access. Make sure that you have to create both tables, then use "Database Tools"/"Relationship" to set a specific relationship type between two entities. Once you created both tables, type in at least 5 sample records per entity table ( special care needed when you enter sample data in FK field). Perform a Query to obtain "Supplier_ID", "Supplier_Name", "Part_Num", "Part_Name" data from both entity tables. Also create a form and report using any entityarrow_forwardNote: This question has been rejected three times now saying the question is incomplete. That's how the question is. Please do not reject it saying it is incomplete. The previous 4 questions were related. I was asked to provide question 3 but I will provide all of them. Question 1: Create the Entity Relationship (ER) Diagram for the following scenario. We want to maintain a database for a Faculty within a multi-campus regional university. The faculty has a number of schools and each school is characterized by its school-code which is unique, name and campus location. Schools employ professors who are characterized by their unique staff-id, name and the school they work for. A school offers courses that are characterized by their course-code which is unique, course name and the school. There are subjects offered within the faculty and these arecharacterized by unique subject-code, title and the school that offers them. Students enroll into courses that are characterized by student-id…arrow_forwardWhen translating an ER model into database tables a.) all of the entity attributes become the foreign keys. b.) the entity attributes become the tables. c.) for each entity, two tables need to be created. d.) the entity attributes become the fields of the new table. The goal of normalization is to, (please select all that apply): a.) eliminate data duplication. b.) is create only 1:N relationships. c.) to create as few as possible tables to promote efficiency between the tables. d.) construct tables so that each table has a single topic or theme.arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
