Exam 1 Flashcards
Database Management System = SOFT CRIN WTDB
Database Management System: software to create and interact with database.
Database = LOST 2 STD
Database = logical structure to store data
Each byte = CH BBB I
Each byte = character that is the basic building block of information.
Record = CFSE & RPD
Record = collection of fields for specific entity and row in the physical database
Relational Database Management System: SP CRDB ENT MAN RTD
Software to create the database and allows you to enter, manipulate, and retrieve data.
SELECT statements are: ST 2 RT DFDB
Used to retrieve data from the database
Syntax gives the BS ORU 4 CO
Gives the basic structure or rules for a command
SELECT clause identifies
Columns
FROM clause identifies
Tables
Each clause begins with a:
Keyword
To execute SQL statements use:
Semicolon or slash
To select all data in a table:
Substitute an asterisk for the column names in a SELECT clause
To select one column from a table:
Enter column name in SELECT clause
To select multiple columns from a table:
Separate column names with a comma
Enclose in double quotation marks when:
It contains blank spaces, special symbols or to retain case
Arithmetic Operations are:
Executed left to right
Multiplication and division are solved:
First with arithmetic operations
Addition and subtraction are solved:
Last with arithmetic operations
You override arithmetic operations with:
Parentheses
To suppress duplicates:
Enter DISTINCT or UNIQUE after SELECT keyword
Field= GRC
Field= group of related characters
Field= ACE
Attribute or character of an entity
Field= CPD
Column in the physical database
File= GRSE
Group of records about the same type of entity
Relational database = COR
Collection of relations
Rows= DAE
Contain data about an entity
Columns= DAAE
Data about attributes of the entity
Cells= THSV
Cells of the table hold a single value
Character = BUD & L, N, SS
Basic unit of data and can be a letter, #, or special symbol
Bit= SDU
Smallest data unit
Byte= 8FB
Few bits (usually 8)
Database components:
Field, record, and file
Relation:
Two-dimensional table
Each column has:
A unique name
The order of the columns is:
Unimportant
The order of the rows is:
Unimportant
No two rows can be:
Identical
Table =
File
File =
Relation
Row =
Record
Record =
Tuple
Column =
Field
Field =
Attribute
Normalization:
Used to reduce or control data redundancy
Primary key:
Field that serves to uniquely identify each record in a table
Candidate key:
Any field that could be used as the primary key
Surrogate key:
The records primary key identifier when no suitable primary key exists
Foreign key:
A field in a table that is a primary key in another table
Composite key:
A unique key that you create by combining two or more fields
Partial dependency:
A column that is only dependent on a portion of the primary key
1NF=
Eliminate repeating groups and identify primary key
2NF=
Table is in 1NF and partial dependencies are eliminated
3NF=
Table is in 2NF and transitive dependencies are eliminated
Tables are linked through:
A common field
Common field=
A primary key in one table and a foreign key in the other table
SQL=
A data sub language that has constructs for defining and processing a database
Data definition language is used to:
Define database structures
Data Manipulation Language is used to:
Query and update data
Concatenation:
Are two vertical bars and can combine data with string literal
Concatenation allows use of:
Column alias
WHERE clause is used to:
Retrieve rows based on a stated condition
WHERE clause requires:
Column name, comparison operator, and value or column for comparison
WHERE clause is:
Case sensitive
List WHERE clause after:
FROM clause
In WHERE clause enclose non-numeric data in:
Single quotes
Comparison Operators:
Indicates how the data should relate to the given search value
The default SQL format for dates is:
DD-MON-YY
>
Greater than
<
Less than
<>
Not equal to
<=
Less than or equal to
> =
Greater than or equal to
Math comparison operators may work with:
Text as well as numbers
BETWEEN… AND Operator
To find values in a specified range
IN Operator
Values are separated by commas and list must be in parentheses
LIKE Operator
Performs pattern searches
Logical operators:
Used to combing conditions
Not
Reverses meaning
And
Both conditions must be true
Or
At least one condition must be true
ORDER BY Clause
Presents data in sorted order
Use DESC keyword to:
Override column default
In ascending order, values will be listed in the following sequence:
Numeric values, character values, NULL values
Secondary sort may be used to:
Sort multiple exact matches
Joins are used to:
Link tables and reconstruct data in a relational database
Joins can be created through:
Using the WHERE clause or using the JOIN keyword in the FROM clause
Cross Join is created by omitting joining condition in the:
WHERE clause or through CROSS JOIN keywords in the FROM clause
Equality Join:
Links rows through equivalent data in a common field that exists in both tables
Equality join is also called:
Equijoins, natural join, inner joins, or simple joins
Table Alias:
A temporary name that works like a column alias
If a table alias is assigned to a table:
It must be used any time the table is referenced in the SQL statement
Use NATURAL JOIN when:
Tables have one column in common
Use JOIN… USING when:
Tables have more than one column in common
Use JOIN… ON when:
A condition is needed to specify a relationship other than equivalency
Using the JOIN keyword frees the:
WHERE clause for exclusive use in restricting rows
Self Joins:
Used to link a table to itself
Outer Joins
Used to include rows that do not have a match in the other table
Set Operators
Used to combine the results of two or more SELECT statements
