Relational databases, Logical structure - Databases

Relational databases

Mastering the properties of data models (MD) is complicated by the fact that different data models use their own terminology for describing structural elements and their relationships. To simplify the study of MD, the corresponding terms are compared with the concepts used in the relational data model.

Brief description of the structure of data models

Data Model

Structure element

Communication

Table structure

Relational

Tables: columns - fields, rows - records

By Key

Linear

Hierarchical

Segments: Source and child - Table Analogs

For a pointer

Linear

Networking

Entries: owner and member - analogues of tables

By the pointer and by the key (the connection is called); a collection of records and a link form a set

Linear, nonlinear

Object-relational

Objects (tables, abstract data types)

By Key

Linear, nonlinear

Object-oriented

Classes of objects (data types, data): object - string, columns - properties (constants, built-in objects, data streams, collections, multidimensional variables, links)

On an object reference and an object pointer

Linear, nonlinear

When discussing data models (Chapters 5-9), we will adhere to such a single-type sequence;

1) a logical structure that includes a description of the structure (elements, links);

2) creating a database within the framework of the data model (MD) under consideration;

3) using the database;

4) properties of the data model (advantages and disadvantages). A comparative table of MD properties is given in Ch. 9.

Logical Framework

Relational databases are widely used in personal computers. The most known are such local DBMS as dBASE, Paradox and especially Access. DBMS Oracle, Sybase, Informix, BTrieve, Ingress, InterBase were originally designed to work in a network with large amounts of data.

The relational model is based on the mathematical concept of the set-theoretic relation, which is a subset of the Cartesian product of the domain list.

A domain is a set of values ​​(for example, a set of integers). The Cartesian product of domains D1, D2, .., Dk (denoted by D, × D2 × ... × Dk) is the set of all tuples (V1, V2, ..., Vk) of length k such that Vi & Icirc; D1, i = I, I. For example, if k = 2, D & Icirc; = {0, 1} and D2 = {a, b, c}, then D1 × D2 is {(0, a), (0, b), (0, c), (1, a), (1, b), (1, c)), and the relation can be, for example, , a), (0, c), (1, b)}.

Relational elements are called tuples and have arity to (degree of relation), with the i-th component being V1. The relation is conveniently represented by a table-the totality of all tuples; Each line is a tuple and each column corresponds to one component. Tuples are usually numbered and their number determines the dimension of the table. Columns are called attributes, and they are often given names. An ordered list of relationship attribute names is called a relationship schema. If the relationship is called Student and its scheme has the attributes A1, A1, .... Ak, then we will write such a scheme as STUDENT (A1, A2, ..., Ak).

A set of relationship schemes is called a relational database scheme, and the current values ​​of the corresponding relationships are a database.

Data from the relationship diagram are represented by two kinds of relationships.

1. A set of objects can be represented by a relation containing all the attributes of a given set of objects. If the objects of the set are identified by linking to another object, then the relationship schema additionally contains the attributes of the second set key.

2. The relationship between the sets of objects E1, E2, Ek is represented by a relation whose schema consists of the key attributes of each of these sets.

A relational model is a representation of a database in the form of a set of ordered normalized relations.

Relational relationships are characterized by the following features.

1. Any type of record contains only simple (by structure) data elements.

2. The order of the tuples in the table is not significant.

3. The ordering of meaningful attributes in the tuple must match the ordering of the attributes in the relational relationship.

4. Any relationship must contain one or more attributes that together make up a unique primary key.

5. If there is a relationship between the two relational relations, then one relation is the original relation, the second is the subordinate.

6. In order for a relationship to exist between the two relational relationships, the attributes that serve as the primary key in the source relationship must also be present in a subordinate relationship.

E. Codd originally proposed 12 (dozen) rules, in fact, the requirements that a relational database must satisfy.

1. Information rule. All information on a logical level is represented only by values ​​in tables without using pointers and indexes.

2. Rule of guaranteed access. Each atomic element of the table is accessible through a combination of the table name, field name and key.

3. System support for Null values. To represent missing data with any type, use the ull-value.

4. Dynamic operational directory based on the relational model. Metadata (dictionaries) are generated in the same languages ​​as data.

5. The rule of an exhaustive data sublanguage. It is possible to use several programming languages ​​in the database, however one (most often - SQL) should be the main one.

6. The view update rule (view, View). All theoretically updated views can be updated by the system.

7. Input, update and delete data at a high level. Working with multiple records should be not only a sample request, but also an update request.

8. Physical independence of data. It is possible to change the address of the database, change the physical layout of the database without affecting the operation of applications and the user.

9. Logical independence of data. When adding or removing elements (tables, fields) in the database structure, other parts of the database remain unchanged.

10. Independence of integrity. The key must not be Null. The primary and parental keys must be unique. Each value of the foreign key must have the value of the parent key. Referential integrity reduces performance due to checking the conditions-links through the dictionary.

11. Independence of distribution. In a distributed database, the data location is independent. For a user such a database should act as a centralized database.

12. Rule of compliance. You can not bypass the restrictions introduced with SQL.

Example 5.1. Imagine a database Learning process in as a relational model (see Table 5.1). Further relations (for example, Table 5.1, a ) will be written in another form:

GROUP (Group number, Title, Number, {Average},.

The underlined attribute is the key.

Table 5.1

a) Attitude Group

Group_Copy

Title

Number

Average Score

1

AND | 1

16

4.3

2

I2

23

4.0

3

I3

18

4.2

b) Student's attitude

Number_Each_Key

Group_Copy

Full name of the student

Year of birth

Average Score

I-1746

1

Gray AP

1979

4.1

I-1747

2

Kirov PG

1980

4.0

I-1748

3

Sukhov PN

1981

4.5

c) Faculty: "Chair:

Department code

Title

Phone

Department

1

MIS

154-12-86

Sorokin PV

2

APP

171-12-05

Borisov B.V.

3

CCI

212-10-81

Stepanov IV

d) Attitude Teacher

Table_number

Full name of the teacher

Uch_degree

Teaching

Department code

381

Shatalov A.S

dt. Mr.

Professor

1

101

Sidorov AT

To. t, n

Associate Professor

2

402

Tarakanov PT

to. f.-m. n

Associate Professor

3

e) Attitude Item

Item Code

Title

Total hours

Practice/laboratory

Semestron

P1

Informatics

350

130

2

P2

Cybernetics

300

120

3

n3

Mathematics

600

200

4

e) Attitude & Study;

Group_Copy

Object count

Number of Number

Activities

Clock

2

P2

402

Practical

2

P2

381

Lectures

1

P3

381

Lectures

1

P3

401

Practical

g) Attitude Progress

Group_Copy

Number of invalid_count

Item Code

Table Number

Activities

Score

1

I-1746

P3

381

Exam

5

2

I-1747

P3

381

Exam

4

3

I-1748

P3

381

Exam

3

Database links database

Fig. 5.1. Database Linking Schema & "Learning Process"

The procedures for creating and using relational databases are based on the theory of relational databases, discussed in detail in Ch. 4. Its results are used for applied purposes. When you enter a data structure, you use the appropriate data formats. For the table Instructor they are presented in Table. 5.2. The entire database (Table 5.1) is represented in 4NF, so we will reflect the scheme of relations between its relations (Figure 5.1), where the underlined fields are primary keys. The list of tables and fields with their data formats, specified in the design process, is given in Appendix 1, and the connection diagram is in Ch. 5.

Table 5.2

Data types of the relationship data "Teacher

Field name

Key

Unique field

Required field

Data type

Size

Field Signatures

Table_number

Primary

Yes

Yes

Numeric

Whole

Tab N

Full name of the teacher

-

No

Yes

Text

30

PFIO

Uch_degree

-

No

No

Text

12

Ust_st

Teaching

-

No

No

Text

12

Uch_zp

Department code

External

No

Yes

Text

6

Code_Cafe

thematic pictures

Also We Can Offer!

Other services that we offer

If you don’t see the necessary subject, paper type, or topic in our list of available services and examples, don’t worry! We have a number of other academic disciplines to suit the needs of anyone who visits this website looking for help.

How to ...

We made your life easier with putting together a big number of articles and guidelines on how to plan and write different types of assignments (Essay, Research Paper, Dissertation etc)