CS 263 Lec 2 Three schema architecture for

CS 263 Lec. 2: Three- schema architecture for database development • Conceptual Schema (during the Analysis phase) is a detailed specification of the overall structure of organisational data that is independent of any database management technology) – depicted in ER or objectmodelling notations (a data model). Specifications stored as metadata in repository or data dictionary • External Schema (or user view) is some portion of the database that is required for a user to perform some task. Also independent of database technology but typically contains a subset of the associated conceptual schema. Often the original description of a user view is is a computer screen display, business transaction or report.

Three schema architecture • Internal Schema (physical schema) contains specifications for how data from a conceptual schema are stored in a computer’s secondary memory.

Three-schema database architecture

Process of developing three-schema architecture for a database project

Three-tiered database location architecture • The data for a given information system may reside in multiple locations or tiers of computers, in order to balance various technical and organisational factors • Four tiers are possible for data to be on – on a client server, an application server, a Web server and a database server. However, three tiers are more commonly considered (Fig. 2 -10): • Client tier – typically a desktop or laptop computer, which concentrates on managing the user-system interface and localised data (also called presentation tier). Web scripting tasks may be executed here.

Three-tiered • Application/Web server tier – processes HTTP protocols, scripting tasks, performs calculations and provides access to data (also called process services tier). • Enterprise server (minicomputer or mainframe tier) – performs sophisticated calculations and manages the merging of data from multiple sources across the organisation (also called the data services tier)

Three-tiered client/server database architecture

Reasons for using a client/server architecture • It allows for simultaneous processing on multiple processors for the same application, thus improving application response time and data processing speed • It is possible to take advantage of the best data processing features of each computer platform (e. g. the advanced user interface capabilities of PCs versus the speed of minicomputers and mainframes) • Can mix client technologies (by different companies such as Intel, Sun, Motorola) and yet share common data • Can change technologies in any tier with limited impact on the other tiers

Reasons for using a client/server architecture • Processing can be performed close to the source of processed data, improving response times and reducing network traffic • Allows for and encourages open system standards • Ease of separating the development of the database and the modules that contain the database from the information system modules that present the contents to end users. These are typically developed in Powerbuilder, VB or Delphi and interact through middleware to the routines that access and analyse the data

Using and defining views • Views provide users controlled access to tables. Advantages of dynamic views: – Simplify query commands – Help provide data security and confidentiality – Enhance programming productivity – Contain most current base table data – Use little storage space – Provide a customised view for a user – Establish physical database independence

Using and defining views • Disadvantages of views: • Use processing time re-creating view each time it is referenced • May or may not be directly updateable • Some disadvantages can be overcome by using materialised views which are stored physically on disk and refreshed at appropriate intervals

The SELECT clause • Specifies (or projects) what data elements (columns) are to be included in the view table • The FROM clause lists the tables and views involved in the view development • The WHERE clause specifies the names of the common columns used to join the tables • Because a view is a table its rows may not be sorted

Views • e. g. , build a query to generate an invoice for order number 1004 - where rather than having to specify the joining of four tables, the query can include all relevant data elements from the view table INVOICE_V: • CREATE VIEW INVOICE_V AS • SELECT CUSTOMER_ID, CUSTOMER_ADDRESS, PRODUCT_ID, QUANTITY etc. • FROM INVOICE_V • WHERE ORDER_ID = 1004;

Views • A view may join multiple tables or views together and may contain derived (or virtual) columns • e. g. , if a user only wants to know the total value of the orders placed for each furniture product, a view can be created from the previous view (INVOICE_V) • We can assign a different name (alias) to a view column than the associated base table or expression column name • Here PRODUCT is a renaming of PRODUCT_ID, local to only this view

Views • TOTAL is the column name given to the expression for total sales of each product • The expression can now be referenced via this view in subsequent queries as if it were a column (rather than a derived expression) • CREATE VIEW ORDER_TOTALS_V AS • SELECT PRODUCT_ID PRODUCT, SUM(UNIT_PRICE*QUANTITY) TOTAL • FROM INVOICE_V • GROUP BY PRODUCT_ID;

Views • Views can establish security because tables and columns that are not included will not be obvious to users of the view • Restricting access to a view with GRANT and REVOKE statements adds another layer of security • Some people advocate the creation of a view for every single base table, even if that view is identical to the base table • This can create to greater programming productivity as databases evolve, through greater flexibility when base tables are changed

Views • The following examples are based on the following views: • CREATE VIEW CUSTOMER_V AS SELECT * FROM CUSTOMER_T; • CREATE VIEW ORDER_V AS SELECT * FROM ORDER_T; • CREATE VIEW ORDER_LINE_V AS SELECT * FROM ORDER_LINE_T; • CREATE VIEW PRODUCT_V AS SELECT * FROM PRODUCT_T;

Views • Updating data directly from a view rather than from base tables is possible under certain limitations • Usually permitted as long as the update is unambiguous in terms of data modification in the base table • When the CREATE VIEW statement contains any of the following five situations, that view may not be updated directly: • 1. The SELECT clause includes the keyword DISTINCT (see later) • 2. The SELECT clause contains expressions, including derived columns, aggregates, statistical functions etc.

Views • 3. The FROM clause, or a subquery, or a UNION clause references more than one table • 4. The FROM clause or a subquery references another view that is not updateable • 5. The CREATE VIEW command contains a GROUP BY or HAVING clause • It could happen that an update to an instance would result in the instance disappearing from the view. • A view EXPENSIVE_STUFF_V lists all furniture products where the unit price is > $300.

Views • That view will include PRODUCT_ID 5, a writers desk = $325 dollars. If we update this view and reduce writer’s desk to $295, the desk will no longer appear in the view because its unit price is now less than $300. • If it is desired to track everything with an original price over $300, we must include a WITH CHECK OPTION clause after the SELECT clause in the CREATE VIEW COMMAND. • This will cause UPDATE or INSERT statements to be rejected when they would cause rows to be removed from the view

Sample CREATE VIEW • CREATE VIEW EXPENSIVE_STUFF_V AS • SELECT PRODUCT_ID, PRODUCT_NAME, UNIT_PRICE • FROM PRODUCT_T • WHERE UNIT_PRICE >300 • WITH CHECK_OPTION; • CHECK_OPTION works only for updateable views and prevents updates that would create rows not included in the view, or cause updated or inserted rows to be removed from the view