Multimedia Programming Techniques for Teachers

1
Multimedia Programming Techniques for Teachers

• Curriculum Designer Vincent Ng
• Total 24 hours
• Session 6/8

2
Objectives/Aims

• Basic database design concept
• Database setup for ASP
• Simple query language
• Manipulating databases with ASP
• Common problems in writing ASP

3
Agenda

• Database design and setup for ASP
• Simple query language
• Break
• Manipulating databases with ASP
• Common problems in writing ASP
• Lab and exercises

4
Database Basics

• Databases are organized by tables with relations
• For Examples

5
Tables

• Table are organized by fields and records

Records
Fields
6
Normalization Identify Key

• In database design, data entities are uniquely
  identified by a unique value, know as key
• Such unique value is usually hidden from the user
  interface and only for internal reference and
  inter-table relationship purposes
• Key values are usually of type integer for
  operation efficiency

7
First Normal Form (1NF)

• 1NF if and only if all underlying simple domains
  contain atomic value only

8
Second Normal Form (2NF)

• 2NF if and only if it is in 1NF and every nonkey
  attribute is fully dependent on the primary key

9
Third Normal Form (3NF)

• 3NF if and only if it is in 2NF and every nonkey
  attribute is non-transitively dependent on the
  primary key

10
Practice Activities 1

• Given un-normalized Form, Normalize it to 3NF

11
Answer to Practice Activities 1

• SELECT Instance_table.Instance_no,
  Student_table.Student_no, FROM Student_table,
  Teacher_table, Instance_tableWHERE Student_table.
  Class Teacher_table.Home_classAND
  Instance_table.Student_no Student_table.Student
  _no

12
Database Setup in Windows Env.
ASP / VB / VC
Access / MS SQL
ODBC
13
ODBC by Microsoft

• Open DataBase Connectivity
• Interface between various systems and databases
  through the use of SQL
• System supports include
• VC, VB, Delphi
• ASP, Perl
• Java
• Other development platform

14
ODBC (contd)

• Databases include
• MS Access
• MS SQL Server
• Oracle
• Sybase
• Capability supported varies between different
  database systems

15
SQL Basics

• Simple Query Language
• Standardized language to for database
  manipulation
• Common commands
• SELECT
• UPDATE
• INSERT
• DELETE

16
SELECT

• Create a listing, or a view of the database with
  different criteria
• Example
• SELECT FROM Department WHERE DepartmentID2
• This statement will list out the company with ID2

17
SELECT (Contd)

• The denotes returns all fields in the table
• If you just want SQL to return particular fields
  you may write
• SELECT DepartmentID, DepartmentName FROM
  Department
• WHERE DepartmentID2

18
SELECT (Contd)

• It is also possible to join relevant records in
  different table into one view
• The following statement returns records of every
  staff name, their extension as well as the
  corresponding department name they are in
• SELECT DepartmentName, StaffName, StaffExtension
  FROM Department, Staff
• WHERE Department.DepartmentIDStaff.DepartmentID

19
UPDATE

• Set the value of a particular field for record
  satisfying certain criteria
• Example
• UPDATE ANTIQUES SET PRICE 500.00
• WHERE ITEM 'Chair'
• This statement will set the field price to 500
  for all record having ITEMchair

20
INSERT

• Insert a new record to a table
• Example
• INSERT INTO ANTIQUES VALUES
• (21, 01, 'Ottoman', 200.00)
• This will insert a record with field equals to
  the values in the parentheses

21
INSERT (Contd)

• You may also specify which field to set in the
  INSERT statement
• Example
• INSERT INTO Staff (StaffID, StaffName) VALUES
• (1, Louis SHun')
• Be aware that other fields in the newly created
  record will be left empty, which may cause error
  because some field may be required to fill in

22
DELETE

• Delete records from the table satisfying certain
  criteria
• Example
• DELETE FROM ANTIQUES WHERE ITEM'Ottoman'
• This statement deletes all records in table
  ANTIQUES having ITEM equals to Ottoman

23
Manipulating Database with ASP

• Four main steps
• Prepare database connection string
• Connect to the database
• Prepare SQL statement
• Execute the statement
• After using the database
• Close record set (if needed)
• Close connection

24
Typical ASP Code

• Typical codes
• sqlstr SELECT from Styles WHERE
  CID" CID
• set adoconServer.CreateObject("ADODB.Connecti
  on")
• adocon.Open "UIDPWDDSNASPWorkshop
• set records adocon.Execute(sqlstr)
• sqlstr "INSERT INTO Styles VALUES ('"
  CName "','text','" text "', "
  CID ")
• adocon.Execute(sqlstr)
• records.close
• adocon.close

25
Using record sets

• Create the record set
• set records adocon.Execute(sqlstr)
• Move the next record
• records.movenext
• Access to a field
• records(ITEM), records(0)
• Check if more
• records.EOF

26
Record set example

• Typical codes
• sqlstr SELECT from Styles WHERE
  CID" CID
• set adoconServer.CreateObject("ADODB.Connecti
  on")
• adocon.Open "UIDPWDDSNASPWorkshop
• set records adocon.Execute(sqlstr)
• response.write lttablegt
• while (not records.eof) do
• response.write lttrgtlttdgt
• response.write records(StyleName)
• response.write lt/tdgtlttdgt
• response.write records(StyleDesc)
• response.write lt/tdgtlt/trgt
• records.movenext
• wend
• response.write lt/tablegt
• records.close
• adocon.close

27
Common problems

• URL with parameters
• Various SQL Exceptions
• Multilingual Support

28
URL with Parameters

• In ASP, one often needs to generate ltagt tags with
  parameters I the URL such as
• ListStaffbyName.asp?nameLouis
• such URL should be OK. But what if the name
  variable contains special characters such as
  space?
• To successful pass special characters thru
  explicit parameterization, one needs to encode
  the URL to form such as
• ListStaffbyName.asp?nameLouisShun
• Use URLEncode method
• Server.URLEncode(string)

29
SQL Exceptions

• Most SQL exceptions can be easily debugged by
  examining the error messages carefully
• There are two types of except that are trickier
  to debug
• Exception Occur
• Special Characters in values

30
Exception Occur

• A lot of time you get this error even though you
  cannot find anything wrong with your codes
• Then you have to debug the code line by line
• If you are using record sets, make sure you
  access fields of a record in sequence from left
  to right

31
Special Characters in SQL Statement

• The only special characters you need to beware of
  is single quote ()
• Use Replace function to insert additional single
  quotes into the statement such as
• haha Toms Shop
• haha Replace(haha, , )
• sqlstr SELECT FROM Client WHERE
  ClientCompany haha

32
Multilingual Support

• Its possible to automatically detect clients
  language preference by reading the
  HTTP_ACCEPT_LANGUAGE
• You may generate codes like
• if Server.ServerVariables(HTTP_ACCEPT_LANGUAGE)
  en-us then
• lt!--include fileenglishversion.asp--gt
• else
• lt!--include filechineseversion.asp--gt
• end if

33
Practice Activities 2

• A calendar which shows your appointments
• Lab2/index.html diary link
• Mkdir diary and upload the code
• There is a problem no password/test
• Another problem cannot update appointment
• How can we fix it? UPDATE

34
What you should know

• Basic database design for ASP
• Querying a database via ASP
• Manipulating records with ASP

35
Feedback/Debrief

• Would your school develop a web authentication
  set up for your students?
• How would this ASP lesson helps?