Creating databases for web applications

1
Creating databases for web applications

• New example last lecture
• Systems design. ER diagrams. Data flow diagrams.
  Storyboards.
• Homework Plan database and applications for a
  library produce ERD and DFD

2
Added example

• Students name, gpa, department
• Go to last lecture for code
• http//newmedia.purchase.edu/Jeanine/db/studentex
  ample/showstudents.php
• http//newmedia.purchase.edu/Jeanine/db/studentex
  ample/showdepartments.php
• http//newmedia.purchase.edu/Jeanine/db/studentex
  ample/enterstudent.html

3
System analysis and design

• Tools for planning
• communication with clients (specific diagrams may
  not always be shown, but used to support
  discussion)
• communication with builders
• Attempt to focus on function before
  implementation
• logical design versus physical design

non-tech meaning of term
4
Models

• Information model Entity relationship diagrams
• Ask yourself what is the critical information?
  What are the relationships (what fields in one
  table point to other records?)
• Process model data flow
• What are the (main) processes? Who initiates
  these processes? What data stores are used
  and/or changed?
• Story board
• What are the Web pagesthe interfaces to the user
  (client/player/employee)? What is the flow from
  one Web page to another?

5
ER models

• Done before creating the tables as part of
  planning and after creating the tables, as
  documentation
• Logical/analysis level (what is in examples here)
  just defines fields and relationships.
  Physical/Design/Implementation level data types
  and also estimates on number of records.

6
Entity-relationship

• Entity the things (products, people, places,
  business concepts)
• attributes fields properties
• Relationships connections between things, as
  represented by a field in one table (attribute of
  an entity) pointing to an entity.

7
Primary keys and foreign keys

• A field (attribute) that uniquely defines a
  record in a table can be its primary key.
• sometimes generated automatically by dbms
• In another table, the way to refer to this record
  would be by using the primary key value. This is
  called a foreign key in that other table.

8
Entity Relationship diagrams

• Blocks represent the records in the table.
  Typically, they list all the fields.
• Lines between the blocks represent relationships.
  The marks on the lines indicate the numbers,
  e.g., one to many, one to one. They also indicate
  what a minimum would be.
• 0 means that an entity in one table may not be
  referenced in the other at all.

9
Example trivia quiz

• Tables are
• Questions
• Players
• History (that is, a question was asked of a
  player on a specific date, with outcome correct
  or not correct
• Each item in History relates to a specific player
  and a specific question

10
Players table player_id Player name Score lastplay
ed date
Question databank table question_id Question Answe
r (answer pattern) Value Category
0
history table question_id player_id whenplayed cor
rect
0
11
Course pre-req example

• In this design, each course has at most one
  pre-requisite course.

12
Course course title Division Pre-requisite
0
0
13
Course pre-reqs, more complete

• Each course can have no, 1, or any number of
  pre-requisites.
• TWO TABLES!
• Courses
• Statements of pre-reqs

14
Statement of pre-requisites Course to be
taken pre-required course (prior course)
Course Course title Division
0
0
0
Each statement of pre-requisites refers to
exactly one course and exactly one 'pre-req'.
A course can be in either position, zero, one or
several times. Creating Web Documents is a
pre-req for Creating Dynamic Web Docs
Creating DBs for Web Some courses may have
pre-requisites of more than one course.
15
clip tag table

• Video archive application has table of clips and
  tags similar to the pre-req tables.
• Demonstrate
• NOTE I removed entries on 2 videos and then
  restored them with shorter names!

16
Data design

• Sometimes done by taking the artifacts (forms,
  reports, screens) and writing down all those
  fields into first draft of records
• These artifacts can be from the current system or
  the proposed materials for a new system
• Apply a technique called normalization to get
  well-defined entities and relationships.
• First step is making sure each record has fixed
  number of fields.
• For example, not indefinite number of pre-reqs
• Subsequent steps ensure that data is not
  repeated, so information only needs to be updated
  in one place
• Make customer data be in a customer file NOT in
  each transaction file
• EXTRA CREDIT opportunity for posting

17
Process diagram

• Focus on
• Blocks
• processes
• agents (this could be people in specific roles or
  'the system' triggered by events, including a
  time condition)
• data stores more general term than databases
• Connections
• Agent engaged with process
• Process using data store

18
Data flow diagram (process diagram) for quiz show
Player
Questions DB
Play game
History DB
Edit questions
Player scores
Editor
19
Story board

• more implementation based than the others
• Therefore, part of system build, not system
  design, or, perhaps, System Physical Design not
  Logical Design
• focus on the scripts and how the scripts call
  each other

20
Include/Required file opendbq
Create quiz tables (php only)
Input Questions (handles form input)
Choose category
Show scores
Ask question
Check answer
Clear tables
21
Entities for store

• Initial design
• catalog of products
• orders
• one or more ordered items
• customer list
• Enhanced design
• Need 1 more table call it ordered items

22
ER diagram for on-line store
Customer list Customer ID first name last
name Billing information E-mail Password
Product catalog Product id Product
name Picture Cost
0
0
Order list Order ID Customer ID Date Status Total
Ordered items Order ID Product Quantity
23
Data flow (process) diagram for on-line store.
Catalog
Browse/Order
Customer
Customer list
Billing
Current orders
Ordered items
Shipping
Billing system (timed event)
Shipping clerk
Note that this is the information/data flow, not
the flow of goods. The shipping operation
produces a physical product the collection of
ordered items, packed and set off for delivery.
24
Storyboard of partial implementation ordering
Create tables (php only)
Include/require opendbo
Input products
Delete current customer cookie
Order product
makeorder
Submit order
Shopping cart
Include/require displaycartfunction
25
Video archive main table

• fields "fileaddr varchar(15) NOT
  NULL,cliptitle varchar(50) NOT NULL,"
• fields fields . "clipdate varchar(10),clipgrou
  p varchar(15), sequence int(6), PRIMARY KEY
  (fileaddr)"
• NOTE example of primary key NOT created by DBMS.

26
Video archive tag table

• fields "fileaddr varchar(15) NOT NULL,tag
  varchar(20) NOT NULL"

27
ER for video clip archive

Video clips File addr Clip title Clip
group Sequence
Tags File addr Tag
28
Grid Design example

• Designs generated in Flash application stored as
  variable length strings in database
• Alternative would be to store design elements,
  like the ordered items
• Application could use sign on procedure!

29
ER for design

Members Member id Member name Member email Member
password
Designs Design id Member id Design name Design
string
30
Song with features example

• Bare bones
• Song table and Feature table plus
• A song can have 0 or more features
• Intention is to provide way of detecting songs
  similar to a given song
• Complex SQL statement!

31
Song features

feature id name description
song id Name Description Year
0
feature of song fisid fid fid
0
32
Song with features storyboard
createtables.php
deletefeaturesinsongs.php
listallfeaturesinsongs.php
addsong.html
addsong.php
opendbe.php (included in all php files)
addfeature.html
addfeature.php
addfeaturestosong1.php
addfeaturestosong2.php
findothers1.php
findothers2.php
33
Songs with features DFD
Add songs
Identify songs
Admin
Finder
Song info
Add feature
Add features to song
Edit info
34
Agents

• Of course in these toy applications such as the
  songs with features, there is just one agent
  (me!) but
• Important to plan what are roles of system
  builders versus system users
• Some social networking song sites do allow users
  to add songs, specify features, add features, etc.

35
Opportunity for improvement

• Functions mentioned but not implemented (in
  orange)
• Navigation could be better
• specifically, going back or 'coming out of'
  steps. Where to go
• Teams will review and enhance these projects and
  present whole thing to the class.

36
Homework

• Design ER and DFD for a library.
• YOUR job is to define entities, relationships,
  agents, data stores, processes.
• Logical design, not physical design
• Note a data store could be manual. Don't be
  concerned with that.