Transcript CS263 Lecture 2

CS263 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