Vocab. Ch. 1-3

Question 1

Redundancy

Answer 1

The duplication of data or the storing of the same data in more than one place.

Question 2

database

Answer 2

A structure that contains information about many different categories of information and about the relationships between those categories.

Question 3

entity

Answer 3

A person, place, object, event, ore idea for which you want to store and process data.

Question 4

attribute

Answer 4

A characteristic or property of an entity.

Question 5

relationship

Answer 5

An association between relationships.

Question 6

one-to-many relationship

Answer 6

One entity is associated with many other entities.

Question 7

data file

Answer 7

A file that is used to store data.

Question 8

database

Answer 8

A structure that can store information about multiple types of entities, the attributes of those entities, and the relationship between the entities.

Question 9

entity-relationship (E-R) diagram

Answer 9

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.

Question 10

database management system (DBMS)

Answer 10

A program, or a collection of programs, through which users interact with a database.

Question 11

database design

Answer 11

The process of determining the structure of a database.

Question 12

forms

Answer 12

Screen objects used to maintain, view, and print data from a database.

Question 13

9 Advantages of Database Processing

Answer 13

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

Question 14

database administrator

Answer 14

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.

Question 15

integrity constraint

Answer 15

A rule that data must follow in the database.

Question 16

integrity

Answer 16

Maintained by the database because all data in it must satisfy all established integrity constraints.

Question 17

security

Answer 17

The prevention of unauthorized access to the database.

Question 18

data independence

Answer 18

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.

Question 19

4 Disadvantages of Database Processing

Answer 19

1) Larger file size
2) Increased complexity
3) Greater impact of failure
4) More difficult recovery

Question 20

field

Answer 20

A single characteristic or attribute of a person, place, object, event, or idea.

Question 21

table

Answer 21

A collection of fields that describes a person, place, object, event, or idea.

Question 22

field value

Answer 22

The specific content of a field

Question 23

record

Answer 23

A set of field values for a specific entity.

Question 24

relational database

Answer 24

A collection of related tables.

Question 25

common field

Answer 25

A field that appears on both tables that provides the connection between the tables.

Question 26

template

Answer 26

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.

Question 27

data type

Answer 27

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

Question 28

data type - AutoNumber

Answer 28

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.

Question 29

data type - Short Text

Answer 29

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.

Question 30

composite key

Answer 30

When two or more fields function as the primary key.

Question 31

foreign key

Answer 31

When you include the primary key from on table as a field in a second table to form a relationship.

Question 32

property of a field

Answer 32

1) field’s name
2) data type
3) maximum number of characteristics or digits
4) description
5) valid values
6) other

Question 33

caption property

Answer 33

Specifies how the field name is displayed in database objects, including table and query datasheets, forms, and reports.

Question 34

default value property

Answer 34

Specifies what value will appear, by default, for the field in each new record you add to a table.

Question 35

delimited text file

Answer 35

A file in which fields of data are separated by a character such as a comma or a tab.

Question 36

referential integrity

Answer 36

A set of rules that Access enforces to maintain consistency between related tables when you update data in a database.

Question 37

Cascade Update Related Fields option

Answer 37

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.

Question 38

Cascade Delete Related Records option

Answer 38

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!)

Question 39

relation

Answer 39

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

Question 40

rows (in a table (relation))

Answer 40

Often called records or tuples

Question 41

columns (in a table (relation))

Answer 41

Often called fields or attributes

Question 42

qualify the column names

Answer 42

When you combine a column name with a table name (Ex: Rep.RepNum)

Question 43

query

Answer 43

A question represented in a way that the DBMS can recognize and process.

Question 44

Query-By-Example

Answer 44

(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.

Question 45

criteria

Answer 45

Conditions that data must satisfy.

Question 46

criterion

Answer 46

A single condition that data must satisfy.

Question 47

comparison operator

Answer 47

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)

Question 48

compound criteria

Answer 48

Or compound conditions - A combination of criteria.

Question 49

compound field

Answer 49

Or calculated field - A field that is the result of a calculation using one or more existing fields.

Question 50

built-in functions

Answer 50

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.

Question 51

grouping

Answer 51

Creating groups of records that share some common characteristics.

Question 52

sort

Answer 52

To list the records in a query in a particular way.

Question 53

sort key

Answer 53

The field on which records are sorted. It can be more than one field.

Question 54

major sort key

Answer 54

The first sort key.

Question 55

minor sort key

Answer 55

second sort key.

Question 56

update query

Answer 56

A query that changes data. To enable this option, click the Update button in the Query Type group on the QUERY TOOLS DESIGN tab.

Question 57

delete query

Answer 57

Permanently deletes all the records satisfying the criteria entered in the query.

Question 58

make-table query

Answer 58

Creates a new table using the results of a query.

Question 59

relational algebra

Answer 59

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.

Question 60

select (in relational algebra)

Answer 60

Takes a horizontal subset of a table, that is, it retrieves certain rows from an existing table and saves them as a new table.

Question 61

project (in relational algebra)

Answer 61

Takes a vertical subset of a table, that is, it causes only certain columns to be included in the new table.

Question 62

join (in relational algebra)

Answer 62

The core operation of relational algebra, it is the command that allows you to extract data from more than one table.

Question 63

concatenation

Answer 63

combination of a row from the first table and a row from the second table that match on the common column.

Question 64

join column

Answer 64

The column that two tables have matches on in a JOIN operation.

Question 65

natural join

Answer 65

The tables are joined together to form a new table labeled “Temp”. Only matched records appear in the new table.

Question 66

outer join

Answer 66

Similar to natural join but it also includes records from each original table that are not common in both tables.

Question 67

Normal Set Operations

Answer 67

Relational algebra includes set operations for:

1) union
2) intersection
3) difference

Question 68

union - of tables A and B

Answer 68

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.

Question 69

intersection - of tables A and B

Answer 69

Is a table containing all rows that are common in both tables.

Question 70

difference - of tables A and B

Answer 70

Referred to as “A minus B” - Is the set of all rows that are in table A but that are not in table B.

Question 71

union compatible

Answer 71

Two tables must have the same structure, as in the same number of columns and corresponding columns represent the same type of data.

Question 72

product

Answer 72

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.

Question 73

division

Answer 73

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.

Question 74

Structured Query Language

Answer 74

(SQL) - A computer language that provides users with the capacity of querying a relational database.

Question 75

commands (SQL)

Answer 75

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.

Question 76

CREATE TABLE (SQL command)

Answer 76

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.

Question 77

INTEGER (SQL command)

Answer 77

Data type used for numbers without a decimal part.

Question 78

SMALLINT (SQL command)

Answer 78

Stores integers but uses less space.

Question 79

DECIMAL(p,q) (SQL command)

Answer 79

Stores a decimal number p digits long with q of these digits being decimal places

Question 80

CHAR(n) (SQL command)

Answer 80

Stores character string n characters long.

Question 81

DATE (SQL command)

Answer 81

Stores dates in the form DD-MON-YYYY

Question 82

SELECT-FROM-WHERE (SQL command)

Answer 82

The basic from of an SQL retrieval command.

Question 83

SELECT (SQL command)

Answer 83

After this word, you list the fields you want to display in the query results.
Example:
SELECT CustomerName

Question 84

FROM (SQL command)

Answer 84

After this word, you list the table(s) that contain the data to display in the query results.
Example:
FROM Customer

Question 85

WHERE (SQL command)

Answer 85

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

Question 86

reserved words (SQL)

Answer 86

Certain words using in SQL as command and written in all uppercase.

Question 87

SELECT* (SQL command)

Answer 87

• 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.

Question 88

compound condition

Answer 88

Formed by connecting two or more simple conditions using one or both of the following operators: AND and OR.

Question 89

AND (SQL operator)

Answer 89

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

Question 90

OR (SQL operator)

Answer 90

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

Question 91

NOT (SQL operator)

Answer 91

Indicates that the condition must be false for a record to appear in the query results.
Example:
WHERE NOT Storehouse=’3’

Question 92

BETWEEN (SQL operator)

Answer 92

Indicates the value must be between the listed numbers.
Example:
WHERE Balance BETWEEN 1000 AND 5000

Question 93

Computed Fields

Answer 93

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

Question 94

Special Operators (SQL)

Answer 94

LIKE and IN - Used when exact matches are not required.

Question 95

LIKE (SQL operator)

Answer 95

When you want to know only that the desired value contains a certain collection of characters.
Example:
WHERE Street LIKE “Oxford”

Question 96

IN (SQL operator)

Answer 96

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)

Question 97

wildcards (SQL)

Answer 97

? - 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.

Question 98

ORDER BY (SQL operator)

Answer 98

Used to sort the data in the
way you desire.
ORDER BY CustomerName
(CustomerName is
considered the Sort Key)

Question 99

Built-In Functions (SQL)

Answer 99

SQL has the following built in functions: COUNT, SUM, AVG, MAX, and MIN.

Question 100

COUNT and SUM

Answer 100

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.

Question 101

subquery

Answer 101

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')
;

Question 102

grouping

Answer 102

Creating groups of records that share a common characteristic.

Question 103

GROUP BY (SQL command)

Answer 103

Command to have the query results grouped by a particular characteristic.
Example:
GROUP BY RepNum
ORDER BY RepNum

Question 104

HAVING (SQL command)

Answer 104

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
;

Question 105

joining tables (SQL)

Answer 105

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.

Question 106

UNION (SQL command)

Answer 106

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

Question 107

UPDATE (SQL command)

Answer 107

Used to make changes to existing data.
Example:
UPDATE Customer
SET Street='1445 Rivard'
WHERE CustomerNum='502'

Question 108

INSERT INTO (SQL command)

Answer 108

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)

Question 109

DELETE (SQL command)

Answer 109

Used to delete data from the database.
Example:
DELETE
FROM OrderLine
WHERE ItemNum='DL51'

Question 110

INTO (SQL command)

Answer 110

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
;

Question 111

pattern match

Answer 111

Selects records with a value for the designated field that matches the pattern of a simple condition value.

Question 112

Like comparison operator

Answer 112

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 (#).

Question 113

wildcard - asterisk (*)

Answer 113

Represents a string of characters.

Question 114

wildcard - question mark (?)

Answer 114

Represents a single character.

Question 115

wildcard - number symbol (#)

Answer 115

Represents any single digit.