In lecture 1 we explain the fundamental concepts of databases. In particular we present the relational data model. Modern commercial databases are based on this model. We have also enclosed some information on the client-server architecture which is used in database applications. At the end of the lecture you will find a brief summary of the history of databases.
None of the exercises for this lecture require using any software.
Here are some general facts that drive the whole domain of databases.
Some aspects of an information system highlight its implementation nature:
Relation data model was invented by Edgar Codd and presented for the first time in a paper in 1970. From the mathematical point o view a database is a set of relations. This is why we talk about the relational data model and relational databases. Mathematicians define a relation as a subset of the Cartesian product of some sets. A relation may be represented as a two-dimensional table that consists of rows and columns. We assume that:
TeacherId | FirstName | LastName | Title |
---|---|---|---|
2137 | Lech | Banachowski | Prof. |
3245 | Krzysztof | Stencel | Dr. |
8976 | John | Smith | Mr. |
LectureName | Code | TeacherId |
---|---|---|
Databases | DBA | 2137 |
Information systems | INS | 3245 |
Internet technologies | INT | 3245 |
Object-oriented programming | OOP | 8976 |
Expert systems | EXS | 2137 |
Column TeacherId of table Lectures is specific, because:
|
The primary key of the table Lectures is the column Code. The column LectureName is a unique key. The primary key of the table Teacher is the column TeacherId. The column SecondName need not be a key. |
Please answer the following simple question:
How many primary keys may a table have? |
A foreign key is the set of one or more columns whose values are also the values of the primary key or a unique key of the related table (it can be the same table). The values of these columns are interpreted as pointers to the rows of the related table.
Column TeacherId of table Lectures is a foreign key that references column TeacherId of table Teachers. For example number 2137 in the row of table Lectures that describes lecture "Databases" is a reference to the row of table Teachers that stores information on the teacher named "Banachowski". This relationship reads: Lecture "Databases" is taught by Lech Banachowski. |
Please answer the following simple question:
How many foreign keys may a table have? |
Domains of all columns are extended by the special value called Null
.
The meaning of Null
is the lack of values. It could be:
Null
is something else than the empty string or zero).
Null
yield Null
as the result
(expression Null=Null
also evaluates Null
).
It concerns almost all operations that have Null
as the argument
- their value is Null
.
Therefore, Null
is the third logical value besides
True
and False
. Here are the truth tables for three major
logical connectives:
OR
)OR | True | False | Null |
True | True | True | True |
False | True | False | Null |
Null | True | Null | Null |
AND
)AND | True | False | Null |
True | True | False | Null |
False | False | False | False |
Null | Null | False | Null |
NOT
)NOT | True | False | Null |
False | True | Null |
It is time for a simple question:
Is
it possible that a logical expression is true, when one if its arguments is Null ?
|
Is Null
and Is Not Null
They allow recognizing null values.
However, be careful, because(X Is Null)
evaluates toTrue
, whenX
isNull
(X Is Null)
evaluates toFalse
, whenX
is notNull
Here is another simple question:(X = Null)
always evaluates toNull
(even forX
beingNull
)
What
is the logical value of expression (Null=Null) Is Null ?
|
Integrity constraints are conditions that must be fulfilled by data in a database, e.g.
0 < Age < 140
birthDate < hireDate
Null
or occurs also in the appropriate column of the
associated primary (or unique) key.
The sum of the salaries of all employees of a department equals the wage fund of this department.
Again a simple question:
Which popular data model does not enforce referential integrity? |
A view is a virtual table to be used by the users, e.g.
LectureName | Teacher |
---|---|
Databases | Banachowski |
Information systems | Stencel |
Internet technologies | Stencel |
Object-oriented programming | Smith |
Expert systems | Banachowski |
The content of a usual view is calculated from the base tables at each usage. It is not stored in the database. However, sometimes we need to save this content in the database. Then we use this frozen image and do not recompute it again and again. Such view is called materialized.
An index is an additional data structure that accelerates access to the data in a table, when you search rows with particular values in a set of columns. For example, the index built for column LastName accelerates the search for the teacher with a given last name. The purpose of an index in a database is similar to that of the index in a book.
The same data of a database may be viewed and processed in many different ways. The point of view depends on the level of abstraction. The basic levels of abstraction are:
The user level is intended mainly for the end users. The logical level is used by the system data administrator, while the database administrator (shortly dba) works with the physical level. Of course, the designer of the database defines and works with all three levels.
Each level is somewhat independent. For example, you can change the location of the data on the disk as well as the storage parameters without any impact on the logical structure of the tables. Such a possibility is called the physical data independence. You can also change tables without any change of the applications, provided these are based on views and not directly tables. This feature is called the logical data independence.
Consider our sample database. Its application can consist in displaying the information on who delivers lectures, i.e. displaying the content of a view Lectures-Teachers. In the future it may turn out that some lectures are given by more than one teacher. The schema of the database with two tables Teachers and Lectures is no longer sufficient. A new associative table is required. It will store the relationship between teachers and lectures. The definition of view Lectures-Teachers must be changed as well. All these changes apply to the logical level of the database. The application itself is based on this view and thus it need not be changed. It is an example of the meaning of the logical data independence.
The physical data independence becomes important, when the disk is already full and we have to buy a new one. New rows will be stored on the new disk, but neither the schemata of the tables nor the applications have to be changed.
The catalog (or data dictionary or metadata) is the collection of tables and views that describe the schema of the database. In other words, the catalog contains definitions of all the objects of all the three levels of the database. The name and the data type of a column are examples of metadata.
It is important to use the relational data model for the catalog. This ensures that the metadata are stored and processed the same way as the data.
Database applications usually consists of at least two parts:
During this lecture we explained the fundamental notions that apply to databases. We also emphasized the importance of databases for information systems. In particular, we presented the primary data model of modern databases. We call it the relational data model. We described the following concepts of this model: table, key, primary key, foreign key, view, pseudo-value Null and integrity constraints.
Null
or occurs also
in the apropriate column of the associated primary (or unique) key.