question

database (db)

answer

an organized collection of data, typically stored in electronic format. Allows you to input, organize, and retrieve data quickly. Traditionally are organized by fields, records, and files.

question

database query

answer

an inquiry into the database that returns info back from the database.

question

index

answer

a data structure that improves the speed of data retrieval operations on a database table. Disadvantage: need to be created and updated, which requires processing resources and takes up disk space.

question

database servers

answer

servers that are entirely optimized to serve only the purposes of the database itself, usually using advanced hardware that can handle the high processing needs of the database.

question

Primary data files

answer

.mdf extension, are the first files created in a db and can contain user-defined objects, such as tables and views, as well as system tables that SQL Server requires for keeping track of the db.

question

Secondary data files

answer

.ndf extension, created on separate physical hard disks when db files become too large on first hard disk

question

Transaction log file

answer

.ldf extension, do not contain objects such as tables or views

question

database management system (DBMS)

answer

a collection of programs that enables you to enter, organize, and select data in a db. 3 types: flat-type, hierarchical, and relational.

question

Flat-type db

answer

simplistic in design, most commonly used in plain-text formats. Hold on record per line, so makes access, performance, and queries very quick. .txt or .ini file. considered flat because they are 2D tables consisting of rows and columns. Column=field, row=record.

question

hierarchical db

answer

similar to tree structure, each "parent" table can have multiple "children", but each child can have only one parent. If parent and child tables are linked together, the db's tables will start to form a tree structure in which each parent may have multiple child tables and each child table may in urn have children of its own, yet no single child table will have more than one parent.

question

relational db

answer

similar to hierarchical db in that data is stored in tables and any new info is automatically added into the table without the need to reorganize the table itself. a table in a relational db can have multiple parents.

question

3 fundamental characteristics of a db:

answer

*they are designed to store billions of rows of data *they are limited o the computer's available hard disk space *they are optimized to use all a computer's available memory to improve performance

question

understanding calculated values

answer

essentially a value that results from the performance of some sort of calculation or formula on a specified input value.

question

understanding relational database concepts

answer

a relational db helps organize all the data from the various rows and columns of each table, each column corresponds to one specific type of information you want to store in the db.

question

Database objects are inherently divided into two broad categories:

answer

storage and programmability

question

Constraints are:

answer

limitations on rules placed on a field or column to ensure that data that is considered invalid is not entered. 6 types of constraints: unique, check, default, not null, primary key, and foreign key

question

Unique constraint

answer

allows the db admin to specifically identify which column should not contain duplicate values

question

Check constraint

answer

allows the admin to limit the types of data a user can insert into the db

question

default constraint

answer

used to insert a default value into a column. If no other value is specified, the default value will be added to all new records.

question

not null constraint

answer

ensures that data is entered into a cell, the cell cannot be blank. Also means that you cannot insert a new record or update a record without adding a value to this field.

question

primary key constraint

answer

uniquely identifies each record in a db table. The primary key must contain unique values and it cannot contain NULL values. Each table should have a primary key, and each table can have only ONE primary key.

question

foreign key constraint

answer

points to a primary key in another table. Columns marked a foreign keys can contain null values (this is not a desirable standard practice, because it may be impossible to verify the constraints if a foreign key consists of two or more columns and contains null values). Integrity of data cannot be guaranteed.

question

self-reference

answer

when a foreign key constraint to reference columns is in the same table.

question

self-join

answer

when a self-reference is used to query table

question

SQL Server Management Studio (SSMS)

answer

primary tool for managing the server and its databases using a graphical interface. Can be used to view and optimize db performance, as well as to create and modify dbs, tables, and indexes.

question

SSMS Object Explorer

answer

allows users to browse, select, and manage any of the objects within the server.

question

SSMS Query Analyzer

answer

provides a GUI-bases interface to write and execute queries, supports: XQuery, SQLCMD, and Transact-SQL

question

XQuery

answer

query and functional programming language that is designed to query collections of XML data

question

SQLCMD

answer

a command line application that comes w/ SQL server and exposes the management features of SQL server. Allows SQL queries to be written and executed from the command prompt. Can also act as a scripting language to create and run a set of SQL statements as a script, stored a .sql files and are used for either management of db or to create the db schema during database deployment.

question

Transact-SQL

answer

the primary means of programming and managing SQL Server. exposes keywords so that you can create and manage db and their components and monitor and manage the server itself.

question

Data Manipulation Language (DML)

answer

the language element that allows you o use the core statements: INSERT, UPDATE, DELETE, & MERGE . When using DML statements, you must query the table to verify that key constraints are met and your syntax is correct.

question

SELECT

answer

retrieves rows from the db and enables the selection of one or many rows or columns from one or many tables in SQL Server

question

INSERT

answer

adds one or more new rows to a table or a view in SQL Server

question

UPDATE

answer

changes existing data in one or more columns in a table or view

question

DELETE

answer

removes rows from a table or view. (if a DELETE statement violates a foreign key constraint, nothing will be deleted)

question

MERGE

answer

performs insert, update, or delete operations on a target table based on the results of a join w/ a source table.

question

Data Definition Language (DDL)

answer

a subset of the Transact-SQL language, it deals w/ creating db objects like table, constraints, and stored procedure. SSMS user interface allows you to visually design DDL statements. Not all option you many wish to use w/ the DDL script can be accomplished through this interface. You have more flexibility, power, and control when using DDL statements. Can also use them o script tasks or activities that can be scheduled or executed as needed. USE, CREATE, ALTER, DROP, TRUNCATE, DELETE

question

USE

answer

changes the db context to the specified db or db snapshot

question

CREATE

answer

creates a SQL Server db object (table, view, or store procedure)

question

ALTER

answer

changes an existing object; you can use it to add or remove columns from a tablet. Can also be used to change the definition of a view, stored procedure, trigger, or function.

question

DROP

answer

removes an object from the db, but if other objects are dependent on the object you are attempting to remove, this statement will fail and an error will be raised.

question

TRUNCATE

answer

removes rows from a table and frees the space used by those rows

question

DELETE

answer

remove rows from a table but does not free the space used by those rows removed

question

system tables

answer

when you want to query system views to verify whether the object(s) you wish to drop are in the db tables, you need to know what tables are the most useful. system views remove to the sys schema, and include: sys.Tables sys.Columns sys.Databases sys.Constraints sys.Views sys.Procedures sys.Indexes sys.Triggers sys.Objects

question

Database objects are divided into two categories:

answer

storage and programmability

question

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

answer

CREATE

question

Constraints can define entity relationships between tables on a continual basis. They are also referred to as _________ constraints.

answer

foreign key

question

In order to use the views object to view a data set, you must use the _______ Transact-SQL statement to show data from the underlying tables.

answer

SELECT

question

DDL influences _____, whereas __________ influences actual data stored in tables.

answer

data, DML

question

The Microsoft database server that hosts relational databases is called_______________.

answer

MS SQL Server

question

The core DDL statements are:

answer

USE, CREATE, ALTER, DROP, TRUNCATE, DELETE

question

The core DML statements are:

answer

SELECT, INSERT, UPDATE, DELETE, MERGE

question

System views belong to the _________.

answer

sys schema

question

The foreign key constraint is a(n) __________ identifier.

answer

relationship

question

Which of the following is not a DDL statement?

answer

b. MERGE

question

Which of the following is not a column constraint?

answer

c. Range

question

What are limitations or rules placed on a field or column to ensure that data that is considered invalid is not entered?

answer

d. constraint

question

Which of the following is not a DML statement?

answer

a. REMOVE d. TRUNCATE

question

Select all of the following statements that are true:

answer

a. Indexes should only be created on columns that are frequently searched. b. A self-reference arises when a foreign key constraint references a column in the same table.

question

Which of the following actions is not supported by ALTER?

answer

c. Modifying the data type of an existing column

question

Which of the following is not a constraint?

answer

a. Null

question

What does SQL stand for?

answer

b. Structured Query Language

question

Which of the following SQL statements is used to extract data from a database?

answer

a. SELECT

question

Which SQL statement is used to update data in a database?

answer

d. UPDATE

Database Administration Fundamentals – Chapter 1 – Flashcards

Unlock all answers in this set

Haven't found what you were looking for?

Search for samples, answers to your questions and flashcards