When a schema is created through a series of successive refinements, starting with the first schema.
Back
The core DDL statements are (6):
Front
ADDUCT: ALTER, DROP, DELETE, USE, CREATE and TRUNCATE
Back
What is the With Execute Owner clause?
Front
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.
Back
What is the NOT NULL constraint?
Front
Requires that data is entered into the cell, it is not allowed to be blank.
Back
Constraints are also referred to as ____ constraints.
Front
Column
Back
What is a two-phase commit system?
Front
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.
Back
Why is it a bad idea to let a foreign key contain a NULL value?
Front
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.
Back
What is DB prototyping?
Front
Building a working model of the DB system in order to suggest improvements or add new features.
Back
What is SQLCMD?
Front
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.
Back
The code DML statements are (5)
Front
MIDUS: MERGE, INSERT, DELETE, UPDATE, SELECT
Back
What is the inside-out approach to db design?
Front
A type of bottom-up approach, the inside-out method begins with identifying a few important concepts then proceeds outward radially.
Back
What is a default constraint?
Front
Used to insert a default value into a column.
Back
Define 3NF
Front
When the table is in 2NF and none of the columns are transitively reliant on another.
Back
What is the primary key?
Front
This uniquely identifies each record in the db.
Back
When querying a database you can obtain faster results from properly ______ tables and views.
Front
indexed
Back
What is the goal of 1NF?
Front
To minimize the inclusion (not prevent) of duplicate information
Back
What are 6 constraint types?
Front
Unique, Check, Default, Not Null, Primary Key, Foreign Key
Back
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?
Front
Use the CREATE or REPLACE VIEW command to redefine it.
Back
What is the CHECK constraint?
Front
Allows the admin to limit the types of data a user can insert into that column of the database.
Back
What is abstraction?
Front
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.
Back
What is XQuery?
Front
A query and functional programming language designed to query collections of XML data.
Back
Name 4 types of decomposition
Front
Top-down, Bottom-up, Inside-out, Mixed
Back
What is the correct way to select an avg of a column?
Front
SELECT AVG("column name") FROM "table name"
Back
What is a non-clustered index?
Front
Same as clustered except the index does not physically rearrange the data.
Back
What's the difference between time, datetime, datetime2, datetimeoffset and smalldatetime?
Front
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.
Back
What is the default length for the CAST function?
Front
30
Back
Define 2NF
Front
2NF is when the table is in 1NF and all remaining columns depend on the primary key
Back
What's the difference between an INNER join and an OUTER join?
Front
INNER will only produce matching rows from both tables while OUTER will join all rows from both tables whether they match or not.
Back
What is native auditing?
Front
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.
Back
What are three things true about views?
Front
1. If a view definition contains the DISTINCT keyword, rows cannot be deleted through the view.
2. The WITH OBJECT IDENTIFIER clause is used to specify a top level (root) object view.
3. The OR REPLACE option is used to change the definition of a existing view without dropping and recreating.
Back
What command do you use to invoke a stored procedure?
Front
Execute
Back
DDL influences _____, while _______ influences actual data stored in tables.
Front
Database objects, DML
Back
Database objects are divided into two categories:
Front
Storage and Programmability
Back
A bit is the T-SQL integer data type that can take a ___ of 1, 0 or NULL.
Front
value
Back
What is a foreign key?
Front
This is a column in one table that points to the primary key in another table.
Back
System views belong to the ______
Front
sys schema
Back
Which DB design process allows you to create a data model independent of a specific DBMS?
Front
Logical DB design
Back
What prefix must you have in front of a string to use Unicode?
Front
N
Back
The MS database server that hosts relational databases is called _____
Front
MS SQL Server
Back
Tables created using the ________ statement are used to store data.
Front
CREATE TABLE
Back
What is a clustered index?
Front
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.
Back
What is the UNIQUE constraint?
Front
Specifically identifies which column should not contain duplicate values.
Back
A regular character uses ___ bytes of storage, whereas a unicode character requires ____ bytes.
Front
one, two
Back
What are the three types of files in SQL?
Front
MDF = Primary data files, NDF = Secondary data files and LDF = Log files
Back
What are 3 characteristics of a simple view?
Front
1. It does not have any usage of SQL group functions or grouping of data.
2. DML operations are allowed on the view
3. It fetches data from one database table only
Back
What is a self-reference?
Front
When the foreign key refers to columns in the same table.
Back
Define 1NF
Front
When all columns in a table are atomic
Back
When is the best time to back up dynamic log files?
Front
When the server is stopped.
Back
In order to use views, you must use the ______ T-SQL statement to show data from the tables.
Front
SELECT
Back
What is TRANSACT-SQL
Front
This is the primary means of programming and manageing SQL Server. When you use an SSMS to perform an action, it is using T-SQL commands in the background to do the work.
Back
Section 2
(49 cards)
What language are triggers written in?
Front
DML or DDL
Back
If you are querying the same table for two different things you'd use a....
Front
UNION
Back
What 2 things speeds up data retrieval?
Front
Primary key constraints and Clustered indexes
Back
The foreign key constraint is a ____ identifer.
Front
Relationship
Back
What does degree refer to?
Front
The number of columns.
Back
Which 4 things always have a related data type?
Front
Column, localvariable, expression and parameter.
Back
A regular character uses how much storage?
Front
1 byte
Back
Can you change the IDENTITY constraint of an existing column with an ALTER statement?
Front
No
Back
What is one thing to consider when creating a view?
Front
Database performance
Back
T/F: A single INSERT statement can be used to add rows to multiple tables.
Front
False
Back
Any ___ permission will always override a GRANT permission.
Front
DENY
Back
What 3 reasons should you consider using a clustered index?
Front
1. Columns contain a large number of distinct values
2. Columns are accessed sequentially
3. Queries return large result sets
Back
A ____ will combine the results of two or more queries into a resulting set that includes all the rows belonging to the query:
Front
UNION
Back
An ___ ____ is the same thing as a CROSS JOIN with a WHERE condition:
Front
INNER JOIN
Back
What's the most efficient way to delete all rows from a table?
Front
TRUNCATE command
Back
What would happen if you had a BETWEEN operator of BETWEEN 'D' and 'F'
Front
You would get all values for D, E but not F.
Back
How do you adjust indexes to reduce fragmentation from page splits?
Front
Set the fillfactor to 60.
Back
Name 3 things which can be used to improve query performance
Front
A primary key, a UNIQUE index and a CLUSTERED index
Back
Application design involves 2 important activities:
Front
transaction design and interface design
Back
What always returns a value but never updates data?
Front
A function
Back
What command would you use to track changes to a table?
Front
ALTER TABLE tablename
ENABLE CHANGE_TRACKING
Back
The core DDL statements are:
Front
CREATE, ALTER and UPDATE
Back
What happens if data is missing for a particular column when designing the INSERT SQL statement?
Front
The INSERT statement uses the default value for the column.
Back
A clustered index usually _____ performance when inserting data.
Front
worsens, because it's constantly sorting it. Improves it for retrieving it though
Back
Which normal form ensures that each attribute describes the entity?
Front
2nf
Back
How would you set fillfactor?
Front
ALTER INDEX ALL ON dbo.OrderDetails REBUILD WITH (FILLFACTOR = 60);
Back
A ____ backup contains only the data that has changed since the last full backup.
Front
Differential
Back
What steps should you take to create full-text searching?
Front
1. Create a full text catalog in the database
2. Create a full text index on the column in question
Back
The sa account is only used in
Front
mixed-mode
Back
All users are automatically members of the ______ database role.
Front
Public
Back
A unicode characters uses how much storage?
Front
2 bytes
Back
How do you suppress the '(1 row affected)
Front
SET NOCOUNT ON
Back
For the CHAR data set, it is a _____ length and uses ___ bytes:
Front
Fixed, N
Back
Which of the following statements are true regarding the procedural data manipulation language?
Front
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.
Back
What is a fillfactor?
Front
It specifies a percentage that indicates how much free space will be in the leaf level of each index page.
Back
Mark works as a Database Designer for Reon Inc. He is assigned the task to create a database for the company. He issues the following query to create the database.
CREATE DATABASE '24342'
What will be the output of the query?
Front
A database will be created.
Back
Which form of database design uses secondary storage media?
Front
Physical database design
Back
T/F: Null is a valid constraint
Front
False, NULL is not a constraint
Back
Use the _____ command to recover data that was accidentally deleted by a user.
Front
Restore
Back
Name 3 levels of security supported by SQL Server
Front
Server, Database and Table
Back
Name the two types of prototyping
Front
Requirements and Evolutionary
Back
When do you pick the DBMS?
Front
Prior to the Logical design phase.
Back
What does the IDENTITY constraint do?
Front
It's used on the primary key to automatically start with 1 and auto-increment by 1.
Back
How do you start a transaction?
Front
Use BEGIN TRAN
Back
Database objects are divide into two categories:
Front
Storage and Programmability
Back
What command allows a Windows account to access SQL-Server?
Front
CREATE LOGIN
Back
SQL server supports ____ conversions without using actual callout functions CAST or CONVERT
Front
Implicit
Back
The ___ role gives access to anything on the SQL server, while the ____ role gives full access to a specific database:
Front
sysadmin, db_owner
Back
What is the bottom up approach?
Front
Breaking down the smaller components so that each describes a basic fragment.