Brainscape's Knowledge GenomeTM

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


See full index

Databases > INFO5052-Midterm > Flashcards

INFO5052-Midterm Flashcards

1
Q

What is SQL Server?

A

The database engine

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

What program is used to query and maintain databases using SQL Server?

A

SQL Server Management Studio (SSMS)

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

What two files does SSMS create by default when you create a new database?

A
  1. Data File
  2. Log file
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What is saved in the data file?

A

The schema and data for a given database

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

What is saved in a log file?

A

Transaction data required to run database. Database cannot be brought online without a log file.

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

Which type of file can be split across multiple files?

A

Data file

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

What is collation?

A

Defines how characters are interpreted

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

What is the recovery model?

A

Affects how long transactions are held in the transaction log

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

What is DDL?

A

Data Definition Language

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

What is DML?

A

Data Manipulation Language

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

What are two best practices with DDL?

A
  1. Always use semi-colons
  2. Use explicit schemas
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is NULL equal to?

A

Unknown

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

When working with complex joins, which type of join should be performed first? (INNER, OUTER)

A

Inner joins first

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

What does TRUNCATE do?

A

Removes all rows from a table without logging the individual row deletions.

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

Which is faster, TRUNCATE or DELETE?

A

TRUNCATE

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

Can a TRUNCATE be rolled back?

A

Yes

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

What are two “magic tables”?

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

Which four commands can make use of magic tables?

A
  1. INSERT
  2. UPDATE
  3. DELETE
  4. MERGE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

What is an expression?

A

Anything that can be evaluated to return a single value

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

How do you return the magic tables?

A

OUTPUT inserted.ColumnName
OUTPUT deleted.ColumnName

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

What is a OLTP?

A

Online Transaction Processing Databases

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

What are the characteristics of an OLTP?

A
  • Large number of users
  • High volume of transactions
  • Users have write access through an application
  • Small amount of data
  • Operational data
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What is the purpose of normalization?

A

Data integrity

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

What is the preferred level of normalization?

A

3rd Normal

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

What is 1st Normal?

A

Unique columns with atomic data

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

What is 2nd Normal?

A

Data is dependent on entire key

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

What is a candidate key?

A

Any column or columns that can uniquely identify a row of data

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

What is 3rd Normal?

A

No column is transitively related to the candidate key

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

What is a logical data model?

A

Describes required data without describing structures. Includes entities, attributes and relationships

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

What is an entity?

A

A thing that we want to store data about. Typically a noun

31
Q

What is an attribute?

A

Properties of an entity. A column

32
Q

What is cardinality?

A

Describes the number of entities on either side of a relationship (one to many, etc)

33
Q

What is a physical data model?

A

Describes the objects that store the data

34
Q

What is an ERD?

A

Entity Relationship Diagram

35
Q

What is a lookup table?

A

Used for a dropdown list when a finite number of options for an attribute exist

36
Q

What is meant by “sparsely populated column”?

A

The attribute is NULL for an overwhelming number of records

37
Q

What is the best practice when a column is sparsely populated?

A

Separate into a new table

38
Q

What is a primary key?

A

A unique, non-nullable column or group of columns

39
Q

What is a foreign key?

A

Constrains the values in a foreign table based on existing values in a primary table

40
Q

What CRUD functions is the foreign table constrained on?

A

Inserts and Updates

41
Q

What CRUD functions is the primary table constrained on?

A

Deletes and Updates

42
Q

Which table is considered the “child” table?

A

Foreign table

43
Q

What side of a one-to-many relationship is the primary table?

A

The “one” side

44
Q

What is a unique constraint?

A

Each value, including NULL, can only appear once in the table

45
Q

Can one unique constraint hold many columns?

A

Yes, it means the combination of columns must be unique

46
Q

What is the format for naming unique constraints?

A

AK_TableName_ColumnName[…n]

47
Q

What does a default constraint help avoid?

A

NULL values

48
Q

What is the naming convention for default constraints?

A

DF_TableName_ColumnName

49
Q

What is an IDENTITY?

A

Auto-incrementing, always unique

50
Q

What is the naming convention for identity?

A

INT IDENTITY (1, 1)
(seed, increment)

51
Q

What are two important details about identities?

A
  1. Cannot be inserted into, so INSERT statements should omit this column
  2. They are not reused
52
Q

What is a check constraint?

A

Specifies a pattern, data must satisfy a condition

53
Q

What is the naming convention of a check constraint?

A

CK_TableName

54
Q

What is an index?

A

Lists of data sorted on a key

55
Q

What is a scan?

A

When you evaluate each row of data in an unordered set

56
Q

What is a seek?

A

Using an index to hone in on desired data

57
Q

What is an execution plan?

A

Set of operations a database takes to execute a specific query

58
Q

What is a clustered index?

A

Sorts and stores the entire row

59
Q

What is typically the index key on a clustered index?

A

Primary Key

60
Q

How many clustered indexes can a table have?

A

One or none

61
Q

What is a heap?

A

A table without a clustered index

62
Q

Which constraint is automatically indexed?

A

Unique

63
Q

What is a covering index?

A

Contains all requested columns for a given query

64
Q

What is a composite index?

A

An index with more than one index key

65
Q

What is the naming convention for an index?

A

IX_TableName_ColumnName

66
Q

How many non-clustered indexes can a table have?

A

0-999

67
Q

How are execution plans read?

A

From right to left

68
Q

What is the operator cost in an execution plan?

A

Combination of I/O cost and CPU cost

69
Q

What is the I/O cost?

A

Estimate of the number of records that will be read with a given query

70
Q

Why use indexes?

A

Makes those queries much faster

71
Q

What are the costs of indexes?

A

Slows down insert, update and delete operations

72
Q

Why do identities make good primary keys?

A
  1. Small
  2. Unique
  3. Simple
  4. Not reused
  5. Auto increment
73
Q

What is a surrogate key?

A

Key created by the system that is meaningless outside of the context of the system

74
Q

Why should foreign keys be indexed?

A

Foreign keys are often part of queries

Databases (4 decks)

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