PHP Programming Part II and Database Design

1
PHP Programming Part IIandDatabase Design

• Session 3
• INFM 718N
• Web-Enabled Databases

2
Agenda

• PHP
• Examples
• Programming well
• Speed dating (20 minutes)
• Database design

3

• Relational normalization
• Structured programming
• Software patterns
• Object-oriented design
• Functional decomposition

Client Hardware
Web Browser
Database
Server Hardware
4
Databases

• Database
• Collection of data, organized to support access
• Models some aspects of reality
• DataBase Management System (DBMS)
• Software to create and access databases
• Relational Algebra
• Special-purpose programming language

5
Database Programming

• Natural language
• Goal is ease of use
• e.g., Show me the last names of students in CLIS
• Ambiguity sometimes results in errors
• Structured Query Language (SQL)
• Consistent, unambiguous interface to any DBMS
• Simple command structure
• e.g., SELECT Last name FROM Students WHERE
  DeptCLIS
• Useful standard for inter-process communications
• Visual programming (e.g., Microsoft Access)
• Unambiguous, and easier to learn than SQL

6
E-R Diagrams

• Entities
• Types
• Subtypes (disjoint / overlapping), aggregation
• Attributes
• Mandatory / optional
• Identifier
• Relationships
• Cardinality
• Existence
• Degree

7
Normalization

• 1NF
• Atomic entries (Doug Oard) -gt Doug, Oard
• Unique columns (classes -gt separate table)
• 2NF
• No repeated data in multiple rows
• Ford, Taurus -gt separate table
• 3NF
• Nonkey dependent on primary key
• City depends on zip

8
Goals of Normalization

• Save space
• Save each fact only once
• More rapid updates
• Every fact only needs to be updated once
• More rapid search
• Finding something once is good enough
• Avoid inconsistency
• Changing data once changes it everywhere

9
Installing WAMP

• http//www.en.wampserver.com/
• Run phpinfo.php
• Error reporting on? MySQL configured?
• Create a database and user accounts (mysql)
• Run mysql_test.php
• Connects OK?

10
Working with PHP

• Local vs. server-based display
• HTML as an indirect display mechanism
• View Source for debugging
• Procedural vs. Object-Oriented

11
Language Learning

• Learn some words
• Put those words together in simple ways
• Examine to broaden your understanding
• Create to deepen your mastery
• Repeat until fluent

12
Thinking About PHP

• Local vs. Web-server-based display
• HTML as an indirect display mechanism
• View Source for debugging
• Procedural perspective (vs. object-oriented)

13
Arrays in PHP

• A set of key-element pairs
• days array(Jan-gt31, Febgt28, )
• months explode(/, Jan/Feb/Mar//Dec)
• _POST
• Each element is accessed by the key
• daysJan
• months0
• Arrays and loops work naturally together

14
Thinking about Arrays

• Naturally encodes an order among elements
• days rksort(days)
• Natural data structure to use with a loop
• Do the same thing to different data
• PHP unifies arrays and hashtables
• Elements may be different types

15
Functions in PHP

• Declaration
• function multiply(a, b3)return ab
• Invoking a method
• b multiply(b, 7)
• All variables in a function have only local scope
• Unless declared as global in the function

16
Why Modularity?

• Limit complexity
• Extent
• Interaction
• Abstraction
• Minimize duplication

17
Using PHP with (X)HTML Forms

• ltform actionformResponseDemo.php,
  methodpostgt
• email ltinput typetext, nameemail,
  valuelt?php echo email ?gt, size30 /gt
• ltinput typeradio, namesure, valueyes /gt
  Yes
• ltinput typeradio, namesure, valueno /gt
  No
• ltinput typesubmit, namesubmit,
  valueSubmit /gt
• ltinput typehidden, namesubmitted,
  valueTRUE /gt
• lt/formgt
• if (isset(_POSTsubmitted))
• echo Your email address is email.
• else
• echo Error page reached without proper form
  submission!

18
Sources of Complexity

• Syntax
• Learn to read past the syntax to see the ideas
• Copy working examples to get the same effect
• Interaction of data and control structures
• Structured programming
• Modularity

19
Some Things to Pay Attention To

• Syntax
• How layout helps reading
• How variables are named
• How strings are used
• How input is obtained
• How output is created
• Structured Programming
• How things are nested
• How arrays are used

• Modular Programming
• Functional decomposition
• How functions are invoked
• How arguments work
• How scope is managed
• How errors are handled
• How results are passed

20
Programming Skills Hierarchy

• Reusing code run the books programs
• Understanding patterns read the book
• Applying patterns modify programs
• Coding without patterns programming
• Recognizing new patterns

21
Best Practices

• Design before you build
• Focus your learning
• Program defensively
• Limit complexity
• Debug syntax from the top down

22
Rapid Prototyping Waterfall
Update Requirements
Write Specification
Choose Functionality
Initial Requirements
Create Software
Build Prototype
Write Test Plan
23
Focus Your Learning

• Find examples that work
• Tutorials, articles, examples
• Cut them down to focus on what you need
• Easiest to learn with throwaway programs
• Once it works, include it in your program
• If it fails, you have a working example to look at

24
Defensive Programming

• Goal of software is to create desired output
• Programs transform input into output
• Some inputs may yield undesired output
• Methods should enforce input assumptions
• Guards against the user and the programmer!
• Everything should be done inside methods

25
Limiting Complexity

• Single errors are usually easy to fix
• So avoid introducing multiple errors
• Start with something that works
• Start with an existing program if possible
• If starting from scratch, start small
• Add one new feature
• Preferably isolated in its own method

26
Types of Errors

• Syntax errors
• Detected at compile time
• Run time exceptions
• Cause system-detected failures at run time
• Logic errors
• Cause unanticipated behavior (detected by you!)
• Design errors
• Fail to meet the need (detected by stakeholders)

27
Debugging Syntax Errors

• Focus on the first error message
• Fix one thing at a time
• The line number is where it was detected
• It may have been caused much earlier
• Understand the cause of warnings
• They may give a clue about later errors
• If all else fails, comment out large code regions
• If it compiles, the error is in the commented part

28
Run Time Exceptions

• Occur when you try to do the impossible
• Use a null variable, divide by zero,
• The cause is almost never where the error is
• Why is the variable null?
• Exceptions often indicate a logic error
• Find why it happened, not just a quick fix!

29
Debugging Run-Time Exceptions

• Run the program to get a stack trace
• Where was this function called from?
• Print variable values before the failure
• Reason backwards to find the cause
• Why do they have these values?
• If necessary, print some values further back

30
Logic Errors

• Evidenced by inappropriate behavior
• Cant be automatically detected
• Inappropriate is subjective
• Sometimes very hard to detect
• Sometimes dependent on user behavior
• Sometimes (apparently) random
• Cause can be hard to pin down

31
Debugging Logic Errors

• First, look where the bad data was created
• If that fails, print variables at key locations
• if (DEBUG) echo \foobar foobar
• Examine output for unexpected patterns
• Once found, proceed as for run time errors
• define (DEBUG, FALSE) to clean the output

32
Three Big Ideas

• Functional decomposition
• Outside-in design
• High-level languages
• Structured programming, object-oriented design
• Patterns
• Design patterns, standard algorithms, code reuse

33
Structured Information

• Field An atomic unit of data
• number, string, true/false,
• Record A collection of related fields
• Table A collection of related records
• Each record is one row in the table
• Each field is one column in the table
• Primary Key The field that identifies a record
• Values of a primary key must be unique
• Database A collection of tables

34
A Simple Example
primary key
35
Another Example

• Which students are in which courses?
• What do we need to know about the students?
• first name, last name, email, department
• What do we need to know about the courses?
• course ID, description, enrolled students, grades
  

36
A Flat File Solution
Discussion Topic Why is this a bad approach?
37
Relational Algebra

• Tables represent relations
• Course, course description
• Name, email address, department
• Named fields represent attributes
• Each row in the table is called a tuple
• The order of the rows is not important
• Queries specify desired conditions
• The DBMS then finds data that satisfies them

38
A Normalized Relational Database
Student Table
Department Table
Course Table
Enrollment Table
39
Approaches to Normalization

• For simple problems
• Start with binary relationships
• Pairs of fields that are related
• Group together wherever possible
• Add keys where necessary
• For more complicated problems
• Entity relationship modeling

40
Example of Join
Student Table
Department Table
41
Problems with Join

• Data modeling for join is complex
• Join are expensive to compute
• Both in time and storage space
• But it is joins that make databases relational
• Projection and restriction also used in flat files

42
Some Lingo

• Primary Key uniquely identifies a record
• e.g. student ID in the student table
• Compound primary key
• Synthesize a primary key with a combination of
  fields
• e.g., Student ID Course ID in the enrollment
  table
• Foreign Key is primary key in the other table
• Note it need not be unique in this table

43
Referential Integrity

• Foreign key values must exist in other table
• If not, those records cannot be joined
• Can be enforced when data is added
• Associate a primary key with each foreign key
• Helps avoid erroneous data
• Only need to ensure data quality for primary keys

44
Project
New Table
SELECT Student ID, Department
45
Restrict
New Table
WHERE Department ID HIST
46
The SELECT Command

• Project chooses columns
• Based on their label
• Restrict chooses rows
• Based on their contents
• e.g. department ID HIST
• These can be specified together
• SELECT Student ID, Dept WHERE Dept History

47
Restrict Operators

• Each SELECT contains a single WHERE
• Numeric comparison
• lt, gt, , ltgt,
• e.g., gradelt80
• Boolean operations
• e.g., Name John AND Dept ltgt HIST

48
What are Requirements?

• Attributes
• Appearance
• Concepts (represented by data)
• Behavior
• What it does
• How you control it
• How you observe the results

49
Who Sets the Requirements?

• People who need the task done (customers)
• People that will operate the system (users)
• People who use the systems outputs
• People who provide the systems inputs
• Whoever pays for it (requirements commissioner)

50
The Requirements Interview

• Focus the discussion on the task
• Look for entities that are mentioned
• Discuss the systems most important effects
• Displays, reports, data storage
• Learn where the systems inputs come from
• People, stored data, devices,
• Note any data that is mentioned
• Try to understand the structure of the data
• Shoot for the big picture, not every detail

51
The Project Plan

• One-page contract
• Between developer and requirements commissioner
• Goal The problem to be solved
• Product What you plan to deliver
• Scope Available time and personnel
• Roles What you expect each other to do

52
First Things First

• Functionality
• Content
• Usability
• Security/Stability

53
One-Minute Paper

• What was the muddiest point in todays class?
• Be brief!
• No names!