What is a database and a Database Management System (DBMS)?
A database management system (DBMS) consists of an interrelated collection of data and a set of programs to access said data. The data collection is called database, this contains information relevant to a company. The main objective of the DBMS is to provide a way in which the information found in a database can be stored and retrieved in a way that is practical and efficient.
What are the applications of a database system?
The databases are widely used, here are some representative applications:
- Banking: For customer information, accounts, loans, bank transfers, etc.
- Airlines: They are used for reservations and the management of schedules. These were the first to use the databases in a geographically distributed way.
- Universities: They are used to manage student information such as: enrollment in subjects, courses, etc.
- Credit card transactions: They are used for everything that has to do with the purchase by credit cards and also for the generation of monthly statements.
- Telecommunications: Helps to keep a record of calls made, generate monthly bills, and maintain the balance on prepaid phone cards and to store information about communication networks.
- Finance: Used to store information of holding companies, purchases and sales of financial products such as stocks and bonds; and also to store market data in real time to allow customers to buy and sell online.
- Sales: To store the information of customers, products and purchases.
As can be seen in this list, databases are an essential part of almost all companies today. During the last four decades of the twentieth century the use of databases grew significantly in companies. Initially, people did not interact directly with database systems, but without realizing it, they interacted indirectly with printed reports such as credit card statements, or through agents such as bank tellers and airlines reservation agents.
SQL language
The Structured Query Language, known as SQL is the standard language used to interact with relational database systems. The SQL was developed by IBM in the 1970s with the aim of implementing the management and storage of large data banks developed years before by E. F. Codd.
Relational model
The relational model proposed by E. F. Codd between 1969 and 1979, introduced important changes in the way in which the databases were conceived. This proposal caused a stir at the time and thirty years later is the pillar of most database managers.
The relational model represents databases as a grouping of relationships, that is, each relationship is similar to a table, or, in the worst case, an independent file. If we visualize a relationship as a table, each row of it represents combinations of data related to each other. This data is defined in the form of columns. In terms of the relational model, a row is called a tuple, a column header is an attribute, and the table is a relation. The type of data that describes the values that a column can store is called a domain. The following describes each of the terms in greater detail:
- Domain: The range of valuesto which an object is limited. Domain example: characters, numbers with floating point, integers or logical values.
- Attributes: Are those objects that can store the values restricted by the domain, that is, it belongs exclusively to a particular domain. Example of attributes: date, name, surname. The attributes are also known as columns.
- Relation: it is a correspondence between groups of attributes. For example, the following relationship is defined as a user (name, surname, date of birth) and identifies a correspondence between these attributes. When there are many relationships this is called a set of relationships, which must have unique names that identify them.
- Degrees of a relationship: The degree of the relationship refers to the number of attributes that exist in a relationship.
- Tuples: Series of records that are stored in relationships.
Keys
A key is a combination of one or more attributes that unambiguously identifies the records that are in a table. It is recommended that the keys have the following characteristics:
- Minimalism: This occurs when the key has the minimum number of attributes.
- Stability: The values of the attributes that make up the keys should not be constantly changing.
- Familiarity: Keys must be composed of attributes that make sense to users.
It is recommended that a key be simple, or in other words, that the length of the attributes of a key be minimal. From the computational point of view, the comparison and storage of numbers are more efficient than that of the characters, this means that the search for records that have a key composed of numerical attributes will be faster than one whose attributes are of alphanumeric type.
Types of keys
- Candidate keys: Set of attributes that unambiguously identify the records of a table. Example: if we put the identification of an automobile as an example we can notice that we have three candidate keys: license plate number, engine serial and body serial. By defining which of the three will identify the records, this becomes the primary key and the others would be secondary keys.
- Primary key: It is the one that is selected by the database designer to uniquely identify the records of a table.
- Secondary key: Attribute sets that uniquely identify records in a table but were not selected as the primary key.
The keys in turn can be divided into simple and compound. The simple ones are those that are composed of an attribute (they comply with the condition of minimalism). Compounds are made up of more than one attribute.
Null Value
The null value is a value supported by the relational model. When it is said that a value is "null" it means that said information is unknown.
The null value is not equal to zero or characters in whites. Comparing any value with a null value brings an undefined response since it is not equal to or different from another value, even to itself.
Foreign key
It is a column or sets of columns of a table that contains values that match the values of the primary key of another table and are used to join two tables. Example:
https://steemitimages.com/DQmSByDAoU3JRjwwDHNtXJ5xaBTVyGGPhUENjz7mLjtAsoY/Employee.png
Figure 1. Primary and Foreign Keys of Department and Employee Relations. Own source
I hope that this information has been to your liking, very soon I will be publishing more content about SQL and its functionalities. Thank you very much for reading and sharing.
Bibliographic references:
- Website: https://es.wikipedia.org/wiki/Base_de_datos
- Website: https://es.wikipedia.org/wiki/SQL
- Website: https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.50.0/com.ibm.ddi.doc/ids_ddi_183.htm
- Book: Basics of database. Fifth edition. ABRAHAM SILBERSCHATZ.
- Book: Introduction to SQL Theory and Practice. Olinto Rodríguez Atencio.