This is a list of keywords, their meanings, and related resources for a course offered by Microsoft Virtual Academy on the fundamentals of databases. It includes over 4 hours of video instruction by MS certified trainers. It's a great intro into database terminology, creation, manipulation and management. The four resource links below are to the course itself, to SQL Server Express 2014, to the Microsoft Certification study guide, and to a full backup of the AdventureWorks database. I found these to be the essential items needed to complete this course.

The SQL Server Express 2014 download includes SQL, the Management Studio and advanced features like full index searching. The study guide is a a practice test with a real life scenario to help you prepare for the actual certification. Finally, the AdventureWorks DB is an example of a real world company's data storage and is offered by Microsoft for use in database training.

Note: If you are using 2014 SQL Server, make sure you are going to use the AdventureWorks 2014 database. The DB and DB Server must match. The links below should go to the correct versions.

Level: Beginner
Prerequisites: None
Cost: Free

Resources

• Web Link » Database Fundamentals course on Microsoft Virtual Academy.
• Web Link » Download MSSQL Server with advanced features (SQLEXPRADV_x86_ENU.exe)
• Download  Study Guide for EXAM 98-364: Database Administration Fundamentals
• Web Link » From CodePlex a complete backup of the AdventureWorks DB.

Keywords

Module 1: Introduction

• database (db): a collection of data, organized by records (rows), fields (columns), stored in tables, and saved electronically as database files.
• relational database: a collection of tables of data all of which are formally described and organized according to the relational model.
• DBMS - Database Management System (also RDBMS): Software designed to allow the definition, creation, querying, update, and administration of databases. (MSSQL, MySQL, MSAccess, Oracle)
• database servers: dedicated physical or virtual servers that host the database files
• SQL Server instance: a copy of the sqlservr.exe program that runs as a Windows operating system service and runs a default instance and multiple named instances.
• SQL Server Management Studio (SSMS): A graphical user interface (GUI) used to browse, select, and manage the SQL Server instance.

 

Module 2: Relational Concepts

• relational model: data is organized in relations (tables). Implemented by most modern database management systems.
•  
• normalization: the process of organizing data in a database, creating tables and establishing relationships between tables. This process uses five normalization forms (NFs) to eliminate redundant data.
  • 1NF: Eliminate Repeating Groups
  • 2NF: Eliminate Redundant Data
  • 3NF: Eliminate Columns Not Dependent on Key
  • 4NF: Isolate Independent Multiple Relationships
  • 5NF: Isolate Semantically Related Multiple Relationships
  •  
• Referential Integrity (RI): ensuring that the relationships between your database tables remains synchronized during data modifications.
• one-to-one relationship: an association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field of one, and only one, record in the related table.
• many-to-many relationship: a complex association between two sets of parameters in which many parameters of each set can relate to many others in the second set.
• parent/child relationship: a relationship between nodes in a tree data structure in which the parent is one step closer to the root (that is, one level higher) than the child.
• Primary key constraint: an attribute or set of attributes used to uniquely identify each row
• Foreign key constraint: a column or combination of columns used to establish a link between data in two tables
• Unique constraint: allows you to enforce uniqueness in columns other than the primary key
• Unique Index: ensures the index key contains no duplicate values and that every row in the table or view is unique in someway
• Triggers: complex T-SQL statements used to provide data integrity when table data modified

 

Module 3: Creating Databases and DB Objects

• data type: an attribute that specifies the type of data that an object can hold as well as the number of bytes of information that can be stored in the object
•  
• SQL Server's built-in data type categories:
  • Exact numerics: (bigint, bit, decimal, int, money, numeric, smallint)
  • Approximate numerics: (float, real)
  • Date and time: (date, datetime2, datetime, datetimeoffset, time)
  • Character strings: (char, varchar, text)
  • Unicode character strings: (nchar, ntext, nvarchar)
  • Binary strings: (binary, varbinary, image)
  • Other data types: (cursor, timestamp, uniqueidentifier, table)
  • Large valued data types: (varchar(max), nvarchar(max))
  • Large object data types: (text, ntext, image, xml)
  •  
• Data types:
  • Money: currency values
  • Int: whole numbers and mathematical computations
  • Float: scientific community, approximate-number data type
  • Datetime: date and time values in one of many different formats
  • Char: fixed length non-unicode string data type - n defines the string length
  • Varchar: variable length non-unicode string data type
  • Bit (Boolean): integer - null, 0 (False), or 1 (True)
  • Datetimeoffset: a date combined with time of day and time zone
  •  
• field: a location in a record in which a particular type of data is stored.
• column: an attribute for a row or record.
• row: a record within a table.
• table: a database object consisting of rows and columns.
• record: a data structure that is a collection of fields (elements), each with its own name and type that appear in a table as a group of fields across one row.
• View: a virtual table consisting of different columns from one or more tables.
• stored procedure: a group of Transact-SQL statements that have been compiled and saved so it can be run several times.
•  
• User-defined functions (udf): Three types of functions - takes zero or more parameters, completes an operation, and returns the result of the operation as a value.
• • Scalar: returns a single data value
  • Table-valued: returns a table data type
  • System: Provided by SQL Server, cannot be modified

  

Module 4: Using Data Manipulation Language (DML)

• SQL - Structured query language: a relational database language used in querying, updating, and managing relational databases.
• DDL -  Data definition language: is used to create, modify, or drop relational databases, entities, attributes, and other objects (e.g. views).
• DML -  Data manipulation language: is used to insert, update, and delete data and to query a database.
•  
• Common DML statements:
  • SELECT: retrieve data
  • INSERT: add data
  • UPDATE: modify data
  • DELETE: remove data
  • BULK INSERT: Import a data file
  •  
• Clauses: selectively limit what data is returned
  • USE: select the database
  • FROM: select the table
  • WHERE: select a field
  • AND: multiple fields where both conditions are true
  • OR: multiple fields where either condition is true
  • BETWEEN: within a field where a value is between two values
  • ORDER BY: list the results descending order by the value of a certain field
  • NOT: where the value of a field is not a certain value
  •  
  • UNION: combine rows from multiple SELECT statements
  • EXCEPT: returns distinct values found in one table but not the other
  • INTERSECT: clause returns any distinct values found in both tables
  •  
  • JOIN: combine related data from multiple tables into one result set
    • INNER JOIN: uses a comparison operator to match rows using a common column
    • OUTER JOIN: (left, right, or full) rows from one or both tables even with no matching values
    • CROSS JOIN: return all rows from the left and right table - WHERE necessary.
    •  
• BULK INSERT: can be used to import a data file into a table
• INSERT: add a table
• UPDATE: modify exsiting data in a table
• DELETE: delete rows from a table
•   
• INDEX: three common types allow for faster retrieval of data
  • clustered
  • non-clustered
  • unique
  •   
• Triggers: are used to enforce business rules when data is modified

 

•  

 

Module 5: SQL Server Administration Fundamentals

• Database Administrator (DBA): direct and performs all aspects of database administration
• Securables: the server, database, and objects a database contains
• Principals: the individuals, groups, and processes granted access to SQL Server
• Permissions: granted to a principal for every SQL Server securable
•   
• login: a security principal that can be authenticated by a secure system SQL server access
• database user: mapped to a SQL or Windows login and provides a user or group access to a database
• permissions: database object level access
•   
• Authentication: verifying a user or system identity
  • Windows Authentication: Windows user or group
  • Mixed-Mode: Windows and SQL
  • sa: built in SQL administrator account
•  
• SQL Server Roles: allowed to perform certain tasks (first 3 fixed)
  • Sysadmin: perform any activity on the server
  • Dbcreator: create, alter, drop, restore databases
  • Securityadmin: manage logins and their properties
  • user-defined: create specific permissions and assign to a role
  • Public: all users automatically assigned. Cannot be removed.
•  
• Database Roles: allowed to perform certain tasks (first 3 fixed)
  • db_owner: perform all configuration activities
  • db_datareader: read all data from all user tables
  • db_datawriter: add, delete, or change data
  • user-defined: create specific permissions and assign to a role
  • guest: included in every database. Used for non-account users
•  
• Object Permissions: can be managed using commands.
  • GRANT: provides access to an object
  • DENY: denies access to an object
  • REVOKE: removes the previously assigned permission
  • object permissions: allow actions on DB objects
•  
• Backups: Performed to restore data. Can be scripted or manual.
  • Full: all the data in a specific database
  • Differential: data that has changed since differential base
  • Incremental: only data that has changed since the last full or incremental backup