What is an OLTP database?
OLTP-(On-line Transaction Processing) is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE).The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second. In OLTP database there is detailed and current data and the system helps in tracking all these business processes at very fast pace.
How do you design OLTP database?
To design an OLTP, the first step is to define the business processes of the organization. The business process involves the daily activities or operations of the organization without which the organization does not exist. After defining the business processes, the second step in the design process is identify the scope of the database design effort. This is usually done by taking one or more related business processes for further analysis and improvement. The third step is to define the information needs of users who interact or are affected by the business process(es) selected and analyzed. The fourth step will be to come out with a conceptual design. The conceptual design models the basic understanding and illustrates the relationship that exists among information sources, users and business processes.
In the fifth step of the design process, the conceptual design is used to construct a logical design model based on one of the database management systems. It can be relational, hierarchical, network or object oriented. During the sixth step, a physical design is constructed which involves consideration about the structures, physical storage and access methods for the database. Once all these decisions are taken, the last step of the design process is the creation and testing of the database.
What are the benefits of normalization?
Normalization reduces redundant data in a database. This helps in faster transactions because updates, inserts and deletes are performed only once on normalized database.
What does 1,2 and 3 Normal form has in common?
They aimed to achieve a condition where all columns in a table fully depends on the primary Key.
Explain the difference between a normalized table and denormalized table?
A normalized table is a table that has all the columns of the table fully dependent on the primary key of the table , it has no redundant data and there are no group data in the table. A denormalized table on the other hand is a table that has controlled redundant data introduced into it and there are group data to make queries faster by avoiding too much joins between related normalized tables.
What is a business process?
It is the daily operational tasks of an organization without which the organization cannot exist. Identifying business process is the first step in designing OLTP databases because it helps in understanding what and how to construct our database and database objects.
