Database Connections Databases The simplest databases can be
Database Connections
Databases The simplest databases can be as simple as MS Access or MS Excel Use them for small sites Large sites may require large industrialstrength databases. ORACLE is the 2 nd largest software company in the world – all based on Databases
Database Terms: TABLE A database consists of many TABLES. A table is analogous to a spreadsheet. For an e-commerce Web site database this may consist of tables such as CUSTOMERS, PRODUCTS, ORDERS, etc.
Database Terms: Record A record is a “row” of data. It all pertains to the same entity. EXAMPLE: A customer’s name and address.
Database Terms: Field A field is a “column” of data. EXAMPLE: First. Name
Database Terms: Primary Key The PRIMARY KEY is the column in the database that is unique for each and every row. Even something as basic and individual as an SSN may not be a good unique identifier. What if someone changes it to protect their identity, or the number is recycled, or they are not US citizens or residents.
Databases Web Databases – CSC 122 Teaches more about Databases
SQL Making Database Queries
SQL “S-Q-L”, or “Sequel” is the most common language to access databases. It comes in many dialects, but the most basic operations require only simple SQL statements. You program the SQL queries into your scripts.
SQL Example 1 - Inserting INSERT INTO Customers (First. Name, Last. Name) VALUES (“Fred”, “Jones”)
SQL Example 2 -- Updating UPDATE Customers SET First. Name = “Freddy” WHERE Cust. ID = 7
SQL Example -- Selecting SELECT First. Name, Last. Name FROM Customers WHERE Last. Name = Jones Use this statement to create a RECORDSET A RECORDSET is a subset of the database that has been copied onto the server for immediate use.
Recordset Even if the record included more than just the first name and last name, the recordset only has those columns. Freddy Suzie Marky Mark Joanie Jones
SQL SELECT * FROM WHERE UPDATE INSERT INTO VALUES SET When choosing a recordset A wildcard From what table? Conditions To update an existing record To add a record Into what table What values SET Rank = “Sergeant”
SQL Operators are conventional. Here are some: = > < <> <= >=
Open Database Connectivity ODBC
How do we get data from a database to a web page? 5 layers 1 Scripts – ASP, PHP 4 Driver Layer 5 Database
5 layers 1 2 3 4 5 Scripts – ASP, PHP ADO (Active Data Objects Layer) OLE DB Layer Driver Layer Database
Important to remember n ODBC is our connection n Open Data Base Connectivity n Establish Database connections through the ODBC Control Panel Applet
- Slides: 19