Relational database has been the most popular database type since 1990. It is based on the relational model, which means that the data will be stored in tables(=relations). So in this context the term relation means the same as table.
We can use SQL(Structured Query Language) to manage relational databases, so we can call them also SQL databases. SQL is a big benefit, because you can use it with all SQL databases. It means that, if you know how to use MySQL, you also know how to use SQL Server.
In last years NoSQL databases have grown quite popular. They don't use the relational model, but they have some benefits compared to SQL databases. They can be must faster sometimes, but if the integrity of data is important, SQL database are the one to choose. Some of the most used NoSQL databases are Google BigTable, Amazon Dynamo, MongoDB and Apache Cassandra.
A relational database matches data by using common characteristics found within the data set. The resulting groups of data are organized and are much easier for many people to understand.
For example, a data set containing all infromation about library. We can divide the data to "groups": books, borrowers, borrows. Such a grouping uses the relational model.
A relational database is a collection of data items organized as a set of formally-described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables. The relational database was invented by E. F. Codd at IBM in 1970.
The standard user and application program interface to a relational database is the structured query language (SQL). SQL statements are used both for interactive queries for information from a relational database and for gathering data for reports.
In addition to being relatively easy to create and access, a relational database has the important advantage of being easy to extend. After the original database creation, a new data category can be added without requiring that all existing applications be modified.
Relational database consists of below components:
id_book | name | author | isbn |
1 | Everything You Ever Wanted to Know | Upton | 082305649x |
2 | Photography | Vilppu | 205711499 |
3 | Drawing Manual | Zelanshi | 1892053039 |
When there is a relationship between two tables, one of them is a parent table and the other one is a child table.
Relational term | SQL equivalent |
---|---|
relation, base relvar | table |
derived relvar | view, query result, result set |
tuple | row (record) |
attribute | column (field) |
In relational database every table should have a unique key, which can uniquely identify each row in a table. A unique key comprises a single column or a set of columns. Two distinct rows in a table can not have the same value (or combination of values) in those columns. This key is called primary key.
The primary key can be just one field of the table or it can be a combination of several fields.
Sometimes there is no field(s), which could be the primary key (natural key) and then we have to add an extra field which will be the primary key. This kind of primary key is called a surrogate key.
Quite often the surrogate key is set to AUTO_INCREMENT, which means that it is an INTEGER with automatic values 1,2,3 ...
Developers often prefer the surrogate, even they could choose a natural key.
Example
CREATE TABLE person( email VARCHAR(50) PRIMARY KEY, fname VARCHAR(30), lname VARCHAR(30) );In above example email is a natural key.
CREATE TABLE person( id_person INT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(50), fname VARCHAR(30), lname VARCHAR(30), UNIQUE INDEX email_index(email) );In above example id_person is a surrogate. Unique index is explained later.
A foreign key is a field (in child table) that points to the primary key of another table (parent table). The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.
So the foreign key is referencing to the primary key of the parent table, like this
For example, say we have two tables, a CUSTOMERS table that includes all CUSTOMERS data, and an ORDERS table that includes all CUSTOMERS orders. The constraint here is that all orders must be associated with a CUSTOMERS that is already in the CUSTOMERS table. In this case, we will place a foreign key on the ORDERS table and have it relate to the primary key of the CUSTOMERS table. This way, we can ensure that all orders in the ORDERS table are related to a CUSTOMERS in the CUSTOMERS table. In other words, the ORDERS table cannot contain information on a customer that is not in the CUSTOMERS table.
The structure of these two tables will be as follows:
Table CUSTOMERS
column name | characteristic |
idCustomers | Primary Key |
Last_Name | |
First_Name |
Table ORDERS
column name | characteristic |
idOrders | Primary Key |
Order_Date | |
Amount | |
idCustomers | Foreign Key |
In the above example, the idCustomers column in the orders table is a foreign key pointing to the idCustomers column in the customers table.
First we can can create the table customers with below SQL-code (in MySQL)
CREATE TABLE Customers( idCustomers integer primary key, Last_Name VARCHAR(45), First_Name VARCHAR(45) ) Engine=InnoDB;
Then we can create the orders table with below SQL-code (in MySQL)
CREATE TABLE Orders (idOrders integer primary key, Order_Date date, idCustomers integer, Amount double, Foreign Key (idCustomers) references Customers(idCustomers) ON UPDATE CASCADE ON DELETE RESTRICT) Engine=InnoDB;
The below ER-diagram will show the relationship between Customers and Orders:
Indexes are essential components in relational databases, designed to improve the speed of data retrieval operations. Much like the index of a book, a database index allows the database engine to find rows of data quickly without scanning the entire table.
An index is a data structure, typically a B-tree or a hash table, that stores the values of one or more columns in a table along with pointers to the corresponding rows. This structure allows the database to locate data efficiently.
The types of indexes supported by different database management systems differ from each other. In the following, I will introduce some examples about using indexes in MySQL.
Here is a code for creating a table named person
CREATE TABLE person( id_person INT PRIMARY KEY AUTO_INCREMENT, fname VARCHAR(30), lname VARCHAR(30), email VARCHAR(30), country VARCHAR(20), city VARCHAR(20), UNIQUE INDEX email_index(email), INDEX name_index(lname, fname) )
Indexes significantly speed up query performance, especially for large datasets. They reduce the amount of data the database engine needs to scan, thus reducing I/O operations and improving response times.
While indexes enhance read operations, they can slow down write operations such as INSERT, UPDATE, and DELETE because the index itself needs to be updated. Therefore, it's essential to strike a balance and create indexes that optimize performance for your specific use cases without causing unnecessary overhead.
Proper indexing is crucial for database performance tuning. By understanding and implementing the right types of indexes, you can ensure efficient data retrieval and overall system efficiency.
You define foreign keys in a database to model relationships in the real world. Relationships between real-world entities can be quite complex, involving numerous entities each having multiple relationships with each other. For example, a family has multiple relationships between multiple people—all at the same time. In databases there are two things which describes the relationship between two table: Cardinality and Modality.
In a relational database the relationship type(Cardinality) between two tables is one of below:
When you are planning a database, you should make an ER-model. Later the ER-model will be inserted in the database document. In ER-model the relationship type is described with below symbols:
Two tables are related in a one-to-one (1—1) relationship if, for every row in the first table, there is at most one row in the second table. The one-to-one relationships is not very common in databases. This type of relationship is often created to get around some limitation of the database management software rather than to model a real-world situation. As example, perhaps you need to transfer only a portion of a large table to some other application on a regular basis. You can split the table into the transferred and the non-transferred pieces, and join them in a one-to-one relationship.
Two tables are related in a one-to-many (1—M) relationship if for every row in the first table, there can be zero, one, or many rows in the second table, but for every row in the second table there is exactly one row in the first table. The one-to-many relationship is also referred to as a parent-child or master-detail relationship. One-to-many relationships are the most commonly modeled relationship.
Two tables are related in a many-to-many (M—M) relationship when for every
row in the first table, there can be many rows in the second table, and for every
row in the second table, there can be many rows in the first table.
Example: Customer can order serveral Products and Products can be ordered by several Customers
But many-to-many relationships can't be directly modeled in relational database systems.
These types of relationships must be divided into multiple one-to-many relationships.
Example: we can add a new table named Ordes like this:
Relationships between two entities may be classified as being either "identifying" or "non-identifying". Identifying relationships exists when the foreign key is included in the primary key of the child entity.
Example.
parent table has fields
The participation of an entity in a relationship is either optional or mandatory. In ER-model the "inner symbol" indicates is it optional or mandatory. ( | = mandatory, 0 = optional).
Modality is a term which descibes if the relationship is mandatory or optional.
A feature provided by relational database management systems (RDBMS's) that prevents users or applications from entering inconsistent data. Most RDBMS's have various referential integrity rules that you can apply when you create a relationship between two tables.
For example, suppose that childTable has a foreign key that points to a field in parentTable.
The purpose of referential integrity is to take care that all the values of foreign key exists on the parent-table.
When designing a database, you have to make decisions regarding how best to take some system in the real world and model it in a database. This consists of deciding which tables to create, what columns they will contain, as well as the relationships between the tables.
The benefits of a database that has been designed according to the relational model are numerous. Some of them are:
Data entry, updates and deletions will be efficient.
Data retrieval, summarization and reporting will also be efficient.
Since the database follows a well-formulated model, it behaves predictably.
Since much of the information is stored in the database rather than in the application, the database is somewhat self-documenting.
Changes to the database schema are easy to make.
The process of database design is divided into different parts. It consists of a series of steps.They are
Requirement analysis is made in order to identify and document the client's needs and desires of the implemented system. All the requirements can't usually get immediately recognized, so with the customer has to agree exactly how the additions and changes in the development of the project will be made. Often, it makes sense to do a so-called prototype, which can ensure that the requirements are correctly understood.
Requirements are made with the customer. Requirements typically include the following issues for the scheme:
When you are planning a database, it would be handy to draw the first versions of the ER-model with paper and pencil. You will just check that there is no many-to-many relationships. And then you should start to add the columns in the diagram. You can find information about ER-model from
Before you start to make tables, you should write the field definitions. In order to do that you should deside for every field:
Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating two factors: redundancy and inconsistent dependency.
Redundant data wastes disk space and creates maintenance problems. If data that exists in more than one place must be changed, the data must be changed in exactly the same way in all locations. A customer address change is much easier to implement if that data is stored only in the Customers table and nowhere else in the database.
There are a few rules for database normalization. Each rule is called a "normal form." If the first rule is observed, the database is said to be in "first normal form." If the first three rules are observed, the database is considered to be in "third normal form." Although other levels of normalization are possible, third normal form is considered the highest level necessary for most applications.
As with many formal rules and specifications, real world scenarios do not always allow for perfect compliance. In general, normalization requires additional tables and some customers find this cumbersome. If you decide to violate one of the first three rules of normalization, make sure that your application anticipates any problems that could occur, such as redundant data and inconsistent dependencies.
All nonkey attributes are totally functionally dependent of the whole primary key (not part of the key).
All nonkey attributes are functionally dependent only upon the primary key (they are not dependent of any nonkey attribute).
Field definitions for NetShop tables might be something like this:
Customers-table | ||
---|---|---|
Field | Data Type | Qualifications |
id_customers | INT | primary key, values between 0 and 9999 |
firstname | VARCHAR(15) | mandatory, maximum length 15 characters |
lastname | VARCHAR(15) | mandatory, maximum length 15 characters |
streetaddress | VARCHAR(45) | mandatory, maximum length 45 characters |
postalcode | CHAR(5) | mandatory, length 5 characters |
city | VARCHAR(20) | mandatory, maximum lenth 20 characters |
VARCHAR(50) | mandatory, maximum length 50 characters |
Products-table | ||
---|---|---|
Field | Data Type | Qualifications |
id_products | INT | primary key, values between 0 and 9999 |
name | VARCHAR(15) | mandatory, maximum length 15 characters |
price | DOUBLE | mandatory, values between 0 and 100 000 |
amount | INT | mandatory, values between 0 and 10 000 |
Orders-table | ||
---|---|---|
Field | Data Type | Qualifications |
dd_customers | INT | primary key, values between 0 and 9999, foreign key which references to table Customers |
id_products | INT | primary key, values between 0 and 9999, foreign key which references to table Products |
date | Date | mandatory, time has to include seconds,... and year |
amount | INT | mandatory, values between 0 and 10 000 |
Optimizing a relational database involves a series of best practices and techniques aimed at improving performance, ensuring efficient data retrieval, and maintaining data integrity. Here are some key strategies to optimize your relational database.
Proper indexing is crucial for query performance. Indexes allow the database to locate and retrieve data quickly. Focus on indexing columns that are frequently used in WHERE clauses, JOIN operations, and as foreign keys.
Write efficient SQL queries. Avoid using SELECT * and instead, specify only the columns you need. Use JOINs appropriately and ensure that your queries leverage indexes effectively.
SELECT id, name FROM Users WHERE age > 25;
Normalize your database to reduce redundancy and ensure data integrity. However, in some cases, denormalization can improve performance by reducing the number of JOIN operations.
Perform regular maintenance tasks such as updating statistics, rebuilding indexes, and checking for database consistency. These tasks help maintain optimal performance.
Choose the most appropriate data types for your columns. Using the correct data type can save space and improve query performance. For example, use INTEGER for numeric data instead of VARCHAR. But, remember that you should not use INTEGER example for phonenumbers or postalcodes.
Continuously monitor your database performance using tools and logs. Analyze slow queries and understand their execution plans to identify bottlenecks and areas for improvement.
Optimizing a relational database is an ongoing process that involves careful planning, regular maintenance, and continuous monitoring. By implementing these strategies, you can significantly enhance the performance and reliability of your database.
In a database context, selectivity (also known as the "selectivity factor") refers to a metric that describes how well certain indexes or queries can differentiate between records. It essentially measures the fraction of rows selected relative to the total number of rows in a table when executing a specific query or using a particular index.
Low Selectivity: If a query returns only a small portion of the total rows in a table, the selectivity is low. This is usually desirable, as low selectivity means the query is very specific and the index is efficient.
High Selectivity: If a query returns a large portion of the rows in the table, the selectivity is high. This might indicate that the index is not very efficient or the query is too broad.
In the example there is person
table with 10 rows. If you run a query that returns 3 rows, the selectivity is 30%. If the query returns 2 rows, the selectivity is 20%.
SELECT * FROM person WHERE country = 'Finland';
This query returns 3 persons out of 10, so the selectivity is 30%.
SELECT * FROM person WHERE city = 'Helsinki';
This query returns 2 persons out of 10, so the selectivity is 20%. This means the condition is more selective than the firts one, and an index on the city
field can be effective in optimizing this query.
Selectivity is thus a crucial tool in optimizing database performance and designing effective indexes.