1、第 - 1 - 页 共 15 页1 databaseDatabase is in accordance with the data structure to organize, storage and management of data warehouse, which arises from fifty years ago, with the dating of information technology and the development of the market, especially since the 1990s, data management is no longer
2、merely data storage and management, and transformed into user needs of the various data management way. The database has a variety of types, from the most simple storage have various data form to can be carried out mass data storage of large database systems are obtained in each aspect has extensive
3、 application. The birth of data management Databases history can be traced back to fifty years ago, when the data management is very simple. Through a lot of classification, comparison and form rendering machine running millions of punched CARDS for data processing, its operation results on paper pr
4、inted or punched card made new. While the data management is punched card for all these physical storage and handling. However, 1 9 5 1 year Remington Rand corporation (Remington Rand Inc.) an enzyme called Univac I computer launched a a second can input hundreds of recording tape drives, which has
5、caused data management revolution. 1956 IBM produce the first disk drives - the RAMAC Model 305. This drives have 50 blanks, each blanks diameter is 2 feet, can store 5 MB of data. The biggest advantage is use disk can be randomly access data, and punched CARDS and tape can order access data.Databas
6、e system appears in the 1960s the bud. When computer began to widely used in data management, the sharing of data put forward more and more high demand. The traditional file system already cannot satisfy peoples needs. Manage and share data can unify the database management system (DBMS) came into b
7、eing. The data model is the core and foundation of database system, various DBMS software are based on a data model. So usually in accordance with the characteristics of the data model and the traditional database system into mesh database, the hierarchy database and relational database three types.
8、 第 - 2 - 页 共 15 页1 Structured query language (SQL) commercial database systems require a query language that is more user friendly. In this chapter,we study SQL, themost inuential commercially marketed query language, SQL. SQL uses a combination of relational-algebra and relational-calculus construc
9、ts.Although we refer to the SQL language as a “query language,” it can do much more than just query a database. It can dene the structure of the data, modify data in the database, and specify security constraints.It is not our intention to provide a complete users guide for SQL.Rather,we present SQL
10、s fundamental constructs and concepts. Individual implementations of SQL may differ in details, or may support only a subset of the full language.2.1 BackgroundIBM developed the original version of SQL at its San Jose Research Laboratory (nowthe Almaden Research Center). IBM implemented the language
11、, originally called Sequel, as part of the System R project in the early 1970s. The Sequel language hasevolved since then, and its name has changed to SQL (Structured Query Language).Many products now support the SQL language. SQL has clearly established itself asthe standard relational-database lan
12、guage.In 1986, the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) published an SQL standard, called SQL-86.IBM published its own corporate SQL standard, the Systems Application Architecture Database Interface (SAA-SQL) in 1987. ANSI publishe
13、d an extended standard forSQL, SQL-89, in 1989. The next version of the standard was SQL-92 standard, and the most recent version is SQL:1999. The bibliographic notes provide references to these standards.Chapter 4 SQLIn this chapter, we present a survey of SQL, based mainly on the widely implemente
14、d SQL-92 standard. The SQL:1999 standard is a superset of the SQL-92 standard;we cover some features of SQL:1999 in this chapter, and provide more detailed coverage 第 - 3 - 页 共 15 页1 in Chapter 9. Many database systems support some of the new constructs in SQL:1999, although currently no database sy
15、stem supports all the new constructs. You should also be aware that some database systems do not even support all the features of SQL-92, and that many databases provide nonstandard features that we donot cover here.The SQL language has several parts: Data-denition language (DDL). The SQL DDL provid
16、es commands for dening relation schemas, deleting relations, and modifying relation schemas. Interactive data-manipulation language (DML). The SQL DML includes a query language based on both the relational algebra and the tuple relational calculus. It includes also commands to insert tuples into, de
17、lete tuples from,and modify tuples in the database. View denition.The SQL DDL includes commands for dening views. Transaction control. SQL includes commands for specifying the beginning and ending of transactions. Embedded SQL and dynamic SQL. Embedded and dynamic SQL dene how SQL statements can be
18、embedded within general-purpose programming languages, such as C, C+, Java, PL/I, Cobol, Pascal, and Fortran. Integrity.The SQL DDL includes commands for specifying integrity constraints that the data stored in the database must satisfy. Updates that violate integrity constraints are disallowed. Aut
19、horization.The SQL DDL includes commands for specifying access rights to relations and views.In this chapter, we cover the DML and the basic DDL features of SQL.Wealso briey outline embedded and dynamic SQL, including the ODBC and JDBC standards for interacting with a database from programs written
20、in the C and Java languages.SQL features supporting integrity and authorization are described in Chapter 6,while Chapter 9 outlines object-oriented extensions to SQL.The enterprise that we use in the examples in this chapter, and later chapters, is a第 - 4 - 页 共 15 页1 banking enterprise with the foll
21、owing relation schemas:Branch-schema = (branch-name, branch-city, assets)Customer-schema = (customer-name, customer-street, customer-city)Loan-schema = (loan-number, branch-name, amount)Borrower-schema = (customer-name, loan-number)Account-schema = (account-number, branch-name, balance)Depositor-sch
22、ema = (customer-name, account-number)Note that in this chapter, as elsewhere in the text, we use hyphenated names for schema, relations, and attributes for ease of reading. In actual SQL systems, however,hyphens are not valid parts of a name (they are treated as the minus operator). A simple way of
23、translating the names we use to valid SQL names is to replace all hyphens by the underscore symbol (“ ”). For example, we use branch name in place ofbranch-name.2.2 Basic StructureA relational database consists of a collection of relations, each of which is assigned a unique name. Each relation has
24、a structure similar to that presented in Chapter 3.SQL allows the use of null values to indicate that the value either is unknown or does not exist. It allows a user to specify which attributes cannot be assigned null values,as we shall discuss in Section 4.11.The basic structure of an SQL expressio
25、n consists of three clauses: select, from,and where. The select clause corresponds to the projection operation of the relational algebra. It is used to list the attributes desired in the result of a query. The from clause corresponds to the Cartesian-product operation of the relational algebra. It l
26、ists the relations to be scanned in the evaluation of the expression. The where clause corresponds to the selection predicate of the relational algebra. It consists of a predicate involving attributes of the relations that appear in the from clause.第 - 5 - 页 共 15 页1 That the term select has differen
27、t meaning in SQL than in the relational algebra is an unfortunate historical fact. We emphasize the different interpretations here to minimize potential confusion.A typical SQL query has the formselect A1,A2,.,Anfrom r1,r2,.,rmwhere PEach Ai represents an attribute, and each ri arelation. P is a pre
28、dicate. The query isequivalent to the relational-algebra expressionA1,A2,.,An(P (r1 r2 rm)If the where clause is omitted, the predicate P is true. However, unlike the result of a relational-algebra expression, the result of the SQL query may containmultiple copies of some tuples; we shall return to
29、this issue in Section 4.2.8.SQL forms the Cartesian product of the relations named in the from clause,performs a relational-algebra selection using the where clause predicate, and then projects the result onto the attributes of the select clause. In practice, SQL may convert the expression into an e
30、quivalent form that can be processed more efciently.However, we shall defer concerns about efciency to Chapters 13 and 14.In 1974, IBMs Ray Boyce and Don Chamberlin will Codd relational database 12 rule mathematical definition with simple keyword grammar expression comes out, put forward the landmar
31、k Structured Query Language (SQL) Language. SQL language features include inquiry, manipulation, definition and control, is a comprehensive, general relational database language, and at the same time, a highly the process of language, only request users do not need pointed out how do pointed out. SQ
32、L integration achieved database of all life cycle operation. SQL database provides and relations interact with the method, it can work with standard programming language. The date of the produce, SQL language became the touchstone of inspection relational database, and SQL standard every variation o
33、f guiding the relational database product development direction. However, until the 第 - 6 - 页 共 15 页1 twentieth century, the mid 1970s to the theory of relation in commercial database Oracle and SQL used in DB2. In 1986, the SQL as ANSI relational database language American standards, that same year
34、 announced the standard SQL text. Currently SQL standard has three versions. ANSIX3135 - is defined as the basic SQL Database Language - 89, “Enhancement“ SQL. A ANS89 , generally called SQL - 89. SQL - 89 defines the schema definition, data operation and the transaction. SQL - 89 and subsequent ANS
35、IX3168-1989, “Language - Embedded SQL Database, constituted the first generation of SQL standard. ANSIX3135-1992 ANS92 describes a enhancements of SQL, now called SQL - 92 standards. SQL - 92 including mode operation, dynamic creation and SQL statements dynamic executive, network environment support
36、 enhancement. Upon completion of SQL - 92 ANSI and ISO standard, they started SQL3 standards development cooperation. The main features SQL3 abstract data types support, for the new generation of object relational database provides standard. The nature of database data 1. Data integrity: database is
37、 a unit or an application field of general data processing system, he storage is to belong to enterprise and business departments, organizations and individuals set of related data. Database data from a global view, he according to certain data model organization, description and storage. Based on t
38、he structure of data between natural relation, thus can provide all the necessary access route, and data no longer deal with a certain applications, but for total organization, with a whole structural features. 2. Data sharing: database data is for many users sharing their information and the establ
39、ishment, got rid of the specific procedures restrictions and restraint. Different users can use the database according to their respective usage the data; Multiple users can also Shared database data resource, i.e., different users can also access database in the same data. Data sharing each user no
40、t only meets the requirements of information, but also meet the various users of information communication between the requirements. 第 - 7 - 页 共 15 页1 Object-oriented database Along with the development of information technology and the market, people found relational database system, while technolo
41、gy is mature, but its limitations is obvious: it can be a very good treatment of so-called “form of data“, but of dominating the more and more complex appear helpless type of data. Since the 1990s, technology has been studying and seek new database system. But in what is the development direction of
42、 the new database system, industry once is quite confused. The influence of agitation by technology at the time, for quite some time, people put a lot of energy spent on research “object-oriented database system (object oriented database)“ or simply as “OO database system“. What is worth mentioning,
43、 the United States Stonebraker professor proposed object-oriented RDS theory once favored by industry. And in Stonebraker himself Informix spend big money was then appointed technology director always. However, several years of development, spatio-temporal object-oriented relational database system
44、product market development situation is not good. Theoretically perfect sex didnt bring market warm response. The main reason of success, the main design thought database products with new database system is an attempt to replace the existing database system. This for many has been using database sy
45、stem for years and accumulated the massive job data, especially big customer for customers, is unable to withstand the conversion between old and new data that huge workload and big spending. In addition, object-oriented RDS system makes query language extremely complex, so whether database developm
46、ent businessman or application customers depending on the complicated application technology to be a dangerous road. Basic structure The basic structure of database, reflects the three levels of observation database of three different Angle. (1) physical data layer. It is the most lining, is databas
47、e on physical storage equipment actually stored data collection. These data are raw data, the object, the user is 第 - 8 - 页 共 15 页1 processed by internal model describing the throne of handled the instructions of string, character and word. (2) conceptual data layer. It is a layer of database, is am
48、ong the whole logic said. Database Points out the logic of each data definition and the logical connection between data collection of storage and record, is. It is related to the database all objects logical relationship, not their physical condition, is under the database administrator concept of d
49、atabase. (3) logical data layer. It is the user sees and use the database, says one or some specific users use collections of data, namely the logical record set. Database different levels is the connection between conversion by mapping. Main features (1) to implement the data sharing. Data sharing contains all users can also access database data, including the user can use all sorts of ways to use the database through interfaces, and provide data sharing. (2) reduce data redundancy. Compared with the file system, because the database to achieve dat
