Search results
CHAPTER 8 (5/E) LECTURE OUTLINE. Database views provide convenient usage. • Virtual view realized when query uses that view. Materialized views allow efficient re-use. • Must be recalculated or updated when base tables change. VIEWS FOR CUSTOMIZATION. Consider database(s) describing university’s activities. • Academic institution.
- 330KB
- 8
- Trigger options
- Transition variables
- BEFORE UPDATE OF age ON User REFERENCING OLD ROW AS o, NEW ROW AS n FOR EACH ROW
- Views
- Creating and dropping views
- Using views in queries
- Why use views?
- Modifying views
- INSERT INTO PopularUser VALUES(987, 0.3);
- CREATE VIEW AveragePop(pop) AS SELECT AVG(pop) FROM User;
- SQL92 updateable views
- INSTEADOF
- Indexes
- Examples of using indexes
- Creating and dropping indexes in SQL
- DROP INDEX ;
- Choosing indexes to create
- SQL features covered so far
Possible events include: INSERT ON table DELETE ON table UPDATE [OF column] ON table Granularity—trigger can be activated: FOR EACH ROW modified FOR EACH STATEMENT that performs modification Timing—action can be executed: AFTER or BEFORE the triggering event INSTEAD OF the triggering event on views (more later)
OLD ROW: the modified row before the triggering event NEW ROW: the modified row after the triggering event OLD TABLE: a hypothetical read-only table containing all rows to be modified before the triggering event NEW TABLE: a hypothetical table containing all modified rows after the triggering event Not all of them make sense all the time, e.g. AFTE...
WHEN (n.age < o.age) SET n.age = o.age; BEFORE triggers are often used to “condition” data Another option is to raise an error in the trigger body to abort the transaction that caused the trigger to fire
A view is like a “virtual” table Defined by a query, which describes how to compute the view contents on the fly DBMS stores the view definition query instead of view contents Can be used in queries just like a regular table
Example: members of Jessica’s Circle CREATE VIEW JessicaCircle AS SELECT * FROM User WHERE uid IN (SELECT uid FROM Member WHERE gid = 'jes'); Tables used in defining a view are called “base tables” User and Member above To drop a view DROP VIEW JessicaCircle;
Example: find the average popularity of members in Jessica’s Circle SELECT AVG(pop) FROM JessicaCircle; To process the query, replace the reference to the view by its definition SELECT AVG(pop) FROM (SELECT * FROM User WHERE uid IN (SELECT uid FROM Member WHERE gid = 'jes')) AS JessicaCircle;
To hide data from users To hide complexity from users Logical data independence If applications deal with views, we can change the underlying schema without affecting applications Recall physical data independence: change the physical organization of data without affecting applications To provide a uniform interface for different implementations or...
Does it even make sense, since views are virtual? It does make sense if we want users to really see views as tables Goal: modify the base tables such that the modification would appear to have been accomplished on the view
No matter what we do on User, the inserted row will not be in PopularUser
• Note that you can rename columns in view definition
More or less just single-table selection queries No join No aggregation No subqueries Arguably somewhat restrictive Still might get it wrong in some cases See the slide titled “An impossible case” Adding WITH CHECK OPTION to the end of the view definition will make DBMS reject such modifications
triggers for views CREATE TRIGGER AdjustAveragePop INSTEAD OF UPDATE ON AveragePop REFERENCING OLD ROW AS o, NEW ROW AS n FOR EACH ROW UPDATE User
An index is an auxiliary persistent data structure Search tree (e.g., B+-tree), lookup table (e.g., hash table), etc. More on indexes later in this course! An index on . can speed up accesses of the form . = . > (sometimes; depending on the index type) An index on . , ... , . can speed up . = ∧ ⋯ ∧ . = . , ... , . > , ... , (again depends) Ordering...
SELECT * FROM User WHERE name = 'Bart'; Without an index on User.name: must scan the entire table if we store User as a flat file of unordered rows With index: go “directly” to rows with name='Bart' SELECT * FROM User, Member WHERE User.uid = Member.uid AND Member.gid = 'jes'; With an index on Member.gid or (gid, uid): find relevant Member rows dir...
CREATE [UNIQUE] INDEX ( ,..., ON ); • With UNIQUE, the DBMS will also enforce that , ... , is a key of
Typically, the DBMS will automatically create indexes for PRIMARYKEY and UNIQUE constraint declarations
More indexes = better performance? Indexes take space Indexes need to be maintained when data is updated Indexes have one more level of indirection Optimal index selection depends on both query and update workload and the size of tables • Automatic index selection is now featured in some commercial DBMS
Query Modification Constraints Triggers Views Indexes
- 143KB
- 27
Suppression of details of data organization and storage. Highlighting of the essential features for an improved understanding of data. Includes basic operations. Retrievals and updates on the database. Dynamic aspect or behavior of a database application. Allows the database designer to specify a set of valid operations allowed on database objects.
- 234KB
- 19
Dec 1, 2003 · A database system is. an integrated collecti on of related files, along with details of the interpretation of the data. contained therein. Basically, database system is nothing more than a ...
- Bhojaraju Gunjal
Database System Concepts - 7th Edition 1.11 ©Silberschatz, Korth and Sudarshan View of Data A database system is a collection of interrelated data and a set of programs that allow users to access and modify these data. A major purpose of a database system is to provide users with an abstract view of the data. • Data models
Changes in Oracle Database Release 19c, Version 19.1 xxiii Changes in Oracle Database Release 18c, Version 18.1 xxiv 1 Introduction to Oracle Database About Relational Databases 1-1 Database Management System (DBMS) 1-1 Relational Model 1-2 Relational Database Management System (RDBMS) 1-2 Brief History of Oracle Database 1-3 Schema Objects 1-4 ...
People also ask
What is a bibliographic database?
What is a database system?
What is a comprehensive introduction to database systems?
What is a conceptual view in a DBMS?
What is the main purpose of a database system?
What are logical views in database design?
german rodriguez martinez. This introduction to database systems offers a comprehensive approach, focusing on database design, database use, and implementation of database applications and database management systems. KEY TOPICS: The first half of the book provides in-depth coverage of databases ... See full PDF. download Download PDF.
