The annual Manufacturing Repairs report is a month-by-month summary of the
repairs by department within each product line. In order to present this
report to management with minimal delay, it was decided to create a separate
table that holds all the relevant data.
1. What would the structures of this table be?
This could be one of the possible solutions:
Report Table
Parm
Type
Spec
RepairID
int
not null
AnyRepair
varchar 50
not null
Date
datetime
not null
DeptID
int
not null
AnyDept
varchar 50
not null
ProductID
int
not null
AnyProd
varchar 50
not null
2. What would the underlying structure for this data be? (What would the
original data look like before it was summarized?)
This could be a possible state before being summarized:
Repair
RepairID
PK
AnyRep
Date
Department
DeptID
PK
AnyDept
Product
ProdID
PK
AnyProd
3. Suppose management would want to drill down into the report to get more
detail (such as the actual product repaired), does this change the answer
for question 1? If so, describe the changes.
Not really. There are some different available options, like queries.