Vocab. Ch. 4-6 Flashcards
view
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.
CREATE VIEW
The SQL command used to create a view. Example: CREATE VIEW Games AS SELECT ItemNum, Description, OnHand, Price FROM Item WHERE Category='GME'
defining query
The SELECT command that creates the view and indicates what to include in the view.
queries on views
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.
CREATE VIEW Games (INum, IDesc, OnHd, Price) AS
SELECT ItemNum, Description, OnHand, Price
FROM Item
WHERE Category=’GME’
Creates a view and changes the field names to INum, IDesc, OnHd, Price
row-and-column subset view
A view that consists of a subset of the rows and columns in some individual table.
Advantages of views
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.
Indexes
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.
index key
The field or combination of fields on which the index is built.
index pros & cons
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.
automatic index creation
Access automatically generates an index for each primary key.
CREATE INDEX
The SQL command for creating an index.
single-field index
Also called a single-column index, an index whose key is a single field.
multiple-field index
Also called a multiple-column index, an index whose key is a more than one key field.
DROP INDEX
The SQL command used to drop (delete) and index that is no longer necessary.
security
The prevention of unauthorized access to the database.
security mechanisms
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.
GRANT
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.
REVOKED
The SQL command used to revoke a users privileges.
Integrity Rules
A relational DBMS must enforce two important integrity rules:
1) entity integrity
2) referential integrity
Entity Integrity
The primary key should never be allowed to have a null value.
Referential Integrity
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)
Casecade Delete Related Records
A check box which ensures that the deletion of a record also deletes any records that relate to it.
Casecade Update Related Fields
A check box which ensures that changes made to the primary key of a record are also made in the related field record.
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.
CHECK
The SQL clause used to enforce legal-values integrity.
ALTER TABLE
The SQL command used to change a table’s structure.
DROP TABLE
The SQL command to delete a table that is no longer needed.
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.
Systables
Information about the tables known to SQL
Syscolumns
Information about the columns or fields within these tables.
Sysindexes
Information about the indexes that are defined on these tables.
Sysviews
Information about the views that have been created.
Documenter
A tool in Access which allows you to print detailed documentation about any table, query, report, form, or other object in the database.
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.
server
In a client/server system, the server is the computer where the database resides.
stored procedure
A place on the server where queries that are run often are stored to improve overall performance.
trigger
An action that occurs automatically in response to an associated database operation such as and INSERT, UPDATE, DELETE or command
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.
arguments
Additional information required by actions in order for the action to be completed.
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 (#).
asterisk (*)
Wildcard - Represents any string of characters.
question mark (?)
Wildcard - Represents any single character.
number symbol (#)
Wildcard - Represents any single digit.
& (ampersand) operator
A concatenation operator that joins text expressions.
concatenation
Refers to joining two or more text fields or characters encapsulated in quotes.
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.
parameter query
Displays a dialog box that prompts the user to enter one or more criteria values when the query is run.
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.
find duplicates query
A select query that finds duplicate records in a table or query. (Use Query Wizard)
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)
top values property
Lets you limit the number of records in the query results.
property propagation
The capability to update field properties in objects automatically when you modify a table field property.
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.
field validation rule
Verifies a field value by comparing it to a constant or to a set of constants.
table validation rule
A validation rule that verifies multiple fields.
trusted folder
A folder on a drive or network that you designate as trusted and where you place databases you know are safe.
normalization process
Enables you to identify the existence of potential problems, called update anomalies, in the design of a relational database.
update anomalies
Potential problems with the relational database design.
normal form
Forms that tables are converted into in order to correct update anomalies in a database.
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.
functionally determines
If B is functionally dependent on A, you can say that A functionally determines B.
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.
repeating group
Multiple entries for a single record.
unnormalized relation
A relation that contains a repeating group.
first normal form
(1NF) - A table (relation) that does not contain repeating groups.
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.
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.
update anomalies
1) update
2) inconsistent data
3) additions
4) deletions
update anomaly (update)
A change to the parameter for an entity requires more than one change.
update anomaly (inconsistent data)
When a single entity has more than one entry for a particular attribute.
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.
update anomaly (deletions)
When you delete an entity, you delete all of the information for another entity.
non-key column
Also a non-key attribute, is a column that is not part of the primary key.
dependency diagram
A diagram that uses arrows to indicate all the functional dependencies present in a table.
partial dependencies
Dependencies on only a portion of the primary key.
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.
third normal form
(3NF) - A table (relation) that is in 2NF and the only determinate it contains are candidate keys.
determinant
Any column (or collection of columns) that determines another column.
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.
alternative keys
Any candidate key not chosen as the primary key.
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.
fourth normal form
(4NF) - A table (relation) that is in 3NF and there are no multivalued dependencies.
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.
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.
interrelation constraint
A condition that involves two or more relations.
custom form
A form that you have created and then changed to meet your needs.
Record Source property
Specifies the table or query that provides the fields for the form.
text box
A control that lets the user type an entry.
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.
Row Source property
Specifies the data source for a control in a form or report or for a field in a table or query.
Display Control property
Specifies the default control used to display a field.
Documenter
Another Access tool, it creates detailed documentation of all, or selected, objects in a database.
Datasheet too
Creates a form in a data sheet that contains all the fields in the source table or query.
control layout
A set of controls grouped together in a form or report, so that you can manipulate the set as a single control.
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.
calculated control
Displays a value that is the result of an expression.
label
An unbounded control that displays text.
visibility property
Determines if Access displays a control or section.
Control Source property
Specifies the source of the data that appears in the control.
Tab Stop property
Specifies whether users can just the Tab key to move to a control on a form.
ControlTip Text property
Specifies the text that appears in a ScreenTip when users hold the mouse pointer over a control in a form.
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.
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.
user view
The set of requirements that is necessary to support the operations of a particular database user.
cumulative design
The view that supports all the user views encountered during the design process.
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.
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.
Creating a one-to-many relationship
Include the primary key of the “one” table as a foreign key in the “many” table.
Creating a many-to-many relationship
Create a new table whose primary key is the combination of the primary keys of the original tables.
Creating a one-to-one relationship
This type of relationship is rare, treat it like a one-to-many relationship.
secondary keys
Columns that are of interest strictly for the purpose of retrieval.
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.
artificial key
A column that you create for an entity to serve solely as the primary key and that is visible to users.
surrogate key
Also called a synthetic key - A system-generated primary key that is usually hidden from users.
Database Design Language
(DBDL) - You represent a table by using all columns and then underlying the primary key.
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.
IDEF1X
“Integrated Definitions” - The style of E-R digram used in the text.
rectangle (E-R diagram)
Represents an entity.
primary key (E-R diagram)
Located above the line that is inside the rectangle for the entity.
attributes (E-R diagram)
Located below the line that is inside the rectangle for each entity.
alternate, secondary, and foreign keys (E-R diagram)
The letters AK, SK, and FK appear in parentheses following each.
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.
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.
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.
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.
many-to-many-to many relationship
When all three entities are critical in the relationship, the three-way relationship is appropriate.
nulls
When a field has no entry. Used when a value is either unknown or inapplicable.
entity subtype
A table where the primary key is also a foreign key.
category
Another name for a subtype.
incomplete category
A category that does not apply to all entities.
complete categories
Where an entity must be in one of multiple related categories.
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.
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.
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.
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.
cardinality
The number of times that must be included in a relationship. Maximum cardinality is “many.”
optional role
The type of role played by an entity in a relationship with minimum cardinality of zero.
mandatory role
The type of role played by an entity with a minimum cardinality of one.
