s1/c# Language and Database Technology Foundation/06-Program Data Center: Database

Entities and records

Entities, all objectively, can be described transactions.

Records, data tables stored in the database, each " row "actually corresponds to an entity, usually called a " record "in the database .

Each "column" in the table , such as number, name, age, ethnicity, etc., is often referred to as a " field " in the database.

A database Management system,DBMS, is asystem software consisting of an interconnected set of data and a set of programs that access data. This collection of data is often referred to as a database, which contains useful data information. The basic goal of database management system is to provide an environment that can easily and efficiently Access database information.

DatabaseSystem,dbsis an actual operational system, which can store, maintain and apply the data provided by the system, it is a collective composed of storage media, processing object and management system, which is usually composed of software, database and database administrator.

Relationship (from left to right, from outer to inner): User (including database administrator)→ application → database management system → operating system → hardware.

Data integrity (integrality) refers to the accuracy of data in a database.

the database in SQL Server can be divided into the following two types of applications.

System Database

User Database

The system database has the following several:

1.Master database

the Master database records All system-level information for the SQL Server system, including the following three categories.

• All login accounts and system configuration settings.
• Settings for all other databases and database files.
• initialization information for SQL Server.

2.Tempdb database

The Tempdb database holds all the temporary and temporary stored procedures, as well as the temporarily generated worksheets.

the TEMPDB database is recreated every time that SQL Server is started.

3.Model database

The model database can be used to create templates for all databases created on the system, for example, when a new, blank database is created using SQL statements, it is created using the default values specified in the template.

4.Msdb database

The MSDB database provides SQL Server agent for dispatching alerts, jobs, and logging operations, as well as records of database backups and restores, which are also written in the database.

5.Resource database

The Resource database is a special database and is a read-only database that contains all the system objects in SQL Server that are physically present in the Resource database, but logically They appear in the SYS schema of each database, so theResource database is a hidden database, and we cannot see him by using the SQL commands to view all the databases .

Password policy only Windows2003 more than the operating system will have, he asked to increase the complexity of the password to prevent cracking, to enhance security, requirements meet the following three conditions.

1, the password must not contain three or more than three two paragraphs with " blank "(Space, tab, line breaks, etc.) or "-","_","#" character to connect letters or numbers.

2. The password length is at least six characters.

3. Password contains at least three of the following four categories of characters.

• English capital letters (a~z)
• English Small Letter (a~z)
• 10 Basic numbers (0~9)
• Non-alphanumeric (such as! ,$,# or %).

Operational permissions fall into two categories:

The first class refers to what the user can do on a server-wide scale, which is determined by a fixed server role.

Fixed server roles are divided into nine categories, each with different operational permissions. Server role:dbcreate fixed server roles can create, change, delete, and restore any database.

The second class of permissions refers to the permissions that the login has on the specified database, and you can set permissions for a specific database in a user mapping item.

There are 10 fixed database operations permissions. For example,db_backupoperator permissions can back up the database,db_datareader can read the data in the database,db_denydatareader not allow the data to be read.

In SQL Server , the files that the database stores on disk include not only the database file itself, but also the transaction log files, which should contain at least one database file and one transaction log file.

1. database files

A database file is a file that holds database data and database objects, a database can have one or more database files, and a database file belongs to only one database. When there are multiple database files, one file is defined as the primary database file (Primary. mdf), and the primary database file has an . A database can have only one primary database file, and the other database files are called secondary database files (secondary).

2. transaction log File

The transaction log file is used to record the update of the database, and the operation information for the content changes in the database is recorded in this file when operations are performed on the database. The transaction log file has a file name extension of . ldf, and a database can have one or more transaction log files.

3. File Group

Similar to folders, filegroups are primarily used to allocate disk space and are managed, each filegroup has a group name, and as with database files, filegroups are divided into primary filegroups and sub-filegroups

The database is read-only: It is generally set to False, and if the value is True, the database will not allow the data to be written again.

Access restrictions: Specify which users have access to the database, with the following three possible values.

1,multiple: The normal state of the database, allowing multiple users to access the database at the same time.

2. Single: Used to maintain the state of the operation, only one user is allowed to access the database at a time.

3. Restricted: Only an administrator or a specific member can use the database.

Auto-off: If set to True , the database shuts down and frees resources when the last user exits. For those databases that are used frequently, this option is not set to True, otherwise it can be a burden due to the additional number of times the database is switched on.

Auto Shrink: If set to True , the database will automatically shrink periodically, freeing up unused database disk space.

Backing up and restoring Databases

There are four ways to backup your database in SQL Server, the following four types are available.

1. Full backup: Backs up all contents of the entire database, including transaction logs.

A full backup is a one-time backup of the entire database to the destination address, which is also restored once from the backup device.

2. Differential backup: Backs up only the portions of the data that have changed since the last full backup.

3. Transaction log backup: Backs up only the contents of the transaction log.

4. File and filegroup Backup: If the database creates multiple database files or filegroups, only some of these files in the database are backed up.

There are two ways of shrinking a database:

1, automatically shrink the database.

2, manually shrink the database.

There are three different ways to shrink file operations:

1, can only release unused space.

2, you can directly specify the size of the data shrink to how large.

3. You can migrate files to other files in the filegroup, and then empty the file directly.

S1/c# Language and Database Technology Foundation/06-Program Data Center: Database