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