Vocab. Ch. 1-3 Flashcards
Redundancy
The duplication of data or the storing of the same data in more than one place.
database
A structure that contains information about many different categories of information and about the relationships between those categories.
entity
A person, place, object, event, ore idea for which you want to store and process data.
attribute
A characteristic or property of an entity.
relationship
An association between relationships.
one-to-many relationship
One entity is associated with many other entities.
data file
A file that is used to store data.
database
A structure that can store information about multiple types of entities, the attributes of those entities, and the relationship between the entities.
entity-relationship (E-R) diagram
In and E-R diagram, rectangles represent entities, and lines represent relationships between connected entities. The dot at the end of a line represents the “many” part of a one-to-many relationship.
database management system (DBMS)
A program, or a collection of programs, through which users interact with a database.
database design
The process of determining the structure of a database.
forms
Screen objects used to maintain, view, and print data from a database.
9 Advantages of Database Processing
1) Getting more info from the same amount of data
2) Sharing data
3) Balancing conflicting requirements
4) Controlling Redundancy
5) Facilitating consistency
6) Improving integrity
7) Expanding security
8) Increasing productivity
9) Providing data independence
database administrator
Also known as Database Administration (DBA) -
A person or group that keeps the overall needs of the entire organization in mind so that the database can be structured in such a way that it benefits the entire organization, not just a single group and there by balances conflicting requirements.
integrity constraint
A rule that data must follow in the database.
integrity
Maintained by the database because all data in it must satisfy all established integrity constraints.
security
The prevention of unauthorized access to the database.
data independence
A property of a DBMS that lets you change the structure of a database without requiring you to change the programs that access the database.
4 Disadvantages of Database Processing
1) Larger file size
2) Increased complexity
3) Greater impact of failure
4) More difficult recovery
field
A single characteristic or attribute of a person, place, object, event, or idea.
table
A collection of fields that describes a person, place, object, event, or idea.
field value
The specific content of a field
record
A set of field values for a specific entity.
relational database
A collection of related tables.
common field
A field that appears on both tables that provides the connection between the tables.
template
A predesigned database that includes professionally designed tables, reports, and other database objects that can make it quick and easy for you to create a database.
data type
Determines what field values you can enter for the fields. The data types for Access are: 1) AutoNumber 2) Short Text 3) Long Text 4) Number 5) Date/Time 6) Currency 7) Yes/No 8) Hyperlink
data type - AutoNumber
Automatically a unique number in this field for every record, beginning with number 1 for the first record, the number 2 for the second record, and so on.
data type - Short Text
Allows field values containing letters, digits, and other characters, and which is appropriate for identifying numbers, such as visit numbers, that are never used for calculations.
composite key
When two or more fields function as the primary key.
foreign key
When you include the primary key from on table as a field in a second table to form a relationship.
property of a field
1) field’s name
2) data type
3) maximum number of characteristics or digits
4) description
5) valid values
6) other
caption property
Specifies how the field name is displayed in database objects, including table and query datasheets, forms, and reports.
default value property
Specifies what value will appear, by default, for the field in each new record you add to a table.
delimited text file
A file in which fields of data are separated by a character such as a comma or a tab.
referential integrity
A set of rules that Access enforces to maintain consistency between related tables when you update data in a database.
Cascade Update Related Fields option
Access permits the change in value to the primary key and changes the appropriate foreign key values in the related table, thereby eliminating the possibility of inconsistent data.
Cascade Delete Related Records option
Access deletes the record in the primary table and also deletes all records in related tables that have matching foreign key values. (Use with caution!)
relation
A two-dimensional table in which:
1) The entries in the table are single-valued. Each location contains only one value
2) Each column has a distinct name (attribute name)
3) All values in a column are values of the same attribute (all entries must match that column name)
4) The order of columns is immaterial
5) Each row is distinct
6) The order of rows is immaterial
rows (in a table (relation))
Often called records or tuples
columns (in a table (relation))
Often called fields or attributes
qualify the column names
When you combine a column name with a table name (Ex: Rep.RepNum)
query
A question represented in a way that the DBMS can recognize and process.
Query-By-Example
(QBE) - An approach to writing queries that is very visual. Users ask their questions by entering column names and other criteria using an on-screen grid, and data appears on the screen in tabular form.
criteria
Conditions that data must satisfy.
criterion
A single condition that data must satisfy.
comparison operator
Also called a relational operator - Used when you want something other than an exact match. The comparison operators are:
1) = (equal to)
2) > (greater than)
3) < (less than)
4) >= (greater than or equal to)
5) <= (less than or equal to)
6) NOT (not equal to)
compound criteria
Or compound conditions - A combination of criteria.
compound field
Or calculated field - A field that is the result of a calculation using one or more existing fields.
built-in functions
Called aggregate functions - Built in functions such as: Count, Sum, Avg (average), Max (largest value), Min (smallest value), StDev (standard deviation), Var (variance), First, Last.
grouping
Creating groups of records that share some common characteristics.
sort
To list the records in a query in a particular way.
sort key
The field on which records are sorted. It can be more than one field.
major sort key
The first sort key.
minor sort key
second sort key.
update query
A query that changes data. To enable this option, click the Update button in the Query Type group on the QUERY TOOLS DESIGN tab.
delete query
Permanently deletes all the records satisfying the criteria entered in the query.
make-table query
Creates a new table using the results of a query.
relational algebra
Theoretical way of manipulating a relational database. Includes operations that act on existing tables to produce new tables, similar to the way the operations of addition, and subtraction act on numbers to produce new numbers.
select (in relational algebra)
Takes a horizontal subset of a table, that is, it retrieves certain rows from an existing table and saves them as a new table.
project (in relational algebra)
Takes a vertical subset of a table, that is, it causes only certain columns to be included in the new table.
join (in relational algebra)
The core operation of relational algebra, it is the command that allows you to extract data from more than one table.
concatenation
combination of a row from the first table and a row from the second table that match on the common column.
join column
The column that two tables have matches on in a JOIN operation.
natural join
The tables are joined together to form a new table labeled “Temp”. Only matched records appear in the new table.
outer join
Similar to natural join but it also includes records from each original table that are not common in both tables.
Normal Set Operations
Relational algebra includes set operations for:
1) union
2) intersection
3) difference
union - of tables A and B
Is a table containing all rows that are in either table A or table B or in both tables. The tables MUST have the same structure for a union to be appropriate, known as union compatible.
intersection - of tables A and B
Is a table containing all rows that are common in both tables.
difference - of tables A and B
Referred to as “A minus B” - Is the set of all rows that are in table A but that are not in table B.
union compatible
Two tables must have the same structure, as in the same number of columns and corresponding columns represent the same type of data.
product
Mathematically called the Cartesian product - The table obtained by concatenating every row in the first table with every row in the second table. Every row of table A is matched with every row in table B. If table A has m rows and table B has n rows, the product will have m*n rows.
division
Most common use is the division of a table with two columns (Table A) by a table with a single column (Table B). The quotient (the result of the division) is a new table with a single column with the name of the column in Table A that was not in Table B. The rows consist of all items in Table A that “match” the items in Table B.
Structured Query Language
(SQL) - A computer language that provides users with the capacity of querying a relational database.
commands (SQL)
Used in SQL to obtain the desired results rather than completing an on-screen form. Can be used to create and update tables and retrieve data from tables.
CREATE TABLE (SQL command)
Used to create a table by describing its layout. The word table is followed by the name of the table to be created and then the names and data types of the columns that make up the table.
INTEGER (SQL command)
Data type used for numbers without a decimal part.
SMALLINT (SQL command)
Stores integers but uses less space.
DECIMAL(p,q) (SQL command)
Stores a decimal number p digits long with q of these digits being decimal places
CHAR(n) (SQL command)
Stores character string n characters long.
DATE (SQL command)
Stores dates in the form DD-MON-YYYY
SELECT-FROM-WHERE (SQL command)
The basic from of an SQL retrieval command.
SELECT (SQL command)
After this word, you list the fields you want to display in the query results.
Example:
SELECT CustomerName
FROM (SQL command)
After this word, you list the table(s) that contain the data to display in the query results.
Example:
FROM Customer
WHERE (SQL command)
After this word, you list any conditions that you want to apply to the data you want to retrieve, such as indicating that the credit limit much be a >, =, <> (not equal), or != (not equal).
Example:
WHERE CreditLimit=10000
reserved words (SQL)
Certain words using in SQL as command and written in all uppercase.
SELECT* (SQL command)
- indicates that you want to include all of the fields in the query results in the order in which they are ordered in the table.
compound condition
Formed by connecting two or more simple conditions using one or both of the following operators: AND and OR.
AND (SQL operator)
When used to connect simple conditions, all the simple conditions must be true for the compound condition to be true.
Example:
WHERE Storehouse=’3’
AND OnHand>40
OR (SQL operator)
When used to connect simple conditions, the compound condition will be true whenever any of the simple conditions are true (inclusive).
Example:
WHERE Storehouse=’3’
OR OnHand>40
NOT (SQL operator)
Indicates that the condition must be false for a record to appear in the query results.
Example:
WHERE NOT Storehouse=’3’
BETWEEN (SQL operator)
Indicates the value must be between the listed numbers.
Example:
WHERE Balance BETWEEN 1000 AND 5000
Computed Fields
You can include fields in queries that are not in the database but whose values you can compute from existing database fields. They can involve addition (+), subtraction (-), multiplication (*), or division (/).
Example:
SELECT CreditLimit-Balance AS AvailableCredit
Special Operators (SQL)
LIKE and IN - Used when exact matches are not required.
LIKE (SQL operator)
When you want to know only that the desired value contains a certain collection of characters.
Example:
WHERE Street LIKE “Oxford”
IN (SQL operator)
Provides a concise way of phrasing certain conditions. Essentially, you can create a set of values and the results will include any that match a value in the set.
Example:
WHERE creditLimit IN (7500, 10000, 15000)
wildcards (SQL)
? - Used to represent any individual character. Example: T?m, represents the letter T followed by any single character, followed by the letter m and when used with WHERE, retrieves records that include the words TIM, TOM, or T3M.
ORDER BY (SQL operator)
Used to sort the data in the way you desire. ORDER BY CustomerName (CustomerName is considered the Sort Key)
Built-In Functions (SQL)
SQL has the following built in functions: COUNT, SUM, AVG, MAX, and MIN.
COUNT and SUM
There are two differences between COUNT and SUM - other than the obvious fact that they are computing different statistics. The case of SUM, you must specify the field for which you want a total and the field must be numeric.
subquery
In some cases, it is useful to obtain the results you want in two stages. You can do so by placing one query inside another. The inner query is called a subquery and is evaluated first. Example: SELECT OrderNum FROM OrderLine WHERE ItemNum IN (SELECT ItemNum FROM Item WHERE Storehouse='3') ;
grouping
Creating groups of records that share a common characteristic.
GROUP BY (SQL command)
Command to have the query results grouped by a particular characteristic.
Example:
GROUP BY RepNum
ORDER BY RepNum
HAVING (SQL command)
Is to groups what WHERE is to rows, allows you to display only those groups that meet certain criteria. Example: WHERE CreditLimit2 ORDER BY RepNum ;
joining tables (SQL)
To join tables, you will construct the SQL command as follows:
1) In the SELECT clause, list all fields you want to display.
2) In the FROM clause, list all tables involved in the query.
3) In the WHERE clause, give the condition that will restrict the data to be retrieved to only those rows from the two tables that match; that is, you’ll restrict it to the rows that have common values in matching fields.
UNION (SQL command)
A table containing all rows that are in the first table, the second table, or both tables. The two tables must have the same structure, or be union compatible (same number of fields and the corresponding fields must have the same data types).
Example:
SELECT CustomerNum, CustomerName
FROM Customer
WHERE RepNum=’15’
UNION
SELECT Customer.CustomerNum, CustomerName
From Customer, Orders
WHERE Customer.CustomerNum=Orders.CustomerNum
UPDATE (SQL command)
Used to make changes to existing data. Example: UPDATE Customer SET Street='1445 Rivard' WHERE CustomerNum='502'
INSERT INTO (SQL command)
Allows you to insert data into a table.
Example:
INSET INTO Rep
VALUES
('75','Argy','Dorothy','424 Bournemouth','Grove','CA','90092',0.00,0.06)DELETE (SQL command)
Used to delete data from the database. Example: DELETE FROM OrderLine WHERE ItemNum='DL51'
INTO (SQL command)
Used to save the results of a query as a table.
Example:
SELECT *
INTO SmallCust ———- Name of table to create
FROM Customer
WHERE CreditLimit,=75000
;
pattern match
Selects records with a value for the designated field that matches the pattern of a simple condition value.
Like comparison operator
Selects records by matching field values to a specific pattern that includes one or more of these wildcard characters: asterisk (*), question mark (?), and number symbol (#).
wildcard - asterisk (*)
Represents a string of characters.
wildcard - question mark (?)
Represents a single character.
wildcard - number symbol (#)
Represents any single digit.
