Database Fundamentals/Collection

Database Fundamentals

edit

Learning Guide

edit

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

edit

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

edit

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

edit
  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

edit

References

edit
  • 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

edit

This lesson introduces database concepts.

Objectives and Skills

edit

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

  • 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

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

Multimedia

edit
  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

edit
  1. Set up an environment to learn about relational databases:
  2. Generate a script from the AdventureWorks database to view the database and tables scripts:
  3. Create an Entity Relationship (E-R) diagram to display database relations:
  4. In the Object Explorer, click on the AdventureWorks database to expand folders to display database structure:

Lesson Summary

edit
  • A database is an organized collection of data.[2]
  • A database is a collection of schemas, tables, queries, reports, views and other objects.[3]
  • A query language is a computer language used to make queries (or questions about data) in databases and information systems.[4]
  • 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.[5]
  • A database server is a computer program that provides database services to other computer programs or computers as defined by the client–server model.[6]
  • 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.[7]
  • A flat file database is a database which is stored on its host computer system as an ordinary flat file.[8]
  • 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.[9]
  • A database table is a collection of related data held in a structured format within a database consisting of fields (columns), and rows.[10]
  • 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.[11]
  • 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.[12]
  • 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.[13]
  • 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. [14]
  • 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.[15]
  • Xquery is a query and functional programming language that queries and transforms collections of structured and unstructured data.[16]
  • 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.[17]
  • 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.[18]
  • 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.[19]
  • 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.[20]

Key Terms

edit
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

edit

References

edit
  Type classification: this is a lesson resource.

Lesson 2 - Tables and Data Types

edit

This lesson introduces tables and data types.


Objectives and Skills

edit

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

  • 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

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

Multimedia

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

Activities

edit
  1. Create a new sample database.
  2. Create a new database:
  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

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

Key Terms

edit
data type
table

See Also

edit

References

edit
  Type classification: this is a lesson resource.

Lesson 3 - Selecting Data

edit

This lesson introduces selecting data.


Objectives and Skills

edit

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

  • 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

edit
  1. Wikipedia: Select (SQL)

Multimedia

edit
  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

edit
  1. Use SQL Server to list items from the Student and Course tables in your College database.
  2. Write the following queries:

Lesson Summary

edit
  • A SELECT statement retrieves zero or more rows from one or more database tables or database views.[2]
  • 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. .[3]
  • A CROSS JOIN will produce rows which combine each row from the first table with each row from the second table.[4]
  • 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.[5]
  • 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.[6]

Key Terms

edit
cross join
intersect
join
select
union

See Also

edit

References

edit
  Type classification: this is a lesson resource.

Lesson 4 - Manipulating Data

edit

This lesson introduces data manipulation.


Objectives and Skills

edit

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

  • 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

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

Multimedia

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

Activities

edit
  1. Use SQL Server to update record information in the Student table in your College database.
  2. Write the following queries:

Lesson Summary

edit
  • A DELETE statement removes one or more records from a table.[2]
  • An INSERT statement adds one or more records to any single table in a relational database.[3]
  • 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).[4]
  • 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.[5]
  • 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.[6]

Key Terms

edit
insert
delete
referential integrity
transactions
update

See Also

edit

References

edit
  Type classification: this is a lesson resource.

Lesson 5 - Views

edit

This lesson introduces views.


Objectives and Skills

edit

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

  • 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

edit
  1. Wikipedia: View (SQL)

Multimedia

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

Activities

edit
  1. Use SQL Server to create views from the Student and Course tables in your College database.
  2. Create the following views:

Lesson Summary

edit
  • 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.[2]

Key Terms

edit
view

See Also

edit

References

edit
  Type classification: this is a lesson resource.

Lesson 6 - Stored Procedures

edit

This lesson introduces stored procedures.


Objectives and Skills

edit

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

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

Readings

edit
  1. Wikipedia: Stored procedure

Multimedia

edit
  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

edit
  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

edit
  • 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). [2]
  • 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). [3]

Key Terms

edit
SQL injection
stored procedure

See Also

edit

References

edit
  Type classification: this is a lesson resource.

Lesson 7 - Functions

edit

This lesson introduces functions.


Objectives and Skills

edit

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

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

Readings

edit
  1. Wikipedia: Function (computer science)

Multimedia

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

Activities

edit
  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

edit
  • 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. [2]
  • 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.). [3]

Key Terms

edit
function

See Also

edit

References

edit
  Type classification: this is a lesson resource.

Lesson 8 - Normalization

edit

This lesson introduces normalization.


Objectives and Skills

edit

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

  • 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

edit
  1. Wikipedia: Database normalization

Multimedia

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

Activities

edit
  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

edit
  • 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. [2]
  • 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[3]
  • 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.[4]
  • 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.[5]
  • 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.[6]
  • 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.[7]

Key Terms

edit
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

edit

References

edit
  Type classification: this is a lesson resource.

Lesson 9 - Constraints

edit

This lesson introduces constraints.


Objectives and Skills

edit

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

  • 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

edit
  1. Wikipedia: Database constraint

Multimedia

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

Activities

edit
  1. Use SQL Server to create constraints to# update record information in the Student table in your College database.
  2. Create the following constraints:

Lesson Summary

edit
  • 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).[2]
  • A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table.[3]
  • Foreign keys are defined in the ISO SQL Standard through a FOREIGN KEY constraint.[4]
  • 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.[5]
  • A unique key is a set of zero, one, or more attributes in database relational modeling and implementation.[6]

Key Terms

edit
composite primary key
foreign key
foreign key constraint
primary key
unique key constraint

See Also

edit

References

edit
  Type classification: this is a lesson resource.

Lesson 10 - Indexes

edit

This lesson introduces indexes.


Objectives and Skills

edit

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

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

Readings

edit
  1. Wikipedia: Database index

Multimedia

edit
  1. Microsoft Virtual Academy: Using DML Statements

Activities

edit

Lesson Summary

edit
  • 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.[2]
  • 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.[3]
  • 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.[4]

Key Terms

edit
clustered index
non-clustered index

See Also

edit

References

edit
  Type classification: this is a lesson resource.

Lesson 11 - Securigy

edit

This lesson introduces database security.


Objectives and Skills

edit

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

  • 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

edit
  1. Wikipedia: Database security

Multimedia

edit
  1. Microsoft Virtual Academy: SQL Server Administration Fundamentals

Activities

edit

Lesson Summary

edit
  • Authentication is the act of confirming the truth of an attribute of a single piece of data (a datum) claimed true by an entity. [2]
  • 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. [3]
  • 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. [4]

Key Terms

edit
authentication
database security
guest user
login
permission
sa account
server roles
sysadmin
user account

See Also

edit

References

edit
  Type classification: this is a lesson resource.

Lesson 12 - Backup and Restore

edit

This lesson introduces database backup and restore.


Objectives and Skills

edit

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

  • 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

edit
  1. Wikipedia: Backup

Multimedia

edit
  1. Microsoft Virtual Academy: SQL Server Administration Fundamentals

Activities

edit

Lesson Summary

edit
  • 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.[2]
  • 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.[3]
  • Each differential backup saves the data that has changed since the last full backup.[4]

Key Terms

edit
backup
base
data backup
differential backup
full backup
incremental backup
log backup
restore

See Also

edit

References

edit
  Type classification: this is a lesson resource.