NMED 3850 A Advanced Online Design

1. NMED 3850 AAdvanced Online Design February 25, 2010 V. Mahadevan

2. Further Database Concepts • We have seen based on our experience with the Calendar assignment the importance of having a unique id for each row of a database table. • For example, to delete or update a specific event on a specific date which has more than one event.

3. DB Concepts (cont.) • To create a table with an auto-incrementer on the id field, the following SQL syntax is used: • CREATE TABLE person ( person_id INT NOT NULL AUTO_INCREMENT, last_name VARCHAR(20), first_name VARCHAR(20), PRIMARY KEY (person_id) ); • The “PRIMARY KEY” syntax identifies the person_id field as the unique defining field in the table i.e. there can be no duplicate person_ids.

4. DB Concepts (cont.) • Consider a system for processing online orders. • What sort of info. do we need to store in the database? • Info. about the person placing the order (name, address, etc.). • Info. about the items being ordered. • A record of the order itself. • This is at the bare minimum.

5. DB Concepts (cont.) • CREATE TABLE person ( person_id INT NOT NULL AUTO_INCREMENT, last_name VARCHAR(20), first_name VARCHAR(20), PRIMARY KEY (person_id) ) ENGINE=INNODB; • CREATE TABLE orders ( order_id INT NOT NULL AUTO_INCREMENT, amount DECIMAL (5, 2), person_id INT NOT NULL, FOREIGN KEY (person_id) REFERENCES person (person_id), PRIMARY KEY (order_id)) ENGINE=INNODB;

6. Table person person_id ... Table orders order_id person_id ... DB Concepts (cont.) • The syntax FOREIGN KEY... links the 2 tables “person” and “order” together.

7. DB Concepts (cont.) • Why not just put the person info. and order info. together in one table? • Some reasons: • What if the same people make multiple orders and 2 people have the same first and last names? How do you know which person the orders refer to? • Very large tables are inefficient to process.

8. DB Concepts (cont.) • By seperating the orders and person tables, we are guaranteed that only unique persons will be stored in the person table (based on person_id). • Different orders made by the same person will still be linked to that same person with no chance for confusion. • Tables will be kept a manageable size.

9. DB Concepts (cont.) • Linking of 2 tables together also has another beneficial effect: maintaining the referential integrity of the data. • For example, suppose there is only one entry in the table person with person_id = 1. • When a new order is processed, it will not be possible to have the order refer to any other person except the person whose person_id = 1. • That's because no other ids exist yet in the person table, so the orders table is maintaining the integrity of the database. • Try experimenting by inserting something into the table orders with a person_id that does not exist in the table person.

10. DB Concepts (cont.) • Okay, so now we have the following data stored in our database: • Info. about persons. • Info. about orders made by persons. • Lastly, we need to store the info. about the actual items being ordered. • The above is a slightly more complex matter, so we will revisit it later.

11. Further HTML / PHP Concepts (cont.) <form acton = "myscript.php" method = post> user name: <input type = text name="username"> <br> password: <input type = password name = "thepasswd"> <p><input type = submit value = "log in"> </form>

12. HTML / PHP Concepts (cont.) • The above code simply creates an HTML form with a password field. • Note that the password is not encrypted i.e. it is still sent over the wire in plain text. Use https on your server if you want encryption. • Once the user name and password have been submitted to the PHP script, the script can then verify them and either allow or deny access.

13. HTML / PHP Concepts (cont.) • How the PHP script will work: • Obviously there must be a database table (call it “users”) which has the username and password information stored in it. • The script will do a select based on the username (assume that usernames are unique). • It will then compare the entered password with the one stored in the table (use the PHP “if” statement to do the comparison). • Based on that, you can output the appropriate PHP code to either allow or deny access.

14. Lab Assignment • 1. Create a login form that: • Allows the user to enter a username and password. • Verifies this info. with the info. stored in a pre-existing table. • Returns either a “login successful” or “login failed” response to the user. • 2. Create the tables person and order as shown in these slides. Enter some data into the person table. What happens when you try to enter data into the orders table and the person_id does not exist in the person table? • 3. Sketch a design for an online shopping cart database. Draw the tables you will need, their links to each other, and a list of information you plan to store. This does NOT have to be syntactically correct. You will need this for next week!