19.2. 19.2 Views

A database system architecture has three main levels:

The goal of the separation of levels is to reach data independence and the convenience of users. The three views on Figure 19.2 show possible user interfaces: multirelational, universal relation and graphical interface.

Figure 19.2.  The three levels of database architecture.

The three levels of database architecture.


The physical layer consists of the actually stored data files and the dense and sparse indices built over them.

The separation of the logical layer from the physical layer makes it possible for the user to concentrate on the logical dependencies of the data, which approximates the image of the reality to be modelled better. The logical layer consists of the database schema description together with the various integrity constraints, dependencies. This the layer where the database administrators work with the system. The connection between the physical layer and the logical layer is maintained by the database engine.

The goal of the separation of the logical layer and the outer layer is that the endusers can see the database according to their (narrow) needs and requirements. For example, a very simple view of the outer layer of a bank database could be the automatic teller machine, or a much more complex view could be the credit history of a client for loan approval.

19.2.1. 19.2.1 View as a result of a query

The question is that how can the views of different layers be given. If a query given by relational algebra expression is considered as a formula that will be applied to relational instances, then the view is obtained. Datalog rules show the difference between views and relations, well. The relations defined by rules are called intensional, because these are the relations that do not have to exist on external storage devices, that is to exist extensionally, in contrast to the extensional relations.

Definition 19.21 The expression given in some query language over schema is called a view.

Similarly to intensional relations, views can be used in definition of queries or other views, as well.

Example 19.7 SQL view. Views in database manipulation language SQL can be given in the following way. Suppose that the only interesting data for us from schema CinePest is where and when are Kurosawa's film shown. The view KurosawaTimes is given by the SQL command

KurosawaTimes

  1  
                        CREATE
                      
                     
                        VIEW
                      KurosawaTimes 
                        AS
                        2    
                        SELECT
                      Theater, Time   3    
                        FROM
                      Film, Show   4    
                        WHERE
                      Film.Title=Show.Title 
                        AND
                      Film.Director="Akira Kurosawa" 

Written in relational algebra is as follows.

Finally, the same by datalog rule is:

Line 2 of KurosawaTimes marks the selection operator used, line 3 marks that which two relations are to be joined, finally the condition of line 4 shows that it is a natural join, not a direct product.

Having defined view , it can be used in further queries or view definitions like any other (extensional) relation.

19.2.1.1.  Advantages of using views

  • Automatic data hiding: Such data that is not part of the view used, is not shown to the user, thus the user cannot read or modify them without having proper access rights to them. So by providing access to the database through views, a simple, but effective security mechanism is created.

  • Views provide simple “macro capabilities”. Using the view KurosawaTimes defined in Example 19.7 it is easy to find those theatres where Kurosawa films are shown in the morning:

    Of course the user could include the definition of KurosawaTimes in the code directly, however convenience considerations are first here, in close similarity with macros.

  • Views make it possible that the same data could be seen in different ways by different users at the same time.

  • Views provide logical data independence. The essence of logical data independence is that users and their programs are protected from the structural changes of the database schema. It can be achieved by defining the relations of the schema before the structural change as views in the new schema.

  • Views make controlled data input possible. The wITH CHECK OPTION clause of command cREATE VIEW is to do this in SQL.

19.2.1.2.  Materialised view.

Some view could be used in several different queries. It could be useful in these cases that if the tuples of the relation(s) defined by the view need not be calculated again and again, but the output of the query defining the view is stored, and only read in at further uses. Such stored output is called a materialised view.

Exercises

19.2-1 Consider the following schema:

Relation FilmMogul contains data of the big people in film business (studio presidents, producers, etc.). The attribute names speak for themselves, Certificate# is the number of the certificate of the filmmogul, PresidentCert#) is the certificate number of the president of the studio. Give the definitions of the following views using datalog rules, relational algebra expressions, furthermore SQL:

  1. RichMogul: Lists the names, addresses,certificate numbers and assets of those filmmoguls, whose asset value is over 1 million dollars.

  2. StudioPresident: Lists the names, addresses and certificate numbers of those filmmoguls, who are studio presidents, as well.

  3. MogulStar: Lists the names, addresses,certificate numbers and assets of those people who are filmstars and filmmoguls at the same time.

19.2-2 Give the definitions of the following views over schema CinePest using datalog rules, relational algebra expressions, furthermore SQL:

  1. Marilyn(Title): Lists the titles of Marilyn Monroe's films.

  2. CorvinInfo(Title,Time,Phone): List the titles and show times of films shown in theatre Corvin, together with the phone number of the theatre.