Brainscape's Knowledge GenomeTM

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


See full index

Database Management > Full Study > Flashcards

Full Study Flashcards

1
Q

What are the three main Objects in a Database, with a explanation of what they are?

A

Entities – Major object that holds significant value to the organization.

Attributes – Characteristics that gives additional information.

Relationships - logical linkage between two entities that describes how those entities are associated with each other.

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

What are the two types of Attributes?

A

Identifiers - Used to specify a unique characteristic of the entity. This will be used to identify the entity.

Descriptor - Merely used to provide more information on the entity. This does not uniquely identify the entity.

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

What is Cardinality?

A

cardinality usually represents the relationship between the data in two different tables by highlighting how many times a specific entity occurs in comparison to another.

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

What are the different types of Cardinality?

A

One-to-One - A single instance of one entity is associated with a single instance of another entity.

One-to-Many - An instance of an entity (called the parent) is associated with zero or more of another entity (called the child).

Many-to-Many - Many instances of one entity are associated with many instances of another entity.

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

What are the guidelines for a ERD?

A

Singular
Named after an Entity
Mixed Case
Same Naming

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

Name some ways to check data integrity

A

Entity - A way to uniquely identify every row in a table.

Domain - Refers to the data type and nullability of a column.

Reverential - Primary and foreign keys remain synchronized.

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

What is a non-decomposable column?

A

A column that can’t be broken down into more columns.

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

What are the advantages of using a non-decomposable column?

A

Easier to Update
Easier to Query
Specific Data Integrity

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

What are the rules for Normalization?

A

1 – Dependencies Between Data are identified.

2 – Redundant Data is Minimized.

3 – The data model is flexible and easier to maintain.

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

What are the different normal forms?

A

Zero Normal Form – Before we start the normalization steps, we look at our question and list all the attributes we will need in our database.

First Normal Form - An entity is in the first normal form if it contains no repeating groups.

Second Normal From - A relationship is in the second normal form (2NF) if it is in the first normal form, and all its attributes depend on the whole primary key.

Third Normal Form - A relationship is in the third normal form (3NF) if all the attributes are non - transitively dependent on the primary key.

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

Name different types of objects in SQL

A

Literal Values - A literal value is an alphanumerical, hexadecimal, or numeric constant.

Delimiters - Used to allow the use of reserved keywords as identifiers.

Comments - There are two different ways to specify a comment.

Identifiers – Identify Database Objects.
Reserved Keywords – Set of names with a reserved meaning.

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

Name different types of a Data Integrity?

A

Entity Integrity - a way of distinguishing between different entities. In other words, a way to uniquely identify every row in a table.

Domain Integrity - concerned with ensuring that column values fall within an acceptable range of values (the domain). Domain integrity also refers to the data type and nullability of a column.

Referential Integrity - refers to the requirement that primary and foreign keys remain synchronized between parent and child tables.

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

What can the Alter Table Statement do?

A

Add a column.
Remove a constraint.
Change a column data type.
Add a check constraint.
Add a foreign key.
Add a default constraint.
Remove a table or column.
Add a unique constraint.
Add a primary key.

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

What are the Advantages of Indexing?

A

Much quicker path to any row in a table.
Enforce entity integrity.
Improve the performance of queries that use joints.
Improve the performance of queries that use the ORDER BY clause.

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

What are guidelines to clustered indexing?

A

Only one clustered index can be created per table.
The leaf pages are the table’s data pages, and the rows are physically stored in order to the index key.
Clustered indexes are smaller than non-clustered indexes.

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

What are guidelines to non-clustered indexing?

A

Up to 249 non-clustered indexes can exist on a table.
The logical order of the rows is different than the physical order of the data.
Create a clustered index before creating any non-clustered indexes.

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

What are guidelines to Composite Indexing?

A

Up to 16 columns can be used in an index. The combined size of the columns cannot exceed 900 bytes.
The order in which the columns are used in the index is very important
When building a composite index, use the most unique column as the first column.

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

What are the columns that can be indexed?

A

Primary Keys and Foreign Keys
Columns you often search.
Cover the Query
Search for a range of values
Sort the results of a query.

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

Why do we use joins?

A

To create a single query that returns data from multiple tables.

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

What are the different types of Joins?

A

INNER JOINS
On the right is a visual representation of
an INNER JOIN between two tables. Only
the records that have a link to both tables
will be displayed.
Also known as an equi-join.
An INNER JOIN cannot be nested inside a
LEFT JOIN or RIGHT JOIN. There must
always be a relational link between tables
used in a join query.
OUTER JOINS
Outer joins differ from INNER JOINs in that
rows that do not have matches in a joined
table can be returned.
LEFT JOIN
All records from table A will be displayed
with the related records from table B. If
there are no related records, a NULL will
be displayed.
RIGHT JOIN
All records from table B will be displayed
with the related records from table A. If
there are no related record, a NULL will be
displayed.

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

What is a Sub-Query?

A

A simple example of a sub-query would be to return all the titles of books that have a price less than that of the average price of all the books.

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

What is a Common Table Expression?

A

The common table expression (CTE) is a powerful construct in SQL that helps simplify a query. CTEs work as virtual tables (with records and columns), created during the execution of a query, used by the query, and eliminated after query execution. CTEs often act as a bridge to transform the data in source tables to the format expected by the query.

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

What is the Syntax for a Common Table Expression?

A

WITH ghostTableName
(Col1, Col2)
AS
(
SELECT Col1ID, SUM(Col2)
FROM Table
GROUP BYCol1ID
)
SELECT Columns
SELECT Col1ID, SUM(Col2)
FROM Table
INNER JOIN ghostTableName
GROUP BY Col1ID

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

What are scripts and batch?

A

A query is a single SQL DML statement.

A batch is a collection of one or more T-SQL statements.

The SQL script file and Query analyser window can contain multiple batches.

If there are multiple batches, then the batch separator keyword terminates each batch.

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

How do you declare a Variable in SQL?

A

DECLARE @variable_Name DATATYPE,
@variable_Name DATATYPE,
@variable_Name DATATYPE

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

What is the syntax for assigning a variable?

A

Using the SET keyword:
DECLARE @Name VARCHAR(30),
@Surname VARCHART(30)
SET @Name = ‘John’
SET @Surname = (SELECT LastName FROM
Employee WHERE EmpID = 123

Using SELECT
DECLARE @Name VARCHAR(30),
@Surname VARCHART(30)
SELECT @Name = FirstName, @Surname
= LastName
FROM Employee WHERE EmpID = 123

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

The Begin and End Keyword?

A

The begin and end keyword are used to define the begin and end of a certain set of demand

The primary purpose of statement blocks is to define the group of statements that are affected by other control-of-flow statements such as IF, WHILE, and CASE. Each of these control-of-flow statements operates with only a single statement or a statement block.

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

What does the GOTO command do?

A

The GOTO command causes execution of a batch to immediately resume at a label. A label is merely an identifier that is followed by a colon to identify it as an independent entity. A label can be placed anywhere in your script as a commenting feature, regardless of whether a GOTO refers to it or not.

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

What does the return command do?

A

The RETURN command enables you to immediately stop execution of a batch. When the RETURN command is executed, the script execution resumes at the next batch in the script. When used in a script, the RETURN command takes no parameters. It is executed as an independent statement.

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

What does the if statement do?

A

If a Boolean expression, which follows the keyword IF, evaluates to true, then IF executes one Transact-SQL statement (or more, enclosed in a block). The optional ELSE statement can also contain a statement or block. It will be executed if the Boolean expression is false.

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

What is the syntax for a IF statement?

A

IF ‘Boolean Expression’
T/SQL statement/Block
ELSE
T/SQL statement/Block

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

What does the while statement do?

A

The WHILE statement repeatedly executes one Transact-SQL statement (or more, enclosed in a block) while the Boolean expression evaluates to true. In other words, if the expression is true, the statement (or block) is executed, and then the expression is evaluated again to determine if the statement (or block) should be executed again. This process repeats until the expression evaluates to false.

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

What does the BREAK statement do?

A

When a break statement is encountered inside a loop, the loop is immediately terminated and the program control resumes at the next statement following the loop.

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

What does the CONTINUE statement do?

A

We use this Continue statement inside the While loop. If the execution finds the SQL continue statement inside the While loop, it will stop executing the current loop iteration and starts the new iteration from the beginning.

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

What does the CASE statement do?

A

CASE is the extension of IF…ELSE statement. Unlike IF…ELSE, where only the maximum of one condition is allowed, CASE allows the user to apply multiple conditions to perform different sets of actions.

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

What are the different types of Transaction Modes?

A

Auto commit Transactions - Each individual statement is a transaction.

Explicit Transaction - Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.

Implicit Transactions - A new transaction is implicitly started when the prior transaction completes.

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

What is a transaction mode?

A

Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fail, the transaction fails. Therefore, a transaction has only two results: success or failure.

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

What are the Benefits of using a TRY…CATCH?

A

Exceptions provide a clean way to check for errors without cluttering the code.

Exceptions provide a mechanism to signal errors directly rather than using some side effects.

Exceptions can be seen by the programmer and checked during the compilations process.

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

What are the main points of using a Stored Procedure?

A

Pre-Compiled Code: They are precompiled and therefore can run with less overhead than would be experienced by submitting the logic in a script.

Sharing Application Logic: Common tasks can be shared between any number of different applications.

Reliability: The approach of using many procedures to accomplish one specific task also enables you to evaluate the effectiveness of every piece.

Security: The use of stored procedures provides security control above and beyond the use of the GRANT and REVOKE statements.

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

What are commands that you can do with procedures?

A

Creating A stored Procedure
Changing a Procedure
Deleting a Procedure
Providing a stored procedure with context
Running the Procedure
Specifying Parameters and their values

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

What are the benefits of using Views?

A

Security - You can restrict users to access directly to a table and allow them to access a subset of data via views.

Simplicity - You can simplify the complex queries with joins and conditions using a set of views.

Consistency - Hide the complex queries logic and calculations in views.

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

What are some things that you can do with a view?

A

Creating a View
Using a view
Altering a view
Removing a view
Inserting into a view.
Updating a View
Delete statement in a view.

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

What is a with check option?

A

The system tests whether the inserted value of the enter_date column evaluates to true for the condition in the WHERE clause of the SELECT statement. The attempted insert fails because the condition is not met.

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

What does WITH ENCRYPTION and WITH SCHEMANDING do?

A

WITH ENCRYPTION option encrypts the information in the system catalogue.
That way you cannot view the text used to create the object.

WITH SCHEMABINDING, binds the view (or UDF) to the database objects that it references.
Any attempt to modify the structure of the database object that the function references fail.

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

What are the steps in creating a View?

A

Declare Variables – Declare the variables to be used within the code block.

Set/Select Statement – Initialize the variables to a specific value.

Declare Cursor - Populate the cursor with values that will be evaluated.

Open Statement - Open the cursor to begin data processing.

Fetch Statement - Assign the specific values from the cursor to the variables.

While Statement - Condition to begin and continue data processing.

BEGIN…END statement – Start and End of the code block.

Close Statement - Releases the current data and associated locks but permits the cursor to be re-opened.

Deallocate Statement – Destroys the cursor.

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

What is trigger?

A

A mechanism that is invoked when a particular action occurs on a particular table.
The execution part of a trigger usually contains a stored procedure or a batch.

47
Q

What are the two forms of triggers?

A

The trigger can be either a DML statement or a DDL statement.
DML triggers (Data Manipulation)
DDL triggers (Data Definition)

48
Q

What does an after trigger do?

A

AFTER triggers fire after the triggering action has been processed. You can specify an AFTER trigger by using either the AFTER or FOR reserved keyword. AFTER triggers can be created only on base tables.

49
Q

What can a After trigger be used for?

A

Create an audit trail of activities in one or more tables of the database Implement business rules
Enforce referential integrity

50
Q

What can a trigger be used for?

A

After Triggers
Alter Triggers
Deleted and Inserted Table

51
Q

What is Authentication?

A

Requires you to ask the question “Does the user have legitimate right access to the system”.
This concept specifies the process of validating user credentials to prevent unauthorized access.

52
Q

How can Authentication be checked?

A

By asking the user to provide:

A password (Something that only they will know)
A key card or badge (Something only they will have on them)
A signature odd Fingerprints (Physical Characteristics only users will have)

53
Q

What are the two types of subsystems to security?

A

Windows Security:
Windows security specifies security at the operating system level—that is, the method by which users connect to Windows using their windows user accounts.

SQL Server Security:
SQL Server security specifies the additional security necessary at the system level—that is, how users who have already logged on to the operating system can subsequently connect to the database server.

54
Q

What is Encryption?

A

The process of scrambling information so that it is incomprehensible till it is decoded by the intended recipient.
Database Engine secures data with hierarchical encryption layers and a key management infrastructure.
Each layer secures the layer beneath it, using a combination of certificates, asymmetric keys, and symmetric keys

55
Q

What is a certificate?

A

A public key certificate is a digitally signed statement that binds the value of a public key to the identity of the person, device, or service that holds the corresponding private key.

56
Q

How does a Certificate work?

A

Certificates are issued and signed by a certification authority (CA). The entity that receives a certificate from a CA is the subject of that certificate.

57
Q

What does a certificate contain?

A

The private keys corresponding public key.
The private keys corresponding identifier information
Issuer identifier information.
The digital signature of the issuer.

58
Q

What is the syntax to create a certificate?

A

USE sample
GO
CREATE CERTIFICATE cert02.
ENCRYPTION BY PASSWORD = ‘pGDFD4bb92DGvbd2439587y’
WITH SUBJECT = ‘SQL Server 2008’

59
Q

What is the syntax to create a key?

A

USE sample
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = ‘p1s4w9d16’
GO
CREATE CERTIFICATE cert01
WITH SUBJECT = ‘Certificate for dbo’

60
Q

What is Authorization?

A

Authorization is the process that is applied after the identity of a user is verified through authentication.

61
Q

What is the process that Authentication follows?

A

The system determines what resources the user can use. In other words, structural and system catalogue information about a particular entity is now available only to principals—that is, subjects that have permission to access that entity.

62
Q

What does tracking changes mean? and what is it useful for?

A

Tracking changes means that actions of unauthorized users are followed and documented on your system.
This process is useful to protect the system against users with elevated privileges.

63
Q

What is a windows login account and what does it do?

A

A Windows user account or a login allows a user to log in to the system.

A user who subsequently wants to access a particular database of the system also needs a database user account to work with the database.
Therefore, users must have a database user account for each database they want to use.

64
Q

What is a schema?

A

Database Engine supports named schemas using the notion of a principal, an entity that can access objects.

65
Q

What are the two types of principals that you get in schemas?

A

An indivisible principal - An indivisible principal represents a single user, such as a login or Windows user account.

A group principal - A group principal can be a group of users, such as a role or a Windows group.

66
Q

What are principles?

A

Principals are ownership of schemas, but the ownership of a schema can be transferred easily to another principal and without changing the schema name.

67
Q

What are the benefits of separating schemas form the database?

A

One principal can own several schemas.

Several indivisible principals can own a single schema via membership in roles or Windows groups.

Dropping a database user does not require the renaming of objects contained by that user’s schema.

Each database has a default schema, which is used to resolve the names of objects that are referred to without their fully qualified names. The default schema specifies the first schema that will be searched by the database server when it resolves the names of objects.

68
Q

What are the three Transact-SQL schema-related statements?

A

CREATE SCHEMA
ALTER SCHEMA
DROP SCHEMA

69
Q

What are the three member role types in SQL?

A

Windows groups and accounts
SQL Server logins
Other Roles

70
Q

What are the predefined roles in SQL?

A

Fixed Server Roles
Fixed Database Roles
Application Roles
User-Defined Roles

71
Q

What are fixed server roles?

A

Fixed server roles are defined at the server level and therefore exist outside of databases belonging to the database server

You cannot add or remove fixed server roles. Additionally, only the members of fixed server roles can execute the system procedures to add or remove logins to or from the role.

Each fixed server role has its implicit permissions within a database system. You can view the permissions for each fixed server role using the sp_srvrolepermission system procedure.

72
Q

What are two system procedures used to add members to and delete members from a fixed server role?

A

Sp_addsrvrolememeber
Sp_dropsrvrolememeber

73
Q

What are Fixed Database Role?

A

Fixed database roles are defined at the database level and therefore exist in each database belonging to the database server. Members of the fixed database role can perform different activities.

74
Q

What are Application Roles?

A

Application roles allow you to enforce security for a particular application. In other words, application roles allow the application itself to accept the responsibility of user authentication, instead of relying on the database system.

Application roles differ significantly from all other role types. First, application roles have no members, because they use the application only and therefore do not need to grant permissions directly to users. Second, you need a password to activate an application role.

75
Q

What are User Defined Defined Database Roles?

A

Generally, user-defined database roles are applied when a group of database users’ needs to perform a common set of activities within a database and no applicable Windows group exists.

76
Q

What are some user-defined roles to add, remove or alter a database?

A

sp_addrolemember
sp_droprolemember
sp_helprole

77
Q

Who can execute statements or perform operations on an entity?

A

Only authorized users, If an unauthorized user attempts to do either task, the execution of the Transact-SQL statement or the operation on the database object will be rejected.

78
Q

What are three Transact-SQL statements related to Authorization?

A

GRANT
DENY
REVOKE

79
Q

What does the GRANT statement do?

A

By default, if user A grants a permission to user B, then user B can only use the permission to execute the Transact-SQL statement listed in the GRANT statement. The WITH GRANT OPTION gives user B the additional capability of granting the privilege to other users.

80
Q

What does the DENY statement do?

A

The DENY statement prevents users from performing actions.

This means that the statement removes existing permissions from user accounts or prevents users from gaining permissions through their group/role membership that might be granted in the future.

81
Q

What does the REVOKE statement do?

A

The REVOKE statement removes one or more previously granted or denied permissions.

82
Q

What are the two types of Concurrency Models?

A

Pessimistic Concurrency
Uses Locks to Block access to data that is used by another process at the same time.

Optimistic Concurrency
Works on the assumption that a transaction is unlikely to modify data that another transaction is modifying at the same time.

83
Q

What are the 4 Transaction Types?

A

Atomicity
All data modifications in a transaction are executed or, in the case of any failure, all already executed changes are undone.

Consistency
Guarantees that a transaction will not allow the database to contain inconsistent data.

Isolation
Separates concurrent transactions from each other.

Durability
Guarantees one of the most important aspects of a database: persistence of data.

84
Q

Whare some problems that can arise from not locking the database?

A

Lost Update
Dirty Reads
Nonrepeatable Reads
Phantoms

85
Q

What are the aspects of Locking?

A

Lock Duration – Specifies the time period that a resource hold a lock.

Lock Modes – Specifies different kinds of locks.

Lock Granularity – Specifies which resource is locked by a single attempt.

86
Q

What determines the type of lock that you use?

A

The choice of lock to use depends on the resource that needs to be locked.

87
Q

What are the three types of locks for row- and page- level locking?

A

Shared (S)
Exclusive (X)
Update (U)

88
Q

What is Lock Granularity?

A

Granularity specifies which resource is locked by a single attempt.

89
Q

What can a database lock?

A

Row, Page, Index Key or Range of Indexes, Table, Extent, The Database Itself

90
Q

What is lock escalation?

A

The process of converting many page-, index-, row-level locks into a singular table lock is called escalation.

When many lock granularity are held during a transaction, the Database automatically changes this into a table lock.

91
Q

What is a deadlock?

A

A special concurrency problem in which to transactions block the progress of each other.
The first transaction has a lock on some database object that the other transaction wants to access, and vice versa.

92
Q

What is database Engine and Isolation Levels?

A

Using isolation levels, you can specify which of the concurrency problems may occur and which you want to avoid.

93
Q

What are the five isolation levels?

A

READ UNCOMMITED - READ UNCOMMITTED provides the simplest form of isolation between transactions, because it does not isolate the read operations from other transactions at all.

READ COMMITED - A transaction that reads a row and uses the READ COMMITTED isolation level tests only whether an exclusive lock is placed on the row.
If no such lock exists, the transaction fetches the row. (This is done using a shared lock.)

REPEATABLE READ - In contrast to the READ COMMITTED isolation level, REPEATABLE READ places shared locks on all data that is read and holds these locks until the transaction is committed or rolled back.

SERIALIZABLE - SERIALIZABLE is the strongest isolation level, because it prevents all four concurrency problems already discussed. It acquires a range lock on all data that is read by the corresponding transaction.

SNAPSHOT

94
Q

What are the backup methods?

A

Full Database Backup - A full database backup captures the state of the database at the time the backup is started. During the full database backup, the system copies the data as well as the schema of all tables of the database and the corresponding file structures.

Differential Backup - A differential backup creates a copy of only the parts of the database that have changed since the last full database backup. (As in a full database backup, any activity that takes place during a differential backup is backed up, too.) The advantage of a differential backup is speed.

Transaction Log - A transaction log backup considers only the changes recorded in the log. It does not make sense to back up a transaction log unless a full database backup has been performed at least once. There are two main reasons to perform a transaction log backup: first, to store the data that has changed since the last transaction log backup or database backup on a secure medium; second (and more importantly), to properly close the transaction log up to the beginning of the active portion of it. (The active portion of the transaction log contains all uncommitted transactions.)

File (or Filegroup) backup - File (or filegroup) backup allows you to back up specific database files (or filegroups) instead of the entire database. In this case, Database Engine backs up only files you specify. Individual files (or filegroups) can be restored from a database backup, allowing recovery from a failure that affects only a small subset of the database files. You can use either a database backup or a filegroup backup to restore individual files or filegroups.

95
Q

What are the most important Backup Database Statements in SQL?

A

DIFFERENTIAL, NOSKIP/SKIP, NOINIT/INIT, NOFORMAT/FORMAT, UNLOAD/NOUNLOAD, MEDIANAME, MEDIADESCTIPTION, and MEDIAPASSWORD, BLOCKSIZE, COMPRESSION

96
Q

What are the recovery Techniques?

A

Automatic Recovery - Automatic recovery is a fault-tolerant feature that Database Engine executes every time it is restarted after a failure or shutdown.

Manual Recovery - A manual recovery of a database specifies the application of the full backup of your database and subsequent application of all transaction logs in the sequence of their creation.

97
Q

Before you start a recovery process, you should be sure that?

A

The Backup has the data that you want to restore.
The Backup set is usable.

98
Q

What does a recovery model do?

A

A recovery model allows you to control to what extent you are ready to risk losing committed transactions if a database is damaged. It also determines the speed and size of your transaction log backups.

99
Q

What are the recovery models?

A

Full Recovery Model: During full recovery, all operations are written to the transaction log. Therefore, this model provides complete protection against media failure. This means that you can restore your database up to the last committed transaction that is stored in the log file.

Bulk-Logged Recovery Model: Bulk-logged recovery supports log backups by using minimal space in the transaction log for certain large-scale or bulk operations.

Simple Recovery Model: In the simple recovery model, the transaction log is truncated whenever a checkpoint occurs. Therefore, you can recover a damaged database only by using the full database backup or the differential backup, because they do not require log backups. Backup strategy for this model is very simple: restore the database using existing database backups and, if differential backups exist, apply the most recent one.

100
Q

What are some reasons for data loss?

A

Program Errors, Human Errors, Computer Failures, Disk Failures, Catastrophes

101
Q

What is Availability?

A

Ensuring the availability of your database system and databases is one of the most important issues today. There are several techniques that you can use to ensure their availability, which can be divided into two groups: those that are components of Database Engine and those that are not implemented in the database server.

102
Q

What are the two availability techniques that are not part of Database Engine?

A

Using a Standby Server
A standby server is just what its name implies—another server that is standing by in case something happens to the production server (also called the primary server). The standby server contains files, databases (system and user-defined), and user accounts identical to those on the production server.

Using RAID technology
RAID (redundant array of inexpensive disks) is a special disk configuration in which multiple disk drives build a single logical unit. Generally, there are six RAID levels, 0 through 5. Only three of these levels, levels 0, 1, and 5, are significant for database systems.

103
Q

What are the RAID technologies?

A

RAID 0 - specifies disk striping without parity. Using RAID 0, the data is written across several disk drives in order to allow data access more readily, and all read and write operations can be speeded up.

RAID 1, which specifies disk mirroring, protects data against media failure by maintaining a copy of the database (or a part of it) on another disk.

RAID level 5 - is implemented by calculating recovery information about data written to disk and writing this parity information on the other drives that form the RAID array.

Failover Clustering
It is a process in which the operating system and database system work together to provide availability in the event of failures. Microsoft Cluster Service transfers resources from a failing machine to an equally configured target node automatically

Log Shipping
Log shipping allows the transaction logs from one database to be constantly sent and used by another database. This allows you to have a warm standby server and provides a way to offload data from the source machine to read-only destination computers.

104
Q

What are the benefits of Distributed Data?

A

Directly available to people whenever they need it
Allows local users to operate autonomously.
Reduces network traffic.
Makes non-stop processing cheaper.

105
Q

What are the Distributed Data Methods?

A

Distributed Transactions
Data Replication

106
Q

What are Publications and Articles?

A

The unit of data to be published is called a publication.
An article contains data from a table and/or one or more stored procedures.

A publication contains one or more articles.
Each publication can contain data only from one database.

A publication is the basis of a subscription.
This means that you cannot subscribe directly to an article, because an article is always part of a publication.

107
Q

What are the two ways that a subscription can be initiated?

A

Using a Push Subscription
Using a Pull Subscription

108
Q

What are transactional replication?

A

In transactional replication, the transaction log of the system is used to replicate data.
All transactions that contain the data to be replicated are marked for replication. A component called Log Reader Agent searches for marked transactions and copies them from the transaction log on the publisher to the distribution database.

109
Q

What is a Distributed Agent?

A

Distribution Agent—moves transactions to subscribers, where they are applied to the target tables in the subscription databases. All tables published using transactional replication must explicitly contain a primary key. The primary key is required to uniquely identify the rows of the published table because a row is the transfer unit in transactional replication.

110
Q

What is snapshot replication?

A

Snapshot replication is tightly related to a component called Snapshot Agent.
This component generates the schema and data of the published tables and stores them in files Transactional and snapshot replications are one-way replications, meaning the only changes to the replicated data are made at the publishing server

111
Q

What is Merge Replication?

A

Merge replication allows the publisher and subscribers to update data to be replicated. Because of that, conflicts can arise during a replication process. After the creation of a publication at the publication server, Snapshot Agent prepares files containing table schema and data and stores them in the distribution working folder at the distributor site.

112
Q

What is the benefit of Peer-To-Peer Transactional Replication?

A

The entire system scales well
The entire system provides high availability

113
Q

How does Peer-To-Peer replication work?

A

A system that supports peer-to-peer replication scales well because each server serves only local users.

The high availability is based upon the fact that if one or more servers go offline, all other servers can continue to operate because all data they need for read and write an operation is stored locally.

When an offline server is online again, the replication process restarts, and the server receives all data modifications that have happened at the other sites.

Database Management (1 decks)

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