1 / 16

ECMM6018 Enterprise Networking For Electronic Commerce Tutorial 6

This tutorial introduces different types of databases, explains SQL syntax, and provides an overview of Database Management Systems (DBMS). It also includes the usage of databases in CGI/Perl scripts.

phamilton
Download Presentation

ECMM6018 Enterprise Networking For Electronic Commerce Tutorial 6

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ECMM6018 Enterprise Networking For Electronic CommerceTutorial 6 CGI/Perl and databases

  2. Databases • Defn: A database is a collection of data that is organized so that its contents can easily be accessed, managed, and updated.[1] • Different types of databases • i.Relational Database: Stores data in the form of related tables • ii.Object Oriented Programming Database: This has the data defined in objects classes and sub classes. • Some of the Databases in use: IBM's DB2, Microsoft's Access, MySQL, Postgresql and products from Oracle, Sybase, and Computer Associates. • [1] http://searchdatabase.techtarget.com/sDefinition/0,,sid13_gci211895,00.html

  3. Databases Continued • For DBMS a record is collection of fields each field containing one entry • In databases records are also known as tuples • Recordset – collection of records returned which match the query entered.

  4. Figure 1: Showing table in database

  5. Figure2: Showing recordset returned for a query

  6. Structured Query Language (SQL) • A programming language, that allows access to a database. • SQL Data Manipulation Language (DML) • SQL Data Definition Language (DDL)

  7. SQL Data Manipulation Language (DML) • This allows us to either extract, insert, delete or update data in the database • SELECT - extracts data from a database table • UPDATE - updates data in a database table • DELETE - deletes data from a database table • INSERT INTO - inserts new data into a database table

  8. SELECT SYNTAX • SELECT column_name(s) FROM table_name • WHERE clause: This allows us to conditionally select data from a table based upon some criteria • SELECT column FROM table WHERE column operator value e.g. Select * from nur

  9. UPDATE SYNTAX • UPDATE table_name SET column_name = new_value WHERE column_name = some_value e.g. update #period# set period ='#period#', Primary='#form.Primary#',Last1='#form.Last1#', First1'#form.First1#',Last2='#form.Last2#',First2='#form.First2#',editor_last1='#editor_last1# where key=#form.key#

  10. DELETE and INSERT SYNTAX • DELETE FROM table_name WHERE column_name = some_value e.g. delete from nur where l_name='zincir' • INSERT INTO table_name VALUES (value1, value2,....) e.g. insert into nur (l_name,f_name) values ('lastname1','firstname1')

  11. SQL Data Definition Language (DDL) • This allows us to create or delete tables in the database. • CREATE TABLE - creates a new database table • ALTER TABLE - alters (changes) a database table • DROP TABLE - deletes a database table

  12. DDL SYNTAX • CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, ....... ) • ALTER TABLE table_name ADD column_name datatype • ALTER TABLE table_name DROP COLUMN column_name

  13. Database connection strings • ODBC (Object Database Connectivity) – An application interface for accessing a database. Makes it possible to assess data from any application irrelevant of the underlying DBMS used. It carries out this task by inserting a middle layer known as a driver between the application and the DBMS. The role of this is to convert application's data queries into commands that the DBMS understands.[1] It is also language independent. • JDBC [1] http://www.webopedia.com/TERM/O/ODBC.html

  14. Connection String in Perl • $dbh=DBI->connect('dbi:Oracle' ,$username,$password); • DBI = Database Independent Module, allows for access to a supported SQL database

  15. Using a database through the DBI module • Connect to the database • Prepare the SQL statement and return a statement handle • Using the Statement handle, Execute the SQL statement • If the SQL statement is a SELECT, use the Statement handle to get the SELECTed records • Close the statement handle • Close the connection

  16. Useful Links • http://www.suite101.com/article.cfm/perl/67813 • http://www.saturn5.com/~jwb/dbi-examples.html

More Related