MySQL and Requirements

1
MySQL and Requirements

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

2
Agenda

• Database normalization
• Access and MySQL
• Requirements analysis
• (if we have time) PHP-MySQL integration

3

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

Client Hardware
Web Browser
Database
Server Hardware
4
An E-R Example
manage-role
1
1
member-of
M
student
team
1
M
creates
human
implement-role
1
needs
1
M
project
client
d
php-project
ajax-project
5
Making Tables from E-R Diagrams

• Pick a primary key for each entity
• Build the tables
• One per entity
• Plus one per MM relationship
• Choose terse but memorable table and field names
• Check for parsimonious representation
• Relational normalization
• Redundant storage of computable values
• Implement using a DBMS

6
Extended ER Diagram (Access)
7
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

8
Normalization

• 1NF Single-valued indivisible (atomic)
  attributes
• Split Doug Oard to two attributes as (Doug,
  Oard)
• Model MM implement-role relationship with a
  table
• 2NF Attributes depend on complete primary key
• (id, impl-role, name)-(id, name)(id, impl-role)
• 3NF Attributes depend directly on primary key
• (id, addr, city, state, zip)-(id, addr,
  zip)(zip, city, state)
• 4NF Divide independent MM tables
• (id, role, courses) - (id, role) (id, courses)
• 5NF Dont enumerate derivable combinations

9
Normalized Table Structure

• Persons id, fname, lname, userid, password
• Contacts id, ctype, cstring
• Ctlabels ctype, string
• Students id, team, mrole
• Iroles id, irole
• Rlabels role, string
• Projects team, client, pstring

10
Referential Integrity

• Foreign key values must exist in another table
• If not, those records cannot be joined
• Checked when data added to this table
• MySQL Error 150
• Triggers when data deleted/changed in other table
• Specify SET NULL, RESTRICT or CASCADE

11
Getting started with MySQL

• root creates database, grants permissions
• By you on WAMP (mysql u root p)
• By Charles Goldman on OTAL
• CREATE DATABASE team1
• GRANT SELECT, INSERT, UPDATE, DELETE, INDEX,
  ALTER, CREATE, DROP ON team1. TO
  foo_at_localhost IDENTIFIED BY bar
• FLUSH PRIVILEGES
• Start mysql
• Start-Run-cmd for WAMP, ssh for OTAL
• mysql u foo p bar you can cd to your playspace
  first, but you dont need to
• Connect to your database
• USE team1

12
Some Useful MySQL Commands

• Looking around
• SHOW DATABASES
• SHOW TABLES
• DESCRIBE tablename
• SELECT FROM tablename
• Optimization
• SHOW TABLE STATUS \G
• OPTIMIZE TABLE tablename
• EXPLAIN
• ALTER TABLE tablename ADD INDEX fieldname

13
Creating Tables

• CREATE TABLE contacts (
• ckey MEDIUMINT UNSIGNED NOT NULL
  AUTO_INCREMENT,
• id MEDIUMINT UNSIGNED NOT NULL,
• ctype SMALLINT UNSIGNED NOT NULL,
• cstring VARCHAR(40) NOT NULL,
• FOREIGN KEY (id) REFERENCES persons(id) ON
  DELETE CASCADE,
• FOREIGN KEY (ctype) REFERENCES ctlabels(ctype)
  ON DELETE RESTRICT,
• PRIMARY KEY (ckey)
• ) ENGINEINNODB
• To delete DROP TABLE contacts

14
Populating Tables

• INSERT INTO ctlabels
• (string) VALUES
• ('primary email'),
• ('alternate email'),
• ('home phone'),
• ('cell phone'),
• ('work phone'),
• ('AOL IM'),
• ('Yahoo Chat'),
• ('MSN Messenger'),
• (other)
• To empty a table DELETE FROM ctlabels

15
The SQL SELECT Command

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

16
WHERE Clause

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

17
Connecting PHP to MySQL

• On WAMP
• dbcmysql_connect (localhost, userid,
  password)
• On OTAL
• dbcmysql_connect(/export/software/otal/mysql/r
  un/mysqld.sock,
• userid, password)

18
Using PHP with (X)HTML Forms

• methodpost
• email value, size30 /
• 
  Yes
• 
  No
• valueSubmit /
• valueTRUE /
• if (isset(_POSTsubmitted))
• echo Your email address is email.
• else
• echo Error page reached without proper form
  submission!

19
database access information as constants. DEFINE
('DB_USER', 'tester') DEFINE ('DB_PASSWORD',
'tester') DEFINE ('DB_HOST', 'localhost') DEFINE
('DB_NAME', 'sitename') // Make the
connection. dbc _at_mysql_connect (DB_HOST,
DB_USER, DB_PASSWORD) OR die ('Could not connect
to MySQL ' . mysql_error() ) // Select the
database. _at_mysql_select_db (DB_NAME) OR die
('Could not select the database ' .
mysql_error() ) // Create a function for
escaping the data. function escape_data (data)
// Address Magic Quotes. if
(ini_get('magic_quotes_gpc')) data
stripslashes(data) // Check for
mysql_real_escape_string() support. if
(function_exists('mysql_real_escape_string'))
global dbc // Need the connection. data
mysql_real_escape_string (trim(data),
dbc) else data mysql_escape_string
(trim(data)) // Return the escaped
value. return data // End of function. ?
20
after Scripts 9.1, 9.3, 9.6, 9.10. 9.13
9.14) // Send NOTHING to the Web browser prior to
the session_start() line! // Check if the form
has been submitted. if (isset(_POST'submitted'
)) require_once ('../mysql_connect.php') //
Connect to the db. errors array() //
Initialize error array. // Check for an email
address. if (empty(_POST'email'))
errors 'You forgot to enter your email
address.' else e
escape_data(_POST'email') // Check
for a password. if (empty(_POST'password'))
errors 'You forgot to enter your
password.' else p
escape_data(_POST'password')
21
if (empty(errors)) // If everything's OK.
/ Retrieve the user_id and first_name for that
email/password combination. / query
"SELECT user_id, first_name FROM users WHERE
email'e' AND passwordSHA('p')"
result _at_mysql_query (query) // Run the
query. row mysql_fetch_array (result,
MYSQL_NUM) // Return a record, if applicable.
if (row) // A record was pulled from the
database. // Set the session data
redirect. session_name ('YourVisitID')
session_start()
_SESSION'user_id' row0
_SESSION'first_name' row1
_SESSION'agent' md5(_SERVER'HTTP_USER_AGENT
') // Redirect the user to the
loggedin.php page. // Start defining the
URL. url 'http//' .
_SERVER'HTTP_HOST' . dirname(_SERVER'PHP_SELF
') // Check for a trailing slash.
if ((substr(url, -1) '/') OR
(substr(url, -1) '\\') ) url
substr (url, 0, -1) // Chop off the slash.
// Add the page. url .
'/loggedin.php' header("Location
url") exit() // Quit the script.
else // No record matched the query.
errors 'The email address and password
entered do not match those on file.' // Public
message. errors mysql_error() .
'

Query ' . query // Debugging
message. // End of if
(empty(errors)) IF. mysql_close() // Close
the database connection. else // Form has not
been submitted. errors NULL // End of
the main Submit conditional.
22
// Begin the page now. page_title
'Login' include ('./includes/header.html') if
(!empty(errors)) // Print any error messages.
echo 'Error! class"error"The following error(s) occurred
/' foreach (errors as msg) // Print each
error. echo " - msg
\n" echo
'Please try again.' // Create the
form. ? Login method"post" Email Address type"text" name"email" size"20" maxlength"40"
/ Password name"password" size"20" maxlength"20" /
value"Login" / name"submitted" value"TRUE" / clude ('./includes/footer.html') ?
23
Arrays in PHP

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

24
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

25
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

26
Why Modularity?

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

27
What are Requirements?

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

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

29
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

30
First Things First

• Functionality
• Content
• Usability
• Security/Stability

31
Backup Slides
32
A Denormalized Flat File
33
A Normalized Relational Database
Student Table
Department Table
Course Table
Enrollment Table
34
Example of Join
Student Table
Department Table
35
Project
New Table
SELECT Student ID, Department
36
Restrict
New Table
WHERE Department ID HIST
37
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

38
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

39
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

40
Best Practices

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

41
Rapid Prototyping Waterfall
Update Requirements
Write Specification
Choose Functionality
Initial Requirements
Create Software
Build Prototype
Write Test Plan
42
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

43
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

44
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

45
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)

46
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

47
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!

48
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

49
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

50
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

51
Three Big Ideas

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

52
One-Minute Paper

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