Creating databases for Web Applications

1
Creating databases for Web Applications

• Introduction to databases.
• Access demonstration. MySQL
• Homework Practice with Access. Make posting with
  unique source on MySql.

2
Claim

• All database management systems share similar
  structure.
• Tables Records Fields
• Records have primary keys
• Records may have fields that hold foreign keys,
  that is, pointers to records in other tables or
  in that table.
• They differ on efficiencies noticeable in
  larger, production systems
• How many simultaneous queries
• Security issues

3
Example (NOT ACCURATE)

• Table of courses
• Each record represents one course.
• Course identifier MAT3530
• Cross-listing NME3520
• Name Creating Databases for Web Applications
• ASSUMING a course has at most one pre-requisite,
  each record has field that
• Pre-req MAT1420
• NOTE the pre-reqs are more complicated, since
  there are many possibilities. This would require
  a new table.

4
Creating database

• Create tables
• Create / specify relations.
• If database to be used on stand-alone computer,
  create Forms, Queries, Views. Instead, we will
  create programs (html and php scripts) to do this.

5
Table

• Define what is a record in the table what are
  the fields?
• What is the information associated with this one
  thing?
• What is the data type of each field?
• If the databases will contain many records, may
  be important to be careful as to size
• Is there an intrinsic primary key (unique for the
  record in the table) or should system supply one?
• Fields can have at most one value (can be null)
• Multiple values means you need another table

6
Caution

• Defining (designing) a database is not trivial.
• May require revision.
• Interview process with clients/customers/systems
  owners can take time.

7
What should we do?

• Favorite web sites
• Music collection
• Courses taken
• Candidates
• ?

8
Access

• Part of Microsoft Office suite

9
Open Office Base

• Open source product part of Open Office
• Very similar to Access

10
Create table

• Create table Use Design view
• Create form Use Wizard
• Use form to populate (that is, put data into
  table)
• Create report

11
Alternative spreadsheet to db

• Recovery Act
• http//www.hhs.gov/recovery/reports/index.html
• Click on weekly report Excel file
• In Excel Massage file (remove first few rows)
  (Protected cells may cause problems.)
• Open Access. File/Open. Browse to the xls file.
  Follow directions.
• Create query get data on NY, NJ, CT.

12
Spreadsheet vs Database

• Scale DBMS can handle large amounts of
  information
• Efficiency DBMS can handle different datatypes
• DBMS have facilities for MANAGING access by
  multiple users
• DBMS supports multiple tables and queries across
  the multiple tables.
• MySQL (and other DBMS) have easier (?)
  connections to php and other programming
  languages
• Extra credit opportunity do posting on php and
  Excel or php and Open Office or VB.net xls,
  etc. Give explanation / examples, NOT JUST THE
  LINK!

13
Trivia game

• Questions table
• Question id
• Question text
• Question answer
• Question category
• Question score
• Player table
• History table

14
Trivia database players

• Player id
• Player name
• Player password

15
Trivia database history

• Event id
• Player id
• Question id
• Result (right or wrong)
• Time stamp

16
Entity Relationship Diagram
Questions Ques id Text Answer Category score
PlayersPlayer idPlayer namePassword
HistoryhistoryIDQues idPlayer idResultTime
stamp
17
Access

• Create relationships using the Lookup Wizard for
  the Data Type and specifying getting values from
  a table.

18
Finalize web accounts (for MySql)

• Use secure ftp
• REMEMBER YOUR ACCOUNT NUMBER PASSWORD

19
Working environment

• You will
• Write php scripts just to create tables. These
  scripts may just be executed one time.
• You will
• create html and php files using Dreamweaver or
  Textpad or Textwriter or equivalents.
• upload to your account using suggested ftp
• Test
• There is an alternative setting up your computer
  to be a server. This is fairly complex.

20
Reprise

• Useful to think in terms of 3 language domains
  (my terminology)
• client side browser (interpreter of html)
• server side interpreter of asp/JavaScript or php
• (There is another level here for the operating
  system, but this is what the asp objects and the
  php built-in functions variables provide.)
• database interpreter of SQL (by Access or MySQL)

21
Homework

• Practice with Access
• Practice with Open Office Base
• Extra Credit Make posting comparing the two.
• Required Find a good source on MySql and make
  posting.
• Continue review of HTML and ActionScript