32606 Acid properties and integrity constraints

Q1.

task 1

• In relational database systems, 3 forms of data integrity are:
  • entity integrity: every table must have a primary key, can be from single column or composite (combination of multiple columns), and it must not be null
    • why is it important: without it, a system wouldnt be able to distinguish between 2 or more different entities. Example: 2 customers having the same customer id placed an order, there is no way to know who to deliver the order to.
  • domain integrity: the values of the table (columns) must be of the right data type and make sense in real world
    • why is it important: it prevents non-sense or invalid data to be entered the database. Example: customer phone number is a string with letters, there is no way to contact the customer.
  • referential integrity: this enforces a value for foreign key of a table must exists in the parent table.
    • why is it important: to make sure that a relationship is valid, it is not valid if the referenced row doesnt exist. Example: in order table, one of the row has a customerId that does not exist in customer table; there would be no way to know who placed the order and where to deliver.

task 2

• Scenerio an online order is placed and paid by a customer
• The 4 ACID properties are:
  • Atomicity: when a customer placed and paid for an order, the order is created and available number of products in inventory is deducted. Atomicity ensures that either both entries go through or it is rolled back (order is not created and nb of products is not deducted)
  • Consistency: a transaction will always keep a database system having all 3 forms of data integrity (entity, domain, referential). Example, when a new user is created but the phone number is a string, therefore not valid, the database will refuse to apply the changes to keep domain integrity
  • Isolated: If two customers try to buy the last item in stock at the exact same millisecond, isolation ensures that only 1 transaction is processed before another.
  • Durability: Once the system shows a notification “Transaction Complete,” that data is written permanent. Even if the server crashes five seconds later, the record of your purchase is safely written to non-volatile storage and won’t be lost when server is turned off and on again.

Q2

@startuml
 
entity "Customer" as Customer {
  * customerId : PK
  --
  name : text
  email : text
  address : text
}
 
entity "Order" as Order {
  * orderId : PK
  --
  orderDate : datetime
  customerId : FK
}
 
entity "Product" as Product {
  * productId : PK
  --
  name : text
  price : decimal
  manufacturerId : FK
}
 
entity "Manufacturer" as Manufacturer {
  * manufacturerId : PK
  --
  name : text
  adCost : decimal
}
 
entity "OrderLine" as OrderLine {
  * orderId : FK
  * productId : FK
  --
  quantity : int
}
 
entity "Logistics" as Logistics {
  * logisticsId : PK
  --
  orderId : FK
  trackingNumber : text
  method : text
  status : text
}
 
entity "Payment" as Payment {
  * paymentId : PK
  --
  orderId : FK
  amount : decimal
  paymentDate : datetime
}
 
entity "FullPayment" as Full {
  lastFourDigit : int
  authCode : text
}
 
entity "PartPayment" as Part {
  provider : text
  transactionHash : text
  paidPeriod: int
  totalPeriod: int
}
 
' Relationships
Customer ||--o{ Order : "places"
Order ||--|{ OrderLine : "contains"
OrderLine }o--|| Product : "references"
Product }o--|| Manufacturer : "produced by"
Order ||--|| Payment : "funded by"
Order ||--o| Logistics : "shipped via"
 
' Inheritance/Specialization
Payment <|-- Full
Payment <|-- Part
 
@enduml