Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

*CIS 233 - Database Systems > Vocab. Ch. 4-6 > Flashcards

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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'
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

defining query

A

The SELECT command that creates the view and indicates what to include in the view.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

row-and-column subset view

A

A view that consists of a subset of the rows and columns in some individual table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

index key

A

The field or combination of fields on which the index is built.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

automatic index creation

A

Access automatically generates an index for each primary key.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

CREATE INDEX

A

The SQL command for creating an index.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

single-field index

A

Also called a single-column index, an index whose key is a single field.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

multiple-field index

A

Also called a multiple-column index, an index whose key is a more than one key field.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

DROP INDEX

A

The SQL command used to drop (delete) and index that is no longer necessary.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

security

A

The prevention of unauthorized access to the database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

REVOKED

A

The SQL command used to revoke a users privileges.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Integrity Rules

A

A relational DBMS must enforce two important integrity rules:

1) entity integrity
2) referential integrity

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Entity Integrity

A

The primary key should never be allowed to have a null value.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Legal-Values Integrity

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

CHECK

A

The SQL clause used to enforce legal-values integrity.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

ALTER TABLE

A

The SQL command used to change a table’s structure.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

DROP TABLE

A

The SQL command to delete a table that is no longer needed.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

system catalog

A 
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.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

Systables

A

Information about the tables known to SQL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

Syscolumns

A

Information about the columns or fields within these tables.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

Sysindexes

A

Information about the indexes that are defined on these tables.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

Sysviews

A

Information about the views that have been created.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

Documenter

A

A tool in Access which allows you to print detailed documentation about any table, query, report, form, or other object in the database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

client

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

server

A

In a client/server system, the server is the computer where the database resides.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

stored procedure

A

A place on the server where queries that are run often are stored to improve overall performance.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

trigger

A

An action that occurs automatically in response to an associated database operation such as and INSERT, UPDATE, DELETE or command

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

data macro

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

arguments

A

Additional information required by actions in order for the action to be completed.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

pattern match

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

like comparison operator

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

asterisk (*)

A

Wildcard - Represents any string of characters.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

question mark (?)

A

Wildcard - Represents any single character.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q

number symbol (#)

A

Wildcard - Represents any single digit.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

& (ampersand) operator

A

A concatenation operator that joins text expressions.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

concatenation

A

Refers to joining two or more text fields or characters encapsulated in quotes.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
48
Q

IIF function

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q

parameter query

A

Displays a dialog box that prompts the user to enter one or more criteria values when the query is run.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q

crosstab query

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

find duplicates query

A

A select query that finds duplicate records in a table or query. (Use Query Wizard)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q

find unmatched query

A

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)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
53
Q

top values property

A

Lets you limit the number of records in the query results.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
54
Q

property propagation

A

The capability to update field properties in objects automatically when you modify a table field property.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
55
Q

object dependency

A

Exists between two objects when a change to the properties of data in one object affects the properties of data in the other object.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
56
Q

field validation rule

A

Verifies a field value by comparing it to a constant or to a set of constants.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
57
Q

table validation rule

A

A validation rule that verifies multiple fields.

58
Q

trusted folder

A

A folder on a drive or network that you designate as trusted and where you place databases you know are safe.

59
Q

normalization process

A

Enables you to identify the existence of potential problems, called update anomalies, in the design of a relational database.

60
Q

update anomalies

A

Potential problems with the relational database design.

61
Q

normal form

A

Forms that tables are converted into in order to correct update anomalies in a database.

62
Q

functional dependence

A

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
Q

functionally determines

A

If B is functionally dependent on A, you can say that A functionally determines B.

64
Q

primary key (New, more precise definition)

A 
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
Q

repeating group

A

Multiple entries for a single record.

66
Q

unnormalized relation

A

A relation that contains a repeating group.

67
Q

first normal form

A

(1NF) - A table (relation) that does not contain repeating groups.

68
Q

second normal form

A

(2NF) - A table (relation) that is in 1NF and no non-key column is dependent on only a portion of the primary key.

69
Q

converting an unnormalized form to 1NF

A

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
Q

update anomalies

A

1) update
2) inconsistent data
3) additions
4) deletions

71
Q

update anomaly (update)

A

A change to the parameter for an entity requires more than one change.

72
Q

update anomaly (inconsistent data)

A

When a single entity has more than one entry for a particular attribute.

73
Q

update anomaly (additions)

A

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
Q

update anomaly (deletions)

A

When you delete an entity, you delete all of the information for another entity.

75
Q

non-key column

A

Also a non-key attribute, is a column that is not part of the primary key.

76
Q

dependency diagram

A

A diagram that uses arrows to indicate all the functional dependencies present in a table.

77
Q

partial dependencies

A

Dependencies on only a portion of the primary key.

78
Q

converting a 1NF table to 2NF

A

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
Q

third normal form

A

(3NF) - A table (relation) that is in 2NF and the only determinate it contains are candidate keys.

80
Q

determinant

A

Any column (or collection of columns) that determines another column.

81
Q

candidate key

A

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
Q

alternative keys

A

Any candidate key not chosen as the primary key.

83
Q

converting a 2NF table to 3NF

A

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
Q

fourth normal form

A

(4NF) - A table (relation) that is in 3NF and there are no multivalued dependencies.

85
Q

multivalued dependence

A

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
Q

three aspects concerning normalization to keep in mind

A

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
Q

interrelation constraint

A

A condition that involves two or more relations.

88
Q

custom form

A

A form that you have created and then changed to meet your needs.

89
Q

Record Source property

A

Specifies the table or query that provides the fields for the form.

90
Q

text box

A

A control that lets the user type an entry.

91
Q

combo box

A

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
Q

Row Source property

A

Specifies the data source for a control in a form or report or for a field in a table or query.

93
Q

Display Control property

A

Specifies the default control used to display a field.

94
Q

Documenter

A

Another Access tool, it creates detailed documentation of all, or selected, objects in a database.

95
Q

Datasheet too

A

Creates a form in a data sheet that contains all the fields in the source table or query.

96
Q

control layout

A

A set of controls grouped together in a form or report, so that you can manipulate the set as a single control.

97
Q

unbound control

A

A control that is not connected to a field in the database. You use them to display text, such as a form title.

98
Q

calculated control

A

Displays a value that is the result of an expression.

99
Q

label

A

An unbounded control that displays text.

100
Q

visibility property

A

Determines if Access displays a control or section.

101
Q

Control Source property

A

Specifies the source of the data that appears in the control.

102
Q

Tab Stop property

A

Specifies whether users can just the Tab key to move to a control on a form.

103
Q

ControlTip Text property

A

Specifies the text that appears in a ScreenTip when users hold the mouse pointer over a control in a form.

104
Q

information-level design

A

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
Q

physical-level design

A

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
Q

user view

A

The set of requirements that is necessary to support the operations of a particular database user.

107
Q

cumulative design

A

The view that supports all the user views encountered during the design process.

108
Q

information-level design method steps

A

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
Q

The process of developing table for user views

A

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
Q

Creating a one-to-many relationship

A

Include the primary key of the “one” table as a foreign key in the “many” table.

111
Q

Creating a many-to-many relationship

A

Create a new table whose primary key is the combination of the primary keys of the original tables.

112
Q

Creating a one-to-one relationship

A

This type of relationship is rare, treat it like a one-to-many relationship.

113
Q

secondary keys

A

Columns that are of interest strictly for the purpose of retrieval.

114
Q

natural key

A

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
Q

artificial key

A

A column that you create for an entity to serve solely as the primary key and that is visible to users.

116
Q

surrogate key

A

Also called a synthetic key - A system-generated primary key that is usually hidden from users.

117
Q

Database Design Language

A

(DBDL) - You represent a table by using all columns and then underlying the primary key.

118
Q

Entiy-Relationship (E-R) Diagrams

A

A popular type of diagram that visually represents the structure of a database in the entity-relationship (E-R) diagram.

119
Q

IDEF1X

A

“Integrated Definitions” - The style of E-R digram used in the text.

120
Q

rectangle (E-R diagram)

A

Represents an entity.

121
Q

primary key (E-R diagram)

A

Located above the line that is inside the rectangle for the entity.

122
Q

attributes (E-R diagram)

A

Located below the line that is inside the rectangle for each entity.

123
Q

alternate, secondary, and foreign keys (E-R diagram)

A

The letters AK, SK, and FK appear in parentheses following each.

124
Q

foreign key (E-R diagram)

A

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
Q

bottom-up design method

A

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
Q

top-down design method

A

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
Q

survey form

A

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
Q

many-to-many-to many relationship

A

When all three entities are critical in the relationship, the three-way relationship is appropriate.

129
Q

nulls

A

When a field has no entry. Used when a value is either unknown or inapplicable.

130
Q

entity subtype

A

A table where the primary key is also a foreign key.

131
Q

category

A

Another name for a subtype.

132
Q

incomplete category

A

A category that does not apply to all entities.

133
Q

complete categories

A

Where an entity must be in one of multiple related categories.

134
Q

entity-relationship (E-R) model

A

An approach to representing data in a database. It uses E-R diagrams exclusively as the tool for representing entities, attributes, and relationships.

135
Q

composite entity

A

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
Q

existence dependency

A

When the existence of one entity depends on the existence of another related entity. Represented by placing an E in the relationship diamond.

137
Q

weak entity

A

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
Q

cardinality

A

The number of times that must be included in a relationship. Maximum cardinality is “many.”

139
Q

optional role

A

The type of role played by an entity in a relationship with minimum cardinality of zero.

140
Q

mandatory role

A

The type of role played by an entity with a minimum cardinality of one.

*CIS 233 - Database Systems (2 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions