Slides from Lecture 14 - Courses - University of California, Berkeley
Download
Report
Transcript Slides from Lecture 14 - Courses - University of California, Berkeley
Fourth Generation Languages and
PHP
University of California, Berkeley
School of Information Management
and Systems
SIMS 257: Database Management
IS 257 - Fall 2002
2002.10.31- SLIDE 1
Lecture Outline
• Review: Security and Integrity
• Fourth Generation Languages
• More on PHP and Web Interfaces
IS 257 - Fall 2002
2002.10.31- SLIDE 2
Lecture Outline
• Review: Security and Integrity
• Fourth Generation Languages
• More on PHP and Web Interfaces
IS 257 - Fall 2002
2002.10.31- SLIDE 3
Security and Integrity Functions in Database
Administration
• Data Integrity
• Security Management
• Backup and Recovery
IS 257 - Fall 2002
2002.10.31- SLIDE 4
Security and Integrity Functions in Database
Administration
• Data Integrity
• Security Management
• Backup and Recovery
IS 257 - Fall 2002
2002.10.31- SLIDE 5
Integrity Constraints (review)
• The constraints we wish to impose in order
to protect the database from becoming
inconsistent.
• Five types
– Required data
– attribute domain constraints
– entity integrity
– referential integrity
– enterprise constraints
IS 257 - Fall 2002
2002.10.31- SLIDE 6
Data and Domain Integrity
• This is now increasing handled by the database.
In Oracle, for example, when defining a table
you can specify:
• CREATE TABLE table-name (
attr2 attr-type NOT NULL, forbids NULL values
attrN attr-type CHECK (attrN = UPPER(attrN)
verifies that the data meets certain criteria
attrO attr-type DEFAULT default_value);
Supplies default values
IS 257 - Fall 2002
2002.10.31- SLIDE 7
Referential Integrity
• Ensures that dependent relationships in
the data are maintained. In Oracle, for
example:
• CREATE TABLE table-name (
attr1 attr-type PRIMARY KEY,
attr2 attr-type NOT NULL,
…, attrM attr-type REFERENCES
owner.tablename(attrname) ON DELETE
CASCADE, …
IS 257 - Fall 2002
2002.10.31- SLIDE 8
Concurrency Control
• The goal is to support access by multiple
users to the same data, at the same time
• It must assure that the transactions are
serializable and that they are isolated
• It is intended to handle several problems
in an uncontrolled system
• Specifically:
– Lost updates
– Inconsistent data states during access
– Uncompleted (or committed) changes to data
IS 257 - Fall 2002
2002.10.31- SLIDE 9
Transaction Control in ORACLE
• Transactions are sequences of SQL statements
that ORACLE treats as a unit
– From the user’s point of view a private copy of the
database is created for the duration of the transaction
• Transactions are started with SET
TRANSACTION, followed by the SQL
statements
• Any changes made by the SQL are made
permanent by COMMIT
• Part or all of a transaction can be undone using
ROLLBACK
IS 257 - Fall 2002
2002.10.31- SLIDE 10
Transactions in ORACLE
•
•
•
•
COMMIT;
SET TRANSACTION READ ONLY;
SELECT NAME, ADDRESS FROM WORKERS;
SELECT MANAGER, ADDRESS FROM
PLACES;
• COMMIT;
• Freezes the data for the user in both tables before either
select retrieves any rows, so that changes that occur
concurrently will not show up
• Commits before and after ensure any uncompleted
transactions are finish, and then release the frozen data
when done
IS 257 - Fall 2002
2002.10.31- SLIDE 11
Transactions in ORACLE
• Savepoints are places in a transaction that you
may ROLLBACK to (called checkpoints in other
DBMS)
–
–
–
–
–
–
–
–
SET TRANACTION…;
SAVEPOINT ALPHA;
SQL STATEMENTS…
IF (CONDITION) THEN ROLLBACK TO SAVEPOINT
ALPHA;
SAVEPOINT BETA;
SQL STATEMENTS…
IF …;
COMMIT;
IS 257 - Fall 2002
2002.10.31- SLIDE 12
Database Security
• Views or restricted subschemas
• Authorization rules to identify users and
the actions they can perform
• User-defined procedures (and rule
systems) to define additional constraints or
limitations in using the database
• Encryption to encode sensitive data
• Authentication schemes to positively
identify a person attempting to gain access
to the database
IS 257 - Fall 2002
2002.10.31- SLIDE 13
Views
• A subset of the database presented to
some set of users
– SQL:
CREATE VIEW viewname AS SELECT
field1, field2, field3,…, FROM table1, table2
WHERE <where clause>;
– Note: “queries” in Access function as views
IS 257 - Fall 2002
2002.10.31- SLIDE 14
Restricted Views
• Main relation has the form:
Name
C_name
Dept
C_dept
Prof
C_prof
TC
J Smith
S
Dept1
S
Cryptography
TS
TS
M Doe
U
Dept2
S
IT Security
S
S
R Jones
U
Dept3
U
Secretary
U
U
U = unclassified : S = Secret : TS = Top Secret
IS 257 - Fall 2002
2002.10.31- SLIDE 15
Restricted Views
S-view of the data
NAME
J Smith
M Doe
R Jones
Dept
Dept1
Dept2
Dept3
Prof
--IT Security
Secretary
Dept
--Dept3
Prof
--Secretary
U-view of the data
NAME
M Doe
R Jones
IS 257 - Fall 2002
2002.10.31- SLIDE 16
Disaster Recovery Planning
Risk
Analysis
Recovery
Strategies
Plan
Maintenance
Testing and
Training
Budget &
Implement
Procedures
Development
From Toigo “Disaster Recovery Planning”
IS 257 - Fall 2002
2002.10.31- SLIDE 17
Threats to Assets and Functions
•
•
•
•
•
Water
Fire
Power Failure
Mechanical breakdown or software failure
Accidental or deliberate destruction of
hardware or software
– By hackers, disgruntled employees, industrial
saboteurs, terrorists, or others
IS 257 - Fall 2002
2002.10.31- SLIDE 18
Threats
• Between 1967 and 1978 fire and water
damage accounted for 62% of all data
processing disasters in the U.S.
• The water damage was sometimes
caused by fighting fires
• More recently improvements in fire
suppression (e.g., Halon) for DP centers
has meant that water is the primary
danger to DP centers
IS 257 - Fall 2002
2002.10.31- SLIDE 19
Lecture Outline
• Review: Security and Integrity
• Fourth Generation Languages
• More on PHP and Web Interfaces
IS 257 - Fall 2002
2002.10.31- SLIDE 20
Fourth Generation Languages
•
•
•
•
1st Generation -- Machine Language
2nd Generation -- Assembly Languages
3rd Generation -- High-Level Languages
4th Generation -- Non-Procedural
Languages
• 5th Generation -- ?? Knowledge-based ??
Natural Language ??
• Where do Object-Oriented Languages
fit??
IS 257 - Fall 2002
2002.10.31- SLIDE 21
Chauffeurs
• In the early days of the US car industry,
production volumes were growing fast, and a
well-known sociologist was asked to predict the
total number of automobiles that would ever be
manufactured. After a great deal of study, the
sociologist reported that no more than 2 million
would be manufactured in the life cycle of the
car. If the car lasted ten years on average, the
maximum annual production would never
exceed 200,000. This conclusion was based on
the much-researched figure that no more than 2
million people would be willing to serve as
chauffeurs.
From James Martin - Fourth Generation Languages
IS 257 - Fall 2002
2002.10.31- SLIDE 22
Fourth Generation Languages
• In the database environment these are used for
creation of database applications
• To speed up the application building process
• To make applications easy and quick to change
• To minimize debugging problems
• To generate bug-free code from high-level
expressions of requirement
• To make languages user-friendly so that “endusers” can solve their own problems and put
computers to work.
IS 257 - Fall 2002
2002.10.31- SLIDE 23
Basic Principles of 4GLs
• The Principle of Minimum Work
• The Principle of Minimum Skill
• The Principle of avoiding alien syntax and
mnemonics
• The Principle of Minimum Time
• The Principle of Minimum errors
• The Principle of Minumum Maintenance
• The Principle of Maximum Results
From James Martin - Fourth Generation Languages
IS 257 - Fall 2002
2002.10.31- SLIDE 24
Properties of 4GLs
• User Friendly
• A nonprofessional programmer can obtain
results with it
• It employs the database management
system directly
• Programs for most applications can be
created with 10 times fewer instructions
than in a Third Generation Language
IS 257 - Fall 2002
2002.10.31- SLIDE 25
More Properties of 4GLs
• Non procedural code is used wherever
possible
• It make intelligent default assumptions
about what the user wants wherever
possible
• It is designed for online operation
• It enforces or encourages structured code
• It makes it easy to understand and
maintain another person’s code
IS 257 - Fall 2002
2002.10.31- SLIDE 26
More Properties of 4GLs
• Non-DP users can learn a subset of the
language in a short course
• It is designed for easy debugging
• Prototypes can be created and modified
quickly
• Results can be obtained in an order of
magnitude less time than with a 3GL for
most applications
IS 257 - Fall 2002
2002.10.31- SLIDE 27
Selection Criteria for 4GLs
• Is it intended for routine computing of ad
hoc decision making
• Is it intended for end users or DP
professionals? (many 4GLs are
appropriate for both)
• Does it require the skills of a programmer,
or can an analyst who does not program in
a 3GL use it.
IS 257 - Fall 2002
2002.10.31- SLIDE 28
Selection Criteria for 4GLs
• Which of the following features does it provide?
–
–
–
–
–
–
Simple queries
Simple queries and updates
Complex queries
Complex queries and updates
The ability to create a database quickly
Intelligent database operations, where the change of
one value in the database causes other operations to
occur automatically, such as validity checks, cross
references, and the updating of related values.
IS 257 - Fall 2002
2002.10.31- SLIDE 29
Selection Criteria for 4GLs
• Which of the following features does it
provide?(cont)
– Generation of data-entry screens for key-entry
operators (with validity checks)
– Generation of data-update screens for key-entry
operators (with validity checks)
– A procedural language giving full programming
capability
– Graphics techniques for application design
– Spreadsheet manipulation
– Multidimensional matrix manipulation
– Report generation
– Graphics generation
IS 257 - Fall 2002
2002.10.31- SLIDE 30
Selection Criteria for 4GLs
• Which of the following features does it provide?(cont)
–
–
–
–
–
–
–
Graphics manipulation
Decision support for what-if questions
Mathematical analysis tools
Financial analysis tools
Other decision-support tools
Text manipulation
Electronic Mailbox
• Is it on-line or off-line?
• Does it run on mainframes, minicomputers or personal
computers?
• Can it access mainframe or remote databases
• Is it genuinely easy to use
• Can results be obtained with it very quickly?
IS 257 - Fall 2002
2002.10.31- SLIDE 31
Components of a 4GL
Application
Parameters
Interpreter
IS 257 - Fall 2002
for building routine applications…
Data
Specification
Report
Specification
Screen
Specification
Rules
Specification
Procedural
facility
Feedback
Testing
tools/debugger
Optimizing
compiler
2002.10.31- SLIDE 32
5GLs -- Natural Language
• Possibilities
• Problems
IS 257 - Fall 2002
2002.10.31- SLIDE 33
Natural Language
• Advantages of using NL
– It encourages untrained users to start
– It encourages upper-management use of
computers
– It reduces the time taken learning complex
syntax
– It lessens the frustration, bewilderment and
anger caused by BAD COMMAND responses
– It is likely to extend greatly the usage of
computers
James Martin, Fourth Generation Languages, 1985
IS 257 - Fall 2002
2002.10.31- SLIDE 34
Natural Language
Disadvantages of using NL
• It lacks precision
• It is not good for
expressing precise
and complex logic
• It is not good for
expressing neat
structures
• It encourages
semantic overshoot
Appropriate response to the
disadvantage
• It should be combined
with other dialogue
contructs that aid in
the representation of
precise logic and
structures
James Martin, Fourth Generation Languages, 1985
IS 257 - Fall 2002
2002.10.31- SLIDE 35
Natural Language
Disadvantages of using NL
• It takes substantial
time to key in
sentences
• Ambiguities are
possible
• Substantial
processing is needed
Appropriate response to the
disadvantage
• Sentences and words can
be abbreviated
• Speech input as well as
typed input will be used
• The computer should
detect and resolve
ambiguities
• The processing should be
on PC workstations.
Processing is dropping
rapidly in cost.
James Martin, Fourth Generation Languages, 1985
IS 257 - Fall 2002
2002.10.31- SLIDE 36
Assumptions and Issues
• Why 4GLs?
– Are they still appropriate?
– Are they still useful?
• Is Cold Fusion a 4GL?
• What about PHP?
• Who needs them?
IS 257 - Fall 2002
2002.10.31- SLIDE 37
Lecture Outline
• Review: Security and Integrity
• Fourth Generation Languages
• More on PHP and Web Interfaces
IS 257 - Fall 2002
2002.10.31- SLIDE 38
PHP
• PHP is an Open Source Software project
with many programmers working on the
code.
– Commonly paired with MySQL, another OSS
project
– Free
– Both Windows and Unix support
• Estimated that more than 250,000 web
sites use PHP as an Apache Module.
IS 257 – Spring 2004
2004.02.26 - SLIDE 39
PHP Syntax
• Similar to ASP
<HTML><BODY>
<?php
$myvar = “Hello World”;
echo $myvar ;
?>
</BODY></HTML>
• Includes most programming structures (Loops,
functions, Arrays, etc.)
• Loads HTML form variables so that they are
addressable by name
IS 257 – Spring 2004
2004.02.26 - SLIDE 40
Combined with MySQL
• DBMS interface appears as a set of
functions:
<HTML><BODY>
<?php
$db = mysql_connect(“localhost”, “root”);
mysql_select_db(“mydb”,$db);
$result = mysql_query(“SELECT * FROM employees”, $db);
Printf(“First Name: %s <br>\n”, mysql_result($result, 0 “first”);
Printf(“Last Name: %s <br>\n”, mysql_result($result, 0 “last”);
?></BODY></HTML>
IS 257 – Spring 2004
2004.02.26 - SLIDE 41
Diveshop PHP
• The same interface (with minor differences)
that we saw for ColdFusion has been
implemented in PHP and MySQL
• Address for the example is
– http://dream.sims.berkeley.edu/~ray/Diveshop/index.php3
• To setup your own MySQL database you
will need to use the “my.sims” interface to
request a MySQL account and PHP access
IS 257 - Fall 2002
2002.10.31- SLIDE 42
Diveshop PHP
• Setup puts a “public_html.php” directory in
your home directory and you put your PHP
files there
• The PHP processor is on the “dream.sims”
machine, so use that for access
• Because the results of the PHP
processing don’t include the original PHP
instructions we will look at the different
PHP scripts that generated the pages
IS 257 - Fall 2002
2002.10.31- SLIDE 43
Simple search
<html>
<head>
<title>Example PHP Database: DiveShop</title>
</head>
<body>
<CENTER>
<font size=+2 face="arial,helvetica">PHP DiveShop Customers</font>
<p>
<IMG SRC="LOGO.GIF" >
</CENTER>
<p>
<table border=0 cellpadding=2 cellspacing=2 width=110%>
<tr>
<td>ID #</td>
<td><b> <font face="arial,helvetica">Name</td>
<td>Street</td>
<td>City</td>
<td>State/Prov</td>
<td>Zip</td>
<td>Country</td>
<td>Phone</td>
<td>First Contact</td>
</tr>
<tr><td colspan=9>
<hr width=100% noshade>
</td></tr>
IS 257 - Fall 2002
<?
mysql_connect("localhost","ray","ray");
$query = "SELECT * FROM DIVECUST";
$result = mysql_db_query("ray", $query);
if ($result) {
echo "Got results...";
while ($r = mysql_fetch_array($result)) {
echo "<tr>";
$tmp = $r["Customer_No"];
echo "<td>$tmp</td>";
$tmp = $r["Name"];
echo "<td>$tmp</td>";
$tmp = $r["Street"];
echo "<td>$tmp</td>";
$tmp = $r["City"];
echo "<td>$tmp</td>";
$tmp = $r["State_Prov"];
echo "<td>$tmp</td>";
$tmp = $r["Zip_Postal_Code"];
echo "<td>$tmp</td>";
$tmp = $r["Country"];
echo "<td>$tmp</td>";
$tmp = $r["Phone"];
echo "<td>$tmp</td>";
$tmp = $r["First_Contact"];
echo "<td>$tmp</td>";
echo "</tr>";
}
} else {
echo "<p>No Results???";
}
?>
</table></body></html>
2002.10.31- SLIDE 44
Details
<?
mysql_connect("localhost","ray","ray");
$query = "SELECT * FROM DIVECUST";
$result = mysql_db_query("ray", $query);
if ($result) {
echo "Got results...";
while ($r = mysql_fetch_array($result)) {
echo "<tr>";
$tmp = $r["Customer_No"];
echo "<td>$tmp</td>";
$tmp = $r["Name"];
…etc…
}
} else {
echo "<p>No Results???";
}
?>
IS 257 - Fall 2002
2002.10.31- SLIDE 45
Adding data
<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="GENERATOR" content="Mozilla/4.75 [en]C-CCK-MCD (WinNT; U) [Netscape]">
<title>New Customer Information
</title>
</head>
<body bgcolor="#FFFFFF">
<h2>
<img SRC="LOGO.GIF" align=LEFT><b><font color="#0000FF"><font size=+4>DiveShop</font></font></b>:
New Customer Input</h2>
<hr><form ACTION="procform.php3" METHOD=GET>
<h2>
Please input your information below</h2>
Name: <input NAME=name TYPE=TEXT SIZE=100>
<br>Street: <input NAME=street TYPE=TEXT SIZE=100>
<br>City: <input NAME=city TYPE=TEXT SIZE=100>
<br>State: <input NAME=state TYPE=TEXT SIZE=2>
<br>ZIP: <input NAME=Zip TYPE=TEXT SIZE=12>
<br>Country: <input NAME=Country TYPE=TEXT SIZE=50>
<br>Phone: <input NAME=Phone TYPE=TEXT SIZE=50>
<p><input TYPE=submit VALUE=" Submit Data "><input type=reset VALUE=" Clear Fields "></form>
<hr>
</body>
</html>
IS 257 - Fall 2002
2002.10.31- SLIDE 46