Question

The company publishes one regional magazine each in Upper North island (UN), Lower North island (LN), Upper South island (US), and Lower South island (LS). The company has 300,000 customers (subscribers) distributed throughout the four regions. On the first of each month, an annual subscription INVOICE is printed and sent to each customer whose subscription is due for renewal. The INVOICE entity contains a REGION attribute to indicate the customer’s region of residence (UN, LN, US, LS):

CUSTOMER (CUS_NUM, CUS_NAME, CUS_ADDRESS, CUS_CITY, CUS_REGION, CUS_SUBSDATE)

INVOICE (INV_NUM, INV_REGION, CUS_NUM, INV_DATE, INV_TOTAL)

The company is aware of the problems associated with centralized management and has decided that it is time to decentralize the management of the subscriptions in its four regional subsidiaries. Each subscription site will handle its own customer and invoice data. The management at company headquarters, however, will have access to customer and invoice data to generate annual reports and to issue ad hoc queries, such as:

•           List all current customers by region.

•           List all new customers by region.

•           Report all invoices by customer and by region.

Given these requirements, how must you partition the database? How many fragments will you create? Design the database fragments for the INVOICE table and show two rows of sample data for each fragment.

Answer 1

Fragmentation of Customer table by Region

FRAGMENT NAME	LOCATION	CONDITION
C1	UN(upper north island)	CUS_REGION='UN'
C2	LN(lower north island)	CUS_REGION='LN'
C3	US(upper south island)	CUS_REGION='US'
C4	LS(lower south island)	CUS_REGION='LS'

Fragmentation of Invoice table by Region

FRAGMENT NAME	LOCATION	CONDITION
I1	UN(upper north island)	INV_REGION='UN'
I2	LN(lower north island)	INV_REGION='LN'
I3	US(upper south island)	INV_REGION='US'
I4	LS(lower south island)	INV_REGION='LS'

EXAMPLES:

FRAGMENT: C1 REGION: UN

CUS_NUM	CUS_NAME	CUS_ADDRESS	CUS_CITY	CUS_REGION	CUS_SUBSDATE
101	JACK	123, xyz street	X	UN	8-dec-07
193	ALISSA	1, mnp street	Y	UN	9-sep-07

FRAGMENT : C2 REGION: LN

CUS_NUM	CUS_NAME	CUS_ADDRESS	CUS_CITY	CUS_REGION	CUS_SUBSDATE
106	JAMES	123, x street	A	LN	18-dec-07
187	LISA	13, mp street	B	LN	19-sep-07

FRAGMENT : C3    REGION: US

CUS_NUM	CUS_NAME	CUS_ADDRESS	CUS_CITY	CUS_REGION	CUS_SUBSDATE
111	KEVIN	123, G street	P	US	8-oct-07
176	CARA	1, p street	Q	US	12-sep-08

FRAGMENT : C4 REGION : LS

CUS_NUM	CUS_NAME	CUS_ADDRESS	CUS_CITY	CUS_REGION	CUS_SUBSDATE
23	JACK	12, baker street	R	LS	28-aug-08
245	ALISSA	13, l street	S	LS	9-sep-07

FRAGMENT : I1 REGION : UN

INV_NUM	INV_REGION	CUS_NUM	INV_DATE	INV_TOTAL
21222	UN	101	12-nov-07	70
34241	UN	193	1-dec-08	65

FRAGMENT : I2 REGION : LN

INV_NUM	INV_REGION	CUS_NUM	INV_DATE	INV_TOTAL
23411	LN	106	11-dec-07	50
37538	LN	187	11-dec-08	73

FRAGMENT : I3 REGION : US

INV_NUM	INV_REGION	CUS_NUM	INV_DATE	INV_TOTAL
26565	US	111	12-nov-07	67
23444	US	176	11-oct-08	32

FRAGMENT : I4 REGION : LS

INV_NUM	INV_REGION	CUS_NUM	INV_DATE	INV_TOTAL
12575	LS	23	12-oct-08	70
45451	LS	245	21-dec-07	65