CSC 385 Chapter 22, Fall 2003

V. Sawma, Internet Computing 1 Chapter 22 - SQL, MySQL, DBI and ADO Outline 22.1 Introduction 22.2 Relational Database Model 22.3 Relational Database Overview 22.4 Structured Query Language 22.4.1 Basic SELECT Query 22.4.2 WHERE Clause 22.4.3 GROUP BY Clause 22.4.4 ORDER BY Clause 22.4.5 Merging Data from Multiple Tables 22.4.6 Inserting a Record 22.4.7 Updating a Record 22.4.8 DELETE FROM Statement 22.4.9 TitleAuthor Query from Books.mdb 22.5 MySQL V. Sawma, Internet Computing 2 Chapter 22 - SQL, MySQL, DBI and ADO Outline 22.6 Introduction to DBI 22.6.1 Perl Database Interface 22.6.2 Python DB-API 22.6.3 PHP dbx module 22.7 ActiveX Data Objects (ADO) 22.8 Internet and World Wide Web Resources V. Sawma, Internet Computing 3 22.1 Introduction • Database – Integrated collection of data • Database management system (DBMS) – Provides mechanisms for storing and organizing data – Allows users to access and store data without addressing internal representation of databases • Relational databases – Consist of data corresponding to one another – Most popular database systems in use – Uses Structured Query Language (SQL) to create queries – Examples: Oracle, MS SQL Server, MySQL, Informix V. Sawma, Internet Computing 4 22.2 Relational Database Model • Relational database model – Logical representation of data allowing users to consider relationships between data – Consists of tables • Following figure illustrates Employee table – Might exist in personnel system – Table row called record – Table column called field – Number field is primary key • Contains unique data that cannot be duplicated • Identifies the record • Examples: social security number, employee ID number, etc. V. Sawma, Internet Computing 5 22.2 Relational Database Model Number Name Department Salary Location 23603 Jones 413 1100 New Jersey 24568 Kerwin 413 2000 New Jersey 34589 Larson 642 1800 Los Angeles 35761 Myers 611 1400 Orlando 47132 Neumann 413 9000 New Jersey 78321 Stephens 611 8500 Orlando Row/Record Column/Field Primary key Fig. 22.1 Relational database structure. V. Sawma, Internet Computing 6 22.2 Relational Database Model • SQL statements – Use to obtain table subsets – Complete set of keywords enable programmers to define complex queries • Results of query called result sets (or record sets) – Following table shows results of SQL query (Fig. 22.2) • Provides geographical location of several departments Department Location 413 New Jersey 642 Los Angeles 611 Orlando Fig. 22.2 Result set formed by selecting data from a table. V. Sawma, Internet Computing 7 22.3 Relational Database Overview • Overview SQL using Books.mdb database – Consists of four tables • Authors, Publishers, AuthorISBN and Tables • Primary key fields in italics • Authors table (Figs. 22.3 and 22.4) – Consists of four fields • Unique ID number, first name, last name and year of birth – Contains null value for YearBorn field • Not primary key, so can contain null values • FirstName and LastName can contain null values also V. Sawma, Internet Computing 8 22.3 Relational Database Overview Field Description AuthorID An integer representing the author’s ID number in the database. This field is the primary key field for this table. FirstName A string representing the author’s first name. LastName A string representing the author’s last name. YearBorn A string representing the author’s year of birth. Fig. 22.3 Authors table from Books.mdb. AuthorID FirstName LastName YearBorn 1 Harvey Deitel 1946 2 Paul Deitel 1968 3 Tem Nieto 1969 4 Kate Steinbuhler Fig. 22.4 Data from the Authors table of Books.mdb. V. Sawma, Internet Computing 9 22.3 Relational Database Overview • Publishers table (Figs. 22.5 and 22.6) – Consists of two fields • Unique ID and publisher name Field Description PublisherID An integer representing the publisher’s ID number in the database. This is the primary key field for this table. PublisherName A string representing the abbreviated name for the publisher. Fig. 22.5 Publishers table from Books.mdb. PublisherID PublisherName 1 Prentice Hall 2 Prentice Hall PTR Fig. 22.6 Data from the Publishers table of Books.mdb. V. Sawma, Internet Computing 10 22.3 Relational Database Overview • Titles table (Figs. 22.7 and 22.8) – Consists of six fields • ISBN number, title, edition number, year published, book description and publisher ID number Field Description ISBN A string representing the ISBN number of the book. Title A string representing the title of the book. EditionNumber A string representing the edition number of the book. YearPublished A string representing the publication year. Description A string representing the description of the book. PublisherID An integer representing the publisher’s ID number. This value must correspond to an ID number in the Publishers table. Fig. 22.7 Titles table from Books.mdb. V. Sawma, Internet Computing 11 22.3 Relational Database Overview ISBN Title Edition Number Year Published Publisher ID 0-13-012507-5 Java How to Program 3 1999 1 0-13-013249-7 Getting Started with Visual C++ 6 with an Introduction to MFC 1 1999 1 0-13-016143-8 Internet and World Wide Web How to Program 1 1999 1 0-13-020522-2 Visual Basic 6 How to Program Instructor's Manual with Solution Disk 1 1999 1 0-13-028417-3 XML How to Program 1 2001 1 0-13-089571-1 C++ How to Program 3 2001 1 0-13-089572-5 C How to Program 3 2001 1 0-13-271974-6 Java Multimedia Cyber Classroom 1 1996 2 0-13-456955-5 Visual Basic 6 How to Program 1 1998 1 0-13-899394-7 Java How to Program 2 1997 1 Fig. 22.8 Portion of the data from the Titles table of Books.mdb. V. Sawma, Internet Computing 12 22.3 Relational Database Overview • AuthorISBN table (Figs. 22.9 and 22.10) – Consists of two fields • ISBN number and author ID number – Links names of authors with respective book titles V. Sawma, Internet Computing 13 22.3 Relational Database Overview Field Description ISBN A string representing the ISBN number for a book. The ISBN number in this field also must appear in the Titles table. AuthorID An integer representing the author’s ID number, which allows the database to connect each book to a specific author. The ID number in this field must also appear in the Authors table. Fig. 22.9 AuthorISBN table from Books.mdb. ISBN AuthorID 0-13-010671-2 1 0-13-010671-2 2 0-13-012507-5 1 0-13-013249-7 2 0-13-016143-8 2 0-13-020522-2 3 0-13-032364-0 2 0-13-032364-0 4 0-13-082928-5 3 Fig. 22.10 Portion of the data from the AuthorISBN table of Books.mdb. V. Sawma, Internet Computing 14 22.3 Relational Database Overview • Microsoft Access diagram (Fig. 22.11) – Illustrates relationships between tables in database – Lines represent table relationships – One-to-many relationship • Example: line between Publishers and Titles tables • Single publisher can have many books in Titles table V. Sawma, Internet Computing 15 22.3 Relational Database Overview Fig. 22.11 Table relationships in Books.mdb. V. Sawma, Internet Computing 16 22.3 Relational Database Overview • Rule of Entity Integrity – Every record must have value in primary key field – Primary key values must be unique • Foreign key field (or constraints) – References primary key field in another table – Specified when creating tables – Maintains Rule of Referential Integrity • Every foreign key field value must appear in another table’s primary key field • Example: PublisherID field in Titles table – Enables information from multiple tables to be joined for analysis