Brainscape's Knowledge GenomeTM

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


See full index

Advanced Databases > MIDTERM > Flashcards

MIDTERM Flashcards

1
Q

What is a database data file?

A

Contains the schema and data for a database. A database cannot exist without one. A database may be split across multiple data files.

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

What is a transaction log file?

A

Contains transaction data to run the database. The database can’t be brought online without one. Can be used to restore data.

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

What is DDL?

A

Statements that affect the database schema. Create, Alter or Drop objects such as tables, constraints, views, indexes.

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

What is DML?

A

Statements affect data only. Used to CRUD Data. Create, read, update, delete.

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

How do ANSI nulls behave?

A

1 = NULL Does not return false, NULL = NULL does not return true.

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

What are table aliases? Benefits of using them?

A

An acronym of a table. Ex: TableA a. Easier to type out, Makes it clearer.

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

Difference between Delete and Truncate?

A

Truncates

  • Do not log row data
  • much faster
  • only work on entire table, so no Where clause
  • Resets identities
  • statements can be rolled back
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What is an OLTP?

A

Online Transaction Processing

  • High volume of users performing large amount of transactions on a small amount of data.
  • Queries include both read and write operations
  • Must maintain data integrity
  • Must be fault tolerant
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Benefits of Normalization?

A
  • Small tables
  • High resistance to data inconsistency
  • Low or no redundancy
  • Improved performance on small targeted queries
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

1st Normal Form?

A
  • Data must be Atomic
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

2nd Normal Form?

A
  • Data for each table must be dependent on the entire key
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

3rd Normal Form?

A
  • No column may be transitively related to the candidate keys
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What is a candidate key?

A
  • Any attribute that might be used to uniquely identify an entity
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is a logical data model?

A
  • A model where we capture entities, attributes and relationships
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is an entity?

A
  • A thing we want to store information about. Typically nouns (Student, Class, Address).
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is an attribute?

A
  • Properties of an entity we want to store. They describe some aspect of the entity (Birthdate, Course description, Postal code)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What is a relationship?

A
  • The ways entities are related to each other.
18
Q

What is meant by cardinality?

A
  • The number of entities that may be related to each other on either side of the relationship.
19
Q

What is a physical data model?

A
  • Helps visualize database by showing keys, constraints, indexes and triggers.
20
Q

What is a primary key constraint?

A
  • It’s a constraint because it limits the data to unique not nullable data
21
Q

What is a Foreign key constraint?

A
  • Prevents invalid data from being inserted into the fk column, because it has to be one of the values contained in the table it points to.
22
Q

What is a Unique constraint?

A
  • Specifies that each value for a column may only appear once in a table
23
Q

What is a Default Constraint?

A
  • Allows us to specify the default value for a column when it’s not specified.
24
Q

What is an Identity?

A

An auto-incrementing column in a table. It will always be populated and unique.

25
Q

What is a check constraint?

A
  • Used to specify a pattern that defines valid data for a table. Data being added to a table must satisfy the condition in a check constraint.
26
Q

What is an index?

A
  • Lists of data that are sorted on a key. By sorting the data, we can find info much faster.
27
Q

What is an index key?

A
  • The column or columns that the index is sorted on.
28
Q

What is a clustered index?

A
  • Clustered indexes sort and store the entire data rows. Each table may have a max of one clustered index
29
Q

What is a non-clustered index?

A
  • A structure sorted on an index key and contains a row locator that points to records in the table the index is associated with.
30
Q

What is a heap?

A
  • A table with no clustered index. Rows in the table are unordered.
31
Q

What are some costs of indexing?

A
  • Take space
  • Need to be updated
  • Need maintenance
  • Scans cheaper than seek on small tables
32
Q

Difference between scan and seek?

A
  • Scan: All data in the table is read

- Seek: index is used to only read specific sets of data in a table. Much faster

33
Q

What is an execution/query plan?

A

-A set of operations that a DBMS executes to complete a set query

34
Q

When are indexes automatically created?

A
  • When you create a unique constraint.
35
Q

What is a covering index?

A
  • An index that contains all requested columns for a query
36
Q

What is a composite index?

A
  • Any index with more than one index key column
37
Q

When can the columns of a composite index be used in a seek?

A
  • Can be used as long as the preceding columns are also included.
38
Q

Why is it a good idea to use an identity or primary key?

A
  • Because we create a surrogate key. A key generated solely for the purpose of identifying records in a table.
39
Q

What is a surrogate key?

A
  • A key created by the system to represent a row. Meaningless outside the context of a system.
40
Q

Why do we index foreign keys?

A
  • Because they are frequently used to reference data in a table. It’s how we find data in a child table by its parent.

Advanced Databases (1 decks)

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