Vocab. Ch. 4-6 Flashcards
1
Q
view
A
An application program’s or an individual user’s picture of the database. An individual can use a view to create reports, charts, and other objects using database data.
2
Q
CREATE VIEW
A
The SQL command used to create a view. Example: CREATE VIEW Games AS SELECT ItemNum, Description, OnHand, Price FROM Item WHERE Category='GME'
3
Q
defining query
A
The SELECT command that creates the view and indicates what to include in the view.
4
Q
queries on views
A
The DBMS will not except a query on a view. Instead, it merges the query with the query that defines the view to form the query that is actually executed.
5
Q
CREATE VIEW Games (INum, IDesc, OnHd, Price) AS
SELECT ItemNum, Description, OnHand, Price
FROM Item
WHERE Category=’GME’
A
Creates a view and changes the field names to INum, IDesc, OnHd, Price
6
Q
row-and-column subset view
A
A view that consists of a subset of the rows and columns in some individual table.
7
Q
Advantages of views
A
1) Provide data independence. The data base can change without the need to change the view, as long as the change is compatible with the requirements of the view.
2) Each user can have their own view.
3) Views should contain only the fields needed by the user, simplifying the interface. In addition, it provides security for the data.
8
Q
Indexes
A
The main mechanism for increasing the efficiency with which data is retrieved from the database within relational model systems. They are very much like indexes in a book, indexes typically contain two fields, one contains the attribute and the other contains the number of the record(s) where it can be found.
9
Q
index key
A
The field or combination of fields on which the index is built.
10
Q
index pros & cons
A
Pros - Makes certain types of retrials more efficient.
Cons - The index takes up space on a storage device.
- The DBMS must update the index whenever corresponding data in the database is updated.
11
Q
automatic index creation
A
Access automatically generates an index for each primary key.
12
Q
CREATE INDEX
A
The SQL command for creating an index.
13
Q
single-field index
A
Also called a single-column index, an index whose key is a single field.
14
Q
multiple-field index
A
Also called a multiple-column index, an index whose key is a more than one key field.
15
Q
DROP INDEX
A
The SQL command used to drop (delete) and index that is no longer necessary.
16
Q
security
A
The prevention of unauthorized access to the database.
17
Q
security mechanisms
A
There are two security mechanisms in SQL systems.
1) Views provide a certain amount of security.
2) The GRANT statement is the main mechanism for providing access to a database.
18
Q
GRANT
A
The basic idea of the GRANT statement is that different types of privileges can be granted to users and, if necessary, later revoked. The privileges include such things as the right to select, insert, update, and delete table data.
19
Q
REVOKED
A
The SQL command used to revoke a users privileges.
20
Q
Integrity Rules
A
A relational DBMS must enforce two important integrity rules:
1) entity integrity
2) referential integrity
21
Q
Entity Integrity
A
The primary key should never be allowed to have a null value.
22
Q
Referential Integrity
A
The rule that if table A contains a foreign key that matches the primary key of table B, the values of this foreign key must match the value of the primary key for some row in table B or be null. (Enforce Referential Integrity)
23
Q
Casecade Delete Related Records
A
A check box which ensures that the deletion of a record also deletes any records that relate to it.
24
Q
Casecade Update Related Fields
A
A check box which ensures that changes made to the primary key of a record are also made in the related field record.
25
Legal-Values Integrity
A third type of integrity, it is the property that states that no record can exist in the database with a value in the field other than one of the legal values.
26
CHECK
The SQL clause used to enforce legal-values integrity.
27
ALTER TABLE
The SQL command used to change a table's structure.
28
DROP TABLE
The SQL command to delete a table that is no longer needed.
29
system catalog
```
or the 'catalog', is where information about tables in the database is kept and it is maintained automatically by the DBMS.
Also contains tables:
1) Systables
2) Syscolumns
3) Sysindexes
4) Sysviews.
```
30
Systables
Information about the tables known to SQL
31
Syscolumns
Information about the columns or fields within these tables.
32
Sysindexes
Information about the indexes that are defined on these tables.
33
Sysviews
Information about the views that have been created.
34
Documenter
A tool in Access which allows you to print detailed documentation about any table, query, report, form, or other object in the database.
35
client
In a client/server system, the client is a computer that is connected to a network and has access through the server to the database.
36
server
In a client/server system, the server is the computer where the database resides.
37
stored procedure
A place on the server where queries that are run often are stored to improve overall performance.
38
trigger
An action that occurs automatically in response to an associated database operation such as and INSERT, UPDATE, DELETE or command
39
data macro
What Access uses to offer the functionality of triggers. They enable you to add logic to table events such as adding, changing, or deleting data.
40
arguments
Additional information required by actions in order for the action to be completed.
41
pattern match
Selects records with a value for the designated field that matches the pattern of a simple condition value.
42
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 (#).
43
asterisk (*)
Wildcard - Represents any string of characters.
44
question mark (?)
Wildcard - Represents any single character.
45
number symbol (#)
Wildcard - Represents any single digit.
46
& (ampersand) operator
A concatenation operator that joins text expressions.
47
concatenation
Refers to joining two or more text fields or characters encapsulated in quotes.
48
IIF function
(Immediate If) - Assigns one value to a calculated field or control if a conditions is true, and a second value if the condition is false. It has three parts:
1) a condition that is true or false,
2) the result when the condition is true, and
3) the result when the condition is false.
49
parameter query
Displays a dialog box that prompts the user to enter one or more criteria values when the query is run.
50
crosstab query
Use the aggregate functions (Avg, Count, First, Last, Max, Min, StDev, Sum, Var) to perform arithmetic operations on selected records. It can also display one additional aggregate function value that summarizes the set of values in each row. It uses one or more fields for the row headings on the left and one field for the column headings at the top.
51
find duplicates query
A select query that finds duplicate records in a table or query. (Use Query Wizard)
52
find unmatched query
A select query that finds all records in a table or query that have no related records in a second table or query. (Use Query Wizard)
53
top values property
Lets you limit the number of records in the query results.
54
property propagation
The capability to update field properties in objects automatically when you modify a table field property.
55
object dependency
Exists between two objects when a change to the properties of data in one object affects the properties of data in the other object.
56
field validation rule
Verifies a field value by comparing it to a constant or to a set of constants.
57
table validation rule
A validation rule that verifies multiple fields.
58
trusted folder
A folder on a drive or network that you designate as trusted and where you place databases you know are safe.
59
normalization process
Enables you to identify the existence of potential problems, called update anomalies, in the design of a relational database.
60
update anomalies
Potential problems with the relational database design.
61
normal form
Forms that tables are converted into in order to correct update anomalies in a database.
62
functional dependence
A column (attribute) B is functionally dependent on another column A (or perhaps a collection of columns) when each value for A in the database is associated with exactly one value of B.
63
functionally determines
If B is functionally dependent on A, you can say that A functionally determines B.
64
primary key (New, more precise definition)
```
Column A (or a collection of columns) is the primary key for a relation (table) R, if:
Property 1: All columns in R are functionally dependent on A.
Property 2: No sub collection of the columns in A (assuming A is a collection of columns and not just a single column) also has property 1.
```
65
repeating group
Multiple entries for a single record.
66
unnormalized relation
A relation that contains a repeating group.
67
first normal form
(1NF) - A table (relation) that does not contain repeating groups.
68
second normal form
(2NF) - A table (relation) that is in 1NF and no non-key column is dependent on only a portion of the primary key.
69
converting an unnormalized form to 1NF
In general, when converting a table that is not in 1NF to 1NF, the primary key will usually include the original primary key concatenated with the key to the repeating group, which is the column that distinguishes one occurrence of the repeating group from another on a given row in the table.
70
update anomalies
1) update
2) inconsistent data
3) additions
4) deletions
71
update anomaly (update)
A change to the parameter for an entity requires more than one change.
72
update anomaly (inconsistent data)
When a single entity has more than one entry for a particular attribute.
73
update anomaly (additions)
When in order to add a new entity to the table, you have to put in temporary data for an attribute. This causes the data to be inaccurate until the "correct" data is entered.
74
update anomaly (deletions)
When you delete an entity, you delete all of the information for another entity.
75
non-key column
Also a non-key attribute, is a column that is not part of the primary key.
76
dependency diagram
A diagram that uses arrows to indicate all the functional dependencies present in a table.
77
partial dependencies
Dependencies on only a portion of the primary key.
78
converting a 1NF table to 2NF
First, take each subset of the set of columns that makes up the primary key; then begin a new table with this subset as the primary key.
Next, place each of the other columns with its appropriate primary key; that is, place each primary key with the minimal collection of columns on which it depends.
79
third normal form
(3NF) - A table (relation) that is in 2NF and the only determinate it contains are candidate keys.
80
determinant
Any column (or collection of columns) that determines another column.
81
candidate key
A column or a collection of columns on which all columns in the table are functionally dependent. All candidate keys meet the definition for a primary key.
82
alternative keys
Any candidate key not chosen as the primary key.
83
converting a 2NF table to 3NF
First, for each determinant that is not a candidate key, remove from the table the columns that depend on this determinant (but do not remove the determinant).
Next, create a new table containing all the columns from the original table that depend on this determinant.
Finally, make the determinant the primary key of this new table.
84
fourth normal form
(4NF) - A table (relation) that is in 3NF and there are no multivalued dependencies.
85
multivalued dependence
A table with columns A, B, & C, where each value of A is associated with a specific collection of values for B, and further, this collection is independent of any values for C.
86
three aspects concerning normalization to keep in mind
1) Carefully convertibles to third normal form.
2) Functional dependencies can change over time. It is critical to review assumptions and dependencies periodically to see if any changes to the design are warranted.
3) By splitting relations to achieve 3NF tables, you create the need to express an interrelation constraint.
87
interrelation constraint
A condition that involves two or more relations.
88
custom form
A form that you have created and then changed to meet your needs.
89
Record Source property
Specifies the table or query that provides the fields for the form.
90
text box
A control that lets the user type an entry.
91
combo box
Is a control that combines the features of a text box and a list box; it lets a user either choose a value from a list or type an entry.
92
Row Source property
Specifies the data source for a control in a form or report or for a field in a table or query.
93
Display Control property
Specifies the default control used to display a field.
94
Documenter
Another Access tool, it creates detailed documentation of all, or selected, objects in a database.
95
Datasheet too
Creates a form in a data sheet that contains all the fields in the source table or query.
96
control layout
A set of controls grouped together in a form or report, so that you can manipulate the set as a single control.
97
unbound control
A control that is not connected to a field in the database. You use them to display text, such as a form title.
98
calculated control
Displays a value that is the result of an expression.
99
label
An unbounded control that displays text.
100
visibility property
Determines if Access displays a control or section.
101
Control Source property
Specifies the source of the data that appears in the control.
102
Tab Stop property
Specifies whether users can just the Tab key to move to a control on a form.
103
ControlTip Text property
Specifies the text that appears in a ScreenTip when users hold the mouse pointer over a control in a form.
104
information-level design
The first step of a two step process where database designers design a database that satisfies the organization's requirements as cleanly as possible. This step is completely independent of any particular DBMS that the organization will use.
105
physical-level design
The second step of a two step process where database designers adapt the informational-level design for the specific DBMS that the organization will use.
106
user view
The set of requirements that is necessary to support the operations of a particular database user.
107
cumulative design
The view that supports all the user views encountered during the design process.
108
information-level design method steps
1) Represent the user view as a collection of tables.
2) Normalize these tables.
3) Identify all keys in these tables.
4) Merge the result of Steps 1 through 3 into the cumulative design.
109
The process of developing table for user views
1) Determine the entities involved and create a separate table for each type of entity.
2) Determine the primary key for each table.
3) Determine the properties for each entity.
4) Determine relationships between entities.
5) Normalize each table.
110
Creating a one-to-many relationship
Include the primary key of the "one" table as a foreign key in the "many" table.
111
Creating a many-to-many relationship
Create a new table whose primary key is the combination of the primary keys of the original tables.
112
Creating a one-to-one relationship
This type of relationship is rare, treat it like a one-to-many relationship.
113
secondary keys
Columns that are of interest strictly for the purpose of retrieval.
114
natural key
Also called a logical key or an intelligent key - A primary key that consists of a column that uniquely identifies an entity, is inherent to the entity, and visible to users.
115
artificial key
A column that you create for an entity to serve solely as the primary key and that is visible to users.
116
surrogate key
Also called a synthetic key - A system-generated primary key that is usually hidden from users.
117
Database Design Language
(DBDL) - You represent a table by using all columns and then underlying the primary key.
118
Entiy-Relationship (E-R) Diagrams
A popular type of diagram that visually represents the structure of a database in the entity-relationship (E-R) diagram.
119
IDEF1X
"Integrated Definitions" - The style of E-R digram used in the text.
120
rectangle (E-R diagram)
Represents an entity.
121
primary key (E-R diagram)
Located above the line that is inside the rectangle for the entity.
122
attributes (E-R diagram)
Located below the line that is inside the rectangle for each entity.
123
alternate, secondary, and foreign keys (E-R diagram)
The letters AK, SK, and FK appear in parentheses following each.
124
foreign key (E-R diagram)
A dashed line with with a dot at one end show the connection between the table using a foreign key and the table it comes from. The dot is placed next to the table that is using the foreign key.
125
bottom-up design method
Specific user requirements are synthesized into a design. This method provides for a rigorous way of tackling each separate requirement and ensuring that it will be met.
126
top-down design method
Begins with a general database design that models the overall enterprise and repeatedly refines the model to achieve a design that supports all necessary applications. This method leads to a more global feel to the project.
127
survey form
A form that is developed to get information from the users to determine what will be needed by the database. It should request the following information.
1) Entity information
2) Attribute (column) information
3) Relationships
4) Functional dependencies
5) Processing information - The manner in which the various types of processing (updates to the database, reports that must be produced, and so on) are to take place.
128
many-to-many-to many relationship
When all three entities are critical in the relationship, the three-way relationship is appropriate.
129
nulls
When a field has no entry. Used when a value is either unknown or inapplicable.
130
entity subtype
A table where the primary key is also a foreign key.
131
category
Another name for a subtype.
132
incomplete category
A category that does not apply to all entities.
133
complete categories
Where an entity must be in one of multiple related categories.
134
entity-relationship (E-R) model
An approach to representing data in a database. It uses E-R diagrams exclusively as the tool for representing entities, attributes, and relationships.
135
composite entity
An entity that exists to implement a many-to-many relationship. Essentially, it is both an entity and a relationship and is represented in an E-R diagram by a diamond within a rectangle.
136
existence dependency
When the existence of one entity depends on the existence of another related entity. Represented by placing an E in the relationship diamond.
137
weak entity
An entity that depends on another entity for its own existence. A double rectangle encloses a weak entity. This term corresponds to a dependent entity.
138
cardinality
The number of times that must be included in a relationship. Maximum cardinality is "many."
139
optional role
The type of role played by an entity in a relationship with minimum cardinality of zero.
140
mandatory role
The type of role played by an entity with a minimum cardinality of one.
