Lecture 15+16 Flashcards
What is a view?
A virtual table derived from other base tables or views, doesn’t have to exist in physical form. They provide security and access control mechanisms, as well as providing ways to access data in a customizable fashion.
How do we create and drop views?
CREATE VIEW AS ;
DROP VIEW ;
How are views implemented? What are two major ways?
Views are maintained by the DBMS and are always up to date with the underlying base tables. Can be implemented as query modification(view query transformed into query on underlying base tables). Or view materialisation(create temporary view table when view first queried, incrementally update to keep consistent with base tables).
Why is query modification of views inefficient? How can we improve basic view materialization?
complex queries take time to generate, and multiple queries may be applied to the same view in a short period.
In view materialisation the table can be kept materialiazed while it is being queried, if not queried for a certain period of time the table can be dropped and recomputed.
What is a big problem with updating views?
They can be complicated and ambiguous, particularly when joins come in, as these could be interpreted multiple ways. Problem could be fixed by changing one of two values, or more.
When can we do updates with views?
A view with a single defining table is updateable if a primary key or candidate key is contained, views defined on tables using joins are generally not updatable, neither are those defined using grouping and aggregate functions. The clause WITH CHECK OPTION must be added at the end of the view definition to allow updating of the view.
What is the system catalog?
A minidatabase, storing metadata which describes schemas, provides descriptions on Tables, attributes, data types, constraints, owner, primary and foreign keys, views, etc.
In oracle it is called the data dictionary, access to it is allowed through views, the categories of which can be: USER(what is in users schema), ALL(what user can access) or DBA(database administrators view).
What are the main DBMS architectures?
Centralised(all functionality, application program execution, and user interface processing occur on one machine) and client/server architecture(software components are distributed over clients and servers).
Parallel (operations carried out simultaneously on multiple parallel computers).
Distributed (database is stored on several computers).
How is Oracle structured?
it is an object-relational DBMS which contains:
- An Oracle database
- An Oracle server instance (has a system global area(SGA), an area of memory for database information shared by the database users, and a set of processes, user processes execute application programs and Oracle processes are server processes that work for user and background processes.)
Memory is structured as the SGA, containing database buffers, redo log buffers, etc, and the Program global area(PGA), a memory buffer containing data and control information for a server process.
What is the Oracle process architecture?
Server processes handle requests from connected user processes, a single user process gets a dedicated server configuration and multiple user processes use multi-thread server configuration.
Background processes perform I/O and monitor other Oracle to provide increased parallelism for better performance and reliability.
Examples of background processes are: Database writer, log writer, checkpoint, system monitor, process monitor, recoverer process, and the archiver.
What occurs at startup and shutdown? Who can do them?
Startup involves starting processes and allocating SGA, mounting a database and opening it.
Shutdown involves closing the database, dismounting the database and shutting down the oracle server instance.
These must both be down by the database administrator.
How is the oracle database logically structured?
into tablespaces, and data blocks, extents, and segments.
Data files are created in table spaces, the table spaces can be SYSTEM or User table spaces.
A database block contains a common and variable header, a table directory, a row directory, and then free space/row data.
A datablock takes up a specific number of bytes(can range from 512-8k). An extent is a specific number of continous data blocks. A segment is a set of extents for a logical structure.
What is the physical storage structure? (not disc)
datafiles which contain all database data, a datafile can only be associated with one database.
Redo log files are made up of redo entries, they record all changes made to data for recovery.
Control files contain the database name, the time stamp of database creation, and the names and locations of datafiles and redo log files.
