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.
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.
Do you like this Story related to Database Basics? Share us in your Network.
