1
|
- Session 4
- INFM 718N
- Web-Enabled Databases
|
2
|
- Database design
- MySQL
- Project teams: next steps
- (if we have time) Programming
|
3
|
|
4
|
- 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
- Mathematical theory that supports optimization
|
5
|
- Structured Query Language (SQL)
- Consistent, unambiguous interface to any DBMS
- Simple command structure:
- e.g., SELECT last-name FROM students WHERE dept=3D“CLIS̶=
1;
- Useful standard for inter-process communications
- Visual programming (e.g., Microsoft Access)
- Unambiguous, and easier to learn than SQL
- Natural language (e.g., interactive voice response system)
- Improves ease of use, but with potential for ambiguity and error
- e.g., Show me the last names of students in CLIS
|
6
|
- What questions must you answer?
- What data is needed to generate the answers?
- Entities
- Attributes of those entities
- Relationships
- Nature of those relationships
- How will the user interact with the system?
- Relating the question to the available data
- Expressing the answer in a useful form
|
7
|
|
8
|
- Entities
- Types
- Subtypes (disjoint / overlapping), aggregation
- Attributes
- Identifier
- Relationships
- Cardinality
- Existence
- Degree
|
9
|
- 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
|
10
|
- 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
|
11
|
- 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
|
12
|
|
13
|
- “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
|
14
|
- 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
|
15
|
- 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
|
16
|
- 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
|
17
|
- “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
|
18
|
- “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
|
19
|
- 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;
|
20
|
- 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;
|
21
|
- 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;
|
22
|
- 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”
|
23
|
- Each SELECT contains a single WHERE
- Numeric comparison
- Boolean operations
- e.g., Name =3D “John” AND Dept <> “HISTR=
21;
|
24
|
|
25
|
|
26
|
|
27
|
|
28
|
|
29
|
- Attributes
- Appearance
- Concepts (represented by data)
- Behavior
- What it does
- How you control it
- How you observe the results
|
30
|
- 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)
|
31
|
- 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
|
32
|
- Functionality
- Content
- Usability
- Security/Stability
|
33
|
- Learn some words
- Put those words together in simple ways
- Examine to broaden your understanding
- Create to deepen your mastery
- Repeat until fluent
|
34
|
- Local vs. Web-server-based display
- HTML as an indirect display mechanism
- “View Source” for debugging
- Procedural perspective (vs. object-oriented)
|
35
|
- 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
|
36
|
- 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
|
37
|
- 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
|
38
|
- Limit complexity
- Extent
- Interaction
- Abstraction
- Minimize duplication
|
39
|
- <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;;
- }
|
40
|
- 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
|
41
|
- 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
|
42
|
- Reusing code [run the book’s programs]
- Understanding patterns [read the book]
- Applying patterns [modify programs]
- Coding without patterns [programming]
- Recognizing new patterns
|
43
|
- Design before you build
- Focus your learning
- Program defensively
- Limit complexity
- Debug syntax from the top down
|
44
|
|
45
|
- 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
|
46
|
- 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
|
47
|
- 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
|
48
|
- 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)
|
49
|
- 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
|
50
|
- 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!
|
51
|
- 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
|
52
|
- 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
|
53
|
- 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
|
54
|
- Functional decomposition
- High-level languages
- Structured programming, object-oriented design
- Patterns
- Design patterns, standard algorithms, code reuse
|
55
|
- What was the muddiest point in today’s class?
- Be brief!
- No names!
|