Chapter 12, Problem 3P

Explanation of Solution

a. Recommendations of required database system:

Recommendations make regarding the type and characteristics of the required data base system are as follows:

• • The magazine publishing organization wants a distributed system with distributed database capabilities.
• • The distributed system will be distributed among the organization locations in Tennessee, South Carolina, Florida, and Georgia.
• • The distributed transparency features such as transaction transparency, fragmentation transparency, performance transparency, and replica transparency are supported by the Distributed Database Management System (DDBMS).
• • Heterogeneous competency is not a compulsory feature since the customers assume there is no existing Database Management System (DBMS) in place and that the organizations needs to normalize on a single DBMS.

Explanation of Solution

b. Data fragmentation needed for tables:

The data fragmentation needed for “CUSTOMER”, and “INVOICE” table is as follows:

The database is horizontally partitioned using the “REGION” attribute for the “INVOICE” table and the “STATE” attribute for the “CUSTOMER” table.

Explanation of Solution

c. Criteria used to partition the database:

The criteria “horizontal fragmentation” is used to partition the database.

Horizontal fragmentation of the “INVOICE” table by region is as follows:

Fragment name	Location	Condition	Node name
I1	Tennessee	REGION_CODE = “TN”	NAS
I2	Georgia	REGION_CODE = “GA”	ATL
I3	Florida	REGION_CODE = “FL”	TAM
I4	South Carolina	REGION_CODE = “SC”	CHA

Horizontal fragmentation of the “CUSTOMER” table by state is as follows:

Fragment name	Location	Condition	Node name
C1	Tennessee	REGION_CODE = “TN”	NAS
C2	Georgia	REGION_CODE = “GA”	ATL
C3	Florida	REGION_CODE = “FL”	TAM
C4	South Carolina	REGION_CODE = “SC”	CHA

Explanation of Solution

d. Database fragments:

Following are the database fragments with node names, location, fragment names, attribute names, and demonstration data.

Fragmentation of “INVOICE” table:

Fragment “I1” of “INVOICE” table with location “Tennessee”, and node “NAS” is as follows:

INV_NUM	REGION_CODE	CUS_NUM	INC_DATE	INV_TOT
213342	TN	10884	1-NOV-15	45.95
209987	TN	10993	15-FEB-16	45.95

Fragment “I2” of “INVOICE” table with location “Georgia”, and node “ATL” is as follows:

INV_NUM	REGION_CODE	CUS_NUM	INC_DATE	INV_TOT
198893	GA	11887	15-AUG-15	70.45
224345	GA	13558	1-JUN-16	45.95

Fragment “I3” of “INVOICE” table with location “Florida”, and node “TAM” is as follows:

INV_NUM	REGION_CODE	CUS_NUM	INC_DATE	INV_TOT
200915	FL	10014	1-NOV-15	45.95
231148	FL	15998	1-MAR-16	24.95

Fragment “I4” of “INVOICE” table with location “South Carolina”, and node “CHA” is as follows:

INV_NUM	REGION_CODE	CUS_NUM	INC_DATE	INV_TOT
243312	SC	21562	15-NOV-15	45.95
231156	SC	18776	1-OCT-16	45.95

Fragmentation of “CUSTOMER” table:

Fragment “C1” of “CUSTOMER” table with location “Tennessee”, and node “NAS” is as follows:

CUS_NUM	CUS_NAME	CUS_ADDRESS	CUS_CITY	CUS_STATE	CUS_SUB_DATE
10884	James D.Burger	123 Court Avenue	Menphis	NV	8-DEC-16
10993	Lisa B.Barnette	910 Eagle Street	Nashville	NV	12-MAR-17

Fragment “C2” of “CUSTOMER” table with location “Georgia”, and node “ATL” is as follows:

CUS_NUM	CUS_NAME	CUS_ADDRESS	CUS_CITY	CUS_STATE	CUS_SUB_DATE
11887	Ginny E.Statton	335 Main street	Atlanta	GA	11-AUG-16
10993	Anna H.Ariona	657 Mason Ave.	Dalton	GA	23-JUN-17

Fragment “C3” of “CUSTOMER” table with location “Florida”, and node “TAM” is as follows:

CUS_NUM	CUS_NAME	CUS_ADDRESS	CUS_CITY	CUS_STATE	CUS_SUB_DATE
10014	John T.Chi	456 Brent Avenue	Miami	FL	18-NOV-16
15998	Lisa B.Barnette	234 Ramala Street	Tampa	FL	23-MAR-17

Fragment “C4” of “CUSTOMER” table with location “South Carolina”, and node “CHA” is as follows:

CUS_NUM	CUS_NAME	CUS_ADDRESS	CUS_CITY	CUS_STATE	CUS_SUB_DATE
21562	Thomas F.Matto	45 N.Pratt Circle	Charleston	SC	2-DEC-16
18776	Mary B.Smith	526 Boone Pike	Charleston	SC	28-OCT-17

Explanation of Solution

e. Distributed database operations supported at each remote site:

The following table show the map of the location, the fragments at each location, and the type of transaction or request support to need to access the data in the distributed database.

Fragment	NAS	ATL	TAM	CHA
INVOICE	I1	I2	I3	I4
CUSTOMER	C1	C2	C3	C4
Distributed operations required	None	None	None	None

From the above table, there is no interstate access of “INVOICE” or “CUSTOMER” data is required. Therefore, there is no distributed database access is required in the four nodes such as “NAS”, “ATL”, “TAM”, and “CHA”.

Explanation of Solution

f. Distributed database operations supported at headquarters site

The following table show the map of the location, the fragments at each location, and the type of transaction or request support to need to access the data in the distributed database.

Fragment	NAS	ATL	TAM	CHA	Headquarters
INVOICE	I1	I2	I3	I4
CUSTOMER	C1	C2	C3	C4
Distributed operations required	None	None	None	None	Distributed request

For the headquarters, the manager needs to able to access the data in all four nodes such through a single SQL request. Therefore, the Distributed Database Management System (DDMS) must support distributed requests.