Fourth Generation Languages and PHP

1
Fourth Generation Languages and PHP

• University of California, Berkeley
• School of Information Management and Systems
• SIMS 257 Database Management

2
Lecture Outline

• Review Security and Integrity
• Fourth Generation Languages
• More on PHP and Web Interfaces

3
Lecture Outline

• Review Security and Integrity
• Fourth Generation Languages
• More on PHP and Web Interfaces

4
Security and Integrity Functions in Database
Administration

• Data Integrity
• Security Management
• Backup and Recovery

5
Security and Integrity Functions in Database
Administration

• Data Integrity
• Security Management
• Backup and Recovery

6
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

7
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

8
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,

9
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

10
Transaction Control in ORACLE

• Transactions are sequences of SQL statements that
  ORACLE treats as a unit
• From the users 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

11
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

12
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

13
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

14
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
  ltwhere clausegt
• Note queries in Access function as views

15
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
16
Restricted Views
S-view of the data
NAME Dept Prof
J Smith Dept1 ---
M Doe Dept2 IT Security
R Jones Dept3 Secretary
U-view of the data
NAME Dept Prof
M Doe --- ---
R Jones Dept3 Secretary
17
Disaster Recovery Planning
From Toigo Disaster Recovery Planning
18
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

19
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

20
Lecture Outline

• Review Security and Integrity
• Fourth Generation Languages
• More on PHP and Web Interfaces

21
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??

22
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
23
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
  end-users can solve their own problems and put
  computers to work.

24
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
25
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

26
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 persons code

27
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

28
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.

29
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.

30
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

31
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?

32
Components of a 4GL
for building routine applications
Application Parameters
Data Specification
Report Specification
Feedback
Screen Specification
Rules Specification
Procedural facility
Testing tools/debugger
Interpreter
Optimizing compiler
33
5GLs -- Natural Language

• Possibilities
• Problems

34
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
35
Natural Language
Disadvantages of using NL
Appropriate response to the disadvantage

• 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

• It should be combined with other dialogue
  contructs that aid in the representation of
  precise logic and structures

James Martin, Fourth Generation Languages, 1985
36
Natural Language
Disadvantages of using NL
Appropriate response to the disadvantage

• It takes substantial time to key in sentences
• Ambiguities are possible
• Substantial processing is needed

• 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
37
Assumptions and Issues

• Why 4GLs?
• Are they still appropriate?
• Are they still useful?
• Is Cold Fusion a 4GL?
• What about PHP?
• Who needs them?

38
Lecture Outline

• Review Security and Integrity
• Fourth Generation Languages
• More on PHP and Web Interfaces

39
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.

40
PHP Syntax

• Similar to ASP
• Includes most programming structures (Loops,
  functions, Arrays, etc.)
• Loads HTML form variables so that they are
  addressable by name

ltHTMLgtltBODYgt lt?php myvar Hello World
echo myvar ?gt lt/BODYgtlt/HTMLgt
41
Combined with MySQL

• DBMS interface appears as a set of functions

ltHTMLgtltBODYgt lt?php db mysql_connect(localhost
, root) mysql_select_db(mydb,db) result
mysql_query(SELECT FROM employees,
db) Printf(First Name s ltbrgt\n,
mysql_result(result, 0 first) Printf(Last
Name s ltbrgt\n, mysql_result(result, 0
last) ?gtlt/BODYgtlt/HTMLgt
42
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

43
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 dont
  include the original PHP instructions we will
  look at the different PHP scripts that generated
  the pages

44
Simple search
lt? 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
"lttrgt" tmp r"Customer_No" echo
"lttdgttmplt/tdgt" tmp r"Name"
echo "lttdgttmplt/tdgt" tmp r"Street"
echo "lttdgttmplt/tdgt" tmp r"City"
echo "lttdgttmplt/tdgt" tmp
r"State_Prov" echo
"lttdgttmplt/tdgt" tmp r"Zip_Postal_Code"
echo "lttdgttmplt/tdgt" tmp
r"Country" echo "lttdgttmplt/tdgt"
tmp r"Phone" echo
"lttdgttmplt/tdgt" tmp r"First_Contact"
echo "lttdgttmplt/tdgt" echo "lt/trgt"
else echo "ltpgtNo Results???"
?gt lt/tablegtlt/bodygtlt/htmlgt
lthtmlgt ltheadgt lttitlegtExample PHP Database
DiveShoplt/titlegt lt/headgt ltbodygt ltCENTERgt ltfont
size2 face"arial,helvetica"gtPHP DiveShop
Customerslt/fontgt ltpgt ltIMG SRC"LOGO.GIF"
gt lt/CENTERgt ltpgt lttable border0 cellpadding2
cellspacing2 width110gt lttrgt lttdgtID
lt/tdgt lttdgtltbgt ltfont face"arial,helvetica"gtNamelt/
tdgt lttdgtStreetlt/tdgt lttdgtCitylt/tdgt lttdgtState/Provlt/
tdgt lttdgtZiplt/tdgt lttdgtCountrylt/tdgt lttdgtPhonelt/tdgt lt
tdgtFirst Contactlt/tdgt lt/trgt lttrgtlttd
colspan9gt lthr width100 noshadegt lt/tdgtlt/trgt
45
Details
lt? 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
"lttrgt" tmp r"Customer_No" echo
"lttdgttmplt/tdgt" tmp r"Name" etc
else echo "ltpgtNo Results???" ?gt
46
Adding data
lt!doctype html public "-//w3c//dtd html 4.0
transitional//en"gt lthtmlgt ltheadgt ltmeta
http-equiv"Content-Type" content"text/html
charsetiso-8859-1"gt ltmeta name"GENERATOR"
content"Mozilla/4.75 enC-CCK-MCD (WinNT U)
Netscape"gt lttitlegtNew Customer
Information lt/titlegt lt/headgt ltbody
bgcolor"FFFFFF"gt lth2gt ltimg SRC"LOGO.GIF"
alignLEFTgtltbgtltfont color"0000FF"gtltfont
size4gtDiveShoplt/fontgtlt/fontgtlt/bgt New Customer
Inputlt/h2gt lthrgtltform ACTION"procform.php3"
METHODGETgt lth2gt Please input your information
belowlt/h2gt Namenbspnbspnbspnbspltinput
NAMEname TYPETEXT SIZE100gt ltbrgtStreetnbspnb
spnbspnbspltinput NAMEstreet TYPETEXT
SIZE100gt ltbrgtCitynbspnbspnbspnbspnbsp
nbspnbspltinput NAMEcity TYPETEXT
SIZE100gt ltbrgtStatenbspnbspnbspnbspnbsp
nbspltinput NAMEstate TYPETEXT
SIZE2gt ltbrgtZIPnbspnbspnbspnbspnbspnbs
pnbspltinput NAMEZip TYPETEXT
SIZE12gt ltbrgtCountrynbspltinput NAMECountry
TYPETEXT SIZE50gt ltbrgtPhonenbspnbspnbspnb
spltinput NAMEPhone TYPETEXT SIZE50gt ltpgtltinput
TYPEsubmit VALUE" Submit Data "gtltinput
typereset VALUE" Clear Fields
"gtlt/formgt lthrgt lt/bodygt lt/htmlgt