Database

Basic Concepts of the Database

• A system that stores data and preserves them
• Data exists on the memory in the application, but data on the memory are not preserved permanently.

Relational Database Management System

Literally a database system based on the relational data model

What is Relational Data?

Data shown in the interactive form
1) 2-dimensional table
2) consists of row(data) and column(field)
3) Each row has its unique primary key

One to one

One record in a table is associated with one and only one record in another table. In this example, the key field in each table, Student ID, is designed to contain unique values. In the Students table, the Student ID field is the primary key; in the Contact Info table, the Student ID field is a foreign key.

This relationship returns related records when the value in the Student ID field in the Contact Info table is the same as the Student ID field in the Students table.

One to many

One customer orders multiples of items so in this case, primary key field in the Customers table, Customer ID, is designed to contain unique value. The foreign key field in the Orders table, Customer ID, is designed to allow multiple instances of the same value.

Many to many

A many-to-many relationship occurs when multiple records in a table are associated with multiple records in another table.you can break the many-to-many relationship into two one-to-many relationships by using a third table, called a join table.

Each record in a join table includes a match field that contains the value of the primary keys of the two tables it joins. (In the join table, these match fields are foreign keys.) These foreign key fields are populated with data as records in the join table are created from either table it joins.

Join tables can access fields and data across tables without having to create a separate relationship.
Example)
To display a list of all the classes a student enrolled in, create a portal on a layout based on the Students table. Design the portal to show related records from the Classes table. Then add the appropriate fields from Classes to the portal. As you browse through records in the Students layout, the portal displays all the classes a particular student is enrolled in.

ACID Properties

Atomicity

A transaction is an atomic unit; hence, all the instructions within a transaction will successfully execute, or none of them will execute. The following transaction transfers 20 dollars from Alice’s bank account to Bob’s bank account. If any of the instructions fail, the entire transaction should abort and rollback.

Consistency

A database is initially in a consistent state, and it should remain consistent after every transaction. Suppose that the transaction in the previous example fails after Write(A_b) and the transaction is not rolled back; then, the database will be inconsistent as the sum of Alice and Bob’s money, after the transaction, will not be equal to the amount of money they had before the transaction.

Isolation

If the multiple transactions are running concurrently, they should not be affected by each other; i.e., the result should be the same as the result obtained if the transactions were running sequentially. Suppose B_bal is initially 100. If a context switch occurs after B_bal *= 20, T2 will read the incorrect value of 100 as the updated value will not have been written back to the database. This violates the isolation property as the result is different from the answer that would have been​ obtained if T1 had ​finished before T2.

Durability

Changes that have been committed to the database should remain even in the case of software and hardware failure. For instance, if Bob’s account contains $120, this information should not disappear upon hardware or software failure.