Brainscape's Knowledge GenomeTM

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


See full index

DAF > DAF > Flashcards

DAF Flashcards

Database Administration Fundamentals

1
Q

database (db)

A
  • a structure or organized collection of data, categories of information and the relationship between these categories, typically stored in electronic format.
  • Allows you to input, organize, and retrieve data quickly.
  • minimize mistakes and redundancy
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

query

A

an inquiry into the database that returns information back from the database

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

database servers

A

contain databases so they can be accessed by multiple users and provide a high level of performance

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

DBMS - database management system

A

a collection of programs that indirectly enables you to enter, organize, and select data in a database

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

3 types of databases

A
  • Flat-type databases
  • Hierarchical databases
  • Relational databases
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Flat-type databases

A
  • simplistic in design (two-dimensional tables - rows & columns)
  • most commonly used in plain-text formats
  • purpose is to hold one record per line
  • they make access, performance, and queries very quick
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

field refers to…

A

a column in a table taht is designed to maintain specific informatio;n about every record in the table
-vertical entry

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

a record refers to…

A

a row

  • each individual entry that exists in a table
  • horizontal entity
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

hierarchical database

A
  • similar to a tree structure
  • each “parent” table can have multiple “children”
  • each child can have only one parent
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

relational database

A
  • most important
  • data is stored in tables
  • new information is automatically added into the table without the need to reorganize the table itself
  • can gave multiple parents
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Database objects are inherently divided into two broad categories…

A

storage and programmability

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

Definition of constraints

A

limitations or rules placed on a field or column to ensure that data that is considered invalid is not entereed

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

types of constraints

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
14
Q

unique constraint

A

allows the database administer to specifically identify which column should not contain duplicate values

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

check constraint

A

allows the administrator to limit the types of data a usere can insert into the database

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

default constraint

A

is used to insert a default value into a column if no other value is specified

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

not null constraint

A

ensures that data is entered into a cell - cell cannot be blank

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

primary key constraint

A
  • defines each record -uniquely identifies each record in a database table
  • cannot be duplicated
  • must contain unique values and cannot contain NULL values
  • all attributes are functionally dependent of
  • each table has a primary key, and only one primary key
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

foreign key constraint

A

-points to a primary key in another table

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

DML stands for

A

Data Manipulation Language

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

Data Manipulation Language

A

is the language element that allows you to use the core statements INSERT, UPDATE, DELETE, MERGE, SELECT

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

SELECT statement

A
  • selects data from a database

- retrieves rows from the database and enables the selection of one or many rows or columns from on or many tables

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

INSERT statement

A

adds one or more new rows to a table or a view

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

UPDATE statement

A
  • updates data

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

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

DELETE statement

A

Removes rows from a table or view

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

MERGE statement

A

Performs insert, update, or delete operations on a target table based on the results of a join with a source table

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

DDL means

A

Data Definition Language

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

data definition language definition

A

deals with creating database objects like tables, constraints, and store procedures

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

core DML statements

A
  • SELECT
  • INSERT
  • UPDATE
  • DELETE
  • MERGE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

core DDL statements

A
  • USE
  • CREATE
  • ALTER
  • DROP
  • TRUNCATE
  • DELETE
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

USE statement

A

changes the database context

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

CREATE statement

A

creates a SQL Server database object (database, index, view, table, or procedure)

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

database object

A
  • database
  • index
  • view
  • table
  • procedure
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

ALTER statement

A
  • changes an existing object
  • adds, deletes, or modifies columns in a table
  • or changes the data type in a table
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

DROP statement

A
  • Removes an object from the database

- deletes column, constraint, database, index, table, view

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

TRUNCATE statement

A
  • removes data from inside a table, leaves the table

- frees the space used by those rows

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

DELETE statement

A
  • remove rows from a table

- does not free up the space used

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

when to use TRUNCATE

A

if you are deleting data from tables in a large database

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

when to use DELETE

A

if you are deleting data from smaller databases

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

three types of files used to store databases

A
  • primary data files
  • secondary data files
  • transaction log file
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

primary data files

A
  • .mdf extension
  • first files created in a dabase
  • contain user-defined objects (tables, views) and system tables
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

secondary data files

A
  • .ndf extenstion

- on separate physical hard disks to give a database more room

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

transaction log file

A
  • .ldf extension

- don’t contain any objects such as tables or views

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

database index

A

data structure that improves the speed retrieval operations on a database table

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

tables

A
  • two-dimensional objects consisting of rows and columns

- used to store data

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

SSMS (SQL Server Management Studio)

A

primary tool to manage a server and its databases using a graphical interface

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

data type

A

an attribute tha specifies the tpe of data an object can hold, as well as how many bytes each data type takes up

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

if two data types differ only in how many bytes each uses…

A

the more bytes usually has a larger range of values and/or increased precision

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

predefined data types

A

built in data types

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

Exact numeric data types

A
  • most common SQL Server data types used to store numeric information
  • whole numbers only
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
51
Q

int (integer numeric data type)

A
  • primary integer data type (whole number)
  • used to store mathematical computations
  • used when no decimal point is required
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
52
Q

precision (p)

A

max total number of decimal digits that can be stored in a numeric data type (both to left and right of decimal point)
-value must be at least 1 and at most 38 (default is 18)

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

money and small money

A

data types used to represent monetary or currency values

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

approximate numeric data types

A

not as commonly used

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

date and time data types

A

deal with dates and times

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

built-in data types are organized into what general categories

A
  • exact numbers
  • approximate numbers
  • date and time
  • character strings
  • unicode character strings
  • binary strings
  • other data types
  • CLR data types
  • spatial date types
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
57
Q

money data type

A

used in places where you want money or currency involved in your database - doesn’t calculate percentages

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

datetime data type

A

store date and time data in different formats

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

Varchar data type

A

-character string data type supporting English attributes

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

Boolean data type

A

bit data type

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

Float data type and Real data type

A
  • used in conjunction with floating-point numeric data type

- considered approximate-number data type

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

exact numeric data types examples

A
  • int
  • bigint
  • bit
  • decimal
  • numeric
  • money
  • small money
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
63
Q

approximate numeric data types

A
  • more precision

- includes decimals

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

implicit conversion

A

when a conversion (converting values from one data type to another) is done automatically

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

cast and convert

A

used to force conversion

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

explicit conversion

A

when a conversion is forced using callout function

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

regular character string

A
  • uses one byte of storage for each character
  • allows you to define one of 256 possible characters
  • accommodates English and European languages
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
68
Q

unicode character string

A
  • uses two bytes of storage per character
  • represent one of 65,536 characters
  • accommodates most languages
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
69
Q

view

A
  • a virtual table consisting of different columns from one or more tables
  • stored in a database as query objects
  • security mechanism
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
70
Q

stored procedure

A
  • previously written SQL statement that has been “stored” or saved into a database
  • used to save time
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
71
Q

SQL injection

A

an attack in which malicious code is inserted into strings to be passed on later when parsing or executing statements

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

primary form of SQL injection

A

direct insertion or code into user-input variables that are concatenated with SQL commands and then executed

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

VAR element

A

-preserve space in the row in which this element resides on the basis of the columns defined size and not the actual number of characters in the string

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

nchar and nvarchar

A

are unicode characters strings can be fixed or variable

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

purpose of a table

A

provide a structure for storing data within a relational database

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

purpose of a table

A

provide a structure for storing data within a relational database

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

The SQL command for retrieving any data from a database the data

A

SELECT

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

what three things do you need to identify in your statement in order to form a proper SELECT query

A
  • what columns to retrieve
  • what tables to retrieve them from
  • what conditions, if any, must satisfy
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
79
Q

BETWEEN clause

A
  • allows you to specify the range to be used in a “between x and y” query format
  • select values within a given range
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
80
Q

NOT keyword

A

is used to search data in terms of what you don’t want in your output

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

UNION clause

A

allows you to combine the results of any two or more queries into a resulting single set that will include all the rows belonging to the query in that union

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

EXCEPT clause

A

returns any distinct values from the left query that are not also found on the right query

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

INTERSECT clause

A

returns any distinct values not found on both the left and right sides of this operand

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

JOIN clause

A

allows you to combine related data from multiple table sources

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

to insert data…

A
  • you can use SSMS

- or the INSERT statement

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

UPDATE statement

A
  • changes data in a table or a view

- updates existing rows in a table

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

DELETE statement

A
  • can identify and delete individual rows

- removes rows from a table or a view

88
Q

TRUNCATE TABLE statement

A

removes actual data from within a table but leaves the table structure in place for future use

89
Q

DROP TABLE command

A
  • can remove entire table
90
Q

compare EXCEPT and INTERSECT

A
  • -both designed to return distinct values by comparing the results of two queries
  • EXCEPT clause gives you the final result set where data exists in the first query and not in the second
  • INTERSECT fives you the final result set where values in both of the queries match by the query on both the left and right
91
Q

rules that apply to use EXCEPT and INTERSECT

A
  • the number and order of the columns must be the same in all queries
  • the data types must be compatible
92
Q

how is JOIN similar to EXCEPT and INTERSECT

A

they return values from two separate table sources

93
Q

three types of JOIN statements

A
  • inner joins
  • outer joins
  • cross joins
94
Q

inner joins

A
  • most common

- allow you to match related records taken from different source tables

95
Q

outer joins

A

can include records from one or both tables you are querying that do not have any corresponding records in the other

96
Q

three types of outer joins

A
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN
97
Q

cross joins

A

return all rows from one table along with all rows from the other table WHERE conditions should always be included

98
Q

INSERT clause

A

INSERT INTO - inserts new rows in a table

99
Q

INSERT clause syntax

A 
INSERT INTO    ()
VALUES ()

clause - contain comma-separated list of the column names in the table you wish to include
clause - would contain the values you would like to insert

100
Q

UPDATE clause

A

allows you to modify data stored in tables using data attributes

101
Q

UPDATE clause syntax

A

UPDATE
SET =
WHERE

102
Q

DELETE statement syntax

A

DELETE FROM

WHERE

103
Q

TRUNCATE TABLE statement syntax

A

TRUNCATE TABLE

104
Q

attribute

A

A property of an entity

105
Q

DROP TABLE statement syntax

A

DROP TABLE

106
Q

using transactions when updating data…

A

-is method to prevent accidental deletion
BEGIN TRAN
DELETE FROM
if it did what you want you would do a COMMIT statement to save the changes, or ROLLBACK to undo them

107
Q

Normalization

A
  • is the elimination of redundant data to save space
  • process of organizing data in order to reduce redundancy by dividing a database into two or more tables and then defining table relationships
108
Q

referential integrity

A
  • The rule that if a table contains a foreign key, the value of that foreign key for any given row must match a value of a primary key in another table or be null
  • -does not allow for deletion of table unless all of the related tables are deleted using a cascade delete
109
Q

First Normalization Form (1NF)

A
  • Eliminate repeating groups
  • a table is in first normal form if it does not contain repeating groups
  • all fields shoud atomic, meaning that the data can’t be divided any further
  • tables must be flat, meaning they can contain only one data value set per row
  • table shouldn’t contain more than one column that represents the same type of non-distinct value
  • every attribute (field) must contain one unit of data
110
Q

NONKEY ATTRIBUTE

A

An attribute is a nonkey attribute if it is not a part of the primary key, candidate key

111
Q

Second Normal Form (2NF)

A
  • Eliminate redundant data
  • A relation is in second normal form if it is in first normal form and no nonkey attribute is dependent on only a portion of the primary key
  • all keys in a table must refer to a primary key
  • a row may not contain values in data cells that do not pertain to the value of the key field
112
Q

Third Normal Form (3NF)

A
  • Eliminate columns not dependent on key
  • A table is in third normal form if it is in 2NF and if the only determinants it contains are candidate keys
  • No attributes depend on other nonkey attributes
  • All fields must be mutually independent, meaning thee are no hidden relation-ships to other fields in the same table
  • A column that is not a key column may not be dependent upon another column that is not a key column
  • the uniqueness of a record depends on the key, the whole key, and nothing but the key
113
Q

First Normalization Form (1NF)

A
  • Eliminate repeating groups
  • the table must have no duplicate records
  • the table must not have multi-valued attributes
  • entries in the column or attribute must be of the same data type
114
Q

fields refer to

A

columns

115
Q

Second Normal Form (2NF)

A
  • Eliminate redundant data
  • each attribute does describes the entity
  • A relation is in second normal form if it is in first normal form and no nonkey attribute is dependent on only a portion of the primary key
  • all keys in a table must refer to a primary key
  • a row may not contain values in data cells that do not pertain to the value of the key field
116
Q

Third Normal Form (3NF)

A
  • Eliminate columns not dependent on key
  • A table is in third normal form if it is in 2NF and if the –
  • checks for transitive dependencies
  • only determinants it contains are candidate keys
  • No attributes depend on other nonkey attributes
  • All fields must be mutually independent, meaning thee are no hidden relation-ships to other fields in the same table
  • A column that is not a key column may not be dependent upon another column that is not a key column
  • the uniqueness of a record depends on the key, the whole key, and nothing but the key
117
Q

SQL logical operators

A
  • ALL
  • AND
  • ANY
  • BETWEEN
  • EXISTS
  • IN
  • LIKE
  • NOT
  • OR
  • SOME
118
Q

SQL command for retrieving any data from a database is

A

SELECT

119
Q

SELECT statement syntax

A

SELECT
FROM
WHERE ….

120
Q

second normal form is violated when…

A
  • an attribute depends on only part of the key

- -resolved by moving the nondependent attribute to a new entity

121
Q

third normal form is violated when…

A

the attribute depends on the key but also on another nonkey attribute
-resolved by moving the non dependent attribute to a new entity

122
Q

3 different types of constraints that ensure that you are able to maintain database integrity

A
  • primary key
  • foreign key
  • composite (unique) key
123
Q

unique key constraint

A

allows you to enforce the uniqueness property of columns

  • similar to primary key except
  • -columns containing a unique key constraint may contain only one row with a NULL value
  • -a table may have multiple unique constraints
124
Q

second normal form is violated when…

A
  • an attribute depends on only part of the key

- -resolved by moving the non-dependent attribute to a new entity

125
Q

third normal form is violated when…

A

the attribute depends on the key but also on another non-key attribute
-resolved by moving the non dependent attribute to a new entity

126
Q

draw back to indexing

A

time it takes to build the actual indexes and the storage space the indexes require

127
Q

how many clustered indexes is allowed for each table

A

one

128
Q

clustered index

A

-data is sorted and stored in the table or view that is based on their respective key values
-physical construct
SQL automatically creates a clustered index when the primary key is defined

129
Q

transitive dependencies

A

similar to partial dependency - they both refer to attributes that are not fully dependent on a primary key

130
Q

non-clustered index

A
  • contains the non-clustered index key values

- each of those keys has a pointer to a data row that contains a key value

131
Q

database security

A

should ensure the users’ assigned rights and responsibilities are enforced through a security plan

132
Q

permission

A

used to grant an entity (such as a user) access to an object (such as another user or a database)

133
Q

securables

A

different objects (defined databases, tables, logins, users, and roles) can be granted permissions to access different securables

134
Q

login or logon

A
  • is the process by which individual access to a computer system is controlled by identification of the user through the credentials he or she provides
  • the most common method involves using a username and password
135
Q

does a user with a fixed database role that has administrative-level permissions have access data?

A

a user still cannot access data without first being granted permission to the database object themselves(ie tables, stored procedures, views, functions)

136
Q

user account

A

a logical representation of a person within an electronic system

137
Q

users can login to SQL Server using

A
  • a Windows domain login (user login)
  • a username login (member of user group)
  • or SQL Server-specific login
138
Q

sysadmin

A

a role where that user has full access to every server function, database, and object for that server

139
Q

with full access as a sysadmin

A

the user can grant other users permissions to all server securables
-they can perform a variety of system-level actions

140
Q

“guest” user account

A
  • users who have not been granted direct access to a database to gain access using the “guest” user account
  • they can make limited changes within the database server
141
Q

user-defined roles

A

-are custom roles that serve as groups

-

142
Q

each objects permission is assigned by

A

granting, revoking, or denying user login persmissions

143
Q

authentication

A

is the act of establishing or confirming a user or system identity

144
Q

granting permission

A

means that a user can access the object

145
Q

denying permission

A

overrides a granted permission

146
Q

revoking a permission

A

removes the permission that has been assigned, regardless of whether it was a denied permission or a granted permission

147
Q

3 types of accounts available for the SQL Server service account

A
  • local user account
  • local system account
  • domain user account
148
Q

local user account

A
  • used if access to the network is not required

- cannot be used outside the server environment

149
Q

local system accounts

A
  • single-server installation

- can create a security hole

150
Q

domain user accounts

A
  • recommended login account

- can grant administrator rights to the SQL Server account

151
Q

SA account

A

-built-in SQL administrator account associated with SQL authentication

152
Q

system admin role

A
  • can perform any activity in the SQL Server installation, regardless of the permission setting
  • can override denied permissions on an object
153
Q

public role

A
  • is a fixed role, but can have object permissions like a standard role
  • every user is automatically a member of the public role and cannot be removed, so the the public role serves as a baseline or minimum permission level
154
Q

assigning a default database to a user

A

does not automatically grant access to that database

155
Q

a user can be granted access to databases in

A

the Database Access tab

156
Q

3 kinds of database server roles

A
  • fixed roles
  • the public role
  • user-defined roles
157
Q

fixed server roles are best used for?

A

-delegating certain server administrative tasks

158
Q

server role

A

roles grant permission to perform certain server-related administrative tasks

159
Q

bulkadmin

A

can perfomr bulk insert operations

160
Q

Dbcreator

A

can create, alter, drop, and restore databases

161
Q

Diskadmin

A

can created, alter, and drop disk files

162
Q

Processadmin

A

can kill a running SQL Server process

163
Q

Securityadmin

A

can manage the logins for the server

164
Q

Serveradmin

A

can configure the server-wide settings, including setting up full-text searches and shutting down the server

165
Q

Setupadmin

A

can configure linked servers, extended stored procedures, and the startup stored procedure

166
Q

Sysadmin

A

can perform any activity in the SQL Server installation, regardless of any other permission setting - even overrides denied permissions on an object

167
Q

User-defined roles

A
  • for users not defined by a fixed server role
  • for users who need to perform specific database functions, but you dont want to grant a role that would permit them do more than what they need to
168
Q

db_owner

A

a special role that has all permissions in the database

169
Q

application role

A

a database -specific role intended to allow an application to gain access regardless of the user

170
Q

purpose of a database backup

A

is to have something to restore if data is lost during a business’s daily routine

171
Q

a full backup contains

A

all the data in a specific database or set of filegroups or files to allow recovering that data

172
Q

differential backup

A

only backs up data since the last full backups

173
Q

incremental backup

A

only backs up data since the last full or incremental backup

174
Q

how to restore from differential backup

A
  • first restore the preceding full backup

- then restore the last differential backup

175
Q

how to restore from incremental backup

A
  • first restore the preceding full backup

- then restore each incremental backup since the full backup in order

176
Q

object security

A
  • permission to the individual database objects may be granted
  • directly to the user or to a standard role, with the user assigned to the role
177
Q

fixed database roles

A
  • primarily organize administrative tasks

- user may belong to multiple

178
Q

fixed database roles include

A
  • db_accessadmin
  • db_backupoperator
  • db_datareader
  • db_datawriter
  • db_ddladmin
  • db_denydatareader
  • db_denydatawriter
  • db_owner
  • db_securityadmin
179
Q

db_accessadmin

A

authorizes a user to access the database, but not manage database-level security

180
Q

db_backupoperator

A

allows a user to perform backups, checkpoints, and DBCC commands, but not restores

181
Q

db_datareader

A
  • authorizes a user to read all data in the database

- can be overridden by a deny permission

182
Q

db_datawriter

A

allows user to write to all data in the database

-can be overridden by a deny permission

183
Q

db_ddladmin

A

authorizes a user to issue DDL commands (create, alter, drop)

184
Q

db_denydatareader

A

permits a user to read from any table in the database

-overrides any object-level grant

185
Q

db_denydatawriter

A

blocks a user from modifying data in any table in the database
-overrides any object-level grant

186
Q

db_owner

A
  • has all permissions in the database

- object level deny will override membership of this role

187
Q

db_securityadmin

A

permits a user to manage database-level security

-includes roles and permissions

188
Q

application role to allow an application to gain access regardless of its user

A

data-base specific role intended

189
Q

object permission

A

are permissions that allow a user to act on database objects, such as tables, store procedures, and views

190
Q

types of object permissions

A
  • select
  • insert
  • update
  • delete
  • DRI (References)
  • execute
191
Q

object permission select

A

the right to select data

192
Q

object permission insert

A

the right to insert data

193
Q

object permission updates

A

the right to modify existing data

194
Q

object permisssion deletes

A

the right to delete existing data

195
Q

DRI (references)

A

the right to create foreign keys with DRI

196
Q

object permission execute

A

the right to execute stored procedures or user-defined functions

197
Q

object permissions are assigned withe the SQL DCL commands

A
  • GRANT
  • REVOKE
  • DENY
198
Q

SQL Server offers three recovery models

A
  • simple recovery
  • full recovery
  • bulk-logged
199
Q

why does simple recovery require the least administration

A

the transaction log backups are truncated on a regular basid

200
Q

full recovery

A

allows you to restore to a point in time since the logs files record all SQL transactions and the time they were performed
-requires shrinking and truncating logs to save room

201
Q

bulk-logged

A
  • least used recovery method
  • compromise between full and simple
  • no point-in-time recovery
202
Q

database backup

A

a full backup of the whole database

-database backups repreent the whole database at the time the backup finished

203
Q

differential database backup

A

a backup of all files in the database

-contains only;y the data that were modified since the most recent database backup of each file

204
Q

partial database backup

A
205
Q

can files in a database be backed up and restored individually

A

yes

206
Q

types of backups supported by SQL

A
  • database backups
  • partial backups
  • file backups
207
Q

partial backup

A
208
Q

differential partial backup

A

contains only the data that were modified since the most recent partial backup

209
Q

file backup

A

a full backup of all the data in one or more files or filegroups

210
Q

differential file backup

A

a backup of one or more files that contain data extents that were changed since the most recent full backup of each file

211
Q

restore scenarios possible in SQL

A
  • complete database restore
  • file restores
  • page restores
  • piecemeal restore
  • recovery only
  • transaction log restores
  • create mirror database
  • create and maintain a stand by server
212
Q

index

A

data structure that improves the speed of data retrieval operations on a database table
-disadvantage need to be created and updated

213
Q

the data in RDBMS is stored in

A

database objects called tables

214
Q

tables

A

a collection of related data entries and it consists of columns and rows

215
Q

every table is broken up into smaller entities called

A

fields

DAF (1 decks)

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