Developing and Managing Databases for BI Instructor: Darlene Ringhand

Week 1 Homework Student: Manuel Moretti BIAM 530

Developing and Managing Databases for BI Instructor: Darlene Ringhand

September 09th, 2018

 Complete the problems at the end of Chapter 1: Problems 1–10.

 

Complete the problems at the end of Chapter 2: Problems 1–6.

 

Submit your numbered answers to all problems in a single Microsoft Word document. Some of these problems require you to draw entity relationship diagrams (ERDs), or class diagrams. For this assignment, you may sketch these diagrams by hand and insert a photo or scanned image of your sketch in your Word document. Make sure your sketches are legible in the document. If you choose, you may also create these diagrams using the drawing tools in MS Word, or create the diagrams in MS Visio or another application and copy and paste them into your Word document. You will learn more about using MS Visio to create these diagrams in Week 2.

 

Chapter 1

 

FIGURE P1.1: The file structure for Problems 1–4

 

1.            How many records does the file contain? How many fields are there per record?

 

2.            What problem would you encounter if you wanted to produce a listing by city? How would you solve this problem by altering the file structure?

 

3.            If you wanted to produce a listing of the file contents by last name, area code, city, state, or zip code, how would you alter the file structure?

 

4.            What data redundancies do you detect? How could those redundancies lead to anomalies?

 

5.            Identify and discuss the serious data redundancy problems exhibited by the file structure shown in Figure P1.5.

 

FIGURE P1.5:The file structure for Problems 5–8

 

6.            Looking at the EMP_NAME and EMP_PHONE contents in Figure P1.5, what change(s) would you recommend?

 

7.            Identify the various data sources in the file you examined in Problem 5.

 

8.            Given your answer to Problem 7, what new files should you create to help eliminate the data redundancies found in the file shown in Figure P1.5?

 

9.            Identify and discuss the serious data redundancy problems exhibited by the file structure shown in Figure P1.9. (The file is meant to be used as a teacher class assignment schedule. One of the many problems with data redundancy is the likely occurrence of data inconsistencies—two different initials have been entered for the teacher named Maria Cordoza.)

 

 10.         Given the file structure shown in Figure P1.9, what problem(s) might you encounter if building KOM were deleted?

 

You would simply lose/omit all of the data about the teachers assigned to that building code and their courses at that time.

 

Chapter 2

 

Use the contents of Figure 2.1 to work Problems 1–3.

 

FIGURE 2.1: Linking relational tables

 

1.            Write the business rule(s) that govern the relationship between AGENT and CUSTOMER. Many CUSTOMERS can be served by one AGENT but only one AGENT can serve a CUSTOMER.

 

2.            Given the business rule(s) you wrote in Problem 1, create the basic Crow’s Foot ERD.

 

3.            Using the ERD you drew in Problem 2, create the equivalent object representation and UML class diagram. (Use Figure 2.4 as your guide.)

 

 Using Figure P2.4 as your guide, work Problems 4–5. The DealCo relational diagram shows the initial entities and attributes for the DealCo stores, which are located in two regions of the country.

 

 4.           Identify each relationship type and write all of the business rules.

 

5.            Create the basic Crow’s Foot ERD for DealCo.

6.            Identify each relationship type and write all of the business rules.