Database Systems - Part 13

System R itself was never produced commercially, but directly ledto the development of SQL/DS (1981 running under DOS/VE OS, aVM version followed in 1982) which was IBM’s first commercialrelational DBMS.• IBM however, did not produce the first commercial implementationof a relational DBMS. That honor went to Oracle (RelationalSoftware) in 1979.• Today, the relational DBMS system of virtually all vendors is basedon SQL.• Each vendor provides all the standard features of SQL. Mostvendors also provide additional features of their own, calledextensions to standard SQL. These extensions lead to portabilityissues when moving SQL-based applications across variousRDBMS. Vendors attempt to distinguish their SQL...
Nội dung trích xuất từ tài liệu:
Database Systems - Part 13 COP 4710: Database Systems Spring 2004 Introduction to SQL BÀI 13, 2 ngày Instructor : Mark Llewellyn markl@cs.ucf.edu CC1 211, 823-2790 http://www.cs.ucf.edu/courses/cop4710/spr2004 School of Electrical Engineering and Computer Science University of Central FloridaCOP 4710: Database Systems (Day 17) Page 1 Mark Llewellyn History of SQL• SQL, pronounced “S-Q-L” by some and “sequel” by others (mostly old-timers), has become the de facto standard language for creating and querying relational databases.• It has been accepted by ANSI (American National Standards Institute) and ISO (International Standards Organization) as well as being a FIPS (Federal Information Processing Standard).• Between 1974 and 1979, workers at the IBM Research Laboratory in San Jose, California undertook the development of System R. This was shortly after Codd’s classic paper defining the relational database was published. The goal of the System R project was to demonstrate the feasibility of implementing the relational model in a DBMS. They used a language named SEQUEL (Structured English QUEry Language), which was a descendent of SQUARE (Specifying QUeries As Relational Expressions), both of which were developed at IBM, San Jose.• SEQUEL was renamed to SQL during this project. COP 4710: Database Systems (Day 17) Page 2 Mark Llewellyn History of SQL (cont.)• System R itself was never produced commercially, but directly led to the development of SQL/DS (1981 running under DOS/VE OS, a VM version followed in 1982) which was IBM’s first commercial relational DBMS.• IBM however, did not produce the first commercial implementation of a relational DBMS. That honor went to Oracle (Relational Software) in 1979.• Today, the relational DBMS system of virtually all vendors is based on SQL.• Each vendor provides all the standard features of SQL. Most vendors also provide additional features of their own, called extensions to standard SQL. These extensions lead to portability issues when moving SQL-based applications across various RDBMS. Vendors attempt to distinguish their SQL versions through these extensions. COP 4710: Database Systems (Day 17) Page 3 Mark Llewellyn History of SQL (cont.)• The current version of ANSI standard for SQL is SQL-99 (also referred to as SQL3). This standard has also been accepted by ISO.• Although many different extensions of SQL exist, we’ll look at the core SQL that will be found on any RDBMS that you will encounter. Whether you use Oracle, Microsoft SQL Server, IBM’s DB2, Microsoft Access, MySQL, or any other well-established RDBMS, you’ll be able to get up to speed on that system with the information in this set of notes. COP 4710: Database Systems (Day 17) Page 4 Mark Llewellyn SQL• SQL is a complete relational database language in the sense that it contains both a data definition language (DDL) and a data manipulation language (DML).• We’ll examine components of both parts of SQL.• If you use Microsoft Access, for example, you’ll need to know less about the DDL side of SQL than you will if you use Oracle 9i or MySQL.• The table on the following pages summarize the commands in the DDL portion of SQL. The entries in the table do not correspond to the order in which you will use the commands, but simply give a quick summary of those available. The table does not contain a complete listing of the commands in the DDL portion of SQL. COP 4710: Database Systems (Day 17) Page 5 Mark Llewellyn Summary of SQL DDL Commands Command or Option DescriptionCREATE SCHEMA AUTHORIZATION Creates a database schemaCREATE TABLE Creates a new table in the user’s DB schema NOT NULL Constraint that ensures a column will not have null values UNIQUE Constraint that ensures a column will not have duplicate values PRIMARY KEY Defines a primary key for a table FOREIGN KEY Defines a foreign key for a table DEFAULT Defines a default value for a column (when no value is given) CHECK Constraint used to validate data in a columnCREATE INDEX Creates an index for a tableCREATE VIEW Creates a dynamic subset of rows/columns from 1 or more tablesALTER TABLE Modifies a table’s definition: adds/deletes/updates attributes or constraintsDROP TABLE Permanently deletes a table (and thus its data) from the DB schemaDROP INDEX Permanently deletes an indexDROP VIEW Permanently deletes a view COP 4710: Database Systems (Day 17) Page 6 Mark Llewellyn The DDL Component Of SQL• Before you can use a RDMS two tasks must be completed: (1) create the database structure, and (2) create the tables that will hold the end-user data.• Completion of the first task involves the construction of the physical files that hold the database. The RDBMS will automatically create the data dictionary tables and create a default database administrator (DBA). ...