SQL Server Database Design

IT Training Solutions Limited

Course

In Washington

£ 1,495 + VAT

The best SQL Server courses

See all

See all

Description

  • Type

    Course

  • Location

    Washington

  • Duration

    5 Days

This course provides students with the technical skills required to program a database solution by using Microsoft SQL Server.

Facilities

Location

Start date

Washington (Tyne and Wear)
See map
Suite 13, Vermont, House Concorde, NE37 2SQ

Start date

On request

About this course

Logical and physical database design. Data integrity concepts. Relationships between tables and columns (primary key and foreign key, one-to-one, one-to-many). How data is stored in tables (rows and columns). Knowledge of basic SQL syntax (SELECT, UPDATE, and INSERT statements).

Questions & Answers

Add your question

Our advisors and other users will be able to reply to you

Who would you like to address this question to?

Fill in your details to get a reply

We will only publish your name and question

Reviews

Course programme

Course Overview

This course provides students with the technical skills required to program a database solution by using Microsoft SQL Server.
Prerequisites
  • Logical and physical database design.
  • Data integrity concepts.
  • Relationships between tables and columns (primary key and foreign key, one-to-one, one-to-many).
  • How data is stored in tables (rows and columns).
  • Knowledge of basic SQL syntax (SELECT, UPDATE, and INSERT statements).


Objectives


    Delegates will be able to :
  • Describe the elements of SQL Server
  • Design a SQL Server enterprise application architecture
  • Describe the conceptual basis of programming in Transact-SQL
  • Create and manage databases and their related components
  • Implement data integrity by using the IDENTITY column property, constraints, defaults, rules, and unique identifiers; plan for the use of indexes
  • Create and maintain indexes; create, use, and maintain data views
  • Implement user-defined functions; design, create, and use stored procedures
  • Create and implement triggers
  • Program across multiple servers by using distributed queries, distributed transactions, and partitioned views
  • Optimize query performance
  • Analyze queries
  • Manage transactions and locks to ensure data concurrency and recoverability.
Whats Included
  • Comprehensive colour course manual and exercises
  • Use of a Modern Training suite with 19 inch flat screen monitors
  • 1 delegate per computer
  • Lunch (When at our training venue)
  • Refreshments (When at our training venue)
  • Relaxed refreshment area at our training room
Course Content
Introduction to SQL server
  • Server components
  • Client server communication process
  • Enterprise manager
  • SQL query analyzer
  • SQL server services
  • MSSQL server service
  • SQL server agent service
  • Background to its development
  • Accessing the data in SQL server databases
  • Transact-SQL
  • XML
  • MDX
  • OLE DB and ODBC APIs
  • Client components
Security in SQL server
  • What is a role
  • Database users
Databases in SQL server
  • Objects in SQL server
  • Retrieval of metadata
  • System tables
  • Databases
Programming tools
  • How to execute T-SQL statements
  • SQL server object names
  • Data manipulation language statements
  • Data definition language statements
  • Data control language statements
  • Transact-SQL
  • OSQL
  • Execution plans
  • SQL query analyzer
Creating a database in SQL server
  • Pages and extents
  • Data storage in Sql server
  • Dropping/deleting a database
  • Shrinking a database or file
  • Managing data and log file growth
  • Filegroups in SQL server
  • Setting database options
  • The transaction log
  • Creating a database
  • Database design
Tables, rows and columns
  • Creating a unique id column (identity)
  • Primary key
  • Adding/dropping a column
  • Deleting a table
  • Data in rows
  • User defined data types
  • SQL server data types
  • Creating a table
Implementing data integrity
  • Creating a database diagram
  • Rules/restrictions of use of constraint types
  • Creating constraints
  • Constraints
  • Referential integrity
  • Entity integrity
  • Domain integrity
Indexing data in SQL server
  • A unique index
  • How SQL server stores data
  • Why not to index
  • Using a clustered and nonclustered index
  • Maintaining the indexes
  • Page splits
  • Why index
  • Creating indexes
  • Recommended indexing practices
  • A composite index
  • Index options
  • Index maintenance
  • Statistics
  • Viewing statistics
  • The index tuning wizard
  • Indexing guidelines
Views and select queries
  • Partitioned views
  • Indexed views
  • Modifying data through the views
  • Hiding the structure of views
  • View definition information
  • Different owners of objects
  • Checking dependencies
  • Dropping a view
  • Modifying a view
  • Creating a view
  • Why have views
  • What is a view
Stored procedures
  • A custom error message
  • Return statement
  • Error messages
  • Running extended stored procedures
  • Using a parameter
  • Modifying stored procedures.
  • Stored procedure guidelines
  • Viewing information on stored procedures
  • Creating a stored procedure
  • Advantages of stored procedures
  • How a stored procedure caches
  • Types of stored procedures
User defined functions
  • Recommended practices with functions
  • SCALAR functions
  • Deleting a function
  • Changing a function
  • Some function restrictions
  • Creating a function
Use of triggers
  • Considerations for the use of triggers
  • Recursive triggers
  • Nested triggers
  • Controlling the trigger fire order
  • How triggers work
  • Deleting triggers
  • Disabling/enabling a trigger
  • Modifying triggers
  • Creating a trigger
  • Considerations before using triggers
  • Why use them
  • What are triggers
Programming across multiple servers
  • Partitioned views
  • Managing distributed transactions
  • A linked server
  • Distributed queries
Optimising query performance
  • Turning on server trace and statistics
  • Overriding the query optimizer
  • Index creation guidelines
  • Indexing strategies
  • The bookmark lookup
  • Viewing the execution plan graphically
  • Data on the execution plan
Analysing queries
  • Nested Loop, Merge Join, Hash Join
  • Queries that use JOINS
  • Queries that use AND, OR
Managing transactions and locks
  • Deadlocks
  • SQL server locks
  • Turning implicit transactions on/off
  • Transaction guidelines
  • Transaction recovery and checkpoint
Backing up a SQL server database
  • Restoring from backup
  • Performing a backup

See related categories

SQL Server Database Design

£ 1,495 + VAT

The best SQL Server courses

See all

See all