SQL Server Tutorial

SQL Server 2000: File and Filegroup

noreply@blogger.com (Admin) — Tue, 10 Aug 2010 14:08:00 +0000

Filegroup classifying a number of files for administrative purposes and the placement of data. For example, three files (Data1.ndf, Data2.ndf, Data3.ndf) can be placed on three disk drives and stored in a filegroup named filegroup1. Files and FIlegroup facilitate the addition of new files on a new disk. If your database exceeds the maximum file size of Windows, you can use a secondary file.

A database has a primary filegroup and some filegroup, which is defined by the user. Filegroup primer contains primary files. When a database is created, the primary filegroup contains the primary data files and other files that are not stored into another filegroup. If the primary filegroup size exceeds the maximum limit, the information can not be added to the system tables. To fix this you need to move some files.

Filegroup defined by the user, is filegroup created by the user when the user create or modify a database. If filegroup is full, only the user tables that are allocated to that filegroup are affected. If you create an object and not Declare filegroup, then use the default filegroup. Filegroup default can be changed by using ALTER DATABASE

Inner Join

noreply@blogger.com (Admin) — Sun, 08 Aug 2010 05:54:00 +0000

Inner Join is a join that produces minimal rows that there is a rows in both tables that match the join condition, which is different rows with another row in a table will be removed. In the SQL-92 standard, inner join is expressed in FROM or WHERE clause. Inner join is the only joint which is supported by SQL-92 standard in the WHERE clause. Inner join in WHERE clause otherwise known as old style inner-join.

In the examples in this module we will use an existing database. For example we will use data from the pubs database to display the Title column from Titles table and column PubName from Publishers table. Both these tables have a relationship at Pub_id column.

SELECT t.Title, p.Pub_Name FROM Publishers p INNER JOIN Titles t
ON p.Pub_id = t.Pub_id ORDER BY Title ASC

If you use multiple joins to connect more than two tables, it's easier if you think about each joint separately. For example you have three tables: A, B and C will be connected. First join will linking A and B, and then the results will be combined with C with the second join to produce the final data. Query optimizer will make a plan of execution, although the result is same. From a number of plans selected the most optimal execution.

How to Use CREATE DATABASE Command

noreply@blogger.com (Admin) — Fri, 06 Aug 2010 03:37:00 +0000

The command "CREATE DATABASE" can be used to create a database and files to store data. You can also express some of the parameters that define the characteristics of a database. For example you can declare a maximum size of the file. If you use a simple command is:

CREATE DATABASE database_name

without parameters, the database will be created with the same size with a database that was created by SQL Server named Model. SQL Server implemented CREATE DATABASE command in two phases:

SQL Server, copying the Model database for the initial database with meta data.
Then, SQL Server fills the page blank except for page that contains internal data that declare how to use a place name in the database.

All objects in the database Model will be copied into the new database. You can also add objects such as tables, views, stored procedures, data types, etc into the database Model, so every time you create a new database objects will be included.

Each of the settings of the database model will be included in the new database unless you use the FOR ATTACH. For example the Model database option select into / bulkcopy set to OFF. If you change the database option with the command ALTER DATABASE model, the setting will affect the new database will be created.

Using UNION operator

noreply@blogger.com (Admin) — Tue, 03 Aug 2010 15:24:00 +0000

You can combine the results of several queries with UNION operator. By default, duplicate rows will be removed. To maintain a duplicate, then use the keyword ALL. Some rules of the UNION operator :

Queries will be merged, must have many same column,same order, and similar data types.
If you use INTO clause in one query, you should put that query as first query.
You can use GROUP BY and HAVING clause in individual query.
ORDER BY clause and compute, are permitted only at the end of the UNION operator, to define the sequence of final results or to calculate the final value.

For example we will display all of the customers and suppliers from the USA, sorted by CompanyName:

SELECT CompanyName, ContactName FROM
Suppliers WHERE Country = ‘USA’
UNION
SELECT CompanyName, ContactName
FROM Customers
WHERE Country=’USA’ ORDER BY CompanyName

If you want to save the results combined into a temporary table, you must use the INTO clause followed by table name, for example TempUSA

SELECT CompanyName, ContactName FROM
INTO TempUSA
Suppliers WHERE Country = ‘USA’
UNION
SELECT CompanyName, ContactName
FROM Customers
WHERE Country=’USA’ ORDER BY CompanyName

Subquery With EXISTS Operator

noreply@blogger.com (Admin) — Mon, 02 Aug 2010 02:10:00 +0000

If a subquery uses EXISTS, such as testing the same function exists or not. WHERE clause test the existence of the outer query-lines produced by the subquery. Subquery does not return data, but produces the value TRUE or FALSE. Subquery examples that use IN, will be changed by using EXISTS:

SELECT DISTICT FompanyName,PostalCode FROM Customers WHERE EXITS (SELECT * FROM Suppliers WHERE PostalCode = Customers.PostalCode)

If you use exists, it must use SELECT *. Keyword, NOT EXISTS is the opposite of EXISTS, WHERE clause subquery is used to indicate that there is no result.

Compute and Compute BY clause

noreply@blogger.com (Admin) — Sat, 31 Jul 2010 15:54:00 +0000

In the previous article I had explained about "order by" clause, now I will explain about the compute and compute by clause.COMPUTE and COMPUTE BY clause is used to generate new rows of data containing detailed and summary. COMPUTE clause produces detail lines and a line that contains the summary. For example we will display all the data from Titles table from Pubs database, this is the same as ordinary SELECT command. After the last line of the data, we want to display the number of fields ytd_sales, for that we need the COMPUTE clause.

Here is the Example:

SELECT * FROM Titles compute sum(ytd_sales)

COMPUTE BY clause producing new lines of summary data, similar to the GROUP BY clause, but the result rows as a subgroup with the values summarized. If you use COMPUTE BY clause must be accompanied with ORDER BY. For example we want to display data from table Titles sorted by Type, and after Type, a total of ytd_sales displayed for each Type, then the command is:

SELECT type,ytd_sales FROM Titles ORDER BY type compute sum(ytd_sales) by type

Learning About ORDER BY clause

noreply@blogger.com (Admin) — Fri, 30 Jul 2010 19:32:00 +0000

ORDER BY clause used to sort the results based on one or more columns. Ordering can be ascending (ASC) or descending (DESC), the default is ASC. If sorting by several columns, nested sequencing process.

For example we will display the employee data was sorted by country:

SELECT country, EmployeeID, LastName, FirstName FROM Employees ORDER BY Country

Data will be sorted based on the Country. Notice, how the LastName, LastName whether in each country will be sorted? No, if we wish for a country such data was sorted by LastName, you must declare two fields in the ORDER BY:

SELECT country, EmployeeID, LastName, FirstName FROM Employees ORDER BY Country, LastName

Adding Data With Subquery

noreply@blogger.com (Admin) — Fri, 30 Jul 2010 05:19:00 +0000

You can use subquery, SELECT in INSERT statement to add values to a table or several tables or views. Subquery allows you to add a few rows at once into a table.

What is the difference subquery usage, with usage VALUES?. Subquery will add a number of existing data, while the VALUES to add new data. For example, we create a new table named TmpPublisher into the pubs database. The table contains four fields and made with command :

CREATE TABLE TmpPublisher
SELECT pub_name varchar, city, state, country FROM Publisher WHERE COUNTRY =’USA’

If the subquery is used in INSERT command, then the subquery is not necessary are enclosed in brackets. Subquery produce the same four columns and fields in the destination table. For example in tanel pub_name TmpPublisher will be filled only for the data not from the USA, then the INSERT clause you should declare a column that will contain the data.

INSERT INTO TmpPublisher (pub_name)
SELECT pub_name FROM Publisher WHERE NOT COUNTRY = 'USA'

Reliability and Concurrency

noreply@blogger.com (Admin) — Mon, 26 Jul 2010 19:39:00 +0000

SQL Server 2000 uses the concept of transactions and transaction logging to ensure consistency and the ability to recover information stored in the database. Transaction is the smallest unit that can be an INSERT or UPDATE single operation, or a set of existing operations within the statement BEGIN and COMMIT transaction. If the transaction contains multiple operations, all transactions must complete or cancel all.

SQL Server ensures all work units are consistent. This is done by writing the data before and after the transaction in a transaction log. Logs can be used to restore the database to a consistent state if a rollback application process, or the application will recover the data due to system failure.

SQL Server supports Microsoft Distributed Transaction Coordinator (MS DTC) so that the integrity of the transaction can be applied to distributed operations that affect multiple installations of SQL Server by using two-phase commint way.

Concurrency control on the SQL Server is updated by implementing the concept of dynamic locking. This concept of locking strategy for analyzing each transaction. SQL Server uses several types of locking, for use on a record or a collection of records or a table.

With dynamic locking, SQL Server can choose the type of locking used by the operation involved.

Trigger an Overview

noreply@blogger.com (Admin) — Sat, 24 Jul 2010 17:19:00 +0000

Trigger is a special stored procedure that runs automatically during or after data modifications. Commands that will activate the trigger are UPDATE, INSERT, and DELETE. Trigger is used to maintain data integrity and implement complex business rules. Trigger made using Transtact-SQL language or SQL Enterprise Manager. Trigger management tasks include changing, rename, view, delete, and create a trigger inactive.

Trigger uses two virtual tables that are Inserted and Deleted to detect data modification. To program the trigger, you must understand that tables and also Transtact-SQL language.
To maintain data integrity, you should be able to use some alternative, such as triggers or constraints. You can also use the primary key and unique key to identify rows in a table uniquely. You can also use the default values and domains in the limit of allowed values in a column. Referential integrity is used to ensure the validity of relationships between tables. We recommend that you use these constraints before choosing a trigger, because trigger can perform more complicated process, but the heavier the system load.

Use a trigger for the following cases :

If the use of declarative way cannot meet the necessary requirements.
A change must alter the other tables are linked.
If the database requires normalizes and automatically convert existing redundant data in multiple tables.
If a value in a table must be validated with data on other tables.
If a value in a table must be validated with data on other tables.

Trigger capabilities :

Trigger to maintain referential integrity by making changes in or deletion of a foreign key data in the database.
Trigger can work on multiple columns in a database even in objects outside the database. You can also use a trigger on the view.
A trigger can perform some action and trigger can be activated by some event.

In addition to these capabilities, triggers, also has a limitation. Trigger cannot be created on a system or temporary table, although the command in the trigger table refers to the system or temporary table. Trigger cannot be activated manually, but automatically activated in case of events UPDATE, INSERT, and DELETE. The trigger always contains one or more event before Transtact-SQL command.

When INSERT or UPDATE that activated trigger, trigger will store new data, or data modification results in a table called Inserted. If DELETE command that activated trigger, trigger will store new data, or data modification results in a table called Deleted. Table is located in memory and is read by the trigger with the command-SQL Transtact.

Batch In SQL Server 2000

noreply@blogger.com (Admin) — Fri, 23 Jul 2010 17:26:00 +0000

Batch is a collection of commands that are sent at once by an application to SQL Server for execution. SQL Server to copy the commands from a batch into an execution plan. Furthermore, the commands in the execution plan is run one by one.

For example, in the execution plan there is an error, then all commands in the batch will not be done. If there is a run-time error, such as overflow or a constraint violation will occur the following things :

If there is a run-time, the relevant commands and the commands later dismissed.
If constraint violations occur, only the relevant commands dismissed, while commands thereafter will be continued.

Commands are executed before the run-time errors will not be affected. If the batch is in a transaction, and error causes rollback, modification of data that have not been in-commit will be aborted.

For example there are five commands in a batch. If the third command syntax errors, the command will not run. If the batch has been compiled and run second command fails, the outcome of the first command will not be affected because it has completed execution.

Query Optimization

noreply@blogger.com (Admin) — Fri, 23 Jul 2010 04:09:00 +0000

SQL Server is an intelligent database server. SQL Server has a main function for processing database queries and transactions provided by the client application with good performance. Applications only need to provide a set of SQL commands, regardless of physical implementation of tables. SQL Server will determine the best way to process these commands with a technique called Cost-Based Query Optimization.

For example an application provides a complex query with the SELECT command. Query is to combine three tables with a join operation and then perform some aggregate operation on numerical data with a particular grouping. The query results should be sorted by date. SQL Server can optimize the query so that it produces several different strategies to run the query. Execution plan, determine the index that is used, the order of the join of the table, the most efficient way to perform aggregate calculations, and the fastest method to sort the results. SQL Server chooses the fastest. Selection is based on the cost of each execution plan consisting of CPU usage.

To generate the execution plan that accurately shows the number and distribution of data in the database, SQL Server uses distribution statistics. Statistical distribution, is a type of histogram that describes the distribution of index and data in a column. SQL Server 2000 provides several mechanisms of query plan reuse and also put together in between multiple users

Single Transtact SQL Command

noreply@blogger.com (Admin) — Thu, 22 Jul 2010 13:08:00 +0000

Transtact SQL command is a command non procedural, meaning that the command does not declare the stretcher on the steps executed by the server. Server must analyze command to obtain the most efficient. This process is called optimization and is performed by the query optimizer.

A command, will have several execution plans. Of the possibilities would serve targeted selected a best execution plan, this process is called optimization. Query optimizer is the most important components in a system of the database SQL, because it can handle different conditions without having to change the program.

For example to process a SELECT command, the SQL Server run the following commands:

SQL trace and split into logical units such as keywords, expressions, operators, and the identifier.
Formed query tree to illustrate the logical steps necessary to change the data source into the desired shape.
Query optimizer to analyze all the ways to access the source tables and choose the steps that use the fastest and least resources. Query trees, repaired to record the steps that ultimately yield better execution plans.
Database engine started to run the execution plan.

Basic steps for processing the SELECT command can be used for other commands such as INSERT, UPDATE, and the DELETE. UDPATE and DELETE commands that have a target on the set of rows are basically doing the same process with the SELECT command. After the lines obtained were alteration or deletion.

Transaction and Lock

noreply@blogger.com (Admin) — Thu, 22 Jul 2010 01:40:00 +0000

Transaction and Lock used to ensure consistency and integrity of the database. Transaction is a logical unit that contains a set of commands. Lock prevents conflict between users so that users can not read or modify data that is being modified by another user. Transactions use the lock to prevent users changing data that is being used by the transaction.

Each SQL Server database has a transaction log that records all transactions and database modifications made by the transaction. Log supports three operations :

The process of recovery from single transactions
If an application executes a ROLLBACK command or SQL Server detects an error, the records in the log is used to return to the data before the transaction begins.
Recovery process all the transactions that have not been completed at the time SQL Server starts
If SQL Server stops suddenly for some reason, the database may be in a state is experiencing a change that has not been written into the data file. If SQL Server starts, the recovery process will be run on each database.
Restore the database to its original state after a failure
Once there is a failure on the hard disk, you can restore the data on the situation before damage occurs.

Transaction log, not a table but is a separate file or several files in the database. Cache of the log, is a separate place in the database. Forms of data in the log is not the same as the format of page data. You can create the transaction log into multiple files. You can also set the log file growth size of the log thus avoiding uncontrolled logging.

Transaction log can be regarded as a serial string of log records. Log sequence number (LSN) identifies each log. New log record is written at the end of the log with higher LSN than the previous LSN record.

Stored Procedure

noreply@blogger.com (Admin) — Wed, 21 Jul 2010 15:36:00 +0000

Stored Procedure is a set of SQL commands that are named and stored on the server. Stored procedure usually contains general commands on the server, and generates a set of recorded. Stored procedure written in the form of a script.

Store procedure will improve the performance of databases, database security, and allows creating programs to the database. Improved performance through the use of local storage, a program that has been compiled, and the use of cache.

Stored procedure is more efficient than regular commands, because the procedure is stored on the server at the time made and executed on the server. Stored procedure that contains a set of commands Transtact-SQL will be executed by a Transtact-SQL command. Before the stored procedure is created, the writing of the command syntax will be checked. If no errors, a procedure will be stored in tables and text SysObjects SysComments stored in tables. First time stored procedure is executed, the execution plan is created and stored procedure is compiled. Subsequent processing will be faster, because SQL Server does not need to check syntax and create a plan of execution, or compile a procedure.

Stored procedure can accept parameters, and generate parameters and provide feedback in the form of a status code and text, and can call another procedure. Stored procedure ensures isolation and security with encryption. With encryption, the user can not read the commands used in stored procedures.

Database Management and Administration Services

noreply@blogger.com (Admin) — Tue, 20 Jul 2010 05:24:00 +0000

SQL Server provides a set of services for development of database applications. Here is a brief description so you will understand its usefulness. SQL Server provides a number of services for database administration and you can use it on your application.

SQL Server provides a number of services for database administration and you can use it on your application. This collection service, called SQL Server Distributed Management Framework (SQL-DMF).

SQL Server provides several graphical administration tools. SQL Server Enterprise Manager is the main administrative environment for SQL Server and can be used for the installation of SQL Server database administration locally and remotely. SQL Server Enterprise Manager provides a wizard for easy administration. Some wizard that there is Database, Data Transformation Services, Management, and Replication.

In addition to the wizard of the most useful one is the Database Management Wizard that automates the maintenance plan. SQL Server Enterprise Manager and other administrative tools using a software component used together which is called SQL Server Distributed Management Objects (SQL-DMO). SQL-DMO provides a set of COM interfaces for database administration of SQL Server installations.

With SQL Namespace (SQL-NS) allows you to use dialog boxes that are part of SQL Server Enterprise Manager within your administration.

SQL Server Agent is an integrated environment for job scheduling and execution. With this facility, you can create some jobs for database administration work, either on a single server or multiple servers.

SQL Server provides two important tools that SQL Server Profiler and Index Tuning Wizard. SQL Server Profiler is used to monitor and record activity in SQL Server performance measurement and optimization. Log files SQL Server Profiler can be used to determine the query. Workload of the SQL Server Profiler, can be restarted on other SQL Server installations so you can recreate the performance characteristics for a workload.

Decision Support Services

noreply@blogger.com (Admin) — Mon, 19 Jul 2010 09:02:00 +0000

SQL Server also provides decision support facilities. This facility is known as data warehousing or business intelligence. Decision support, analysis support the user at the database information.

Decision support system database is implemented as a separate duplicate information and read-only and stored in another operating system, and is usually called a data mart or data warehouse. Some reasons for the decision support system separate databases :

Decision support activities can be located on a separate database server so that even the integrity and operational databases performance undisturbed.
Operational database structure is usually very complicated and optimized for the transaction.
Decision support database must declare the consolidation of a number of operating system databases, operating systems, and different hardware platforms.

The data did not suddenly appear in a decision support database. Such data must be moved periodically. Before the transfer of data should be validated, cleaned, and in the transformation. This process is called extraction and transformation. SQL Server has a facility called Data Transformation Services (DTS) to conduct a population of decision support database. With DTS, you can create a routine to extract data from a number of operating systems using OLE DB or ODBC, perform data validation and transformation, and then call him at high speed into a decision support database.

Usually, decision support databases are redundant, because it is a copy of the operational database, thus requiring periodic maintenance involving large amounts of data. Query processor of SQL Server provides a number of techniques called bulk processing updates that are designed to improve the performance of operations serve targeted. For example, when changing large amounts of data, the query processor will try to save all changes to the index that are affected in a pass if possible. This method is faster than by changing the index by way of line by line.

SQL Server also contains a number of tools to write queries in natural language. This tool is called English Query. You can use this facility to create a description of the entry and relationships expressed in a database.

Distributed Database Services

noreply@blogger.com (Admin) — Mon, 19 Jul 2010 07:27:00 +0000

SQL Server has the ability to refer to the tables from multiple databases in a query, even the tables on a different computer. The tables are stated in the FROM clause of a SELECT statement, INSERT, UPDATE, or DELETE.

Distributed Queries, very useful. For example you will combine data from tabeltabel on several servers. Besides the remote table can also be accessed using OLE DB or ODBC.

SQL Server supports the use of Microsoft Distributed Transaction Coordinator (MS DTC) to conduct transactions in multiple SQL Server by using two-phase commit protocol. SQL Server can create a list of all remote servers automatically within a two-phase commit.

SQL Server also supports a number of model replication using replication architecture-and publish-subscribe. Replication automates the process to synchronize data between multiple SQL Server databases to facilitate the development of distributed applications.

Snapshot Replication is a replication of the basic model in SQL Server. With this model, a 'shot' with a scheduled publication created and distributed to all subscribers. This method is very useful if the information in the publication of the relatively static and subscriber only needs to refresh periodically.

Transactional Replication is used when all active subscriber on a LAN or WAN, and changes to the published data should be replicated to the installation of SQL Server subscriber.

Merge Replication is used if the subscriber is autonomous and often operate in a way disconnected. For example a user at the computer not connected can make changes in his data on the subscript when offline. When the user is connected to the network and start the synchronization, the subscript will be merged line by line with published data on the server. Just new rows and changes rows involved process.

Therefore, the publication can be simultaneously on multiple subscriber is offline. Conflict will easily occur when a user makes changes to the same data. Merge Replication supports a number of conflict resolution scheme that detects conflicts at the row or column level.

Microsoft Data Transformation Services (DTS) is a facility for the transformation and integration of data transfer. DTS is a COM object model for transferring data between a source and destination data. DTS object model will run the DTS package. A DTS package is a set of transformation commands and transfer data can be stored in a file in the system database msdb, or in Microsoft Repository.

SQL Server 2000 Architecture

noreply@blogger.com (Admin) — Sat, 17 Jul 2010 17:03:00 +0000

SQL Server 2000 is a database engine client / server is different from traditional single computer database, which uses a file system usage together for example dBASE, Microsoft Jet, FoxPro. Database systems that use the shared files depends on a single client processes per user to manipulate the data in shared files on network servers. In multi-user environment will appear to various problems, namely concurrency control using locking mechanism at the network layer.

Database system with client / server such as SQL Server 2000 using a process server to manipulate data, and requires the client process associated with the process server using IPC mechanism (inter-process communication), a local or remote, such as TCP / IP socket. The process server is a server application that processes SQL commands. The process server also handles concurrency by using more sophisticated locking mechanism from the network file system, which is used together. After running the server processes the commands, the results will be sent back to the client process through the mechanism of IPC. In this way client / server provides better access to data, which is shared by many users.

SQL Server 2000 provides a good language and interface for programming and communication with the server. Transtact-SQL Server is a programming language that is a superset of ANSI-SQL. ANSI-SQL defines four basic commands for manipulating the data which are SELECT, INSERT, UPDATE and DELETE, and a number of commands to define the database structure. Transtact-SQL add on a few things on the ANSI-SQL. Additional programming is the construction, which allows the use of stored procedures and triggers to modify data that will be executed for a particular event occurs.

Transtact-SQL Overview

noreply@blogger.com (Admin) — Sat, 17 Jul 2010 07:18:00 +0000

Transtact-SQL is the development of the SQL language published by the International Organization for Standardization (ISO) and American National Standards Institute (ANSI). Transtact-SQL, is needed in the SQL server. Every application that communicates with SQL Server will send Transtact-SQL command to the server.

Transtact-SQL command is a set of programs that run on ata data objects in the database. SQL Server has three types of Transtact-SQL, the Data Definition Language, Data Control Language, and Manipulation Language.

Data Definition Language (DDL)

Data Definition Language (DDL) is part of a database management system, used to define the data and manage all the attributes and properties from a database. With the DDL you can specify the column definitions, key columns, files location, and storage strategy.

General form of DDL statements:
CREATE object_name
ALTER object_name
DROP object_name

Data Control Language (DCL)

DCL is used to adjust the controls on database objects. Commands can be used is the GRANT and REVOKE.

GRANT command is used to give rights to user to access a database. REVOKE command is used to dispose of the rights granted to the user

Manipulation Language (DML)

DML is used to display, add, modify, and delete data in the objects defined by DDL. DDL Commands are SELECT, INSERT, UPDATE, DELETE.

SELECT command will search the records from the database
INSERT command is used to add new records on a table or view.
UPDATE command is used to change the data in a table.
DELETE command is used to remove data in a table.

What is database

noreply@blogger.com (Admin) — Sat, 17 Jul 2010 05:31:00 +0000

The database is a collection of related data, In the past, the database is a file, for example students.dbf, employees.dbf, which contains a table. In the student file, containing the related columns, such as ID, Name, Address, etc.

In SQL Server, the database is not a file but it is a logical concept that contains a collection of related objects. For example a database containing data, database structure, indexes, securities, view, and stored procedures.

Objects in a database:

Tables, objects that contain the types of data and raw data.
Column. A table containing the columns to accommodate the data. The column has a type, and a unique name.
Data type. A column has a data type. Type of data that can be selected are the characters, numeric, date, boolean, etc.
Stored procedure. Is the sql commands that make up the macro. By running the stored procedure means that you run the sql commands in a stored procedure.
Trigger. Trigger is a stored procedure that is activated when the data are added, modified, or deleted from the database. Trigger is used to ensure the integrity rules within the database.
Rule. Rule applied to the column so that the data being entered must be in accordance with the rules.
Primary Key. The main key to ensure every line of unique data, can be distinguished from other data.
Foreign Key. Is the foreign key columns that refer to primary key or unique constraint on another table. Primary key and foreign keys are used to connect a table with other tables.
Constraints. Constraint is that the data integrity mechanism based on servers and implemented by the system.
Default. Default contained in the field, so if the column is not filled with data, then filled in with default values.
View. View is a query that uses several tables, and stored in the database. View can select multiple columns of a table or to connect multiple tables. View can be used to maintain data security.
Index. Index help organize the data so that queries become faster.