Online Transaction Processing (OLTP)

This is what we learned in Databases I:

• ER diagrams
• Relational model (primary keys and foreign keys)
• Transactions

Operations/Transactions

• Insert (eg. new customer)
• Delete (eg. supplier discontinues product, so it must be deleted as soon as we run out of stock)
  • Side note: in practice, we would flag the product’s tuple as DELETED, and delay physical removal from table until later
• Update (eg. a customer has moved to a new address, so their information must be updated to replace the old address with the current one)
• Query (eg. a customer wants to purchase a particular size of an item, but that store is out of stock of that size. A query is performed to see if another store has that particular item & size in stock)

DBMS (Database Management System)

• Concurrency control —
• Recovery — ensure data is kept in the event of a crash/system failure; done with logs, audit trails
• Security — ensure data is stored safely, especially in the case of financial data

Adding analytics to DBMS will slow down the system, leading to poor performance.

Case Study: Clothing Store with 10 Branches in Ontario (OLTP)

Consider a clothing store with 10 branches.

Each store opens at 9:30AM and closes at 11:00PM.

In a regular day, the following transactions may occur:

• 5000 new customers – insert
• 100 customer address changes – update