Brainscape's Knowledge GenomeTM

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


See full index

Microsoft Technology Associate (MTA) > Database Fundamentals > Flashcards

Database Fundamentals Flashcards

1
Q

What is a database?

A
  • organised collection of data, typically stored in electronic format
  • allows you to input, organise and retrieve data quickly
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is a query?

A

-inquiry into the DB that returns information back

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

What is an index?

A
  • data structure that improves the speed of the data retrieval operations on a DB table
  • have disadvantage in that they need to be created and updated which requires processing resources and takes up disk space
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What is a database server?

A
  • DB’s often stored on there so they can be accessed by multiple users and provide a high level of performance
  • DB servers do not house any form of application and instead are optimised to serve only the purpose of the DB itself using advanced hardware that can handle the high processing needs
  • Usually racks
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is Microsoft SQL server?

A

A DB server. Has 3 types of files to store DB’s;

  • primary data files have an .mdf extension and are the first files created in a DB. Contain user-defined objects, such as tables and views as well as system tables that SQL server requires for keeping track of the DB
  • if DB becomes too large and you run out of room on first hard disk you can create secondary data files which have a .ndf extension, on seperate hard disks
  • Transaction log file use a .ldf extension and don’t contain objects
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What is a DBMS?

A

-collection of programs that enables you to enter, organise and select data in a database

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

What is a flat file DB?

A
  • 2D tables consisting of rows and columns
  • each column is a field and each row is a record
  • access, performance and queries are quick on a flat file DB
  • .txt file
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What is a Hierarchical DB?

A

-tree structure, each parent table can have multiple children but the children can only have one parent

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

What is a relational DB?

A
  • similar to hierarchical in that data is stored in tables and any new info is added into it without need for reorganisation
  • can have multiple parents unlike hierarchical
  • each column corresponds to an attribute and each row to a record
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

What are the fundamental characteristics of a DB?

A
  • designed to store billions of rows of data
  • limited to the computers available hard disk space
  • optimised to use all the computers available memory to improve performance
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What is a calculated value?

A

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

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

How are DB objects divided?

A

-into storage and programmability

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

What are constraints?

A

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

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

Some types of constraints available?

A
  • unique = allows DB admin to ID which column should not contain duplicates
  • check = allows admin to limit types of data user can insert into DB
  • default = used to insert default value into a column if no other value specified
  • not null = ensures data is entered into cell
  • primary key = uniquely ID’s each record in a DB
  • foreign key = in one table points to primary key in another table (can contain null values)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is self reference?

A

-where a foreign key constraint references columns in the same table

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

What is SSMS?

A
  • SQL Sever Management System
  • primary tool to manage a server and its DB using a GUI
  • can be used to view and optimise DB performance, as well as to create and modify DB’s, tables and indexes
  • includes query analyser which provides GUI based interface to write and execute queries.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What are the 3 things SSMS query analyser supports?

A
  • Xquery = query and functional programming language that is designed to query collection of XML data
  • SQLCMD =command line app that comes with SQL server and exposes the management features of it. Allows SQL queries to be written and executed from the command prompt. Can also create SQL script files (.sql) to run a set of SQL statements as a script
  • Transact-SQL = primary means of programming and managing SQL server. When you use SSMS to perform an action or task you are using this
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

What is data manipulation language (Dml)

A
  • the language element that allows you to use the core statements SELECT, INSERT, UPDATE, DELETE and MERGE to manipulate data in any SQL server tables
  • select = retrieves rows from DB and enables the selection of one or many rows or columns from one or many tables
  • insert = adds one or more new rows to a table or a view
  • update = change existing data in one or more columns in a table or view
  • delete = removes rows from a table or view
  • merge =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
19
Q

What is special about DML statements?

A
  • they either succeed or fail
  • if you tried to insert 10,000 records into a table but violated one of the primary key the entire 10,000 rows of records would roll back and not one record would be inserted into the table
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What is Data definition language?

A
  • subset of the Transact SQL language
  • deals with creating DB objects like tables, constraints and stored procedures
  • The interface used to create these underlying DDL statements is the SSMS UI
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What are the limitations of the SSMS when using DDL statements?

A
  • allows you to visually design DDL statements and a DDL script statement task can be completed through its UI
  • but not all the operations you may wish to use can be accomplished through this interface
  • have to be familiar with the statements themselves
  • most DDL statements can be executed through SSMS but using the statements themselves gives you more power, flexibility and control
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What are some key DDL statements?

A
  • USE = changes the DB context, when performing commands you will have to enter the USE command to select the DB first
  • CREATE = creates a SQL server DB object
  • ALTER = change an existing object
  • DROP = removes an object from the DB, error will be raised if other objects are dependent on the object being removed
  • TRUNCATE = removes rows from a table and frees the space used by those rows, better for larger DB’s
  • DELETE = remove rows from a tables but does not free the space used by those rows removed, better for smaller DB’s
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What are system tables?

A
  • keep track of DB
  • when you want to query system views to verify where the object(s) you wish to drop are in the DB tables, you need to know what tables are the most useful
  • system views belong to the sys schema
  • some of those tables include; sys.Tables, sys.Columns, sys.Database, sys.Constraints, sys.Views and sys.Objects
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

What is a data type?

A

An attribute that specifies the type of data an object can hold, as well as how many bytes each type take sup

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

What categories do data types fall into in SQL server 2008?

A
  • exact numbers
  • approx numbers
  • date and time
  • character strings
  • Unicode character strings
  • binary strings
  • other
  • CLR
  • spatial
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Examples of some data types?

A
  • Money = used for money, if you want % best use float. Exact number with fixed precision
  • Datetime = used to store date and time data in different formats. Has datetime which is good for dates 1/01/1753-31/12/9999 accurate to 3.33ms and datetime2 which is good for 1/01/1900-06/06/2079 accurate to 1 min. Use 8 and 4 bytes respectively
  • integer = used to store math computations. Good when no decimal
  • varchar = variable character string good when supporting english attrib. If using foreign use nvarchar
  • boolean = columns stored in 8 bits per byte. Converts true and false string values into 1 and 0
  • float = approx data type
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

What does each column, local variable, expression and parameter always have?

A

Data type and each of the data types is an attribute

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

What 3 things can happen when two expressions have different data types, collation, precision, scale or length?

A
  • when two expressions have different types, rules for data-type precedence specify that the data type with the lower-precedence is converted to the data type with the higher precedence
  • collation refers to a set of rules that determine how data is sorted and compared. SQL server has predefined collation precedence, if you wish to override how data is sorted you must use collation clause
  • precision scale and length of the result depend on the precision, scale and length of those values you are performing the operations on
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

What are exact numeric data types?

Examples?

A
  • most common SQL server DT, used to store numeric info
  • some allow only whole numbers, others allow decimals
  • int
  • bigint
  • bit = does 2 things; 1) optimises storage of bit columns meaning <=8 bits turn into a byte, 9-16 into 2 bytes etc 2) TRUE and FALSE string values converted to 1 and 0 respectively
  • decimal and numerical have fixed precision (max number of digits that can be stored left and right of decimal point) and scale (max number of digits that can be stored to right of decimal point). expressed as decimal/numerical[(p[ ,s])]
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

What are approx numeric DT’s?

A
  • not used that often
  • used when need extra precision
  • float and real give extra precision at cost of increased storage
  • syntax is float(n) with n being the number of bits used to store the mantissa. Default value of n is 53 and range is 1-53
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

Examples of Date and time DT’s?

A

date = range 01/01/01-31/12/9999 AD
-stores info in string literal format = YYYY/MM/DD
-Takes 3 bytes of data with 1 day of accuracy
-default 1900-01-01
DateTimeOffset = similar to DateTime in using 24 hour clock but also keeps track of time zones
SmallDateTime = Same as DateTime but does not include seconds
Time = defines time based on 24 hour clock and without timezone awareness

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

What is an implicit conversion?

A
  • automatic conversion of a data type
  • eg; multiply an items cost (float) with number of items (integer) the result will be expressed as a float
  • some conversions are not allowed; cant convert DateTime to a float, despite it being represented by a float, as its meant to be a Date and/or time
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

How do you force a conversion?

A

Cast = in compliance with ANSI standards, which allow you to import or export to other DBMS’s
-cast(source-value AS destination-type), cast(count AS float)
Convert = specific to T-SQL but a little more powerful
-CONVERT( data_type [(length)], expression [,style])
-you can specifics how many digits or characters the value will be
-CONVERT(nvarchar(10), OrderDate, 101)

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

What is a character string?

A
  • regular char has 1 byte per character meaning you can define 256 characters
  • Unicode character uses two bytes per character so you can define 65,536 characters; allows you store any language
  • regular character literal is always expressed with single quotes. Unicode character literal needs the letter N prefixing the quotes
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

What is the VAR element?

A
  • SQL server will preserve space in the row in which that element resides based on the columns defined size and not the actual number of characters in the character string itself, plus an extra two bytes for offset data
  • eg; if you want to specify a string that supports a max of only 25 character you would use VARCHAR(25)
  • any data type without VAR element within its name is fixed length
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

What are the pros and cons when using Unicode DT’s?

A
  • storage consumption is reduced allowing for faster read operations
  • possibility of row expansion, leading to data movement outside the current page
  • Means any update on data using variable length DT’s may be less efficient than updates using fixed-length
  • possible to use MAX specifier to define variable length DT’s instead of using max number of characters ID in string
  • eg; when column defined with MAX, a value with the size ID up to a certain threshold is stored inline in the row. A value with a size greater than the threshold is stored externally to the row and ID as a large object (LOB)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

Difference between char and varchar?

A
  • char is ID as char[(n)] and is a fixed length, regular character with length of n bytes. N must be between 1-8000
  • varchar[(n|max)] is variable length that consist of 1-8000
  • both have Unicode variants nchar and nvarchar
  • use nchar when sizes of the column data entries will be similar and nvarchar when they will vary considerably
  • SQL server supports two character string types; regular and unicode (char as opposed to nchar)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

What is an SQL DB?

A
  • central container that retrieves data from many different tables and views
  • can run queries on these data, thereby interacting with the info stored in the DB to obtain the output you require
  • to make DB’s easier to manage your organisation should establish and use a single, consistent standard
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

How do you create tables using ANSI SQL syntax?

A

CREATE TABLE planets (name varchar(5), diameter varchar(50))

INSERT INTO planets (name, diameter) VALUES (‘earth’, 10000)

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

What is a view?

A
  • virtual table consisting of different columns from one or more tables
  • unlike a table, stored in a DB as a query object; therfore it is an object that obtains its data from one or more tables (underlying tables)
  • meant to be a security mechanism as it ensures that users can retrieve and modify only the data seen by them through their permissions, thus ensuring they cannot see or access remaining data in the underlying tables
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

How do views ensure security?

A

Restrict access to;

  • Specific rows and/or columns
  • rows obtained by using joins
  • stat summaries of data in given tables
  • subsets of another view or subsets of views and tables
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

Examples of views?

A
  • subset of rows or columns of a base table
  • union of two or more tables
  • join of two or more tables
  • stat summary of bias tables
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

Why create a view?

A
  • views allow you to limit the type of data users can access
  • reduce complexity for end users so they dont have to write complex SQL queries. Instead you can write them on their behalf and hide them in a view
  • Can create one using SSMS, or by writing a Transact-SQL statement
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

What is a stored procedure?

A

-previously written SQL statement that has been stored or saved into a DB
-saves time and memory If you have to run the same query over and over again
-eg; “exec usp_displayallusers” exec tells SQL server to execute code in the procedure and usp indicates to SQL that it is a user-created stored procedure
-

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

What is SQL injection?

A
  • an attack in which malicious code is inserted intro strings that are later passed on to instances of SQL server waiting for parsing and execution
  • Any procedure that constructs SQL statements should be reviewed continually for injection vulnerabilities because SQL server will execute all syntactically valid queries from any source
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
46
Q

What is the select command?

What is SET NOCOUNT?

A
  • command used to retrieve data from a database
  • select query includes up to 3 parts; columns to retrieve (commas separating each column not spaces), tables to retrieve these columns from, conditions the data must satisfy (if any)
  • only parts that are needed is SELECT FROM
    • ID selects all columns from table

-by default it is set to off but when on it tells the number of rows a query affected

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

What is the purpose of the AND, OR conjunctions?

A
  • allow multiple conditions to be chosen

- WHERE = ‘attribute1’ OR(/AND) ‘attribute2’

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

What is the BETWEEN clause?

A
  • can be used to find a range of values

- much nicer than having; WHERE >= ‘X’ AND <= ‘Y’ instead its; WHERE BETWEEN X AND Y

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

What is the NOT clause?

A
  • excludes data
  • can also write ‘<>’ instead of NOT
  • WHERE <> ‘attribute’ will produce same result as WHERE NOT = ‘attribute’
50
Q

What is the UNION clause?

A

-allows you to combine the results of two or more queries into a resulting single set that includes all the rows belonging to the query in that union
Two rules
-number and order of the columns must be the same in each of the queries in the clause
-data types must be compatible

51
Q

What are the EXCEPT and INTERSECT clauses?

A
  • both designed to return distinct values by comparing the results of two queries
  • same rules apply as UNION
  • Think of it like Venn diagrams. INTERSECT in those items in the middle of the diagram. EXCEPT is those items in only one of the halves (the first query)
52
Q

What is the JOIN clause?

A

-allows you to combine related data from multiple table sources
-can use SELECT clause but this approach is more simplistic
-can be specified in both FROM and WHERE clause but it is most used in the FROM clause
-most common type is INNER JOIN
-can also have self join where a table joins itself
3 types;
Inner join = allows you to match related records taken from different tables
Outer join = can include records from one or both tables you are querying that do not have any corresponding records in the other table. LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN
Cross joins = returns all rows from one table along with all rows from the other table. WHERE conditions should always be included

53
Q

What is the INSERT command?

A

-used to add data to a table
INSERT INTO ()
VALUES ()
-multiple columns and values are separated by commas

54
Q

What is the UPDATE statement?

A

-allows you to modify data stored in tables using data attributes
UPDATE
SET =
WHERE

55
Q

What is the DELETE statement?

A

-deletes one or more rows in a table or view
DELETE FROM
WHERE

56
Q

What is the TRUNCATE TABLE clause?

A

-delete all rows from a particular table
TRUNCATE TABLE
-remove data but leaves the structure in place for later use

57
Q

What is the DROP command?

A

-removes an entire table

DROP TABLE

58
Q

What is referential integrity?

A
  • does not allow the deletion of tables unless all of the related tables are deleted using a cascading delete
  • can also use transactions if data is deleted, truncated or accidentally updated
  • inserting a BEGIN TRAN before a SQL statement and a COMMIT statement at the end to save the changes or a ROLLBACK statement to undo the commands
59
Q

What is normalisation?

A
  • Elimination of redundant data to save space
  • Divides DB into two or more tables and then defines table relationships
  • Reduces locking contention and improves multiple user performance (locks put in place to prevent two users making changes to same record at same time)
60
Q

What is 1NF?

A
  • Eliminate repeating groups
  • no duplicate records, needs a primary key
  • no multivalued attributes
  • entries in the column or attribute must be of the same data type
61
Q

What is 2NF?

A
  • Eliminate redundant data
  • Ensures each attribute describes the entity
  • attributes of the entity in questions, which is not part of candidate key, must be functionally dependent on the primary key
  • No partial dependency; if PK is two attributes the attributes must depend on both parts of the PK not just one
62
Q

What is 3NF?

A
  • Eliminate columns not dependent on key
  • Checks for transitive dependencies; atrrib1 dependent on attrib2 which is dependent on primary key
  • every attrib must provide a fact about the key, the whole key and nothing but the key
  • move non dependent attribute to a new entity
63
Q

What is 4NF?

A
  • involves two independent attributes brought together to form a PK along with a third attribute
  • but if two attributes don’t really uniquely ID the entity without the third the the design violates the 4NF
64
Q

What is 5NF?

A

-provides a method for designing complex relationships involving multiple entities

65
Q

Advantages of normalisation?

A
  • Costs - makes DB’s easier to work with and reduces development costs
  • Usability - Placing Columns I’m the correct table makes it easier to understand a DB and write correct queries, helps reduce design time and cost
  • Extensibility - non-normalised DB is often more complex and therefore harder to modify. Leads to delays in rolling out new DB’s and increases in development costs
66
Q

What is a unique key constraint?

A
  • allows you o enforce the uniqueness property of columns, in addition to a primary key within a table
  • different from primary keys in that one column in a row can contain a NULL value but more than one cannot
  • Table may have multiple unique constraints
67
Q

What are Primary keys

A
  • attribute or set of attributes that can be used to uniquely ID each row
  • Every table must have one
  • must be unique and not null
  • sometimes multiple, each one known as a candidate key
  • candidate keys that are not selected are alternate keys
68
Q

What are foreign keys?

A
  • primary key of one table replicated in another, and all the key attributes duplicated from the primary to the secondary table become known as the foreign key
  • enforcing the foreign key attribute is actually referred to as referential integrity
  • Can create relational integrity issues if the wrong FK is chosen
69
Q

What are composite keys?

A

-occurs when you define more than one column as your primary key

70
Q

What are clustered indexes?

A
  • each table can only have one clustered index that defines how SQL server will sort the data stored inside the table
  • physical construct
  • SQL server auto creates one when the primary key is defined
  • data is stored and sorted in the table/view that is based on their respective key values
  • table within a clustered index
71
Q

What is an index?

A
  • on disk (or stored) structure associated entirely with a table or a view that increases the speed of data retrieval
  • a series of keys is built from one or more columns in each row within a table/view, these keys are then stored in a structure called a B tree that enables SQL reverse to find the rows associated with those defined values very quickly and efficiently
72
Q

What is a non clustered index?

A
  • contains the non-clustered index key values, and each of these keys has a pointer to a data row that contains the key value
  • pointer referred to as a row locator
  • locators structure is dependent on whether the data pages are stored in a heap or as a clustered table
  • if it points to a heap, the row locator is a pointer to the row, but in a clustered table the row locator is then the clustered index key
73
Q

What should be the end result of database security?

A
  • Users assigned rights and responsibilities are enforced through a security plan
  • for small organisations this may be a few roles and all IT users designated as admins
  • for larger organisations this will need to be more complex
  • should consider security plan at early possible time when designing a DB
74
Q

What is a permission?

What is the SQL server security model?

A
  • Used to grant an entity (a user) access to an object (another user or DB)
  • based on ‘securables’; different objects can be granted perms to access different securables . Users are assigned roles, which may in turn grant perms to objects
75
Q

What is a login?

What is a user account?

A
  • process by which individual access to a computer system is controlled by ID of the user through the credentials they provide
  • logical representation of a person within an electronic system
76
Q

What are the 3 ways a user can be ID in SQL server?

A
  • Windows user login
  • Membership in a windows group
  • SQL server-specific login (if server uses mixed-mode security)
77
Q

What are the 3 ways users can log in to a SQL server?

A
  • SQL server login
  • windows domain login
  • username login

-Once user logs in they have whatever admin rights they are granted by their fixed server role

78
Q

What is sysadmin?

A
  • System admin
  • full access to every server function, DB, and object for that server
  • can change other users perms
  • users without this role cant alter DB server configurations or grant access where they shouldn’t be able to
  • user must reconnect to the SQL server instance in order for the full capabilities of the sysadmin role to take effect
79
Q

What are database roles?

A
  • each user is auto a member of the public standard DB role, but user defined roles are custom roles that server as groups
  • these roles may then be granted perms to a DB object, and users may be assigned to a DB user-defined role
  • certain DB fixed roles that also affect object access, such as the right to read and write from the DB
80
Q

How is an objects perms assigned?

A

Through granting, dyeing or revoking user login perms

81
Q

What are the 3 service accounts available for SQL server?

A

Local user account - If you find that access to the network is not actually required, this is the best option because a local user account cannot be used outside server environment
Local system account - if you are using a single-server installation. The SQL server can use the local system account of the OS for perms to the machine. Fails to provide network security for DB because it has privileges inside the OS that the admin account does not
Domain user account - Recommended one because the SQL server can then use the windows account specifically created for it. You then grant admin rights to the SQL server account

82
Q

What is Authentication?

A
  • act of establishing or confirming a user or system Identity
  • windows auth mode is better than mixed-mode because users need not learn yet another password and because this mode leverages the security design of the network
  • Windows auth will also authenticate users within windows user groups
  • Access, roles and perms can be assigned a group and they will apply to any user within that group. Also stores individual usernames so can grant perms at user level as well as group
83
Q

How are users granted access to DB’s in SSMS?

A
  • may be assigned a default DB and language but this does not auto grant access to that DB
  • user may be granted access to DB’s in the DB access tab
84
Q

How do you create a new user using SQL?

A

CREATE LOGIN

85
Q

How do I remove a windows login from SQL server?

A

Using SSMS:
-select security directory, use menu to find and delete the desired user
Using SQL:
-DROP LOGIN

86
Q

What is the sa account?

A
  • the built in SQL admin account associated with SQL auth
  • This is known as mixed-mode, allows you to connect to a SQL server using Windows Auth or SQL server Auth
  • SQL auth is less secure than windows logins, therefore avoiding mixed mode is recommended
  • good for backwards compatibility
87
Q

What are the three kinds of database server roles?

What role categories does SQL server include?

A
  • fixed, public, user-defined

- fixed, predefined, server

88
Q

What are some fixed server roles?

A

Bulkadmin - can perform bulk insert operations
Dbcreator - Can create, alter, Drop and restore DB’s
Processadmin - can kill a running SQL server process
Securityadmin - can manage the logins for the server
Serveradmin - can configure the server settings, including setting up full text searches

89
Q

What is the BUILTINS/Administrator?

A
  • the one user that SQL server auto creates during installation of the software
  • includes all windows users in the windows admin group and allows a choice of what groups or users are added during setup
  • can be deleted or modified as desired after installation
90
Q

How do I assign a user to different server roles using SQL?

A 
-using stored procedure
Sp_addsrvrolemember
[ @loginame = ] ‘login’
[ @rolename = ] ‘role’
-eg;
EXEC sp_addsrvrolemember ‘XPS\Lauren’,’sysadmin’
91
Q

What is the public role?

A
  • fixed role but can have object perms like a standard role
  • Every user is auto a member of the public role and cannot be removed
  • serves as a baseline perms level
92
Q

What are user defined roles?

A
  • cant modify perms assigned to fixed server roles so you may need to grant individual server perms to a user
  • typically employed for users who need to perform specific database functions but whom you dont want to grant a role that would permit them to do more than what they need to
93
Q

How are DB logins worked out?

How do you grant access to a DB from the login side?

A
  • When a login is granted access to a DB, it is assigned a DB username which may be the same as the login name or something else by which the login will be known within the DB
  • use object explorer and the user mapping page of the login properties
94
Q

How do you grant access to a DB from the DB POV?

A
  • use New User Context Menu command Unser the DB>security>Users node to open the DB User-New form
  • enter the login to be added in the login name field and the name by which they will be known in the User Name field
  • you can use the login properties form to grant access to any DB and to assign DB roles
95
Q

How do you grant access to a DB using SQL?

A

USE

CREATE USER

96
Q

What is a guest user?

A
  • A user who wishes to access the dB but has not been declared a user within the DB is auto granted perms of a guest user
  • guest user account is not actually created when a DB is created, must be specifically added
  • must be removed from a DB when no longer needed as they risk for a security breach
97
Q

What is object security?

A
  • if user has access to a DB then perms to the individual DB objects may be granted
  • users may be assigned to multiple roles, so multiple security paths from a user to an object may exist
98
Q

What are some fixed DATABASE roles?

A

-User can belong to multiple
Db_accessadmin - auth’s a user to access the DB
Db_backupoperator - allows a user to perform backups, checkpoints and the DBCC commands but not restores
Db_datareader - auth’s a user to read all data in a DB
Db_datawriter - allows a user to write to all data in the DB

99
Q

How to assign fixed DB roles with SSMS?

A

One of 2 ways

1) by adding the role to the user in the users DB user properties form, either as the user is being created or after they exist
2) by adding Ruth user to the role in the DB role properties dialog. To do so, select role under the databases security node, the use the context menu to open the properties form

100
Q

How to assign diced DB roles with SQL?

A

USE

CREATE ROLE AUTHORIZATION

101
Q

What is an application role?

A
  • a DB specific role intended to allow an application to gain access regardless of its user
  • eg; If a specific VB program is used to search the customer table and it doesn’t handle user ID, that VB program can access SQL server using a hard-coded application role. Thus anyone using that application gains access to the DB
102
Q

What are object permissions?

A

-Permissions that allow a user to act on DB objects, such as tables, stored procedures and views
-assigned with the commands GRANT, REVOKE and DENY
Examples;
-select = the right to select data, can be applied to specific columns
-Insert = right to insert data
-Update = right to modify existing data

103
Q

What is special about the DENY command?

A

Overrides everything regardless of other permissions

104
Q

How do you check security if your environment prohibits mixed-mode security?

A
  • right click SQL server management study and use RUN AS command to run as a different user
  • this requires creation of a dummy user in the windows domain
105
Q

How do I grant object permissions with SQL statements?

A

GRANT
ON
TO ,
WITH GRANT OPTION

106
Q

How to manage roles with SQL?

A

CREATE ROLE ‘role’
DROP ROLE ‘role’
Assign users to role;
EXEC sp_addrolemember ‘role’, ‘username’

107
Q

Why would we make a Hierarchical role structure?

A
  • if security structure is complex
  • the worker role may have limited access
  • the manager role may have all worker rights plus others
  • admin role may have all manager rights plus others
  • change in lower levels affects upper levels, admin is required in only one location rather than loads
108
Q

How would you create a Hierarchical role structure?

A

1) create the worker role and set its perms
2) create manager role and set its perms, add the manager role as a user to the worker role
3) create the admin role, add the role as a user to the manager role

109
Q

When might a DB object access another DB object?

A

-a program might call a stored procedure that then selects data from a table
-a report might select from a view which then selects from a table
-a complex stored producers might call several other stored producers
In these cases the user must have perms to execute the stored procedure or select from the view

110
Q

What is an ownership chain?

A
  • whether the user needs perms to select from underlying tables depends on the ownership chain from the object the user called to the underlying table
  • if chain is unbroken from the stored procedure to the tables, then it can execute using the perms of its owner. User only needs perms to execute the stored procedure, and the procedure use can its owners perms to access tables
  • great for developing tight security where users execute stored procedures but aren’t granted direct permission to any tables
111
Q

What is a database backup?

A

-full copy of DB that gives you something to restore if data is lost during a business daily routine (user accidentally deleting a table)

112
Q

What 3 recovery models does SQL server provide?

A

Simple recovery = requires the least admin since the transaction log backups are truncated on a regular basis
Full recovery = allows you to restore to a point in time since the log files record all SQL transactions and the time they were performed. Negative side is logs can grow a lot
Bulk-logged = least used form, compromise between the two allowing for good performance while using the least long space, cannot do a point-in-time recovery

113
Q

What are the 3 types of backup?

A
  • full backup contains all data in a specific DB or set of filegroups -or files to allow recording that data
  • differential backup is based on the latest full backup of the data, known as the base of the differential. Contains only the data that has changed since the last differential base. Smaller and faster. Takes up large memory overtime
  • Incremental backup is based on the last backup of the data. Contains only the data that has changed since the last full or incremental backup. Smaller and faster than other two, at restore time full backup is restored first, followed by each incremental backup
114
Q

What is a log backup?

A

0After the first data backup regular transaction log backups are required

  • each one covers part of the transaction log that was active when the backup was created and the log backup includes all log records that were not backed up in a previous log backup
  • only full and differential are allowed in Microsoft SQL
115
Q

What is a 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 file groups
  • a partial backup of a read only DB contains only the primary file group
116
Q

What is differential partial backup?

A

-contains only the data that has been modified since the most recent partial backup of the same set of filegroups

117
Q

What are file backups?

A
  • files in a DB can be backed up and restored individually
  • increases the speed of recovery by letting you restore only damaged files without resorting the rest of the DB
  • can be complex therefore should only be used where they clearly add value to your restore plan
118
Q

What is SQL server file backup?

What are Differential file backups?

A
  • full backup of all the data in one or more files or filegroups
  • a backup of one or more files that contain data extents that were changed since the most recent full backup of each file
119
Q

When should backups take place?

A
  • has minimal effect on transactions that are running; therefore they can be run during regular operations
  • During a backup operation, SQL server copies the data directly from the DB files to the backup devices
120
Q

What are some SQL restore scenarios?

A

Complete DB restore = restores entire DB, beginning with a full DB backup, which may be followed by restoring a differential DB backup
File restore = restores a file or filegroup in multi-filegroup DB’s
Page restore = restores individual pages
Recovery only = recovers data that is already consistent with the DB and needs only to be made available

121
Q

What is the restore command?

A

enables you to;
-restore an entire DB from a full backup
-restore part of a DB (partial backup)
-restore specific files or filegroups to a DB (a file restore)
-restore specific pages to a DB (page restore)
-restore a transaction log
-revert back to the point in time captured by a DB snapshot
Eg; RESTORE DATABASE FROM DISK = ‘name of backup’ GO

Microsoft Technology Associate (MTA) (1 decks)

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