MIDTERM Flashcards
American National Standards Institute (ANSI)
One of two industry-accepted committees that sets standards for SQL
Attribute
A data field, or column
Bridging table
A table created to eliminate a many-to-many relationship between two tables.
Character
The basic unit of data. It can be a letter, number, or special symbol
Candidate key
Any column that could be used as the primary key
Common column
A column that exists in two or more tables and contains equivalent data
Common field
A column that exists in two tables and is used to “join” two tables
Composite key
A unique key that you create by combining two or more columns
Data redundancy
Refers to having the same data in different places within a database, which wastes spaces and complicates database updates and changes
Database
Stores all organizational data in a central location
Database administrator (DBA)
The person who is responsible for installing, administering, and maintaining the database
Database applications
Provide the interface that allows users to interact with the database.
Database management system (DBMS)
An application that performs all routine data-handling operations.
Entity
An object about which you want to store data.
Entity-Relationship (E-R) Model
A diagram that identifies the entities and data relationships in a database. The model is a logical representation of the physical system to be built.
Field
One attribute or characteristic of a database entity
File
A group of records about the same type of entity
First normal form (1NF
Data has primary key and no repeating groups
Foreign key
A column in a table that is a primary key in another table; creates a relationship between the two tables.
Instance
A row, or record
Key fields
Establish relationships among records in different tables
Many-to-many (N:M) relationship
A relationship in which an instance can occur multiple times in each entity
Normalization
Takes the raw data of an entity and evolves the data into a form that will reduce a database’s data redundancy.
One-to-many (1:M) relationship
A relationship in which an instance can only appear once in one entity, but one or more times in the other entity
One-to-one (1:1) relationship
A relationship in which each occurrence of a specific entity is found only once in each set of data
Primary key
A column in a relational database table whose value must be unique for each row.
Record
A collection of fields describing the attributes of one database element. In PL/SQL, a record is a composite datatype that can assume the same structure as the row being retrieved.
Relational database
A database that stores data in a tabular format.
Relationships
Links that show how different records are related
Row
A group of column values for a specific occurrence of an entity. In a database, records are commonly represented as rows.
Second normal form (2NF)
Data that is in 1NF and contains no partial dependencies.
Surrogate key
A column that you create to be the record’s primary key identifier.
SQL*Plus
A tool enabling users to interact with the database. Through SQL*Plus, users can enter SQL commands, set or alter environmental variables, display the structure of tables, and execute interactive scripts
Structured query language (SQL)
The industry standard for interacting with a relational database. It is a data sublanguage, and unlike a programming language, it processes sets of data as groups and can navigate data stored within various tables.
Third normal form (3NF)
Data that is in 2NF and contains no transitive dependencies
Unnormalized data
Data that does not have a primary key identified and/or contains repeating groups.
Character field
A field composed of nonnumeric data. This field will not display a heading longer than the width of the data stored in the field
Clause
Each section of a statement that begins with a keyword (SELECT clause, FROM clause, WHERE clause, etc.).
Column alias
Another name substituted for a column name. A column alias is created in a query and displayed in the results.
Concatenation
The combining the contents of two or more columns or character strings. Two vertical bars, or pipes (||), instruct Oracle 11g to concatenate the output of a query
Keywords
Words used in a SQL query that have a predefined meaning to Oracle9i. Common keywords include SELECT, FROM, and WHERE.
Numeric column
A column composed of only numeric data. In output, the column will display the entire column heading, regardless of the width of the field. (Also known as a numeric field.)
Projection
Choosing specific column(s) in a SELECT statement.
Query
A question posed to the database.
Relational database management system (RDBMS)
A software program used to create a relational database. It has functions that allow users to enter, manipulate, and retrieve data.
String literal
Alphanumeric data, enclosed within single quotation marks, that instructs the software to interpret “literally” exactly what has been entered and to show it in the resulting display
Syntax
The basic structure, pattern, or rules, for an SQL statement. For an SQL statement to execute properly, the correct syntax must be used.
Data definition language (DDL)
Commands, basically SQL commands, that create or modify database tables or other objects.
Database object
A defined, self-contained structure in Oracle 11g. Database objects include tables, sequences, indexes, and synonyms.
Datatype
Identifies the type of data Oracle9i will be expected to store in a column.
Schema
A collection of database objects owned by one user. By grouping objects according to the owner, multiple objects can exist in the same database that have the same object name.
Virtual column
A column value that is automatically generated based on the expression used to define the column.
Child table
A table having data that reference data within a parent table. Considered the “many” side in a one-to-many relationship.
Constraints
Rules that ensure the accuracy and integrity of data. Constraints prevent data that violate these rules from being added to tables. Constraints include PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL
Data dictionary
Oracle 11g stores all information about database objects in this “dictionary.” Stored information includes objects’ names, types, structures, owners, and the identity of users who have access to each object.
Referential integrity
When a user refers to something that exists in another table, the REFERENCES keyword is used to identify the table and column that must already contain the data being entered
Data manipulation language (DML)
Commands used to modify data. Changes to data made by DML commands are not accessible to other users until the data is committed.
Exclusive lock
When DDL operations are performed, Oracle 11g places this lock on a table so that no other user can alter the table or place a lock on it. See table locks.
Shared lock
A table lock that lets other users access portions of a table but not alter the structure of table. See table locks
Substitution variable
Instructs Oracle 11g to use a substituted value in place of a specific variable at the time a command is executed. Used to make SQL statements or PL/SQL blocks interactive.
Table locks
When DML commands are issued, Oracle 11g implicitly “locks” the row or rows being affected so that no other user can change the same data.
Transaction
A series of DML statements is considered to be one transaction. In Oracle 11g, a transaction is simply a series of statements that have been issued and not committed. The duration of a transaction is defined by when a COMMIT implicitly or explicitly occurs
Transaction control
Data control statements that either save modified data or undo uncommitted changes made in error.
Application cluster environment
A high-volume work environment in which multiple users simultaneously request data from a database.
Data dictionary
Oracle 11g stores all information about database objects in this “dictionary.” Stored information includes objects’ names, types, structures, owners, and the identity of users who have access to each object.
Database object
A defined, self-contained structure in Oracle 11g. Database objects include tables, sequences, indexes, and synonyms.
Dynamic view
Used to access statistics relating to the database’s performance. See view.
Function-based index
Can be used when a query is based on a calculated value or a function. See index.
Index —
A separate database object that stores frequently referenced values so they can be quickly located. An index can either be created implicitly by Oracle 11g or explicitly by a user.
Private synonym
An alias used by an individual to reference objects owned by that individual. See synonym.
Public synonym
An alias that can be used by others to access an individual’s database objects. See synonym.
Sequence
A database object that generates sequential integers that can be used for an organization’s internal controls. A sequence can also serve as a primary key for a table.
Synonym
An alternative name given to a database object with a complex name. Synonyms can be either private or public.
Comparison operator
A search condition that indicates how data should relate to a given search value (equal to, greater than, less than, etc.). Common comparison operators include >, <, >=, and <= .
Condition
A portion of an SQL statement that identifies what must exist, or a requirement that must be met. When a query is executed, any record meeting the given condition will be returned in query results.
Equality operator
A search condition that evaluates data for exact, or equal, values. The equality operator symbol is the equal sign (=).
Logical operators
Used to combine two or more search conditions. The logical operators include AND and OR. The NOT operator reverses the meaning of search conditions.
NULL value
Means no value has been stored in that particular field. A NULL value indicates the absence of data, not a blank space.
Primary sort
When only one column is specified in the ORDER BY clause, data is ordered, or sorted, based on the data organization within the specified column.
Secondary sort
When two or more columns are specified in the ORDER BY clause, data in the second column (or additional columns) provide an alternative field on which to order data if an exact match occurs between two or more rows in the first, or primary, sort.
Wildcard characters
Symbols used to represent one or more alphanumeric characters. The wildcard characters in Oracle 11g are the percent sign (%) and the underscore symbol ( _ ). The percent sign is used to represent any number of characters; the underscore represents one character.
Cartesian join
Links table data so each record in the first table is matched with each individual record in the second table. Also called a Cartesian product or cross join.
Column alias
Another name substituted for a column name. A column alias is created in a query and displayed in the results.
Column qualifier
Indicates the table containing a referenced column.
Equality joins
Links table data in two (or more) tables having equivalent data stored in a common column. These joins might also be called equijoins, inner joins, or simple joins.
Inner joins
Joins that display data if there were a corresponding record in each table queried. Equality joins, non-equality joins, and self-joins are all classified as inner joins.
Non-equality join
Links data in two tables that do not have equivalent rows of data.
Outer join
Links data in tables that do not have equivalent rows. An outer join can be created in either the WHERE clause with an outer join operator (+) or by using the OUTER JOIN keywords.
Outer join operator
The plus (+) symbol enclosed in parentheses, used in an outer join operation.
Self-join
Links data within a table to other data within the same table. A self-join can be created with a WHERE clause or by using the JOIN keyword with the ON clause.
Set operators
Combine the results of two (or more) SELECT statements. Valid set operators in Oracle 11g are UNION, UNION ALL, INTERSECT, and MINUS.
Table alias
A temporary name for a table, given in the FROM clause. Table aliases are used to reduce memory requirements or the number of keystrokes needed when specifying a table throughout the SQL statement.
Argument
Values listed within parentheses in a function
Case conversion functions
Allow a user to temporarily alter the case of data stored in a column or character string.
Character functions
Used to change the case of characters or manipulate characters.
Function
A named PL/SQL block or predefined block of code that accepts zero or more input parameters and returns one value.
Julian date
Represents the number of days that have passed between a specified date and January 1, 4712, B.C.
Manipulation functions
Allow the user to control data (e.g., determine the length of a string, extract portions of a string) to yield a desired query output.
Single-row functions
Return one row of results for each record processed.
Dimension
Term used to describe any category used in analyzing data, such as time, geography, and product line.
Group functions
Process groups of rows, returning only one result per group of rows processed. Also called multiple-row functions and aggregate functions.
Normal distribution
When a large number of data values are obtained for statistical analysis, they tend to cluster around some “average” value. This dispersion of values is called normal distribution.
Standard deviation
A calculation used to determine how closely individual values are to the mean, or average, of a group of numbers.
Statistical group functions
Perform basic statistical calculations for data analysis. Oracle 11g’s functions include standard deviation and variance.
Correlated subquery
A subquery that references a column in the outer query. The outer query executes the subquery once for every row in the outer query.
Multiple-column subquery
A nested query that returns more than one column of results to the outer query. It can be listed in the FROM, WHERE, or HAVING clause.
Multiple-row subquery
Nested queries that return more than one row of results to the parent query. They are most commonly used in WHERE and HAVING clauses and require multiple-row operators.
Single value
The output of a single-row subquery
Single-row subquery
A nested subquery that can return to the outer query only one row of results that consists of only one column. The output of a single-row subquery is a single value.
Uncorrelated subquery
A subquery that follows this method of processing: the subquery is executed, then the results of the subquery are passed to the outer query, and finally the outer query is executed.
