Chapter 1: Overview of Database Concepts

SELECT

SQL command to retrieve, or query, data values

INSERT, UPDATE, DELETE

SQL commands to create or modify data values (also called data manipulation language)

CREATE, ALTER, DROP

SQL commands to define data structures (also called data definition language)

COMMIT, ROLLBACK

SQL commands to save or undo data value modifications (also called transaction control)

GRANT, REVOKE

SQL commands to set permissions to access database structures (also called data control language)

SDLC

Systems Development Life Cycle

record

a collection of fields describing the attributes of one database element. Can also be referred to as a row.

transaction control statement

a command that saves modified data permanently or undoes uncommitted changes made in error

lookup table

a common description for the table referenced in a foreign key relationship. Typically used to identify descriptive data for a column value and ensure consistency of these descriptive values.

structured query language

a data sub-language with commands focused on creating database objects and manipulating data stored in a database. The industry standard for interacting with a relational database.

database object

a defined, self-contained structure in Oracle 11g that includes tables, sequences, indexes, and synonyms.

E-R model

a diagram that identifies the entities in the database, and shows how the entities are related to one another. It serves as a logical representation of the physical system to be built

common field

a field that exists in two or more tables that contains equivalent data and is typically used to join tables.

file

a group of records about the same type of entity. Can also be referred to as a table.

field

a group of related characters that represents one attribute or characteristic of an entity in a database. Can also be referred to as a column or an attribute of a database.

database normalization

a multistage process that designers use to develop raw data about an entity into a structured form that reduces data redundancy

query

a question posed to a database

foreign key

a representation in a table of a primary key from another table that establishes a relationship with that table. It appears on the "many" side of a one-to-many relationship.

Systems Development Life Cycle

a series of steps for the design and development of a database system

database management system

a software product used to create and maintain the structure of a databse, and then to enter, manipulate and retrieve the data it stores.

database

a storage structure that provides mechanisms for recording, manipulating, and retrieving data. A collection of interrelated files.

bridging entity

an entity used to eliminate a many-to-many relationship by creating two one-to-many relationships.

data anomaly

an inconsistency in data stored in a database

data mining

analyzing historical sales data and other information stored in an organization's database to support business functions, such as developing marketing campaigns.

column

another name for a field in a physical database

table

another name for a file in a physical database

row

another name for a record in a physical database

entity

any person, place, or thing with characteristics or attributes that will be included in the database system. In an E-R model, it is usually represented as a square or rectangle.

transitive dependency

at least one value in the record is not dependent on the primary key but on another field in the record.

data definition language

commands that create or modify database tables or other objects

data manipulation language

commands used to modify existing data. Changes to data made by DML commands are not accessible to other users until the changes have been committed.

DCL

data control language

DDL

data definition language

DML

data manipulation language

many-to-many relationship

data that has multiple occurrences in both entities. Represented in an E-R model with a straight line with a crow's foot at each end. This relationship cannot exist in a normalized database.

DBMS

database management system

one-to-many relationship

each occurrence of data in one entity can be represented by many occurrences of the data in the other entity. Represented in the E-R model with a straight line with a crow's foot at the many end.

one-to-one relationship

each occurrence of data in one entity is represented by only one occurrence of data in the other entity. Represented in the E-R model with a simple straight line.

ERD

entity-relationship diagram. Another name for an E-R model.

1NF

first-normal form

data redundancy

having duplicate data in different places in a database, which wastes storage space and complicates updates and changes.

solid line

in an E-R model, represents a mandatory relationship between entities.

square

in an E-R model, represents an entity

dashed line

in an E-R model, represents an optional relationship between entities.

systems integration and testing

in the waterfall model of SDLC, the fifth step. Consists of placing the system into operation for testing.

systems investigation

in the waterfall model of SDLC, the first step. Consists of understanding the problem and getting to know what output is needed from the database.

systems implementation

in the waterfall model of SDLC, the fourth step. Consists of creating the system.

systems analysis

in the waterfall model of SDLC, the second step. Consists of understanding the solution to the identified problem.

systems maintenance and review

in the waterfall model of SDLC, the seventh step. Consists of evaluating the implemented system.

systems deployment

in the waterfall model of SDLC, the sixth step. Consists of placing the system into production.

systems design

in the waterfall model of SDLC, the third step. Consists of defining the logical and physical components.

first-normal form

normalization step that eliminates all repeating values and identifies a primary key or primary composite key. After this process data can be identified uniquely.

second-normal form

normalization step that makes certain the table is in 1NF and eliminates any partial dependencies.

third-normal form

normalization step that makes certain the table is in 2NF and removes any transitive dependencies

security

part of the functionality of a DBMS that controls user access and privileges

data integrity

part of the functionality of a DBMS that enables constraints or checks on data

backup

part of the functionality of a DBMS that enables recovery options for database failures

data dictionary

part of the functionality of a DBMS that maintains information about data-base structure

multiuser access

part of the functionality of a DBMS that manages concurrent data access

data storage

part of the functionality of a DBMS that manages the physical structure of the database

data access language

part of the functionality of a DBMS that provides a language that allows database access

unnormalized data

refers to database records that contain repeating groups of data (multiple entries for a single column)

SDLC steps (waterfall model)

1. Systems Investigation 2. Systems analysis 3. Systems design 4. Systems implementation 5. Systems integration and testing 6. Systems deployment 7. Systems maintenance and review

database management system functionality

Data Storage, Security, Multiuser Access, Backup, Data Access Language, Data Integrity, Data Dictionary

2NF

second-normal form

SQL

structured query language

character

the basic unit of data in a database. It can be a letter, number, or special symbol.

partial dependency

the fields contained in a record (row) depend on only one portion of the primary key

3NF

third-normal form

TC

transactional control

composite primary key

when more than one field is used as the primary key for a table