1
|
- Session 4
- INFM 718N
- Web-Enabled Databases
|
2
|
- Database normalization
- Access and MySQL
- Requirements analysis
- (if we have time) PHP-MySQL integration
|
3
|
|
4
|
|
5
|
- Pick a primary key for each entity
- Build the tables
- One per entity
- Plus one per M:M 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
|
|
7
|
- Save space
- 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
|
- 1NF: Single-valued indivisible (atomic) attributes
- Split “Doug Oard” to two attributes as (“DougR=
21;,
“Oard”)
- Model M:M 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 M:M tables
- (id, role, courses) -> (id, role) + (id, courses)
- 5NF: Don’t enumerate derivable combinations
|
9
|
- 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
|
- “Foreign key” values must exist in another table
- If not, those records cannot be joined
- Checked when data added to this table
- Triggers when data deleted/changed in other table
- Specify SET NULL, RESTRICT or CASCADE
|
11
|
- “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’@’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 don’t need to]
- Connect to your database
|
12
|
- Looking around
- SHOW DATABASES;
- SHOW TABLES;
- DESCRIBE tablename;
- SELECT * FROM tablename;
- Optimization
- SHOW TABLE STATUS \G;
- OPTIMIZE TABLE tablename;
- EXPLAIN <SQLquery>;
- ALTER TABLE tablename ADD INDEX fieldname;
|
13
|
- CREATE TABLE contacts (
- ckey MEDIUMINT UNSIGN=
ED NOT
NULL AUTO_INCREMENT,
- id MEDIUMINT UNSIGNE=
D NOT
NULL,
- ctype SMALLINT UNSIGN=
ED NOT
NULL,
- cstring VARCHAR(40) N=
OT
NULL,
- FOREIGN KEY (id) REFE=
RENCES
persons(id) ON DELETE CASCADE,
- FOREIGN KEY (ctype)
REFERENCES ctlabels(ctype) ON DELETE RESTRICT,
- PRIMARY KEY (ckey)
- ) ENGINE=3DINNODB;
- To delete: DROP TABLE contacts;
|
14
|
- INSERT INTO ctlabels
- (string) VALUES
- ('primary email'),
- ('alternate email'),<=
/li>
- ('home phone'),
- ('cell phone'),
- ('work phone'),
- ('AOL IM'),
- ('Yahoo Chat'),
- ('MSN Messenger'),
- (‘other’)=
;
- To empty a table: DELETE FROM ctlabels;
|
15
|
- SELECT (“projection”) chooses columns
- WHERE (“restriction”) chooses rows
- Based on their contents
- e.g. department ID =3D “HIST”
- These can be specified together
- SELECT Student ID, Dept WHERE Dept =3D “History”
|
16
|
- Each SELECT contains a single WHERE
- Numeric comparison
- Boolean operations
- e.g., Name =3D “John” AND Dept <> “HISTR=
21;
|
17
|
- On WAMP:
- $dbc=3Dmysql_connect (‘localhost’, ‘userid’,
‘password’);
- On OTAL:
- $dbc=3Dmysql_connect(‘:/export/software/otal/mysql/run/mysqld.=
sock’,
- ‘userid’, ‘password’);
|
18
|
- <form action=3D“formResponseDemo.php”,
method=3D“post”>
- email: <input type=3D“text”, name=3D“emailR=
21;, value=3D“<?php
echo $email ?>”, size=3D30 />
- <input type=3D“radio”, name=3D“sure”,
value=3D“yes” /> Yes
- <input type=3D“radio”, name=3D“sure”,
value=3D“no” /> No
- <input type=3D“submit”, name=3D“submit”,
value=3D“Submit” />
- <input type=3D“hidden”, name=3D“submitted̶=
1;,
value=3D“TRUE” />
- </form>
- if (isset($_POST[“submitted”])) {
- echo “Your email address is $email.”;
- } else {
- echo “Error: page reached without proper form submission!R=
21;;
- }
|
19
|
|
20
|
|
21
|
|
22
|
|
23
|
- A set of key-element pairs
- $days =3D array(“Jan”->31, “Feb”=3D>2=
8,
…);
- $months =3D explode(“/”,
“Jan/Feb/Mar/…/Dec”);
- $_POST
- Each element is accessed by the key
- {$days[“Jan”]}
- $months[0];
- Arrays and loops work naturally together
|
24
|
- Naturally encodes an order among elements
- 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
|
- Declaration
- function multiply($a, $b=3D3){return $a*$b;}
- Invoking a method
- All variables in a function have only local scope
- Unless declared as global in the function
|
26
|
- Limit complexity
- Extent
- Interaction
- Abstraction
- Minimize duplication
|
27
|
- Attributes
- Appearance
- Concepts (represented by data)
- Behavior
- What it does
- How you control it
- How you observe the results
|
28
|
- People who need the task done (customers)
- People that will operate the system (users)
- People who use the system’s outputs
- People who provide the system’s inputs
- Whoever pays for it (requirements commissioner)
|
29
|
- Focus the discussion on the task
- Look for entities that are mentioned
- Discuss the system’s most important effects
- Displays, reports, data storage
- Learn where the system’s 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
|
- Functionality
- Content
- Usability
- Security/Stability
|
31
|
|
32
|
|
33
|
|
34
|
|
35
|
|
36
|
|
37
|
- 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
- Modularity
|
38
|
- 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
|
- Reusing code [run the book’s programs]
- Understanding patterns [read the book]
- Applying patterns [modify programs]
- Coding without patterns [programming]
- Recognizing new patterns
|
40
|
- Design before you build
- Focus your learning
- Program defensively
- Limit complexity
- Debug syntax from the top down
|
41
|
|
42
|
- 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
|
- 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
|
- 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
|
- Syntax errors
- 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
|
- Focus on the first error message
- 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
|
- 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
|
- 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
|
- Evidenced by inappropriate behavior
- Can’t 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
|
- First, look where the bad data was created
- If that fails, print variables at key locations
- if (DEBUG) echo “\$foobar =3D $foobar”;
- Examine output for unexpected patterns
- Once found, proceed as for run time errors
- define (“DEBUG”, FALSE); to clean the output
|
51
|
- Functional decomposition
- High-level languages
- Structured programming, object-oriented design
- Patterns
- Design patterns, standard algorithms, code reuse
|
52
|
- What was the muddiest point in today’s class?
- Be brief!
- No names!
|