Wikiversity

Database Fundamentals/Introduction

< Database Fundamentals

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.
  1. ↑ Microsoft: Database Fundamentals Exam Details
  2. ↑ Wikipedia:Database
  3. ↑ Wikipedia:Database
  4. ↑ Wikipedia:Query language
  5. ↑ Wikipedia:Database index
  6. ↑ Wikipedia:Database server
  7. ↑ Wikipedia:Database
  8. ↑ Wikipedia:Flat file database
  9. ↑ Wikipedia:Hierarchical database model
  10. ↑ Wikipedia:Table (database)
  11. ↑ Wikipedia:Relational database model
  12. ↑ Wikipedia:Relational database model
  13. ↑ Wikipedia:Relational Database
  14. ↑ Wikipedia:Relational Database
  15. ↑ Wikipedia:SQL Server Management Studio
  16. ↑ Wikipedia:xquery
  17. ↑ Wikipedia:Microsoft SQL Server
  18. ↑ Wikipedia:Transact-SQL
  19. ↑ Wikipedia:Data manipulation language
  20. ↑ Wikipedia:Data definition language
Retrieved from "https://en.wikiversity.org/w/index.php?title=Database_Fundamentals/Introduction&oldid=1755631"