Database Fundamentals/Collection

Database Fundamentals

Learning Guide

This learning guide supports the Wikiversity course Database Fundamentals, available at http://en.wikiversity.org/wiki/Database_Fundamentals. It has a corresponding reading guide available at http://en.wikipedia.org/wiki/Book:Database_Fundamentals.

Overview

Database Fundamentals introduces database concepts, including relational databases, tables and data types, data selection and manipulation, views, stored procedures, functions, normalization, constraints, indexes, security, and backup and restore.

This course comprises 12 lessons covering database fundamentals. Each lesson includes a combination of Wikipedia readings, YouTube videos, and hands-on learning activities. The course also assists learners in preparing for Microsoft Exam 98-364: Database Fundamentals certification.

Preparation

This is a second-semester, college-level course. Learners should already be familiar with Information Systems concepts. Familiarity with Database Software is also helpful.

Lessons

1. Introduction
2. Tables and Data Types
3. Selecting Data
4. Manipulating Data
5. Views
6. Stored Procedures
7. Functions
8. Normalization
9. Constraints
10. Indexes
11. Security
12. Backup and Restore

See Also

• Databases
• Database Applications
• Database Software
• Exam 98-364: Database Fundamentals
• Information Systems

References

• Microsoft: Database Fundamentals Exam Details
• Microsoft Official Academic Course (2011). Exam 98-364: Database Administration Fundamentals. Wiley. ISBN 9781118026885
• Microsoft Virtual Academy: Database Fundamentals

Educational level: this is a tertiary (university) resource.

Lesson 1 - Introduction

This lesson introduces database concepts.

Objectives and Skills

Objectives and skills for the database concepts portion of Microsoft Exam 98-364 Database Fundamentals include:

• Understanding core database concepts
  • Understand relational database concepts
    • Understand what a relational database is, the need for relational database management systems (RDBMS), and how relations are established
  • Understand how data is stored in tables
    • Understand what a table is and how it relates to the data that will be stored in the database; columns/fields, rows/records
  • Understand data definition language (DDL)
    • Understand how T-SQL can be used to create database objects, such as tables and views
  • Understand data manipulation language (DML)
    • Understand what DML is and its role in databases

Readings

1. Wikipedia: Database
2. Wikipedia: Relational database
3. Wikipedia: SQL
4. Wikipedia: Data definition language
5. Wikipedia: Data manipulation language

Multimedia

1. Microsoft Virtual Academy: Introduction to Core Database Concepts
2. YouTube: Relational Database Concepts
3. YouTube: Intro to SQL
4. YouTube: SQL Data Definition Language
5. YouTube: SQL Server Management Studio Intro

Activities

1. Set up an environment to learn about relational databases:
   • Microsoft SQL Server 2014:
     • Watch YouTube: How To Download and Install Microsoft SQL Server 2014 management studio.
     • Download Microsoft: Microsoft SQL Server 2014 Express.
     • Install Microsoft SQL Server 2014 Express and SQL Server Management Studio on your computer.
   • Microsoft AdventureWorks Sample Database:
     • Watch YouTube: Install Adventure Works 2014 Sample Database SQL Server 2014.
     • Download Microsoft: AdventureWorks Sample Database and SQL Server Script.
     • Install Microsoft Adventureworks Database in SQL Server on your computer.
   • Navigating Microsoft SQL Server 2014:
     • Watch YouTube: Microsoft SQL Server 2014 - Introductory Tutorial.
     • Read Tutorial: SQL Server Management Studio.
     • Practice learning how to use Microsoft SQL Server 2014 and SQL Server Management Studio on your computer.
2. Generate a script from the AdventureWorks database to view the database and tables scripts:
   • Watch YouTube: How to generate scripts for complete Microsoft SQL Server database (including data).
   • Read Microsoft: Generate a Script (SQL Server Management Studio).
   • Practice downloading either a database script or table script to identify DDL (use, create, alter, drop, truncate, delete) and DML (select, insert, update, delete, merge) commands.
3. Create an Entity Relationship (E-R) diagram to display database relations:
   • Watch YouTube: SQL Server Database Diagram in SQL Management Studio.
   • Read Microsoft: Open Database Diagram Designer (Visual Database Tools).
   • In the E-R diagram, identify the displayed tables, columns and relationships.
4. In the Object Explorer, click on the AdventureWorks database to expand folders to display database structure:
   • Watch YouTube: Exploring SQL Server Management Studio.
   • Read Microsoft: Manage Objects By Using Object Explorer.
   • Practice displaying and examining the database tables, columns and data.

Lesson Summary

• A database is an organized collection of data.
• A database is a collection of schemas, tables, queries, reports, views and other objects.
• A query language is a computer language used to make queries (or questions about data) in databases and information systems.
• A database index is a data structure that improves the speed of data retrieval operations on a database table. Indexes are used to quickly locate data without searching every row in a database table every time a database table is accessed.
• A database server is a computer program that provides database services to other computer programs or computers as defined by the client–server model.
• A database management system (DBMS) is a computer software application that interacts with the user, other applications, and the database itself to capture and analyze data. A general-purpose DBMS is designed to allow the definition, creation, querying, update, and administration of databases.
• A flat file database is a database which is stored on its host computer system as an ordinary flat file.
• A hierarchical database model is a data model in which the data is organized into a tree-like structure. This model mandates that each child record has only one parent, whereas each parent record can have one or more child records.
• A database table is a collection of related data held in a structured format within a database consisting of fields (columns), and rows.
• A relational database is a database based on the relational model proposed by E.F. Codd in 1970 where data is stored into one or more tables (or "relations") of columns and rows, with a unique key identifying each row.
• A database constraint restricts the data that can be stored in relations. The two principal rules for the relational model are known as entity integrity and referential integrity.
• A primary key uniquely specifies a tuple within a table. In order for an attribute to be a good primary key it must not repeat.
• A foreign key is a field in a relational table that matches the primary key column of another table. The foreign key can be used to cross-reference tables. Foreign keys do not need to have unique values in the referencing relation.
• SQL Server Management Studio (SSMS) is a software application used for configuring, managing, and administering all components within Microsoft SQL Server. The tool includes both script editors and graphical user interface tools which work with objects and features of the server.
• Xquery is a query and functional programming language that queries and transforms collections of structured and unstructured data.
• SQLCMD is a command line application that comes with Microsoft SQL Server, and exposes the management features of SQL Server. It allows SQL queries to be written and executed from the command prompt.
• Transact-SQL Transact-SQL (T-SQL) is Microsoft's and Sybase's proprietary extension to SQL. T-SQL expands on the SQL standard to include procedural programming, local variables, various support functions for string processing, date processing, mathematics, etc. and changes to the DELETE and UPDATE statements.
• A data manipulation language (DML) is a family of syntax elements similar to a computer programming language used for selecting, inserting, deleting and updating data in a database.
• A data definition language (DDL) is a syntax similar to a computer programming language for defining data structures. Structured query language uses a collection of verbs used to modify the schema of the database by adding, changing, or deleting definitions of tables or other objects.

Key Terms

constraints

Data Definition Language (DDL)

Data Manipulation Language (DML)

database (db)

database management system (DBMS)

database server

flat-type database

hierarchical database

index

relational database

SQLCMD

SQL Server Management Studio (SSMS)

table

Transact-SQL

query

XQuery

See Also

• Microsoft: Designing Tables
• Microsoft: Implementing a Relational Database
• Microsoft: Data Definition Language (DDL) Statements (Transact-SQL)
• Microsoft: Data Manipulation Language (DML) Statements (Transact-SQL)
• TutorialsPoint: DBMS Overview

References

Type classification: this is a lesson resource.

Database Fundamentals

Lesson 2 - Tables and Data Types

This lesson introduces tables and data types.

Objectives and Skills

Objectives and skills for the tables and data types portion of Microsoft Exam 98-364 Database Fundamentals include:

• Create database objects
  • Understand tables and how to create them
    • Purpose of tables; create tables in a database by using proper ANSI SQL syntax
  • Choose data types
    • Understand what data types are, why they are important, and how they affect storage requirements

Readings

1. Wikipedia: Table (database)
2. Wikipedia: Field (computer science)
3. Wikipedia: Data type
4. Wikipedia: Data definition language

Multimedia

1. Microsoft Virtual Academy: Creating Databases and Database Objects
2. YouTube: Database Objects

Activities

1. Create a new sample database.
2. Create a new database:
   • Watch YouTube: How to Create MS SQL Database Using SQL Server Management Studio.
   • Read [1] Microsoft: SQL Server Create a Database].
   • Create a new sample database called College using SQL Server Management Studio.
3. Create tables in sample database:
   • Watch YouTube: SQL Server 2014 How to Create Tables.
   • Read Microsoft: Create Tables Using SQL Server Management Studio (Database Engine).
   • Read W3CSchools.com: SQL General Data Types.
   • Create three tables using SQL Server Management Studio named Student, Course and Instructor.
   • Add three fields (columns) into each table with applicable data types of your choice.
     • For example: Course table could include the CourseID, Description, CreditHours fields. CourseID is the primary key.
     • Student table could include the StudentID, StudentFirstName, StudentLastName fields. StudentID is the primary key.
     • Instructor table could include the InstructorID, InstructorFirstName, InstructorLastName, Department. InstructorID is the primary key.
   • For each table, insert three or five records. In the Student table, insert a student record with your first name and last name.
   • Practice displaying and examining the database tables, columns and data.

Lesson Summary

• Each column in an SQL table declares the data type(s) that column may contain.
• A table is a collection of related data held in a structured format within a database. It consists of fields (columns), and rows.

Key Terms

data type

table

See Also

• Microsoft: CREATE TABLE (Transact-SQL)

References

Type classification: this is a lesson resource.

Database Fundamentals

Lesson 3 - Selecting Data

This lesson introduces selecting data.

Objectives and Skills

Objectives and skills for the selecting data portion of Microsoft Exam 98-364 Database Fundamentals include:

• Manipulate data
  • Select data
    • Utilize SELECT queries to extract data from one table, extract data by using joins, combine result sets by using UNION and INTERSECT

Readings

1. Wikipedia: Select (SQL)

Multimedia

1. Microsoft Virtual Academy: Using DML Statements
2. YouTube: Introducing Query Design
3. YouTube: SQL Server Queries Part 1 - Writing Basic Queries
4. YouTube: SQL JOIN Statement
5. YouTube: SQL GROUP BY, HAVING & ORDER BY

Activities

1. Use SQL Server to list items from the Student and Course tables in your College database.
2. Write the following queries:
   • Watch YouTube: SELECT Statement.
   • Watch YouTube: Introducing Query Designer.
   • Read Microsoft: SQL Server SELECT - SQL Command.
   • Read Microsoft: SELECT Examples (Transact - SQL).
   • Read W3Schools.com: SQL SELECT Statement.
   • Write a query that displays all of the columns and all of the rows from the Course table to display the CourseID, Description, CreditHours fields.
   • Write a query that will display the name of all students from the Student table.
   • Modify the previous query to display only students with your last name.
   • Write a query that will return one column name CourseID, Description and contains the last name of the instructor.
   • Practice creating simple queries using the College database you created.

Lesson Summary

• A SELECT statement retrieves zero or more rows from one or more database tables or database views.
• A SQL join clause combines records from two or more tables in a relational database. A JOIN is a means for combining fields from two tables (or more) by using values common to each. .
• A CROSS JOIN will produce rows which combine each row from the first table with each row from the second table.
• A INTERSECT clause combines the results of two queries and returns only rows that appear in both result sets. For purposes of duplicate removal the INTERSECT operator does not distinguish between NULLs. The INTERSECT operator removes duplicate rows from the final result set.
• A UNION clause combines the results of two SQL queries into a single table of all matching rows. The two queries must result in the same number of columns and compatible data types in order to unite.

Key Terms

cross join

intersect

join

select

union

See Also

• Microsoft: Data Manipulation Language (DML) Statements (Transact-SQL)
• Microsoft: Comparison Operators (Transact-SQL)
• Microsoft: Using Joins
• W3Schools.com: SQL SELECT Statement

References

Type classification: this is a lesson resource.

Database Fundamentals

Lesson 4 - Manipulating Data

This lesson introduces data manipulation.

Objectives and Skills

Objectives and skills for the data manipulation portion of Microsoft Exam 98-364 Database Fundamentals include:

• Manipulate data
  • Insert data
    • Understand how data is inserted into a database, how to use INSERT statements
  • Update data
    • Understand how data is updated in a database and how to write the updated data to the database by using the appropriate UPDATE statements, update by using a table
  • Delete data
    • Delete data from single or multiple tables, ensure data and referential integrity by using transactions

Readings

1. Wikipedia: Insert (SQL)
2. Wikipedia: Update (SQL)
3. Wikipedia: Delete (SQL)

Multimedia

1. Microsoft Virtual Academy: Using DML Statements
2. YouTube: SQL INSERT, UPDATE, and DELETE
3. YouTube: ACID Principles

Activities

1. Use SQL Server to update record information in the Student table in your College database.
2. Write the following queries:
   • Watch YouTube: How to Insert Into Table In Database.
   • Watch YouTube: How to Update Query in Database.
   • Watch YouTube: How to Delete Query in Database.
   • Read [2] Microsoft: Changing Data in a Database].
   • Apply the INSERT statement in a query to insert a new record into the Student table.
   • Apply the UPDATE statement in a query to update the first name field in the previous step in the Student table.
   • Apply the DELETE statement in a query to remove the record that you just added in the Student table.

Lesson Summary

• A DELETE statement removes one or more records from a table.
• An INSERT statement adds one or more records to any single table in a relational database.
• Referential integrity is a property of data which, when satisfied, requires every value of one attribute (column) of a relation (table) to exist as a value of another attribute (column) in a different (or the same) relation (table).
• A transaction symbolizes a unit of work performed within a database management system (or similar system) against a database. A transaction generally represents any change in database.
• An UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.

Key Terms

insert

delete

referential integrity

transactions

update

References

Type classification: this is a lesson resource.

Database Fundamentals

Lesson 5 - Views

This lesson introduces views.

Objectives and Skills

Objectives and skills for the views portion of Microsoft Exam 98-364 Database Fundamentals include:

• Create database objects
  • Create views
    • Understand when to use views and how to create a view by using T-SQL or a graphical designer

Readings

1. Wikipedia: View (SQL)

Multimedia

1. Microsoft Virtual Academy: Creating Databases and Database Objects
2. YouTube: SQL Views

Activities

1. Use SQL Server to create views from the Student and Course tables in your College database.
2. Create the following views:
   • Watch YouTube: How to create a view using Microsoft SQL Server Studio Management.
   • Watch YouTube: SQL Views.
   • Read Microsoft: Create Views.
   • Read W3Schools.com: SQL Views.
   • Create a view that displays all of the columns and all of the rows from the Course table to display the CourseID, Description, CreditHours fields.
   • Create a view that will display the name of all students from the Student table.
   • Modify the previous view to display only students with your last name.
   • Create a view that will return one column name CourseID, Description and contains the last name of the instructor.
   • Practice creating simple views using the College database you created.

Lesson Summary

• A view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object.

Key Terms

view

See Also

• Microsoft: How to: Create Views (Visual Database Tools)

References

Type classification: this is a lesson resource.

Database Fundamentals

Lesson 6 - Stored Procedures

This lesson introduces stored procedures.

Objectives and Skills

Objectives and skills for the stored procedures portion of Microsoft Exam 98-364 Database Fundamentals include:

• Create database objects
  • Create stored procedures and functions
    • Select, insert, update, or delete data

Readings

1. Wikipedia: Stored procedure

Multimedia

1. Microsoft Virtual Academy: Creating Databases and Database Objects
2. YouTube: SQL Stored Procedures
3. YouTube: SQL Server Programming Part 1 - Stored Procedure Basics
4. YouTube: Stored procedures in sql server Part 18

Activities

1. Use SQL Server to create stored procedures to update record information in the Student table in your College database.
2. Create the following stored procedures:
   • Apply the INSERT statement in a stored procedure to insert a new record into the Student table.
   • Apply the UPDATE statement in a stored procedure to update the first name field in the previous step in the Student table.
   • Apply the DELETE statement in a stored procedure to remove the record that you just added in the Student table.

Lesson Summary

• A stored procedure (also termed proc, storp, sproc, StoPro, StoredProc, StoreProc, sp, or SP) is a subroutine available to applications that access a relational database management system (RDMS).
• A SQL injection is a code injection technique, used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker).

Key Terms

SQL injection

stored procedure

See Also

• Microsoft: Create a Stored Procedure
• Microsoft TechNet: Creating Stored Procedures (Database Engine)
• MSSQL: SQL Server Stored Procedures Tutorial

References

Type classification: this is a lesson resource.

Database Fundamentals

Lesson 7 - Functions

This lesson introduces functions.

Objectives and Skills

Objectives and skills for the functions portion of Microsoft Exam 98-364 Database Fundamentals include:

• Create database objects
  • Create stored procedures and functions
    • Select, insert, update, or delete data

Readings

1. Wikipedia: Function (computer science)

Multimedia

1. Microsoft Virtual Academy: Creating Databases and Database Objects
2. YouTube: SQL Functions

Activities

1. Use SQL Server to create built-in functions to calculate data in the Student table in your College database.
2. Create the following built-in functions:
   • Apply the AVG function in a query to display the average number of course credit hours in the Course table.
   • Apply the MAX function in a query to display the maximum number of course credit hours in the Course table.
   • Apply the SUM function in a query to display the total number of course credit hours in the Course table.

Lesson Summary

• A user-defined function (UDF) is a function provided by the user of a program or environment, in a context where the usual assumption is that functions are built into the program or environment.
• An aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning or measurement such as a set, a bag or a list.).

Key Terms

function

See Also

• Microsoft: Aggregate Functions (Transact-SQL)
• W3Schools.com: SQL Functions
• Tutorials Point:SQL Useful Functions

References

Type classification: this is a lesson resource.

Database Fundamentals

Lesson 8 - Normalization

This lesson introduces normalization.

First Normal Form (1NF)

- Data is in 1NF when:

 1. Each column contains atomic (indivisible) values.
 2. Each row is unique, and no column has repeating groups.

Example: Before normalization (not in 1NF):

StudentID	Name	Subjects
1	Alice	Math, Science
2	Bob	History, Math

After normalization (in 1NF):

StudentID	Name	Subject
1	Alice	Math
1	Alice	Science
2	Bob	History
2	Bob	Math

Second Normal Form (2NF)

- A table is in 2NF if:

 1. It is in 1NF.
 2. All non-key attributes are fully dependent on the primary key.

Example: Before normalization (not in 2NF):

OrderID	ProductName	Price	CustomerName
1	Pen	1.00	Alice
2	Notebook	5.00	Bob

After normalization (in 2NF): Orders Table:

OrderID	CustomerName
1	Alice
2	Bob

Products Table:

ProductID	ProductName	Price
1	Pen	1.00
2	Notebook	5.00

OrderDetails Table:

OrderID	ProductID
1	1
2	2

Third Normal Form (3NF)

- A table is in 3NF if:

 1. It is in 2NF.
 2. It contains no transitive dependencies (i.e., no non-key attribute depends on another non-key attribute).

Example: Before normalization (not in 3NF):

EmployeeID	Name	Department	Manager
1	Alice	Sales	John
2	Bob	IT	Sarah

After normalization (in 3NF): Employees Table:

EmployeeID	Name	DepartmentID
1	Alice	101
2	Bob	102

Departments Table:

DepartmentID	Department	Manager
101	Sales	John
102	IT	Sarah

Objectives and Skills

Objectives and skills for the normalization portion of Microsoft Exam 98-364 Database Fundamentals include:

• Understand data storage
  • Understand normalization
    • Understand the reasons for normalization, the five most common levels of normalization, how to normalize a database to third normal form

Readings

1. Wikipedia: Database normalization

Multimedia

1. Microsoft Virtual Academy: Relational Concepts
2. YouTube: Normalization
3. YouTube: Physical Database Design Methodology

Activities

1. While using the types of entities based on the College database (student, course, instructor), convert the entities to an equivalent collection of tables that are normalized and are in fourth normal form.
   • Using this information, convert the unnormalized relation to fourth normal form:
     • Create an example of a table that is in first normal form, but not in second normal form.
     • Create an example of a table that is in second normal form but not in third normal form.
     • Create an example of a table that is in third normal form but not in fourth normal form.

Lesson Summary

• Database normalization is the process of organizing the columns (attributes) and tables (relations) of a relational database to minimize data redundancy. Normalization involves decomposing a table into less redundant (and smaller) tables without losing information; defining foreign keys in the old table referencing the primary keys of the new ones. The objective is to isolate data so that additions, deletions, and modifications of an attribute can be made in just one table and then propagated through the rest of the database using the defined foreign keys.
• First normal form (1NF) is a property of a relation in a relational database. A relation is in first normal form if and only if the domain of each attribute contains only atomic (indivisible) values, and the value of each attribute contains only a single value from that domain. First normal form enforces these criteria: Eliminate repeating groups in individual tables. Create a separate table for each set of related data. Identify each set of related data with a primary key
• Second normal form (2NF) is a normal form used in database normalization. A table that is in first normal form (1NF) must meet additional criteria if it is to qualify for second normal form. A table is in 2NF if it is in 1NF and every non-prime attribute of the table is dependent on the whole of every candidate key.
• Third normal form is a normal form that is used in normalizing a database design to reduce the duplication of data and ensure referential integrity by ensuring that (1) the entity is in second normal form, and (2) all the attributes in a table are determined only by the candidate keys of that table and not by any non-prime attributes.
• Fourth normal form (4NF) is a normal form used in database normalization. Introduced by Ronald Fagin in 1977, 4NF is the next level of normalization after Boyce–Codd normal form (BCNF). Whereas the second, third, and Boyce–Codd normal forms are concerned with functional dependencies, 4NF is concerned with a more general type of dependency known as a multivalued dependency. A Table is in 4NF if and only if, for every one of its non-trivial multivalued dependencies X \twoheadrightarrow Y, X is a superkey—that is, X is either a candidate key or a superset thereof.
• Normal forms 5 and 6 are less commonly used. It is possible to design a database in 5NF from outset and adapt it into 6NF with slight tweaks.
• Data redundancy is the existence of data that is additional to the actual data and permits correction of errors in stored or transmitted data.

Key Terms

fifth normal form (5NF)

first normal form (1NF)

form

fourth normal form (4NF)

normalization

normal forms (NF)

redundant data

second normal form (2NF)

third normal form (3NF)

See Also

• Database Management/Normalization

References

Type classification: this is a lesson resource.

Database Fundamentals

Lesson 9 - Constraints

This lesson introduces constraints.

Objectives and Skills

Objectives and skills for the constraints portion of Microsoft Exam 98-364 Database Fundamentals include:

• Understand data storage
  • Understand primary, foreign, and composite keys
    • Understand the reason for keys in a database, choose appropriate primary keys, select appropriate data type for keys, select appropriate fields for composite keys, understand the relationship between foreign and primary keys

Readings

1. Wikipedia: Database constraint

Multimedia

1. Microsoft Virtual Academy: Relational Concepts
2. YouTube: Database Objects

Activities

1. Use SQL Server to create constraints to# update record information in the Student table in your College database.
2. Create the following constraints:
   • Watch YouTube: Creating Primary and Foreign Keys in SQL Server 2012.
   • Read Microsoft: Create Primary Keys.
   • Read W3CSchools.com: SQL Primary Key Constraint.
   • Read SQL Foreign Key Constraint.
   • Create a constraint making the InstructorID of the Instructor table a foreign key in the Course table.
   • Create a constraint making the StudentID of the Student table a foreign key field in the StudentCourse table.
   • Create a constraint making the CourseID of the Course table a foreign key field in the StudentCourse table.
   • By using SQL Server, Object Explorer, use Database Diagram to create an E-R Diagram. Save the diagram with your database.

Lesson Summary

• A composite primary key containing at least one compound key with at least one other attribute or simple key (this is an extension of a compound key).
• A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table.
• Foreign keys are defined in the ISO SQL Standard through a FOREIGN KEY constraint.
• The key that is selected as the primary key. Only one key within an entity is selected to be the primary key. This is the key that is allowed to migrate to other entities to define the relationships that exist among the entities. When the data model is instantiated into a physical database, it is the key that the system uses the most when accessing the table, or joining the tables together when selecting data.
• A unique key is a set of zero, one, or more attributes in database relational modeling and implementation.

Key Terms

composite primary key

foreign key

foreign key constraint

primary key

unique key constraint

References

Type classification: this is a lesson resource.

Database Fundamentals

Lesson 10 - Indexes

This lesson introduces indexes.

Objectives and Skills

Objectives and skills for the indexes portion of Microsoft Exam 98-364 Database Fundamentals include:

• Understand data storage
  • Understand indexes
    • Understand clustered and non-clustered indexes and their purpose in a database

Readings

1. Wikipedia: Database index

Multimedia

1. Microsoft Virtual Academy: Using DML Statements

Lesson Summary

• A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed.
• Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table. Clustered indices can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected.
• In a non-clustered index, the physical order of the rows is not the same as the index order. The indexed columns are typically non-primary key columns used in JOIN, WHERE, and ORDER BY clauses. There can be more than one non-clustered index on a database table.

Key Terms

clustered index

non-clustered index

References

Type classification: this is a lesson resource.

Database Fundamentals

Lesson 11 - Securigy

This lesson introduces database security.

Objectives and Skills

Objectives and skills for the database security portion of Microsoft Exam 98-364 Database Fundamentals include:

• Administer a database
  • Understand database security concepts
    • Understand the need to secure a database, what objects can be secured, what objects should be secured, user accounts, and roles

Readings

1. Wikipedia: Database security

Multimedia

1. Microsoft Virtual Academy: SQL Server Administration Fundamentals

Lesson Summary

• Authentication is the act of confirming the truth of an attribute of a single piece of data (a datum) claimed true by an entity.
• Database security concerns the use of a broad range of information security controls to protect databases (potentially including the data, the database applications or stored functions, the database systems, the database servers and the associated network links) against compromises of their confidentiality, integrity and availability.
• In computer security, logging in, (or logging on or signing in or signing on), is the process by which an individual gains access to a computer system by identifying and authenticating themselves.

Key Terms

authentication

database security

guest user

login

permission

sa account

server roles

sysadmin

user account

References

Type classification: this is a lesson resource.

Database Fundamentals

Lesson 12 - Backup and Restore

This lesson introduces database backup and restore.

Objectives and Skills

Objectives and skills for the database backup and restore portion of Microsoft Exam 98-364 Database Fundamentals include:

• Administer a database
  • Understand database backups and restore
    • Understand various backup types, such as full and incremental, importance of backups, how to restore a database

Readings

1. Wikipedia: Backup

Multimedia

1. Microsoft Virtual Academy: SQL Server Administration Fundamentals

Lesson Summary

• A backup, or the process of backing up, refers to the copying and archiving of computer data so it may be used to restore the original after a data loss event.
• An incremental style repository aims to make it more feasible to store backups from more points in time by organizing the data into increments of change between points in time.
• Each differential backup saves the data that has changed since the last full backup.

Key Terms

backup

base

data backup

differential backup

full backup

incremental backup

log backup

restore

See Also

• Microsoft: Backing Up and Restoring Databases in SQL Server

References

Type classification: this is a lesson resource.

Database Fundamentals