Database Administration Fundamentals Flashcards
What is the UNIQUE constraint?
Specifically identifies which column should not contain duplicate values.
What is the CHECK constraint?
Allows the admin to limit the types of data a user can insert into that column of the database.
What is a default constraint?
Used to insert a default value into a column.
What is the NOT NULL constraint?
Requires that data is entered into the cell, it is not allowed to be blank.
What is the primary key?
This uniquely identifies each record in the db.
What is a foreign key?
This is a column in one table that points to the primary key in another table.
Why is it a bad idea to let a foreign key contain a NULL value?
Because it may be impossible to verify the constraints if a foreign key consists of two or more columns if one of them is NULL.
What is a self-reference?
When the foreign key refers to columns in the same table.
What is XQuery?
A query and functional programming language designed to query collections of XML data.
What is SQLCMD?
A command line application that comes with SQL and exposes the management features of SQL Server. It allows SQL queries to be written and executed from the command prompt.
What is TRANSACT-SQL
This is the primary means of programming and managing SQL Server. When you use an DBMS, like SSMS, to perform an action, it is using T-SQL commands in the background to do the work.
What are the three types of files in SQL?
MDF = Primary data files, NDF = Secondary data files and LDF = Log files
Database objects are divided into two categories:
Storage and Programmability
Tables created using the ________ statement are used to store data.
CREATE TABLE
Constraints are also referred to as ____ constraints.
Column
In order to use views, you must use the ______ T-SQL statement to show data from the tables.
SELECT
DDL influences _____, while _______ influences actual data stored in tables.
Database objects, DML
The MS database server that hosts relational databases is called _____
MS SQL Server
The core DDL statements are (6):
ADDUCT: ALTER, DROP, DELETE, USE, CREATE and TRUNCATE
The core DML statements are (5)
MIDUS: MERGE, INSERT, DELETE, UPDATE, SELECT
System views belong to the ______
sys schema
What are 6 constraint types?
Unique, Check, Default, Not Null, Primary Key, Foreign Key
A bit is the T-SQL integer data type that can take a ___ of 1, 0 or NULL.
value
A regular character uses ___ bytes of storage, whereas a unicode character requires ____ bytes.
one, two
When querying a database you can obtain faster results from properly ______ tables and views.
indexed
What’s the difference between time, datetime, datetime2, datetimeoffset and smalldatetime?
Time is the 24 hr clock, Datetime is accurate to .00333 seconds, datetime2 is accurate up to 100 nanoseconds, datetimeoffset includes daylight savings time and smalldatetime does not keep track of seconds.
What prefix must you have in front of a string to use Unicode?
N
What is the default length for the CAST function?
30
What is native auditing?
The process of extracting trails on a regular basis so they can be transferred to a designated security system where the database admins do not have access, this ensures a certain level of separation of duties and provides evidence that the audit trails were not modified.
What is a two-phase commit system?
A feature of a transaction processing system which enables DB’s to be returned to the pre-transaction state if some error condition occurs. All databases are updated or none of them are.
Name 4 types of decomposition
Top-down, Bottom-up, Inside-out, Mixed
What is the inside-out approach to db design?
A type of bottom-up approach, the inside-out method begins with identifying a few important concepts then proceeds outward radially.
What is the top-down approach?
When a schema is created through a series of successive refinements, starting with the first schema.
What is the With Execute Owner clause?
When creating a stored procedure, this can be used to allow the person running the SP to have the same permissions as the person who owns the SP. This is better than granting SELECT to the user.
What is abstraction?
A method of coding that enables a user to focus on the coding rather than the syntax for a specific database API, allowing them to use generic methods of access as long as they have the additional codes or libraries which fill in the blanks.
What is the correct way to select an avg of a column?
SELECT AVG(“column name”) FROM “table name”
What is a clustered index?
It consists of a root page, intermediate levels and leaf levels in a B-tree structure. Each row contains a valid key and a pointer. A clustered index forces the data in the table to be sorted in the order of the index. Each table can only have 1 clustered index.
What is a non-clustered index?
Same as clustered except the index does not physically rearrange the data.
What are 3 characteristics of a simple view?
- It does not have any usage of SQL group functions or grouping of data.
Which DB design process allows you to create a data model independent of a specific DBMS?
Logical DB design
What’s the difference between an INNER join and an OUTER join?
INNER will only produce matching rows from both tables while OUTER will join all rows from both tables whether they match or not.
What is the goal of 1NF?
To minimize the inclusion (not prevent) of duplicate information
What command do you use to invoke a stored procedure?
Execute
If you have already defined a view on a specific table, then you add columns to that table, what must you do to the view?
Use the CREATE or REPLACE VIEW command to redefine it.
What are three things true about views?
- If a view definition contains the DISTINCT keyword, rows cannot be deleted through the view.
What is DB prototyping?
Building a working model of the DB system in order to suggest improvements or add new features.
When is the best time to back up dynamic log files?
When the server is stopped.
Define 1NF
When all columns in a table are atomic
Define 2NF
2NF is when the table is in 1NF and all remaining columns depend on the primary key
Define 3NF
When the table is in 2NF and none of the columns are transitively reliant on another.
How do you adjust indexes to reduce fragmentation from page splits?
Set the fillfactor to 60.
What is a fillfactor?
It specifies a percentage that indicates how much free space will be in the leaf level of each index page.
How would you set fillfactor?
ALTER INDEX ALL ON dbo.OrderDetails REBUILD WITH (FILLFACTOR = 60);
What would happen if you had a BETWEEN operator of BETWEEN ‘D’ and ‘F’
You would get all values for D, E but not F.
What is the bottom up approach?
Breaking down the smaller components so that each describes a basic fragment.
Which form of database design uses secondary storage media?
Physical database design
What will be the output of the query?
A database will be created.
When do you pick the DBMS?
Prior to the Logical design phase.
Which of the following statements are true regarding the procedural data manipulation language?
It requires users to specify which data is needed and how to obtain it, it is a low-level DML, it requires users to know the data structure used in the db.
What does the IDENTITY constraint do?
It’s used on the primary key to automatically start with 1 and auto-increment by 1.
Database objects are divided into two categories:
Storage and Programmability
What always returns a value but never updates data?
A function
The core DDL statements are:
CREATE, ALTER and UPDATE
The foreign key constraint is a ____ identifer.
Relationship
Name 3 things which can be used to improve query performance
A primary key, a UNIQUE index and a CLUSTERED index
T/F: A single INSERT statement can be used to add rows to multiple tables.
FALSE
Can you change the IDENTITY constraint of an existing column with an ALTER statement?
No
T/F: Null is a valid constraint
False, NULL is not a constraint
Which 4 things always have a related data type?
Column, localvariable, expression and parameter.
SQL server supports ____ conversions without using actual callout functions CAST or CONVERT
Implicit
A regular character uses how much storage?
1 byte
A unicode characters uses how much storage?
2 bytes
For the CHAR data set, it is a _____ length and uses ___ bytes:
Fixed, N
What is one thing to consider when creating a view?
Database performance
How do you suppress the ‘(1 row affected)
SET NOCOUNT ON
An ___ ____ is the same thing as a CROSS JOIN with a WHERE condition:
INNER JOIN
What’s the most efficient way to delete all rows from a table?
TRUNCATE command
How do you start a transaction?
Use BEGIN TRAN
A ____ will combine the results of two or more queries into a resulting set that includes all the rows belonging to the query:
UNION
If you are querying the same table for two different things you’d use a….
UNION
A clustered index usually _____ performance when inserting data.
worsens, because it’s constantly sorting it. Improves it for retrieving it though
What 2 things speeds up data retrieval?
Primary key constraints and Clustered indexes
What 3 reasons should you consider using a clustered index?
- Columns contain a large number of distinct values
Which normal form ensures that each attribute describes the entity?
2nf
What command allows a Windows account to access SQL-Server?
CREATE LOGIN
Any ___ permission will always override a GRANT permission.
DENY
A ____ backup contains only the data that has changed since the last full backup.
Differential
All users are automatically members of the ______ database role.
Public
Use the _____ command to recover data that was accidentally deleted by a user.
Restore
Name 3 levels of security supported by SQL Server
Server, Database and Table
The sa account is only used in
mixed-mode
The ___ role gives access to anything on the SQL server, while the ____ role gives full access to a specific database:
sysadmin, db_owner
What language are triggers written in?
DML or DDL
What happens if data is missing for a particular column when designing the INSERT SQL statement?
The INSERT statement uses the default value for the column.
Name the two types of prototyping
Requirements and Evolutionary
What steps should you take to create full-text searching?
- Create a full text catalog in the database
What command would you use to track changes to a table?
ALTER TABLE tablename
Application design involves 2 important activities:
transaction design and interface design
What does degree refer to?
The number of columns.
