Database Fundamentals/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

Tables and Data Types →