Mobile

The data description is the primary key. Relational databases

The Figure shows a table (ratio of degree 5) containing some information about the employees of a hypothetical enterprise. Table rows correspond to tuples. Each line is actually a description of one real world object (in this case, an employee), whose characteristics are contained in the columns. Relational relationships correspond to entity sets, while tuples correspond to entities. The columns in a table representing a relational relationship are called attributes.

Each attribute is defined on a domain, so the domain can be thought of as the set of valid values for that attribute. Several attributes of the same relation, and even attributes of different relations, can be defined on the same domain.

An attribute whose value uniquely identifies tuples is called key (or simply key). The key is the "Personnel number" attribute, since its value is unique for each employee of the enterprise. If tuples are identified only by concatenating the values of several attributes, then the relation is said to have a composite key.

primary key- in relational model given one of the potential keys of the relation, chosen as the primary key (or default key).

A relation can contain multiple keys. Always one of the keys is declared primary, its values cannot be updated. All other relation keys are called possible keys.

From the point of view of the theory, all potential (possible) keys of the relation are equivalent, that is, they have the same properties of uniqueness and minimality. However, as a primary one, one of the potential keys is usually chosen, which is most convenient for certain practical purposes, for example, for creating external keys in other ways, or to create a clustered index. Therefore, the one that has the smallest size (physical storage) and / or includes the fewest attributes is usually chosen as the primary key.

If a primary key consists of a single attribute, it is called simple key.

If a primary key consists of two or more attributes, it is called composite key. So, first name, last name, patronymic, passport number, passport series cannot be primary keys separately, since they can be the same for two or more people. But there are no two personal documents of the same type with the same series and number. Therefore, in a relation containing data about people, the primary key can be a subset of attributes, consisting of the type of personal document, its series and number.

In contrast to the hierarchical and network data models, the relational one does not have the concept of a group relationship. To reflect associations between tuples of different relations, duplication of their keys is used.

Attributes that are copies of the keys of other relationships are called foreign keys.

For example, the relationship between DEPARTMENT and EMPLOYEE is created by copying the primary key "Department_Number" from the first relation to the second. Thus, in order to get a list of employees of a given subdivision, it is necessary: 1) From the DEPARTMENT table, set the attribute value "Department_Number" The corresponding to the given "Department_Name". 2) select all records from the EMPLOYEE table, attribute value "Department_Number" which is equal to the one obtained in the previous step. In order to find out in which department an employee works, you need to perform the reverse operation: 1) Determine "Department_Number" from the EMPLOYEE table. 2) Based on the value obtained, we find an entry in the DEPARTMENT table.

18. Normalization in relational databases, concept normal form when designing databases.

normal form - a property of a relationship in a relational data model that characterizes it in terms of redundancy, which can potentially lead to logically erroneous results of sampling or changing data. Normal form is defined as the set of requirements that a relation must satisfy.

The process of converting a database to a normal form format is called normalization . Normalization is intended to bring the database structure to a form that provides minimal redundancy, that is, normalization is not intended to reduce or increase performance, or reduce or increase the size of the database. The ultimate goal of normalization is to reduce the potential inconsistency of information stored in the database.

Redundancy is usually eliminated by decomposing relations in such a way that only primary facts are stored in each relation (that is, facts that are not derived from other stored facts).

functional dependencies.

A relational database contains both structural and semantic information. The structure of a database is determined by the number and type of relationships included in it, and the one-to-many relationships that exist between the tuples of those relationships. The semantic part describes the set of functional dependencies that exist between the attributes of these relationships. Let us give a definition of functional dependence.

19. 1NF: Basic definitions and transformation rules.

To discuss first normal form, two definitions need to be given:

simple attribute - an attribute whose values are atomic (indivisible).

Complex attribute - is obtained by combining several atomic attributes that can be defined on the same or different domains (it is also called a vector or data aggregate).

Definition of first normal form:

a relation is in 1NF if the values of all its attributes are atomic. . AT otherwise it is not a table at all and such attributes need to be decomposed.

Consider an example:

The HR database of the enterprise needs to store information about employees that you can try to represent in relation to

EMPLOYEE(EMPLOYEE_NUMBER, NAME, BIRTH_DATE, WORK_HISTORY, CHILDREN).

From a careful examination of this relationship, it follows that the attributes "work_history" and "children" are complex, moreover, the attribute "work_history" includes another complex attribute "salary_history".
These units look like this:

 HISTORY_WORK (DATE_RECEPTION, NAME, HISTORY_SALARY),

 HISTORY_SALARY (DATE_APPOINTMENT, SALARY),

 CHILDREN (CHILD_NAME, BIRTH_YEAR).

Their relationship is shown in Fig. 3.3.

Fig.3.3. Initial relationship.

To bring the original relation EMPLOYEE to the first normal form, it is necessary to decompose it into four relations, as shown in the following figure:

Fig.3.4. Normalized set of relations.

Here, the primary key of each relation is highlighted in blue, the names of foreign keys are in font of blue color. Recall that it is foreign keys that serve to represent functional dependencies that exist in the original relation. These functional dependencies are indicated by lines with arrows.

The normalization algorithm is described by E.F. Codd as follows:

Starting with the relation at the top of the tree (Figure 3.3), its primary key is taken, and each immediately subordinate relation is expanded by inserting the domain or combination of domains of that primary key.
The Primary Key of each relation thus extended consists of the Primary Key that the relation had before the expansion and the added Primary Key of the parent relation.
After that, all non-simple domains are deleted from the parent relation, the top node of the tree is removed, and the same procedure is repeated for each of the remaining subtrees.

20. 2NF: Basic definitions and transformation rules.

Very often, the primary key of a relation includes several attributes (in which case it is called composite) - see, for example, the relation CHILDREN shown in fig. 3.4 question 19. This introduces the concept full functional dependence.

Definition:

A non-key attribute is functionally dependent on a composite key if it is functionally dependent on the entire key but not functionally dependent on any of its constituent attributes.

Example:

Let there be a SUPPLY relation (N_SUPPLIER, PRODUCT, PRICE).
A supplier may supply different goods, and the same goods may be supplied by different suppliers. Then the relation key is "N_supplier + item". Let all suppliers supply goods at the same price. Then we have the following functional dependencies:

N_supplier, item -> price
product -> price

Incomplete functional dependence of the "price" attribute on the key leads to the following anomaly: when the price of a product changes, a complete scan of the relationship is required in order to change all records about its suppliers. This anomaly is a consequence of the fact that two semantic facts are combined in one data structure. The following expansion gives the relationship in 2NF:

DELIVERY (N_SUPPLIER, PRODUCT)
PRODUCT_PRICE (COMMODITY, PRICE)

Thus, one can give

Definition of second normal form: A relation is in 2NF if it is in 1NF and every non-key attribute is functionally dependent on the key.

21. 3NF: Basic Definitions and transformation rules.

Before discussing the third normal form, it is necessary to introduce the concept: transitive functional dependency.

Definition:

Let X, Y, Z be three attributes of some relation. In this case, X --> Y and Y --> Z, but there is no reverse correspondence, i.e. Z -/-> Y and Y -/-> X. Then Z transitively depends on X.
Let there be a relation STORAGE ( FIRM, WAREHOUSE, VOLUME), which contains information about firms receiving goods from warehouses and the volumes of these warehouses. Key attribute - "firm". If each firm can receive goods from only one warehouse, then in this respect there are the following functional dependencies:

firm -> stock
stock -> volume

This creates anomalies:

if in this moment no company receives goods from the warehouse, then data on its volume cannot be entered into the database (because the key attribute is not defined)
if the volume of the warehouse changes, it is necessary to view the entire relationship and change the cards for all firms associated with this warehouse.

To eliminate these anomalies, it is necessary to decompose the original relation into two:

STORAGE ( FIRM, STOCK)
STOCK_VOLUME ( STOCK, VOLUME)

Definition of third normal form:

A relation is in 3NF if it is in 2NF and every non-key attribute is not transitively dependent on the primary key.

Earlier in this book, we pointed out certain relationships that exist between certain fields of sample tables. The snum field in the Customers table, for example, corresponds to the snum field in the Salesmen table and the Orders table. The cnum field of the Customers table also corresponds to the cnum field of the Orders table. We have called this type of relationship referential integrity; and during the discussion, you saw how it can be used.

In this chapter, you will explore referential integrity in more detail and find out all about the limitations you can use to support it. You will also see how this limitation enforces when you use DML modification commands. Because referential integrity involves linking fields or groups of fields, often across tables, this action can be somewhat more complex than other constraints. For this reason, it's good to be fully familiar with it, even if you don't plan on creating tables. Your modification commands can be made more efficient with a referential integrity constraint (as with other constraints, but a referential integrity constraint can affect tables other than the one in which it is defined), and certain query functions, such as joins, are repeatedly structured in terms of referential integrity relationships (as highlighted in Chapter 8).

FOREIGN KEY AND PARENT KEY

When all the values in one field of a table are represented in a field in another table, we say that the first field refers to the second. This indicates a direct relationship between the values of the two fields. For example, each of the customers in the Customers table has a snum field that points to the salesperson assigned in the Salespersons table. For each order in the Orders table, there is one and only this seller and one and only this customer. This is displayed using the snum and cnum fields in the Orders table.

When one field in a table refers to another, it is called a foreign key; and the field it refers to is called the parent key. So the snum field in the Customers table is the foreign key, and the snum field it refers to in the Salespersons table is the parent key.

Likewise, the cnum and snum fields of the Orders table are foreign keys that refer to their parent keys named in the Customers table and the Salesmen table. The names of the foreign key and the parent key do not have to be the same, it is just a convention that we follow to make the join more understandable.

MULTI-COLUMN FOREIGN KEYS

In fact, a foreign key does not necessarily consist of only one field. Like a primary key, a foreign key can have any number of fields, all of which are treated as a single unit. The foreign key and the parent key it refers to must, of course, have the same number and field type, and be in the same order. Foreign keys consisting of one field - those that we used exclusively in our sample tables, are the most common. To keep our discussion simple, we will often refer to a foreign key as a single column. This is no coincidence. If this is left unchecked, anyone will say of a field that is a foreign key that it also applies to a group of fields that is a foreign key.

MEANING OF FOREIGN AND PARENT KEYS

When a field is a foreign key, it is related in some way to the table it refers to. What you are saying is, "each value in this field (foreign key) is directly bound to a value in another field (parent key)." Each value (each row) of the foreign key must refer unambiguously to one and only that value (row) of the parent key. If so, then in fact your system, as they say, will be in a referential integrity state. You can see this in an example. The snum foreign key in the Customers table is 1001 for the Hoffman and Clemens rows. Suppose we had two rows in the Salespersons table with a value in the snum = 1001 field. How do we know which of the two salespeople Hoffman and Clemens were assigned to? Likewise, if there are no such rows in the Vendors table, we'll end up with Hoffman and Clemens assigned to a vendor that doesn't exist!

It is clear that each value in the foreign key must be represented once, and only once, in the parent key.

In fact, a given foreign key value can refer to only one parent key value without assuming the reverse is possible: i.e. any number of foreign keys can refer to a single parent key value. You can see this in the sample tables of our examples. Both Hoffman and Clemens are assigned to Peel, so both of their foreign key values match the same parent key, which is great. A foreign key value must refer to only one parent key value, but a parent key value can refer to any number of foreign key values. As an illustration, foreign key values from the Customers table that matched their parent key in the Salesmen table are shown in Figure 19.1. For convenience, we did not take into account gender not related to this example.

FOREIGN KEY CONSTRAINTS

SQL supports referential integrity with a FOREIGN KEY constraint. Although the FOREIGN KEY constraint is a new feature in SQL, it does not yet provide generality. Also, some of its implementations are more complex than others. This function should limit the values you can enter into your database to force the foreign key and parent key to comply with the referential integrity principle. One of the actions of a Foreign Key constraint is to discard values for fields constrained as a foreign key that is not yet present in the parent key. This restriction also affects your ability to change or delete parent key values (we will discuss this later in this chapter).

HOW CAN FIELDS BE REPRESENTED AS FOREIGN KEYS

You use a FOREIGN KEY constraint in a CREATE TABLE (or ALTER TABLE) statement that contains a field that you want to declare as a foreign key. You give them a parent key that you will refer to inside the FOREIGN KEY constraint. Putting this constraint on the command is the same as for the other constraints discussed in the previous chapter. Figure 19.1: Foreign Key of the Customer table with parent key

Like most constraints, it can be a table or column constraint, in the form of a table allowing multiple fields to be used as a single foreign key.

FOREIGN KEY AS A TABLE LIMIT

FOREIGN KEY table constraint syntax: FOREIGN KEY REFERENCES [ ] The first column list is a comma-separated list of one or more table columns that will be created or modified by this command. Pktable is a table containing a parent key. It can be a table that is created or modified current team. The second list of columns is the list of columns that will make up the parent key. The lists of the two columns must be compatible, i.e.:

* They must have the same number of columns.

* In this sequence, the first, second, third, etc., columns of the foreign key column list must have the same data types and sizes as the first, second, third, etc., columns of the parent key column list . The columns in the lists of both columns must not have the same name, although we have used this method in our examples to make the relationship clearer.

Let's create a Customers table with the snum field defined as a foreign key referring to the Salespeople table: CREATE TABLE Customers (cnum integer NOT NULL PRIMARY KEY cname char(10), city char(10), snum integer, FOREIGN KEY (snum) REFERENCES Salespeople (snum ); Be aware that when using ALTER TABLE instead of CREATE TABLE, in order to apply a FOREIGN KEY constraint, the values you specify in the foreign key and parent key must be in a referential integrity state. Otherwise, the command will be rejected. Although ALTER TABLE is very useful from - for its convenience, you will have to first form structural principles in your system, whenever possible, such as referential integrity.

FOREIGN KEY AS A COLUMN LIMIT

The option of restricting a column with a FOREIGN KEY constraint - otherwise called - a referential constraint (REFERENCES), since it actually does not contain the words FOREIGN KEY, but simply uses the word REFERENCES, followed by the parent key, like this: CREATE TABLE Customers ( cnum integer NOT NULL PRIMARY KEY, cname char(10), city char(10), snum integer REFERENCES Salespeople (snum)); The above defines Customers.snum as a foreign key whose parent key is Salespeople.snum. This is equivalent to this table constraint: FOREIGN KEY (snum) REGERENCES Salespeople (snum)

DO NOT LIST PRIMARY KEY COLUMNSM

By using a FOREIGN KEY constraint on a table or column, you may choose not to list the parent key's columns if the parent key has a PRIMARY KEY constraint. Naturally, in the case of keys with many fields, the order of the columns in the foreign and primary keys must match, and in any case, the principle of compatibility between two keys still applies. For example, if we placed a PRIMARY KEY constraint on the snum field of the Salespersons table, we could use it as a foreign key on the Customers table (similar to the previous example) in this command: CREATE TABLE Customers (cnum integer NOT NULL PRIMARY KEY, cname char(10) , city char(10), snum integer REFERENCES Salespeople); This facility was built into the language to encourage you to use primary keys as parent keys.

HOW REFERENCE INTEGRITY RESTRICTS PARENT KEY VALUES

Maintaining referential integrity requires some restrictions on the values that can be represented in fields declared as foreign key and parent key. The parent key must be structured to ensure that each foreign key value will match one specified row. This means that it (the key) must be unique and not contain any null values. This is not sufficient for a parent key if such a requirement is met as when declaring a foreign key. SQL must be sure that double values or empty values(NULL) were not entered into the parent key. Therefore, you must ensure that all genders that are used as parent keys have either a PRIMARY KEY constraint or a UNIQUE constraint, such as a NOT NULL constraint.

PRIMARY KEY AS UNIQUE FOREIGN KEY

Referencing your foreign keys only to primary keys, as we did in generic tables, is a good strategy. When you use foreign keys, you don't just associate them with the parent keys they refer to; you associate them with a specific table row where that parent key will be found. By itself, the parent key does not provide any information that is not already present in the foreign key. The meaning, for example, of the snum field as a foreign key in the Customers table is the relationship it provides, not to the value of the snum field it refers to, but to other information in the Salespersons table, such as salespersons' names, their locations, and so on. . A foreign key is not just a relationship between two identical values; it is a relationship, using these two values, between two rows of the table specified in the query. This snum field can be used to associate any information in a row from the Customers table with a reference row from the Salesmen table - for example, to find out if they live in the same city, who has a longer name, if the seller has any other customers besides this customer customers, and so on. Since the purpose of a primary key is to identify the uniqueness of a row, this is the more logical and less ambiguous choice for a foreign key. For any foreign key that uses a unique key as its parent key, you must create a foreign key that uses the same table's primary key for the same action. A foreign key that has no other purpose than linking rows, resembles a primary key used solely to identify rows, and is a good remedy keep the structure of your database clear and simple, and therefore less hassle.

FOREIGN KEY CONSTRAINTS

A foreign key, in particular, can contain only those values that are actually represented in the parent key or empty (NULL). An attempt to enter other values in this key will be rejected. You can declare a foreign key to be NOT NULL, but this is optional and in most cases not desirable. For example, suppose you enter a customer without knowing in advance which salesperson it will be assigned to. The best way out in this situation is to use a NOT NULL value, which must be changed later to a specific value.

WHAT HAPPENS IF YOU EXECUTE THE MODIFICATION COMMAND

Let's assume that all foreign keys created in our example tables are declared and enforced with foreign key constraints, as follows: CREATE TABLE Salespeople (snum integer NOT NULL PRIMARY KEY, sname char(10) NOT NULL, city char(10) , comm decimal); CREATE TABLE Customers (cnum integer NOT NULL PRIMARY KEY, cname char(10) NOT NULL, city char(10), rating integer, snum integer, FOREIGN KEY (snum) REFERENCES Salespeople, UNIQUE (cnum, snum) ; CREATE TABLE Orders ( cnum integer NOT NULL PRIMARY KEY, amt decimal, odate date NOT NULL, cnum integer NOT NULL snum integer NOT NULL FOREIGN KEY (cnum, snum) REFERENCES CUSTOMERS (cnum, snum);

INCLUDING TABLE DESCRIPTIONS

There are several attributes of such definitions that need to be discussed. The reason we decided to make the cnum and snum fields in the Orders table a single foreign key is to ensure that for each customer contained in the orders, the merchant crediting that order is the same one listed in the Customers table. To create such a foreign key, we would have to place a UNIQUE table constraint on two fields of the Customers table, even if it is not required for that table itself. As long as the cnum field in this table has a PRIMARY KEY constraint, it will be unique in any case, and therefore it is not possible to get another combination of the cnum field with some other field. Creating a foreign key in this way maintains the integrity of the database, even if doing so will prevent you from tampering internally and crediting any vendor other than the one assigned to that particular customer.

From a database integrity standpoint, internal interrupts (or exceptions) are of course undesirable. If you allow them and at the same time want to maintain the integrity of your database, you can declare the snum and cnum fields in the Orders table as independent foreign keys of those fields in the Sales table and the Customer table, respectively. In fact, using the snum field in the Orders table, as we did, is optional, although it was useful to do so for variety. The cnum field linking each order of customers in the Customers table, in the Orders table, and in the Customers table, must always be shared in order to find the correct snum field for that order (allowing no exceptions). This means that we write a piece of information - which customer is assigned to which vendor - twice, and more work will need to be done to make sure both versions are consistent. If we don't have a foreign key constraint as mentioned above, this situation will be especially problematic because each order will need to be checked manually (along with the query) to make sure that the correct seller credited each respective sale. Having this type of information redundancy in your database is called denormalization, which is undesirable in an ideal relational database, although in practice it can be allowed. Demoralization can make some queries run faster, since a query on a single table is always significantly faster than a query on a join.

APPLICATION OF RESTRICTIONS

How do these restrictions affect your ability and inability to use DML modification commands? For fields defined as foreign keys, the answer is quite simple: any values you put into that field with an INSERT or UPDATE command must already be present in their parent keys. You can put NULL values in this field, even though NULL values are not allowed in parent keys if they have a NOT NULL constraint. You can DELETE any rows with foreign keys without using parent keys at all.

Since the question of changing parent key values is involved, the answer, by ANSI definition, is even simpler, but perhaps a little more limited: any value of a parent key referenced by a foreign key value cannot be deleted or changed. This means, for example, that you cannot remove a customer from the Customers table while it still has orders in the Orders table. Depending on how you use these tables, this can be either desirable or troublesome. However, this is certainly better than having a system that allows you to remove a customer with current orders and leave the Orders table referring to non-existent customers. The point of this constraint system is that the creator of the Orders table, using the Customers table and the Sellers table as parent keys, can impose significant restrictions on the actions in these tables. For this reason, you will not be able to use a table that you do not control (i.e., you did not create it and you are not the owner of it) unless the owner (creator) of that table specifically grants you the right to do so (as explained in Chapter 22). There are some other possible actions to change the parent key that are not part of ANSI but can be found in some commercial programs. If you want to change or delete the current parent key reference value, there are basically three possibilities:

You can restrict, or prevent, change (in the ANSI way) by indicating that changes to the parent key are restricted.

You can make a change to the parent key and thereby make changes to the foreign key automatic, which is called a cascading change.

You can make a change to the parent key, and set the foreign key to NULL, automatically (assuming NULLS is allowed in the foreign key), which is called an empty foreign key change.

Even within these three categories, you may not want to process all modification commands in this way. INSERT, of course, is irrelevant. It puts the new values of the parent key into the table so that none of those values can be called at the moment. However, you may want to allow modifications to cascade without deletions, and vice versa. The best situation may be that allows you to define any of the three categories, regardless of the UPDATE and DELETE commands. We will therefore refer to the update effects and delete effects, which determine what happens if you issue an UPDATE or DELETE command on the parent key. These effects we talked about are called RESTRICTED changes, CASCADES changes, and NULL changes. The actual capabilities of your system should be within the strict ANSI standard - modification and deletion effects, both automatically limited - for the more ideal situation described above. By way of illustration, we'll show a few examples of what you can do with the full range of modify and delete effects. Of course, modify and delete effects, which are non-standard features, lack the standard state syntax. The syntax we use here is simple to write and will serve to illustrate the function of these effects.

For the sake of completeness of the experiment, let's assume that you have a reason to change the snum field of the Salesperson table in case our Salesperson table changes partitions. (Usually changing primary keys is not something we recommend doing in practice. It's just another reason for existing primary keys to do nothing but act like primary keys: they shouldn't change.) When you change the vendor number, you want to keep all of its customers. However, if this salesperson leaves his firm or company, you may not want to remove his customers when you remove him from the database. Instead, you'll want to make sure the customers are assigned to someone else. To do this, you must specify UPDATE with a Cascading effect, and DELETE with a Limited effect. CREATE TABLE Customers (cnum integer NOT NULL PRIMARY KEY, cname char(10) NOT NULL, city char(10), rating integer, snum integer REFERENCES Salespeople, UPDATE OF Salespeople CASCADES, DELETE OF Salespeople RESTRICTED); If you now try to remove Peel from the Vendors table, the command will not be valid until you change the snum gender value of customers Hoffman and Clemens to another assigned vendor. On the other hand, you can change Peel's snum field to 1009, and Hoffman and Clemens will be automatically changed as well.

The third effect is NULL changes. It happens that when sellers leave the company, their current orders are not transferred to another seller. On the other hand, you want to cancel all orders automatically for customers whose accounts you delete. By changing the numbers of the seller or customer, you can simply transfer them to him. The example below shows how you can create an Order table using these effects. CREATE TABLE Orders (onum integer NOT NULL PRIMARY KEY, amt decimal, odate date NOT NULL cnum integer NOT NULL REFERENCES Customers snum integer REFERENCES Salespeople, UPDATE OF Customers CASCADES, DELETE OF Customers CASCADES, UPDATE OF Salespeople CASCADES, DELETE OF Salespeople NULLS); Of course, in a DELETE command with an NULL change effect on the Salespersons table, the NOT NULL constraint must be removed from the snum field.

FOREIGN KEYS THAT ARE REFERRED BACK TO THEIR SUB-TABLES

As mentioned earlier, a FOREIGN KEY constraint can present them to this private table as the table's parent key. Far from being simple, this feature can come in handy. Suppose we have an Employees table with a manager field. This field contains the numbers of each of the employees, some of whom are also administrators. But since every administrator is at the same time an employee, he will naturally also be represented in this table. Let's create a table where the employee number (the column named empno) is declared as the primary key and the administrator as the foreign key will refer to it: CREATE TABLE Employees (empno integer NOT NULL PRIMARY KEY, name char(10) NOT NULL UNIOUE , manager integer REFERENCES Employees); (Because the foreign key is the table's referenced primary key, the column list can be excluded.) This table has the contents: EMPNO NAME MANAGER _____ ________ _______ 1003 Terrence 2007 2007 Atali NULL 1688 McKenna 1003 2002 Collier 2007 As you can see, each of them( but not Atali) refers to another employee in the table as its administrator. The atali that has the highest number in the table must have its value set to NULL. This gives another principle of referential integrity. A foreign key that references back to a private table must allow values = NULL. If it's not, how could you insert the first row? Even if this first row refers to itself, the value of the parent key must already be set when the value of the foreign key is entered. This principle will be true even if the foreign key does not directly refer back to the private table, but by referencing another table, which then refers back to the foreign key table. For example, suppose our Salespersons table has an additional field that references the Customers table, so that each table references the other, as shown in next statement CREATE TABLE: CREATE TABLE Salespeople (snum integer NOT NULL PRIMARY KEY, sname char(10) NOT NULL, city char(10), comm declmal, cnum integer REFERENCES Customers); CREATE TABLE Customers (cnum integer NOT NULL PRIMARY KEY, cname char(10) NOT NULL, city char(10), rating integer, snum integer REFERENCES Salespeople); This is called a cross reference. SQL supports this in theory, but in practice it can be a problem. Whichever table of the two is created first is a reference table that does not yet exist for the other. In the interest of providing a cross-reference, SQL actually allows this, but neither table will be usable while they are both in the process of being created. On the other hand, if the two tables are created by different users, the problem becomes even more difficult. Cross reference can become useful tool but it is not without ambiguity and dangers. The previous example, for example, is not entirely usable: because it limits the salesperson to a single customer, and besides, it is not necessary to use a cross-reference to achieve this. We recommend that you be careful in using it and analyze how your programs handle the effects of modification and deletion, as well as the privilege and dialog processing processes before you create a cross reference integrity system. (Privileges and conversational request handling will be discussed in Chapters 22 and , respectively.)

SUMMARY

You now have fairly good reference integrity management. The basic idea is that all foreign key values refer to the specified row of the parent key. This means that each foreign key value must be represented once, and only once, in the parent key. Whenever a value is placed in a foreign key, the parent key is checked to make sure its value is present; otherwise, the command will be rejected. The parent key must have a PRIMARY KEY or a UNIQUE constraint to ensure that the value is not represented more than once. An attempt to change the value of a parent key that is currently represented in a foreign key will be rejected altogether. Your system may, however, offer you the choice to get the value of the foreign key set to NULL or to get the new value of the parent key, and specify which one can be retrieved independently for UPDATE and DELETE commands. This concludes our discussion of the CREATE TABLE command. Next, we will introduce you to another type of command - CREATE. In Chapter 20, you will learn how to represent data objects that look and act like a table, but are actually the results of queries. Some constraint functions can also be performed by views, so you can better assess your need for constraints after you read the next three chapters.

WORKING WITH SQL

1. Create a table named Cityorders. It should contain the same onum, amt, and snum fields as the Orders table, and the same cnum and city fields as the Customers table, so that each customer's order will be entered into this table along with its city. The onum field will be the primary key of Cityorders. All genders in Cityorders must have restrictions when compared to the Customers and Orders tables. It is assumed that the parent keys in these tables already have appropriate constraints.

2. Let's complicate the problem. Redefine the Orders table like this: add new column named prev to be identified for each order, the onum field of the previous order for this current customer. Do this using a foreign key referring to the order table itself. The foreign key must also refer to the customer's cnum field, which provides some prescribed relationship between the current order and the referenced one.

(See Appendix A for answers.)

These are electronic storages of information, access to which is carried out using one or more computers. Typically, databases are created to store and access data containing information about some subject area, that is, some area of human activity or part of the real world.

DBMS is software for creating, filling, updating and deleting a database.

The unit of information stored in the database is a table. Each table is a collection of rows and columns, where the rows correspond to an object instance, a specific event or phenomenon, and the columns correspond to the attributes (features, characteristics, parameters) of the object, event or phenomenon. Each row contains information about a specific event.

In database terms, the columns of a table are called fields, and its rows are called records.

Relationships may exist between individual database tables, that is, information in the previous table may be added to another. Databases, between separate tables of which there are links, are called relational. The same table can be master of one database table and child of another.

Relationship tables interact in a master-child relationship. The same table can be the master of one database table and the child of another.

An object is something that exists and is distinguishable, having a set of properties. The difference between one object and another object is determined by specific property values.

Essence - reflection of an object in the memory of a person or a computer.

Attribute - a specific value of any of the properties of the entity.

Field is a single entry element that stores a particular attribute value.

Communication field – is the field by which two tables are related.

Primary and Secondary Keys

Each database table may have a primary key - this is a field or a set of fields that uniquely identifies a record.

The value of the primary key in the database table must be unique, that is, there must not be two or more records in the table with the same primary key value.

Primary keys make it easy to establish relationships between tables. Since the primary key must be unique, not all fields in the table can be used for it.

If the table does not have fields whose values are unique, to create a primary key, an additional numeric field is usually introduced into it, the values of which the DBMS can dispose of at its discretion.

Secondary keys are set by field, which is often used when searching or sorting data: indexes built on secondary keys will help the system find the necessary values stored in the corresponding fields much faster.

Unlike primary keys, fields for secondary keys may contain non-unique information.

Relational relationships between tables

One to one. A one-to-one relationship exists when one record in the parent table corresponds to one record in the child table.

This relationship is much less common than the one-to-many relationship, it is used if you do not want the database table to swell from the secondary table. A one-to-one relationship causes multiple reads to read related information across multiple tables, slowing down the retrieval of the information you need. In addition, databases that include tables with a one-to-one relationship cannot be considered fully normalized.

Like a one-to-many relationship, a one-to-one relationship can be either rigid or non-rigid.

Keys are fundamental elements of a relational database because they establish a relationship between a pair of tables and provide unique identification for each record in a table. Keys have more importance than establishing relationships; they also help with referential integrity, and they are the main component of table-level integrity. Tables store huge chunks of data in them that typically span thousands of records, all of which are unsorted and disorganized. Obtaining specific data from these many records can be difficult at times or sometimes impossible. This is where the Keys appear. Here we will look at two very important relational database schema keys and the difference between them: Primary Key and Foreign Key.

What is a primary key?

A primary key is a special key that uniquely identifies each record in a table. In a relational database, it's very important to have a unique ID in every row of a table, and a primary key is just what you need to uniquely identify a tuple in a table. A tuple is a set of value attributes in a relational database. A primary key can refer to a column or set of columns in a relational database table that is used to implicitly identify all records in the table. The primary key must be unique for each record because it acts as a unique identifier and must not contain null values. Each database must have one and only one primary key.

What is a foreign key?

A foreign key refers to a field or collection of fields in a database record that uniquely identifies a key field in another database record in another table. Simply put, it establishes a relationship between records in two different tables in a database. It can be a column in a table that points to primary key columns, which means that the foreign key defined in the table is related to the primary key of some other table. Links are critical in relational databases to establish relationships between records, which are essential for database sorting. Foreign keys play an important role in the normalization of relational databases, especially when tables need to access other tables.

Difference between primary key and foreign key

Primary Key and Foreign Key Basics

A primary key is a special key in a relational database that acts as a unique identifier for each record, which means that it uniquely identifies each row/record in a table and its value must be unique for each row of the table. On the other hand, a foreign key is a field in one table that links two tables together. It refers to a column or group of columns that uniquely identifies a row in another table or the same table.

Relationship between primary key and foreign key

A primary key uniquely identifies a record in a relational database table, while a foreign key refers to a field in a table that is the primary key of another table. The primary key must be unique and only one primary key is allowed in a table to be defined, whereas more than one foreign key is allowed in a table.

Duplicate primary key and foreign key values

The primary key is a combination UNIQUE restrictions and Not Null, so a primary key field in a relational database table cannot have duplicate values. No two rows can carry duplicate values for a primary key attribute. Unlike a primary key, a foreign key can contain duplicate values, and a table in a relational database can contain more than a foreign key.

NULL primary key and foreign key

One of the main differences between them is that, unlike primary keys, foreign keys can also contain NULL values. A table in a relational database can have only one primary key, which is not nullable.

Temporary table of primary key and foreign key

A primary key constraint can be implicitly defined on temporary tables and their variables, whereas a foreign key constraint cannot be applied to local or global temporary tables.

Removing Primary Key and Foreign Key

A primary key value cannot be deleted from a parent table that is referred to as a foreign key in a child table. Before dropping a parent table, you must first drop the child table. Conversely, a foreign key value can be removed from a child table even if the value is in the parent table's primary key.

Primary Key vs. Foreign Key: Comparison Table

Summary of Key Keys

Keys play a crucial role in the existence of a database schema for establishing relationships between tables and within a table. Keys Establish Relationships and Apply Various types integrity, especially table-level and relationship-level integrity. First, they consider the table to contain unique records, and the fields you use to establish relationships between tables should contain the appropriate values. Primary key and foreign key are the two most important and common types of keys used in relational databases. A primary key is a special key used to uniquely identify records in a table, while a foreign key is used to establish a relationship between two tables. Both are identical in structure but play different roles in a relational database schema.

In this article we will try to consider everything related to keys in SQL: for what are needed, creation, restrictions of keys. In general: it will be boring 😉

The plan for today is:

In relational database theory − keys these are some entities created to establish certain restrictions that maintain the integrity and availability of data in database tables.

If to speak in simple terms, then the keys in sql are designed to provide additional column functionality. Be it uniqueness or that the column refers to another table (foreign key).

primary key

A column that must be unique in a database is labeled with a primary key. The primary key or primary key means that the value of the primary key column cannot be repeated in the table. Thus, this key allows you to uniquely identify a record in the table without fear that the value of the column will be repeated. Just an example: let's say you have a table of users. This table has fields: full name, year of birth, phone. How to identify a user? Parameters such as full name and phone number cannot be trusted. After all, we can have several users not only with the same last name, but also with the first name. The phone number may change over time and the user with the phone number may not be the one in our database.

That's what the primary key is for. Once assigned a unique identifier and that's it. AT mySql on the example of which we execute all examples from the field AUTO_INCREMENT cannot be set unless you specify that it is the primary key.

I think it's not worth mentioning that a field marked as a primary key cannot be empty when creating a record.

External key ( foreign key)

Is there some more external key (foreign key). It is also called referential. It is needed to link tables together.

If you look at the picture above, then the foreign key will be the supplier field in the shoes table. As a rule, when creating a table, a column of unique integer values is specified. How we did it when we created the table supplier.

Speaker supplier_id will be unique for each entry. Her value and will speak on the revenge column the supplier in the table shoes. I propose to immediately consider, using an example, how a foreign key is created.

Creating a foreign key

create table shoes(shoes_id int auto_increment primary key, title text, size int, price float, count int, type varchar(30), supplier int, foreign key (supplier) references supplier (supplier_id));

As you can see from the example above, the syntax for creating a foreign key is quite simple. You need to add a field to the table, and then declare this field as a foreign key and specify where it will refer to. In this case, the field supplier will refer to the field supplier_id in the table supplier.

Composite key (composite key)

As for the composite key, these are several primary keys in the table. Thus, creating composite key, the uniqueness of the record will be checked against the fields that are combined into this key.

There are situations when, when inserting into a table, you need to check a record for uniqueness in several fields at once. That's what the composite key is for. For example, I will create a simple table with composite key to show the syntax:

Create table test(field_1 int, field_2 text, field_3 bigint, primary key (field_1, field_3));

In the example above, two fields are combined into a composite key and there will be no records in the table with these identical fields.

That's all for the keys SQL. This small tutorial is a preparation for an article where we will take a closer look at how to combine tables so that they make up a single database.