To keep track of office furniture, computers, printers, etc. of its different bu
ID: 3670774 • Letter: T
Question
To keep track of office furniture, computers, printers, etc. of its different business divisions in different locations, the FOUNDIT company uses the table structure. The sample values of data are provided for your consideration, but you should also base your answers on the common sense about a corporate environment.
Table: Sample ITEM Records
Attribute Name
Sample
Value
Sample
Value
Sample
Value
Sample
Value
Sample
Value
ITEM_ID
231134-678
342245-225
254668-449
254668-225
231134-449
ITEM_LABEL
HP DeskJet
895Cse
HP Toner
DT Scanner
DT Scanner
HP DeskJet
895Cse
DIVISION_NAME
EAST
EAST
EAST
WEST
WEST
DIVISION_
MANAGER
London
London
London
Manning
Manning
ROOM_#
325
325
123
123
331
BLDG_CODE
NTC
NTC
CSF
NTC
CSF
BLDG_NAME
Nottooclear
Nottoclear
Canseefar
Nottoclear
Canseefar
BLDG_MANAGER
Rightonit
Rightonit
May
May
May
a. Given that information, write the relational schema and draw the dependency diagram. Make sure that you label the transitive and/or partial dependencies.
b. Write the relational schema and create a set of dependency diagrams that meet 3NF requirements. You can create new tables and columns as necessary.
* Present the dependency diagrams in the following format:
ATTRIBUTE 1 --> ATTRIBUTE 2, ATTRIBUTE 6, …
(ATTRIBUTE 1, ATTRIBUTE 3)--> ATTRIBUTE 4, ATTRIBUTE 5, …
…
*. A relational schema mentioned in these problems is just a format used to illustrate the table structure, such as
TABLE NAME (PRIMARY_KEY_ATTRIBUTE1, ATTRIBUTE 2, ATTRIBUTE 3,…, ATTRIBUTE 4)
You can present the tables in this way in your answers.
Attribute Name
Sample
Value
Sample
Value
Sample
Value
Sample
Value
Sample
Value
ITEM_ID
231134-678
342245-225
254668-449
254668-225
231134-449
ITEM_LABEL
HP DeskJet
895Cse
HP Toner
DT Scanner
DT Scanner
HP DeskJet
895Cse
DIVISION_NAME
EAST
EAST
EAST
WEST
WEST
DIVISION_
MANAGER
London
London
London
Manning
Manning
ROOM_#
325
325
123
123
331
BLDG_CODE
NTC
NTC
CSF
NTC
CSF
BLDG_NAME
Nottooclear
Nottoclear
Canseefar
Nottoclear
Canseefar
BLDG_MANAGER
Rightonit
Rightonit
May
May
May
Explanation / Answer
(a) Relational Schema for the given information:
ITEM_RECORDS( ITEM_ID, ITEM_LABEL, DIVISION_NAME, DIVISION_MANAGER, ROOM_#, BLDG_CODE, BLDG_NAME, BLDG_MANAGER );
Dependency Diagram:
(b) Relational SChema that meet 3NF requirements:
ITEM_TABLE( ITEM_ID , ITEM_LABEL);
DIVISION_TABLE( DIVISION_NAME , DIVISION_MANAGER );
BLDG_TABLE( BLDG_CODE , BLDG_NAME);
STORE_TABLE( ROOM_# , BLDG_MANAGER);
FOUNDIT_TABLE( ITEM_ID, DIVISION_NAME, BLDG_CODE, ROOM_#);
Dependency Diagrams:
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.