Download DATABASE SYSTEMS 622 and more Assignments Database Management Systems (DBMS) in PDF only on Docsity!
RICHFIELD GRADUATE INSTITUTE OF TECHNOLOGY (PTY) LTD
FACULTY OF INFORMATION TECHNOLOGY
DATABASE SYSTEMS 622
2ND SEMESTER ASSIGNMENT
Name & Surname : ICAS No : Qualification: BSC IT Semester: 2 Module Name: DATABASE SYSTEMS 622 Date Submitted: 20/04/ ASSESSMENT CRITERIA MARKS ALLOCATION
EXAMINER
MARKS
MODERATED
MARKS
MARKS FOR CONTENTS
QUESTION 1 20
QUESTION 2 30
QUESTION 3 20
QUESTION 4 20
TOTAL MARKS 90
MARKS FOR TECHNICAL ASPECTS
1. TABLE OF CONTENTS
Accurate numbering according to the numbering in text and page numbers.
2. LAYOUT AND SPELLING
Font – Calibri 12 Line spacing - 1. Margin should be justified.
3. REFERENCE
According to the Harvard Method
TOTAL MARKS 10
TOTAL MARKS FOR THE ASSIGMENT 100
Examiner’s Comments: Moderator’s Comments: Signature of Examiner: Signature of Moderator:
TABLE OF CONTENT
Question 1 1.1.1 Define Multidimensional Data Analysis. 1.1.2 Explain To Them The Main OLAP Client/ Server Components And Architectures. 1.1.3 Choosing Between ROLAP And MOLAP. 1.1.4 Explain use of a star schema in the design. 1.1.5 What are attribute hierarchies and aggregation levels. Question 2 2.1. How many database requests can you identify for an inventory update for both PRODUCT and PART? 2.1.2 Using SQL to write each database request you identified. 2.1.3 Write the complete transaction. 2.1.4 Write the transaction log, using Tables. 2.1.5 Using the transaction log created to trace its use in database recovery. 2.2 Three most common concurrent transaction execution problems. 2.3 DBMS component is responsible for concurrency control. 2.4 Describe the database recovery process and the use of deferred-write and write-though techniques. Question 3 3.1 Design a star schema for this problem with database modeling tool. 3.2 Estimate the number of rows in the fact table. 3.3 Estimate the total size of the fact table (in bytes). Question 4. 4.1 Three most common concurrent transaction execution problems. 4.2 Discuss the database recovery process and the use of deferred-write and write-through techniques.
wanting a faster database, MOLAP is faster than ROLAP. If they need access to ad hoc, ROLAP would be better than MOLAP. So the overall requirement of the database depends on the needs required. 1.1. How I would use a star schema in the design is by adding a fact and dimension table in the physical warehouse. I would then have each fact table related to the dimension table in a many to one relationship. 1.1. Attributes hierarchies provide a top-down data organization that is used for two main purposes: aggregation and drill-down/roll-up data analysis” (Rob, 2007). Attribute hierarchies is a set order that the attributes are placed in. With a set order, it makes it more simplistic for searching, editing or calculating and reaching solutions. Aggregates are break downs, whether it be a break-down of year, month, week, or quarter, semi- quarterly or annually. Aggregate breaks down data to better view calculations and to refine what is displayed for a better visualization. References: https://www.coursehero.com/file/p7k59iu/Specifically-it-provides-the-interface-through-which- the-end-user-queries-the/ https://www.coursehero.com/file/p3jrnk2/13-The-project-group-is-ready-to-make-a-final-decision- choosing-between-ROLAP/ QUESTION 2 2.1. There are two correct answers 4 or 2. Depending in how the SQL statements are done. 2.1. The database requests are shown in the following table. Four SQL statements Two SQL statements UPDATE PRODUCT SET PROD_QOH = PROD_OQH + 1 WHERE PROD_CODE = ‘ABC’
UPDATE PRODUCT
SET PROD_QOH = PROD_OQH + 1
WHERE PROD_CODE = ‘ABC’
UPDATE PART
SET PART_QOH = PART_OQH - 1
WHERE PART_CODE = ‘A’
UPDATE PART
SET PART_QOH = PART_OQH - 1
WHERE PART_CODE = ‘B’
UPDATE PART
SET PART_QOH = PART_OQH - 1
WHERE PART_CODE = ‘C’
UPDATE PART
SET PART_QOH = PART_OQH - 1
WHERE PART_CODE = ‘A’ OR
PART_CODE = ‘B’ OR
PART_CODE = ‘C’
The transactions are shown in the following table. Four SQL statements Two SQL statements BEGIN TRANSACTION UPDATE PRODUCT SET PROD_QOH = PROD_OQH + 1 WHERE PROD_CODE = ‘ABC’ UPDATE PART SET PART_QOH = PART_OQH - 1 WHERE PART_CODE = ‘A’ UPDATE PART SET PART_QOH = PART_OQH - 1
BEGIN TRANSACTION
UPDATE PRODUCT
SET PROD_QOH = PROD_OQH + 1
WHERE PROD_CODE = ‘ABC’
UPDATE PART
SET PART_QOH = PART_OQH - 1
WHERE PART_CODE = ‘A’ OR
PART_CODE = ‘B’ OR
PART_CODE = ‘C’
Lost Updates The lost update problem occurs when two concurrent transactions, T1 and T2, are updating the same data element and one of the updates is lost Uncommitted Data The phenomenon of uncommitted data occurs when two transactions, T1 and T2, are executed concurrently and the first transaction (T1) is rolled back after the second transaction (T2) has already accessed the uncommitted data—thus violating the isolation property of transactions Inconsistent Retrievals Inconsistent retrievals occur when a transaction accesses data before and after another transaction(s) finish working with such data. For example, an inconsistent retrieval would occur if transaction T1 calculates some summary (aggregate) function over a set of data while another transaction (T2) is updating the same data. 2. Severe database integrity and consistency problems can arise when two or more concurrent transactions are executed. In order to avoid such problems, the DBMS must exercise concurrency control. The DBMS's component in charge of concurrency control is the scheduler. a) During the read phase , the transaction reads the database, executes the needed computations, and makes the updates to a private copy of the database values. All update operations of the transaction are recorded in a temporary update file, which is not accessed by the remaining transactions. b) During the validation phase , the transaction is validated to ensure that the changes made will not affect the integrity and consistency of the database. If the validation test is positive, the transaction goes to the write phase. If the validation test is negative, the transaction is restarted and the changes are discarded. c) During the write phase , the changes are permanently applied to the database. 2.4. Write-through technique Database is immediately updated by transaction operations during transaction’s execution Recovery process for write-through Identify last checkpoint If transaction was committed before checkpoint Do nothing If transaction committed after last checkpoint DBMS redoes the transaction using “after” values
If transaction had ROLLBACK or was left active Uses the before value in the transaction log records to ROLLBACK (undo) Deferred-write technique Transaction operations do not immediately update physical database Only transaction log is updated Database is physically updated only after transaction reaches its commit point using transaction log information Database Systems, 8th^ Edition Transaction Recovery Recovery process for deferred-write: Identify last checkpoint If transaction committed before checkpoint Do nothing If transaction committed after checkpoint Use transaction log to redo the transaction If transaction had ROLLBACK operation or was left active Do nothing because no updates were made References: http://www.acs.uwinnipeg.ca/rmcfadyen/creativecommons/Relational%20Databases%20and% Microsoft%20Access%20V2.0.pdf https://dcs.abu.edu.ng/staff/sheidu-salami/courses/cosc406/1_Elmasri_Navathe_6th_edition.pdf https://www.coursehero.com/file/p6of8qg/The-project-group-is-ready-to-make-a-final-decision- choosing-between-ROLAP-and/
QUESTION 4
The three main concurrency control problems are triggered by lost updates , uncommitted data , and Inconsistent analysis (non repeatable read) A lost update occurs when two different transactions are trying to update the same column on the same row within a database at the same time. Typically, one transaction updates a particular column in a particular row, while another that began very shortly afterward did not see this update before updating the same value itself. The result of the first transaction is then "lost", as it is simply overwritten by the second transaction. Each transaction is unaware of the other transactions. The last update overwrites updates made by the other transactions, which results in lost data. For example, two editors make an electronic copy of the same document. Each editor changes the copy independently and then saves the changed copy thereby overwriting the original document. The editor who saves the changed copy last overwrites the changes made by the other editor. This problem could be avoided if one editor could not access the file until the other editor had finished and committed the transaction. Uncommitted Dependency (Dirty Read) Uncommitted dependency occurs when a second transaction selects a row that is being updated by another transaction. The second transaction is reading data that has not been committed yet and may be changed by the transaction updating the row. For example, an editor is making changes to an electronic document. During the changes, a second editor takes a copy of the document that includes all the changes made so far, and distributes the document to the intended audience. The first editor then decides the changes made so far are wrong and removes the edits and saves the document. The distributed document contains edits that no longer exist and should be treated as if they never existed. This problem could be avoided if no one could read the changed document until the first editor does the final save of modifications and commits the transaction. Inconsistent Analysis (Non repeatable Read) Inconsistent analysis occurs when a second transaction accesses the same row several times and reads different data each time. Inconsistent analysis is similar to uncommitted dependency in that another transaction is changing the data that a second transaction is reading. However, in inconsistent analysis, the data read by the second transaction was committed by the transaction that made the change. Also, inconsistent analysis involves multiple reads (two or more) of the same row, and each time the information is changed by another transaction; thus, the term non repeatable read. For example, an editor reads the same document twice, but between each reading the writer rewrites the document. When the editor reads the document for the second time, it has changed. The original read was not repeatable. This problem could be avoided if the writer could not change the document until the editor has finished reading it for the last time. What Can Be Done to Prevent a Lost Update? Pessimistic concurrency control A system of locks prevents users from modifying data in a way that affects other users. After a user performs an action that causes a lock to be applied, other users cannot perform actions that would conflict with the lock until the owner releases it. This is called pessimistic
control because it is mainly used in environments where there is high contention for data, where the cost of protecting data with locks is less than the cost of rolling back transactions if concurrency conflicts occur. Optimistic concurrency control One recommended method for preventing lost updates is to use to perform what is called optimistic locking on the data. Optimistic concurrency control typically uses four phases in order to help to ensure that data isn’t lost:
- Begin - A timestamp is recorded to pinpoint the beginning of the transaction.
- Modify - Read values and make writes tentatively.
- Validate - Make a check to ensure that other transactions have not modified any data that is used by the current transaction (including any transactions that have completed or are still active after the current transaction's start time.
- Commit or Rollback - If there are not conflicts, the transaction can be committed. Otherwise, the transaction can be aborted (or other resolution methods can be employed) to prevent a lost update from occurring.
The image below demonstrates what happens when an optimistic lock is used. https://technet.microsoft.com/en-us/library/ms189132(v=sql.105).aspx 4. Recovery restores a database from a given state, usually inconsistent, to a previously consistent state. Depending on the type and the extent of the failure, the recovery process ranges from a Transaction Management and Concurrency. The write-ahead-log protocol ensures that transaction logs are always written before any database data are actually updated.
