Introducing the relational model (reproduced in the Database.System.Concepts [2.1Structure of Relational Databases] to [2.3Keys])

2.1 Structure of the relational database

A relational database consists of a set of tables, each of which is assigned a unique name. For example, consider the instructor table in Figure 2.1, which stores information about instructors. This table has four columns, namely ID, name, dept_name, salary. Each line of the form records the information of an instructor. Similarly, the curriculum in Figure 2.2 Stores course information. Information for each course is subject to course identification, name, Dept_name and credits. Note that each instructor is identified by the ID column, and each course is identified using the course logo.

Figure 2.3 is a third table, as in this example, which stores the prerequisites for each course. This table has two columns, namely the course identification and prerequisites. Each line has two courses, and the second one is the prerequisite for the first course.

Therefore, in each row of this table implies that in a sense, one of the two courses is the premise of another course.

Again, as we consider the Teacher's table, each row of the table can be considered to be a unique ID, a consistent name, a department name, and a salary of four relationships.

Typically, each row of a table represents the relationship of a set of values. Because tables are a collection of such relationships, the relationship between the concept of a table and the mathematical concept is consistent, and the relational data model is its name. In mathematical jargon, a tuple is a simple value sequence (or list). The relationship between n values is represented by an array of N. In other words, an array has n values that match each row in the table.

Therefore, in a relational model, term relationships are used to reference tables, and term tuples are used to reference rows. Similarly, the term attribute is used to reference columns.

For example, in table 2.1, we can see that the instructor table has four columns, namely ID, name, dept_name, salary.

We use a term instance to refer to a particular instance of a relationship. In other words, a specific set of rows is included. An example of the instructor in Figure 2.1 shows that there are 12 tuples that conform to the information of the 12-bit instructor.

In this chapter, we use a number of different potential relational data models to illustrate a wide variety of concepts. These relationships represent a part of the university, and they do not include all the data that actually a university database can contain, because it simplifies its presentation. We should discuss the criteria suitable for relational structures for chapters 7 and 8.

The order of a relationship with a tuple element is irrelevant, because a relationship is a set of arrays. Therefore, whether the relationship array is arranged in order, 2.1, or not in order, 2.4, is not related. This relationship is the same in two charts. Because all two contain the same set of arrays. For ease of interpretation, most of our relationships are categorized according to the first attribute.

Each property contained in each relationship has a set of allowable values, called an attribute range. The salary range is the attribute range of all instructors ' salary values, and the name range of all instructors is also the attribute range of the instructor's name.

We note that for all relationships R, all properties R is the atomic domain. The elements of an atomic domain are considered to be irreducible combinations. For example, suppose the instructor table has a phone number, which can store the phone number of each instructor. However, this phone number is atomic, because the tuple element's range is a set of phone numbers, and it has its composition, that is, the personal phone number in the collection.

The important question is not the scope of its own, but the scope of our data. Suppose the phone number is a property that only stores a number, and even if we split these values into country codes, area codes, and local numbers, we can use it as a non-atomic domain. If we try to use each phone number as a single, non-divided combination, then the phone number will have an atomic domain.

In this chapter, and in chapters 3rd through 6th, we assume that all properties have atomic domains. In the 22nd chapter, we extend the relational data model to allow atomic domains.

A null value is a special value that indicates that the value is unknown or nonexistent. For example, let's say that before we include the phone number in the attributes of the instructor table, the instructor simply doesn't have a phone number or the phone number is not listed. We use null values to indicate that we do not know or do not exist at this time. We can see at a later time that the null value creates a lot of difficulties when we want to evaluate or update the data, so we want to exclude it as much as possible. We assume that null values are not originally present, and in part 3.6 we will describe the effects of the hollow values on the different operations.

2.2 Database Schema

When we refer to a database, we should distinguish it from the database schema, which is the logic designed for the database. A DB instance is a snapshot of the data in a database for a short period of time.

A relational concept conforms to the variables of the programming language, and the concept of the relational pattern conforms to the type definition of the programming language.

Typically, a relational pattern consists of a series of attributes and their scope. Before we learn the 3rd chapter of the Sol language, we don't have to consider the scope of the previously defined attribute.

The view of the relationship case conforms to the concept of the variable value, and the value of the given variable may change over time. The project content of similar relationship cases will change and update with the change of time. In contrast, relationship patterns usually do not change.

While it is important to understand the differences between relationship patterns and relationship instances, we often use the same names, such as using instructors to refer to patterns and cases. When needed, we explicitly refer to the pattern or to the instance. For example, "Instructor mode" or "Instructor relationship case". However, here we can clearly know whether the expression is a pattern or a case, we just use the relationship name.

Think seriously about the residential relationship in Figure 2.5, which includes (Dept_name, construction, advance)

Note that the Dept_name name is present both in the teaching mode and in the departmental structure. This repetition is not a coincidence. Instead, it is commonly used to think of relational patterns as one way to connect to another tuple of relationships. For example, suppose we want to find out which instructor is in Watson, our first thing is to find all the departments in Watson, and then we compare to find whether there is consistency.

Let's go on to the University database example.

Each course in the university may be increased by the duration of the study, across different semesters or within the semester. In class, we need a relationship to describe an individual's needs or choices. This model includes: Course logo, SEC ID, semester, year, building, room number, time blank.

Figure 2.6 shows an example of a partial relationship.

We need a relationship to describe the relationship between the instructor and the course they are teaching. The relationship pattern to describe is figure 2.7, which illustrates the same situation as the instructor relationship.

As you can imagine, there are many relationships included in the university database, in addition to the relationships we have enumerated, instructors, addresses, courses, part of the previous conditions and teachings. In the textbook we use the following relationship:

(1) Students (ID, name, Dept_name, tot_cred)

(2) Recommended by (s_id, i_id)

(3) Holding (ID, course logo, sec_id, semester, year, grade)

(4) Classroom (building, house number, capacity)

(5) Time (idle time, start time, end time)

2.3 Keys

We must have a way to specify how the tuples in a given relationship are eliminated. This is represented by their properties. That is, the values of the tuple attribute values must be so that they can uniquely identify the tuple. In other words, any two tuples in a relationship do not allow formatting attributes that have exactly the same value.

A super key is a set of one or more properties that are assembled together to allow us to uniquely identify a tuple in a relationship. For example, the id attribute of a relationship instructor is sufficient to differentiate between an instructor tuple and another instructor tuple. Therefore, the ID is a super key. On the other hand, the instructor's name is not a super-key, because the instructor's name may be the same.

Formally, let R represent the relationship, the set of properties in the pattern R. If we say that a subset of R K is a super key of R, then we restrict the example of relationship R, where no two different tuples have the same value for all attributes in K. That is to say, if T1 and T2 both in T and T1 is not equal to T2, so k.t1 is not equal to K.T2.

A super key can contain unrelated properties. For example, the combination of ID and name is the relationship instructor. If K is a super key, then any superset of K is the same. We are often interested in super keys that do not have subsets. This minimal superset key is called a candidate key.

There may be several sets of different properties that can be used as candidate keys. Suppose a combination of name and dept_name is sufficient to distinguish the members of an instructor relationship. Then, {ID} and {Name,dept_name} are candidate keys. Although the attribute ID and name can distinguish the instructor group, their combination {Id,name} does not constitute a candidate key, because the property ID itself is the candidate key.

We will use the term primary key to represent the candidate key chosen by the Database Designer as the primary means of identifying tuples in the relationship. The key, whether a primary key, a candidate key, or a super-key, is the property of the entire relationship. Neither of the two individual tuples in a relationship can have the same value for a key property at the same time. The designation of a key represents a constraint in the real-world enterprise that is being modeled.

You must carefully select the primary key. As we have said, a person is clearly not enough, because there are many people of the same name. In the United States, a person's social Security number attribute will be a candidate key. Because non-American residents usually do not have social Security numbers, international businesses must produce their own unique identifiers. Another approach is to use some unique combinations of other properties as keys.

The primary key should be selected so that its property values will never change or change very little. For example, a person's address field should not be part of a primary key because it is likely to change. On the other hand, social security figures are guaranteed not to change. The unique identifiers generated by the enterprise generally do not change unless two enterprises are merged; In this case, two enterprises may emit the same identifiers and may need to reassign identifiers to ensure they are unique.

The primary key properties of the relational schema are typically listed before other properties; For example, the Dept_name property of the department is listed first because it is the primary key. The primary key attribute also includes underscores.

A relationship, such as R1, can then include the primary key of another relationship, such as R2, in its properties. This property is called a foreign key in R1 and refers to R2. The relationship R1 is also referred to as a foreign key-dependent reference relationship, and R2 is referred to as a foreign key. For example, because Dept_name is the primary key for deyarding, the attribute Dept_name in the instructor is a foreign key from the instructor-referenced department. In any DB instance, given any tuple (for example, TA), from the instructor relationship. There must be tuples in the departmental relationship, such as TB, so that the value of the Dept_name property of the TA is the same as the value of the TB primary key dept_name.

Now consider teaching relationships to this section. The reasonable requirement is that if a course has a class, at least one teacher must be taught. However, there may be more than one teacher teaching this lesson. In order to enforce this constraint, we will require that a particular combination of (course identification, sec_id, semester, year) appear in the section. However, this set of values does not constitute the primary key for teaching because more than one teacher can teach this part. Therefore, we cannot declare a foreign-key constraint from one section to another (although we can define a foreign key constraint in another direction, from a foreign key constraint to a section). The constraint from section to instruction is an example of referential integrity.

Constraints from section to TES are an example of referential integrity constraints, which require that the specified attribute of at least one tuple in the referenced relationship appear in the specified attribute of any tuple in the reference relationship.

Introducing the relational model (reproduced in the Database.System.Concepts [2.1Structure of Relational Databases] to [2.3Keys])