Database Systems 11 th Edition Author: Coronel, Carlos; Morris, Steven Chapter 1
ID: 3694170 • Letter: D
Question
Database Systems 11th Edition Author: Coronel, Carlos; Morris, Steven
Chapter 12 Problems: Need answer to Question 3. a, b, c, e, f
2. The following data structure and constraints exist for a magazine publishing company:
a. The company publishes one regional magazine in each of four states: Florida (FL), South Carolina (SC),
Georgia (GA), and Tennessee (TN).
b. The company has 300,000 customers (subscribers) distributed throughout the four states listed in Problem 2a.
c. On the first day 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
state of residence (FL, SC, GA, TN):
CUSTOMER (CUS_NUM, CUS_NAME, CUS_ADDRESS, CUS_CITY, CUS_ZIP, 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 to decentralize management of the subscriptions into the company’s 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:
• Listing all current customers by region
• Listing all new customers by region
• Reporting all invoices by customer and by region
Given those requirements, how must you partition the database?
3. Given the scenario and requirements in Problem 2, answer the following questions:
a. What recommendations will you make regarding the type and characteristics of the required database system?
b. What type of data fragmentation is needed for each table?
c. What criteria must be used to partition each database?
e. What type of distributed database operations must be supported at each remote site?
f. What type of distributed database operations must be supported at the headquarters site?
Explanation / Answer
Hello user, I will gladly answer your question.
3.a) It is needed that each regional subsidiarie, have secure access method to its database, due that the headquarters need to access the information of all the subsidiaries, and need to avoid inconsistent data if one subsidiarie modify one or more rows while the headquarters are making queries to extract data.
Besides, they need to establish scheduled backups and each subsidiarie, or in case of a replicated data, it is needed to established scheduled replications in the database for later access.
3.b) As the situation denotes, it is needed to fragment each table data accordingly to each subsidiarie customers pool, because it would be useless to have one FL subscriptor in the GA database.
3.c) The criteria would be the geographical location of the customers and invoices.
3.e) The following operations must be suppported: In the case of a customer removing its subscription, it is needed to support the cancelation of service; in case of change of residence to another Region in which operates the company, the company must support the "migration" of the user from one subsidiarie database to the other one.
3.f) It is needed that the headquarters support the access to the information to make the queries that are needed to collect the information, which can be used to analysis, for example. This support require the Consistency of the information, and the blocking of access while the data is beign modified by the owner subsidiarie.
If you have any doubts, you can contact me by this way. Have a nice day! :D
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.