IMGT REVIEWER

Structure Query Language ( SQL )

An industry-standard programming language used to insert, retrieve, modify, and delete data in a relational database.

Structure Query Language ( SQL )

Contains statements for defining and administering the objects in a database.

Structure Query Language ( SQL )

The language supported by almost all database systems, and is the subject of published industry standards.

Structure Query Language ( SQL )

Developed by E.F Codd while he was working at the IBM research labs in San Jose 1971.

Data Definition Language ( DDL )

Used to define and manage all the objects in an SQL Server such as databases, tables and views.
- Create, Alter, and Drop statements.

Data Manipulation Language ( DML )

Used to Select, Insert, Update and Delete data in the objects defined by DDL.

Data Control Language ( DCL )

Security commands such as Grant, Revoke, and Deny that control how a principal ( user or role ) can access a securable ( object or data ).

Transact-SQL ( T-SQL )

The language containing the commands used to administer SQL Server, to create and manage all objects in SQL Server, and to insert, retrieve, modify, and delete all data in tables.

SQL Server instances Databases

Schemas Database

What are the entities in involved SQL Server architecture

SQL Server Instances

An installation of a SQL Server database engine/service database.

SQL Server Instances

Can be a Default Instance or Named Instance

SQL Server Instances

Can install multiple instances of SQL Server on the same computer

SQL Server Instances

Each instance is completely independent of the others in terms of security, the data that it manages, and in all other respects.

Databases

• A container of objects such as tables, views, stored procedures, and so on.

• Each instance of SQL Server can contain multiple databases.

MySQL Server Edition

• Enterprise Edition -Standard Edition

• Workgroup Edition -Web Edition

• Compact Edition

• Express Edition

Enterprise Edition

• High end edition with the advanced performance and availability features required to support thousands of connections and databases measured by terabytes.

• This edition includes all the right features, including Integration Services, Analysis Services, Web Services, database mirroring, and failover clustering.

Standard Edition

• Perfectly suited for medium-size businesses that need all the primary features and can live without the very high-end high availability features of Enterprise Edition

• This edition includes all the right features, including Integration Services, Analysis Services, Web Services, database mirroring, and failover clustering.

Workgroup Edition

- Intended as a departmental database server, Workgroup Edition includes the right mix of features for a small transactional database.

Web Edition

SQL Server 2012 is licensed for hosting websites.

Compact Edition

- Its small footprint of only 1MBof RAM means that it can actually run well on a mobile smart device.

Express Edition

It's a full version of the SQL Server database engine intended to serve as an embedded database within an application. It's completely free.

SQL Server Management Studio

1.Solution Explorer

2.Properties Window

3.Object Explorer

4.Query Editor (T-SQL)

Solution Explorer

Displays solutions, which are convenient groupings of objects, T-SQL, or special programs called stored procedures, among other items.

Properties Window

Displays the set of properties for each object.

Object Explorer

Expand Databases folder

Query Editor (T-SQL)

Execute sp_helpdb system stored procedure

System Database

- Some databases are created automatically when you install SQL Server.
- The default system databases should not be deleted because they may cause failure to your instance of SQL Server.

Master

- Keeps the instance of SQL Server functioning.
- This database records all the system information for all the SQL Server instances.
- Contains all information that is global to the server, including logins, error messages, system configuration, and the list of other databases that exist on the server.

Msdb

- Store information about the scheduling of alerts, the definition of jobs, and the recording of the server operators to be contacted when a particular event occurs on the server.

Tempdb

- Contains all temporarily created stored procedures and tables and is generally used as a work area by SQL Server.
- Where tasks that require memory are performed, such as join and sort operations.

Model

- Stores a complete template for creating new databases.
- When you create a new database, SQL Server copies the whole contents of the database into the new database you create.

Traditional File-based

- A manual file is set up to hold all external and internal correspondence relating to a project, product, employee, or client, such files are labeled and stored in cabinets.
- For security, cabinets have locks or may be located in secure areas of the building.

File-based System

- Collection of application programs that perform services for the end-users such as the production of reports.
- An early attempt to computerize the manual filing system.
- Was developed in response to the needs of industry for more efficient data access.

Limitations of File-based approach

- Separation and isolation of data
- Duplication of data
- Data dependence
- Incompatible file formats
- Fixed queries / proliferation (production or creation) of application programs)

Database

- Shared collection of logically related data, and description of this data, designed to meet the information needs of an organization.

Database Management System (DBMS)

A software system that enables users to define, create, maintain, and control access to the database.

Self-describing nature of dbase system

- The database contains not only the dbase itself but also a complete definition or description of the dbase structure and constraints. The definition is stored in the system catalog (also called as meta-data)

Insulation between programs and data, and data abstraction

- The structure of data files is stored in the DBMS catalog separately from the access programs ( program independence ). In the DBMS environment, in adding another piece of data, we just need to change the description of data in meta-data, no programs are changed.

Support of multiple views of the data

A database typically has many users, each of whom may require a different perspective or view of the database.

View

may be a subset of dbase

Sharing of data and multi user transaction processing

- Must allow multiple users to access the dbase at the same time

Concurrency control

the DBMS must include software to ensure that several users trying to update the same data do so in a controlled manner so that the result of the updates is correct.

Database Administrators ( DBA )

- database environments such as database itself and DBMS and related software.
- is responsible for authorizing access to the dbase, for coordinating and monitoring its use, and for acquiring software and hardware resources.
-Accountable for problems such as breach of security or poor system response time.

Database Designers

Responsible for identifying the data to be stored in the dbase and for choosing appropriate structures to represent and store this data.

End-users

People whose jobs require access to the database for querying, updating, and generating reports.

Casual end user

occasionally access the dbase, but they need different information each time.

Typically middle

high-level managers or other occasional browsers.

Naive or parametric end users

make up a sizable portion of dbase end users. Their main job function revolves around constantly querying and updating the dbase.

Example: Bank tellers, reservation clerks for airlines, hotels.

Sophisticated end users

include engineers, scientists, business analysts, and others who thoroughly familiarize themselves with the facilities of DBMS

Stand-alone users

maintain personal dbases by using ready-made program packages that provide easy-to-use menu or graphic based.

System analysts and application programer (software engineers)

- System analysts
-Application programmers

System analysts

determine the requirements of end-users and develop specifications for canned transactions (using standard types of queries and updates)

Application programmers

implement these specifications as programs, then they test, debug, document and maintain the canned transactions.

Dr. Edgar F. Codd

Who is the person pioneered the concept of relational database model in 1970.

"a relational model of data for large shared data banks"

Dr. C odd presented his relational database theory when he published his seminal paper entitled....

System R.

implemented the Structured Query Language (SQL) on their research project, known as ...

Oracle Corporation

first to market the SQL software in 1979. That SQL product is now the world's renowned " _____ " Database

RDBMS

• A collection of interrelated information that is organized into tables.

• We store data in each table by rows, and the data is arranged into columns.

• We store tables in database schemas.

• Schema is a logical container for the database objects that the user creates

• In relational databases, we can grant permissions to other users so that they can access our tables or the other users can grant us permission to access their tables.

(Entity Relationship Diagram)

ERD

ERD

- Modeling tool used to depict graphically a database design before it is actually implemented.
- It has three basic components, namely, an Entity, Relationship and an Attribute.

Entity

- A real-world object distinguishable or unique from other objects.
- An entity can be a concrete or physical object like employee, student, faculty, customer etc. Or it could also be conceptual or abstract like transaction, order, course, subjects etc.
- It can be thought of as a noun like student, employee etc. It is normally represented by a rectangle shape.

Relationship

- A way of relating one entity to another. Entities can therefore participate in a relationship.
- It is commonly thought as a verb connecting the entities or nouns.
- It is normally represented by a diamond shape.

Attribute

• Refers to the characteristic or basic fact or field of an Entity or Relationship

• For example a Student entity could have the following attributes: ID Number, Last Name, First Name, Address, Birth Date, etc.

Tables

- Tables are database objects that store data in a collection of rows and columns.
- A table definition is a collection of columns.
- Each row represents a unique record, and each column represents a field within the record.

Data Types

- An attribute that specifies the type of data that the column can store

Character Data

consists of any combination of letters, symbols, and numeric characters.

char

Fixed-length non-unicode character data with length of n bytes. n must be a value from 1 through 256.

nchar

Fixed-length Unicode character data with length of n bytes. n must be a value from 1 through 65,536. SQL Server allocates double the space internally for this type.

varchar

Variable-length non-unicode character data with length from 1 through 8,000. If no column size is defined - that is, varchar ( ), the length will default to 1.
Can use another setting that can exceed the 8,000-character limit, by defining the data type with the constant max.

nvarchar

The nvarchar type is defined in a similar way to varchar, except it uses unicode and therefore doubles the amount of space required to store the data.

Date and Time Data

Consists of valid date or time combination

date

hold a date from January 1, AD 1 through December 31, 9999. The format is YYYY-MM-DD

datetime

January 1, 1753 through December 31, 9999, to an accuracy of 0.00333 second storage is 4 bytes.

datetime2

January 1, AD 1 through December 31, 9999, to an accuracy of 100 nanosecs, storage is 6-8 bytes.

smalldatetime

January 1, 1900, through June 6, 2079, with accuracy to the minute, storage is 4 bytes.

datetimeoffset

Store a time relative to a specific date and time zone. If you want to store 6 p.m on March 24, 2008 in US Eastern Standard Time, the value would be stored as 2008-03-24 13:00:00 +5:00.

time

hold a time based on the 24-hour clock. Format is hh:mm:ss[.nnnnnnn]

Numeric Data

Consists of numbers only. includes positive and negative numbers, decimal and fractional numbers, and whole numbers (integers)

Integer Data

consists of negative or positive whole numbers, such as -15, 0, 5, and 2509.

bigint

From -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.

smallint

From 2^15 (-32,768) through 2^15-1 (32,767). Storage size is 2 bytes.

int

From -2^31 (-2,147,483,648) through 2^31-1 (2,147,483,647). Storage size is 4 bytes.

tinyint

From 0 through 255. Storage size is 1 byte.

bit

This can contain only a 1 or a 0 as a value ( or null, which is no value). It's useful as a status bit - on / off, yes / no, or true / false

Decimal Data

- Consists of data that is stored to the least significant digit.

decimal [ ( p [ ,s ] ) ] and numeric [ ( p [ ,s ] ) ]

Fixed precision and scale numbers. When maximum precision ( 38 ) is used, valid values are from -10^38+1 through 10^38-1
Precision - The number of digits in a

Precision

The number of digits in a numeric column.

Scale

The number of digits to the right of a decimal point in a numeric column.

float

hold very large numbers in the range of -1.79E+308 through 1.79E+308.

real

can store only numbers in the range of -3.40E+38 through 3.40E+38

Monetary Data

Represents positive or negative amounts of money.

money

data type to store values 922,337,203,685,477.580 through +922,337,203,685,477.5807

small money

data type to store values in the range from --214,748.3648 through 214,748.3647

Null Value

is not the same as zero ( 0 ), blank, or a zero-length character string such as "".

-means that no entry has been made.

Identity Column

- Use to create a column that contains system-generated sequential values to identify each row inserted into a table.
- This value is based on a seed value and an increment value.
- There can only be one identity column per table
- NULL values are not allowed
- It can be used with the following data type: tinyint, smallint, int, bigint or numeric / decimal with a scale of zero.

primary key

- Column or set of columns that uniquely identify each row in a table
- It does not allow null values
- No two rows can have

Foreign Key

- Column or set of columns whose values match the primary key (PK) in the same or another table.

Column Name

should be meaningful so as to describe the column content.

Data Type

Any one of 25 possible definitions provides the basis for the data a column will contain.