Programming Foundation: Databases
Database are used to store data in an efficient way.
They provide security and easy access when these data are needed.
Database provides structure, rules, security and gives easy access when data is needed.
A simple example of database is spreadsheet.
Info are stored in rows and columns, and these allow easy access of the data later on.
In databases, rows are called records and columns are called fields.
Records and fields make up a database table and as the fields increases, we might need to create more tables and link them together.
There are many times of databases.
Relational database is allows easy merging of tables.
Record/Row contain an instance of an entity. And Field/Column contain attributes of each instance.
For example: Customers of a restaurant are entity. And each customer is an instance of the customer entity. And each has different attribute like firstname, lastname, phone, email, DOB etc.
Another example of entity is Dishes. Each dish has the attribute of name, price, and content.
Keys and Unique Values
For example, you want to call out a name from your database. You got two similar names. And this happens a lot when you have plenty names.
So a way to prevent that is using a unique value record. Unique value is also called key.
Each record has its own key that differentiate it from all other records in the table.
Keys also allow each referencing of a record in different tables.
Foreign key is when a key from another table is use to reference that table in another table.
ACID and Transactions
It is a simple task when removing or adding a customer to your table.
What if you want to transfer money from one account to another. This involves many action:
You frist check the balance of the sending account, transfer the money, update the receiving account etc.
And during this single transfer, you want to make sure no other transfer is running until this one finishes.
You can see that this kind of update to our database is quite complex and criticial.
That is where the term transaction comes in.
Transaction is the term use to ensure that all the process involve in such transfer are completed before another change is made to the two accounts. It guides against indivual stage of the process too and completes the whole process only when each stage is completed.
ACID stands for Atomic. Consistent. Isolated. Consistent.
All this are carried out for each stage of the transaction before it is complete.
After you have your database ready, one of the ways to talk to it is using Structured Query Language.
You can use to (CRUD) Create, Read, Update and Delete data from the database.
Before you create a database, it is best to plan on a paper.
List all the instances you will need and the attributes of each instance.