Objectives:
This tutorial will cover:
INFO20003 Tutorial – Week 10
(Tutorial: Database Administration and Transactions)
Copyright By PowCoder代写 加微信 powcoder
I. Apply capacity planning concepts – 10 minutes
II. Review of backup & recovery concepts – 15 minutes
III. Apply backup & recovery concepts to case studies – 10 minutes
IV. Review transactions concepts – 10 minutes
V. Apply transactions concepts – 15 minutes
Exercises:
1. Capacity planning
Consider the case of a department store. An analyst has determined that there are 50 distinct suppliers that provide 2000 distinct items to the store. They have determined that the average delivery is of 40 distinct items and that each supplier delivers approximately once a week (the analyst has estimated this to be 50 deliveries a year). For each delivery by a supplier, there are on average 40 rows added to the DeliveryItem table. While the Item and Supplier tables stay constant in size, the DeliveryItem table grows by 100,000 rows every year.
This assumes that suppliers and items stay constant; however, if the business is successful, the suppliers and frequency of deliveries and number of distinct items delivered can be expected to grow. If we know the length of each row, we can estimate how big each table will be year by year.
Using information about data type storage from the MySQL documentation and information from the data dictionary, the analyst has determined the following average row lengths of each table:
DeliveryItem
Number of rows
Average row length
Table 1: Row volume and row length for the Supplier delivers Item entities.
Assume that the number of suppliers and number of items do not change from year to year, and that the delivery schedule remains the same. Calculate the size of the three tables:
a. When database use begins (year 0)
b. After one year of database use
c. After five years of database use
INFO20003 Tutorial – Week 10 1
Key Concepts:
• Why do we need a backup of our database?
• Backup planning
o Backup type – logical or physical
o Backup mode – online or offline
o Backup location – onsite or offsite
o Full backups and incremental backups
• How does database recovery work? Exercises continued:
2. Backup and recovery case study
ACME Manufacturing makes widgets in its factory. The factory runs 24 hours a day, 7 days a week in three shifts. The quietest shift is the Sunday night shift, which runs from midnight Sunday to 8am Monday. While ACME manufactures widgets, the database must run. This is ACME’s only widget factory.
The database administrator has implemented a backup policy that takes a full backup every Sunday at 3am during the night shift, and then an incremental backup on Tuesday, Thursday and Saturday mornings at 3am.
The backup strategy has determined that if there is a database failure, restoration of the database is time-critical. ACME must have the shortest outage time to restore and recover the database. This means the database must be restored quickly so that the manufacturing can continue. ACME must have the smallest elapsed time from the point of failure to the database being fully operational and useable.
a. Given the business requirements and the database administrator’s backup policy, what database backup type, mode and site would you recommend?
b. Consider the Full and Incremental backup timeline in Figure 1. If the database suffered a media failure on Friday at 9:23am, how many backups would need to be restored?
All data in the DB
All data in the DB
Sunday Tuesday Thursday
Media failure
Figure 1. A timeline of full and incremental backups showing the media failure on Friday morning.
Saturday Sunday
INFO20003 Tutorial – Week 10 2
Length and size of backup
c. Given the same failure, what would be the benefits and costs of changing the backup strategy to do full backups on Sunday, Tuesday, Thursday and Saturday mornings at 3am?
Key Concepts:
• What is a transaction?
• Concurrency
• The lost update problem
Exercises continued: 3. Transactions
It’s class registration day, when UniMelb students register in tutorial classes for the upcoming semester. In one particular subject, each tutorial class can fit a maximum of 24 students.
Eamonn and Jacqueline both wish to register in the Wednesday 10am tutorial class for this subject. This class already has 23 students enrolled – just one place remains.
Suppose the database contains tables like this:
TutorialClass (SubjectCode, TutorialNumber, TotalEnrolments)
FK FK FK TutorialEnrolment (SubjectCode, TutorialNumber, StudentNumber)
a. Describe how a lost update could occur in this database when Eamonn and Jacqueline try to simultaneously register in the Wednesday 10am tutorial.
b. How could the lost update problem be avoided in this situation? END OF TUTORIAL
INFO20003 Tutorial – Week 10 3
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com