Exam Preparation DataBase Concurrency Problem

Concurrency Problems

1. Concurrency problems

• If locking is not available and several users access a database concurrently, problems may occur
• If their transactions use the same data at the same time.
• 
  
• Lost updates.
• Uncommitted dependency (dirty read).
• Inconsistent analysis (non-repeatable read).
• Phantom reads.
• http://msdn.microsoft.com/en-us/library/aa213029%28SQL.80%29.aspx

2. Lost update

• Successfully completed update is overridden by another user.

3. Uncommitted dependency (dirty read).

• Occurs when one transaction can see intermediate results of another transaction before it has committed.

4. 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.

5. Inconsistent analysis (non-repeatable read) example
6. Phantom Reads

• Records that appear in a set being read by another transaction.
• Phantom reads can occur when other transactions insert rows that would satisfy the WHERE clause of another transaction's statement.
• http://db.apache.org/derby/docs/10.0/manuals/develop/develop71.html

7. Phantom Reads example
8. Back to Marcia's Drycleaning

• Stored Procedure 01Insert a new record in SERVICE table
• Stored Procedure 02 Insert a new record or UPDATE in ORDER and ORDER_ITEM table Also select data from CUSTOMER and SERVICE
• Stored Procedure 03 Insert a new record in CUSTOMER table
• Concurrency problems may occur between 01 and 02, between 02 and 03.

9. Dirty read SP01(SP03)and SP02

• SP02 trusts SERVICE table data, but it has not been committed yet.

10. Non-repeatable reads SP02 and User Operation

• Non-repeatable reads may occur between SP02 and other user operations.

11. Phantom read SP01(SP03) and SP02

• SP02 trusts SERVICE table data, but it has not been committed yet.

12. Appropriate concurrency control

• SP01 INSERT SERVICE Read Committed
• SP03 INSERT CUSTOMER Read Committed
• SP02 INSERT ORDER or UPDATE and SELECT All table Serializable

If you are interested in my PPT, please check it:)
https://docs.google.com/leaf?id=0B8gU4bWkOtLvNjlhNTA1YWYtMGY0Zi00YTA2LWIwYTYtYjdhYzY4ZDE1Y2Vj&hl=en

Exam Preparation Database SQL Query

Create Stored Procedure

CREATE
PROCEDURE usp_AddCustomer

@CustomerID INT,

@FirstName    VARCHAR(30),

@LastName    VARCHAR(30),

@Phone        VARCHAR(20),

@Email        VARCHAR(30)

AS

INSERT
INTO CUSTOMER(CustomerID,FirstName,LastName,Phone,Email)

VALUES

(@CustomerID, @FirstName, @LastName, @Phone,@Email);

 

EXEC usp_AddCustomer
'8',
'John','Smith','723-555-3432','John@somewhere.com';

 

SELECT
*

FROM CUSTOMER

WHERE CustomerID=8;

 

Create Trigger part1

 

CREATE
TRIGGER reminder

ON CUSTOMER

AFTER
INSERT

AS
RAISERROR ('Notify new Customer inserted', 16, 10);    

 

INSERT
INTO CUSTOMER

VALUES

(8,'Mick',
'Jager','723-599-1111','MJager@somewhere.com');

 

 

Create Trigger part2

 

CREATE
TRIGGER SetOrederItem1

ON CLOTH_ITEM

AFTER
INSERT

AS

    DECLARE

        @OrderID        INT,

        @ItemNo        INT,

        @Item            VARCHAR(30),

        @Quantity        INT,

        @UnitPrice        MONEY,

        @S_ItemNo        INT,

        @S_UnitPrice    MONEY

    SELECT @ItemNo=ItemNo, @UnitPrice=UnitPrice

    FROM inserted i

 

    SELECT @S_ItemNo=S.ItemNo,@S_UnitPrice=S.UnitPrice

    FROM
SERVICE S

    WHERE S.ItemNo=@ItemNo

 

IF (@ItemNo=@S_ItemNo)AND(@UnitPrice<>@S_UnitPrice)

BEGIN

        RAISERROR ('Price is different from SERVICE table', 16, 1)

        ROLLBACK
TRANSACTION

END

ELSE

BEGIN

        PRINT'The modification excuted'

END;

 

INSERT
INTO CLOTH_ITEM

VALUES

(2009006,6,'Suit-Mens',10,'$6.00');

 

 

INSERT
INTO CLOTH_ITEM

VALUES

(2009008,6,'Suit-Mens',10,'$9.00');

 

 

Create View

 

CREATE
VIEW OrderSummaryView

AS
SELECT OrderID,DateIn,DateOut,TotalAmount

FROM ORDER_RECORD;

Data Base week12_1

12/04/2010

Hi there,

I back again this class for theoretical exam:)

 
 

Concurrency Problems

1. Concurrency problems

If locking is not available and several users access a database concurrently, problems may occur

If their transactions use the same data at the same time.

Lost updates.

Uncommitted dependency (dirty read).

Inconsistent analysis (non-repeatable read).

Phantom reads.

http://msdn.microsoft.com/en-us/library/aa213029%28SQL.80%29.aspx

2. Lost update

Successfully completed update is overridden by another user.

3. Uncommitted dependency (dirty read).

Occurs when one transaction can see intermediate results of another transaction before it has committed.

4. 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.

5. Inconsistent analysis (non-repeatable read) example

6. Phantom Reads

Records that appear in a set being read by another transaction.

Phantom reads can occur when other transactions insert rows that would satisfy the WHERE clause of another transaction's statement.

http://db.apache.org/derby/docs/10.0/manuals/develop/develop71.html

7. Phantom Reads example

8. Back to Marcia's Drycleaning

Stored Procedure 01

Insert a new record in SERVICE table

Stored Procedure 02

Insert a new record in ORDER and ORDER_ITEM table

Also select data from CUSTOMER and SERVICE

Stored Procedure 03

Insert a new record in CUSTOMER table

Concurrency problems may occur between 01 and 02, between 02 and 03.

9. Dirty read SP01(SP03)and SP02

SP02 trusts SERVICE table data, but it has not been committed yet.

10. Non-repeatable reads SP02 and User Operation

Non-repeatable reads may occur between SP02 and other user operations.

10. Phantom read SP01(SP03) and SP02

SP02 trusts SERVICE table data, but it has not been committed yet.

11. Appropriate concurrency control

SP01 INSERT SERVICE

Read Committed

SP03 INSERT CUSTOMER

Read Committed

SP02 INSERT ORDER and SELECT All table

Serializable

Data Base week9_4 Extra class

26/03/2010

 

Transaction Management

Concurrency Control

 

HW Research

Compare the features of Oracle and SQL Server, under the headings below:

• Name three different programming interfaces that applications programmers can use to process data in each DBMS from external programs written in C++, Java, Visual Basic or other languages. 
  
• For each DBMS, identify and name one query tool supplied with the DBMS and name the language it uses.
• SQL Server and Oracle both provide similar data access tools. Consider an environment where both products are in use. What differences if any would we expect to see in physical database designs between the two products? How would this impact the data management role?
• For each DBMS, name a feature to support distributing read-only data and additionally to support distributing data for updating remotely.
• Describe THREE types of distributed update conflict problem that may occur, and one strategy used to manage this.
• For each DBMS, name two logging and archiving features available to support database recovery and/or audit.
• For each DBMS, describe what option(s) must be chosen to ensure log files are created.
• Describe what implementation strategies would be required to ensure no data is lost in the event of a media failure. Your answer should refer to backup, recovery, audit.
• Describe the options each DBMS has to support concurrent transaction processing. For each option, give the range of values available.
• From your answers to part(i), state the options you would choose for consistent processing in each database if you were required to implement ACID (atomic, consistent, isolated and durable) transactions. 
• Describe one consequence to the business of choosing options that fail to guarantee ACID transactions in a multi-user environment. 

 

Data Base week9_3

24/03/2010

 

DataBase Architecture Presentation

 

Research

1. A common businesses requirement is to limit or deny access to certain types of data. Explain why this is necessary. Refer to data access, security and privacy in your answer.
   
2. Describe two ways using a DBMS can limit access to data. How completely does this meet typical business expectations to control data access, security and privacy?
   
3. 
   
   
   Business information is rarely for the use of one individual alone. Usually, a range of people in an organization would need to have access to each item of information. So, businesses strive to share access among appropriate groups.
   1. Give one outcome for business risk as shared access grows. What effect does introducing a security policy have on this outcome?
      
   2. authorized usersGive one outcome for business performance as shared access grows.
      
   3. Give one outcome for database management system performance as shared access grows.
      
4. Define data integrity. Give one common cause of poor data integrity. Explain why this can present problems to businesses.
   
5. Why does integrating flat file data in a database improve data integrity? Explain why this alone cannot guarantee data integrity.
   
6. Provide one business benefit of integrating data in this way.
   
7. Give three features of DBMS that are designed to improve reliable access to data in the event of system problems or failures. State how these lower the risk of data loss. 
   
8. State how these features affect the reliability and risk of the business.
   
9. 
   
   
   Following are the five tasks performed by database administrators that aid in data management in an organization
   
   
   

Task	Business impact	Strengths & weaknesses of the ways DBMS support these tasks
managing database structure
controlling concurrent processing
managing processing rights and responsibilities
developing database security
providing for database recovery
managing the DBMS maintaining the data repository

 

 

Data Base week9_2

23/03/2010

 

Exam Feed Back

 

Draw UML

Develop a UML Class Diagram for the following scenario. Use aggregation and /or composition where appropriate

 

Data Base week9_1

22/03/2010

 
 

Exam	Sum up understanding
Test	Format - NZQA

 
 

Next Test

UML	Question Form	about 5 Classes
XML	Script - tree diagram	Tree diagram - script

 
 

Draw UML class diagram

Data Base week8_3

17/03/2010


 
Exam 19/03/2010 9:00-12:10


 
Checklist for Exam

• 
  
  Anomaly
  
  • Update
    
  • Delete
    
  • Insert
    
• Entity integrity
  
• Referential integrity
  
• Identify entity
  
• Draw ERD
  
• Data Dictionary
  

 

Data Base week8_2

16/03/2010

 
 

UML

◆composition

◇aggregation

 

Draw ERD & UML from Scenario

Data Base week8_1

15/03/2010

1. ERD
   
2. XML
   

    
    

   1. ERD
      

      identifying: FK&PK
      

      non-identifying: FK
      

       
       

      Awesome car scenario
      

       
       

   2. XML
      

   Pros.
   

• Transportability
  
• Accessibility
  
• Many device use
  

   
   

   
   

Data Base week7_3

10/03/2010

 

inheritance = generation

 

1. is part of
   
2. 
   
   
   has
   
   
   
3. 
   
   
   is part of
   
   car◆--------------engine
   
   
   
   Circle◆------------point
   
   Polygon◆------------|
   
   
   
4. 
   
   
   has
   
   Person◇-----------address
   

Data Base week7_2

09/03/2010


 
Web Interface ---------- Programming ----------- Data Base


 
input                                     class                            table

name                                 name                            name

address                             address                        address

phone                                phone                            phone

email                                 email                             email

 

UML: Dynamic

Person

ID

Name

Address

Phone

Email

#get

#set


 
ERD: Persistent

Person

ID

Name

Address

Phone

Email


 
UML

• classname
  
• attribute
  
• 
  
  operation
  
  
  
• inheritance
  
• composition
  
• aggregation
  

Data Base week7_1

08/03/2010

 

Practical Exam 4hours

• Scenario - ERD
  
• Implementation (DDL, DML)
  
• Store processer
  
• Trigger
  
• Creating View
  

Theory Exam

• part 1 54 questions
  
• part 2 TBA
  
• part 3 TBA
  

 
NZQA No

6800 - 7.407 Exam

ERD

Normalization

Integrity

6725-7.408 Exam

Practical

Theoretical

 

UML


 
User web page ---- Programming ---- Data Base

Many process Security, Data integrity


 
Object Oriented Program Concept - UML

Data Base week6_4 (Extra class)

Discuss about exam

Exam

3hours

 

• ER Diagram
  
• Data Dictionary
  
• Table Definition
  
• 
  
  Explain using real example
  
  • Insert Anomaly
    
  • Update Anomaly
    
  • 
    
    Delete Anomaly
    
    
    
    
  • Function Dependency
    
  • 
    
    Transitive Dependency
    
    
    
  • 1st Normal Form
    
  • 2nd Normal Form
    
  • 
    
    3rd Normal Form
    
    
    
  • Entity Integrity
    
  • Referential Integrity
    

Data Base week6_3

03/03/2010

 

Preparation for exam

• 
  
  Scenario
  
  • ER Diagram
    
  • Data dictionary
    
  • 
    
    Table definition
    
    
    
    
• 
  
  Normalisation (Explain using example table)
  
  • 
    
    Insert Anomaly
    
    • If a new student enrols, their data have to be inserted without grade or some data.
      
  • 
    
    Modification Anomaly
    
    • If Cooke's email address is changed, we have to change this data 2 times. So, typing miss may be occurred.
      
  • 
    
    Deletion Anomaly
    
    • 
      
      If Lau quits this school, we will lose vital data.
      
      
      
      
  • 
    
    Functional Dependency (X → Y)
    
    • StudentID - StudentName
      
    • StudentID - StudentEmail
      
    • ClassID - ClassName
      
    • StudentID, ClassID - Grade
      
  • 
    
    Transitive Dependency (A→B, B→C, Thus A→C)
    
    • 
      
      ClassID - TermID - Term
      
      
      
      
  • 
    
    Entity Integrity
    
    • Every table must have primary key and the primary key must be unique
      
  • 
    
    Referential Integrity
    
    • Referential Integrity means if you use foreign key, the primary key, which is referred by the foreign key, must be exist in the original table.
      

Data Base week6_2

02/03/2010

 

ITEX

ITEX is a store in Auckland that assembles and sells a range of IBM compatible computers to resellers and individual customers on demand.

ITEX purchase parts from a variety of suppliers, both in NZ and overseas and use the parts to assemble a range computers based on the orders. A part may be supplied by many suppliers and each supplier may supply many parts. Once supplied, a supplier's invoice will have at least one line item and obviously the line item will belong to only one invoice. The line item will represent one part because each part may be involved in many invoice line items.

ITEX uses the parts to build computers based on the customer orders. So each computer is made up of one or more parts and a part may be used in many such computers.

When the computer is ready an invoice is generated. Each sales invoice has at least one invoice line item. Obviously the line-item will belong to only one invoice. A line item will be for one computer and so each such computer may be involved in many invoice line items. A sales invoice must be for one customer.

These invoices are based on the customer orders. An order from a customer must be for at least one computer. Such a computer may be involved in many customer orders. An order may generate one or more invoices.

• Identify the main entity types of ITEX.
  
• Identify the main relationship types between the entity types described in (a) and represent each relationship as an ER diagram.
  
• Determine the multiplicity constraints for each relationship described in (b). Represent the multiplicity for each relationship in the ER diagrams created in (b).
  
• Identify attributes and associate them with entity or relationship types. Represent each attribute in the ER diagrams created in (c).
  
• Determine candidate and primary key attributes for each entity.
  
• Using your answers (a) to (e) attempt to represent the data requirements of ITEX computer store as a single ER diagram. State any assumptions necessary to support your design.
  

 

Data Base week6_1

01/03/2010

 

Auckland Business School

Auckland Business School employs many teachers to teach their various courses each year. A teacher can teach several different courses as long as he or she is qualified to teach the material. Each course must have one teacher.

Students from different programmes enroll themselves into courses. For example, first Semester courses are offered as core courses and the students from Bachelors programme and Graduate/Post Graduate Diploma will have to do. Course, therefore, can be attended by several different students.

At the beginning of each semester, courses are assigned to individual classrooms where the class meets on a regular basis

• 
  
  Identify the main entity types of the Auckland Business School.
  Teacher, Qualification, Course, Program, Classroom, Enrollment, Student
  
• Identify the main relationship types between the entity types described in (a) and represent each relationship as an ER diagram.
  
• Determine the multiplicity constraints for each relationship described in (b). Represent the multiplicity for each relationship in the ER diagrams created in (b).
  
• Identify attributes and associate them with entity or relationship types. Represent each attribute in the ER diagrams created in (c).
  
• Determine candidate and primary key attributes for each (strong) entity type.
  
• Using your answers (a) to (e) attempt to represent the data requirements of the Auckland Business School as a single ER diagram.
  
• State any assumptions necessary to support your design.
  

 

Data Base week5_3

008 Semester 1, 2010 - Week 4 Case Study 1 Ver.1.0

Read the following case study, which describes the data requirements for a ViewMe™ video rental company. ViewMe™ video rental company has several branches throughout Auckland. The data held on each branch is the branch address made up of street, city, state, and zip code, and the telephone number. Each branch is given a branch number, which is unique throughout the company. Each branch is allocated staff, which includes a Manager. The Manager is responsible for the day-today running of a given branch. The data held on a member of staff is his or her name, position, and salary. Each member of staff is given a staff number, which is unique throughout the company.

Each branch has a stock of videos. The data held on a video is the catalog number, video number, title, category, daily rental, cost, status, and the names of the main actors, and the director. The catalog number uniquely identifies each video. However, in most cases, there are several copies of each video at a branch, and the individual copies are identified using the video number. A video is given a category such as Action, Adult, Children, Drama, Horror, or Sci-Fi. The status indicates whether a specific copy of a video is available for rent.

Before hiring a video from the company, a customer must first register as a member of a local branch. The data held on a member is the first and last name, address, and the date that the member registered at a branch. Each member is given a member number, which is unique throughout all branches of the company. Once registered, a member is free to rent videos, up to maximum of ten at any one time. The data held on each video rented is the rental number, the full name and number of the member, the video number, title, and daily rental, and the date the video is rented out and date returned. The rental number is unique throughout the company.

 

• 
  
  Identify the main entity types of the video rental company.
  Branch, Staff, Video, Copy of video, Customer, Video rented
  
• Identify the main relationship types between the entity types described in (a) and represent each relationship as an ER diagram.
  
• Determine the multiplicity constraints for each relationship described in (b). Represent the multiplicity for each relationship in the ER diagrams created in (b).
  
• Identify attributes and associate them with entity or relationship types. Represent each attribute in the ER diagrams created in Determine candidate and primary key attributes for each (strong) entity type.
  
• 
  
  Using your answers (a) to (e) attempt to represent the data requirements of the video rental company as a single ER diagram. State any assumptions necessary to support your design.
  
  
  My ER Diagram (It needs to modify some tables.)
  
  

 

 

<We have learnt>

Theoretical/ Designing

Normalisation

Scenario

-table

-attribute

-PK

-relation

-FK

-resolve

Practical

DDL

DML

 

Homework Mercy's Dry Cleaning SQL queries

Data Base week5_2

23/02/2010

 
 

We should understand what is the purpose of the SQL.

(To think about behind the business.)

 
 

<SQL>

SELECT Buyer, Department

FROM SKU_DATA;

 
 

SELECT DISTINCT Buyer, Department

FROM SKU_DATA;

 
 

SELECT *

FROM SKU_DATA;

 
 

SELECT *

FROM SKU_DATA

WHERE Department = 'Water Sports';

 
 

SELECT SKU_Description, Buyer

FROM SKU_DATA

WHERE Department = 'Climbing';

 
 

SELECT *

FROM ORDER_ITEM

ORDER BY OrderNumber, Price;

 
 

SELECT *

FROM ORDER_ITEM

ORDER BY Price DESC, OrderNumber ASC;

 
 

SELECT *

FROM SKU_DATA

WHERE Department = 'Water Sports'

AND Buyer = 'Nancy Meyers';

 
 

SELECT *

FROM SKU_DATA

WHERE Department = 'Camping'

OR Department = 'Climbing';

 
 

SELECT *

FROM SKU_DATA

WHERE Buyer IN ('Nancy Meyers', 'Cindy Lo', 'Jerry Martin');

 
 

SELECT *

FROM SKU_DATA

WHERE Buyer IN ('Nancy Meyers', 'Cindy Lo', 'Jerry Martin', 'David');

 
 

SELECT *

FROM SKU_DATA

WHERE Buyer NOT IN ('Nancy Meyers', 'Cindy Lo', 'Jerry Martin');

  
 

SELECT *

FROM ORDER_ITEM

WHERE ExtendedPrice

BETWEEN 100 AND 200;

 
 

SELECT *

FROM ORDER_ITEM

WHERE ExtendedPrice >=100

AND ExtendedPrice <= 200;

 
 

* "Between" and "=<, >=" are same result, however "=<, >=" is more clear than "between".

Data Base week5_1

22/02/2010

 
 

SQL (Structured Query Language)
A standardized non-procedural language used to create, manipulate and manage a database; consists of:

• Data Definition Language (DDL)
  
  • Commands that create, alter and drop tables
    
• Data Manipulation Language (DML)
  
  • Commands that maintain and query tables
    
• Data Control Language (DCL)
  
  • Commands that administer privileges and commit data
    

 
 

Constrains (not must but for used by human being.)

• Constraints enforce rules at the table level.
  
• Constraints prevent the deletion of a table if there are dependencies.
  
• The following constraint types are valid in SQL:
  
  • NOT NULL
    
  • UNIQUE
    
  • PRIMARY KEY
    
  • FOREIGN KEY
    
  • CHECK