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.

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 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 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:

When you have check that there is no many-to-many relationships in your database, you can add the fields (columns) in your ER-model. And then the ER-model could look like this:

Note: The primary key is underlined.

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 1
Table OrderV1 has keys

• idOrder1: primary key
• idCustomer: foreign key
• idProduct: foreign key

So the foreign keys does not belong to the primary key and the Relationships are non-identifying.
The ER-diagram made with MySQL Workbench looks this

Example 2
Table OrderV2 has keys

• idcustomer,idProduct: primary key
• idCustomer: foreign key
• idProduct: foreign key

So the foreign keys belongs to the primary key and the Relationships are identifying.
The ER-diagram made with MySQL Workbench looks this

MySQL Workbench uses solid line for identifying relationships and non-solid for 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

Requirement analysis for Netshop-database

1. Introduction

This is a simple netshop where customers can buy products.

2. Actions

• N1: We are able to add products
• N2: Every product has a name and price
• N3: Customer can check the name and price of the products
• N4: Customer can check how many pieces of particular product is on the store
• N5: We are able to check: who has ordered, what product and when
• N6: At this stage we are satisfied, if the customer can order only one procuct-type at a time. This means that the customer can choose a product and then how many pieces, but the customer can not include another product to the same order.
• N7: We need to know the address of the Customers, so that we can send the products to them
• I1: We need to know the email-address of our customers
• D1: We would like to know how old our customers are

3. Connections

We have already made the payment system, so you don't have to take care of that.

4. Other features

We would like that the shop is open 24 hour.

In above example "N" means that the feature is necessary, "I" means that it is important(but not mandatory) and "D" means desirable (it would be nice to have).

ER-model for Netshop-database

Normalization of Netshop-database

NF1

There is no similar fields in any tables. And all the fields are divided into pieces. So the database is in NF1

NF2

Customerss and Products has a primary key which consist of one field, so they are in NF2. In Orders-table Date and Amount are not dependent on Customers_ID or Products_Id, so the databse is in NF2.

NF3

In Customers table City is dependent on postalcode, even postalcode is not the primary key. So the database is not in NF3. And this should be fixed!

Field definitions for NetShop-database

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
email	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

Referential Integrity in NetShop-database

• We are not allowed to delete customer, if that customer has orders
• If we have to change Customers_Id, we will cascade the changes to Orders
• We not allowed to delete product, if that product has orders
• If we have to change Products_Id, we will cascade the changes to Orders

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;

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.

• Index Design: Low selectivity is a good indicator that an index can improve query performance. If an index is selective, it significantly reduces the number of rows that need to be scanned.
• Query Optimization: Database query optimizers use selectivity to assess which indexes to use for improving query performance.
• Resource Management: Understanding selectivity helps database administrators make better decisions regarding resource usage and query performance.

Selectivity is thus a crucial tool in optimizing database performance and designing effective indexes.