SQL数据库英文文献及翻译

合集下载

SQL数据库外文翻译--数据库的工作

Working with DatabasesThis chapter describes how to use SQL statements in embedded applications to control databases. There are three database statements that set up and open databases for access: SET DATABASE declares a database handle, associates the handle with an actual database file, and optionally assigns operational parameters for the database.SET NAMES optionally specifies the character set a client application uses for CHAR, VARCHAR, and text Blob data. The server uses this information totransli terate from a database‟s default character set to the client‟s character set on SELECT operations, and to transliterate from a client application‟s character set to the database character set on INSERT and UPDATE operations.g CONNECT opens a database, allocates system resources for it, and optionally assigns operational parameters for the database.All databases must be closed before a program ends. A database can be closed by using DISCONNECT, or by appending the RELEASE option to the final COMMIT or ROLLBACK in a program. Declaring a databaseBefore a database can be opened and used in a program, it must first be declared with SET DATABASE to:CHAPTER 3 WORKING WITH DATABASES. Establish a database handle. Associate the database handle with a database file stored on a local or remote node.A database handle is a unique, abbreviated alias for an actual database name. Database handles are used in subsequent CONNECT, COMMIT RELEASE, and ROLLBACK RELEASE statements to specify which databases they should affect. Except in dynamic SQL (DSQL) applications, database handles can also be used inside transaction blocks to qualify, or differentiate, table names when two or more open databases contain identically named tables.Each database handle must be unique among all variables used in a program. Database handles cannot duplicate host-language reserved words, and cannot be InterBase reserved words.The following statement illustrates a simple database declaration:EXEC SQLSET DATABASE DB1 = ‟employee.gdb‟;This database declaration identifies the database file, employee.gdb, as a database the program uses, and assigns the database a handle, or alias, DB1.If a program runs in a directory different from the directory that contains the database file, then the file name specification in SET DATABASE must include a full path name, too. For example, the following SET DATABASE declaration specifies the full path to employee.gdb:EXEC SQLSET DATABASE DB1 = ‟/interbase/examples/employee.gdb‟;If a program and a database file it uses reside on different hosts, then the file name specification must also include a host name. The following declaration illustrates how a Unix host name is included as part of the database file specification on a TCP/IP network:EXEC SQLSET DATABASE DB1 = ‟jupiter:/usr/interbase/examples/employee.gdb‟;On a Windows network that uses the Netbeui protocol, specify the path as follows: EXEC SQLSET DATABASE DB1 = ‟//venus/C:/Interbase/examples/employee.gdb‟; DECLARING A DATABASEEMBEDDED SQL GUIDE 37Declaring multiple databasesAn SQL program, but not a DSQL program, can access multiple databases at the same time. In multi-database programs, database handles are required. A handle is used to:1. Reference individual databases in a multi-database transaction.2. Qualify table names.3. Specify databases to open in CONNECT statements.Indicate databases to close with DISCONNECT, COMMIT RELEASE, and ROLLBACK RELEASE.DSQL programs can access only a single database at a time, so database handle use is restricted to connecting to and disconnecting from a database.In multi-database programs, each database must be declared in a separate SET DATABASE statement. For example, the following code contains two SET DATABASE statements:. . .EXEC SQLSET DATABASE DB2 = ‟employee2.gdb‟;EXEC SQLSET DATABASE DB1 = ‟employee.gdb‟;. . .4Using handles for table namesWhen the same table name occurs in more than one simultaneously accessed database, a database handle must be used to differentiate one table name from another. The database handle is used as a prefix to table names, and takes the formhandle.table.For example, in the following code, the database handles, TEST and EMP, are used to distinguish between two tables, each named EMPLOYEE:. . .EXEC SQLDECLARE IDMATCH CURSOR FORSELECT TESTNO INTO :matchid FROM TEST.EMPLOYEEWHERE TESTNO > 100;EXEC SQLDECLARE EIDMATCH CURSOR FORSELECT EMPNO INTO :empid FROM EMP.EMPLOYEEWHERE EMPNO = :matchid;. . .CHAPTER 3 WORKING WITH DATABASES38 INTERBASE 6IMPORTANTThis use of database handles applies only to embedded SQL applications. DSQL applications cannot access multiple databases simultaneously.4Using handles with operationsIn multi-database programs, database handles must be specified in CONNECT statements to identify which databases among several to open and prepare for use in subsequent transactions.Database handles can also be used with DISCONNECT, COMMIT RELEASE, and ROLLBACKRELEASE to specify a subset of open databases to close.To open and prepare a database w ith CONNECT, see “Opening a database” on page 41.To close a database with DISCONNECT, COMMIT RELEASE, or ROLLBACK RELEASE, see“Closing a database” on page 49. To learn more about using database handles in transactions, see “Accessing an open database” on p age 48.Preprocessing and run time databasesNormally, each SET DATABASE statement specifies a single database file to associate with a handle. When a program is preprocessed, gpre uses the specified file to validate the program‟s table and column referenc es. Later, when a user runs the program, the same database file is accessed. Different databases can be specified for preprocessing and run time when necessary.4Using the COMPILETIME clause A program can be designed to run against any one of several identically structured databases. In other cases, the actual database that a program will use at runtime is not available when a program is preprocessed and compiled. In such cases, SET DATABASE can include a COMPILETIME clause to specify a database for gpre to test against during preprocessing. For example, the following SET DATABASE statement declares that employee.gdb is to be used by gpre during preprocessing: EXEC SQLSET DATABASE EMP = COMPILETIME ‟employee.gdb‟;IMPORTANTThe file specification that follows the COMPILETIME keyword must always be a hard-coded, quoted string.DECLARING A DATABASEEMBEDDED SQL GUIDE 39When SET DATABASE uses the COMPILETIME clause, but no RUNTIME clause, and does not specify a different database file specification in a subsequent CONNECT statement, the same database file is used both for preprocessing and run time. To specify different preprocessing and runtime databases with SET DATABASE, use both the COMPILETIME andRUNTIME clauses.4Using the RUNTIME clauseWhen a database file is specified for use during preprocessing, SET DATABASE can specify a different database to use at run time by including the RUNTIME keyword and a runtime file specification:EXEC SQLSET DATABASE EMP = COMPILETIME ‟employee.gdb‟RUNTIME ‟employee2.gdb‟;The file specification that follows the RUNTIME keyword can be either ahard-coded, quoted string, or a host-language variable. For example, the following C code fragment prompts the user for a database name, and stores the name in a variable that is used later in SET DATABASE:. . .char db_name[125];. . .printf("Enter the desired database name, including node and path):\n");gets(db_name);EXEC SQLSET DATABASE EMP = COMPILETIME ‟employee.gdb‟ RUNTIME : db_name; . . .Note host-language variables in SET DATABASE must be preceded, as always, by a colon.Controlling SET DATABASE scopeBy default, SET DATABASE creates a handle that is global to all modules in an application.A global handle is one that may be referenced in all host-language modules comprising the program. SET DATABASE provides two optional keywords to change the scope of a declaration:g STATIC limits declaration scope to the module containing the SET DATABASE statement. No other program modules can see or use a database handle declared STATIC.CHAPTER 3 WORKING WITH DATABASES40 INTERBASE 6EXTERN notifies gpre that a SET DATABASE statement in a module duplicates a globally-declared database in another module. If the EXTERN keyword is used, then another module must contain the actual SET DATABASE statement, or an error occurs during compilation.The STATIC keyword is used in a multi-module program to restrict database handle access to the single module where it is declared. The following example illustrates the use of theSTATIC keyword:EXEC SQLSET DATABASE EMP = STATIC ‟employee.gdb‟;The EXTERN keyword is used in a multi-module program to signal that SET DATABASE in one module is not an actual declaration, but refers to a declaration made in a different module. Gpre uses this information during preprocessing. The following example illustrates the use of the EXTERN keyword:EXEC SQLSET DATABASE EMP = EXTERN ‟employee.gdb‟;If an application contains an EXTERN reference, then when it is used at run time, the actual SET DATABASE declaration must be processed first, and the database connected before other modules can access it.A single SET DATABASE statement can contain either the STATIC or EXTERN keyword, but not both. A scope declaration in SET DATABASE applies to both COMPILETIME and RUNTIME databases.Specifying a connection character setWhen a client application connects to a database, it may have its own character set requirements. The server providing database access to the client does not know about these requirements unless the client specifies them. The client application specifies its character set requirement using the SET NAMES statement before it connects to the database.SET NAMES specifies the character set the server should use when translating data from the database to the client application. Similarly, when the client sends data to the database, the server translates the data from the client‟s character set to the database‟s default character set (or the character set for an individual column if it differs from the databas e‟s default character set). For example, the following statements specify that the client is using the DOS437 character set, then connect to the database:EXEC SQLOPENING A DATABASEEMBEDDED SQL GUIDE 41SET NAMES DOS437;EXEC SQLCONNECT ‟europe.gdb‟ USER ‟JAMES‟ PASSWORD ‟U4EEAH‟;For more information about character sets, see the Data Definition Guide. For the complete syntax of SET NAMES and CONNECT, see the Language Reference. Opening a databaseAfter a database is declared, it must be attached with a CONNECT statement before it can be used. CONNECT:1. Allocates system resources for the database.2. Determines if the database file is local, residing on the same host where the application itself is running, or remote, residing on a different host.3. Opens the database and examines it to make sure it is valid.InterBase provides transparent access to all databases, whether local or remote. If the database structure is invalid, the on-disk structure (ODS) number does not correspond to the one required by InterBase, or if the database is corrupt, InterBase reports an error, and permits no further access. Optionally, CONNECT can be used to specify:4. A user name and password combination that is checked against the server‟s security database before allowing the connect to succeed. User names can be up to 31 characters.Passwords are restricted to 8 characters.5. An SQL role name that the user adopts on connection to the database, provided that the user has previously been granted membership in the role. Regardless of role memberships granted, the user belongs to no role unless specified with this ROLE clause.The client can specify at most one role per connection, and cannot switch roles except by reconnecting.6. The size of the database buffer cache to allocate to the application when the default cache size is inappropriate.Using simple CONNECT statementsIn its simplest form, CONNECT requires one or more database parameters, each specifying the name of a database to open. The name of the database can be a: Database handle declared in a previous SET DATABASE statement.CHAPTER 3 WORKING WITH DATABASES42 INTERBASE 61. Host-language variable.2. Hard-coded file name.4Using a database handleIf a program uses SET DATABASE to provide database handles, those handles should be used in subsequent CONNECT statements instead of hard-coded names. For example,. . .EXEC SQLSET DATABASE DB1 = ‟employee.gdb‟;EXEC SQLSET DATABASE DB2 = ‟employee2.gdb‟;EXEC SQLCONNECT DB1;EXEC SQLCONNECT DB2;. . .There are several advantages to using a database handle with CONNECT:1. Long file specifications can be replaced by shorter, mnemonic handles.2. Handles can be used to qualify table names in multi-database transactions. DSQL applications do not support multi-database transactions.3. Handles can be reassigned to other databases as needed.4. The number of database cache buffers can be specified as an additional CONNECT parameter.For more information about setting the number of database cache buffers, see “Setting d atabase cache buffers” on page 47. 4Using strings or host-language variables Instead of using a database handle, CONNECT can use a database name supplied at run time. The database name can be supplied as either a host-language variable or a hard-coded, quoted string.The following C code demonstrates how a program accessing only a single database might implement CONNECT using a file name solicited from a user at run time:. . .char fname[125];. . .printf(‟Enter the desired database name, including nodeand path):\n‟);OPENING A DATABASEEMBEDDED SQL GUIDE 43gets(fname);. . .EXEC SQLCONNECT :fname;. . .TipThis technique is especially useful for programs that are designed to work with many identically structured databases, one at a time, such as CAD/CAM or architectural databases.MULTIPLE DATABASE IMPLEMENTATIONTo use a database specified by the user as a host-language variable in a CONNECT statement in multi-database programs, follow these steps:1. Declare a database handle using the following SET DATABASE syntax:EXEC SQLSET DATABASE handle = COMPILETIME ‟ dbname‟;Here, handle is a hard-coded database handle supplied by the programmer, dbnameis a quoted, hard-coded database name used by gpre during preprocessing.2. Prompt the user for a database to open.3. Store the database name entered by the user in a host-language variable.4. Use the handle to open the database, associating the host-language variable with the handle using the following CONNECT syntax:EXEC SQLCONNECT : variable AS handle;The following C code illustrates these steps:. . .char fname[125];. . .EXEC SQLSET DATABASE DB1 = ‟employee.gdb‟;printf("Enter the desired database name, including nodeand path):\n");gets(fname);EXEC SQLCONNECT :fname AS DB1;. . .CHAPTER 3 WORKING WITH DATABASES44 INTERBASE 6In this example, SET DATABASE provides a hard-coded database file name for preprocessing with gpre. When a user runs the program, the database specified in the variable, fname, is used instead. 4Using a hard-coded database namesIN SINGE-DATABASE PROGRAMSIn a single-database program that omits SET DATABASE, CONNECT must contain a hard-coded, quoted file name in the following format:EXEC SQLCONNECT ‟[ host[ path]] filename‟; host is required only if a program and a dat abase file it uses reside on different nodes.Similarly, path is required only if the database file does not reside in the current working directory. For example, the following CONNECT statement contains ahard-coded file name that includes both a Unix host name and a path name:EXEC SQLCONNECT ‟valdez:usr/interbase/examples/employee.gdb‟;Note Host syntax is specific to each server platform.IMPORTANT A program that accesses multiple databases cannot use this form of CONNECT.IN MULTI-DATABASE PROGRAMSA program that accesses multiple databases must declare handles for each of them in separate SET DATABASE statements. These handles must be used in subsequent CONNECT statements to identify specific databases to open:. . .EXEC SQLSET DATABASE DB1 = ‟employee.gdb‟;EXEC SQLSET DATABASE DB2 = ‟employee2.gdb‟;EXEC SQLCONNECT DB1;EXEC SQLCONNECT DB2;. . .Later, when the program closes these databases, the database handles are no longer in use. These handles can be reassigned to other databases by hard-coding a file name in a subsequent CONNECT statement. For example,OPENING A DATABASEEMBEDDED SQL GUIDE 45. . .EXEC SQLDISCONNECT DB1, DB2;EXEC SQLCONNECT ‟project.gdb‟ AS DB1;. . .Additional CONNECT syntaxCONNECT supports several formats for opening databases to provide programming flexibility. The following table outlines each possible syntax, provides descriptions and examples, and indicates whether CONNECT can be used in programs that access single or multiple databases:For a complete discussion of CONNECT syntax and its uses, see the Language Reference.Syntax Description ExampleSingle accessMultiple accessCONNECT …dbfile‟; Opens a single, hard-coded database file, dbfile.EXEC SQLCONNECT …employee.gdb‟;Yes NoCONNECT handle; Opens the database file associated with a previously declared database handle. This is the preferred CONNECT syntax.EXEC SQLCONNECT EMP;Yes YesCONNECT …dbfile‟ AS handle;Opens a hard-coded database file, dbfile, and assigns a previously declared database handle to it.EXEC SQLCONNECT …employee.gdb‟AS EMP;Yes Yes CONNECT :varname AS handle;Opens the database file stored in the host-language variable, varname, and assigns a previously declared database handle to it.EXEC SQL CONNECT :fname AS EMP;Yes YesTABLE 3.1 CONNECT syntax summaryCHAPTER 3 WORKING WITH DATABASES46 INTERBASE 6Attaching to multiple databasesCONNECT can attach to multiple databases. To open all databases specified in previous SETDATABASE statements, use either of the following CONNECT syntax options: EXEC SQLCONNECT ALL;EXEC SQLCONNECT DEFAULT;CONNECT can also attach to a specified list of databases. Separate each database request from others with commas. For example, the following statement opens two databases specified by their handles:EXEC SQLCONNECT DB1, DB2;The next statement opens two hard-coded database files and also assigns them to previously declared handles:EXEC SQLCONNECT ‟employee.gdb‟ AS DB1, ‟employee2.gdb‟ AS DB2;Tip Opening multiple databases with a single CONNECT is most effective when a program‟s database access is simple and clear. In complex programs that open and close several databases, that substitute database names with host-language variables, or that assign multiple handles to the same database, use separate CONNECT statements to make program code easier to read, debug, and modify.Handling CONNECT errors. The WHENEVER statement should be used to trap and handle runtime errors that occur during database declaration. The following C code fragment illustrates an error-handling routine that displays error messages and ends the program in an orderly fashion:. . .EXEC SQLWHENEVER SQLERRORGOTO error_exit;. . .OPENING A DATABASEEMBEDDED SQL GUIDE 47:error_exitisc_print_sqlerr(sqlcode, status_vector);EXEC SQLDISCONNECT ALL;exit(1);. . .For a complete discussion of SQL error handling, see Chapter 12, “Error Handling and Recovery.”数据库的工作这章描述怎样使用在嵌入式应用过程中的SQL语句控制数据库。

外文翻译原文-Appropriate Uses For SQLite

毕业设计（论文）——外文翻译（原文）Appropriate Uses For SQLiteSQLite is different from most other SQL database engines in that its primary design goal is to be simple:●Simple to administer●Simple to operate●Simple to embed in a larger program●Simple to maintain and customizeMany people like SQLite because it is small and fast. But those qualities are just happy accidents. Users also find that SQLite is very reliable. Reliability is a consequence of simplicity. With less complication, there is less to go wrong. So, yes, SQLite is small, fast, and reliable, but first and foremost, SQLite strives to be simple.Simplicity in a database engine can be either a strength or a weakness, depending on what you are trying to do. In order to achieve simplicity, SQLite has had to sacrifice other characteristics that some people find useful, such as high concurrency, fine-grained access control, a rich set of built-in functions, stored procedures, esoteric SQL language features, XML and/or Java extensions, tera- or peta-byte scalability, and so forth. If you need some of these features and do not mind the added complexity that they bring, then SQLite is probably not the database for you. SQLite is not intended to be an enterprise database engine. It is not designed to compete with Oracle or PostgreSQL.The basic rule of thumb for when it is appropriate to use SQLite is this: Use SQLite in situations where simplicity of administration, implementation, and maintenance are more important than the countless complex features that enterprise database engines provide. As it turns out, situations where simplicity is the better choice are more common than many people realize.Another way to look at SQLite is this: SQLite is not designed to replace Oracle. It is designed to replace fopen( ).Situations Where SQLite Works W ell●Application File FormatSQLite has been used with great success as the on-disk file format for desktop applications such as financial analysis tools, CAD packages, record keeping programs, and so forth. The traditional File/Open operation does an sqlite3_open() and executes a BEGIN TRANSACTION to get exclusive access to the content. File/Save does a COMMIT followed by another BEGIN TRANSACTION. The use of transactions guarantees that updates to the application file are atomic, durable, isolated, and consistent.Temporary triggers can be added to the database to record all changes into a (temporary) undo/redo log table. These changes can then be played back when the user presses the Undo and Redo buttons. Using this technique, an unlimited depth undo/redo implementation can be written in surprisingly little code.●Embedded devices and applicationsBecause an SQLite database requires little or no administration, SQLite is a good choice for devices or services that must work unattended and without human support. SQLite is a good fit foruse in cellphones, PDAs, set-top boxes, and/or appliances. It also works well as an embedded database in downloadable consumer applications.●WebsitesSQLite usually will work great as the database engine for low to medium traffic websites (which is to say, 99.9% of all websites). The amount of web traffic that SQLite can handle depends, of course, on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.●Replacement for ad hoc disk filesMany programs use fopen( ), fread( ), and fwrite( ) to create and manage files of data in home-grown formats. SQLite works particularly well as a replacement for these ad hoc data files.●Internal or temporary databasesFor programs that have a lot of data that must be sifted and sorted in diverse ways, it is often easier and quicker to load the data into an in-memory SQLite database and use queries with joins and ORDER BY clauses to extract the data in the form and order needed rather than to try to code the same operations manually. Using an SQL database internally in this way also gives the program greater flexibility since new columns and indices can be added without having to recode every query.●Command-line dataset analysis toolExperienced SQL users can employ the command-line sqlite program to analyze miscellaneous datasets. Raw data can be imported from CSV files, then that data can be sliced and diced to generate a myriad of summary reports. Possible uses include website log analysis, sports statistics analysis, compilation of programming metrics, and analysis of experimental results.Y ou can also do the same thing with an enterprise client/server database, of course. The advantages to using SQLite in this situation are that SQLite is much easier to set up and the resulting database is a single file that you can store on a floppy disk or flash-memory stick or email to a colleague.●Stand-in for an enterprise database during demos or testingIf you are writing a client application for an enterprise database engine, it makes sense to use a generic database backend that allows you to connect to many different kinds of SQL database engines. It makes even better sense to go ahead and include SQLite in the mix of supported databases and to statically link the SQLite engine in with the client. That way the client program can be used standalone with an SQLite data file for testing or for demonstrations.●Database PedagogyBecause it is simple to setup and use (installation is trivial: just copy the sqlite or sqlite.exe executable to the target machine and run it) SQLite makes a good database engine for use in teaching SQL. Students can easily create as many databases as they like and can email databases to the instructor for comments or grading. For more advanced students who are interested in studying how an RDBMS is implemented, the modular and well-commented and documented SQLite code can serve as a good basis. This is not to say that SQLite is an accurate model of how other database engines are implemented, but rather a student who understands how SQLite works can more quickly comprehend the operational principles of other systems.●Experimental SQL language extensionsThe simple, modular design of SQLite makes it a good platform for prototyping new, experimental database language features or ideas.Situations Where Another RDBMS May Work Better●Client/Server ApplicationsIf you have many client programs accessing a common database over a network, you should consider using a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, the file locking logic of many network filesystems implementation contains bugs (on both Unix and Windows). If file locking does not work like it should, it might be possible for two or more client programs to modify the same part of the same database at the same time, resulting in database corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it.A good rule of thumb is that you should avoid using SQLite in situations where the same database will be accessed simultaneously from many computers over a network filesystem.●High-volume WebsitesSQLite will normally work fine as the database backend to a website. But if you website is so busy that you are thinking of splitting the database component off onto a separate machine, then you should definitely consider using an enterprise-class client/server database engine instead of SQLite.●Very large datasetsWith the default page size of 1024 bytes, an SQLite database is limited in size to 2 terabytes (241 bytes). And even if it could handle larger databases, SQLite stores the entire database in a single disk file and many filesystems limit the maximum size of files to something less than this. So if you are contemplating databases of this magnitude, you would do well to consider using a client/server database engine that spreads its content across multiple disk files, and perhaps across multiple volumes.●High ConcurrencySQLite uses reader/writer locks on the entire database file. That means if any process is reading from any part of the database, all other processes are prevented from writing any other part of the database. Similarly, if any one process is writing to the database, all other processes are prevented from reading any other part of the database. For many situations, this is not a problem. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.出处：/whentouse.html。

数据库中英文对照外文翻译文献

中英文对照外文翻译Database Management SystemsA database (sometimes spelled data base) is also called an electronic database , referring to any collection of data, or information, that is specially organized for rapid search and retrieval by a computer. Databases are structured to facilitate the storage, retrieval , modification, and deletion of data in conjunction with various data-processing operations .Databases can be stored on magnetic disk or tape, optical disk, or some other secondary storage device.A database consists of a file or a set of files. The information in these files may be broken down into records, each of which consists of one or more fields. Fields are the basic units of data storage , and each field typically contains information pertaining to one aspect or attribute of the entity described by the database . Using keywords and various sorting commands, users can rapidly search , rearrange, group, and select the fields in many records to retrieve or create reports on particular aggregate of data.Complex data relationships and linkages may be found in all but the simplest databases .The system software package that handles the difficult tasks associated with creating ,accessing, and maintaining database records is called a database management system(DBMS).The programs in a DBMS package establish an interface between the database itself and the users of the database.. (These users may be applications programmers, managers and others with information needs, and various OS programs.)A DBMS can organize, process, and present selected data elements form the database. This capability enables decision makers to search, probe, and query database contents in order to extract answers to nonrecurring and unplanned questions that aren’t available in regular reports. These questions might initially be vague and/or poorly defined ,but people can “browse” through the database until they have the needed information. In short, the DBMS will “manage” the stored data items and assemble the needed items from the common database in response to the queries of those who aren’t programmers.A database management system (DBMS) is composed of three major parts:(1)a storage subsystemthat stores and retrieves data in files;(2) a modeling and manipulation subsystem that provides the means with which to organize the data and to add , delete, maintain, and update the data;(3)and an interface between the DBMS and its users. Several major trends are emerging that enhance the value and usefulness of database management systems;Managers: who require more up-to-data information to make effective decisionCustomers: who demand increasingly sophisticated information services and more current information about the status of their orders, invoices, and accounts.Users: who find that they can develop custom applications with database systems in a fraction of the time it takes to use traditional programming languages.Organizations : that discover information has a strategic value; they utilize their database systems to gain an edge over their competitors.The Database ModelA data model describes a way to structure and manipulate the data in a database. The structural part of the model specifies how data should be represented(such as tree, tables, and so on ).The manipulative part of the model specifies the operation with which to add, delete, display, maintain, print, search, select, sort and update the data.Hierarchical ModelThe first database management systems used a hierarchical model-that is-they arranged records into a tree structure. Some records are root records and all others have unique parent records. The structure of the tree is designed to reflect the order in which the data will be used that is ,the record at the root of a tree will be accessed first, then records one level below the root ,and so on.The hierarchical model was developed because hierarchical relationships are commonly found in business applications. As you have known, an organization char often describes a hierarchical relationship: top management is at the highest level, middle management at lower levels, and operational employees at the lowest levels. Note that within a strict hierarchy, each level of management may have many employees or levels of employees beneath it, but each employee has only one manager. Hierarchical data are characterized by this one-to-many relationship among data.In the hierarchical approach, each relationship must be explicitly defined when the database is created. Each record in a hierarchical database can contain only one key field and only one relationship is allowed between any two fields. This can create a problem because data do not always conform to such a strict hierarchy.Relational ModelA major breakthrough in database research occurred in 1970 when E. F. Codd proposed a fundamentally different approach to database management called relational model ,which uses a table asits data structure.The relational database is the most widely used database structure. Data is organized into related tables. Each table is made up of rows called and columns called fields. Each record contains fields of data about some specific item. For example, in a table containing information on employees, a record would contain fields of data such as a person’s last name ,first name ,and street address.Structured query language(SQL)is a query language for manipulating data in a relational database .It is nonprocedural or declarative, in which the user need only specify an English-like description that specifies the operation and the described record or combination of records. A query optimizer translates the description into a procedure to perform the database manipulation.Network ModelThe network model creates relationships among data through a linked-list structure in which subordinate records can be linked to more than one parent record. This approach combines records with links, which are called pointers. The pointers are addresses that indicate the location of a record. With the network approach, a subordinate record can be linked to a key record and at the same time itself be a key record linked to other sets of subordinate records. The network mode historically has had a performance advantage over other database models. Today , such performance characteristics are only important in high-volume ,high-speed transaction processing such as automatic teller machine networks or airline reservation system.Both hierarchical and network databases are application specific. If a new application is developed ,maintaining the consistency of databases in different applications can be very difficult. For example, suppose a new pension application is developed .The data are the same, but a new database must be created.Object ModelThe newest approach to database management uses an object model , in which records are represented by entities called objects that can both store data and provide methods or procedures to perform specific tasks.The query language used for the object model is the same object-oriented programming language used to develop the database application .This can create problems because there is no simple , uniform query language such as SQL . The object model is relatively new, and only a few examples of object-oriented database exist. It has attracted attention because developers who choose an object-oriented programming language want a database based on an object-oriented model. Distributed DatabaseSimilarly , a distributed database is one in which different parts of the database reside on physically separated computers . One goal of distributed databases is the access of informationwithout regard to where the data might be stored. Keeping in mind that once the users and their data are separated , the communication and networking concepts come into play .Distributed databases require software that resides partially in the larger computer. This software bridges the gap between personal and large computers and resolves the problems of incompatible data formats. Ideally, it would make the mainframe databases appear to be large libraries of information, with most of the processing accomplished on the personal computer.A drawback to some distributed systems is that they are often based on what is called a mainframe-entire model , in which the larger host computer is seen as the master and the terminal or personal computer is seen as a slave. There are some advantages to this approach . With databases under centralized control , many of the problems of data integrity that we mentioned earlier are solved . But today’s personal computers, departmental computers, and distributed processing require computers and their applications to communicate with each other on a more equal or peer-to-peer basis. In a database, the client/server model provides the framework for distributing databases.One way to take advantage of many connected computers running database applications is to distribute the application into cooperating parts that are independent of one anther. A client is an end user or computer program that requests resources across a network. A server is a computer running software that fulfills those requests across a network . When the resources are data in a database ,the client/server model provides the framework for distributing database.A file serve is software that provides access to files across a network. A dedicated file server is a single computer dedicated to being a file server. This is useful ,for example ,if the files are large and require fast access .In such cases, a minicomputer or mainframe would be used as a file server. A distributed file server spreads the files around on individual computers instead of placing them on one dedicated computer.Advantages of the latter server include the ability to store and retrieve files on other computers and the elimination of duplicate files on each computer. A major disadvantage , however, is that individual read/write requests are being moved across the network and problems can arise when updating files. Suppose a user requests a record from a file and changes it while another user requests the same record and changes it too. The solution to this problems called record locking, which means that the first request makes others requests wait until the first request is satisfied . Other users may be able to read the record, but they will not be able to change it .A database server is software that services requests to a database across a network. For example, suppose a user types in a query for data on his or her personal computer . If the application is designed with the client/server model in mind ,the query language part on the personal computer simple sends the query across the network to the database server and requests to be notified when the data are found.Examples of distributed database systems can be found in the engineering world. Sun’s Network Filing System(NFS),for example, is used in computer-aided engineering applications to distribute data among the hard disks in a network of Sun workstation.Distributing databases is an evolutionary step because it is logical that data should exist at the location where they are being used . Departmental computers within a large corporation ,for example, should have data reside locally , yet those data should be accessible by authorized corporate management when they want to consolidate departmental data . DBMS software will protect the security and integrity of the database , and the distributed database will appear to its users as no different from the non-distributed database .In this information age, the data server has become the heart of a company. This one piece of software controls the rhythm of most organizations and is used to pump information lifeblood through the arteries of the network. Because of the critical nature of this application, the data server is also the one of the most popular targets for hackers. If a hacker owns this application, he can cause the company's "heart" to suffer a fatal arrest.Ironically, although most users are now aware of hackers, they still do not realize how susceptible their database servers are to hack attacks. Thus, this article presents a description of the primary methods of attacking database servers (also known as SQL servers) and shows you how to protect yourself from these attacks.You should note this information is not new. Many technical white papers go into great detail about how to perform SQL attacks, and numerous vulnerabilities have been posted to security lists that describe exactly how certain database applications can be exploited. This article was written for the curious non-SQL experts who do not care to know the details, and as a review to those who do use SQL regularly.What Is a SQL Server?A database application is a program that provides clients with access to data. There are many variations of this type of application, ranging from the expensive enterprise-level Microsoft SQL Server to the free and open source mySQL. Regardless of the flavor, most database server applications have several things in common.First, database applications use the same general programming language known as SQL, or Structured Query Language. This language, also known as a fourth-level language due to its simplistic syntax, is at the core of how a client communicates its requests to the server. Using SQL in its simplest form, a programmer can select, add, update, and delete information in a database. However, SQL can also be used to create and design entire databases, perform various functions on the returned information, and even execute other programs.To illustrate how SQL can be used, the following is an example of a simple standard SQL query and a more powerful SQL query:Simple: "Select * from dbFurniture.tblChair"This returns all information in the table tblChair from the database dbFurniture.Complex: "EXEC master..xp_cmdshell 'dir c:\'"This short SQL command returns to the client the list of files and folders under the c:\ directory of the SQL server. Note that this example uses an extended stored procedure that is exclusive to MS SQL Server.The second function that database server applications share is that they all require some form of authenticated connection between client and host. Although the SQL language is fairly easy to use, at least in its basic form, any client that wants to perform queries must first provide some form of credentials that will authorize the client; the client also must define the format of the request and response.This connection is defined by several attributes, depending on the relative location of the client and what operating systems are in use. We could spend a whole article discussing various technologies such as DSN connections, DSN-less connections, RDO, ADO, and more, but these subjects are outside the scope of this article. If you want to learn more about them, a little Google'ing will provide you with more than enough information. However, the following is a list of the more common items included in a connection request.Database sourceRequest typeDatabaseUser IDPasswordBefore any connection can be made, the client must define what type of database server it is connecting to. This is handled by a software component that provides the client with the instructions needed to create the request in the correct format. In addition to the type of database, the request type can be used to further define how the client's request will be handled by the server. Next comes the database name and finally the authentication information.All the connection information is important, but by far the weakest link is the authentication information—or lack thereof. In a properly managed server, each database has its own users with specifically designated permissions that control what type of activity they can perform. For example, a user account would be set up as read only for applications that need to only access information. Another account should be used for inserts or updates, and maybe even a third account would be used for deletes.This type of account control ensures that any compromised account is limited in functionality. Unfortunately, many database programs are set up with null or easy passwords, which leads to successful hack attacks.译文数据库管理系统介绍数据库（database,有时拼作data base）又称为电子数据库，是专门组织起来的一组数据或信息，其目的是为了便于计算机快速查询及检索。

外文文献-中文翻译-数据库

外文文献-中文翻译-数据库英文原文2:《DBA Survivor: Become a Rock Star DBA》by Thomas LaRock，Published By Apress.2010You know that a database is a collection of logically related data elements that may be structured in various ways lo meet the multiple processing and retrieval needs of organizations and individuals. There’s nothing new about databases—early ones were chiseled in stone, penned on scrolls, and written on index cards. But now databases are commonly recorded on magnetizable media, and computer programs are required to perform the necessary storage and retrieval operations.Yo u’ll see in the following pages that complex data relationships and linkages may be found in all but the simplest databases. The system software package that handles the difficult tasks associated with creating, accessing, and maintaining database records is called a database management system (DBMS) .The programs in a DBMS package establish an interface between the database itself and the users of the database. (These users may be applications programmers, managers and others with information needs, and various OS programs.)A DBMS can organize, process, and present selected data elements from the database. This capability enables decision makers to search, probe, and query database contents in order to extract answers to nonrecurring and unplanned questions (hat aren't available in regular reports. These questions might initially be vague and / or poorly defined, but peo ple can "browse” through the database until they have the needed information. Inshort, the DBMS will “m anage”the stored data items and assemble the needed items from the common database in response to the queries of those who aren’t10programmers. In a file-oriented system, users needing special information may communicate their needs to a programmer, who, when time permits, will write one or more programs to extract the data and prepare the information[4].The availability of a DBMS, however, offers users a much faster alternative communications path.If the DBMS provides a way to interactively and update the database, as well as interrogate it capability allows for managing personal data-Aces however, it does not automatically leave an audit trail of actions and docs not provide the kinds of control a necessary in a multiuser organization. These-controls arc only available when a set of application programs arc customized for each data entry and updating function.Software for personal computers which perform me of the DBMS functions have been very popular. Personal computers were intended for use by individuals for personal information storage and process- These machines have also been used extensively small enterprises, professionals like doctors, acrylics, engineers, lasers and so on .By the nature of intended usage, database systems on these machines except from several of the requirements of full doge database systems. Since data sharing is not tended, concurrent operations even less so. the fewer can be less complex. Security and integrity maintenance arc de-emphasized or absent. As data limes will be small, performance efficiency is also important. In fact, the only aspect of a database system that is important is data Independence. Data-dependence, as stated earlier, means that applicant programs and user queries need not recognizant physical organization of data on secondary storage. The importance of this aspect, particularly for the personal computer user, is that this greatly simplifies database usage. The user can store, access and manipulate data a( a high level (close to (he application) and be totally shielded from the10low level (close to the machine) details of data organization. We will not discuss details of specific PC DBMS software packages here. Let us summarize in the following the strengths and weaknesses of personal computer data-base software systems：The most obvious positive factor is the user friendliness of the software. A user with no prior computer background would be able to use the system to store personal and professional data, retrieve and perform relayed processing. The user should, of course, satiety himself about the quality of software and the freedom from errors (bugs) so that invest-merits in data arc protected.For the programmer implementing applications with them, the advantage lies in the support for applications development in terms of input screen generations, output report generation etc. offered by theses stems.The main negative point concerns absence of data protection features. Unless encrypted, data cane accessed by whoever has access to the machine Data can be destroyed through mistakes or malicious intent. The second weakness of many of the PC-based systems is that of performance. If data volumes grow up to a few thousands of records, performance could be a bottleneck.For organization where growth in data volumes is expected, availability of. the same or compatible software on large machines should be considered.This is one of the most common misconceptions about database management systems that are used in personal computers. Thoroughly comprehensive and sophisticated business systems can be developed in dBASE, Paradox and other DBMSs. However, they are created by experienced programmers using the DBMS's own programming language. Thai is not the same as users who create and manage personal10files that are not part of the mainstream company system.Transaction Management of DatabaseThe objective of long-duration transactions is to model long-duration, interactive Database access sessions in application environments. The fundamental assumption about short-duration of transactions that underlies the traditional model of transactions is inappropriate for long-duration transactions. The implementation of the traditional model of transactions may cause intolerably long waits when transactions aleph to acquire locks before accessing data, and may also cause a large amount of work to be lost when transactions are backed out in response to user-initiated aborts or system failure situations.The objective of a transaction model is to pro-vide a rigorous basis for automatically enforcing criterion for database consistency for a set of multiple concurrent read and write accesses to the database in the presence of potential system failure situations. The consistency criterion adopted for traditional transactions is the notion of scrializability. Scrializa-bility is enforced in conventional database systems through theuse of locking for automatic concurrency control, and logging for automatic recovery from system failure situations. A “transaction’’ that doesn't provide a basis for automatically enforcing data-base consistency is not really a transaction. To be sure, a long-duration transaction need not adopt seri-alizability as its consistency criterion. However, there must be some consistency criterion.Version System Management of DatabaseDespite a large number of proposals on version support in the context of computer aided design and software engineering, the absence of a consensus on version semantics10has been a key impediment to version support in database systems. Because of the differences between files and databases, it is intuitively clear that the model of versions in database systems cannot be as simple as that adopted in file systems to support software engineering.For data-bases, it may be necessary to manage not only versions of single objects (e.g. a software module, document, but also versions of a collection of objects (e.g. a compound document, a user manual, etc. and perhaps even versions of the schema of database (c.g. a table or a class, a collection of tables or classes).Broadly, there arc three directions of research and development in versioning. First is the notion of a parameterized versioning", that is, designing and implementing a versioning system whose behavior may be tailored by adjusting system parameters This may be the only viable approach, in view of the fact that there are various plausible choices for virtually every single aspect of versioning.The second is to revisit these plausible choices for every aspect of versioning, with the view to discardingsome of themes either impractical or flawed. The third is the investigation into the semantics and implementation of versioning collections of objects and of versioning the database.There is no consensus of the definition of the te rm “management information system”. Some writers prefer alternative terminology such as “information processing system”， "information and decision syste m, “organizational information syste m”, or simply “i nformat ion system” to refer to the computer-based information processing system which supports the operations, management, and decision-making functions of an organization. This text uses “MIS” because i t is descriptive and generally understood; it also frequently uses "information system”instead of ''MIS” t o refer to an organizational information system.10A definition of a management information system, as the term is generally understood, is an integrated, user-machine system for providing information 丨o support operations, management, and decision-making functions in an organization. The system utilizes computer hardware and software; manual procedures: models for analysis planning, control and decision making; and a database. The fact that it is an integrated system does not mean that it is a single, monolithic structure: rather, ii means that the parts fit into an overall design. The elements of the definition arc highlighted below: Computer-based user-machine system.Conceptually, a management information can exist without computer, but it is the power of the computer which makes MIS feasible. The question is not whether computers should be used in management information system, but the extent to whichinformation use should be computerized. The concept of a user-machine system implies that some (asks are best performed humans, while others are best done by machine. The user of an MIS is any person responsible for entering input da(a, instructing the system, or utilizing the information output of the system. For many problems, the user and the computer form a combined system with results obtained through a set of interactions between the computer and the user.User-machine interaction is facilitated by operation in which the user's input-output device (usually a visual display terminal) is connected lo the computer. The computer can be a personal computer serving only one user or a large computer that serves a number of users through terminals connected by communication lines. The user input-output device permits direct input of data and immediate output of results. For instance, a person using The computer interactively in financial planning poses 4t what10if* questions by entering input at the terminal keyboard; the results are displayed on the screen in a few second.The computer-based user-machine characteristics of an MIS affect the knowledge requirements of both system developer and system user, “computer-based” means that the designer of a management information system must have a knowledge of computers and of their use in processing. The “user-machine” concept means the system designer should also understand the capabilities of humans as system components (as information processors) and the behavior of humans as users of information.Information system applications should not require users Co be computer experts. However, users need to be able lo specify(heir information requirements; some understanding of computers, the nature of information, and its use in various management function aids users in this task.Management information system typically provide the basis for integration of organizational information processing. Individual applications within information systems arc developed for and by diverse sets of users. If there are no integrating processes and mechanisms, the individual applications may be inconsistent and incompatible. Data item may be specified differently and may not be compatible across applications that use the same data. There may be redundant development of separate applications when actually a single application could serve more than one need. A user wanting to perform analysis using data from two different applications may find the task very difficult and sometimes impossible.The first step in integration of information system applications is an overall information system plan. Even though application systems are implemented one at a10time, their design can be guided by the overall plan, which determines how they fit in with other functions. In essence, the information system is designed as a planed federation of small systems.Information system integration is also achieved through standards, guidelines, and procedures set by the MIS function. The enforcement of such standards and procedures permit diverse applications to share data, meet audit and control requirements, and be shares by multiple users. For instance, an application may be developed to run on a particular small computer. Standards for integration may dictate that theequipment selected be compatible with the centralized database. The trend in information system design is toward separate application processing form the data used to support it. The separate database is the mechanism by which data items are integrated across many applications and made consistently available to a variety of users. The need for a database in MIS is discussed below.The term “information” and “data” are frequently used interchangeably; However, information is generally defined as data that is meaningful or useful to The recipient. Data items are therefore the raw material for producing information.The underlying concept of a database is that data needs to be managed in order to be available for processing and have appropriate quality. This data management includes both software and organization. The software to create and manage a database is a database management system.When all access to any use of database is controlled through a database management system, all applications utilizing a particular data item access the same data item which is stored in only one place. A single updating of the data item updates it for10all uses. Integration through a database management system requires a central authority for the database. The data can be stored in one central computer or dispersed among several computers; the overriding requirement is that there be an organizational function to exercise control.It is usually insufficient for human recipients to receive only raw data or even summarized data. Data usually needs to be processed and presented in such a way that Che result is directed toward the decision to be made. To do this, processing of dataitems is based on a decision model.For example, an investment decision relative to new capital expenditures might be processed in terms of a capital expenditure decision model.Decision models can be used to support different stages in the decision-making process. “Intelligence’’ models can be used to search for problems and/or opportunities. Models can be used to identify and analyze possible solutions. Choice models such as optimization models maybe used to find the most desirable solution.In other words, multiple approaches are needed to meet a variety of decision situations. The following are examples and the type of model that might be included in an MIS to aid in analysis in support of decision-making; in a comprehensive information system, the decision maker has available a set of general models that can be applied to many analysis and decision situations plus a set of very specific models for unique decisions. Similar models are available tor planning and control. The set of models is the model base for the MIS.Models are generally most effective when the manager can use interactive dialog (o build a plan or to iterate through several decision choices under different conditions.10中文译文2:《数据库幸存者：成为一个摇滚名明星》众所周知，数据库是逻辑上相关的数据元的汇集.这些数据元可以按不同的结构组织起来，以满足单位和个人的多种处理和检索的需要。

SQL数据库中英文对照外文翻译文献

SQL数据库中英文对照外文翻译文献中英文对照外文翻译文献(文档含英文原文和中文翻译)Working with DatabasesThis chapter describes how to use SQL statements in embedded applications to control databases. There are three database statements that set up and open databases for access: SET DATABASE declares a database handle, associates the handle with an actual database file, and optionally assigns operational parameters for the database.SET NAMES optionally specifies the character set a client application uses for CHAR, VARCHAR, and text Blob data. The server uses this information to transliterate from a database?s default character set to the client?s character set on SELECT operations, and to transliterate from a client application?s character set to the database character set on INSERT and UPDATE operations.g CONNECT opens a database, allocates system resources for it, and optionally assigns operational parameters for the database.All databases must be closed before a program ends. A database can be closed by using DISCONNECT, or by appending the RELEASE option to the final COMMIT or ROLLBACK in a program.Declaring a databaseBefore a database can be opened and used in a program, it must first be declared with SET DATABASE to:CHAPTER 3 WORKING WITH DATABASES. Establish a database handle. Associate the database handle with a database file stored on a local or remote node.A database handle is aunique, abbreviated alias for an actual database name. Database handles are used in subsequent CONNECT, COMMIT RELEASE, and ROLLBACK RELEASE statements to specify which databases they should affect. Except in dynamic SQL (DSQL) applications, database handles can also be used inside transaction blocks to qualify, or differentiate, table names when two or more open databases contain identically named tables.Each database handle must be unique among all variables used in a program. Database handles cannot duplicate host-language reserved words, and cannot be InterBase reserved words.The following statement illustrates a simple database declaration:EXEC SQLSET DATABASE DB1 = ?employee.gdb?;This database declaration identifies the database file, employee.gdb, as a database the program uses, and assigns the database a handle, or alias, DB1.If a program runs in a directory different from the directory that contains the database file, then the file name specification in SET DATABASE must include a full path name, too. For example, the following SET DATABASE declaration specifies the full path to employee.gdb:EXEC SQLSET DATABASE DB1 = ?/interbase/examples/employee.gdb?;If a program and a database file it uses reside on different hosts, then the file name specification must also include a host name. The following declaration illustrates how a Unix host name is included as part of the database file specification on a TCP/IP network:EXEC SQLSET DATABASE DB1 = ?jupiter:/usr/interbase/examples/employee.gdb?;On a Windows network that uses the Netbeui protocol, specify the path as follows: EXEC SQLSET DATABASE DB1 = ?//venus/C:/Interbase/examples/employee.gdb?; DECLARING A DATABASEEMBEDDED SQL GUIDE 37Declaring multiple databasesAn SQL program, but not a DSQL program, can access multiple databases at the same time. In multi-database programs, database handles are required. A handle is used to:1. Reference individual databases in a multi-database transaction.2. Qualify table names.3. Specify databases to open in CONNECT statements.Indicate databases to close with DISCONNECT, COMMIT RELEASE, and ROLLBACK RELEASE.DSQL programs can access only a single database at a time, so database handle use is restricted to connecting to and disconnecting from a database.In multi-database programs, each database must be declared in a separate SET DATABASE statement. For example, the following code contains two SET DATABASE statements: . . .EXEC SQLSET DATABASE DB2 = ?employee2.gdb?;EXEC SQLSET DATABASE DB1 = ?employee.gdb?;. . .4Using handles for table namesWhen the same table name occurs in more than one simultaneously accessed database, a database handle must be used to differentiate one table name from another. The database handle is used as a prefix to table names, and takes the form handle.table.For example, in the following code, the database handles, TEST and EMP, are used to distinguish between two tables, each named EMPLOYEE:. . .EXEC SQLDECLARE IDMATCH CURSOR FORSELECT TESTNO INTO :matchid FROM TEST.EMPLOYEEWHERE TESTNO > 100;EXEC SQLDECLARE EIDMATCH CURSOR FORSELECT EMPNO INTO :empid FROM EMP.EMPLOYEEWHERE EMPNO = :matchid;. . .CHAPTER 3 WORKING WITH DATABASES38 INTERBASE 6IMPORTANTThis use of database handles applies only to embedded SQL applications. DSQL applications cannot access multiple databases simultaneously.4Using handles with operationsIn multi-database programs, database handles must be specified in CONNECT statements to identify which databases among several to open and prepare for use in subsequent transactions.Database handles can also be used with DISCONNECT, COMMIT RELEASE, and ROLLBACKRELEASE to specify a subset of open databases to close.To open and prepare a database with CONNECT, see “Opening a database” on page 41.To close a database with DISCONNECT, COMMIT RELEASE, or ROLLBACK RELEASE, see“Closing a database” on page 49. To learn more about using database handles in transactions, see “Accessing an open database” on page 48.Preprocessing and run time databasesNormally, each SET DATABASE statement specifies a single database file to associate with a handle. When a program is preprocessed, gpre uses the specified file to validate the prog ram?s table and column references. Later, when a user runs the program, the same database file is accessed. Different databases can be specified for preprocessing and run time when necessary.4Using the COMPILETIME clause A program can be designed to run against any one of several identically structured databases. In other cases, the actual database that a program will use at runtime is not available when a program is preprocessed and compiled. In such cases, SET DATABASE can include a COMPILETIME clause to specify a database for gpre to test against during preprocessing. For example, the following SET DATABASE statement declares that employee.gdb is to be used by gpre during preprocessing: EXEC SQLSET DATABASE EMP = COMPILETIME ?employee.gdb?;IMPORTANTThe file specification that follows the COMPILETIME keyword must always be a hard-coded, quoted string.DECLARING A DATABASEEMBEDDED SQL GUIDE 39When SET DATABASE uses the COMPILETIME clause, but no RUNTIME clause, and does not specify a different database file specification in a subsequent CONNECT statement, the same database file is used both for preprocessing and run time. To specify different preprocessing and runtime databases with SET DATABASE, use both the COMPILETIME andRUNTIME clauses.4Using the RUNTIME clauseWhen a database file is specified for use during preprocessing, SET DATABASE can specify a different database to use at run time by including the RUNTIME keyword and a runtime file specification:EXEC SQLSET DATABASE EMP = COMPILETIME ?employee.gdb?RUNTIME ?employee2.gdb?;The file specification that follows the RUNTIME keyword can be either ahard-coded, quoted string, or a host-language variable. For example, the following C code fragment prompts the user for a database name, and stores the name in a variable that is used later in SET DATABASE:. . .char db_name[125];. . .printf("Enter the desired database name, including node and path):\n");gets(db_name);EXEC SQLSET DATABASE EMP = COMPILETIME ?employee.gdb?RUNTIME : db_name; . . .Note host-language variables in SET DATABASE must be preceded, as always, by a colon.Controlling SET DATABASE scopeBy default, SET DATABASE creates a handle that is global to all modules in an application.A global handle is one that may be referenced in all host-language modules comprising the program. SET DATABASE provides two optional keywords to change the scope of a declaration:g STATIC limits declaration scope to the module containing the SET DATABASE statement. No other program modules can see or use a database handle declared STATIC.CHAPTER 3 WORKING WITH DATABASES40 INTERBASE 6EXTERN notifies gpre that a SET DATABASE statement in a module duplicates a globally-declared database in another module. If the EXTERN keyword is used, then another module must contain the actual SET DATABASE statement, or an error occurs during compilation.The STATIC keyword is used in a multi-module program to restrict database handle access to the single module where it is declared. The following example illustrates the use of the STATIC keyword:EXEC SQLSET DATABASE EMP = STATIC ?employee.gdb?;The EXTERN keyword is used in a multi-module program to signal that SET DATABASE in one module is not an actual declaration, but refers to a declaration made in a different module. Gpre uses this information during preprocessing. Thefollowing example illustrates the use of the EXTERN keyword: EXEC SQLSET DATABASE EMP = EXTERN ?employee.gdb?;If an application contains an EXTERN reference, then when it is used at run time, the actual SET DATABASE declaration must be processed first, and the database connected before other modules can access it.A single SET DATABASE statement can contain either the STATIC or EXTERN keyword, but not both. A scope declaration in SET DATABASE applies to both COMPILETIME and RUNTIME databases.Specifying a connection character setWhen a client application connects to a database, it may have its own character set requirements. The server providing database access to the client does not know about these requirements unless the client specifies them. The client application specifies its character set requirement using the SET NAMES statement before it connects to the database.SET NAMES specifies the character set the server should use when translating data from the database to the client application. Similarly, when the client sends data to the database, the server translates the data from the client?s character set to the database?s default character set (or the character set for an individual column if it differs from the database?s default character set). For example, the followingstatements specify that the client is using the DOS437 character set, then connect to the database:EXEC SQLOPENING A DATABASEEMBEDDED SQL GUIDE 41SET NAMES DOS437;EXEC SQLCONNECT ?europe.gdb? USER ?JAMES? PASSWORD ?U4EEAH?;For more information about character sets, see the Data Definition Guide. For the complete syntax of SET NAMES and CONNECT, see the Language Reference. Opening a database After a database is declared, it must be attached with a CONNECT statement before it can be used. CONNECT:1. Allocates system resources for the database.2. Determines if the database file is local, residing on the same host where the application itself is running, or remote, residing on a different host.3. Opens the database and examines it to make sure it is valid.InterBase provides transparent access to all databases, whether local or remote. If the database structure is invalid, the on-disk structure (ODS) number does not correspond to the one required by InterBase, or if the database is corrupt, InterBase reports an error, and permits no further access. Optionally, CONNECT can be used to specify:4. A user name and password combination that is checked against the server?s security database before allowing the connect to succeed. User names can be up to 31 characters.Passwords are restricted to 8 characters.5. An SQL role name that the user adopts on connection to the database, provided that the user has previously been granted membership in the role. Regardless of role memberships granted, the user belongs to no role unless specified with this ROLE clause.The client can specify at most one role per connection, and cannot switch roles except by reconnecting.6. The size of the database buffer cache to allocate to the application when the default cache size is inappropriate.Using simple CONNECT statementsIn its simplest form, CONNECT requires one or more database parameters, each specifying the name of a database to open. The name of the database can be a: Database handle declared in a previous SET DATABASE statement.CHAPTER 3 WORKING WITH DATABASES42 INTERBASE 61. Host-language variable.2. Hard-coded file name.4Using a database handleIf a program uses SET DATABASE to provide database handles, those handles should be used in subsequent CONNECT statements instead of hard-coded names. For example, . . .EXEC SQLSET DATABASE DB1 = ?employee.gdb?;EXEC SQLSET DATABASE DB2 = ?employee2.gdb?;EXEC SQLCONNECT DB1;EXEC SQLCONNECT DB2;. . .There are several advantages to using a database handle with CONNECT:1. Long file specifications can be replaced by shorter, mnemonic handles.2. Handles can be used to qualify table names in multi-database transactions. DSQL applications do not support multi-database transactions.3. Handles can be reassigned to other databases as needed.4. The number of database cache buffers can be specified as an additional CONNECT parameter.For more information about setting the number of databas e cache buffers, see “Setting database cache buffers” on page 47. 4Using strings or host-language variables Instead of using a database handle, CONNECT can use a database name supplied at run time. The database name can be supplied as either a host-language variable or a hard-coded, quoted string.The following C code demonstrates how a program accessing only a single database might implement CONNECT using a file name solicited from a user at run time:. . .char fname[125];. . .printf(?Enter the desired database name, including nodeand path):\n?);OPENING A DATABASEEMBEDDED SQL GUIDE 43gets(fname);. . .EXEC SQLCONNECT :fname;. . .TipThis technique is especially useful for programs that are designed to work with many identically structured databases, one at a time, such as CAD/CAM or architectural databases.MULTIPLE DATABASE IMPLEMENTATIONTo use a database specified by the user as a host-language variable in a CONNECT statement in multi-database programs, follow these steps:1. Declare a database handle using the following SET DATABASE syntax:。

数据库安全中英文对照外文翻译文献

中英文对照外文翻译文献(文档含英文原文和中文翻译)Database Security in a Web Environment IntroductionDatabases have been common in government departments and commercial enterprises for many years. Today, databases in any organization are increasingly opened up to a multiplicity of suppliers, customers, partners and employees - an idea that would have been unheard of a few years ago. Numerous applications and their associated data are now accessed by a variety of users requiring different levels of access via manifold devices and channels – often simultaneously. For example:• Online banks allow customers to perform a variety of banking operations - via the Internet and over the telephone – whilst maintaining the privacy of account data.• E-Commerce merchants and their Service Providers must store customer, order and payment data on their merchant server - and keep it secure.• HR departments allow employees to update their personal information –whilst protecting certain management information from unauthorized access.• The medical profession must protect the confidentiality of patient data –whilst allowing essential access for treatment.• Online brokerages need to be able to provide large numbers of simultaneous users with up-to-date and accurate financial information.This complex landscape leads to many new demands upon system security. The global growth of complex web-based infrastructures is driving a need for security solutions that provide mechanisms to segregate environments; perform integrity checking and maintenance; enable strong authentication andnon-repudiation; and provide for confidentiality. In turn, this necessitates comprehensive business and technical risk assessment to identify the threats,vulnerabilities and impacts, and from this define a security policy. This leads to security definitions throughout the infrastructure - operating system, database management system, middleware and network.Financial, personal and medical information systems and some areas of government have strict requirements for security and privacy. Inappropriate disclosure of sensitive information to the wrong parties can have severe social, legal and regulatory consequences. Failure to address the basics can result in substantial direct and consequential financial losses - witness the fraud losses through the compromise of several million credit card numbers in merchants’ databases [Occf], plus associated damage to brand-image and loss of consumer confidence.This article discusses some of the main issues in database and web server security, and also considers important architecture and design issues.A Simple ModelAt the simplest level, a web server system consists of front-end software and back-end databases with interface software linking the two. Normally, the front-end software will consist of server software and the network server operating system, and the back-end database will be a relational orobject-oriented database fulfilling a variety of functions, including recording transactions, maintaining accounts and inventory. The interface software typically consists of Common Gateway Interface (CGI) scripts used to receive information from forms on web sites to perform online searches and to update the database.Depending on the infrastructure, middleware may be present; in addition, security management subsystems (with session and user databases) that address the web server’s and related applications’ requirements for authentication, accesscontrol and authorization may be present. Communications between this subsystem and either the web server, middleware or database are via application program interfaces (APIs)..This simple model is depicted in Figure 1.Security can be provided by the following components:• Web server.• Middleware.• Operating system.. Figure 1: A Simple Model.• Database and Database Management System.• Security management subsystem.The security of such a system addressesAspects of authenticity, integrity and confidentiality and is dependent on the security of the individual components and their interactions. Some of the most common vulnerabilities arise from poor configuration, inadequate change control procedures and poor administration. However, even if these areas are properlyaddressed, vulnerabilities still arise. The appropriate combination of people, technology and processes holds the key to providing the required physical and logical security. Attention should additionally be paid to the security aspects of planning, architecture, design and implementation.In the following sections, we consider some of the main security issues associated with databases, database management systems, operating systems and web servers, as well as important architecture and design issues. Our treatment seeks only to outline the main issues and the interested reader should refer to the references for a more detailed description.Database SecurityDatabase management systems normally run on top of an operating system and provide the security associated with a database. Typical operating system security features include memory and file protection, resource access control and user authentication. Memory protection prevents the memory of one program interfering with that of another and limits access and use of the objects employing techniques such as memory segmentation. The operating system also protects access to other objects (such as instructions, input and output devices, files and passwords) by checking access with reference to access control lists. Security mechanisms in common operating systems vary tremendously and, for those that are lacking, there exists special-purpose security software that can be integrated with the existing environment. However, this can be an expensive, time-consuming task and integration difficulties may also adversely impact application behaviors.Most database management systems consist of a number of modules - including database querying and database and file management - along with authorization, concurrent access and database description tables. Thesemanagement systems also use a variety of languages: a data definition language supports the logical definition of the database; developers use a data manipulation language; and a query language is used by non-specialist end-users.Database management systems have many of the same security requirements as operating systems, but there are significant differences since the former are particularly susceptible to the threat of improper disclosure, modification of information and also denial of service. Some of the most important security requirements for database management systems are: • Multi-Level Access Control.• Confidentiality.• Reliability.• Integrity.• Recovery.These requirements, along with security models, are considered in the following sections.Multi-Level Access ControlIn a multi-application and multi-user environment, administrators, auditors, developers, managers and users – collectively called subjects - need access to database objects, such as tables, fields or records. Access control restricts the operations available to a subject with respect to particular objects and is enforced by the database management system. Mandatory access controls require that each controlled object in the database must be labeled with a security level, whereas discretionary access controls may be applied at the choice of a subject.Access control in database management systems is more complicated than in operating systems since, in the latter, all objects are unrelated whereas in a database the converse is true. Databases are also required to make accessdecisions based on a finer degree of subject and object granularity. In multi-level systems, access control can be enforced by the use of views - filtered subsets of the database - containing the precise information that a subject is authorized to see.A general principle of access control is that a subject with high level security should not be able to write to a lower level object, and this poses a problem for database management systems that must read all database objects and write new objects. One solution to this problem is to use a trusted database management system.ConfidentialitySome databases will inevitably contain what is considered confidential data. For example, it could be inherently sensitive or its source may be sensitive, or it may belong to a sensitive table, thus making it difficult to determine what is actually confidential. Disclosure is also difficult to define, as it can be direct, indirect, involve the disclosure of bounds or even mere existence.An inference problem exists in database management systems whereby users can infer sensitive information from relatively insensitive queries. A trivial example is a request for information about the average salary of an employee and the number of employees turns out to be just one, thus revealing the employee’s salary. However, much more sophisticated statistical inference attacks can also be mounted. This highlights the fact that, although the data itself may be properly controlled, confidential information may still leak out.Controls can take several forms: not divulging sensitive information to unauthorized parties (which depends on the respective subject and object security levels), logging what each user knows or masking response data. The first control can be implemented fairly easily, the second quickly becomesunmanageable for a large number of users and the third leads to imprecise responses, and also exemplifies the trade-off between precision and security. Polyinstantiation refers to multiple instances of a data object existing in the database and it can provide a partial solution to the inference problem whereby different data values are supplied, depending on the security level, in response to the same query. However, this makes consistency management more difficult.Another issue that arises is when the security level of an aggregate amount is different to that of its elements (a problem commonly referred to as aggregation). This can be addressed by defining appropriate access control using views.Reliability, Integrity and RecoveryArguably, the most important requirements for databases are to ensure that the database presents consistent information to queries and can recover from any failures. An important aspect of consistency is that transactions execute atomically; that is, they either execute completely or not at all.Concurrency control addresses the problem of allowing simultaneous programs access to a shared database, while avoiding incorrect behavior or interference. It is normally addressed by a scheduler that uses locking techniques to ensure that the transactions are serial sable and independent. A common technique used in commercial products is two-phase locking (or variations thereof) in which the database management system controls when transactions obtain and release their locks according to whether or not transaction processing has been completed. In a first phase, the database management system collects the necessary data for the update: in a second phase, it updates the database. This means that the database can recover from incomplete transactions by repeatingeither of the appropriate phases. This technique can also be used in a distributed database system using a distributed scheduler arrangement.System failures can arise from the operating system and may result in corrupted storage. The main copy of the database is used for recovery from failures and communicates with a cached version that is used as the working version. In association with the logs, this allows the database to recover to a very specific point in the event of a system failure, either by removing the effects of incomplete transactions or applying the effects of completed transactions. Instead of having to recover the entire database after a failure, recovery can be made more efficient by the use of check pointing. It is used during normal operations to write additional updated information - such as logs, before-images of incomplete transactions, after-images of completed transactions - to the main database which reduces the amount of work needed for recovery. Recovery from failures in distributed systems is more complicated, since a single logical action is executed at different physical sites and the prospect of partial failure arises.Logical integrity, at field level and for the entire database, is addressed by the use of monitors to check important items such as input ranges, states and transitions. Error-correcting and error-detecting codes are also used.Security ModelsVarious security models exist that address different aspects of security in operating systems and database management systems. For example, theBell-LaPadula model defines security in terms of mandatory access control and addresses confidentiality only. The Bell LaPadula models, and other models including the Biba model for integrity, are described more fully in [Cast95] and [Pfle89]. These models are implementation-independent and provide a powerfulinsight into the properties of secure systems, lead to design policies and principles, and some form the basis for security evaluation criteria.Web Server SecurityWeb servers are now one of the most common interfaces between users and back-end databases, and as such, their security becomes increasingly important. Exploitation of vulnerabilities in the web server can lead to unforeseen attacks on middleware and backend databases, bypassing any controls that may be in place. In this section, we focus on common web server vulnerabilities and how the authentication requirements of web servers and databases are met.In general, a web server platform should not be shared with other applications and should be the only machine allowed to access the database. Using a firewall can provide additional security - either between the web server and users or between the web server and back-end database - and often the web server is placed on a de-militarized zone (DMZ) of a firewall. While firewalls can be used to block certain incoming connections, they must allow HTTP (and HTTPS) connections through to the web server, and so attacks can still be launched via the ports associated with these connections.VulnerabilitiesVulnerabilities appear on a weekly basis and, here, we prefer to focus on some general issues rather than specific attacks. Common web server vulnerabilities include:• No policy exists.• The default configuration is on.• Reusable passwords appear in clear.• Unnecessary ports available for network services are not disabled.• New security holes are not tracked. Even if they are, well-known vulnerabilities are not always fixed as the source code patches are not applied by system administrator and old programs are not re-compiled or removed.• Security tools are not used to scan the network for weaknesses and changes or to detect intrusions.• Faulty and buggy software - for example, buffer overflow and stack smashingAttacks• Automatic directory listings - this is of particular concern for the interface software directories.• Server root files are generally visible or accessible.• Lack of logs and bac kups.• File access is often not explicitly configured by the system administrator according to the security policy. This applies to configuration, client, administration and log files, administration programs, and CGI program sources and executables. CGI scripts allow dynamic web pages and make program development (in, for example, Perl) easy and rapid. However, their successful exploitation may allow execution of malicious programs, launching ofdenial-of-service attacks and, ultimately, privilege escalation on a server.Web Server and Database AuthenticationWhile user, browser and web server authentication are relatively well understood [Garf97], [Ghos98] and [Tree98], the introduction of additional components, such as databases and middleware, raise a number of authentication issues. There are a variety of options for authentication in a simple model (Figure 1). Firstly, both the web server and database management system can individually authenticate a user. This option requires the user to authenticatetwice which may be unacceptable in certain applications, although a singlesign-on device (which aims to manage authentication in a user-transparent way) may help. Secondly, a common approach is for the database to automatically grant user access based on web server authentication. However, this option should only be used for accessing publicly available information. Finally, the database may grant user access employing the web server authentication credentials as a basis for its own user authentication, using security management subsystems (Figure 1). We consider this last option in more detail.Web-based communications use the stateless HTTP protocol with the implication that state, and hence authentication, is not preserved when browsing successive web pages. Cookies, or files placed on user’s machine by a web server, were developed as a means of addressing this issue and are often used to provide authentication. However, after initial authentication, there is typically no re authentication per page in the same realm, only the use of unencrypted cookies (sometimes in association with IP addresses). This approach provides limited security as both cookies and IP addresses can be tampered with or spoofed.A stronger authentication method, commonly used by commercial implementations, uses digitally signed cookies. This allows additional systems, such as databases, to use digitally signed cookie data, including a session ID, as a basis for authentication. When a user has been authenticated by a web server (using a password, for example), a session ID is assigned and is stored in a security management subsystem database. When a user subsequently requests information from a database, the database receives a copy of the session ID, the security management subsystem checks this session ID against its local copy and, if authentication is successful, user access is granted to the database.The session ID is typically transmitted in the clear between the web server and database, but may be protected by SSL or even by physical security measures. The communications between the browser and web servers, and the web servers and security management subsystem (and its databases), are normally protected by SSL and use a web server security API that is used to digitally sign and verify browser cookies. The communications between the back-end databases and security management subsystem (and its databases) are also normally protected by SSL and use a database security API that verifies session Ids originating from the database and provides additional user authorization credentials. The web server security API is generally proprietary while, for the database security API, many vendors have adopted standards such as the Generic Security Services API (GSS-API) or CORBA [RFC2078] and [Corba].Architecture and DesignSecurity requirements for designing, building and implementing databases are important so that the systems, as part of the overall infrastructure, meet their requirements in actual operation. The various security models provide an important insight into the design requirements for databases and their management systems.Secure Database Management System ArchitecturesIn multi-level database management systems, a variety of architectures are possible: trusted subject, integrity locked, kernels and replicated. Trusted subject is used by most of the leading database management system vendors and can be integrated in existing products. Basically, the trusted subject architecture allows users to access a database via an un trusted front-end, a trusted database management system and trusted operating system. The operating systemprovides physical access to the database and the database management system provides multilevel object protection.The other architectures - integrity locked, kernels and replicated - all vary in detail, but they use a trusted front-end and an un trusted database management system. For details of these architectures and research prototypes, the reader is referred to [Cast95]. Different architectures are suited to different environments: for example, the trusted subject architecture is less integrated with the underlying operating system and is best suited when a trusted path can be assured between applications and the database management system.Secure Database Management System DesignAs discussed above, there are several fundamental differences between operating system and database management system design, including object granularity, multiple data types, data correlations and multi-level transactions. Other differences include the fact that database management systems include both physical and logical objects and that the database lifecycle is normally longer.These differences must be reflected in the design requirements which include:• Access, flow and infer ence controls.• Access granularity and modes.• Dynamic authorization.• Multi-level protection.• Polyinstantiation.• Auditing.• Performance.These requirements should be considered alongside basic information integrity principles, such as:• Well-formed transactions - to ensure that transactions are correct and consistent.• Continuity of operation - to ensure that data can be properly recovered, depending on the extent of a disaster.• Authorization and role management – to ensure that distinct roles are defined and users are authorized.• Authenticated users - to ensure that users are authenticated.• Least privilege - to ensure that users have the minimal privilege necessary to perform their tasks.• Separation of duties - to ensure that no single individual has access to critical data.• Delegation of authority - to ensure that the database management system policies are flexible enough to meet the organization’s requirements.Of course, some of these requirements and principles are not met by the database management system, but by the operating system and also by organizational and procedural measures.Database Design MethodologyVarious approaches to design exist, but most contain the same main stages. The principle aim of a design methodology is to provide a robust, verifiable design process and also to separate policies from how policies are actually implemented. An important requirement during any design process is that different design aspects can be merged and this equally applies to security.A preliminary analysis should be conducted that addresses the system risks, environment, existing products and performance. Requirements should then beanalyzed with respect to the results of a risk assessment. Security policies should be developed that include specification of granularity, privileges and authority.These policies and requirements form the input to the conceptual design that concentrates on subjects, objects and access modes without considering implementation details. Its purpose is to express information and process flows in a complete and consistent way.The logical design takes into account the operating system and database management system that will be used and which of the security requirements can be provided by which mechanisms. The physical design considers the actual physical realization of the logical design and, indeed, may result in a revision of the conceptual and logical phases due to physical constraints.Security AssuranceOnce a product has been developed, its security assurance can be assessed by a number of methods including formal verification, validation, penetration testing and certification. For example, if a database is to be certified as TCSEC Class B1, then it must implement the Bell-LaPadula mandatory access control model in which each controlled object in the database must be labeled with a security level.Most of these methods can be costly and lengthy to perform and are typically specific to particular hardware and software configurations. However, the international Common Criteria certification scheme provides the added benefit of a mutual recognition arrangement, thus avoiding the prospect of multiple certifications in different countries.ConclusionThis article has considered some of the security principles that are associated with databases and how these apply in a web based environment. Ithas also focused on important architecture and design principles. These principles have focused mainly on the prevention, assurance and recovery aspects, but other aspects, such as detection, are equally important in formulating a total information protection strategy. For example, host-based intrusion detection systems as well as a robust and tested set of business recovery procedures should be considered.Any fit-for-purpose, secure e-business infrastructure should address all the above aspects: prevention, assurance, detection and recovery. Certain industries are now starting to specify their own set of global, secure e-business requirements. International card payment associations have recently started to require minimum information security standards from electronic commerce merchants handling credit card data, to help manage fraud losses and associated impacts such as brand-image damage and loss of consumer confidence.网络环境下的数据库安全简介数据库在政府部门和商业机构得到普遍应用已经很多年了。

关于sql的参考文献

关于sql的参考文献SQL（Structured Query Language）是一种用于管理和操作关系型数据库的标准化语言。

在学习和使用SQL时，参考文献是非常重要的资源，它们可以提供详细的指导和深入的理解。

以下是一些关于SQL的参考文献，它们涵盖了不同的主题和难度级别，可以帮助你深入学习和掌握SQL。

1. "SQL Cookbook" by Anthony Molinaro 这本书提供了大量的SQL问题和解决方案，涵盖了各种常见的数据库操作和查询场景。

它适合那些希望通过实际问题来学习SQL的人。

2. "SQL in 10 Minutes a Day" by Ben Forta 这本书适合初学者，它以简洁明了的方式介绍了SQL的基本概念和语法。

通过逐步构建知识，读者可以快速入门SQL。

3. "Learning SQL" by Alan Beaulieu 这本书适合那些希望深入学习SQL的人。

它涵盖了SQL的基础知识和高级概念，包括数据库设计、查询优化和数据分析等方面。

4. "SQL Performance Explained" by Markus Winand 这本书专注于SQL性能优化，它探讨了如何编写高效的SQL查询和设计高性能的数据库架构。

对于那些希望提高SQL查询性能的人来说，这是一本非常有价值的参考书。

5. "SQL Antipatterns: Avoiding the Pitfalls of Database Programming" by Bill Karwin 这本书介绍了一些常见的SQL反模式和错误，以及如何避免它们。

通过学习这些反模式，读者可以提高自己编写高质量SQL代码的能力。

6. "The Art of SQL" by Stephane Faroult and Peter Robson 这本书深入探讨了SQL的内部工作原理和优化技巧。

sqlserver数据库介绍的书籍 -回复

sqlserver数据库介绍的书籍-回复SQL Server数据库介绍的书籍SQL Server是一种关系型数据库管理系统，由微软公司开发和提供支持。

它具有广泛的应用领域，从小型企业到大型企业都可以使用它来存储和处理数据。

对于想要学习SQL Server数据库的人来说，书籍是一个非常有用的资源。

在这篇文章中，我将介绍一些SQL Server数据库介绍的书籍，并为您逐步解释它们的内容。

1. "SQL Server 2017 Bible" by Adam Jorgensen, Stacia Varga, Denny Cherry, and Joseph D'Antoni.这本书是关于SQL Server 2017版本的指南。

它详细介绍了SQL Server的各个方面，包括数据库管理、查询编写、性能调优、安全性以及高可用性和灾难恢复。

这本书非常适合初学者和有经验的SQL Server用户，可以帮助读者了解SQL Server的基本概念和功能。

2. "Microsoft SQL Server 2019: A Beginner's Guide" by Dusan Petkovic.这本书适用于初学者，它提供了一个全面的介绍，帮助读者了解SQL Server 2019的基本概念和功能。

书中包含大量的示例和练习，以加深对SQL Server的理解。

读者可以学习如何创建数据库、执行查询以及管理和维护SQL Server的技巧。

3. "Pro SQL Server Internals" by Dmitri Korotkevitch.这本书适用于那些希望深入了解SQL Server内部工作原理的读者。

它涵盖了SQL Server的存储引擎、查询优化、并发控制和事务管理等方面。

作者提供了详细的解释和示例，以帮助读者理解SQL Server的内部工作原理，并优化数据库性能。

SQL Server数据库管理外文翻译文献

SQL Server数据库管理外文翻译文献本文翻译了一篇关于SQL Server数据库管理的外文文献。

摘要该文献介绍了SQL Server数据库管理的基本原则和策略。

作者指出，重要的决策应该基于独立思考，避免过多依赖外部帮助。

对于非可确认的内容，不应进行引用。

文献还强调了以简单策略为主、避免法律复杂性的重要性。

内容概述本文详细介绍了SQL Server数据库管理的基本原则和策略。

其中包括：1. 独立决策：在数据库管理中，决策应该基于独立思考。

不过多依赖用户的帮助或指示，而是依靠数据库管理员的专业知识和经验进行决策。

独立决策：在数据库管理中，决策应该基于独立思考。

不过多依赖用户的帮助或指示，而是依靠数据库管理员的专业知识和经验进行决策。

2. 简单策略：为了避免法律复杂性和错误的决策，应采用简单策略。

这意味着避免引用无法确认的内容，只使用可靠和可验证的信息。

简单策略：为了避免法律复杂性和错误的决策，应采用简单策略。

这意味着避免引用无法确认的内容，只使用可靠和可验证的信息。

3. 数据库管理准则：文献提出了一些SQL Server数据库管理的准则，包括：规划和设计数据库结构、有效的数据备份和恢复策略、用户权限管理、性能优化等。

数据库管理准则：文献提出了一些SQL Server数据库管理的准则，包括：规划和设计数据库结构、有效的数据备份和恢复策略、用户权限管理、性能优化等。

结论文献通过介绍SQL Server数据库管理的基本原则和策略，强调了独立决策和简单策略的重要性。

数据库管理员应该依靠自己的知识和经验，避免过度依赖外部帮助，并采取简单策略来管理数据库。

此外，遵循数据库管理准则也是确保数据库安全和性能的重要手段。

以上是对于《SQL Server数据库管理外文翻译文献》的详细内容概述和总结。

如果需要更多详细信息，请阅读原文献。

外文翻译原文—数据库

DatabaseA database consists of an organized collection of data for one or more uses, typically in digital form. One way of classifying databases involves the type of their contents, for example: bibliographic, document-text, statistical. Digital databases are managed using database management systems, which store database contents, allowing data creation and maintenance, and search and other access. ArchitectureDatabase architecture consists of three levels, external, conceptual and internal. Clearly separating the three levels was a major feature of the relational database model that dominates 21st century databases.The external level defines how users understand the organization of the data. A single database can have any number of views at the external level. The internal level defines how the data is physically stored and processed by the computing system. Internal architecture is concerned with cost, performance, scalability and other operational matters. The conceptual is a level of indirection between internal and external. It provides a common view of the database that is uncomplicated by details of how the data is stored or managed, and that can unify the various external views into a coherent whole.Database management systemsA database management system (DBMS) consists of software that operates databases, providing storage, access, security, backup and other facilities. Database management systems can be categorized according to the database model that they support, such as relational or XML, the type(s) of computer they support, such as a server cluster or a mobile phone, the query language(s) that access the database, such as SQL or XQuery, performance trade-offs, such as maximum scale or maximum speed or others. Some DBMS cover more than one entry in these categories, e.g., supporting multiple query languages.Components of DBMSMost DBMS as of 2009[update] implement a relational model. Other DBMS systems, such as Object DBMS, offer specific features for more specialized requirements. Their components are similar, but not identical.RDBMS components•Sublanguages—Relational DBMS (RDBMS) include Data Definition Language (DDL) for defining the structure of the database, Data Control Language (DCL) for defining security/access controls, and Data Manipulation Language (DML) for querying and updating data.•Interface drivers—These drivers are code libraries that provide methods to prepare statements, execute statements, fetch results, etc. Examples include ODBC, JDBC, MySQL/PHP, FireBird/Python.•SQL engine—This component interprets and executes the DDL, DCL, and DML statements.It includes three major components (compiler, optimizer, and executor).•Transaction engine—Ensures that multiple SQL statements either succeed or fail as a group, according to application dictates.•Relational engine—Relational objects such as Table, Index, and Referential integrity constraints are implemented in this component.•Storage engine—This component stores and retrieves data from secondary storage, as well asmanaging transaction commit and rollback, backup and recovery, etc.ODBMS componentsObject DBMS (ODBMS) has transaction and storage components that are analogous to those in an RDBMS. Some ODBMS handle DDL, DCL and update tasks differently. Instead of using sublanguages, they provide APIs for these purposes. They typically include a sublanguage and accompanying engine for processing queries with interpretive statements analogous to but not the same as SQL. Example object query languages are OQL, LINQ, JDOQL, JPAQL and others. The query engine returns collections of objects instead of relational rows.TypesOperational databaseThese databases store detailed data about the operations of an organization. They are typically organized by subject matter, process relatively high volumes of updates using transactions. Essentially every major organization on earth uses such databases. Examples include customer databases that record contact, credit, and demographic information about a business' customers, personnel databases that hold information such as salary, benefits, skills data about employees, manufacturing databases that record details about product components, parts inventory, and financial databases that keep track of the organization's money, accounting and financial dealings.Data warehouseData warehouses archive historical data from operational databases and often from external sources such as market research firms. Often operational data undergoes transformation on its way into the warehouse, getting summarized, anonymized, reclassified, etc. The warehouse becomes the central source of data for use by managers and other end-users who may not have access to operational data. For example, sales data might be aggregated to weekly totals and converted from internal product codes to use UPC codes so that it can be compared with ACNielsen data. Analytical databaseAnalysts may do their work directly against a data warehouse, or create a separate analytic database for Online Analytical Processing. For example, a company might extract sales records for analyzing the effectiveness of advertising and other sales promotions at an aggregate level. Distributed databaseThese are databases of local work-groups and departments at regional offices, branch offices, manufacturing plants and other work sites. These databases can include segments of both common operational and com mon user databases, as well as data generated and used only at a user’s own site. End-user databaseThese databases consist of data developed by individual end-users. Examples of these are collections of documents in spreadsheets, word processing and downloaded files, or even managing their personal baseball card collection.External databaseThese databases contain data collect for use across multiple organizations, either freely or via subscription. The Internet Movie Database is one example.Hypermedia databasesThe Worldwide web can be thought of as a database, albeit one spread across millions of independent computing systems. Web browsers "process" this data one page at a time, while web crawlers and other software provide the equivalent of database indexes to support search and otheractivities.ModelsPost-relational database modelsProducts offering a more general data model than the relational model are sometimes classified as post-relational. Alternate terms include "hybrid database", "Object-enhanced RDBMS" and others. The data model in such products incorporates relations but is not constrained by E.F. Codd's Information Principle, which requires that all information in the database must be cast explicitly in terms of values in relations and in no other way.Some of these extensions to the relational model integrate concepts from technologies that pre-date the relational model. For example, they allow representation of a directed graph with trees on the nodes.Some post-relational products extend relational systems with non-relational features. Others arrived in much the same place by adding relational features to pre-relational systems. Paradoxically, this allows products that are historically pre-relational, such as PICK and MUMPS, to make a plausible claim to be post-relational.Object database modelsIn recent years[update], the object-oriented paradigm has been applied in areas such as engineering and spatial databases, telecommunications and in various scientific domains. The conglomeration of object oriented programming and database technology led to this new kind of database. These databases attempt to bring the database world and the application-programming world closer together, in particular by ensuring that the database uses the same type system as the application program. This aims to avoid the overhead (sometimes referred to as the impedance mismatch) of converting information between its representation in the database (for example as rows in tables) and its representation in the application program (typically as objects). At the same time, object databases attempt to introduce key ideas of object programming, such as encapsulation and polymorphism, into the world of databases.A variety of these ways have been tried for storing objects in a database. Some products have approached the problem from the application-programming side, by making the objects manipulated by the program persistent. This also typically requires the addition of some kind of query language, since conventional programming languages do not provide language-level functionality for finding objects based on their information content. Others have attacked the problem from the database end, by defining an object-oriented data model for the database, and defining a database programming language that allows full programming capabilities as well as traditional query facilities. Storage structuresDatabases may store relational tables/indexes in memory or on hard disk in one of many forms: •ordered/unordered flat files•ISAM•heaps•hash buckets•logically-blocked files•B+ treesThe most commonly used are B+ trees and ISAM.Object databases use a range of storage mechanisms. Some use virtual memory-mapped files tomake the native language (C++, Java etc.) objects persistent. This can be highly efficient but it can make multi-language access more difficult. Others disassemble objects into fixed- and varying-length components that are then clustered in fixed sized blocks on disk and reassembled into the appropriate format on either the client or server address space. Another popular technique involves storing the objects in tuples (much like a relational database) which the database server then reassembles into objects for the client.Other techniques include clustering by category (such as grouping data by month, or location), storing pre-computed query results, known as materialized views, partitioning data by range (e.g., a data range) or by hash.Memory management and storage topology can be important design choices for database designers as well. Just as normalization is used to reduce storage requirements and improve database designs, conversely denormalization is often used to reduce join complexity and reduc e query execution time.IndexingIndexing is a technique for improving database performance. The many types of index share the common property that they eliminate the need to examine every entry when running a query. In large databases, this can reduce query time/cost by orders of magnitude. The simplest form of index is a sorted list of values that can be searched using a binary search with an adjacent reference to the location of the entry, analogous to the index in the back of a book. The same data can have multiple indexes (an employee database could be indexed by last name and hire date.)Indexes affect performance, but not results. Database designers can add or remove indexes without changing application logic, reducing maintenance costs as the database grows and database usage evolves.Given a particular query, the DBMS' query optimizer is responsible for devising the most efficient strategy for finding matching data. The optimizer decides which index or indexes to use, how to combine data from different parts of the database, how to provide data in the order requested, etc.Indexes can speed up data access, but they consume space in the database, and must be updated each time the data are altered. Indexes therefore can speed data access but slow data maintenance. These two properties determine whether a given index is worth the cost.TransactionsMost DBMS provide some form of support for transactions, which allow multiple data items to be updated in a consistent fashion, such that updates that are part of a transaction succeed or fail in unison. The so-called ACID rules, summarized here, characterize this behavior:•Atomicity: Either all the data changes in a transaction must happen, or none of them. The transaction must be completed, or else it must be undone (rolled back).•Consistency: Every transaction must preserve the declared consistency rules for the database. •Isolation: Two concurrent transactions cannot interfere with one another. Intermediate results within one transaction must remain invisible to other transactions. The most extreme form of isolation is serializability, meaning that transactions that take place concurrently could instead be performed in some series, without affecting the ultimate result.•Durability: Completed transactions cannot be aborted later or their results discarded. They must persist through (for instance) DBMS restarts.In practice, many DBMSs allow the selective relaxation of these rules to balance perfect behavior with optimum performance.ReplicationDatabase replication involves maintaining multiple copies of a database on different computers, to allow more users to access it, or to allow a secondary site to immediately take over if the primary site stops working. Some DBMS piggyback replication on top of their transaction logging facility, applying the primary's log to the secondary in near real-time. Database clustering is a related concept for handling larger databases and user communities by employing a cluster of multiple computers to host a single database that can use replication as part of its approach.SecurityDatabase security denotes the system, processes, and procedures that protect a database from unauthorized activity.DBMSs usually enforce security through access control, auditing, and encryption:•Access control manages who can connect to the database via authentication and what they can do via authorization.•Auditing records information about database activity: who, what, when, and possibly where. •Encryption protects data at the lowest possible level by storing and possibly transmitting data in an unreadable form. The DBMS encrypts data when it is added to the database and decrypts it when returning query results. This process can occur on the client side of a network connection to prevent unauthorized access at the point of use.ConfidentialityLaw and regulation governs the release of information from some databases, protecting medical history, driving records, telephone logs, etc.In the United Kingdom, database privacy regulation falls under the Office of the Information Commissioner. Organizations based in the United Kingdom and holding personal data in digital format such as databases must register with the Office.LockingWhen a transaction modifies a resource, the DBMS stops other transactions from also modifying it, typically by locking it. Locks also provide one method of ensuring that data does not c hange while a transaction is reading it or even that it doesn't change until a transaction that once read it has completed.GranularityLocks can be coarse, covering an entire database, fine-grained, covering a single data item, or intermediate covering a collection of data such as all the rows in a RDBMS table.Lock typesLocks can be shared or exclusive, and can lock out readers and/or writers. Locks can be created implicitly by the DBMS when a transaction performs an operation, or explic itly at the transaction's request.Shared locks allow multiple transactions to lock the same resource. The lock persists until all such transactions complete. Exclusive locks are held by a single transaction and prevent other transactions from locking the same resource.Read locks are usually shared, and prevent other transactions from modifying the resource. Write locks are exclusive, and prevent other transactions from modifying the resource. On some systems, write locks also prevent other transactions from reading the resource.The DBMS implicitly locks data when it is updated, and may also do so when it is read.Transactions explicitly lock data to ensure that they can complete without a deadlock or other complication. Explic it locks may be useful for some administrative tasks.Locking can significantly affect database performance, especially with large and complex transactions in highly concurrent environments.IsolationIsolation refers to the ability of one transaction to see the results of other transactions. Greater isolation typically reduces performance and/or concurrency, leading DBMSs to provide administrative options to reduce isolation. For example, in a database that analyzes trends rather than looking at low-level detail, increased performance might justify allowing readers to see uncommitted changes ("dirty reads".)DeadlocksDeadlocks occur when two transactions each require data that the other has already locked exclusively. Deadlock detection is performed by the DBMS, which then aborts one of the transactions and allows the other to complete.From: Wikipedia, the free encyclopedia。

【计算机专业文献翻译】优调SQL过程

外文资料译文及原文院（系）：计算机学院专业：计算机科学与技术班级：2401102学号：20023011059姓名：指导教师：2005年6月简介有关如何调优数据库系统和应用程序的好的建议的来源有很多。

比如OLTP 应用程序的DB2调优技巧(以前在IBM® DB2® 开发者园地上发表)之类的文章通过使用事务和数据并行性以及分析查询方案，给出了从表空间和索引设计到缓冲池的内存分配等方面的建议。

这些方面的内容是性能调优的基础知识。

但是，有关如何组织存储过程自身中的逻辑并着眼于其性能的专门建议却并不多见。

本文就提供了这样一种建议。

尽管本文着重于介绍 SQL 过程，但是这里所提供的大多数信息同样适用于用其它语言编写的在应用程序中或存储过程中嵌入的 SQL 逻辑。

背景知识和术语在深入研究详细问题之前，让我们先想想DB2 中有关过程化 SQL 的一些基本术语和概念。

过程化 SQL 构造（例如标量变量、IF 语句和 WHILE 循环）是在DB2 Universal Database™ (UDB) V7 发行版中引入 DB2 的。

以前的 DB2 发行版支持 C 和Java™ 作为存储过程的语言。

V7 引入了 SQL 存储过程，以及其它许多可以促进 OLTP 应用程序开发的特性（例如临时表、应用程序保存点和标识列）。

当创建 SQL 过程时，DB2 将过程主体中的 SQL 查询与过程逻辑区分开来。

为了使性能最优，SQL 查询被静态地编译成包中的节。

（对于静态编译的查询而言，节主要是由 DB2 优化器为该查询选择的存取方案构成的。

包是节的集合。

在过程的执行期间，每当控制从过程逻辑流向 SQL 语句时，在 DLL 和 DB2 引擎之间就存在“上下文切换”。

（在 DB2 V8 中，SQL 过程是在“不受保护的方式”下运行的，即与 DB2 引擎在相同的寻址空间中。

因此我们这里谈及的上下文切换并不是操作系统级别上的完全的上下文切换，而是指 DB2 中层的更换。

数据库外文参考文献及翻译

数据库外文参考文献及翻译数据库外文参考文献及翻译SQL ALL-IN-ONE DESK REFERENCE FOR DUMMIESData Files and DatabasesI. Irreducible complexityAny software system that performs a useful function is going to be complex. The more valuable the function, the more complex its implementation will be. Regardless of how the data is stored, the complexity remains. The only question is where that complexity resides. Any non-trivial computer application has two major components: the program the data. Although an application’s level of complexity depends on the task to be performed, developers have some control over the location of that complexity. The complexity may reside primarily in the program part of the overall system, or it may reside in the data part.Operations on the data can be fast. Because the programinteracts directly with the data, with no DBMS in the middle, well-designed applications can run as fast as the hardware permits. What could be better? A data organization that minimizes storage requirements and at the same time maximizes speed of operation seems like the best of all possible worlds. But wait a minute . Flat file systems came into use in the 1940s. We have known about them for a long time, and yet today they have been almost entirely replaced by database s ystems. What’s up with that? Perhaps it is the not-so-beneficial consequences。

数据库管理系统毕业论文中英文资料对照外文翻译文献综述

数据库管理系统毕业论文中英文资料对照外文翻译文献综述中英文资料对照外文翻译文献综述英文翻译数据库管理系统的介绍Raghu Ramakrishnan数据库（database,有时被拼作data base）又称为电子数据库，是专门组织起来的一组数据或信息，其目的是为了便于计算机快速查询及检索。

数据库的结构是专门设计的，在各种数据处理操作命令的支持下，可以简化数据的存储、检索、修改和删除。

数据库可以存储在磁盘、磁带、光盘或其他辅助存储设备上。

数据库由一个或一套文件组成，其中的信息可以分解为记录，每一条记录又包含一个或多个字段（或称为域）。

字段是数据存取的基本单位。

数据库用于描述实体，其中的一个字段通常表示与实体的某一属性相关的信息。

通过关键字以及各种分类（排序）命令，用户可以对多条记录的字段进行查询，重新整理，分组或选择，以实体对某一类数据的检索，也可以生成报表。

所有数据库（除最简单的）中都有复杂的数据关系及其链接。

处理与创建，访问以及维护数据库记录有关的复杂任务的系统软件包叫做数据库管理系统（DBMS）。

DBMS软件包中的程序在数据库与其用户间建立接口。

（这些用户可以是应用程序员，管理员及其他需要信息的人员和各种操作系统程序）DBMS可组织、处理和表示从数据库中选出的数据元。

该功能使决策者能搜索、探查和查询数据库的内容，从而对正规报告中没有的，不再出现的且无法预料的问题做出回答。

这些问题最初可能是模糊的并且（或者）是定义不恰当的，但是人们可以浏览数据库直到获得所需的信息。

简言之，DBMS将“管理”存储的数据项和从公共数据库中汇集所需的数据项用以回答非程序员的询问。

DBMS由3个主要部分组成：（1）存储子系统，用来存储和检索文件中的数据；（2）建模和操作子系统，提供组织数据以及添加、删除、维护、更新数据的方法；（3）用户和DBMS之间的接口。

在提高数据库管理系统的价值和有效性方面正在展现以下一些重要发展趋势：1.管理人员需要最新的信息以做出有效的决策。

数据库外文翻译外文文献英文文献数据库安全

Database Security“Why do I need to secure my database server? No one can access it —it’s in a DMZ protected by the firewall!” This is often the response when it is recommended that such devices are included within a security health check. In fact, database security is paramount in defending an organizations information, as it may be indirectly exposed to a wider audience than realized.This is the first of two articles that will examine database security. In this article we will discuss general database security concepts and common problems. In the next article we will focus on specific Microsoft SQL and Oracle security concerns.Database security has become a hot topic in recent times. With more and more people becoming increasingly concerned with computer security, we are finding that firewalls and Web servers are being secured more than ever(though this does not mean that there are not still a large number of insecure networks out there). As such, the focus is expanding to consider technologies such as databases with a more critical eye.◆Common sense securityBefore we discuss the issues relating to database security it is prudent to high- light the necessity to secure the underlying operating system and supporting technologies. It is not worth spending a lot of effort securing a database if a vanilla operating system is failing to provide a secure basis for the hardening of the data- base. There are a large number of excellent documents in the public domain detailing measures that should be employed when installing various operating systems.One common problem that is often encountered is the existence of a database on the same server as a web server hosting an Internet (or Intranet) facing application. Whilst this may save the cost of purchasing a separate server, it does seriously affect the security of the solution. Where this is identified, it is often the case that the database is openly connected to the Internet. One recent example I can recall is an Apache Web server serving an organizations Internet offering, with an Oracle database available on the Internet on port 1521. When investigating this issue further it was discovered that access to the Oracle server was not protected (including lack of passwords), which allowed the server to be stopped. The database was not required from an Internet facing perspective, but the use of default settings and careless security measures rendered the server vulnerable.The points mentioned above are not strictly database issues, and could be classified as architectural and firewall protection issues also, but ultimately it is the database that is compromised. Security considerations have to be made from all parts of a public facing net- work. You cannot rely on someone or something else within your organization protecting your database fr om exposur e.◆ Attack tools are now available for exploiting weaknesses in SQL and OracleI came across one interesting aspect of database security recently while carrying out a security review for a client. We were performing a test against an intranet application, which used a database back end (SQL) to store client details. The security review was proceeding well, with access controls being based on Windows authentication. Only authenticated Windows users were able to see data belonging to them. The application itself seemed to be handling input requests, rejecting all attempts to access the data- base directly.We then happened to come across a backup of the application in the office in which we were working. This media contained a backup of the SQL database, which we restored onto our laptop. All security controls which were in place originally were not restored with the database and we were able to browse the complete database, with no restrictions in place to protect the sensitive data. This may seem like a contrived way of compromising the security of the system, but does highlight an important point. It is often not the direct approach that is taken to attack a target, and ultimately the endpoint is the same; system compromise. A backup copy of the database may be stored on the server, and thus facilitates access to the data indirectly.There is a simple solution to the problem identified above. SQL 2000 can be configured to use password protection for backups. If the backup is created with password protection, this password must be used when restoring the password. This is an effective and uncomplicated method of stopping simple capture of backup data. It does however mean that the password must be remembered!◆Curr ent tr endsThere are a number of current trends in IT security, with a number of these being linked to database security.The focus on database security is now attracting the attention of the attackers. Attack tools are now available for exploiting weaknesses in SQL and Oracle. The emergence of these tools has raised the stakes and we have seen focused attacks against specific data- base ports on servers exposed to the Internet.One common theme running through the security industry is the focus on application security, and in particular bespoke Web applications. With he functionality of Web applications becoming more and more complex, it brings the potential for more security weaknesses in bespoke application code. In order to fulfill the functionality of applications, the backend data stores are commonly being used to format the content of Web pages. This requires more complex coding at the application end. With developers using different styles in code development, some of which are not as security conscious as other, this can be the source of exploitable errors.SQL injection is one such hot topic within the IT security industry at the moment. Discussions are now commonplace among technical security forums, with more and more ways and means of exploiting databases coming to light all the time. SQL injection is a misleading term, as the concept applies to other databases, including Oracle, DB2 and Sybase.◆ What is SQL Injection?SQL Injection is simply the method of communication with a database using code or commands sent via a method or application not intended by the developer. The most common form of this is found in Web applications. Any user input that is handled by the application is a common source of attack. One simple example of mishandling of user input is highlighted in Figure 1.Many of you will have seen this common error message when accessing web sites, and often indicates that the user input has not been correctly handled. On getting this type of error, an attacker will focus in with more specific input strings.Specific security-related coding techniques should be added to coding standard in use within your organization. The damage done by this type of vulnerability can be far reaching, though this depends on the level of privileges the application has in relation to the database.If the application is accessing data with full administrator type privileges, then maliciously run commands will also pick up this level of access, and system compromise is inevitable. Again this issue is analogous to operating system security principles, where programs should only be run with the minimum of permissions that is required. If normal user access is acceptable, then apply this restriction.Again the problem of SQL security is not totally a database issue. Specific database command or requests should not be allowed to pass through theapplication layer. This can be prevented by employing a “secure coding” approach.Again this is veering off-topic, but it is worth detailing a few basic steps that should be employed.The first step in securing any application should be the validation and control of user input. Strict typing should be used where possible to control specific data (e.g. if numeric data is expected), and where string based data is required, specific non alphanumeric characters should be prohibited where possible. Where this cannot be performed, consideration should be made to try and substitute characters (for example the use of single quotes, which are commonly used in SQL commands).Specific security-related coding techniques should be added to coding standard in use within your organization. If all developers are using the same baseline standards, with specific security measures, this will reduce the risk of SQL injection compromises.Another simple method that can be employed is to remove all procedures within the database that are not required. This restricts the extent that unwanted or superfluous aspects of the database could be maliciously used. This is analogous to removing unwanted services on an operating system, which is common security practice.◆ OverallIn conclusion, most of the points I have made above are common sense security concepts, and are not specific to databases. However all of these points DO apply to databases and if these basic security measures are employed, the security of your database will be greatly improved.The next article on database security will focus on specific SQL and Oracle security problems, with detailed examples and advice for DBAs and developers.There are a lot of similarities between database security and general IT security, with generic simple security steps and measures that can be (and should be) easily implemented to dramatically improve security. While these may seem like common sense, it is surprising how many times we have seen that common security measures are not implemented and so causea security exposure.◆User account and password securityOne of the basic first principals in IT security is “make su re you have a good password”. Within this statement I have assumed that a password is set in the first place, though this is often not the case.I touched on common sense security in my last article, but I think it is important to highlight this again. As with operating systems, the focus of attention within database account security is aimed at administrationaccounts. Within SQL this will be the SA account and within Oracle it may be the SYSDBA or ORACLE account.It is very common for SQL SA accounts to have a password of ‘SA’ or even worse a blank password, which is just as common. This password laziness breaks the most basic security principals, and should be stamped down on. Users would not be allowed to have a blank password on their own domain account, so why should valuable system resources such as databases be allowed to be left unprotected. For instance, a blank ‘SA’password will enable any user with client software (i.e. Microsoft query analyser or enterprise manager to ‘manage’ the SQL server and databases).With databases being used as the back end to Web applications, the lack of password control can result in a total compromise of sensitive information. With system level access to the database it is possible not only to execute queries into the database, create/modify/delete tables etc, but also to execute what are known as Stored Procedures.数据库安全“为什么要确保数据库服务安全呢？任何人都不能访问-这是一个非军事区的保护防火墙”，当我们被建议使用一个带有安全检查机制的装置时，这是通常的反应。

【计算机专业文献翻译】SQL查询的安全性证明

Proving the Safety of SQL QueriesAbstract：Many programs need to access data in a relational database. This is usually done by means of queries written in SQL. Although the language SQL is declarative, certain runtime errors are possible. Since the occurrence of these errors depends on the data, they are not easily found during testing. The question whether a query is safe can be reduced to a consistency check. It is well known that consistency is in general undecidable, and that this applies also to SQL queries. However, in this paper, we propose a consistency check that can handle a surprisingly large subset of SQL (it uses Skolemizationwith sorted Skolem functions, and a few other tricks). This consistency check is also the basis for generating other semantic warnings. Furthermore, it can be used to generate test data for SQL queries.Keywords:SQL, Runtime Errors, Consistency.1. IntroductionProbably a large percentage of the software that is developed today uses data stored in a relational database. The database is normally accessed with statements in the language SQL, especially queries. Although is a declarative language, there are situations in which errors might occur at runtime that depend on the data.2. Possible Runtime Errors in SQLAs explained in the introduction, if the INTO-clause is used for storing the query result in program variables, the query must never return more than one row. Suppose the given query is：SELECT t1, . . ., tk INTO v1, . . ., vk FROM R1 X1, . . ., Rn Xn WHERE ϕIn order to make sure that there are never two solutions, we duplicate the tuple variables and check the following query for consistency. If it is consistent (including the constraints, see Section 3.3), a runtime error can occur, and the constructed model givesan example:SELECT * FROM R1 X1, ..., Rn Xn,R1 X_1 , ..., Rn X_n WHERE ϕ AND ϕ AND (X1= X_1 OR ···OR Xn _= X_n )The formula ϕ_ results from ϕ by replacing each Xi by X_i . We use Xi _= X_i as an abbreviation for requiring that the primary key values of the two tuple variables are different (we assume that primary keys are always NOT NULL). If one of the relations Ri has no declared key, it is always possible that there are several solutions(if the condition ϕ is consistent).If the given query uses “SELECT DISTINCT”, one needs to add a test that the result tuples differ:(t1 _= t_1 OR ···OR tk _= t_kOR t1 IS NULL AND t_1 IS NOT NULLOR t_1 IS NULL AND t1 IS NOT NULL. . .OR tk IS NULL AND t_k IS NOT NULLOR t_k IS NULL AND tk IS NOT NULL)The same problem can occur with conditions of the form A = (SELECT ...) inside a query: Whenever a subquery is used as scalar expression, it must not return multiple rows. If the subquery is non-correlated(i.e. does not access tuple variables from the outer query), we can use exactly the same test as above. If the query is correlated, it might not be completely clear what knowledge from the outer condition should be used (as usual for runtime errors, evaluation order becomes important here). In order to be safe, we propose to ignore the outer condition. Let the subquery have the form：SELECT t FROM R1 X1, . . ., Rn Xn WHERE ϕIf it accesses the tuple variables S1 Y1, . . ., Sm Ym from the outer query, we would require that the following query is inconsistent (after adding the constraints):SELECT * FROM R1 X1, ..., Rn Xn, R1 X_1 , ..., Rn X_n , S1 Y1, ..., Sm Ym WHERE ϕ AND ϕ_ AND (X1 _= X_1 OR ···OR Xn _= X_n )3. Inconsistent ConditionsIn this section, we present an algorithm for detecting inconsistent conditions in SQL queries. A trivial example of an inconsistent query isSELECT * FROM R WHERE A=1 AND A=2.Since the problem is in general undecidable, we can handle only a subset of all queries. However, our algorithm is reasonably powerful and can decide the consistency of surprisingly many queries.To be precise, consistency in databases means that there is a finite model, i.e. a relational database state(sometimes called a database instance), such that the query result is not empty.In this paper, we assume that the given SQL query contains no data type operations, i.e. all atomic formulas are of the form t1 θt2 where θis a comparison operator (=, <>, <, <=, >, >=), and t1, t2 are attributes (possibly qualified with a tuple variable) or constants (literals). Null values and IS NULL are treated in Section 3.5, before that, they are excluded. Aggregations and LIKE-conditions are not treated in this paper, they are subject of our future research.3.1. Conditions Without SubqueriesIf the query contains no subqueries, the consistency can be decided with methods known in the literature, especially the algorithms of Guo, Sun and Weiss [8].The condition then consists of the above atomic formulas connected with AND, OR, NOT. We first push negation down to the atomic formulas, where it simply “turns around” the comparison operator. In t his way, NOT is eliminated from the formula. Then, we translate the formula in disjunctive normal form: ϕ1 ∨···∨on is consistent if at least one of the it is consistent. Now a conjunction of the above atomic formulas can be tested for satisfiability with the method of [8]. They basically create a directed graph in which nodes are labeled with “Tuplevariable.Attribute” (maybe a representative for an equivalence class with respect to =) and edges are labeled with < or ≤. Then they compute an interval of possible values for each node. Note that SQL data types likeNUMERIC(1) also restrict the interval of possible values.Unfortunately, if there is only a finite number of values that can be assigned to nodes, inequality conditions (t1 <> t2) between the nodes become important and can encode graph-coloring problems. Therefore, we cannot expect an efficient algorithm if there are many <>- conditions. Otherwise, the method of [8] is fast. (However, the DNF transformation that we apply before [8] can lead to an exponential increase in size.)3.2.Integrity ConstraintsThe above algorithm constructs just any model of the query, not necessarily a database state that satisfies all constraints. However, it is easy to add conditions to the query that ensure that all constraints are satisfied. For instance, consider this constraint on GRADES:CHECK(POINTS >= 0)Then the following condition would be added to each query that references GRADES:AND NOT EXISTS (SELECT * FROM GRADES WHERE NOT(POINTS >= 0)) The original query is consistent relative to the constraints if this extended query is consistent. Note that pure “for all” constraints like keys or CHECK-constraints do not need nested subqueries and thus never endanger the termination of the method. No new Skolem functions are constructed, the conditions are only instantiated for each existing Skolem term of the respective sort (relation). This is also what one would intuitively expect. Foreign keys, however, require the existence of certain tuples, and therefore might sometimes result in an infinite set TQ. This is subject of the next section.3.3 Restrictions and Possible SolutionsThe main restriction of our method is that the set TQ must be finite, i.e. no tuple variable over a relation R may depend directly or indirectly on a tuple variable over the same relation R. This is certainly satisfied if there is only a single level of subqueries.However, GRADES has a foreign key ENO that references EXERCISES. This can be enforced in models by adding the following condition to all queries:AND NOT EXISTS (SELECT * FROM GRADES C WHERE NOT EXISTS (SELECT * FROM EXERCISES P WHERE P.ENO = C.ENO))We now get a Skolem function fP : GRADES →EXERCISES. In itself this would be no problem, and actually there will never be a problem if the foreign keys are not cyclic and the query itself contains only a single level of NOT EXISTS. But in Example 1, the query introduces the Skolem function fG : EXERCISES →GRADES.Together we can now generate infinitely many terms: fE1, fG(fE1), fP(fG(fE1)), fG(fP(fG(fE1))), and so on. However, it is easy to prove that fP(fG(fE1)) = fE1: We know thatfG(fE1).ENO = fE1.ENO andfP(fG(fE1)).ENO = fG(fE1).ENO.Since ENO is key of EXERCISES, the two tuples must be the same, and thus a finite TQ suffices. One case that is not solved is cyclic (recursive) foreign keys. Because of the undecidability, this problem can in general not be eliminated. However, one could at least heuristically try to construct a model by assuming that, e.g., 2 tuples in the critical relation R suffice. Then TQ(R) would consist of two constants and one would replace each subquery declaring a tuple variable over R by a disjunction with these two constants. For relations not in the cycle, the original method could still be used. If the algorithm of Section 3.1 constructs a model, the query is of course consistent. If no model is found, the system can print a warning that it cannot verify the consistency. At user option, it would also be possible to repeat the step with more constants.4. Related WorkAs far as we know, there is not yet a tool for checking given SQL queries for semantic errors independently of example database states. There are a few papers about testing SQL queries, though (e.g. [13, 12]).Of course, for the problem of detecting inconsistent conditions, a large body of work exists in the literature. In general, all work in automated theorem proving can be applied (see, e.g., [4]). The problem whether there exists a contradiction in a conjunction of inequalities is very relevant for many database problems and has been intensively studiedin the literature. Klug’sclassic paper [10] checks for such inconsistencies but does not treat subqueries and assumes dense domains for the attributes. The algorithm in [9] can handle recursion, but only negations of EDB predicates, not generalNOT EXISTS subqueries. A very efficient method has been proposed by Guo, Sun, and Weiss [8]. We use it here as a subroutine. Our main contribution is the way we treat subqueries. Although this uses ideas known from Skolemization, the way we apply it combined with analgorithm like [8], apply the relations as sorts, and detect equal terms in the Herbrand universe seems new.We also can handle null values. Consistency checking in databases has also been applied for testing whether a setoff constraint is satisfiable. A classic paper about this problem is [3]. They give an algorithm which terminates if the constraints are finitely satisfiable or if they are unsatisfiable,which is the best one can do. However, the approach presented here can immediately tell whether it can handle the given query and constraints. Also in the field of description logics, decidable fragments of first order logic are used. Recently Minock [11] defined a logic that is more restricted than ours, but is closed under syntactic query difference.Consistency of database queries has also connections to semantic query optimization (see, e.g., [5]), and cooperative query answering (see, e.g., [7, 6]). However, in semantic query optimization, only relatively simple consistency checks can be used for efficiency reasons(optimization time must be amortized during later execution).In cooperative query answering, a database state is given: The system does not really notice that the query is inconsistent, only that it yields an empty answer in the current state. In both fields, the question for possible runtime errors is not asked.Test data generation as in [13] is also a form of consistency check, and both papers have overlapping goals But the concrete methods are quite different.5. ConclusionsQuality software must be reliable in the sense that it never generates runtime errors (exceptions). A check for the safety of SQL queries can be reduced to a consistency check.In this paper, we proposed a new method for checking the consistency of SQLqueries: We used a state-of-the-art algorithm for conjunctions of comparisons(=, _=, <, >, ≤, ≥) [8], and extended it by handling subqueries with an interesting variant of Skolemization,Skolemization,and null values/three-valued logic with operators “null-to-false” and “null-to-true”. The method can also be used to generate test data. Our overall goal is to develop a semantic checker for SQL queries [2].References:[1] Serge Ambitious, Richard Hull, and Victor Vianu. Foundations of Databases. Addison-Wesley, 1994.[2] Stefan Brass and Christian Goldberg. Semantic errors in SQL queries: A quite complete list. In Proc. of the Fourth Int. Conf. on Quality Software (QSIC’04), 250–257. IEEE Computer Society Press, 2004. Extended version to appear in Journal of Systems and Software.[3] Franc¸ois Bry and Rainer Manthey. Checking consistency of database constraints: a logical basis. In Proceedings of the 12th Int. Conf. on Very Large Data Bases (VLDB’86), 13–20. Morgan Kaufmann, 1986.[4] Chin-Liang Chang and Richard Char-Tung Lee. Symbolic Logic and Mechanical Theorem Proving. Academic Press, 1973.[5] Qi Cheng, Jarek Gryz, Fred Koo, Cliff Leung, Linqi Liu, Xiaoyan Qian, and Bernhard Schiefer. Implementation of two semantic query optimization techniques in DB2 universal database. In Proc. of the 25th Int. Conf. on Very Large Data Bases (VLDB’99), 687–698, 1999.[6] Wesley W. Chu, Hua Yang, Kuorong Chiang, Michael Minock, Gladys Chow, and Chris Larson. CoBase: A scalable and extensible cooperative information system. Jour. of Intelligent Information Syst., 6:223–259, 1996.[7] Terry Gaasterland, Parke Godfrey, and Jack Minker. An overview of cooperative answering. Journal of Intelligent Information Systems, 1(2):123–157, 1992.[8] Sha Guo, Wei Sun, and Mark A. Weiss. Solving satis- fiability and implication problems in database systems. ACM Trans. on Database Systems, 21:270–293, 1996. [9] Alon Y. Halevy, Inderpal Singh Mumick, Yehoshua Sagiv, and Oded Shmueli. Static analysis in Datalog extensions. Journal of the ACM, 48:971–1012, 2001.。

数据库外文参考文献及翻译.

数据库外文参考文献及翻译数据库外文参考文献及翻译数据库管理系统——实施数据完整性一个数据库，只有用户对它特别有信心的时候。

这就是为什么服务器必须实施数据完整性规则和商业政策的原因。

执行SQL Server的数据完整性的数据库本身，保证了复杂的业务政策得以遵循，以及强制性数据元素之间的关系得到遵守。

因为SQL Server的客户机/服务器体系结构允许你使用各种不同的前端应用程序去操纵和从服务器上呈现同样的数据，这把一切必要的完整性约束，安全权限，业务规则编码成每个应用，是非常繁琐的。

如果企业的所有政策都在前端应用程序中被编码，那么各种应用程序都将随着每一次业务的政策的改变而改变。

即使您试图把业务规则编码为每个客户端应用程序，其应用程序失常的危险性也将依然存在。

大多数应用程序都是不能完全信任的，只有当服务器可以作为最后仲裁者，并且服务器不能为一个很差的书面或恶意程序去破坏其完整性而提供一个后门。

SQL Server使用了先进的数据完整性功能，如存储过程，声明引用完整性（DRI），数据类型，限制，规则，默认和触发器来执行数据的完整性。

所有这些功能在数据库里都有各自的用途;通过这些完整性功能的结合，可以实现您的数据库的灵活性和易于管理，而且还安全。

声明数据完整性声明数据完整原文请找腾讯3249114六,维-论'文.网定义一个表时指定构成的主键的列。

这就是所谓的主键约束。

SQL Server使用主键约束以保证所有值的唯一性在指定的列从未侵犯。

通过确保这个表有一个主键来实现这个表的实体完整性。

有时，在一个表中一个以上的列（或列的组合）可以唯一标志一行，例如，雇员表可能有员工编号（ emp_id ）列和社会安全号码（ soc_sec_num ）列，两者的值都被认为是唯一的。

这种列经常被称为替代键或候选键。

这些项也必须是唯一的。

虽然一个表只能有一个主键，但是它可以有多个候选键。

SQL Server的支持多个候选键概念进入唯一性约束。

mysql数据库英文文献

mysql数据库英文文献及翻译MySQL architecture is best understood in the context of its history. Thus, the two are discussed in the same chapter.MySQL HistoryMySQL history goes back to 1979 when Monty Widenius, working for a small companycalled TcX, created a reporting tool written in BASIC that ran on a 4 Mhzcomputer with 16 KB RAM. Over time, the tool was rewritten in C and ported to run on Unix. It was still just a low-level storage engine with a reporting front end. The tool was known by the name of Unireg.Working under the adverse conditions of little computational resources, and perhaps building on his God-given talent,Monty developed a habit and ability to write very efficient code naturally. He also developed, or perhaps was gifted from the start,with an unusually acute vision of what needed to be done to the code to make it useful in future development—without knowing in advance much detail about what that future development would be.In addition to the above, with TcX being a very small company and Monty being one of the owners, he had a lot of say in what happened to his code. While there are perhaps a good number of programmers out there with Monty’s talent and ability, for a number of reasons, few get to carry their code around for more than 20 years. Monty did.Monty’s work, talents, and ownership of the code provided a foundation upon which the Miracle of MySQL could be built.Some time in the 1990s, TcX customers began to push for an SQL interface to their data. Several possibilities were considered. One was to load it into a commercial database.Monty was not satisfied with the speed. He tried borrowing mSQL code for the SQL part and integrating it with his low-level storage engine. That did not work well,either. Then came the classic move of a talented,driven programmer: “I’ve had enough of those tools that somebody else wrote that don’t work! I’m writing my own!”Thus in May of 1996 MySQL version 1.0 was released to a limited group, followed by a public release in October 1996 of version 3.11.1. The initial public release provided only a binary distribution for Solaris. A month later, the source and the Linux binary were released.In the next two years, MySQL was ported to a number of other operating systems as the feature set gradually increased. MySQL was originally released under a special license that allowed commercial use to those who were not redistributing it with their software. Special licenses were available for sale to those who wanted to bundle it with their product. Additionally, commercial support was also being sold. This provided TcX with some revenue to justify the further development of MySQL,although the purpose of its original creation had already been fulfilled.During this period MySQL progressed to version 3.22. It supported a decent subset of the SQL language, had an optimizer a lot more sophisticated than one would expect could possibly be written by one person, was extremely fast, and was very stable.Numerous APIs were contributed, so one could write a client in pretty much any existing programming language. However, it still lacked support for transactions,subqueries, foreign keys, stored procedures, and views. The locking happened only at a table level, which in some cases could slow it down to a grinding halt. Someprogrammers unable to get around its limitations still considered it a toy, while others were more than happy to dump their Oracle or SQL Server in favor of MySQL, and deal with the limitations in their code in exchange for improvement in performance and licensing cost savings.Around 1999–2000 a separate company named MySQL AB was established. It hired several developers and established a partnership with Sleepycat to provide an SQL interface for the Berkeley DB data files. Since Berkeley DB had transaction capabilities,this would give MySQL support for transactions, which it previously lacked.After some changes in the code in preparation for integrating Berkeley DB,version 3.23 was released.Although the MySQL developers could never work out all the quirks of the Berkeley DB interface and the Berkeley DB tables were never stable, the effort was not wasted.As a result, MySQL source became equipped with hooks to add any type of storage engine, including a transactional one.By April of 2000, with 原文请找腾讯3249114六~维-论~文.网,ISAM, was reworked and released as MyISAM. Among a number of improvements,full-text search capabilities were now supported. A short-lived partnership with NuSphere to add Gemini, a transactional engine with row-level locking, ended in a lawsuit toward the end of 2001. However, around the same time, Heikki Tuuri approached MySQL AB with a proposal to integrate his own storage engine,InnoDB, which was also capable of transactions and row-level locking.Heikki’s contribut ion integrated much more smoothly with the new table handler interface already polished off by the Berkeley DB integration efforts. The MySQL/InnoDB combination became version 4.0, and was released as alpha in October of 2001. By early 2002 the MySQL/InnoDB combowas stable and instantly took MySQL to another level. Version 4.0 was finally declared production stable in March 2003.It might be worthy of mention that the version number change was not caused by the addition of InnoDB. MySQL developers have always viewed InnoDB as an important addition, but by no means something that they completely depend on for success.Back then, and even now, the addition of a new storage engine is not likely to be celebrated with a version number change. In fact, compared to previous versions,not much was added in version 4.0. Perhaps the most significant addition was the query cache, which greatly improved performance of a large number ofapplications.Replication code on the slave was rewritten to use two threads: one for network I/O from the master, and the other to process the updates. Some improvements were added to the optimizer. The 1506mysql数据库英文文献及翻译client/server protocol became SSL-capable.Version 4.1 was released as alpha in April of 2003, and was declared beta in June of 2004. Unlike version 4.0, it added a number of significant improvements. Perhaps the most significant was subqueries, a feature long-awaited by many users. Spatial indexing support was added to the MyISAM storage engine. Unicode support was implemented. The client/server protocol saw a number of changes. It was made more secure against attacks, and supported prepared statements.In parallel with the alpha version of 4.1, work progressed on yet another development branch: version 5.0, which would add stored procedures, server-side cursors,triggers, views, XA transactions, significant improvements in the query optimizer,and a number of other features. The decision to create a separate development branch was made because MySQL developers felt that it would take a long time to stabilize 4.1 if, on top of all the new features that they were adding to it, they had to deal with the stored procedures. Version 5.0 was finally released as alpha in December 2003. For a while this created quite a bit of confusion—there were two branches in the alpha stage. Eventually 4.1 stabilized (October 2004), and the confusion was resolved.Version 5.0 stabilized a year later, in October of 2005.The first alpha release of 5.1 followed in November 2005, which added a number of improvements, some of which are table data partitioning, row-based replication,event scheduler, and a standardized plug-in API that facilitates the integration of new storage engines and other plug-ins.At this point, MySQL is being actively developed. 5.0 is currently the stable version,while 5.1 is in beta and should soon become stable. New features at this point go into version 5.2.MySQL ArchitectureFor the large part, MySQL architecture defies a formal definition or specification.When most of the code was originally written, it was not done to be a part of some great system in the future, but rather to solve some very specific problems. However,it was written so well and with enough insight that it reached the point where there were enough quality pieces to assemble a database server.Core ModulesI make an attempt in this section to identify the core modules in the system. However,let me add a disclaimer that this is only an attempt to formalize what exists.MySQL developers rarely think in those terms. Rather, they tend to think of files,directories, classes, structures, and functions. It is much more common to hear “This happens in mi_open( )” than to hear “This happens on the MyISAM storage engine level.” MySQL developers know the code so well that they are able to think conceptually on the level of functions, structures, and classes. They will probably find the abstractions in this section rather useless. However, it would be helpful to a person used to thinking in terms of modules and managers.With regard to MySQL, I use the term “module” rather loosely. Unlike what one would typically call a module, in many cases it is not something you can easily pull out and replace with another implementation. The code from one module might be spread across several files, and you often find the code from several different modules in the same file. This is particularly true of the older code. The newer code tends to fit into the pattern of modules better. So in our definition, a module is a piece of code that logically belongs together in some way, and performs a certain critical function in User Authentication Module• Access Control Module• Parser• Command Dispatcher• Query Cache Module• Optimizer• Table Manager• Table Modification Modul es• Table Maintenance Module• Status Reporting Module• Abstracted Storage Engine Interface (Table Handler)• Storage Engine Implementations (MyISAM, InnoDB, MEMORY, Berkeley DB)• Logging Module• Replication Master Module• Replication Slave Module• C lient/Server Protocol API• Low-Level Network I/O API• Core APIInteraction of the Core ModulesWhen the server is started on the command line, the Initialization Module takes control.It parses the configuration file and the command-line arguments, allocates global memory buffers, initializes global variables and structures, loads the access control tables, and performs a number of other initialization tasks. Once the initialization job is complete, the Initialization Module passes control to the Connection Manager, which starts listening for connections from clients in a loop.mysql数据库英文文献及翻译When a client connects to the database server, the Connection Manager performs a number of low-level network protocol tasks and then passes control to the Thread Manager, which in turn supplies a thread tohandle the connection (which from now on will be referred to as the Connection Thread). The Connection Thread might be created anew, or retrieved from the thread cache and called to active duty. Once the Connection Thread receives control, it first invokes the User Authentication Module.The credentials of the connecting user are verified, and the client may now issue requests.The Connection Thread passes the request data to the Command Dispatcher. Some requests, known in the MySQL code terminology as commands, can be accommodated by the Command Dispatcher directly, while more complex ones need to be redirected to another module. A typical command may request the server to run a query, change the active database, report the status, send a continuous dump of the replication updates, close the connection, or perform some other operation.In MySQL server terminology, there are two types of client requests: a query and a command. A query is anything that has to go through the parser. A command is a request that can be executed without the need to invoke the parser. We will use the term query in the context of MySQL internals. Thus, not only a SELECT but also a DELETE or INSERT in our terminology would be called a query. What we would call a query is sometimes called an SQL statement.If full query logging is enabled, the Command Dispatcher will ask the Logging Module to log the query or the command to the plain-text log prior to the dispatch. Thus in the full logging configuration all queries will be logged, even the ones that are not syntactically correct and will never be executed, immediately returning an error.The Command Dispatcher forwards queries to the Parser through the Query Cache Module. The Query Cache Module checks whether the query is of the type that can be cached, and if there exists a previously computed cached result that is still valid.In the case of a hit, the execution is short-circuited at this point, the cached result is returned to the user, and the Connection Thread receives control and is now ready to process another command. If the Query Cache Module reports a miss, the query goes to the Parser, which will make a decision on how to transfer control based on the query type.One can identify the following modules that could continue from that point: the Optimizer, the Table Modification Module, the Table Maintenance Module, the Replication Module, and the Status Reporting Module. Select queries are forwarded to the Optimizer; updates, inserts, deletes, and table-creation and schema-altering queries go to the respective Table Modification Modules; queries that check, repair, update key statistics, or defragment the table go to the Table Maintenance module;queries related to replication go to the Replication Module; and status requests go to the Status Reporting Module. There also exist a number of Table Modification Modules: Delete Module, Create Module, Update Module, Insert Module, and Alter Module.At this point, each of the modules that will receive control from the Parser passes the list of tables involved in the query to the Access Control Module and then, upon success,to the Table Manager, which opens the tables and acquires the necessary locks.Now the table operation module is ready to proceed with its specific task and will issue a number of requests to the Abstracted Storage Engine Module for low-level operations such as inserting or updating a record, retrieving the records based on a key value, or performing an operation on the table level, such as repairing it or updating the index statistics.The Abstracted Storage Engine Module will automatically translate the calls to the corresponding methods of the specific Storage Engine Module via object polymorphism.In other words, when dealing with a Storage Engine object, the caller thinks it is the caller does not need to be aware of the exact object type of the Storage Engine object.As the query or command is being processed, the corresponding module may send parts of the result set to the client as they become available. It may also send warnings or an error message. If an error message is issued, both the client and the server will understand that the query or command has failed and take the appropriate measures.The client will not accept any more result set, warning, or error message data for the given query, while the server will always transfer control to the Connection Thread after issuing an error. Note that since MySQL does not use exceptions for reasons of implementation stability and portability, all calls on all levels must be checked for errors with the appropriate transfer of control in the case of failure.If the low-level module has made a modification to the data in some way and if the binary update logging is enabled, the module will be responsible for asking the Logging Module to log the update event to the binary update log, sometimes known as the replication log, or, among MySQL developers and power users, the binlog. Once the task is completed, the execution flow returns to the Connection Thread,which performs the necessary clean-up and waits for another query or command from the client. The session continues until the client issues the Quit command.In addition to interacting with regular clients, a server may receive a command from a replication slave to continuously read its binary update log. This command will be handled by the Replication Master Module.If the server is configured as a replication slave, the Initialization Module will call the Replication Slave Module, which in turn will start two threads, called the SQL Thread and the I/O thread. They take care of propagating updates that happened on the master to the slave. It is possible for the same server to be configured as both a master and a slave.mysql数据库英文文献及翻译Network communication with a client goes through the Client/Server Protocol Module,which is responsible for packaging the data in the proper format, and depending on the connection settings, compressing it. The Client/Server Protocol Module in turn uses the Low-Level Network I/O module, which is responsible for sending and receiving the data on the socket level in a cross-platform portable way. It is also responsible for encrypting the data using the OpenSSL library calls if the connection options are set appropriately.As they perform their respective tasks, the core components of the server heavily rely on the Core API. The Core API provides a rich functionality set, which includes file I/O, memory management, string manipulation, implementations of various data structures and algorithms, and many other useful capabilities. MySQL developers are encouraged to avoid direct libc calls, and use the Core API to facilitate ports to new platforms and code optimization in the future.Writer：Sasba pacbev译文：深入理解MySQL核心技术姓名：苗月明学号：0651135MySQL的历史与架构MySQL的架构的最好的理解是从他的历史背景中去发现。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

SQL数据库英文文献及翻译The fact that you are reading a book on SQL indicates that you, somehow, need to interact with databases. SQL is a language used to do just this, so before looking at SQL itself, it is important that you understand some basic concepts about databases and database technologies.Whether you are aware of it or not, you use databases all the time. Each time you select a name from your email address book, you are using a database. If you conduct a search on an Internet search site, you are using a database. When you log into your network at work, you are validating your name and password against a database. Even when you use your ATM card at a cash machine, you are using databases for PIN number verification and balance checking.But even though we all use databases all the time, there remains much confusion over what exactly a database is. This is especially true because different people use the same database terms to mean different things. Therefore, a good place to start our study is with a list and explanation of the most important database terms.Reviewing Basic Concepts What follows is a very brief overview of some basic database concepts. It is intended to either jolt your memory if you already have some database experience, or to provide you with the absolute basics, if you are new to databases. Understanding databases is an important part of mastering SQL, and you might want to find a good book on database fundamentals to brush up on the subject if needed.What Is a Database?The term database is used in many organized fashion. The simplest way to think of it is to imagine a database as a filing cabinet. The filing cabinet is simply a physical location to store data, regardless of what that data is or how it is organizedDatabase A container (usually a file or set of files) to store organized data.Misuse Causes ConfusionPeople often use the term database to refer to the database software they are running. This is incorrect, and it is a source of much confusion. Database software is actually called the Database Management System (or DBMS). The database is the container created and manipulated via the DBMS. A database might be a file stored on a hard drive, but it might not. And for the most part this is not even significant as you never access a database directly anyway; you always use the DBMS and it accesses the database for you.TablesWhen you store information in your filing cabinet you don't just toss it in a drawer. Rather, you create files within the filing cabinet, and then you file related data in specific files.In the database world, that file is called a table. A table is a structured file that can store data of a specific type. A table might contain a list of customers, a product catalog, or any other list of information.Table A structured list of data of a specific type.The key here is that the data stored in the table is one type of data or one list. You would never store a list of customers and a list of orders in the same database table. Doing so would make subsequent retrieval and access difficult. Rather, you'd create two tables, one for each list.Every table in a database has a name that identifies it. That name is always unique—meaning no other table in that database can have the same name.Table Names What makes a table name unique is actually a combination of several things including the database name and table name. Some databases also use the name of the database owner as part of the unique name. This means that while you cannot use the same table nametwice in the same database, you definitely can reuse table names in different databases.Tables have characteristics and properties that define how data is stored in them. These include information about what data may be stored, how it is broken up, how individual pieces of information are named, and much more. This set of information that describes a table is known as a schema, and schema are used to describe specific tables within a database, as well as entire databases (and the relationship between tables in them, if any).Schema Information about database and table layout and properties.Columns and DatatypesTables are made up of columns. A column contains a particular piece of information within a table. Column A single field in a table. All tables are made up of one or more columns.The best way to understand this is to envision database tables as grids, somewhat like spreadsheets. Each column in the grid contains a particular piece of information. In a customer table, for example, one column contains the customer number, another contains the customer name, and the address, city, state, and zip are all stored in their own columnsBreaking Up DataIt is extremely important to break data into multiple columns correctly. For example, city, state, and zip should always be separate columns. By breaking these out, it becomes possible to sort or filter data by specific columns (for example, to find all customers in a particular state or in a particular city). If city and state are combined into one column, it would be extremely difficult to sort or filter by state.1657Each column in a database has an associated datatype. A datatype defines what type of data the column can contain. For example, if the column is to contain a number (perhaps the number of items in an order), the datatype would be a numeric datatype. If the column were to contain dates, text, notes, currency amounts, and so on, the appropriate datatype would be used to specify this. DatatypeA type of allowed data. Every table column has an associated datatype that restricts (or allows) specific data in that column.Datatypes restrict the type of data that can be stored in a column (for example, preventing the entry of alphabetical characters into a numeric field). Datatypes also help sort data correctly, and play an important role in optimizing disk usage. As such, special attention must be given to picking the right datatype when tables are created.Datatype CompatibilityDatatypes and their names are one of the primary sources of SQL incompatibility. While most basic datatypes are supported consistently, many more advanced datatypes are not. And worse, occasionally you'll find that the same datatype is referred to by Data in a table is stored in rows; each record saved is stored in its own row. Again, envisioning a table as a spreadsheet style grid, the vertical columns in the grid are the table columns, and the horizontal rows are the table rows. For example, a customers table might store one customer per row. The number of rows in the table is the number of records in it.Row A record in a table.Records or Rows?You may hear users refer to database records when referring to rows. For the most part, the two terms are used interchangeably, but row is technically the correct term.Primary KeysEvery row in a table should have some column (or set of columns) that uniquely identifies it. A table containing customers might use a customer number column for this purpose, whereas a table containing orders might use the order ID. An employee list table might use an employee ID or the employee social security number column.This column (or set of columns) that uniquely identifies each row in a table is called a primary key. The primary key is used to refer to a specific row. Without a primary key, updating or deleting specific rows in a table becomes extremely difficult as there is no guaranteed safe way to refer to just the rows to be affected.Always Define Primary Keys Although primary keys are not actually required, most database designers ensure that every table they create has a primary key so that future data manipulation is possible and manageable.Any column in a table can be established as the primary key, as long as it meets the following conditions:•No two rows can have the same primary key value.•Every row must have a primary key value (primary key columns may not allow NULL values). •Values in primary key columns can never be modified or updated.•Primary key values can never be reused. (If a row is deleted from the table, its primary key may not be assigned to any new rows in the future.)What Is SQL?SQL (pronounced as the letters S-Q-L or as sequel) is an abbreviation for Structured Query Language. SQL is a language designed specifically for communicating with databases.Unlike other languages (spoken languages like English, or programming languages like Java or Visual Basic), SQL is made up of very few words. This is deliberate. SQL is designed to do one thing and do it well—provide you with a simple and efficient way to read and write data from a database.Primary keys are usually defined on a single column within a table. But this is not required, and multiple columns may be used together as a primary key. When multiple columns are used, the rules listed above must apply to all columns that make up the primary key, and the values of all columns together must be unique (individual columns need not have unique values).。