Languages:

• Sql server: TSQL
• Oracle:  SQL, PL/SQL

TOOLS:

• SQL server: SSMS, SSBIDS
• Oracle :  SQL*PLUS, TOAD

Different Types of Keys in RDBMS:- [components of RDBMS]

The different keys are  

1. Primary key
2. Composite key
3. Candidate key
4. Super key
5. Foreign key.

PRIMARY KEY in SQL Server with example   :-

What is Primary Key in SQL Server?

• A column that uniquely identifies each record in table is called “Primary key”.

• Example is Student-ID.

STUDENT TABLE:

SID

SNAME

GROUP

    Where SID is PRIMERY KEY

COMPOSITE  KEY in SQL Server  :- 

What is Composite Key in SQL Server?

    Sid                 Cid           Sname          Cname                      Doc

     1                     10                a                  java                          10

     1                      11                a                 .net                            15

     2                      10                b                  java                           10

• A combination of columns uniquely identifying the record then it is called “composite key”. In the above set of rows Sid and Cid are the Composite keys

CANDIDATE KEY in SQL Server :-

What is Candidate Key in SQL Server?

• Vehicle Table  :

Vehicle no         eng. No           Vname            Price            Model 

• Attributes which are eligible for Primary Keys are  called  “Candidate key”.

1. Candidate keys  : vehno,eng.no
2. Primery key  :vehno
3. Alternate key  :engno.

• A primery key is always Candidate key  but

A candidate key may or may not be a Primary key.

SUPER KEY in SQL Server :–

What is Super Key in SQL Server?

• The combination of columns uniquely identifiers the record is called “Super key”.

Super Key Example:-  

               Dep.table

               Dno    dname              loc    –    s.k.1

               Dno+dname                         –    s.k.2

               Dno+loc                               –    s.k.3

               Dname+loc                           –    s.k.4                

               Primary key = min (super key)

So, hence primary key is dno & dname.

Primary key dno & alternate key dname.

FOREIGN KEY  in SQL Sever:-

What is Foreign Key in SQL Server?

• It is used to establish relationship b/w two tables is called “Foreign key”.
• A  Foreign key is a primary key in some other table.

Foreign Key Example   :-

  Emp                                                                        Dep          

  Empno         ename        sal      d.no                         D.no                    Dname           Emp.no

     1                    A              5        10                             10                        sales          12 n’t automic

     2                     B              6        20                             20                        alcs            25

      3                     C              7       30                              30                        stones        367

• If the relationship is one to many  always the F.K is should be added to the table participating on “many-side of the relationship”.

Example   :-  

           Customer                                Order

            Cid          cname                   order.no           ord.date      price

• 1  to many ,so primary key can be added to  order table only.
• If relationship is one to one ,  the foreign key can be added to any table.   

    Dept                                        mgr

D.no      Dname                           mgrno    Name        D.no

      It  is one to one.

Many –to-Many Relationship in sql with example  :-

     Example  :

•         Supp.                                                     Cust

  Eid         Ename                                    Cid                    Cname

  11              A                                            1                         X

   12              B                                           2                          Y

   13              C                                            3                          Z

                                          SUPP-CUST

                                          Sid         Cid

                                            10            1

                                            10            2

                                            11             1 

• RDBMS Doesn’t support    Many-to-Many relationship –

    In RDBMS, Many-to-Many relationship will be splitting to two-one-two many Relationship.

DATABASE DESIGN in SQL Server:-

Steps followed in database development:

• Analysis
• Design
• Development
• Implementation
• Maintenance.

1. Db designed by “DB Designer(or) DB Architect”
2. The DB designer designed the  db by using two techniques,

                       (i) ER model:  Top-down  Approach

                       (ii) Normalization: Bottom up    Approach

1. DB developed by “DB Developer”
2. The developer the will develop the database by using “RDBMS TOOL” i:e sql server (or) oracle. 

ER MODEL in SQL Server   :–

1. ER stands for “entity relationship” model
2. It is a “Graphical  representation of data”.
3. The  diff. element of ER model

•       Entities
•       Entity sets
•       Attributes
•       Relationships

ENTITY in SQL Server with Example:–  

• It is an object.

Ex: Employee,customer,bank A/c,course etc.

ENTITY SETS in ER Diagram with Example:–

• Collection of entities that share common properties is called “Entity Sets”. 

Example —

 

Symbol: rectangle represents the entity set.

    Example:  Employee

Examples of ATTRIBUTES  in Database:–

• Attributes one the properties of entity.

Example:–

          Empid       ename      job     sal

These are all properties of employee.

• Attributes are indicated by symbol is “Oval or Ellipse”.

RELATIONSHIPS in SQL Server    :–

• A relationship is a association b/w entities of entity sets.

 

• The relationship is indicated by symbol  is “dimension”

Types of Relationships in SQl Server :–

•  1 to 1
• 1 to many
• Many to Many

  

• ER  model can be used to at “Design part”

Difference Between ER model and Relational Model

ER Model                                               Relational Model

Entity sets                                         Here,entity sets are converted to “Tables”

Attributes                                           “Fields”

Relationships                                     “Foreign keys”

DATABASE:  Database is a “collection of interrelated data”.

Ex: Sales D.B contains Sales data for ex: consumer’s products etc. 

  DATABASE TYPES:

1. OLTP: It is simply called Database

2. OLAP: Data warehousing. 

D.B:

-“Organizations Creates Database is to keep details about day to day transactions”.

-The Basic Operators Performed on Database are

•                        INSERT
•                        UPDATE
•                        DELETE
•                        SELECT

DBMS : {Database Management System}

System: software (or) set of tools.

It is a software to manage Database.

• DBMS is an interface between user & Database.
• DBMS is also called as “Database Server”.
• Database is introduced in 1970’s.
• FMS introduced in 1960
• HDBMS and NDBMS introduced in 1970
• RDBMS introduced in 1980
• ORDBMS and OODBMS introduced in 1990 

Files & FMS:

File Management System:

• Data  stored in files & data managed by using FMS (or) FPS (File processing System)
• Examples of FMS is COBOL Language. 

FMS

• More Redundancy (Duplication of data)
• More Inconsistency
• Less security
• Lightly Development
• It doesn’t support DATA INTEGRITY RULES

DBMS

Less Redundancy  (Because Of DBMS Follows the Normalization)

• Less inconsistency 
• More security
• Rapid Development Fast {Some tools Have DBMS i: e. RAD tools rapid Application development}
• DBMS supports “DATA INTEGRITY” Rules

1.  Primary key
2. Foreign key
3. Unique Key, etc

DBMS TYPES:

Outdated DBMS Types are

• HDBMS
• NDBMS

Presently used DBMS Types are

• RDBMS      
• ORDBMS
• OODBMS

These are all based  on “DATA MODEL”

DATA MODEL:

1. Model means representation of data.
2. Data representation can be

• Hierarchical Data model
• N/W Data model
• Relational Data model                                   

HDBMS:

• In HDBMS DATA Represented in the form of inverted tree.

Example: IMS [Integrated mgmt System] introduced by IBM.

Disadvantages:

• This supports only one to many relationships.
• It doesn’t’ support many to many relationships.
• Data retrieval is slow.

NDBMS:

• It supports all types of relationships.
• In NDBMS data represented in the form of “Nodes”

• Ex. Of  NDBMS is IDMS [integrated database mgmt system]
• IDMS introduced by IBM

Disadvantages:

• Complexity relationship.
• Data retrieval is slow.

RDBMS:

• This model is introduced by “E.F.CODD”
• He designs some rules are called “CODD Rules”
• 12 Rules are designed.
• A dbms which data file all CODD rules is perfect ”RDBMS”.

Rule (i): Information Rule:

• Data must be organized in 2d tables.

    Table:      Collection of named columns & unnamed rows.

   Ex:           Empid              Ename        Salary

                        1                          A               500

                         2                         B                600                        

• Vertical Columns are also called Fields (or) attributes.
• Horizontal rows are also called records (or) tuples.
• Intersection of row & column must be atomic [single].
• Records are uniquely identified by “primary key”.
• Tables are related using “foreign key”.

Examples of RDBMS:

•    SQL SERVER
•    ORACLE
•    DB2
•    MYSQL
•    SYBASE
•    INGRES
•    INFORMIX
•    TERADATA.

The first RDBMS is called “system R”. It is introduced by IBM.

ORDBMS:

• Which stands for obj. relational DBMS?
• ORDBMS= rdbms + OOPS
• It is not a new system; it is an extension of RDBMS.
• User defined Types [UDT] is introduced by ORDBMS.                                   

Address    {

                  House No                                   

                   Street                                

                   City

                   State

                    }

• Examples of ORDBMS are SQL SERVER, ORACLE.

OODBMS:

• It is extremely based on “oops”.
• Here data is represented in the form of “classes &Objects”

SQL SERVER Tutorial – Lesson 1

Different Products from Microsoft:

• Operating System: Examples are Windows XP, 2K3, Win7, Win8
• Database Servers: Examples are SQL Server, MS Access
• Application Development Tools: Examples are C#.Net, VB.Net and ASP.Net
• Enterprise Resource Planning: Examples are Microsoft Dynamics
• Data Warehousing: Examples are Microsoft Business intelligence (MSBI)

SQL SERVER Introduction:

• It is a Database Server s\w.
• It is a Application s\w.
• It is a DBMS.
• It is a RDBMS & ORDBMS.
• It is a Back end tool. 

 Software’s are two types

• System Software: Example is System
• Application Software: Example is User where this user is having different types including word processing, spread sheets, presentation, DBMS. 

DBMS TYPE:

• HDBMS-Hierarchical
• NDBMS-Network
• RDBMS-Relational (EX: Oracle, SQL)
• ORDBMS-Object Relational
• OODBMS-Object Oriented

Different between Oracle & Sql Server:

• Sql runs on Window Platform operating system.
• Oracle runs on different platforms – Linux,Unix.
• Developers are both side same, but both side database.

Admin is used to different tools. 

Example of RDBMS:

• Oracle
• Sql Server
• My Sql
• Sybase
• Ingnes
• Informix
• Teradata
• Db2 

OLTP: Online Transaction Processing – Example: ATM 

Modules: 

• Presentation Logic
• Business Logic
• Storage Logic : Here, we can store permanently.   

• File
• DB

A tool which provides facility to create database is called

These tools are called “ Back end tools”.

Business Logic: Means-programs those are  C#, VB, JAVA. Here, validating the data.

Presentation Logic: It is created by using some tools are called “ Front end tools”. Examples are VB, C# etc.

“Any front end tool can be connected to any Back end tool”.

Sql Server Language: TSQL: TRANSACT SQL

Oracle Language: SQL, PL/SQL                                                                          

TOOLS:

• SSMS : Sql Server Management Studio-> OLTP

• SSBIDS : Sql Server Business Intelligence Development Studio-> OLAP (online analytical processing) (Or) DWH.

• Sql Server Profiler
• Sql Server DB Tuning Adviser.
  • These tools are performing Tuning.

Data:

          Facts about

           People,                       EX: 1   **   5000

           Places,

           objects

Information: Organized data is called “information”.

Example: Emp no     Name        Salary

1                   **             5000