Welcome to the first week back from the “break”, in class today we had an overview of what we will be covering this week and in the coming weeks and our Milestone Two hand in was extended by a week (from May 12th to May 19th).
So today we had a brief introduction to Views, ACID and Transactions, we also briefed over Milestone Two again and Todd gave us some subjects to research ourselves – SQL Exception handling and Test app development.
Views are like a filtered version of the database, where one only sees what one wants you to see. This is providing a user with permissions to see only select parts of the database.
ACID, not to be confused with the mind-altering substance, is a database acronym that stands for Atomicity, Consistency, Isolation and Durability. These four principles are “a set of properties that guarantee that database transactions are processed reliably.” From <http://www.w3resource.com/mysql/mysql-transaction.php – 01/05/2017>
Atomicity ensures that transactions are either completed in whole or not completed at all. This means that if part of a transaction works and the DBMS encounters an error, the changes that were made in the previous commands of the transaction will be rolled back.
Consistency is ensuring that data is protected in the event of a DBMS crash, most importantly that you won’t lose your data.
Isolation is about ensuring that only one transaction is being completed on a piece of data, or table, at any one time. For example, one table cannot be updated twice in one command.
Durability relates mainly to the way the DBMS interacts with the hardware platform that is running the DBMS. Everything from backup strategies and ensuring the hardware box does not lose power, such as with an Uninterrupted Power Supply, right through to the way the CPU interacts with the DBMS.
“A transaction is a logical unit of work that contains one or more SQL statements. Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.” From <http://www.w3resource.com/mysql/mysql-transaction.php – 01/05/2017>
Transactions and ACID are interrelated, this is because transactions help ensure atomicity and transactions need isolation.
Although you can technically use DDL within transactions, in a way, doing that defeats the whole purpose of using transactions. This is because DDL statements cannot be rolled back (undone in the event of an error), therefore, it is best practice to use only DML statements within transactions.
Look at feedback and adjust the database structure as necessary. We are required to hand in 3 things for this milestone, a report (a continuation of the report handed in for Milestone One), an SQL file (new additions to the SQL file from Milestone One) and the Visual Studio project folder, all to be zipped into a single file and handed in prior to the prescribed date on Moodle.
When we are writing about handling multiplayer gameplay, we are required to define the ACID terms (Atomicity, Consistency, Isolation and Durability), then describe how they work within a DBMS and finally, we must describe how we have implemented ACID into our own apps database.