Brainscape's Knowledge GenomeTM

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


See full index

Microsoft MTA 98-364 > Database Administration Fundamentals > Flashcards

Database Administration Fundamentals Flashcards

1
Q

What is the UNIQUE constraint?

A

Specifically identifies which column should not contain duplicate values.

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

What is the CHECK constraint?

A

Allows the admin to limit the types of data a user can insert into that column of the database.

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

What is a default constraint?

A

Used to insert a default value into a column.

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

What is the NOT NULL constraint?

A

Requires that data is entered into the cell, it is not allowed to be blank.

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

What is the primary key?

A

This uniquely identifies each record in the db.

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

What is a foreign key?

A

This is a column in one table that points to the primary key in another table.

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

Why is it a bad idea to let a foreign key contain a NULL value?

A

Because it may be impossible to verify the constraints if a foreign key consists of two or more columns if one of them is NULL.

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

What is a self-reference?

A

When the foreign key refers to columns in the same table.

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

What is XQuery?

A

A query and functional programming language designed to query collections of XML data.

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

What is SQLCMD?

A

A command line application that comes with SQL and exposes the management features of SQL Server. It allows SQL queries to be written and executed from the command prompt.

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

What is TRANSACT-SQL

A

This is the primary means of programming and managing SQL Server. When you use an DBMS, like SSMS, to perform an action, it is using T-SQL commands in the background to do the work.

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

What are the three types of files in SQL?

A

MDF = Primary data files, NDF = Secondary data files and LDF = Log files

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

Database objects are divided into two categories:

A

Storage and Programmability

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

Tables created using the ________ statement are used to store data.

A

CREATE TABLE

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

Constraints are also referred to as ____ constraints.

A

Column

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

In order to use views, you must use the ______ T-SQL statement to show data from the tables.

A

SELECT

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

DDL influences _____, while _______ influences actual data stored in tables.

A

Database objects, DML

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

The MS database server that hosts relational databases is called _____

A

MS SQL Server

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

The core DDL statements are (6):

A

ADDUCT: ALTER, DROP, DELETE, USE, CREATE and TRUNCATE

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

The core DML statements are (5)

A

MIDUS: MERGE, INSERT, DELETE, UPDATE, SELECT

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

System views belong to the ______

A

sys schema

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

What are 6 constraint types?

A

Unique, Check, Default, Not Null, Primary Key, Foreign Key

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

A bit is the T-SQL integer data type that can take a ___ of 1, 0 or NULL.

A

value

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

A regular character uses ___ bytes of storage, whereas a unicode character requires ____ bytes.

A

one, two

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

When querying a database you can obtain faster results from properly ______ tables and views.

A

indexed

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

What’s the difference between time, datetime, datetime2, datetimeoffset and smalldatetime?

A

Time is the 24 hr clock, Datetime is accurate to .00333 seconds, datetime2 is accurate up to 100 nanoseconds, datetimeoffset includes daylight savings time and smalldatetime does not keep track of seconds.

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

What prefix must you have in front of a string to use Unicode?

A

N

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

What is the default length for the CAST function?

A

30

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

What is native auditing?

A

The process of extracting trails on a regular basis so they can be transferred to a designated security system where the database admins do not have access, this ensures a certain level of separation of duties and provides evidence that the audit trails were not modified.

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

What is a two-phase commit system?

A

A feature of a transaction processing system which enables DB’s to be returned to the pre-transaction state if some error condition occurs. All databases are updated or none of them are.

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

Name 4 types of decomposition

A

Top-down, Bottom-up, Inside-out, Mixed

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

What is the inside-out approach to db design?

A

A type of bottom-up approach, the inside-out method begins with identifying a few important concepts then proceeds outward radially.

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

What is the top-down approach?

A

When a schema is created through a series of successive refinements, starting with the first schema.

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

What is the With Execute Owner clause?

A

When creating a stored procedure, this can be used to allow the person running the SP to have the same permissions as the person who owns the SP. This is better than granting SELECT to the user.

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

What is abstraction?

A

A method of coding that enables a user to focus on the coding rather than the syntax for a specific database API, allowing them to use generic methods of access as long as they have the additional codes or libraries which fill in the blanks.

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

What is the correct way to select an avg of a column?

A

SELECT AVG(“column name”) FROM “table name”

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

What is a clustered index?

A

It consists of a root page, intermediate levels and leaf levels in a B-tree structure. Each row contains a valid key and a pointer. A clustered index forces the data in the table to be sorted in the order of the index. Each table can only have 1 clustered index.

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

What is a non-clustered index?

A

Same as clustered except the index does not physically rearrange the data.

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

What are 3 characteristics of a simple view?

A
  1. It does not have any usage of SQL group functions or grouping of data.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

Which DB design process allows you to create a data model independent of a specific DBMS?

A

Logical DB design

41
Q

What’s the difference between an INNER join and an OUTER join?

A

INNER will only produce matching rows from both tables while OUTER will join all rows from both tables whether they match or not.

42
Q

What is the goal of 1NF?

A

To minimize the inclusion (not prevent) of duplicate information

43
Q

What command do you use to invoke a stored procedure?

A

Execute

44
Q

If you have already defined a view on a specific table, then you add columns to that table, what must you do to the view?

A

Use the CREATE or REPLACE VIEW command to redefine it.

45
Q

What are three things true about views?

A
  1. If a view definition contains the DISTINCT keyword, rows cannot be deleted through the view.
46
Q

What is DB prototyping?

A

Building a working model of the DB system in order to suggest improvements or add new features.

47
Q

When is the best time to back up dynamic log files?

A

When the server is stopped.

48
Q

Define 1NF

A

When all columns in a table are atomic

49
Q

Define 2NF

A

2NF is when the table is in 1NF and all remaining columns depend on the primary key

50
Q

Define 3NF

A

When the table is in 2NF and none of the columns are transitively reliant on another.

51
Q

How do you adjust indexes to reduce fragmentation from page splits?

A

Set the fillfactor to 60.

52
Q

What is a fillfactor?

A

It specifies a percentage that indicates how much free space will be in the leaf level of each index page.

53
Q

How would you set fillfactor?

A

ALTER INDEX ALL ON dbo.OrderDetails REBUILD WITH (FILLFACTOR = 60);

54
Q

What would happen if you had a BETWEEN operator of BETWEEN ‘D’ and ‘F’

A

You would get all values for D, E but not F.

55
Q

What is the bottom up approach?

A

Breaking down the smaller components so that each describes a basic fragment.

56
Q

Which form of database design uses secondary storage media?

A

Physical database design

57
Q

What will be the output of the query?

A

A database will be created.

58
Q

When do you pick the DBMS?

A

Prior to the Logical design phase.

59
Q

Which of the following statements are true regarding the procedural data manipulation language?

A

It requires users to specify which data is needed and how to obtain it, it is a low-level DML, it requires users to know the data structure used in the db.

60
Q

What does the IDENTITY constraint do?

A

It’s used on the primary key to automatically start with 1 and auto-increment by 1.

61
Q

Database objects are divided into two categories:

A

Storage and Programmability

62
Q

What always returns a value but never updates data?

A

A function

63
Q

The core DDL statements are:

A

CREATE, ALTER and UPDATE

64
Q

The foreign key constraint is a ____ identifer.

A

Relationship

65
Q

Name 3 things which can be used to improve query performance

A

A primary key, a UNIQUE index and a CLUSTERED index

66
Q

T/F: A single INSERT statement can be used to add rows to multiple tables.

A

FALSE

67
Q

Can you change the IDENTITY constraint of an existing column with an ALTER statement?

A

No

68
Q

T/F: Null is a valid constraint

A

False, NULL is not a constraint

69
Q

Which 4 things always have a related data type?

A

Column, localvariable, expression and parameter.

70
Q

SQL server supports ____ conversions without using actual callout functions CAST or CONVERT

A

Implicit

71
Q

A regular character uses how much storage?

A

1 byte

72
Q

A unicode characters uses how much storage?

A

2 bytes

73
Q

For the CHAR data set, it is a _____ length and uses ___ bytes:

A

Fixed, N

74
Q

What is one thing to consider when creating a view?

A

Database performance

75
Q

How do you suppress the ‘(1 row affected)

A

SET NOCOUNT ON

76
Q

An ___ ____ is the same thing as a CROSS JOIN with a WHERE condition:

A

INNER JOIN

77
Q

What’s the most efficient way to delete all rows from a table?

A

TRUNCATE command

78
Q

How do you start a transaction?

A

Use BEGIN TRAN

79
Q

A ____ will combine the results of two or more queries into a resulting set that includes all the rows belonging to the query:

A

UNION

80
Q

If you are querying the same table for two different things you’d use a….

A

UNION

81
Q

A clustered index usually _____ performance when inserting data.

A

worsens, because it’s constantly sorting it. Improves it for retrieving it though

82
Q

What 2 things speeds up data retrieval?

A

Primary key constraints and Clustered indexes

83
Q

What 3 reasons should you consider using a clustered index?

A
  1. Columns contain a large number of distinct values
84
Q

Which normal form ensures that each attribute describes the entity?

A

2nf

85
Q

What command allows a Windows account to access SQL-Server?

A

CREATE LOGIN

86
Q

Any ___ permission will always override a GRANT permission.

A

DENY

87
Q

A ____ backup contains only the data that has changed since the last full backup.

A

Differential

88
Q

All users are automatically members of the ______ database role.

A

Public

89
Q

Use the _____ command to recover data that was accidentally deleted by a user.

A

Restore

90
Q

Name 3 levels of security supported by SQL Server

A

Server, Database and Table

91
Q

The sa account is only used in

A

mixed-mode

92
Q

The ___ role gives access to anything on the SQL server, while the ____ role gives full access to a specific database:

A

sysadmin, db_owner

93
Q

What language are triggers written in?

A

DML or DDL

94
Q

What happens if data is missing for a particular column when designing the INSERT SQL statement?

A

The INSERT statement uses the default value for the column.

95
Q

Name the two types of prototyping

A

Requirements and Evolutionary

96
Q

What steps should you take to create full-text searching?

A
  1. Create a full text catalog in the database
97
Q

What command would you use to track changes to a table?

A

ALTER TABLE tablename

98
Q

Application design involves 2 important activities:

A

transaction design and interface design

99
Q

What does degree refer to?

A

The number of columns.

Microsoft MTA 98-364 (1 decks)

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