In this post we will know some basic concepts of database which includes different types of databases, schema, SQL , queries and table expressions.
What is a Database?
A database is any collection of related data. The database stores all data in tables that are related to one another. Relationships are established between tables such that data is shared and duplication is minimized.
Below are the different types of Databases which are commonly known.
A relational database is a database that is treated as a set of tables and manipulated in accordance with the relational model of data. It contains a set of objects used to store, manage, and access data. Examples of such objects are tables, views, indexes,
functions, triggers, and packages. Objects can be either defined by the system (system-defined objects) or defined by the user (user-defined objects).
Distributed relational database :-
A distributed relational database consists of a set of tables and other objects that are spread across different but interconnected computer systems. Each computer system has a relational database manager to manage the tables in its environment. The database managers communicate and cooperate with each other in a way that allows a given database manager to execute SQL statements on another computer system.
Partitioned relational database :-
A partitioned relational database is a relational database whose data is managed across multiple database partitions. This separation of data across database partitions is transparent to most SQL statements. However, some data definition language (DDL) statements take database partition information into consideration (for example, CREATE DATABASE PARTITION GROUP). DDL is the subset of SQL statements used to describe data relationships in a database.
Federated database :-
A federated database is a relational database whose data is stored in multiple data sources (such as separate relational databases). The data appears as if it were all in a single large database and can be accessed through traditional SQL queries. Changes to the data can be explicitly directed to the appropriate data source.
In a relational database, the schema defines the tables, the fields in each table, and the relationships between fields and tables. A schema can be simply described as the “layout” of a database. Schema are normally described using Structured Query Language as a series of CREATE statements that may be used to replicate the schema in a new database.
Structured Query Language (SQL):-
Structured Query Language (SQL) is a widely-used programming language for working with relational databases. SQL is used for defining and manipulating data in a relational database.
Queries and table expressions:-
A query is a component of certain SQL statements; it specifies a (temporary) result table.
A table expression creates a temporary result table from a simple query. Clauses further refine the result table. For example, you can use a table expression as a query to select all of the managers from several departments, specify that they must have over 15 years of working experience, and be located at the New York branch office.
A common table expression is like a temporary view within a complex query. It can be referenced in other places within the query, and can be used in place of a view. Each use of a specific common table expression within a complex query shares the same temporary view.
Some other concepts of databases which are included in this post are normal forms, table constraints and relations between tables.