Curriculum
Section 1: Concepts of Database Management Systems
1 Introduction to Database
2 Disadvantages of File based systems
3 What are Database Systems?
4 Database Management Systems (DBMS)
5 Database Models
6 Database Models- Relational Data Model
7 Relational Database Management Systems
8 Entities and Tables
Section 2: ER Model and Normalization
9 Data Modeling
10 Entity Relationship: One-to-many
11 Normalization
12 Relational Operators
Section 3: Overview of SQL Server
13 Introduction to SQL Server
14 Features of SQL Server
15 Components of SQL Server
16 Two- tier & Three- tier models
17 System databases under SQL Server 2005
18 Logical Database Components
19 Relational database architecture
Section 4: Overview of SQL Server (Lab Guide)
20 Overview of SQL Server
Section 5: Transact SQL Language
21 SQL Features and Variables
22 Data types & Comments in Transact SQL
23 Functions in transact SQL
24 Expressions and DDL
25 Data Manipulation Language (DML)
26 DCL And Executing Transact SQL Statement
27 Enhancements in SQL Transact
Section 6: Transact SQL Language (LAB Guide)
28 Transact SQL Commands
29 Transact SQL Batch
30 Transact SQL Script
Section 7: Creating and Managing Database
31 SQL Server Supported Databases
32 User defined Databases
33 Sample Database Filegroups and Transaction Logs
34 Database Modifications, Dropping and snapshot
Section 8: Creating and Managing Database (Lab Guide)
35 Creating a Database in SQL Server
36 Modifiying a database in sql server management studio
37 Creating and Dropping Database Snapshot
38 Controlling the size of database
Section 9: Accessing data from a database
39 Select Statement and its Different Expressions
40 Various Clauses with Select
41 GROUP BY Clause
42 ORDER BY Clause
Section 10: Accessing data from a database (Lab Guide)
43 Using Select statement with various clauses
44 Continued discussion on Select statement with various clauses
Section 11: Creating Tables and using DataTypes
45 Data Types in SQL Server
46 Creating Table & Column Nullability
47 Default Definition and Identity Property
Section 12: Creating Tables and using DataTypes
48 Globally Unique Identifier
49 Unique Constraints
50 Modifying Tables
51 Working with Tables
Section 13: Creating Tables and using Data Types (Lab Guide)
52 Creating Tables
53 Using constraints, unique identifiers and typed xml
Section 14: Introduction to data integrity
54 Data integrity Introduction
55 IDENTITY properties
56 Types of Data integrity
57 CHECK constraints
58 Practical Example for Creating a User defined Data type
Section 15: Introduction to Indexes
59 Extents, Clustered and Nonclustered indexes
60 Heap, IAM and Allocation pages
Section 16: Types of Indexes
61 Unique Index
62 Full Text Indexes
63 Differences between Regular & Full-text Index
64 Practical Demonstration for Types of Index
Section 17: Assignments on SQL Server
65 Create database practical examples
66 Creating, altering and inserting data in table
67 Selecting records using builtin functions
68 Practical Assignment using builtin functions
69 Using constraints in tables
70 Using constraints in tables- Identity
Section 18: Creating Indexes
71 Starting with Creating Indexes
72 Creating a Composite Indexes
73 String Functions
74 Using Math Functions
75 Functions using Declare Statements
76 Date Functions
77 Query Assignments
Section 19: Maintaining Indexes
78 Online index operations and degree of parallelism
79 Locking Option, Lock escalation and Indexes
80 Partitioned indexes, XML index and index statistics
Section 20: Implementing Views
81 Partitioned, Distributed and updatable partitioned view
82 Creating a view & Guidelines
83 Creating Views Basics
84 Partitioned Views
85 Dropping a view, Creating Notable View
Section 21: Additional Examples
86 If Else and Case Statement
87 Case Statement – Select Case Types
88 Joins
89 Left Outer Joins
90 Cursors
91 Understanding Cursors continued
92 Subqueries
Section 22: Managing Views
93 Schemabinding, indexed, Query optimizer
94 Modifying Views
95 Practical Example- Managing Views
Section 23: Introducing Stored Procedures
96 Types of Stored Procedures and Catalog SP
97 Database Mail, Database Engine, Security Stored Procedures
98 Fulltext Search, Scan for Startup Procs, Custom SP and SQLCMD utility
99 Recompile, Default, Null, Wild card and Controlofflow
Section 24: Pratical Examples
100 Examples- Catalogue Stored Procedure
101 Additional Examples for Stored Procedures
Section 25: Additional Examples
102 Output Parameters- Create Procedure Summation
103 Output Parameter Procedure
104 Example- Procedures with Return Statement
105 Cursors, Control of flow and transaction
Section 26: More About Stored Procedures
106 Understanding View Dependencies
107 Code Snippet
108 Passing Values by Position
109 Recompiling Stored Procedures
110 Understanding Error Functions
111 Error_Severity Function
Section 27: Introduction to Triggers
112 What is a Triggers ?
113 DDL Triggers, DML Triggers & Nested Triggers
114 Recursive Triggers & Disabling a Triggers
115 Instead Of Triggers
116 Examples on Stored Procedures & Triggers
117 Practical Example on Stored Procedure continued
Section 28: Additional Examples on Triggers
118 Triggers Examples
119 Creating Trigger for Insert, Update or Delete Employee Records
120 Example- Alter Table
121 Example- Inserting Record with Salary Condition
122 Example of Creating Table Enquiry
123 Creating Tables- Stud members & departments
124 Examples- Queries
