WEEK 8 ASSIGNMENT create a star schema for the Bob's Fixit Group Case Study . The star schema needs to support the analysis or measurement of material price by suppliers, date, and jobs.
WEEK 8 ASSIGNMENT
create a star schema for the Bob's Fixit Group Case Study . The star schema needs to support the analysis or measurement of material price by suppliers, date, and jobs.
Case Study
Bob owns a small company called Bob’s Home repairs. He does the small home repair jobs that the large companies pass by. Need some woodwork or wood furniture fixed? Call Bob.
Here is how the business works:
Someone calls Bob and asks him to bid on a job. He drives over, looks at the situation, and gives them a bid. Sometimes it is an official looking bid by mail, and sometimes it is scribbled on notebook paper. He decides how long it will take to do the job (he bills by the hour), how much wood will be needed, any odds and ends that are unique to the job, and an overall price. He moves from job to job and bills customers as he finishes the work.
Bob buys items and supplies from a variety of places, but he buys stuff only when it is needed for a particular project. A potential problem: if he gets behind on his payments to various suppliers, then they won't let him order any more. This would stop his business dead in its tracks. His biggest and most crucial supply is lumber (the price rises and falls constantly). So, he must pay all bills within 30 days of receiving them, especially the lumber companies.
Bob is pretty nice to his customers. They don't have to pay until the work is completed and they are satisfied with his work. This has occasionally led to some problems because the cash coming in is sometimes slower than the cash going out, and he would like to have a better idea of when his bills are due and when his customers will be paying.
Currently, all business records are kept in Bob's head and in one file cabinet. Sometimes he forgets which jobs he bid on and how much he bid on them. He doesn't call potential customers to ask about earlier bids, but this could increase business. He wants reports on suppliers that need to be paid and customers that are slow in paying their bills. So, he wants to computerize these aspects of the business to make things more efficient. Can you help him?
An outstanding project will have the following characteristics, with lower grades for projects that lack these things:
- The E-R diagram that clearly identifies the entities, relationships, and attributes. (This will be due Week 3.)
- The tables that align to your E-R diagram that are in third normal form, using good names for the tables and the fields. All tables are populated with data. All appropriate primary and foreign keys are added to your tables and match the E-R diagram. (This will be due Week 5.)
- The star schema tables with all correct fields, primary keys and foreign keys. (This will be due Week 7).
- The star schema tables with sample data inserted in each table. The sample queries executed against the relational tables. (This will be due Week 7).
- The sample analytical queries are executed against the star schema. (This will be due Week 9).
- The entire package is organized in a single Word document for easy viewing and editing, with diagrams and scripts copied into the Word document. Add any appropriate verbiage to help structure and organize your package. Include an introduction letter to Bob including details on the project. You need to format this package as a final software package for Bob.
Here is what you don't need to do:
- You don't need to computerize the entire business, just the processes that Bob finds most bothersome.
- You don't need to create forms for data entry. This is something you would do if hired as a consultant.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 1 images
