50 Database Terminologies That Every Developer Should Know

This blog lists 50 most important concepts that developer and other IT professional should know about database. Every application you use today uses database of some kind, and it is good to know about these database concepts before developing an application. These concepts help you do the right database design the first time. When designed correctly the application perform faster, highly scalable and more secure.

Communication with database administrator becomes easy after you hit the end of a list. These 50 database concepts are not database specific. Learn here and apply on all Database Management Systems that are available today. Developers, are you ready to crack them?

1. Database: A database is a collection of data which is organized to make the retrieval process faster and storing process easier. 
2. Table: A table is a collection of related data held in a structured format within a database that consist of columns and rows.
3. Column: A column is a set of data values of a particular type one for each row of the table. 
4. Constraint: A Constraint specify rules for the data in a table.  Not Null, Unique, Primary Key, Foreign Key, Check and Default Value are examples of dB constraints.
5. Row:  A row represents a single structured data item in a table. A row is also called a record or a tuple.
6. View: A view is the result set of a stored query on the data which the database users can query just as they would in a database.
7. Synonym: A database object which provide an alternative name for an object.  It also provides a layer of abstraction that protects a client application from changes made to the base objects.
8. Primary Key: A primary key is a special database column or a combination of columns designated to uniquely identify the records of the table. PK must contain unique value for each row and cannot contain null values.
9. Foreign Key: A foreign key is a field in a table that uniquely identifies a row of another table. The table containing the foreign key is called the referencing or child table and the table containing the candidate key is called the referenced or parent table.
10. Normalization: Normalization is the process of organizing data in database. This includes creating tables and establishing relationships between those tables accordingly to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency.
11. De-Normalization : To place normalized data in a duplicate location, thus optimizing the performance of the system.
12. SQL: Structured Query Language is a special-purpose programming language designed for managing data in a relational database management system.
13. Index: An Index is a database object that improves the speed of data retrieval operations in a database table at the cost of additional writes and storage space to maintain the index objects. Indexes are used to quickly locate data without having to search the entire table.
14. Transaction: A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties called the Atomicity, Consistency, Isolation, and Durability (ACID) properties, to qualify as a transaction.
15. OLTP: On-Line Transaction Processing or OLTP is characterized by a large number of short on-line transactions like Insert, Update and Delete. An application database is an example of OLTP database.
16. OLAP: On-Line Aanlytic Processing is characterized by low volume of transactions but the queries are often very complex. Data mining application and Business Intelligence queries are some examples of OLAP.
17. Data Warehouse: Data warehouse is a system used for reporting and data analysis. DWs are central repositories of integrated data from one or more data sources. They store current and historical data which are mainly used for generating annual or quarterly comparison report.
18. ETL: Extract, Transform and Load (ETL) refers to a process that extracts data from one or more data sources, Transforms the data into a proper format which then loads in a target database. Sql Server Integration Services and Informatica are two most popular ETL tools.
19. Backup & Recovery: A backup is a copy of data and recovery is a process of restoring a data or a database from a backup copy. In most organization a DBA is responsible for make a backup and restore when needed.
20. Hot Backup: A Hot backup is the backing up process which is done while the database is online. This is also called online backups. Hot backup is performed on databases that are processing transactions 24 by 7.
21. Cold Backup: A Cold backup is the backing up process which is done while the database is in a shutdown state.
22. Full Back up:  Copies everything from a database.
23. Differential Backup: Copies the data or files that have changed since the last full backup.
24. Incremental Backup: Copies all the data or file that has changed since the last backup was made.
25. DDL: Data Definition Language statement defines structure change of db schema objects. Create, Alter, Drop, and Truncate are example of DDL statements.
26. DML: Data Manipulation language (DML) statements access and manipulate data in existing table, view or in a materialized view.
27. Rollback: A rollback is an operation which returns the database to previous state. Rollback is important to data integrity and backing out the changes that was implemented.
28. Commit:  A commit statement ends a transaction and makes all the changes visible to other users. You can’t rollback the changes that are committed.
29. Cascade: Delete with cascade deletes any items that are referenced by the property are also removed.  Cascade is more frequently used on table with Foreign Key relationship.
30. Drop: Drops the data along with the structure of a table. This also removes all the index and privileges to the objects. Drop will move the table to recycle bin in Oracle.
31. Truncate: Truncate deletes all the rows from a table without creating undo files and it cannot be rolled back (at least in Oracle db). Truncate is faster than delete statement. The change is permanent. Remember both Drop and Truncate are DDL statements and can’t use Rolled back operation after truncate statement.
32. Union: Union Operator combines the result-set of two or more SELECT statements. It does not allow duplicates records.  If you want duplicate to your result then use Union All.
33. Join: A Join Clause combines rows from two or more base table on a common field between them.
34. Inner Join: Returns all rows when there is at least one match in both tables.
35. Left Outer Join: Return all rows from the left table and the matched rows from the right table.
36. Right Outer Join: Return all the rows from the right table and the matched row from the left table.
37. Full Join: Return all rows when there is a match in one of the tables
38. Relational Database: Database that allows you to link information from different tables. Oracle, MySql, SQL Server are some of the example of RDBMS database.
39. Non-Relational Database: A non-relational database is a database that does not incorporate the table/key model that relational database management systems promote. MangoDB and NoSQL are some of the examples of non-relational database.
40. Merge: Merge is a Data Manipulation Language that allows you to perform multiple DML operations in an efficient manner. Some calls it UPSERT also. Merge reduces the multiple IF-statements and the costly full table scan for each "IF" statement. 
41. Jobs:  A program that is designed to execute SQL/PL/SQL/TSQL/Procedures at a scheduled date and time. Scheduler Jobs, Agents Jobs are some of the examples.
42. NoSQL: A Database that does not use structured query language. It is often referred as Not Only SQL.
43. Function: A Function is a routine that accepts parameter(s), performs an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set.
44. Stored Procedure: A stored procedure is a group of SQL statements that form a logical unit and perform a particular task, and they are used to encapsulate a set of operations or queries to execute on a database server.
45. Database Link: A database link is an Oracle objects which can access objects from a remote database as long as the user has the grants and privileges. SQL Server calls it Linked Server and other DBMS may call it something else.
46. Schema: A schema is a collection of database objects.
47. Cache: A cache is a temp memory that improves the accessibility and performance of application that access database by caching frequently used data on a middle tier system.
48. Query: A request of information from a database using a query language (SQL).
49. Query Execution Plan: It is an ordered set of steps used to access data in a database management system.
50. Add yours [here]

The list does not end here. There are more database concepts that I missed because I picked 50 random db terminologies. If you want a right database design for your application and also want to avoid nasty emails from DBA, do not end your research here! Explore more database concepts, real database problems, developer tips and trick all in my blog.

---

Sources:
• http://www.support.microsoft.com
• http://www.docs.oracle.com
• http://www.wikipedia.com
• http://www.w3schools.com