TUGAS 3 - BASIS DATA | UNIVERSITAS TERBUKA | QISTHINA'S DIARY

Summary

The presenter completes a database assignment covering transactions, savepoints, triggers, and database replication. The work is based on the practical guide and an existing hospital database from an earlier assignment.

1. Transactions: COMMIT and ROLLBACK

• The presenter first checks that the relevant tables use InnoDB, which supports transactions.
• A stored procedure is created to begin a transaction and insert a patient record.
• In the first version, the procedure has no COMMIT. After it runs, the new patient does not appear as a permanently stored record.
• A second procedure performs the same insert but includes COMMIT. Running it makes the patient record persist in the table.
• A third procedure updates a patient’s name and then uses ROLLBACK. The update is canceled, so the name remains unchanged.
• The presenter also demonstrates a separate insert to distinguish a committed change from one that was rolled back.

Key lesson: COMMIT makes transaction changes permanent; ROLLBACK cancels uncommitted changes.

2. Savepoints

• The presenter creates a transaction that inserts several patient records and sets a savepoint named point_one.
• A rollback is then used to cancel changes made after the savepoint rather than undoing the entire transaction.
• The demonstrated result shows that records inserted after the savepoint are canceled, while records outside that rollback range remain. The presenter verifies the outcome by checking the patient table.

Key lesson: A savepoint marks a point within a transaction, allowing a rollback to undo only subsequent work.

3. Trigger for Updating Medicine Stock

• Two tables are created for the example: a medicine table and a medicine-transaction table.
• The presenter adds relationships between the transaction table and the patient and medicine tables using foreign keys.
• Several medicines are entered into the medicine table.
• A trigger is created so that inserting a medicine transaction automatically reduces the corresponding medicine’s stock.
• In the demonstration, a medicine’s stock goes from 20 to 17 after a transaction is added.
• The presenter notes that calculating a transaction’s total price automatically would require adding a mathematical expression to the relevant logic.

Key lesson: A trigger can automatically apply related database changes when an event—such as inserting a transaction—occurs.

4. Replication Between a Mac and Ubuntu

• The presenter uses a Mac as the master and Ubuntu as the replica, differing from the Windows-to-Linux setup described in the guide.
• The existing hospital database is exported from the Mac and imported into Ubuntu. The presenter checks that the database and tables are present.
• The server ID is checked, and the presenter tests the Ubuntu server’s connection to the Mac using a replication account.
• The replica is configured with the master’s IP address, replication credentials, and relevant master log-file information.
• The initial status check shows that replication is not yet configured. After configuration, the SQL process is still not running because of a problematic password-related event.
• The presenter stops replication, skips the event, and restarts it. The status then reports both replication processes as running.
• To test replication, the presenter changes Betadine’s stock on the Mac from 5 to 6, then checks Ubuntu and confirms that the updated value appears there as well.
• The presenter describes replication as useful for keeping data synchronized and providing another copy of the data if one computer becomes unavailable.

Speakers and Sources

• Presenter/narrator: Demonstrates the assignment and explains the database operations.
• E-learning practical guide: Cited as the source of the assignment instructions; it is not a separate speaking voice in the subtitles.