Existential Duality - More on Databases

Read Kanchana's previous articles on Databases here and here. 

Google the search giant’s self driving car has logged 700,000 autonomous miles. They have improved their software so that it can detect – ‘pedestrians, buses, a stop sign held up by a crossing guard or a cyclist making gestures that indicate a possible turn’ says Google’s official blogspot. So we are getting close to a vehicle that drives without human intervention ; capable of navigating in chaotic roads without distraction.

Now to last weeks question.

What happens in real time whenever we swipe a card ? All the information or the data that gets generated or used when a card swipe happens is not stored in just one table.In reality there would be a number of tables that are interlinked and that get updated individually, simultaneously as the requirement of the event that triggered the updation demands.

In a typical banking database we have entities that hold

Customer details

Account details

Transaction details

Customer-account details

lodged in separate tables.The reason why they are kept separately is simple.Access control.Only specific groups or individuals will need to know information about specific entities.Not every one who works on the database needs to know the bank account details of each and every customer.

For eg a banking assistant who sends mailers to defaulting customers needs to know only the mailing address of the defaulted customer and only her associated account numbers. He need not know the other account details.

A typical CUSTOMER table will have details of Customer identification, Name, Permanent and mailing addresses .The ACCOUNTS table will have Account number ,Account type ,Credit limit ,Balance ,Time stamp. The Transaction table will have Transaction identification Number ,Customer Id, Time stamp ,Amount ,Balance and Description . The Customer Account table will have only two distinct details namely Customer Id and Account Id.

So Customers,Accounts,Transactions are all entities.They hold unique information that can be stored in separate distinct columns and row formats called tables.And tables are the foundation blocks of any Database.

Lets see the table designs
CUSTOMERS

Accounts

Transaction 

The CUST_AC table has just two rows

The names of the tables and columns make the metadata of the database.In each table one column that has unique values becomes the primary key.In CUSTOMER,ACCOUNTS AND TRANSACTION we have Customer_no, Acc_no and Transaction_no as the unique values and the respective primary keys. Indexing is done on the primary key of the table ie arranging the rows in the table in the ascending or descending order of the value in the primary key column. Foreign key of one table is the key pointing to the primary key of another table.Here Customer_no is the foreign key in TRANSACTION pointing to Customer_no the primary key in CUSTOMERS table. The relationship between the tables is the link between the primary key and foreign key s.

So in this Relational Database Management System can there can be different relationships between tables ?Why does CUST_AC have only two rows? …Lets see them next.