SQL Server 2008: Indexes, Backups, and Recovery

• The www.company.com database is hosted on a SQL Server 2008 server.

Clustered and Non-Clustered Indexes

• Adding a clustered index on the FlightNumber column physically sorts the rows by FlightNumber.
• Adding a non-clustered index on the Airline column retrieves data faster but does not physically sort rows by airline.

Database Backups

• A full database backup is a copy of all the data in the entire database.
• A transaction log backup backs up all the data in the database.
• A differential backup copies only the data that was changed after the last full backup.
• A file or filegroup restore specifies a portion of the database to recover.

Clustered Index Performance

• They improve the performance of queries that return large result sets.
• They improve the performance of queries on columns that are accessed sequentially

Database Recovery Models

• Using the simple recovery model for database backups provides more work-loss exposure than the full recovery model.
• Using the simple recovery model provides less log file backups compared to the full recovery model.

Data Types

• The VARCHAR data type can contain any combination of characters.
• The NUMERIC data type is for numbers with decimal places.
• The INT data type contains only whole numbers.

Keys

• StateID is a primary key in the State table.
• StateIS is a foreign key in the Address table.

User Permissions

• Users can perform unlimited actions on data that is on the server.
• Users can perform unlimited actions on database objects that are on the server.

Index Types

• A clustered index sorts and stores the data rows of a table or view in order, based on the clustered index key.
• A non-clustered index is created on a table or view by using a clustered index or heap.
• A unique index ensures that the index key contains no duplicate values, meaning every row in the table or view is unique.
• A filtered index is a clustered index that is optimized for queries that select a small percentage of rows from a table.

SQL Object Names

• Object names in SQL are not allowed to contain spaces.

Integrity Checks

• The CHECK TABLE command works for InnoDB and MyISAM tables.

Database Designs

• Conceptual database design can help create a data model of an enterprise that is independent of a particular DBMS.

SQL Components and Internet Explorer

• SQL Server application components that return large result sets are accessed sequentially through Internet Explorer.

SQL Keywords

• The INSERT keyword adds rows to a database table.
• UPDATE changes existing data.
• DELETE removes records from a table.

Indexes vs Non-Clustered Indexes

• A clustered index is based on a primary key field.
• A non-clustered index can be based on any other field.

Inserting Values

• The INSERT INTO ... VALUES statement inserts values into a table.

Data Integrity

• Referential integrity helps ensure that no inconsistent data is entered in the database.

ALTER Statement

• The CREATE statement adds a new table in a database.

DATETIME

• The datetimeoffset data type should be used to record the time of day a user has taken an exam with time zone awareness.

External Tables

• External tables cannot be written to with DML commands.

Selecting Data

• SELECT retrieves a set of table data.
• SELECT * returns all columns.

Prototyping

• Prototyping is the process of building working models of a database system.

Recovery Situations

• Tablespace Point-in-Time Recovery (TSPITR) should be used to recover a tablespace that contains rollback segments, a table that has become logically corrupted, and a DML statement that has affected only a subset of the database.

DDL Statements

• The ALTER statement is used to rename a column in a database table.
• The DROP TABLE command removes one or more table definitions, data, indexes, triggers, constraints, and permission specifications for those tables.

Composite Primary Key

• The ChapterId and LanguageId columns should selected from the Chapter and Language tables to create a composite primary key for the ChapterLanguage table.

Defining a Column

• Data type and name are required to define a column.

Stored Procedures

• EXECUTE permission is needed to run a stored procedure.

Foreign Keys

• The ALTER TABLE statement helps to remove a foreign key.

DELETE vs TRUNCATE

• DELETE allows identification of specific records.
• TRUNCATE TABLE deletes all data in a table.

Copying Records

• SELECT INTO is used to copy records from an existing table to a new table.

INSERT Statements

• INSERT statements operate on tables.

DML SQL Statements

• The SELECT, DELETE, and UPDATE commands support correlated subqueries.

Order in queries

• The WHERE clause restrict the result set.

Binary Log Files

• Binary log files store updates that have been made after the backup was made.

Creating a Product Table

• An attribute declared as a primary key cannot contain NULL values.
• ProductName is declared as Varchar without specifying the width of the column.

Combining Query Results

• The UNION statement combines the results of two queries into a single result.

Deleting Tables

• When rows in the Order table are deleted and the corresponding rows in the OrderItem table are automatically deleted, it is an example of cascade delete.

Security

• Encryption adds an additional security layer to prevent unauthorized viewing of data with the help of an algorithm called cipher.
• The client computer is the first line of defense against SQL injections and weak input validation. The client computer should be formatted with NTFS to protect program, database and log files from unauthorized access.

Approaches

• The mixed approach decomposes the components of a database to some extent.
• Database planning is a management activity that allows the stages of the database application to be realized effectively.
• Database design creates a design for the database that will support the enterprise’s required database system. -
• To find rows that meet a specific condition, use the WHERE keyword in a select statement.
• One reason to create a stored procedure is to improve performance.
• If you are designing and developing a database for a stock exchange. Sensitive data can use encryption techniques for protection.

Database Timezone

• Run the "shell> mysql_tzinfo_to_sql /ust/share/zoneinfo | mysql -u root mysql" statement if you need to ensure that a system use the same MySQL timezone.
• A VARCHAR (30) data type should be used to stores product names that vary from three to 30 characters and minimize of storage space.

Table Population

• The INSERT, SQL*Loader,and MERGE are all statments that populate a table.

SQL1

• James create a Windows Authentication login for each domain user and configure the logins as database users for the appropriate database. Add each database user to the db_datareader and db_datawriter database roles to configure access to a seperate database for each departmen.

Security & Authorization

• GRANT SELECT is the command to give a user to be able to read data in a table.

Data Definition Language

• DROP is the data definition language (DDL) keyword to delete a database table.
• An update statement does not remove rows from a table.

Indexes

• Change the fillfactor for the indexes to 60 to minimaze the amount of fragmentation due to page splits when managing a large database.

Data Integrity

• Foreign Key should be defined to establish a realtionship between the data that is in two tables.
• 2NF is represented where in a table, a candidate key and an atttribute that is not a constituent of the candidate key. The non key attribute depends upon the whole.
• You can update the database by the UPDATE statement.
• Authentication helps with user identity & validation.
• Abstraction enables a user to use generic methods to access a database and to focus on the coding.

INSERT Statement

• The table definition is used with the INSERT statement.
• ALTER is the correct statement to add a colunm to an existing table.
• An index is a database structure that is a term to describe “bitmap,” “b-tree,” and “hash”.
• CREATE is the correct command to create a database.
• SELECT INTO is the statement that is used to copy records from one table into an existing table.
• To create a composite key: CREATE TABLE TableName(Column1 DataType, Column2 DataType, PRIMARY KEY(Column1, Column2));
• The relationship between foreign and primary keys is that: a foreign key constraint with a primary key enforces referential integrity, ties attribute(s) to the primary key of another entity and creates a link between two entities.
• Object modeling is used to record an object/entity so that it is represented visually.
• One approach is the bottom-up approach.

Key and Identifier

• If you are planning a datatabase and identifying key objects ore entities ahe key step is object identification.
• It checks that no record in a child table can exist if its corresponding record is not available in the parent table is true about referential integrity.
• The process extracting and using the trails on a regular basis is called Native Auditing.

Error

• Attempting to retrieve data with a query, results in an error.

ER MODEL

• To create/design a database where the database is structured in a fixed format the realtional model is used.
• CREATE USER should be used to enable a new employee to authenticate.
• TRUNCATE and COMMIT are the 2 commands that cannot be rolled back.

Data Management and Relationships

• UPDATE and DELETE statements are valid data manipulation language (DML) commands. The DDL commands create, remove, and modify database structures.
• Use data type in data validation.
• LOCK prevents the current database operation from reading or writing a data item while the data item is being accessed by another operation.
• UPDATE is used to change data.
• If your server crashed and made it back online, perform a table check operation.
• One to many is the relationship between regional center and study center.

SQL Syntax and Functions

• The UNION keyword in SQL is used to combine the results of two queries and returns only rows that appear in both result sets; INSTERSECT may also be used for this function.

Redundancy

• During normalization this process breaks table into smaller tables.
• The use can also provide authorization and authentication by the Database Planning as requirements collection analysis and application design.

Table Definitions

• DML statements need to operate, insert statements operate.
• INSERT, SQL*Loader,and MERGE statements can be sued to polulate a table.
• Tables contain data, and contain three rows and 100 columns to store the a table of 100 student ID.

Relationships

• A Functional delete is a cascade delete.
• A foreign key is used to apply to a table and an existing department.
• Composite can make the statement where the statement helps create a composite key.
• To add another column to an existing table, DML statement you'd use is ALTER.

Backups on MySQL Database

• The shell> mysql_tzinfo_to_sql /ust/share/zoneinfo | mysql -u root mysql script is to helps convert Unix-type zone files into SQL statements, and loads the time zone tables in a mysql database?
• The ideal time to back up dynamic log files is when the server is stopped.

User Security

• Abstraction provides a set of permissions to new users.

Tables, Keys and Constraints

• Primary keys in a data table ensure a unique value in a column.
• Stored procedure: a named group of SQL statements.
• Before running DELETE it is a good idea to run a SELECT statenent.

Hotspot

• Azure Cognitive Services should be selected to parse the image.
• Azure Data Factory should be selected to upload the data to the database

Database Planning

• The correct database planning that helps to determine the requirements through interviewing is gathering information.

Authentication on MySQL

• You would perform, "Start the server with the –skip-networking option," to disable connections from the remote clients.
• The value of the primary key cannot be NULL, The value of the primary key must be unique,The natural key contains highly sensitive data, and The value of the primary key must be persistent can be used to difine an artificial key for use as a primary key in the Info table

Performance

• You would want to customize the stored procedure with type specific and length-restricted parameters for preventing unauthorized changes.
• One way to add an index is to improve performance.
• Index makes searches more efficient.

Trigger

• You'll need to rollback trgrDeleteCustoemr which has an AFTER the deletion of the rows
• A view is stored statement.

Keys and Restores

• Primary Key uniquely identify a row in a table.
• Restore restores the correct backup to a damaged or corrupted databased.

DML Commands

• INSERT, DELETE, UPDATE, SELECT INTO are all valid DML commands.

JOIN

• Join command to retrieve data from two related database tables based on a column that exists in both tables

View

• One statement that best defines a view is a query.
• If you remove the products in furtinture they will appear empty because it will be deleted and the result after the execution the result set of the view is empty

Data validation

• The model for database management is a database model based on first-order predicate logic.

Relationships

• A functionally dependent is the relationship between productID and productsCategory.

Database Transaction

• Provide reliable units of work which allow correct recovery from failures and keeps the database consisting: Database Transactions

Other Keypoints

• CREATE, ALTER, DROP are types of Database.