MTA 98-364 Database Administration Fundamentals

MTA 98-364 Database Administration Fundamentals

memorize.aimemorize.ai (lvl 286)
Section 1

Preview this deck

What is the top-down approach?

Front

Star 0%
Star 0%
Star 0%
Star 0%
Star 0%

0.0

0 reviews

5
0
4
0
3
0
2
0
1
0

Active users

0

All-time users

0

Favorites

0

Last updated

2 years ago

Date created

Mar 1, 2020

Cards (99)

Section 1

(50 cards)

What is the top-down approach?

Front

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

Back

The core DDL statements are (6):

Front

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

Back

What is the With Execute Owner clause?

Front

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.

Back

What is the NOT NULL constraint?

Front

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

Back

Constraints are also referred to as ____ constraints.

Front

Column

Back

What is a two-phase commit system?

Front

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.

Back

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

Front

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.

Back

What is DB prototyping?

Front

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

Back

What is SQLCMD?

Front

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.

Back

The code DML statements are (5)

Front

MIDUS: MERGE, INSERT, DELETE, UPDATE, SELECT

Back

What is the inside-out approach to db design?

Front

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

Back

What is a default constraint?

Front

Used to insert a default value into a column.

Back

Define 3NF

Front

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

Back

What is the primary key?

Front

This uniquely identifies each record in the db.

Back

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

Front

indexed

Back

What is the goal of 1NF?

Front

To minimize the inclusion (not prevent) of duplicate information

Back

What are 6 constraint types?

Front

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

Back

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?

Front

Use the CREATE or REPLACE VIEW command to redefine it.

Back

What is the CHECK constraint?

Front

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

Back

What is abstraction?

Front

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.

Back

What is XQuery?

Front

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

Back

Name 4 types of decomposition

Front

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

Back

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

Front

SELECT AVG("column name") FROM "table name"

Back

What is a non-clustered index?

Front

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

Back

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

Front

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.

Back

What is the default length for the CAST function?

Front

30

Back

Define 2NF

Front

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

Back

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

Front

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

Back

What is native auditing?

Front

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.

Back

What are three things true about views?

Front

1. If a view definition contains the DISTINCT keyword, rows cannot be deleted through the view. 2. The WITH OBJECT IDENTIFIER clause is used to specify a top level (root) object view. 3. The OR REPLACE option is used to change the definition of a existing view without dropping and recreating.

Back

What command do you use to invoke a stored procedure?

Front

Execute

Back

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

Front

Database objects, DML

Back

Database objects are divided into two categories:

Front

Storage and Programmability

Back

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

Front

value

Back

What is a foreign key?

Front

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

Back

System views belong to the ______

Front

sys schema

Back

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

Front

Logical DB design

Back

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

Front

N

Back

The MS database server that hosts relational databases is called _____

Front

MS SQL Server

Back

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

Front

CREATE TABLE

Back

What is a clustered index?

Front

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.

Back

What is the UNIQUE constraint?

Front

Specifically identifies which column should not contain duplicate values.

Back

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

Front

one, two

Back

What are the three types of files in SQL?

Front

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

Back

What are 3 characteristics of a simple view?

Front

1. It does not have any usage of SQL group functions or grouping of data. 2. DML operations are allowed on the view 3. It fetches data from one database table only

Back

What is a self-reference?

Front

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

Back

Define 1NF

Front

When all columns in a table are atomic

Back

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

Front

When the server is stopped.

Back

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

Front

SELECT

Back

What is TRANSACT-SQL

Front

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

Back

Section 2

(49 cards)

What language are triggers written in?

Front

DML or DDL

Back

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

Front

UNION

Back

What 2 things speeds up data retrieval?

Front

Primary key constraints and Clustered indexes

Back

The foreign key constraint is a ____ identifer.

Front

Relationship

Back

What does degree refer to?

Front

The number of columns.

Back

Which 4 things always have a related data type?

Front

Column, localvariable, expression and parameter.

Back

A regular character uses how much storage?

Front

1 byte

Back

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

Front

No

Back

What is one thing to consider when creating a view?

Front

Database performance

Back

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

Front

False

Back

Any ___ permission will always override a GRANT permission.

Front

DENY

Back

What 3 reasons should you consider using a clustered index?

Front

1. Columns contain a large number of distinct values 2. Columns are accessed sequentially 3. Queries return large result sets

Back

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

Front

UNION

Back

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

Front

INNER JOIN

Back

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

Front

TRUNCATE command

Back

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

Front

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

Back

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

Front

Set the fillfactor to 60.

Back

Name 3 things which can be used to improve query performance

Front

A primary key, a UNIQUE index and a CLUSTERED index

Back

Application design involves 2 important activities:

Front

transaction design and interface design

Back

What always returns a value but never updates data?

Front

A function

Back

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

Front

ALTER TABLE tablename ENABLE CHANGE_TRACKING

Back

The core DDL statements are:

Front

CREATE, ALTER and UPDATE

Back

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

Front

The INSERT statement uses the default value for the column.

Back

A clustered index usually _____ performance when inserting data.

Front

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

Back

Which normal form ensures that each attribute describes the entity?

Front

2nf

Back

How would you set fillfactor?

Front

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

Back

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

Front

Differential

Back

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

Front

1. Create a full text catalog in the database 2. Create a full text index on the column in question

Back

The sa account is only used in

Front

mixed-mode

Back

All users are automatically members of the ______ database role.

Front

Public

Back

A unicode characters uses how much storage?

Front

2 bytes

Back

How do you suppress the '(1 row affected)

Front

SET NOCOUNT ON

Back

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

Front

Fixed, N

Back

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

Front

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.

Back

What is a fillfactor?

Front

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

Back

Mark works as a Database Designer for Reon Inc. He is assigned the task to create a database for the company. He issues the following query to create the database. CREATE DATABASE '24342' What will be the output of the query?

Front

A database will be created.

Back

Which form of database design uses secondary storage media?

Front

Physical database design

Back

T/F: Null is a valid constraint

Front

False, NULL is not a constraint

Back

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

Front

Restore

Back

Name 3 levels of security supported by SQL Server

Front

Server, Database and Table

Back

Name the two types of prototyping

Front

Requirements and Evolutionary

Back

When do you pick the DBMS?

Front

Prior to the Logical design phase.

Back

What does the IDENTITY constraint do?

Front

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

Back

How do you start a transaction?

Front

Use BEGIN TRAN

Back

Database objects are divide into two categories:

Front

Storage and Programmability

Back

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

Front

CREATE LOGIN

Back

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

Front

Implicit

Back

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

Front

sysadmin, db_owner

Back

What is the bottom up approach?

Front

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

Back