Course Name: CS623-Database Management Systems, Fall 2020
Professor: Dr. Christelle Scharff
Collaborators: Briana Figueroa & Sayema Islam
Group # 4
Language: Java
Product to Stock
Rule 0:
Product(prodId, pname, price...)
Stock(prodId, depId, quantity...)
Rule 2:
Product(prodID, pname, price)
Stock(prodId, depId, quantity, Product.prodId)
Stock to Depot:
Rule 0:
Stock(prodId, depId, quantity...)
Depot(depId, addr, volume...)
Rule 2:
Stock(prodId, depId, quantity, Depot.depId)
Depot(depId, addr, volume)
We started by first setting up the schema for our project's database in postgre as can be seen from the screenshots below:
(N.B. All this code can also be found in our Team4Project.sql file in the repository.)
We then created a java file/class to execute our assigned transaction--the renaming of one of the depots' IDs--by first connecting to the postgre database, and then implementing the ACID properties of atomicity and isolation (consistency is taken care of by our real-world execution of the transaction, and durability by the committing of these changes to the postgre database itself).
Because the Stock table has a foreign key that depends on the Depot table, neither of the tables could be updated without somehow manipulating the foreign keys. Thus we first considered implementing a transaction where the foreign key of Stock relating to Depot was dropped, and then reinstated using a CASCADE command before updating only one table to demonstrate that all these commands were necessary to execute the transaction (i.e. all or nothing) but given the nature of CASCADE, it was decided that this was not a good way to demonstrate atomicity.
So instead, we decided to execute the transaction by first dropping the foreign key of Stock, then updating BOTH the Depot and Stock tables to show the importance of both atomicity and consistency, as in the real world, renaming an ID in one table should also mean that the corresponding IDs in any other tables are updated/renamed as well (consistency), and both of these statements should be executed at once (atomicity). The foreign key between Stock and Depot was then, of course, reinstated. Thus in essence, we somewhat mimicked the way a CASCADE would work, but through commands to both tables instead of simply one, in order to highlight the transaction's ACID compliance.