Database Design
ad hoc query
A "spur of the moment" question
entity cluster
A "virtual" entity type used to represent multiple entities and relationships in the ERD; An entity cluster is formed by combining multiple interrelated entities into a single abstract entity object; An entity cluster is considered "virtual" or "abstract" in the sense that it is not actually an entity in the final ERD
table
A (conceptual) matrix composed of intersecting rows (entities) and columns (attributes) that represents an entity set in the relational model (Also called a relation)
object/relational database management system (O/RDBMS)
A DBMS based on the extended relational model (ERDM); The ERDM, championed by many relational database researchers, constitutes the relational model's response to the OODM, This model includes many of the object-oriented model's best features within an inherently simpler relational database structural environment
data dictionary
A DBMS component that stores metadata data about data, Thus, the data dictionary contains the data definition as well as its characteristics and relationships
Primary key (PK)
A candidate key selected as a unique entity identifier
field
A character or group of characters (alphabetic or numeric) that defines a characteristic of a person, place, or thing
attribute
A characteristic of an entity or object & an attribute has a name and a data type
class
A collection of like objects with shared structure (attributes) and behavior (methods), A class encapsulates an object's data representation and a method's implementation (Classes are organized in a class hierarchy)
relational database management system (RDBMS)
A collection of programs that manages a relational database (A good RDBMS also creates and maintains a data dictionary (system catalog) to help provide data security, data integrity, concurrent access, easy access, and system administration to the data in the database through a query language (SQL) and application programs)
record
A collection of related (logically connected) fields
foreign key
A column in one table whose values match the primary key of another table
computed column
A column that does not exist in the database but can be computed using data in existing columns
referential integrity
A condition by which a dependent table's foreign key must have either a null entry or a matching entry in the related table; Even though an attribute may not have a corresponding attribute, it is impossible to have an invalid entry
compound condition
A condition formed by connecting two or more simple conditions
transitive dependency
A condition in which an attribute is dependent on another attribute that is not part of the primary key
full functional dependence
A condition in which an attribute is functionally dependent on a composite key but not on any subset of that composite key
data inconsistency
A condition in which different versions of the same data yield different (inconsistent) results
data redundancy
A condition that exists when a data environment contains redundant (unnecessarily duplicated) data
data independence
A condition that exists when data access is unaffected by changes in the physical data storage characteristics
logical independence
A condition that exists when the internal model can be changed without affecting the conceptual model
physical independence
A condition that exists when the physical model can be changed without affecting the internal model
simple condition
A condition that has the form: column name, comparison operator, and either another column name or a value
union compatible
A condition where two tables have the same number of columns and their corresponding columns have identical data types and lengths
completeness constraint
A constraint that specifies whether each entity supertype occurrence must also be a member of at least one subtype; The completeness constraint can be partial or total
data anomaly
A data abnormality that exists when inconsistent changes to a database have been made
structural dependence
A data characteristic that exists when a change in the database schema affects data access, thus requiring changes in all access programs
structural independence
A data characteristic that exists when changes in the database schema do not affect data access
data dependence
A data condition in which the data representation and manipulation are dependent on the physical data storage characteristics
entity relationship (ER) model (ERM)
A data model developed by P Chen in 1975; It describes relationships (1:1, 1:M, and M:N) among entities at the conceptual level with the help of ER diagrams
network model
A data model standard created by the CODASYL Data Base Task Group in the late 1960s; It represented data as a collection of record types and relationships as predefined sets with an owner record type and a member record type in a 1:M relationship
object-oriented data model (OODM)
A data model whose basic modeling structure is an object
null data value
A data value used to represent situations in which an actual value is unknown, unavailable or not
null
A data value used to represent situations in which an actual value is unknown, unavailable or not applicable
transactional database
A database designed to keep track of the day-to-day transactions of an organization (See also production database)
centralized database
A database located at a single site
operational database
A database that is designed primarily to support a company's day-to-day operations (Also known as a transactional database or production database)
multiuser database
A database that supports multiple concurrent users
single-user database
A database that supports only one user at a time
system catalog
A detailed system data dictionary that describes all objects in a database
entity relationship diagram (ERD)
A diagram that depicts an entity relationship model's entities, attributes, and relations
script file
A file that contains one or more SQL commands
index
A file that relates key values to records that contain those key values. the main mechanism for increasing the efficiency with which data is retrieved from the database
relational diagram
A graphical representation of a relational database's entities, the attributes within those entities, and the relationships among those entities
specialization hierarchy
A hierarchy that is based on the top-down process of identifying lower-level, more specific entity subtypes from a higher-level entity supertype; Specialization is based on grouping unique characteristics and relationships of the subtypes
full outer join
A join in which all rows from both tables will be included regardless of whether they match rows from the other table
outer join
A join in which all rows from one table in a join is listed, regardless of whether they match any rows in the other table
left outer join
A join in which all rows from the table on the left will be included regardless of whether they match rows from the table
right outer join
A join in which all rows from the table on the right will be included regardless of whether they match rows from the
inner join
A join that compare the tables in the FROM clause and lists only those rows that satisfy the condition in the WHERE clause
prime attribute
A key attribute, that is, an attribute that is part of a key or is the whole key, See also key attribute
Secondary key
A key that is used strictly for data retrieval purposes, For example, a customer is not likely to know his or her customer number (primary key), but the combination of last name, first name, middle initial, and telephone number is likely to make a match to the appropriate table row
secondary key
A key that is used strictly for data retrieval purposes, For example, a customer is not likely to know his or her customer number (primary key), but the combination of last name, first name, middle initial, and telephone number is likely to make a match to the appropriate table row
Structured Query Language
A language used for retrieving and manipulating database data
schema
A logical grouping of database objects (tables, indexes, views, queries, etc) that are related to each other (Usually, a schema belongs to a single user or application)
transaction
A logical unit of work
distributed database
A logically related database that is stored over two or more physically independent sites
Candidate key
A minimal attribute that is itself a superkey
physical model
A model in which the physical characteristics (location, path, and format) are described for the data; Both hardware- and software-dependent (See also physical design)
composite key
A multiple-attribute key
workgroup database
A multiuser database that supports a relatively small number of users (usually fewer than 50) or that is used for a specific department in an organization
file
A named collection of related records
query language
A nonprocedural language that is used by a DBMS to manipulate its data (example of a query language is SQL)
Structured Query Language
A powerful and flexible relational database language composed of commands that enable users to create database and table structures, perform various types of data manipulation and data administration, and query the database to extract useful information
iterative process
A process based on repetition of steps and procedures
denormalization
A process by which a table is changed from a higher level normal form to a lower level normal form; Usually done to increase processing speed (Potentially yields data anomalies)
normalization
A process that assigns attributes to entities in such a way that data redundancies are reduced or eliminated
data management
A process that focuses on data collection, storage, and retrieval (Common data management functions include addition, deletion, modification, and listing)
existence-dependent
A property of an entity whose existence depends on one or more other entities; In an existence-dependent environment, the existence-independent table must be created and loaded first because the existence-dependent key cannot reference a table that does not yet exist
software independence
A property of any model or application that does not depend on the software used to implement it
atomicity
A property of transactions that states that all parts of a transaction must be treated as a single logical unit of work in which all operations must be completed (committed) to produce a consistent database
defining query
A query which indicates the rows and columns to include in a view
query
A question or task asked by an end user of a database in the form of SQL code
query
A question represented in a way that the DBMS can understand
natural key (natural identifier)
A real-world, generally accepted identifier used to identify real-world objects; As its name implies, a natural key is familiar to end users and forms part of their day-to-day business vocabulary
weak relationship
A relationship that exists when the PK of the related entity does not contain a PK component of the parent entity (Also known as a non-identifying relationship)
identifying relationship
A relationship that exists when the related entities are existence-dependent; Also called a strong relationship or strong identifying relationship because the dependent entity's primary key contains the primary key of the parent entity
recursive relationship
A relationship that is found within a single entity type; For example, an EMPLOYEE is married to an EMPLOYEE or a PART is a component of another PART
non-identifying relationship
A relationship that occurs when the primary key of the dependent (many side) entity does not contain the primary key of the related parent entity (Also known as a weak relationship)
dependency diagram
A representation of all data dependencies (primary key, partial, or transitive) within a table
conceptual schema
A representation of the conceptual model, usually expressed graphically; See also conceptual model
Crow's Foot notation
A representation of the entity relationship diagram using a three-pronged symbol to represent the "many" sides of the relationship
data model
A representation, usually graphic, of a complex "real-world" data structure; Data models are used in the database design phase of the database life cycle
constraint
A restriction placed on data, Constraints are normally expressed in the form of rules; Example: "A student's GPA must be between a certain range" (Constraints help to ensure data integrity)
integrity constraint
A rule for the data in the database
database
A shared, integrated computer structure that houses a collection of related data A database contains two types of data: end-user data (raw facts) and metadata
desktop database
A single-user database that runs on a personal computer
database management system (DBMS)
A software program that lets you create a database and then use it to add, change, delete, sort, and view the data in a database
statement history
A special area in which the most recently entered command is stored
Boyce-Codd normal form (BCNF)
A special form of third normal form (3NF) in which every determinant is a candidate key; A table that is in BCNF must be in 3NF (See also determinant)
logical design
A stage in the design phase that matches the conceptual design to the requirements of the selected DBMS and is, therefore, software-dependent; It is used to translate the conceptual design into the internal model for a selected database management system, such as DB2, SQL Server, Oracle, IMS, Informix, Access, and Ingress
database
A structure that contains different categories of information and the relationships between these categories
nested subquery
A subquery with a subquery
surrogate key
A system-assigned primary key, generally numeric and auto-incremented
union
A table containing every row that is in either the first table, the second table, or both tables
third normal form (3NF)
A table is in 3NF when it is in 2NF and no nonkey attribute is functionally dependent on another nonkey attribute; that is, it cannot include transitive dependencies
intersection
A table which contains all rows that are in the two tables
entity instance
A term used in ER modeling to refer to a specific table row; Also known as an entity occurrence
mandatory participation
A term used to describe a relationship in which one entity occurrence must have a corresponding occurrence in another entity, Example: EMPLOYEE works in DIVISION (A person cannot be an employee if he or she is not assigned to a company's division
row-and-column subset view
A view that consists of a subset of the rows and columns in some base table
performance tuning
Activities that make a database perform more efficiently in terms of storage and access speed
ternary relationship
An ER term used to describe an association (relationship) between three entities Example: A CONTRIBUTOR contributes money to a FUND from which a RECIPIENT receives money
binary relationship
An ER term used to describe an association (relationship) between two entities Example: PROFESSOR teaches COURSE
unary relationship
An ER term used to describe an association within an entity, Example: A COURSE is a prerequisite to another COURSE
participants
An ER term used to label the entities that participate in a relationship, Example: PROFESSOR teaches CLASS (The teaches relationship is based on the participants PROFESSOR and CLASS)
object
An abstract representation of a real-world entity that has a unique identity, embedded properties, and the ability to interact with other objects and with itself
alias
An alternate name for a table
view
An application program's or an individual user's picture of the database
relationship
An association between entities
Foreign key
An attribute (or combination of attributes) in one table whose values must match the primary key in another table or whose values must be null
Superkey
An attribute (or combination of attributes) that uniquely identifies each entity in a table
composite attribute
An attribute that can be further subdivided to yield additional attributes, For example, a phone number (615-898-2368) may be divided into an area code (615), an exchange number (898), and a four-digit code (2368) (Compare to simple attribute)
) multivalued attribute
An attribute that can have many values for a single entity occurrence, For example, an EMP_DEGREE attribute might store the string "BBA, MBA, PHD" to indicate three different degrees held
single-valued attribute
An attribute that can have only one value
atomic attribute
An attribute that cannot be further subdivided to produce meaningful components; For example, a person's last name attribute cannot be meaningfully subdivided into other name components; therefore, the last name attribute is atomic
simple attribute
An attribute that cannot be subdivided into meaningful components (Compare to composite attribute)
derived attribute
An attribute that does not physically exist within the entity and is derived via an algorithm, Example: Age = current date - birth date
nonprime attribute
An attribute that is not part of a key (nonkey attribute)
composite entity
An entity designed to transform an M:N relationship into two 1:M relationships; The composite entity's primary key comprises at least the primary keys of the entities that it connects (Also known as a bridge entity, See also linking table)
key
An entity identifier based on the concept of functional dependence; may be classified as Superkey, Candidate Key, Secondary Key or Foreign Key
existence-independent
An entity that can exist apart from one or more related entities; It must be created first when referencing an existence-dependent table to it
weak entity
An entity that displays existence dependence and inherits the primary key of its parent entity, Example: A DEPENDENT requires the existence of an EMPLOYEE
unique index
An index in which the index key can have only one pointer value (row) associated with it
unique index
An index that ensures the uniqueness of values in a non-primary key column
catalog
An object that stores information about the tables in the database
system catalog
An object that stores information about the tables in the database
MySQL
An open source database management system that supports the SQL language
MySQL
An open source, full-featured database management system
index
An ordered array composed of index key values and row ID values (pointers), Indexes are generally used to speed up and facilitate data retrieval (Also known as an index key)
database system
An organization of components that defines and regulates the collection, storage, management, and use of data in a database environment
determinant
Any attribute in a specific row whose value directly determines other values in that row (See also Boyce-Codd normal form (BCNF))
cardinality
Assigns a specific value to connectivity, Expresses the range (minimum to maximum) of allowed entity occurrences associated with a single occurrence of the related entity
data warehouse
Bill Inmon, the acknowledged "father of the data warehouse," defines the term as "an integrated, subject-oriented, time-variant, nonvolatile collection of data that provides support for decision making"
grouping
Creates groups of tows that share some common characteristic
metadata
Data about data, that is, data concerning data characteristics and relationships
object-oriented database management system (OODBMS)
Data management software used to manage data found within an object-oriented database model
time-variant data
Data whose values are a function of time; For example, time-variant data can be seen at work when the history of all administrative appointments (date of appointment and date of termination) are tracked
default database
Database to which all subsequent commands pertain
internal schema
Depicts a specific representation of an internal model, using the database constructs supported by the chosen database (The internal model is the representation of a database as "seen" by the DBMS; In other words, the internal model requires a designer to match the conceptual model's characteristics and constraints to those of the selected implementation model)
connectivity
Describes the classification of the relationship between entities (Classifications include 1:1, 1:M, and M:N)
connectivity
Describes the classification of the relationship between entities; Classifications include 1:1, 1:M, and M:N
relational model
Developed by E F Codd (of IBM) in 1970, it represents a major breakthrough for users and designers because of its conceptual simplicity; The relational model, based on mathematical set theory, represents data as independent relations, Each relation (table) is conceptually represented as a matrix of intersecting rows and columns, the relations are related to each other through the sharing of common entity characteristics (values in columns)
CHAR(n)
Fixed-length character data
composite identifier
In ER modeling, a key composed of more than one attribute
optional participation
In ER modeling, refers to a condition where one entity occurrence does not require a corresponding entity occurrence in a particular relationship
optional attribute
In ER modeling, refers to an attribute that does not require a value, therefore it can be left empty
required attribute
In ER modeling, refers to an attribute that must have a value (In other words, it cannot be left empty)
null
In SQL, refers to the absence of an attribute value (Note: A null is not a blank)
partial completeness
In a generalization hierarchy, means that not every supertype occurrence is a member of a subtype; that is, there may be some supertype occurrences that are not members of any subtype
total completeness
In a generalization/specialization hierarchy, a condition in which every supertype occurrence must be a member of at least one subtype
entity supertype
In a generalization/specialization hierarchy, refers to a generic entity type that contains the common characteristics of entity subtypes
entity subtype
In a generalization/specialization hierarchy, refers to a subset of an entity supertype where the entity supertype contains the common characteristics and the entity subtypes contain the unique characteristics of each entity subtype
repeating group
In a relation, a characteristic describing a group of multiple entries of the same type that exist for a single key attribute occurrence; For example, a car can have multiple colors (top, interior, bottom, trim, and so on)
relation
In a relational database model, an entity set, Relations are implemented as tables; Relations (tables) are related to each other through the sharing of a common entity characteristic (value in a column)
data integrity
In a relational database, refers to a condition in which the data in the database is in compliance with all entity and referential integrity constraints
entity set
In a relational model, refers to a grouping of related entities
overlapping subtype
In a specialization hierarchy, describes a condition where each entity instance (row) of the supertype can appear in more than one subtype
domain
In data modeling, refers to the construct used to organize and describe an attribute's set of possible values
internal model
In database modeling, refers to a level of data abstraction that adapts the conceptual model to a specific DBMS model for implementation
partial dependency
In normalization, a condition in which an attribute is dependent on only a portion (subset) of the primary key
subschema
In the network model, the portion of the database "seen" by the application programs that produce the desired information from the data contained within the database
method
In the object-oriented data model, a named set of instructions to perform an action; Methods represent real-world actions (Methods are invoked through messages)
inheritance
In the object-oriented data model, the ability of an object to inherit the data structure and methods of the classes above it in the class hierarchy (See also class hierarchy)
primary key (PK)
In the relational model, an identifier composed of one or more attributes that uniquely identifies a row
relationship degree
Indicates the number of entities or participants associated with a relationship, A relationship degree can be unary, binary, ternary, or higher level
homonyms
Indicates the use of the same name to label different attributes; generally should be avoided
hardware independence
Means that a model does not depend on the hardware used in the implementation of the model; Therefore, changes in the hardware will have no effect on the database design at the conceptual level
business rule
Narrative descriptions of a policy, procedure, or principle within an organization
hierarchical model
No longer a major player in the current database market; important to know, however, because the basic concepts and characteristics form the basis for subsequent database development; This model is based on an "upside-down" tree structure in which each record is called a segment (the top record is the root segment, Each segment has a 1:M relationship to the segment directly below it)
design trap
Occurs when a relationship is improperly or incompletely identified and, therefore, is represented in a way that is not consistent with the real world, The most common design trap is known as a fan trap
many-to-many (M:N) relationship
One of three types of relationships (associations among two or more entities) in which one occurrence of an entity is associated with many occurrences of a related entity and one occurrence of the related entity is associated with many occurrences of the first entity
one-to-many (1:M, 1:N)
One of three types of relationships (associations among two or more entities) that are used by data models
EXISTS
Operator that checks for the existence of rows that satisfy some criterion
INTERSECT
Operator that creates a temporary table containing all rows that are in both tables
UNION
Operator that creates a temporary table containing every row that is in either the first table, the second table, or both tables
MINUS
Operator that creates a temporary table containing the set of all rows that are in the first table but that are not in the second
data
Raw facts, that is, facts that have not yet been processed to reveal their meaning to the end user
database management system (DBMS)
Refers to the collection of programs that manages the database structure and controls access to the data stored in the database
EER diagram (EERD)
Refers to the entity-relationship diagram resulting from the application of extended entity relationship concepts that provide additional semantic content in the ER model
granularity
Refers to the level of detail represented by the values stored in a table's row; Data stored at their lowest level of granularity are said to be atomic data
Reserved words
SQL command words
entity occurrence
See entity instance
index key
See index
nonkey attribute
See nonprime attribute
Open source software
Software for which the source code is freely and publicly available
entity
Something about which someone wants to store data; typically a person, a place, a thing, a concept, or an event (See also attribute)
extended entity relationship model (EERM)
Sometimes referred to as the enhanced entity relationship model; the result of adding more semantic constructs (entity supertypes, entity subtypes, and entity clustering) to the original entity relationship (ER) model
extended relational data model (ERDM)
Sometimes referred to as the enhanced entity relationship model; the result of adding more semantic constructs (entity supertypes, entity subtypes, and entity clustering) to the original entity relationship (ER) model
aggregate function
Special SQL functions that apply to groups of rows and used to calculate sums, averages, counts, maximum values, and
flags
Special codes implemented by designers to trigger a required response, to alert end users to specified conditions, or to encode values; Flags may be used to prevent nulls by bringing attention to the absence of a value in a table
SQL
Structured Query Language
data dictionary
System catalog or catalog
identifiers
The ERM uses identifiers to uniquely identify each entity instance, In the relational model, such identifiers are mapped to primary keys in tables
USE
The SQL command to change the default database
SHOW COLUMNS
The SQL command to list all the columns in a given table
SHOW TABLES
The SQL command to list all the tables in the database
SHOW INDEX
The SQL command to list the indexes associated with a table
SHOW GRANTS
The SQL command to list the privileges granted to a user
INSERT
The SQL command used to add rows to a table
UPDATE
The SQL command used to change a value in a table
UPDATE
The SQL command used to change existing data in a table
CREATE INDEX
The SQL command used to create an index
CREATE VIEW
The SQL command used to define a view
DELETE
The SQL command used to delete a row in a table
DROP VIEW
The SQL command used to delete a view
DROP INDEX
The SQL command used to delete an index
DROP TABLE
The SQL command used to drop an entire table
CREATE UNIQUE INDEX
The SQL command used to ensure that only unique values are allowed in a non-primary key column
GRANT
The SQL command used to give users access privileges to data in the database
VALUES
The SQL command used to insert values in a table
SHOW COLUMNS
The SQL command used to list all the columns in a table
DESCRIBE
The SQL command used to list all the columns in a table and their corresponding data types
ROLLBACK
The SQL command used to reverse update changes to a table
REVOKE
The SQL command used to revoke the access privileges of users to data in the database
COMMIT
The SQL command used to save update changes to a table
SELECT
The SQL command used to view data in a table
external model
The application programmer's view of the data environment; Given its business-unit focus, an external model works with a data subset of the global database schema
subtype discriminator
The attribute in the supertype entity that determines to which entity subtype each supertype occurrence is related
key attribute
The attribute(s) that form(s) a primary key (See also prime attribute)
key attribute
The attribute(s) that form(s) a primary key, See also prime attribute
knowledge
The body of information and facts about a specific subject
NOT NULL
The clause in a CREATE TABLE command used to indicate which columns cannot contain null values
ADD clause
The clause of the ALTER TABLE command used to add a column to a table
MODIFY clause
The clause of the ALTER TABLE command used to change the characteristics of a column
WITH GRANT OPTION
The clause of the GRANT command which grants the indicated privilege to the user and also permits the user to grant the same privileges (or a subset of them) to other users
ORDER BY clause
The clause that allows query results to be sorted in a specified order
IN clause
The clause that consists of the IN operator, the operator which finds a value in a group of values specified in the condition
GROUP BY clause
The clause that groups rows based on the specified column
FROM clause
The clause that indicates the table from which to retrieve the specified columns
HAVING clause
The clause that limits a condition to the groups that are included
WHERE clause
The clause that specifies any conditions for the query
SELECT clause
The clause that specifies the columns to retrieve in the query
query result set
The collection of data rows that are returned by a query
key
The column on which data is to be sorted when the ORDER BY clause is used
sort key
The column on which data is to be sorted when the ORDER BY clause is used
Cartesian Product
The combination of all rows in the first table and all rows in the second table
product
The combination of all rows in the first table and all rows in the second table
DELETE
The command to delete data from a database
ALTER TABLE
The command used to change a table's structure
relational schema
The description of the organization of a relational database as seen by the database administrator
base table
The existing permanent tables in a relational database
semantic data model
The first of a series of data models that more closely represented the real world, modeling both data and their relationships in a single structure known as an object (The SDM, published in 1981, was developed by M Hammer and D McLeod)
first normal form (1NF)
The first stage in the normalization process; It describes a relation depicted in tabular format, with no repeating groups and with a primary key identified (All nonkey attributes in the relation are dependent on the primary key)
subquery
The inner query in nested queries
data management language (DML)
The language (set of commands) that allows an end user to manipulate the data in the database (SELECT, INSERT, UPDATE, DELETE, COMMIT, and ROLLBACK)
data definition language (DDL)
The language that allows a database administrator to define the database structure, schema, and subschema
minor sort key
The less important column when data on two columns needs to be sorted
secondary sort key
The less important column when data on two columns needs to be sorted
production database
The main database designed to keep track of the day-to-day operations of a company (See also transactional database)
major sort key
The more important column when data on two columns needs to be sorted
primary sort key
The more important column when data on two columns needs to be sorted
class hierarchy
The organization of classes in a hierarchical tree where each "parent" class is a superclass and each "child" class is a subclass (See also inheritance)
conceptual model
The output of the conceptual design process; The conceptual model provides a global view of an entire database, Describes the main data objects, avoiding details
enterprise database
The overall company data representation, which provides support for present and expected future needs
database administrator
The person or group charged with managing the database
security
The prevention of unauthorized access to a database
join
The process of combining two or more tables by finding rows in the tables that have identical values in matching fields.
self-join
The process of joining a table to itself
database administration
The process of managing a database
integrity support
The process of specifying integrity constraints for a database that the DBMS will enforce
database design
The process that yields the description of the database structure, Database design is the second phase of the database life cycle
entity integrity
The property of a relational table that guarantees that each entity has a unique value in a primary key and that there are no null values in the primary key
information
The result of processing raw data to reveal its meaning islands of information—A term used in the old-style file system environment to refer to independent, often duplicated, and inconsistent data pools created and managed by different organizational departments
determination
The role of a key, In the context of a database table, the statement "A determines B" indicates that knowing the value of attribute A means that the value of attribute B can be looked up (determined)
difference
The set of all rows that are in the first table but that are not in the second table
external schema
The specific representation of an external view, that is, the end user's view of the data environment
child
The table containing the foreign key
parent
The table referenced by a foreign key
synonym
The use of different names to identify the same object, such as an entity, an attribute, or a relationship; should generally be avoided (See also homonym)
physical data format
The way in which a computer "sees" (stores) data
logical data format
The way in which a human being views data
data type
Type of data that a column can contain as well as the maximum number of characters or digits that the
VARCHAR(n)
Variable-length character data
strong relationship
When two entities are existence-dependent; from a database design perspective, this exists whenever the primary key of the related entity contains the primary key of the parent entity
functional dependence
Within a relation R, an attribute B is functionally dependent on an attribute A if and only if a given value of the attribute A determines exactly one value of the attribute B, The relationship "B is dependent on A" is equivalent to "A determines B" and is written as A B
One-to-one (1:1)
a relationship where one entity instance is associated with only one instance of the related entity
DDL
data definition language
DML
data manipulation language
Total completeness
every supertype occurrence must be a member of at least one subtype
AVG
function that calculates the average value in a numeric range
MAX
function that calculates the maximum value in a numeric range
MIN
function that calculates the minimum value in a numeric range
COUNT
function that counts the number of rows in a table
SUM
function that sums numeric values in a range
AND
logical operator that connects simple conditions and requires all conditions to be true for the compound condition to be true
OR
logical operator that connects simple conditions, compound condition will be true whenever one of the simple conditions is true
NOT
logical operator that reverses the truth of the original condition
Partial completeness
not every supertype occurrence is a member of a subtype; that is, there may be some supertype occurrences that are not members of any subtype
DESC
operator included in an ORDER BY clause when results are to be sorted in descending order
DISTINCT
operator that eliminates duplicate values in the results of a query
roll back
reverse update changes to a table
commit
save update changes to a table
BETWEEN
special operator that specifies a range of values in a condition
IS NULL
special operator that specifies null values for a column in a query
LIKE
special operator that uses one or more wildcard characters to test for a pattern match
IS NOT NULL
special operator used to specify no null values for a column in a query
IN
special operator which finds a value in a group of values specified in the condition
DATE
used to represent a date and/or time
DECIMAL(p,q)
used to represent decimal numbers
INT
used to represent integer values
