Basic SQL Server Concepts

Basic structure of the database

1. Data sheet: Usually a two-dimensional table consisting of rows and columns.

2. Records or tuples: rows in a data table

3. Fields or properties: Columns in a data table

Primary key and foreign key

Primary key: A primary key uniquely identifies row data in a table, and a primary key value corresponds to one row of data. A primary key consists of one or more fields whose value is unique and does not allow null values. A table can have only one primary key.

Foreign keys: A relational database typically contains multiple tables, which can be associated by a foreign key.

Data integrity rules

1. Entity integrity rules (line integrity)

A tuple in a relationship must not have a null value on the primary Key's attribute

2. Domain integrity rules (column integrity)

Specifies whether a dataset is valid for a column or determines whether null values are allowed.

3. Referential integrity rules

If two tables are associated with each other, then referential integrity rules require that references to non-existent tuples are not allowed.

4. User-defined integrity rules

is a constraint on a specific data, which is determined by the application environment. It reflects the semantic requirements that the specific data involved in a particular application must meet.

System Database

Master Database: Database file--master.mdf; log file--mastlog.ldf

Records all system-level information for the SQL Server system. It records all login accounts and system configuration settings. The master database records all other database information, including the location of the database file, and it also records initialization information for SQL Server.

Model Database: Database file--model.mdf; log file--modellog.ldf

Serves as a template for all databases created on the instance of SQL Server. Modifications to the model database will be applied to all databases created at a later time.

MSDB database: Database file--msdbdata.mdf; log file--msdblog.ldf

For SQL Server Agent scheduled alerts and jobs, all task schedules, alarms, and operations are stored back in the database, which is also used to store all database backup histories. The SQL Server Agent will use this database.

TEMPDB Database: Database file--tempdb.mdf; log file--templog.ldf

Represents a workspace for saving temporary objects or intermediate result sets.

Microsoft SQL Server R2 components

1. SSMS:

All the components used to access, configure, control, manage, and develop SQL Server.

2. Performance Tools:

Provides SQL Server Profiler and Database Engine Tuning Advisor for monitoring databases and performance optimizations.

3. Business Intelligence Development Studio:

Integrated development environment for Analysis Services and Integration Services Resolute Solutions

4. SQL Server Configuration Manager:

Managing SQL Server services and network configuration

5. Import and Export data:

Provides a set of graphical tools and programmable objects for moving, copying, and transforming data.

6. Integration Service:

Build high-performance, integrated data solutions that include data extraction, transformation, and loading of databases.

Basic SQL Server Concepts