Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

Microsoft MTA 98-364 > Database Administration Fundamentals > Flashcards

Database Administration Fundamentals Flashcards

(99 cards)

Start Studying
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
When querying a database you can obtain faster results from properly ______ tables and views.
indexed
26
What's the difference between time, datetime, datetime2, datetimeoffset and smalldatetime?
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.
27
What prefix must you have in front of a string to use Unicode?
N
28
What is the default length for the CAST function?
30
29
What is native auditing?
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.
30
What is a two-phase commit system?
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.
31
Name 4 types of decomposition
Top-down, Bottom-up, Inside-out, Mixed
32
What is the inside-out approach to db design?
A type of bottom-up approach, the inside-out method begins with identifying a few important concepts then proceeds outward radially.
33
What is the top-down approach?
When a schema is created through a series of successive refinements, starting with the first schema.
34
What is the With Execute Owner clause?
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.
35
What is abstraction?
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.
36
What is the correct way to select an avg of a column?
SELECT AVG("column name") FROM "table name"
37
What is a clustered index?
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.
38
What is a non-clustered index?
Same as clustered except the index does not physically rearrange the data.
39
What are 3 characteristics of a simple view?
1. It does not have any usage of SQL group functions or grouping of data.
40
Which DB design process allows you to create a data model independent of a specific DBMS?
Logical DB design
41
What's the difference between an INNER join and an OUTER join?
INNER will only produce matching rows from both tables while OUTER will join all rows from both tables whether they match or not.
42
What is the goal of 1NF?
To minimize the inclusion (not prevent) of duplicate information
43
What command do you use to invoke a stored procedure?
Execute
44
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?
Use the CREATE or REPLACE VIEW command to redefine it.
45
What are three things true about views?
1. If a view definition contains the DISTINCT keyword, rows cannot be deleted through the view.
46
What is DB prototyping?
Building a working model of the DB system in order to suggest improvements or add new features.
47
When is the best time to back up dynamic log files?
When the server is stopped.
48
Define 1NF
When all columns in a table are atomic
49
Define 2NF
2NF is when the table is in 1NF and all remaining columns depend on the primary key
50
Define 3NF
When the table is in 2NF and none of the columns are transitively reliant on another.
51
How do you adjust indexes to reduce fragmentation from page splits?
Set the fillfactor to 60.
52
What is a fillfactor?
It specifies a percentage that indicates how much free space will be in the leaf level of each index page.
53
How would you set fillfactor?
ALTER INDEX ALL ON dbo.OrderDetails REBUILD WITH (FILLFACTOR = 60);
54
What would happen if you had a BETWEEN operator of BETWEEN 'D' and 'F'
You would get all values for D, E but not F.
55
What is the bottom up approach?
Breaking down the smaller components so that each describes a basic fragment.
56
Which form of database design uses secondary storage media?
Physical database design
57
What will be the output of the query?
A database will be created.
58
When do you pick the DBMS?
Prior to the Logical design phase.
59
Which of the following statements are true regarding the procedural data manipulation language?
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
What does the IDENTITY constraint do?
It's used on the primary key to automatically start with 1 and auto-increment by 1.
61
Database objects are divided into two categories:
Storage and Programmability
62
What always returns a value but never updates data?
A function
63
The core DDL statements are:
CREATE, ALTER and UPDATE
64
The foreign key constraint is a ____ identifer.
Relationship
65
Name 3 things which can be used to improve query performance
A primary key, a UNIQUE index and a CLUSTERED index
66
T/F: A single INSERT statement can be used to add rows to multiple tables.
FALSE
67
Can you change the IDENTITY constraint of an existing column with an ALTER statement?
No
68
T/F: Null is a valid constraint
False, NULL is not a constraint
69
Which 4 things always have a related data type?
Column, localvariable, expression and parameter.
70
SQL server supports ____ conversions without using actual callout functions CAST or CONVERT
Implicit
71
A regular character uses how much storage?
1 byte
72
A unicode characters uses how much storage?
2 bytes
73
For the CHAR data set, it is a _____ length and uses ___ bytes:
Fixed, N
74
What is one thing to consider when creating a view?
Database performance
75
How do you suppress the '(1 row affected)
SET NOCOUNT ON
76
An ___ ____ is the same thing as a CROSS JOIN with a WHERE condition:
INNER JOIN
77
What's the most efficient way to delete all rows from a table?
TRUNCATE command
78
How do you start a transaction?
Use BEGIN TRAN
79
A ____ will combine the results of two or more queries into a resulting set that includes all the rows belonging to the query:
UNION
80
If you are querying the same table for two different things you'd use a....
UNION
81
A clustered index usually _____ performance when inserting data.
worsens, because it's constantly sorting it. Improves it for retrieving it though
82
What 2 things speeds up data retrieval?
Primary key constraints and Clustered indexes
83
What 3 reasons should you consider using a clustered index?
1. Columns contain a large number of distinct values
84
Which normal form ensures that each attribute describes the entity?
2nf
85
What command allows a Windows account to access SQL-Server?
CREATE LOGIN
86
Any ___ permission will always override a GRANT permission.
DENY
87
A ____ backup contains only the data that has changed since the last full backup.
Differential
88
All users are automatically members of the ______ database role.
Public
89
Use the _____ command to recover data that was accidentally deleted by a user.
Restore
90
Name 3 levels of security supported by SQL Server
Server, Database and Table
91
The sa account is only used in
mixed-mode
92
The ___ role gives access to anything on the SQL server, while the ____ role gives full access to a specific database:
sysadmin, db_owner
93
What language are triggers written in?
DML or DDL
94
What happens if data is missing for a particular column when designing the INSERT SQL statement?
The INSERT statement uses the default value for the column.
95
Name the two types of prototyping
Requirements and Evolutionary
96
What steps should you take to create full-text searching?
1. Create a full text catalog in the database
97
What command would you use to track changes to a table?
ALTER TABLE tablename
98
Application design involves 2 important activities:
transaction design and interface design
99
What does degree refer to?
The number of columns.

Microsoft MTA 98-364 (1 decks)

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