Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

Databases > INFO5052-Midterm > Flashcards

INFO5052-Midterm Flashcards

(74 cards)

Start Studying
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
What is 1st Normal?
Unique columns with atomic data
26
What is 2nd Normal?
Data is dependent on entire key
27
What is a candidate key?
Any column or columns that can uniquely identify a row of data
28
What is 3rd Normal?
No column is transitively related to the candidate key
29
What is a logical data model?
Describes required data without describing structures. Includes entities, attributes and relationships
30
What is an entity?
A thing that we want to store data about. Typically a noun
31
What is an attribute?
Properties of an entity. A column
32
What is cardinality?
Describes the number of entities on either side of a relationship (one to many, etc)
33
What is a physical data model?
Describes the objects that store the data
34
What is an ERD?
Entity Relationship Diagram
35
What is a lookup table?
Used for a dropdown list when a finite number of options for an attribute exist
36
What is meant by "sparsely populated column"?
The attribute is NULL for an overwhelming number of records
37
What is the best practice when a column is sparsely populated?
Separate into a new table
38
What is a primary key?
A unique, non-nullable column or group of columns
39
What is a foreign key?
Constrains the values in a foreign table based on existing values in a primary table
40
What CRUD functions is the foreign table constrained on?
Inserts and Updates
41
What CRUD functions is the primary table constrained on?
Deletes and Updates
42
Which table is considered the "child" table?
Foreign table
43
What side of a one-to-many relationship is the primary table?
The "one" side
44
What is a unique constraint?
Each value, including NULL, can only appear once in the table
45
Can one unique constraint hold many columns?
Yes, it means the combination of columns must be unique
46
What is the format for naming unique constraints?
AK_TableName_ColumnName[...n]
47
What does a default constraint help avoid?
NULL values
48
What is the naming convention for default constraints?
DF_TableName_ColumnName
49
What is an IDENTITY?
Auto-incrementing, always unique
50
What is the naming convention for identity?
INT IDENTITY (1, 1) (seed, increment)
51
What are two important details about identities?
1. Cannot be inserted into, so INSERT statements should omit this column 2. They are not reused
52
What is a check constraint?
Specifies a pattern, data must satisfy a condition
53
What is the naming convention of a check constraint?
CK_TableName
54
What is an index?
Lists of data sorted on a key
55
What is a scan?
When you evaluate each row of data in an unordered set
56
What is a seek?
Using an index to hone in on desired data
57
What is an execution plan?
Set of operations a database takes to execute a specific query
58
What is a clustered index?
Sorts and stores the entire row
59
What is typically the index key on a clustered index?
Primary Key
60
How many clustered indexes can a table have?
One or none
61
What is a heap?
A table without a clustered index
62
Which constraint is automatically indexed?
Unique
63
What is a covering index?
Contains all requested columns for a given query
64
What is a composite index?
An index with more than one index key
65
What is the naming convention for an index?
IX_TableName_ColumnName
66
How many non-clustered indexes can a table have?
0-999
67
How are execution plans read?
From right to left
68
What is the operator cost in an execution plan?
Combination of I/O cost and CPU cost
69
What is the I/O cost?
Estimate of the number of records that will be read with a given query
70
Why use indexes?
Makes those queries much faster
71
What are the costs of indexes?
Slows down insert, update and delete operations
72
Why do identities make good primary keys?
1. Small 2. Unique 3. Simple 4. Not reused 5. Auto increment
73
What is a surrogate key?
Key created by the system that is meaningless outside of the context of the system
74
Why should foreign keys be indexed?
Foreign keys are often part of queries

Databases (4 decks)

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