Brainscape's Knowledge GenomeTM

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


See full index

MIDTERM REVIEWER > CC105 > Flashcards

CC105 Flashcards

1
Q

translate the logical description of data into the technical specifications for storing and retrieving data.

A

Purpose

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

create a design for storing data that will provide adequate performance and insure database integrity, security and recoverability.

A

Goal

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

smallest unit of data in database.

A

Field

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

Field design

A

 Choosing data type.
 Coding, compression, encryption.
 Controlling data integrity.

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

Choosing Data Types

A

 CHAR
 VARCHAR2
 LONG
 NUMBER
 DATE
 BLOB

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

fixed-length character.

A

CHAR

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

variable-length character (memo).

A

VARCHAR2

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

large number.

A

LONG

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

positive/negative number

A

NUMBER

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

actual date.

A

DATE

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

binary large object (good for graphics, sound clips, etc.).

A

BLOB

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

Field Data Integrity

A

 Default value
 Range control
 Null value control
 Referential integrity

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

assumed value if no explicit value.

A

Default value

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

allowable value limitations (constraints or validation rules).

A

Range control

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

allowing or prohibiting empty fields.

A

Null value control

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

range control (and null value allowances) for foreign-key to primary key match-ups.

A

Referential integrity

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q
  • Substitute an estimate of the missing value (e.g. using a formula).
  • Construct a report listing missing values.
A

Handling Missing Data

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

A group of fields stored in adjacent memory locations and retrieved together as a unit.

A

Physical Record

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

The amount of data read or written in one I/O operation.

A

Page

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

The number of physical records per page.

A

Blocking Factor

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

Transforming normalized relations into un-normalized physical record specifications

A

Denormalization

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

Common denormalization opportunities.

A

o One-to-one relationship.
o Many-to-many relationship with attributes.
o Reference data (1:N relationship where 1-side has data not used in any other relationship).

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

often correspond with User Schemas (user views)

A

Partitions

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q
  • Distributing the rows of a table into several separate files.
  • Useful for situations where different users need access to different rows.
A

Horizontal Partitioning

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

Three types of Horizontal Partitioning

A

Key Range Partitioning,
Hash Partitioning,
Composite Partitioning.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q
  • Distributing the columns of a table into several separate files.
  • Useful for situations where different users need access to different columns.
A

Vertical Partitioning

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

Advantages of Partitioning:

A

 Records used together are grouped together.
 Each partition can be optimized for performance.
 Security, recovery.
 Partitions stored on different disks: contention.
 Take advantage of parallel processing capability.

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

Disadvantages of Partitioning:

A

 Slow retrievals across partitions.
 Complexity.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q
  • Purposely storing the same data in multiple locations of the database.
  • Improves performance by allowing multiple users to access the same data at the same time with minimum
    contention.
A

Data Replication

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

A named portion of secondary memory allocated for the purpose of storing physical records.

A

Physical File

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

 Constructs to link two pieces of data:

A

o Sequential storage.
o Pointers.

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

How the files are arranged on the disk.

A

File Organization

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

How the data can be retrieved based on the file organization.

A

Access Method

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

Records of the file are stored in sequence by the primary key field values

A

Sequential file organization

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

a separate table that contains organization of records for quick retrieval.

A

Index

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

are automatically indexed.

A

Primary keys

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

has a CREATE INDEX operation

A

Oracle

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

allows indexes to be created for most field types.

A

MS ACCESS

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

Indexing approaches:

A

o B-tree index.
o Bitmap index.
o Hash Index.
o Join Index

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

Leaves of the tree are all at same level  consistent access time.

A

B-tree index

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

saves on space requirements

A

Bitmap

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

possible values of the attribute.

A

Rows

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

table rows.

A

Columns

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

indicates whether the attribute of a row has the values

A

Bit

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
45
Q
  • In some relational DBMSs, related records from different tables can be stored together in the same disk area.
  • Useful for improving performance of join operations.
A

Clustering Files

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

Rules for Using Indexes

A
  1. Use on larger tables.
  2. Index the primary key of each table.
  3. Index search fields (fields frequently in WHERE clause).
  4. Fields in SQL ORDER BY and GROUP BY commands.
  5. When there are >100 values but not when there are.
  6. DBMS may have limit on number of indexes per table and number of bytes per indexed field(s).
  7. Null values will not be referenced from an index.
  8. Use indexes heavily for non-volatile databases; limit the use of indexes for volatile databases.
    Why? Because modifications (e.g. inserts, deletes) require updates to occur in index files.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
47
Q

A set of disk drives that appear to the user to be a single disk drive.

A

RAID

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

RAID stands for?

A

Redundant Array of Inexpensive Disks.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
49
Q
  • Maximized parallelism.
  • No redundancy.
A

Raid 0

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
50
Q
  • Redundant data – fault tolerant.
  • Most common form.
A

Raid 1

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q
  • No redundancy.
  • One record spans across data disks.
A

Raid 2

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q
  • Error correction in one disk.
  • Record spans multiple data disks (more than RAID2).
A

Raid 3

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
53
Q
  • Error correction in one disk.
  • Multiple records per stripe.
A

Raid 4

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
54
Q
  • Rotating parity array.
  • Error correction takes place in same disks as data storage.
A

Raid 5

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

Query Optimization

A

 Parallel Query Processing.
 Override Automatic Query Optimization.
 Data Block Size – Performance tradeoffs:
 Balancing I/O across Disk Controllers.
 Wise use of indexes.
 Compatible data types.
 Simple queries.
 Avoid query nesting.
 Temporary tables for query groups.
 Select only needed columns.
 No sort without index

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

Data Block Size – Performance tradeoffs:

A

o Block contention.
o Random vs. sequential row access speed.
o Row size.
o Overhead.

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

SQL stands for

A

Structured Query Language.

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

The standard for relational database management systems (RDBMS).

A

SQL

59
Q
  • Specify syntax/semantics for data definition and manipulation.
  • Define data structures.
A

SQL-92 Standard – Purpose:

60
Q

Benefits of a Standardized Relational Language

A

Reduced training costs.
Productivity.
Application portability.
Application longevity.
Reduced dependence on a single vendor.
Cross-system communication.

61
Q

SQL Environment

A

Catalog
Schema
Data Definition Language (DDL):
Data Manipulation Language (DML)
Data Control Language (DCL)

62
Q

a set of schemas that constitute the description of a database.

A

Catalog

63
Q

The structure that contains descriptions of objects created by a user (base tables, views, constraints).

A

Schema

64
Q

Commands that define a database, including creating, altering, and dropping tables and establishing
constraints

A

Data Definition Language (DDL):

65
Q

Commands that maintain and query a database.

A

Data Manipulation Language (DML)

66
Q

Commands that control a database, including administering privileges and committing data

A

Data Control Language (DCL)

67
Q

SQL Data types (from Oracle8)

A

String types

68
Q

String types

A

 CHAR(n)
 VARCHAR2(n)
 LONG

69
Q

fixed-length character data, n characters long Maximum length = 2000 bytes.

A

CHAR(n)

70
Q

variable length character data, maximum 4000 bytes.

A

VARCHAR2(n)

71
Q

variable-length character data, up to 4GB. Maximum 1 per table.

A

LONG

72
Q

Numeric types

A

 NUMBER(p,q)
 INTEGER(p)
 FLOAT(p)

73
Q

general purpose numeric data type.

A

NUMBER(p,q)

74
Q

signed integer, p digits wide.

A

INTEGER(p)

75
Q

floating point in scientific notation with p binary digits precision.

A

FLOAT(p)

76
Q

fixed-length date/time in dd-mm-yy form.

A

DATE

77
Q

Major CREATE statements:

A

 CREATE SCHEMA
 CREATE TABLE
 CREATE VIEW

78
Q

defines a portion of the database owned by a particular user.

A

CREATE SCHEMA

79
Q

defines a table and its columns.

A

CREATE TABLE

80
Q

defines a logical table from one or more views.

A

CREATE VIEW

81
Q

Other CREATE statements:

A

CHARACTER SET
COLLATION
TRANSLATION
ASSERTION
DOMAIN.

82
Q

Steps in table creation:

A

 Identify data types for attributes.
 Identify columns that can and cannot be null.
 Identify columns that must be unique (candidate keys).
 Identify primary key - foreign key mates.
 Determine default values.
 Identify constraints on columns (domain specifications).
 Create the table and associated indexes.

83
Q

provide users-controlled access to tables.

A

Views

84
Q

Advantages of views:

A

 Simplify query commands.
 Provide data security.
 Enhance programming productivity.

85
Q

A table containing the raw data.

A

Base Table

86
Q
  • A “virtual table” created dynamically upon request by a user.
  • No data actually stored; instead data from base table made available to user.
A

Dynamic View

87
Q
  • Copy or replication of data.
  • Data actually stored.
A

Materialized View

88
Q

works only for updateable views and prevents updates that would create rows not included in the view.

A

CHECK_OPTION

89
Q

constraint that ensures that foreign key values of a table must match primary key values of a related table in 1:M relationships.

A

Referential integrity

90
Q

Restricting:

A

 Deletes of primary records.
 Updates of primary records.
 Inserts of dependent records.

91
Q

statement allows you to change column specifications:

A

ALTER TABLE
- ALTER TABLE CUSTOMER_T ADD (TYPE VARCHAR(2))

92
Q

statement allows you to remove tables from your schema:

A

DROP TABLE
- DROP TABLE CUSTOMER_T

93
Q
  • Control processing/storage efficiency
  • Creating indexes
A

Schema

94
Q

Adds data to a table.

A

Insert Statement

95
Q

Removes rows from a table

A

Delete Statement

96
Q

Inserting into a table.

A

 INSERT INTO CUSTOMER_T VALUES (001, ‘CONTEMPORARY Casuals’, 1355 S. Himes Blvd.’, ‘Gainesville’, ‘FL’, 32601);

97
Q

Inserting a record that has some null attributes requires identifying the fields that actually get data.

A

INSERT INTO PRODUCT_T (PRODUCT_ID, PRODUCT_DESCRIPTION,PRODUCT_FINISH, STANDARD_PRICE, PRODUCT_ON_HAND) VALUES (1, ‘End Table’, ‘Cherry’, 175, 8);

98
Q

Inserting from another table

A

INSERT INTO CA_CUSTOMER_T SELECT * FROM CUSTOMER_T WHERE STATE = ‘CA’;

99
Q

Delete certain rows

A

DELETE FROM CUSTOMER_T WHERE STATE = ‘HI’;

100
Q

Delete all rows

A

DELETE FROM CUSTOMER_T;

101
Q

Modifies data in existing rows

A

Update Statement

102
Q

Update Statement

A

UPDATE PRODUCT_T SET UNIT_PRICE = 775 WHERE PRODUCT_ID = 7;

103
Q

Used for queries on single or multiple tables.

A

SELECT Statement

104
Q

Clauses of the SELECT statement:

A

 SELECT
 FROM
 WHERE
 GROUP BY
 HAVING
 ORDER BY

105
Q

List the columns (and expressions) that should be returned from the query.

A

SELECT

106
Q

Indicate the table(s) or view(s) from which data will be obtained.

A

FROM

107
Q

Indicate the conditions under which a row will be included in the result.

A

WHERE

108
Q

Indicate categorization of results.

A

GROUP BY

109
Q

Indicate the conditions under which a category (group) will be included.

A

HAVING

110
Q

Sorts the result according to specified criteria.

A

ORDER BY

111
Q

is an alternative column or table name

A

Alias

112
Q

LIKE operator allows you to compare strings using wildcards.

A

LIKE operator

113
Q

for customizing conditions in WHERE clause.

A

AND, OR, and NOT Operators

114
Q

more efficient than separate OR conditions.

A

IN operator

115
Q

single value returned from SQL query with aggregate function. -

A

Scalar aggregate:

116
Q

multiple values returned from SQL query with aggregate function (via GROUP BY).

A

Vector aggregate

117
Q

a relational operation that causes two or more tables with a common domain to be combined into a single table or view.

A

Join

118
Q

a join in which the joining condition is based on equality between values in the common columns; common columns appear redundantly in the result table.

A

Equi-join

119
Q

an equi-join in which one of the duplicate columns is eliminated in the result table.

A

Natural join

120
Q

a join in which rows that do not have matching values in common columns are nonetheless included in the result table (as opposed to inner join, in which rows must have matching values in order to appear in the result table).

A

Outer join

121
Q

includes all columns from each table in the join, and an instance for each row of each table.

A

Union join

122
Q

placing an inner query (SELECT statement) inside an outer query.

A

Subquery

123
Q

Subqueries can be:

A

 Non correlated
 Correlated

124
Q

execute once for the entire outer query.

A

Non correlated

125
Q

execute once for each row returned by the outer query.

A

Correlated

126
Q

A discrete unit of work that must be completely processed or not processed at all.

A

Transaction

127
Q

SQL commands for transactions. - - -

A

BEGIN TRANSACTION/END TRANSACTION.
COMMIT.
ROLLBACK.

128
Q

Marks boundaries of a transaction.

A

BEGIN TRANSACTION/END TRANSACTION.

129
Q

Makes all updates permanent

A

COMMIT.

130
Q

Cancels updates since the last COMMIT.

A

ROLLBACK.

131
Q
  • System tables that store metadata.
  • Users usually can view some of these tables.
A

Data Dictionary Facilities

132
Q

Examples in Oracle8i. -

A

DBA_TABLES
DBA_TABLES DBA_CONSTRAINTS
DBA_USERS
DBA_TAB_PRIVS

133
Q

descriptions of tables. - - -

A

DBA_TABLES

134
Q

description of constraint.

A

DBA_TABLES DBA_CONSTRAINTS

135
Q

information about the users of the system.

A

DBA_USERS

136
Q

descriptions of grants on objects in the database.

A

DBA_TAB_PRIVS

137
Q

Subclasses of standard types or an object type.

A

User-defined data types (UDT).

138
Q

Capability to create and drop code modules.

A

Persistent Stored Modules (SQL/PSM).

139
Q

Program modules that execute on demand.

A

Routines

140
Q

routines that return values and take input parameters.

A

Functions

141
Q

routines that do not return values and can take input or output parameters.

A

Procedures

142
Q

Routines that execute in response to a database event (INSERT, UPDATE, or DELETE).

A

Triggers

143
Q

Including hard-coded SQL statements in a program written in another language such as C or Java.

A

Embedded SQL

144
Q

Ability for an application program to generate SQL code on the fly, as the application is running.

A

Dynamic SQL

MIDTERM REVIEWER (7 decks)

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