DDL: Views

Thinking back to the three-level ANSI-SPARC architecture, we have said that the external level is a collection of user views of the same data. These views can differ in the subset of entities they contain, the set of attributes required for a particular entity and the names used for entities and attributes.

Often as part of a development project, there is a requirement to provide direct end-user access to data. For example, a senior manager may want to load data into a spreadsheet on a regular basis in order to perform statistical analyses. It is important in such situations that the data structures offered by the database are easily understood by the end user. Where the internal model does not correspond exactly to the end user's requirements, an object called a view can be used to modify the presentation appropriately.

Unlike an index, a view does not usually take up additional space in the database. A view can be though of more as a stored query which is run whenever the view is accessed. We have already seen methods for manipulating the appearance of the results of a query. These include column aliases, calculated fields, date formats and so on, and it is these same methods that are used in the creation of a view. Once a view has been created, it can be referenced in a query in exactly the same way as a table.

As an example, assume that the university management requires data on student performance consisting of the student identifier, the SCQF level and the student's average result at that level. In addition, a target result for each level is required along with the deviation from that target. The following statement could be used to create a view called PERFORMANCE:

    CREATE VIEW performance AS
    SELECT  s.matric_no AS "Student",
            m.level AS "SCQF level",
            AVG(r.result) AS "Result",
            DECODE(m.level, 7,  56,
                            8,  60,
                            9,  65,
                            10, 65,
                            65) as "KPI",
            AVG(r.result) -
            DECODE(m.level, 7,  56,
                            8,  60,
                            9,  65,
                            10, 65,
                                65) as "Deviation"
    FROM    student s
            JOIN registration r ON s.matric_no = r.matric_no
            JOIN module m ON r.module_code = m.module_code
    ORDER BY s.matric_no, m.level

From the end user's perspective, the view will look like a table with the following structure:

Further reading

Create View

SQL CREATE VIEW Statement