Term
|
Definition
| Complete information system that uses database. Basic elements include: users, database application, database management system, and a database. |
|
|
Term
| Functions of Database Applications |
|
Definition
Create and process forms
Process user queries
Create and process reports
Execute application logic
Control database applications |
|
|
Term
|
Definition
|
|
Term
|
Definition
Relation instance: a table, columns and rows
Relation schema: specifies the name of the relation, plus the name and type of each column |
|
|
Term
|
Definition
| CREATE TABLE Tablename (colname type(length)); |
|
|
Term
|
Definition
DROP TABLE Tablename;
Deletes a table; the schema information and the tuples are deleted. |
|
|
Term
|
Definition
| ALTER TABLE Tablename ADD COLUMN columnname type; |
|
|
Term
|
Definition
| INSERT INTO Tablename(columnname1,2,3) VALUES (col1info, col2info, col3info); |
|
|
Term
|
Definition
| DELETE FROM Tablename WHERE columnname="something"; |
|
|
Term
|
Definition
| SELECT * FROM Tablename; (this picks everything, give stipulations with where) |
|
|
Term
|
Definition
| E/R model is convenient for representing the high level database design. Moving from ERD to schema is straightforward. |
|
|
Term
|
Definition
Specify the source of data
If multiple tables are used, specify relationships between tables
Select desirable fields from the table(s) and options related to the fields
Specify criterion |
|
|
Term
|
Definition
Relation=Table
Attribute=Column |
|
|
Term
|
Definition
1) The name of the relation is different from all others
2) Each cell of the relation contains only one value
3) Each attribute (or field) has a name that is distinct
4) All the values of a particular attribute are from the same domain
5) The orders of the attributes makes no difference
6) There are no duplicate tuples
7) The order of the tuples makes no difference |
|
|
Term
|
Definition
|
|
Term
|
Definition
|
|
Term
|
Definition
| Domain, attribute, tuple, primary key, degree, cardinality |
|
|
Term
|
Definition
| Entity integrity and referential integrity |
|
|
Term
| Data Manipulation Operations |
|
Definition
| Defined through relational algebra and equivalent relational calculus |
|
|
Term
|
Definition
A relation, but we store a definition, rather than a set of tuples.
CREATE VIEW Viewname(columnname1,columnname2) AS SELECT Columnname1, Columnname2 from Table1, Table2.
Presents information while hiding underlying relation(s)
Advantages: simplify query commands, assist with data security, enhance programming productivity, contain most current base table data, use little storage space, provide customized view for user, establish physical data independence
Disadvantages: use processing time each time view is referenced, may or may not be directly updateable |
|
|
Term
| Conceptual Database Design |
|
Definition
Process of describing data, relationships between the data, and the constraints on the data
After analysis - gather the essential data required and understand how it's related
The focus is on the data, not the process
Output is a Conceptual Data Model |
|
|
Term
|
Definition
| Data model is derived from an intimate understanding of the business |
|
|
Term
|
Definition
| Data model is derived by reviewing specifications and business documents |
|
|
Term
|
Definition
Top Down approach. A detailed, logical representation of the entities, associations and data elements for an organization or business
Three main constructs: data entities, relationships, attributes |
|
|
Term
|
Definition
| A single noun, descriptive and specific, concise. |
|
|
Term
|
Definition
Four Steps:
1) Develop a logical data model for each known user interface for the application using normalization principles
2) Combined normalized data requirements from all user interfaces into one consolidated logical database model
3) Translate the conceptual ER data model for the application into normalized data requirements
4) Compare the consolidated logical database design with the translated ER model and produce one final logical database model for the application |
|
|
Term
|
Definition
Steps
1) Represent Entities
2) Represent relationships
3) Normalize the relations
4) Merge the relations |
|
|
Term
| Cold Backup (Consistent Backup) |
|
Definition
The only way to make a consistent whole database backup is to shut down the database with the normal, immediate, or transactional options and make the backup while the database is closed
Advantage: no recovery is required after datafiles are restored - quicker restore
Disadvantage: no access to database during backup time (depends on size/speed) |
|
|
Term
| Hot Backup (Inconsistent Backup) |
|
Definition
IF the database must be up and running 24 hours a day, you can run the database in ARCHIVELOG mode and perform an online backup
Advantage: database remains open during backup
Disadvantage: large databases may have performance impact during backup, recovery takes longer and is slightly more complex |
|
|
Term
|
Definition
Logical backups are exports of schema objects, like tables and stored procedures, into a binary file. Oracle utilities are used to move Oracle schema objects in and out of Oracle.
Not recommended for backup of a whole database, but useful for backing up individuals objects or schemas or moving data into another database |
|
|
Term
|
Definition
| A set of schemas that constitute the description of a database |
|
|
Term
|
Definition
The structure that contains descriptions of objects created by a user (Base tables, views, constraints).
Control processing/storage efficiency: choice of indexes, file organization for base tables, file organizations for indexes, data clustering, statistics maintenance.
Creating indexes: speed up random/sequential access to base table data. Example: CREATE INDEX NAME_IDX ON Tablename(Columnname) |
|
|
Term
| Data Definition Language (DDL) |
|
Definition
Commands that define a database, including creating, altering, and dropping tables and establishing constraints.
CREATE SCHEMA, CREATE TABLE, CREATE VIEW, etc. |
|
|
Term
| Data Manipulation Language (DML) |
|
Definition
| Commands that maintain and query a database |
|
|
Term
| Data Control Language (DCL) |
|
Definition
| Commands that control a database, including administering privileges and committing data |
|
|
Term
|
Definition
| Constraint that ensures that foreign key values of a table must match primary key values of a related table in 1:M relationships. |
|
|
Term
|
Definition
Modifies data in existing rows.
UPDATE Tablename SET Columnname = thing WHERE Columnitem = thing |
|
|
Term
|
Definition
| Makes it easier to update a table. Allows combination of insert and update in one statement. |
|
|
Term
|
Definition
Clauses of the SELECT Statement:
SELECT: list columns that should be returned from the query
FROM: indicates table or view where data will be obtained
WHERE: indicate conditions under which a row will be included
GROUP BY: indicate categorization of results
HAVING: indicate conditions under which a category (group) will be included
ORDER BY: sorts the result according to specified critera |
|
|
Term
|
Definition
| Wildcard, ex %Desk will include results with anything preceding the word Desk. |
|
|
Term
|
Definition
Operator that is more efficient than or
Ex WHERE STATE IN('FL', 'TX', 'CA', 'HI') |
|
|
Term
|
Definition
| A "virtual table" created dynamically upon request by a user. No data actually stored; instead data from base table made available to user |
|
|
Term
|
Definition
| Copy or replication of data, data actually stored. Must be refreshed periodically to match the corresponding base tables. |
|
|
Term
| Union, intersection, difference |
|
Definition
| Expressed by (subquery) UNION/INSTERSECT/EXCEPT (subquery) |
|
|
Term
|
Definition
| SUM, AVG, COUNT, MIN, MAX, COUNT |
|
|
Term
|
Definition
| Actual execution of DBMS software that manages data in the databases tablespace |
|
|
Term
|
Definition
-Created on loading the software from disk to memory
-It is an aggregation of processes and memory structures
-It is shareable thus allowing multiple users to access the same database |
|
|
Term
|
Definition
| The datafiles contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the datafiles allocated for a database |
|
|
Term
|
Definition
| Every Oracle database has a control file. A control file contains entries that specify the physical structure of the database such as Database name and the Names and locations of datafiles and redo log files |
|
|
Term
|
Definition
| The primary function of the redo log is to record all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, then the changes can be obtained from the redo log, so work is never lost. |
|
|
Term
|
Definition
| Oracle automatically archives log files when the database is in ARCHIVELOG mode. This prevents Oracle from overwriting the redo log file before they have been safely archived to another location. |
|
|
Term
|
Definition
Parameter files contain a list of configuration parameters for that instance and database.
Parameters:
1) Name and locations of the control files
2) Block Size
3) Cache sizes
4) Database name
5) Instance name
6) Domain Name
7) Is read each time a database instance is started
8) has a .ora suffix |
|
|
Term
| Alert and Trace Log Files |
|
Definition
| Each server and background process can write to an associated trace file. When an internal error is detected by a process, it dumps information about the error to its trace file. The alert log of a database is a chronological log of messages and errors. |
|
|
Term
|
Definition
| A database is divided into logical storage units called tablespaces, which group related logical structures together. One of more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace. |
|
|
Term
|
Definition
| At the finest level of granularity, Oracle database data is stored in data blocks. One data block corresponds to a specific number of bytes of physicaly database space on disk. The standard block size is specified by the DB_BLOCK_SIZE initialization parameter |
|
|
Term
|
Definition
| Contiguous units of storage, usually disk space, within a segment. Oracle uses extents for performance reasons by storing data that needs to be retrieved in a single disk I/O. An extent is made up of multiple data blocks. |
|
|
Term
|
Definition
| A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user. Schema objects are the logical structures that directly refer to the database's data. Schema objects include structures like tables, views, and indexes. |
|
|
Term
|
Definition
| Shared memory region that contains data and control information for one Oracle Instance |
|
|
Term
|
Definition
| Database buffers store the most recently used blocks of data. The set of database buffers in an instance is the database buffer cache. The buffer cache contains modified as well as unmodified blocks. Because the most recently (and often, the most frequently) used data is kept in memory, less disk I/O is necessary, and performance is improved |
|
|
Term
| Redo Log Buffer of the SGA |
|
Definition
| The redo log buffer stores redo entries—a log of changes made to the database. The redo entries stored in the redo log buffers are written to an online redo log, which is used if database recovery is necessary. The size of the redo log is static |
|
|
Term
|
Definition
| The shared pool contains shared memory constructs, such as shared SQL areas. A shared SQL area is required to process every unique SQL statement submitted to a database. A shared SQL area contains information such as the parse tree and execution plan for the corresponding statement. |
|
|
Term
|
Definition
| This database background process performs instance recovery at the start of the database. SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during crash and instance recovery because of file read or offline errors. It coalesces i.e. combines contiguous free extents into larger free extents. |
|
|
Term
|
Definition
| This database background process cleans up failed user processes. PMON is responsible for releasing the lock i.e. cleaning up the cache and freeing resources that the process was using. Its effect can be seen when a process holding a lock is killed. |
|
|
Term
|
Definition
– This background process is responsible for managing the contents of the data block buffer cache and dictionary cache. DBWR performs batch writes of changed block. Since Oracle uses write-ahead logging, DBWR does not need to write blocks when a transaction commits. In the most common case, DBWR writes only when more data needs to be read into the system global area and too few database buffers are free. The least recently used data is written to the datafiles first.
– Although there is only one SMON and one PMON process running per database instance, one can have multiple DBWR processes running at the same %me. Note the number of DBWR processes running is set via the DB_WRITER_PROCESSES. |
|
|
Term
|
Definition
| This background process manages the writing of the contents of the redo log buffer to the online redo log files. LGWR writes the log entries in batch form. The Redo log buffers entries always contain the most up-to-date status of the database. |
|
|
Term
|
Definition
| The Archiver process reads the redo log files once Oracle has filled them and writes a copy of the used redo log files to the specified archive log destination(s). Actually, for most databases, ARCH has no effect on the overall system performance. On some large database sites, however, archiving can have an impact on system performance. |
|
|
Term
|
Definition
| Contains machine-language code and execution plans for frequently used SQL commands |
|
|
Term
|
Definition
| a work area given for backup and recovery operations |
|
|
Term
|
Definition
| stores the machine-language and execution plans for Java commands used in application programs and database operations |
|
|
Term
|
Definition
| Responsible for signaling DBWN and LGWR to write the contents of the Database Buffer Cache and Redo Log Cache to the data files and Redo Log files respectively |
|
|
Term
|
Definition
| Detect and correct errors as a result of communications problems in a distributed database environment |
|
|
Term
|
Definition
| Used to organize tablespace data within a tablespace. A segment stores individual database objects like a table or index. |
|
|
Term
|
Definition
Database Administrator
• Installation, configuration, upgrading and patching of Oracle software
• Creation and maintenance databases and their objects
• Establishment and maintenance backup and recovery policies and procedures
• Implementation and maintenance database security
• Performance and tuning of databases
• Plan growth and changes (capacity planning)
• Give consultation to development teams
• Interface with Oracle Corporation for technical support. |
|
|
Term
|
Definition
| The mechanism that protects the database against intentional or accidental threats, ie theft and fraud. |
|
|
Term
|
Definition
| The granting of a privilege that enables a user to have a legitimate access to a system. Also called access controls. |
|
|
Term
|
Definition
| A mechanism of determining whether a user is who they claim to be. System admin is responsible for giving system access to individual user accounts. |
|
|
Term
|
Definition
| Requires authorization for specific objects |
|
|
Term
|
Definition
| Allow users to have complete access to all objects within the database |
|
|
Term
|
Definition
| Encoding of data by a special algorithm that renders the data unreadable by any program without the decryption key. Used in security. |
|
|
Term
| RAID (Redundant Array of Independent Disks) |
|
Definition
| RAID works on having a large disk array comprising an arrangement of several independent disks that are organized to improve reliability and at the same %me increase performance. |
|
|
Term
|
Definition
| Inserting malicious SQL code through an application interface |
|
|
Term
|
Definition
| SQL commands that assign or take away privileges. |
|
|
Term
|
Definition
Data Store
Data Extraction and filtering: extract and validate data from the operational database and external data sources
End-User Query Tool: create queries that access either the Operational or the DSS database
End User Presentation Tools: organize and present the Data |
|
|
Term
|
Definition
Technique for assembling and managing data from various sources for the purpose of answering business questions. Thus making decisions that were not previous possible
A decision support database maintained separately from the organization’s operational database.
Single, integrated source of decision support information formed by collecting data from multiple sources, internal to the organization as well as external, and transforming and summarizing this information to enable improved decision making.
Designed for easy access by users to large amounts of information, and data access is typically supported by specialized analytical tools and applications.
Subject oriented, integrated, time variant, non volatile. |
|
|
Term
| 12 Rules of a Data Warehouse |
|
Definition
Data Warehouse and Operational Environments are separated
• Data is integrated
• Contains historical data over a long period of time
• Data is a snapshot data captured at a given point in %me
• Data is subject-oriented 12 Rules of Data Warehouse
• Mainly read-only with periodic batch updates
• Development Life Cycle has a data driven approach versus the traditional process-driven approach
• Data contains several levels of detail – Current, Old, Lightly Summarized, Highly Summarized
• Environment is characterized by Read-only transactions to very large data sets
• System that traces data sources, transformations, and storage
• Metadata is a critical component – Source, transformation, integration, storage, relationships, history, etc
•Contains a chargeback mechanism for resource usage that enforces optimal use of data by end users |
|
|
Term
|
Definition
| Smaller, more focused Data Warehouse |
|
|
Term
|
Definition
Discover previously unknown data characteristics, relationships, dependencies, or trends. A methodology designed to perform knowledge-discovery expeditions over the database data with minimal end-user intervention.
Data->Information->Knowledge |
|
|
Term
|
Definition
Data Preparation: identify the main data sets to be used
Data Analysis and Classification: study the data and identify common characteristics or patterns
Knowledge Acquisition: use the results of analysis to use modeling or algorithms
Prognosis: predict future behavior, forecast business outcomes |
|
|
Term
|
Definition
• Forecasting what may happen in the future
• Classifying people or things into groups by recognizing patterns
• Clustering people or things into groups based on their attributes
• Associating what events are likely to occur together
• Sequencing what events are likely to lead to later events |
|
|
