During application development basically we pass through 2 phases front-end & back-end. Front-end is nothing but the area where user interacts. That’s why Front-end is also known as User Interface (UI). Some of the user interface examples are forms, controls, images or validations. While back-end is the part where we store user information. For an example while designing a Customer details form using Save option we do store Customer details like First Name, Last Name, Email ID or Phone Number to the database.
So here database resides behind the user view. That’s why work related to database such as database designing, storing, updating, retrieving or deleting records comes under back-end development. Hope now you have a clear vision about what is the difference between front-end & back-end development. This session I am specially writing by targeting you like the beginners of database. Here you can easily understood database basics and most commonly used database terminologies.
Types of Databases
They are fundamental to modern computing, supporting everything from small applications to large-scale enterprise systems. Different types of databases are designed to handle various data structures, relationships, and use cases. Understanding these types helps in selecting the right database for specific needs.
1. Relational Databases – They store data in tables consisting of rows and columns, where relationships between tables are established using keys.
2. NoSQL Databases – NoSQL (Not Only SQL) databases provide a more flexible approach to data storage, accommodating unstructured or semi-structured data.
3. NewSQL Databases – NewSQL databases combine the scalability of NoSQL with the ACID guarantees of traditional relational databases. They are designed for distributed environments requiring high performance and consistency.
Examples: – Google Spanner – CockroachDB
These databases are used in financial services and global-scale applications demanding high availability and strict consistency.
4. In-Memory Databases – In-memory databases store data primarily in RAM rather than on disk, enabling extremely fast read/write operations.
Use Cases:
– Real-time analytics
– High-frequency trading
– Caching systems
6. Object-Oriented Databases – These databases store data as objects, making them compatible with object-oriented programming languages.
Advantages:
– Eliminates the need for object-relational mapping (ORM).
– Supports inheritance and polymorphism.
Examples:
– db4o
– ObjectDB
7. Distributed Databases – Distributed databases store data across multiple physical locations, improving availability and fault tolerance.
Types:
– Homogeneous: Same database system across nodes (e.g., Google Spanner).
– Heterogeneous: Different systems working together (e.g., federated databases).
8. Cloud Databases – Hosted on cloud platforms, these databases offer scalability, managed services, and pay-as-you-go pricing.
Examples:
– Amazon RDS
– Google Cloud Firestore
Database Stores data using Tabular Shape
First of all let me tell you whatever the database program (MS SQL Server or MySQL or Oracle or DB2) you will choose for you application development, theories for database basics are same for all. Database stores data using tabular shape. While this tabular shape further divided into 2 dimensions rows and columns. A database management system (DBMS) stores data in such a fashion that it becomes very easier to retrieve, update or produce information. For an example let you have an employee details table. Where you have fields like Employee Name, Date of Birth, Email ID, Salary or Contact Details. In such case if you want to extract information about all those employee who is having salary greater than 30 thousand, database is smart enough to do that for you.
ACID Properties
In database basics ACID stands for Atomicity, Consistency, Isolation and Durability. Here before I explain more about these properties first let you know these properties are the keys for which database is so popular today. Atomicity says during a transaction involving 2 or more discrete pieces, whether all of them are committed or none. Let take an case study where in a bank while a customer depositing some money to his or her account in back-end we do insert the fresh entry and in the next step we update the previous balance. Here if insert get executed and update fails than it is not acceptable. That’s why in such cases we do all consecutive actions under a single transaction. This feature is called Atomicity.
Like Atomicity Consistency is one more key feature of DBMS. Consistency says during a transaction either creates a new with valid state of data or else if any failure occurs return all data to the previous state of before transaction. Here I will give you an example. Assume during you submit a form in between due to sudden power failure you get disconnected from the server. From the complete record partial data available to the server and connection break. In such case database will not insert that partial record to the table rather it will discard the transaction without affecting the previous state.
Isolation says a transaction in process and not yet committed must remain isolated from other transaction. Durability is nothing but once data Committed is get saved by the system, even after in-case of any failure or system restart, the data is remain as it is in correct state.
Entity & Relationship
In ER Model an Entity is nothing but the real-world object. Properties of an Entity is called attributes. Every attributes holds a set of values which called domain. For an example in a Employee Details table, Employee is an Entity while attributes are Name, Age, Sex, Salary or Designation. About relationship I can say it is a logical association of entities.
Primary Key & Composite Key
In a table Primary Key is the field which stores unique entry for each individual records. Using Primary Key we can identify any individual record in millions. Which field we declare as the Primary Key that can’t contain null value or blank. While inserting a record make sure Primary Key is the mandatory field. Generally we use auto increment feature in Primary Key field. Generally we use integer data type under Primary Key field. Rather a single column if we are declaring 2 or more column as Primary Key than that is called Composite Key. In case of Composite key we can’t guarantee uniqueness of a single column but while it comes together it fulfill all the properties of a Primary Key.
Unique Key
A Unique Key is very similar to Primary Key. Like the Primary Key Unique Key provides unique identification of each record in a table. Keep noted in a table you can have only one Primary Key but you can define as many as unique key you wants. One more difference of Primary Key and Unique Key is Unique Key allow null value to store.
Foreign Key
A Foreign Key always points Primary Key of an another table. It’s a key feature of Foreign Key that it prevents to insert records which is not establishing relationship between two tables. Even Foreign Key filed never accepts invalid data from being inserted.
CHECK Constraint in SQL
In a SQL table we use CHECK constraint to limit the range of value that can be placed in a column. If you will define CHECK constraint to a single column it allows only limited values for that column. In case you define CHECK constraint to a table it can limit the values in certain columns based on values in other columns in the row.
Database Basics Unique Key and Foreign Key
Unique keys and foreign keys are fundamental to relational database design, ensuring data accuracy and meaningful relationships. While unique keys prevent duplicate entries in critical fields, foreign keys maintain connections between tables, preserving referential integrity. Understanding these constraints allows for robust database structures that support efficient data management. By applying them correctly, businesses can avoid inconsistencies and build scalable, reliable systems.
What is a Unique Key?
A unique key is a constraint applied to a column or a set of columns in a database table to ensure that all values stored in those columns are distinct. The primary purpose of a unique key is to prevent duplicate entries in specific fields while allowing NULL values (unless explicitly restricted).
Characteristics of a Unique Key
– Uniqueness: Ensures no duplicate values exist in the constrained column(s).
– Nullable: Can contain NULL values unless defined as NOT NULL.
– Multiple Keys: A table can have more than one unique key.
What is a Foreign Key?
A foreign key is a column (or a set of columns) in one table that references the primary key or unique key of another table. Foreign keys help maintain consistency by preventing actions that would leave orphaned records or violate logical relationships.
Characteristics of a Foreign Key
– References Another Table: Points to a primary key or unique key in a related table.
– Ensures Referential Integrity: Prevents invalid data from being inserted into the foreign key column.
– Cascading Actions: Supports operations like CASCADE DELETE or UPDATE to maintain consistency.
– Can Allow NULL: Depending on the design, foreign keys may permit NULL values if the relationship is optional.
Conclusion
Choosing the right database depends on data structure, scalability needs, and performance requirements. Relational databases excel in structured data environments, while NoSQL systems offer flexibility for unstructured data.
