1. Uniqueness: Each surrogate key is unique across the table it is used in.
2. Non-meaningful: The value of a surrogate key does not have a business meaning and is not derived from business data.
3. Stability: Surrogate keys do not change if the data in the row changes.
4. Simplicity: They are typically simple numeric values, which makes them easy to index and join.

1. Simplicity: Easier to use as a primary key compared to composite or natural keys, which might involve multiple columns or complex data types.
2. Stability: Remains constant even when the data in the table changes, reducing the risk of key updates.
3. Performance: Often improves performance for indexing and join operations due to their simplicity and smaller size compared to natural keys.

1. Lack of Business Meaning: Since surrogate keys do not contain business information, they do not provide any context or meaning outside the database.
2. Additional Management: They require an additional column in the table and the logic to generate and manage them.

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 more 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)
)

• The primary key is automatically indexed
• The unique index email_index will prevent using same email to several person and also it makes "search by email queries" faster
• The index name_index, will make "search by name queries" faster

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.

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:

• One-to-one relationship
  
• One-to-many relationship
  
• Many-to-many relationship
  

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 corresponding row in the second table. One-to-one relationships are not very common in databases. This type of relationship is often created to work around limitations of the database management software rather than to model a real-world scenario. For example, you might need to regularly transfer only part of a large table to another application. In this case, you could split the table into 'transferred' and 'non-transferred' sections and link them using 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 corresponding rows in the second table. However, for every row in the second table, there is exactly one matching row in the first table. This type of relationship is also referred to as a parent-child or master-detail relationship. One-to-many relationships are the most commonly modeled relationships in databases.

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:

An identifying relationship is a type of relationship in database design where a child entity cannot exist without a parent entity. This is common in Entity-Relationship (ER) models.

In an identifying relationship, the primary key of the child entity includes the primary key of the parent entity. This means that the identity of the child depends on the parent.

For example, consider the entities Customer and Order. An order must belong to a customer. Therefore, the Order entity cannot exist without the Customer entity. The primary key of Order might be a combination of CustomerID and OrderNumber, forming an identifying relationship.

Key characteristics of an identifying relationship include:

• The child entity depends on the parent for identification.
• The child’s primary key includes the parent’s primary key.
• In ER diagrams, it is typically represented with a solid line.

By contrast, in a non-identifying relationship, the child entity has its own primary key, and the parent’s key is used only as a foreign key.

Identifying relationships are important for maintaining data integrity when entities are tightly linked, ensuring that child data cannot exist without its associated parent data.

In ER-diagram the indentifying relationship is marked with solid line.

Example: Identifying relationship

1. The relationships between hour and person is identifying
2. The relationships between hour and project is identifying

Example: Non-Identifying relationship

1. The relationships between borrow and book is non-identifying
2. The relationships between borrow and borrower is non-identifying

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).

• Both ends mandatory : in this case Person must belong to Department and Department can not exists without Person.
  
• One end mandatory, other end optional : in this case Person must belong to Department, but Department can exists without Person.
  
• One end mandatory, other end optional : in this case Person does not have to belong to Department, but Department can't exists without Person.
  
• Both ends optional : in this case Person doesn't have to belong to Department and Department can exists without Person.
  So, neither entity is mandatory.
  

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.

• Referential integrity rule would prevent you from adding a record to childTable that cannot be linked to parentTable
• Referential integrity might prevent you to delete a row from the parentTable, if that row has child rows. This is called restrict rule
• Referential integrity rules might also specify that whenever you delete a record from parentTable, any records in childTable that are linked to the deleted record will also be deleted automatically. This is called cascading delete
• Referential integrity rules could specify that whenever you modify the value of a linked field in parentTable, all records in childTable that are linked to it will also be modified accordingly. This is called cascading update

The purpose of referential integrity is to take care that all the values of foreign key exists on the parent-table.

1. Determine the purpose of the database - This helps prepare for the remaining steps.
2. Find and organize the information required - Gather all of the types of information to record in the database.
3. Divide the information into tables - Divide information items into major entities or subjects, such as Products or Orders. Each subject then becomes a table.
4. Turn information items into columns - Decide what information needs to be stored in each table. Each item becomes a field, and is displayed as a column in the table.
5. Specify primary keys - Choose each table's primary key. The primary key is a column, or a set of columns, that is used to uniquely identify each row.
6. Set up the table relationships - Look at each table and decide how the data in one table is related to the data in other tables. Add fields to tables or create new tables to clarify the relationships, as necessary.
7. Refine the design - Analyze the design for errors. Create tables and add a few records of sample data. Check if results come from the tables as expected. Make adjustments to the design, as needed.
8. Apply the normalization rules - Apply the data normalization rules to see if tables are structured correctly. Make adjustments to the tables

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.

SELECT id, name FROM Users WHERE age > 25;

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.

SELECT * FROM person WHERE country = 'Finland';

SELECT * FROM person WHERE city = 'Helsinki';