As a database administrator of a large manufacturing organization, you are in ch

Question

As a database administrator of a large manufacturing organization, you are in charge of their website database that handles suppliers, parts, catalog, customers, and payments of delivered items. The following relations are part of the large database:

Supplier(SID, Sname, Saddress, Stelephone)
Customer(CID, Cname, Caddress, Ctelephone)
Part(PID, Pname, Pmodel, Pcolor)
Catalog(SID, PID, Price)

(The Catalog relation lists the prices charged for parts by Suppliers).

Because the application is web-based and the database is accessed by many users, you want to increase the concurrency usage without compromising the data quality. For each of the following transactions, state the minimum SQL isolation level you would use in order to avoid any conflict problems in them. Please explain your choice.

a.A transaction that adds a new part to a supplier’s catalog.

b.A transaction that increases the price that a supplier charges for a part.

c.A transaction that determines the total number of items for a given supplier.

d.A transaction that shows, for each part, the supplier who has the part at the lowest price.

Explanation / Answer

a) READ UNCOMMITTED : As we are inserting a new row in the table Catalog, we do not need any lock on the
                                              existing rows.
b) READ COMMITTED : As we are updating one existing row in the table Catalog, exclusive lock on the row which  
                                         we are updating is required.

c) SERIALIZABLE : To prevent other transactions from inserting or updating the table Catalog while reading
                                  from it (known as the phantom problem)
d) same as "c"
SERIALIZABLE : To prevent other transactions from inserting or updating the table Catalog while reading
                                  from it (known as the phantom problem)

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.