Synapseindia ASP DOTNET and PHP Development I. II.
III.
Databases in the Abstract Creating Databases using Relational DataBase Management Systems (RDBMSs) Databases and Web Interfaces
I. Databases in the Abstract
A. B. C. D.
Definition Normalization Structure Language
I. A. Definition ●
A database is an organized collection of data whose content must be quickly and easily – – –
●
Accessed Managed Updated
A relational database is one whose data are split up into tables, sometimes called relations
I. B. Normalization
(read logical organization) ●
First Normal Form (1NF) –
●
Second Normal Form (2NF) – –
●
All attributes are single valued & non-repeating Must be 1NF & must have primary key Each non-primary key attribute must be functionally dependent on primary key
Third Normal form (3NF) – –
Must be 2NF Each non-primary key attribute must be dependent only on primary key
I. C. 1. Tables (Relations) â—? â—?
Each column constitutes an attribute Each row constitutes a record or tuple Attribute 1 (column 1) Record 1 (tuple 1) Record 2 (tuple 2)
Attribute 2 (column 2)
I. C. 2. Keys ●
Primary –
– ●
An attribute or group of attributes which uniquely identifies each record in a table May not be a Null value
Foreign –
used primarily for enforcing referential integrity, but also for establishing relationships between the two tables
I. C. 3. Relationships ● ● ●
One-to-one (1-to-1) One-to-many (1-to-M or 1-to-∞) Many-to-Many (M-to-M or ∞-to-∞)
I. D. Structured Query Language (SQL) ● ●
●
Pronounce “Sequel” or “Ess Que Ell” Industry standard language of (Relational) Databases Allows for complete – – –
Table Creation, Deletion, Editing Data extraction (Queries) Database management & administration
II. Creating Databases using RDBMSs A. Microsoft Access – – – –
Creating Tables Entering, Importing, Editing, & Viewing Data Defining Relationships Constructing Queries
A. MySQL … (Documentation) – –
Where to put it (servers at UVa) Ditto MS Access
A. Others –
mSQL, PostGreSQL, Oracle, DB2, Informix, Sybase, Empress, Adabas, ….
III. Databases and Web Interfaces
(What you need to get started) A. Requirements for a Database Web Interface B. Where to Put Your Database and Scripts C. Server-Side Scripting Languages • • • •
ASP Cold Fusion Perl PHP
III. A. Requirements for a Database Web Interface ● ● ●
●
Your database (Access, MySQL) A Web server with appropriate RDBMS A way of connecting the two (Common Gateway Interface – CGI – scripts and SQL) Security concerns
III. B. Where to Put Your Database and Scripts ●
Academic side of UVa –
Access ●
–
MySQL ● ●
●
ESERVICES es-web1 (web.virginia.edu) MySQL server (dbm1.itc.virginia.edu) Home directory (www.people – accessible through blue.unix), faculty, curry, avery, minerva – www, jm.acs – www.itc(.virginia.edu)
Medical side of UVa –
See the UVa Health System’s Web Development Center
Active Server Pages (ASP) •
When a browser calls an ASP document, the ASP Server reads the .asp document and
1. Substitutes appropriate files for the (server-side) include statements Runs the ASP code (Visual Basic Script – see the Tutorial and Language Reference, …) 2. Returns the resulting HTML code to the browser ●
Example (code, copy of database)
ASP Key Points (1) ●
● ●
ASP code enclosed in: <% VBScript code %> Everything outside is HTML The result of the combined HTML and ASP code must be a “standard” HTML document, e.g.: –
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Final//EN"> <html> <head> <title>Miracle Drug Study</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-88591"> <meta name="Description" content=""><meta name="Keywords" content=""> <link rel=STYLESHEET type="text/css" href=""> </head>
ASP Key Points (2) ●
Connect with database: –
Create connection object: ●
–
Open connection: ●
●
set conn = Server.CreateObject("ADODB.Connection") conn.open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=f:\web\database\rescomp\study.mdb")
Submit a (read-only) Query: –
Generate SQL statement: ●
–
SQL = "SELECT FirstName, LastName, DOB, Gender FROM Patients WHERE Gender = '" & Gender & "' ORDER BY FirstName DESC"
set Patients = conn.execute(SQL)
ASP Key Points (3) ●
Move through the data records: –
●
do while NOT Patients.eof Name = Patients(0) & " " & Patients(1) Patients.MoveNext loop
Add to or edit table: –
Create and open Record Set object: ●
set RS = Server.CreateObject("ADODB.Recordset") RS.Open “table name", conn, , adLockOptimistic, adCmdTable (where adLockOptimistic = 3, adCmdTable = 2)
ASP Key Points (4) ●
Add to or edit table (continued): – Create new record, Edit, & Update: ●
–
RS.AddNew RS(“Dosage”) = 200 RS.Update
Or Find desired record, Edit, & Update : ●
do while NOT RS.eof if RS(“ID”) = 7 then RS(“Dosage”) = 200 RS.Update else RS.MoveNext end if loop
ASP Key Points (5) ●
Clean up (free server resources) when done: –
Queries: ●
–
Record Sets: ●
–
Patients.Close set Patients = nothing RS.Close set RS = nothing
The Connection: ●
conn.close set conn = nothing
ASP Security ●
●
Apart from various Internet Information Services (IIS – Window’s Web service) security holes (for viruses and worms), security is quite good. Use https:// if you want to protect content over the internet – provides Secure Socket Layer (SSL) security