Hierarchical Database to Relational Database
Early databases were hierarchical databases – data organized in a tree structure. IBM introduced first generation of database: IMS (Information Management System) in 60’s. Data in a hierarchical database is organized in a tree like structure.
Which works fine for a family tree
But can’t work for an Enterprise application in which relations are not always hierarchical –
A Seller can sell multiple products, which can be bought by multiple customers, who can have different payment methods for different orders.
We can think of Product, Customer, Order, Payment, and Seller as entities with relationship to each other, which is not hierarchical.
In above structure data is in entities which are related. Entity relations can be one-to-many (Seller and product, because a seller can sell multiple products), one-to-one (Product and Order, one order can be placed for a product), many-to-one (orders and customer, many orders can relate to one customer), and many-to-many (customer can have multiple address and same address can belong to multiple customers).
Data in RDBMS (Relational Database Management System) is structured – Divided into tables in which each table has structure (rows and columns) and they are related. RDBMS support structured query language (sql) for data definition and manipulation. RDBMS require schema creation (architecture of database) and users need to be granted privileges for data access. Further RDBMS also provide UI tools for easy data view. Tables in RDBMS have a primary key for uniquely identifying the record such as EMPLOYEE_ID can be primary key (PK) in EMPLOYEE table. Every employee record in EMPLOYEE table has a unique value of EMPLOYEE_ID. Tables rarely exist in isolation and structure of table is a contract which can’t be break during data storage.