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
2
Q
query
A
an inquiry into the database that returns information back from the database
3
Q
database servers
A
contain databases so they can be accessed by multiple users and provide a high level of performance
4
Q
DBMS - database management system
A
a collection of programs that indirectly enables you to enter, organize, and select data in a database
5
Q
3 types of databases
A
- Flat-type databases
- Hierarchical databases
- Relational databases
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
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
8
Q
a record refers to…
A
a row
- each individual entry that exists in a table
- horizontal entity
9
Q
hierarchical database
A
- similar to a tree structure
- each “parent” table can have multiple “children”
- each child can have only one parent
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
11
Q
Database objects are inherently divided into two broad categories…
A
storage and programmability
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
13
Q
types of constraints
A
- unique
- check
- default
- not null
- primary key
- foreign key
14
Q
unique constraint
A
allows the database administer to specifically identify which column should not contain duplicate values
15
Q
check constraint
A
allows the administrator to limit the types of data a usere can insert into the database
16
Q
default constraint
A
is used to insert a default value into a column if no other value is specified
17
Q
not null constraint
A
ensures that data is entered into a cell - cell cannot be blank
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
19
Q
foreign key constraint
A
-points to a primary key in another table
20
Q
DML stands for
A
Data Manipulation Language
21
Q
Data Manipulation Language
A
is the language element that allows you to use the core statements INSERT, UPDATE, DELETE, MERGE, SELECT
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
23
Q
INSERT statement
A
adds one or more new rows to a table or a view
24
Q
UPDATE statement
A
- updates data
- changes existing data in one or more columns in table or view
25
DELETE statement
Removes rows from a table or view
26
MERGE statement
Performs insert, update, or delete operations on a target table based on the results of a join with a source table
27
DDL means
Data Definition Language
28
data definition language definition
deals with creating database objects like tables, constraints, and store procedures
29
core DML statements
- SELECT
- INSERT
- UPDATE
- DELETE
- MERGE
30
core DDL statements
- USE
- CREATE
- ALTER
- DROP
- TRUNCATE
- DELETE
31
USE statement
changes the database context
32
CREATE statement
creates a SQL Server database object (database, index, view, table, or procedure)
33
database object
- database
- index
- view
- table
- procedure
34
ALTER statement
- changes an existing object
- adds, deletes, or modifies columns in a table
- or changes the data type in a table
35
DROP statement
- Removes an object from the database
| - deletes column, constraint, database, index, table, view
36
TRUNCATE statement
- removes data from inside a table, leaves the table
| - frees the space used by those rows
37
DELETE statement
- remove rows from a table
| - does not free up the space used
38
when to use TRUNCATE
if you are deleting data from tables in a large database
39
when to use DELETE
if you are deleting data from smaller databases
40
three types of files used to store databases
- primary data files
- secondary data files
- transaction log file
41
primary data files
- .mdf extension
- first files created in a dabase
- contain user-defined objects (tables, views) and system tables
42
secondary data files
- .ndf extenstion
| - on separate physical hard disks to give a database more room
43
transaction log file
- .ldf extension
| - don't contain any objects such as tables or views
44
database index
data structure that improves the speed retrieval operations on a database table
45
tables
- two-dimensional objects consisting of rows and columns
| - used to store data
46
SSMS (SQL Server Management Studio)
primary tool to manage a server and its databases using a graphical interface
47
data type
an attribute tha specifies the tpe of data an object can hold, as well as how many bytes each data type takes up
48
if two data types differ only in how many bytes each uses...
the more bytes usually has a larger range of values and/or increased precision
49
predefined data types
built in data types
50
Exact numeric data types
- most common SQL Server data types used to store numeric information
- whole numbers only
51
int (integer numeric data type)
- primary integer data type (whole number)
- used to store mathematical computations
- used when no decimal point is required
52
precision (p)
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)
53
money and small money
data types used to represent monetary or currency values
54
approximate numeric data types
not as commonly used
55
date and time data types
deal with dates and times
56
built-in data types are organized into what general categories
- exact numbers
- approximate numbers
- date and time
- character strings
- unicode character strings
- binary strings
- other data types
- CLR data types
- spatial date types
57
money data type
used in places where you want money or currency involved in your database - doesn't calculate percentages
58
datetime data type
store date and time data in different formats
59
Varchar data type
-character string data type supporting English attributes
60
Boolean data type
bit data type
61
Float data type and Real data type
- used in conjunction with floating-point numeric data type
| - considered approximate-number data type
62
exact numeric data types examples
- int
- bigint
- bit
- decimal
- numeric
- money
- small money
63
approximate numeric data types
- more precision
| - includes decimals
64
implicit conversion
when a conversion (converting values from one data type to another) is done automatically
65
cast and convert
used to force conversion
66
explicit conversion
when a conversion is forced using callout function
67
regular character string
- uses one byte of storage for each character
- allows you to define one of 256 possible characters
- accommodates English and European languages
68
unicode character string
- uses two bytes of storage per character
- represent one of 65,536 characters
- accommodates most languages
69
view
- a virtual table consisting of different columns from one or more tables
- stored in a database as query objects
- security mechanism
70
stored procedure
- previously written SQL statement that has been "stored" or saved into a database
- used to save time
71
SQL injection
an attack in which malicious code is inserted into strings to be passed on later when parsing or executing statements
72
primary form of SQL injection
direct insertion or code into user-input variables that are concatenated with SQL commands and then executed
73
VAR element
-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
74
nchar and nvarchar
are unicode characters strings can be fixed or variable
75
purpose of a table
provide a structure for storing data within a relational database
76
purpose of a table
provide a structure for storing data within a relational database
77
The SQL command for retrieving any data from a database the data
SELECT
78
what three things do you need to identify in your statement in order to form a proper SELECT query
- what columns to retrieve
- what tables to retrieve them from
- what conditions, if any, must satisfy
79
BETWEEN clause
- allows you to specify the range to be used in a "between x and y" query format
- select values within a given range
80
NOT keyword
is used to search data in terms of what you don't want in your output
81
UNION clause
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
82
EXCEPT clause
returns any distinct values from the left query that are not also found on the right query
83
INTERSECT clause
returns any distinct values not found on both the left and right sides of this operand
84
JOIN clause
allows you to combine related data from multiple table sources
85
to insert data...
- you can use SSMS
| - or the INSERT statement
86
UPDATE statement
- changes data in a table or a view
| - updates existing rows in a table
87
DELETE statement
- can identify and delete individual rows
| - removes rows from a table or a view
88
TRUNCATE TABLE statement
removes actual data from within a table but leaves the table structure in place for future use
89
DROP TABLE command
- can remove entire table
90
compare EXCEPT and INTERSECT
- -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
rules that apply to use EXCEPT and INTERSECT
- the number and order of the columns must be the same in all queries
- the data types must be compatible
92
how is JOIN similar to EXCEPT and INTERSECT
they return values from two separate table sources
93
three types of JOIN statements
- inner joins
- outer joins
- cross joins
94
inner joins
- most common
| - allow you to match related records taken from different source tables
95
outer joins
can include records from one or both tables you are querying that do not have any corresponding records in the other
96
three types of outer joins
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
97
cross joins
return all rows from one table along with all rows from the other table WHERE conditions should always be included
98
INSERT clause
INSERT INTO - inserts new rows in a table
99
INSERT clause syntax
```
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
UPDATE clause
allows you to modify data stored in tables using data attributes
101
UPDATE clause syntax
UPDATE
SET =
WHERE
102
DELETE statement syntax
DELETE FROM
| WHERE
103
TRUNCATE TABLE statement syntax
TRUNCATE TABLE
104
attribute
A property of an entity
105
DROP TABLE statement syntax
DROP TABLE
106
using transactions when updating data...
-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
Normalization
- 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
referential integrity
- 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
First Normalization Form (1NF)
- 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
NONKEY ATTRIBUTE
An attribute is a nonkey attribute if it is not a part of the primary key, candidate key
111
Second Normal Form (2NF)
- 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
Third Normal Form (3NF)
- 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
First Normalization Form (1NF)
- 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
fields refer to
columns
115
Second Normal Form (2NF)
- 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
Third Normal Form (3NF)
- 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
SQL logical operators
- ALL
- AND
- ANY
- BETWEEN
- EXISTS
- IN
- LIKE
- NOT
- OR
- SOME
118
SQL command for retrieving any data from a database is
SELECT
119
SELECT statement syntax
SELECT
FROM
WHERE ....
120
second normal form is violated when...
- an attribute depends on only part of the key
| - -resolved by moving the nondependent attribute to a new entity
121
third normal form is violated when...
the attribute depends on the key but also on another nonkey attribute
-resolved by moving the non dependent attribute to a new entity
122
3 different types of constraints that ensure that you are able to maintain database integrity
- primary key
- foreign key
- composite (unique) key
123
unique key constraint
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
second normal form is violated when...
- an attribute depends on only part of the key
| - -resolved by moving the non-dependent attribute to a new entity
125
third normal form is violated when...
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
draw back to indexing
time it takes to build the actual indexes and the storage space the indexes require
127
how many clustered indexes is allowed for each table
one
128
clustered index
-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
transitive dependencies
similar to partial dependency - they both refer to attributes that are not fully dependent on a primary key
130
non-clustered index
- contains the non-clustered index key values
| - each of those keys has a pointer to a data row that contains a key value
131
database security
should ensure the users' assigned rights and responsibilities are enforced through a security plan
132
permission
used to grant an entity (such as a user) access to an object (such as another user or a database)
133
securables
different objects (defined databases, tables, logins, users, and roles) can be granted permissions to access different securables
134
login or logon
- 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
does a user with a fixed database role that has administrative-level permissions have access data?
a user still cannot access data without first being granted permission to the database object themselves(ie tables, stored procedures, views, functions)
136
user account
a logical representation of a person within an electronic system
137
users can login to SQL Server using
- a Windows domain login (user login)
- a username login (member of user group)
- or SQL Server-specific login
138
sysadmin
a role where that user has full access to every server function, database, and object for that server
139
with full access as a sysadmin
the user can grant other users permissions to all server securables
-they can perform a variety of system-level actions
140
"guest" user account
- 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
user-defined roles
-are custom roles that serve as groups
| -
142
each objects permission is assigned by
granting, revoking, or denying user login persmissions
143
authentication
is the act of establishing or confirming a user or system identity
144
granting permission
means that a user can access the object
145
denying permission
overrides a granted permission
146
revoking a permission
removes the permission that has been assigned, regardless of whether it was a denied permission or a granted permission
147
3 types of accounts available for the SQL Server service account
- local user account
- local system account
- domain user account
148
local user account
- used if access to the network is not required
| - cannot be used outside the server environment
149
local system accounts
- single-server installation
| - can create a security hole
150
domain user accounts
- recommended login account
| - can grant administrator rights to the SQL Server account
151
SA account
-built-in SQL administrator account associated with SQL authentication
152
system admin role
- can perform any activity in the SQL Server installation, regardless of the permission setting
- can override denied permissions on an object
153
public role
- 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
assigning a default database to a user
does not automatically grant access to that database
155
a user can be granted access to databases in
the Database Access tab
156
3 kinds of database server roles
- fixed roles
- the public role
- user-defined roles
157
fixed server roles are best used for?
-delegating certain server administrative tasks
158
server role
roles grant permission to perform certain server-related administrative tasks
159
bulkadmin
can perfomr bulk insert operations
160
Dbcreator
can create, alter, drop, and restore databases
161
Diskadmin
can created, alter, and drop disk files
162
Processadmin
can kill a running SQL Server process
163
Securityadmin
can manage the logins for the server
164
Serveradmin
can configure the server-wide settings, including setting up full-text searches and shutting down the server
165
Setupadmin
can configure linked servers, extended stored procedures, and the startup stored procedure
166
Sysadmin
can perform any activity in the SQL Server installation, regardless of any other permission setting - even overrides denied permissions on an object
167
User-defined roles
- 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
db_owner
a special role that has all permissions in the database
169
application role
a database -specific role intended to allow an application to gain access regardless of the user
170
purpose of a database backup
is to have something to restore if data is lost during a business's daily routine
171
a full backup contains
all the data in a specific database or set of filegroups or files to allow recovering that data
172
differential backup
only backs up data since the last full backups
173
incremental backup
only backs up data since the last full or incremental backup
174
how to restore from differential backup
- first restore the preceding full backup
| - then restore the last differential backup
175
how to restore from incremental backup
- first restore the preceding full backup
| - then restore each incremental backup since the full backup in order
176
object security
- 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
fixed database roles
- primarily organize administrative tasks
| - user may belong to multiple
178
fixed database roles include
- db_accessadmin
- db_backupoperator
- db_datareader
- db_datawriter
- db_ddladmin
- db_denydatareader
- db_denydatawriter
- db_owner
- db_securityadmin
179
db_accessadmin
authorizes a user to access the database, but not manage database-level security
180
db_backupoperator
allows a user to perform backups, checkpoints, and DBCC commands, but not restores
181
db_datareader
- authorizes a user to read all data in the database
| - can be overridden by a deny permission
182
db_datawriter
allows user to write to all data in the database
| -can be overridden by a deny permission
183
db_ddladmin
authorizes a user to issue DDL commands (create, alter, drop)
184
db_denydatareader
permits a user to read from any table in the database
| -overrides any object-level grant
185
db_denydatawriter
blocks a user from modifying data in any table in the database
-overrides any object-level grant
186
db_owner
- has all permissions in the database
| - object level deny will override membership of this role
187
db_securityadmin
permits a user to manage database-level security
| -includes roles and permissions
188
application role to allow an application to gain access regardless of its user
data-base specific role intended
189
object permission
are permissions that allow a user to act on database objects, such as tables, store procedures, and views
190
types of object permissions
- select
- insert
- update
- delete
- DRI (References)
- execute
191
object permission select
the right to select data
192
object permission insert
the right to insert data
193
object permission updates
the right to modify existing data
194
object permisssion deletes
the right to delete existing data
195
DRI (references)
the right to create foreign keys with DRI
196
object permission execute
the right to execute stored procedures or user-defined functions
197
object permissions are assigned withe the SQL DCL commands
- GRANT
- REVOKE
- DENY
198
SQL Server offers three recovery models
- simple recovery
- full recovery
- bulk-logged
199
why does simple recovery require the least administration
the transaction log backups are truncated on a regular basid
200
full recovery
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
bulk-logged
- least used recovery method
- compromise between full and simple
- no point-in-time recovery
202
database backup
a full backup of the whole database
| -database backups repreent the whole database at the time the backup finished
203
differential database backup
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
partial database backup
a backup of all the full data in the primary fiilegroup, every read/write filegroup, and any ooptionally specified read-only fiiels or filegroups
-
205
can files in a database be backed up and restored individually
yes
206
types of backups supported by SQL
- database backups
- partial backups
- file backups
207
partial backup
a backup of all the full data in the primary filegroup, every read/write filegroup, and any optionally specified read-only files or filegroups
-
208
differential partial backup
contains only the data that were modified since the most recent partial backup
209
file backup
a full backup of all the data in one or more files or filegroups
210
differential file backup
a backup of one or more files that contain data extents that were changed since the most recent full backup of each file
211
restore scenarios possible in SQL
- 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
index
data structure that improves the speed of data retrieval operations on a database table
-disadvantage need to be created and updated
213
the data in RDBMS is stored in
database objects called tables
214
tables
a collection of related data entries and it consists of columns and rows
215
every table is broken up into smaller entities called
fields
