See discussions, stats, and author profiles for this publication at: https://www.researchgate.

net/publication/332346954

Learning Database through Developing Database Web Applications

Article in International Journal of Information and Education Technology · January 2019

DOI: 10.18178/ijiet.2019.9.4.1207

CITATIONS READS

2 5,190

1 author:

Ching-Yu Huang
Kean University
59 PUBLICATIONS 538 CITATIONS

SEE PROFILE

All content following this page was uploaded by Ching-Yu Huang on 18 May 2019.

The user has requested enhancement of the downloaded file.

 International Journal of Information and Education Technology, Vol. 9, No. 4, April 2019

Learning Database through Developing Database Web

Applications
Ching-Yu Huang

 customers to access the DB directly without any application

Abstract—Databases play a very important role in the interface. Therefore, developing DB applications to help
modern software system, especially in the big data age. It is the users (staff and customers) access data is very important for
centralized repository for all kinds of data. Therefore, all scaling up any business. Since web-based applications are
students majoring in technology and computer science related
fields should take database courses. However, learning about
becoming essential for various industries, a web DB
databases could be challenging without hands-on exercises. It is application should one of the main focuses for projects in any
very important for students to be familiar with database DB course.
commands and the associated clauses so they can write better In order for a computer language to communicate with the
database queries. Since the data resides in the database, it is DBMS, specific drivers are required for each language.
hard for students to imagine the results, especially if there are a Different DBMS require users to install different DB drivers,
lot of records. Visualizing the intermediate results will help
students to learn how to write better queries and troubleshoot.
which are usually provided by the vendors. In addition, many
It is not practical to just use the command line to interact with DB drivers are Operating System (OS) platform dependent,
the database server. Students must learn the industry standard especially for C, C++, and other compiled languages.
3-tier method to retrieve, insert, update and delete thousands of Scripting languages such as Perl and Python use a standard
data from databases, and how to write web programs to do so. DB library across different platforms [3], [4]. Even through
Designing an E-commerce platform should be the core of Java is a platform independent programming language, it still
fundamental database curriculum - including a friendly graphic
requires connecting packages which are OS dependent. Now,
user interface for customers to sign up for accounts, search
items, place orders and view histories. The instructors should more and more languages are being introduced which better
not focus on teaching the database topics, but also show accommodate the integration between web and database
students how to manage the database server and user accounts servers. These languages, which are considered server-side,
in the classes. This paper proposes an integrated curriculum for must be run on web servers. For example, Hypertext
learning about databases through developing database web Preprocessor (PHP) [3] is one of the most popular languages
applications.
for those working on web DB applications. Apache [4], a free
Index Terms—Apache, database web applications, PHP,
open source web server, has a built-in module that allows
MySQL, XAMPP. users to integrate PHP with MySQL [5].
It is difficult for DB instructors to set up and manage both
Web and DB servers and manage student accounts, while
I. INTRODUCTION teaching DB courses at the same time. An integrated software
package, XAMPP, was introduced in 2002 which helps
Before the first commercial Database Management System
combat this issue [6]. The package is cross platform (X), and
(DBMS) was introduced by Oracle in late 1970s [1], teaching
it includes an Apache (A) web server and a MySQL (M)
Databases was mainly based on theoretical concepts. Even in
database, as well as Perl (P) and Python (P) compatibility.
late 1980s, most schools could not afford to buy the
The latest XAMPP also has phpMyAdmin – a free and open
expensive DBMS, preventing students from attaining
source administration tool for MySQL and MariaDB – and
hands-on experience. After free and open source relational
Tomcat, a web server for Java Servlet. Additionally, XAMPP
DBMS, such as MySQL [2], was made available to the public
already has PHP built-in. It is available for Windows, iOS,
in the mid-1990s, students were able to utilize DB for
and Linux systems and does not require powerful hardware.
projects. Although MySQL originally did not have many This allows students to easily install XAMPP on their laptops,
functions during its early developmental stages, performing and run an Apache web server and a MySQL server at the
basic SQL queries significantly helped students gain same time. Once students are familiar with the client-server
hands-on experience for data management. model, it will be easier to pick up the 3-tier architecture that is
Since the data is stored in the database, the ability to often used in the real world, where each is typically stored on
retrieve and display data is critical. Even though the DBMS a separate machine.
provides both a command line interface and a Graphic User This paper will utilize XAMPP as a part of the proposed
Interface (GUI) for users to retrieve, insert, update, and curriculum and hands-on exercises. MySQL will be used as
delete records, it is often difficult for non-computer science the database to learn SQL, phpMyAdmin [7] will be used for
employees to write SQL statements through these interfaces creating users and managing MySQL DBMS, and MySQL
to manually work on the DB. It is impossible for external Workbench – a visual administration tool to manage MySQL
DB – will be used as the GUI for writing SQL queries and
Manuscript received September 12, 2018; revised December 4, 2018.
viewing the results. PHP and Apache will be the languages
Ching-Yu Huang is with School of Computer Science, Kean University,
Union, New Jersey, USA (e-mail: chuang@kean.edu). used for developing web applications.

doi: 10.18178/ijiet.2019.9.4.1207 241

 International Journal of Information and Education Technology, Vol. 9, No. 4, April 2019

II. DATABASE CURRICULUM 3) The following SQL statement shows the unique
Relational DB [8] are often adopted for teaching purposes department for any staff who has name contains the
because it still accounts for the majority of the DB job market; letter “e”.
thus, they will be the focus of the curriculum. The proposed SELECT distinct dept from Staff WHERE name like
DB curriculum is based on 40 total hours - 32 hours of total '%e%';
lesson time (13 weeks and 2.5 hours per week) and 4 exams, 4) The following SQL statement shows the names of all
with 2 hours allocated for each, including reviewing the staff who are female and have a salary >= 40000.
answers. Students should have at least a basic level of SELECT name FROM Staff WHERE sex='F' and salary
programming knowledge with a good foundation in variables, >= 40000;
loops, logic conditions, if statements, arrays, and functions. 5) The following SQL statement shows the names and
The curriculum will build on this prerequisite knowledge by salaries of all staff that work at IT department, with the
focusing on basic DB knowledge and its application. output sorted from high to low (in salary).
Theoretical concepts such as relational calculus, and SELECT name, salary FROM Staff WHERE dept=’IT’
advanced topics such as triggers, transactions, concurrency ORDER BY salary DESC;
control, query Processing & optimization, DBA, and data TABLE I: STAFF TABLE
mining are not covered. The assignments include 2 sid name dept sex salary ext
homework assignments and one web DB application project, 101 Mary IT F 45000 121
with two phases. 102 Smith HR M 37000 NULL
103 Tony IT M 38000 115
Instructors should prepare several tables with records and 104 Sarah PJ F 35000 311
export the tables into SQL files for students to download. 105 Mark IT M 45000 153
This way, students can import the tables into their local 106 Alice HR F 39000 433
MySQL server and work on the same tables and dataset as 107 Ben IT F NULL NULL
that of the instructors. Consequently, everyone should obtain
the same results when doing exercises with the same SQL C. Web DB Application
statements. If students have any difficulties, instructors can In the 3rd week, students should learn how to display the
quickly help the students. Students should do more exercises data from the DB on the browser after learning the basic
on basic SQL operations, and should not be limited to the SELECT statements. This should be done twofold: first, the
examples shown in this paper, as this is not intended to be a instructor should quickly cover basic web design and how to
textbook. The following subsections detail the weekly take input and pass the values to PHP programs on the
curriculum. Apache web server. Then, students should learn how to use
A. Introduction of DB PHP to retrieve the data from the DB using the input values.
The first week of lessons should include the following The main goal of this paper is to focus on learning the basic
topics: class policy, the course schedule, an overview of the DB queries and integrating the DB and Web. This means
DB curriculum, basic DB concepts, and the role of DB in only basic web programming is required to show how the
modern software systems. The fundamental knowledge of the web and DB work together. Students should take web
client-server model and 3-tier architecture should be covered. programming courses for learning how to design better web
Instructors should also help students install the XAMPP pages and more aesthetic GUI. Project 1 should be given in
package, and make sure the MySQL and Apache web servers the 3rd week and should be due at the end of the 7th week. The
can be run successfully. details of the project are described in section 2.4.
1) Input on web page (browser)
B. BASIC SQL — Select, Where, Sorting
Hypertext Markup Language (HTML) is a markup
The 2nd week should cover basic concepts of tables, rows,
language to create web pages and web applications using tags
columns, and data types. This week focus on the SQL
<>. Many HTML tags have attributes that can modify
SELECT command and its syntax, with AS, LIKE,
elements, specify tags’ values, or perform specific actions.
DISTINCT, ORDER BY, AND, OR, NOT, and WHERE
The browser will interpret the tags and attributes, with
clauses. Students should be able to select specific records and
specific syntax, that will take input and display results. The
columns with simple and compound conditions. In addition,
HTML file should have the *.html extension and should
one should cover how to sort the output. Homework 1 should
begin with an <HTML> tag and end with an </HTML> tag.
be given in the 2nd week, and due at the end of the 6th week. It
Fig. 1 shows the HTML source code of a simple web page
should mainly focus on the SELECT queries. As a result of
that allows a user to enter input. The web page is shown in
this week’s curriculum, students should be able to write the
Fig. 2. Most HTML tags require end tags </> to match the
following SQL statements based on Table I, a sample table of
begin tag <>.
staff members at a company.
The message written between <TITLE> and </TITLE>
1) The following SQL statement shows all columns, for all
will be displayed as the title of the web page. The <BR> tag
staff.
creates a new single line break. The <input> tag specifies an
SELECT * FROM Staff;
input field where the user can enter data. <input> elements
2) The following SQL statement shows the salary and
are used within a <form> element to declare input controls
department for any staff named Mary.
which allow users to input data [9]. An input field can vary in
SELECT salary, dept FROM Staff WHERE name='Mary';
many ways, depending on the type attribute. The first

242
 International Journal of Information and Education Technology, Vol. 9, No. 4, April 2019

<INPUT> tag allows users to enter text, and the 2nd values of the corresponding name in the array (note that the $
<INPUT> tag is a submit type which will ask the browser to sign is used to indicate variables in PHP). For example, the
pass the values of <INPUT> tags, which are placed between following statement will assign the ‘keyword’ value of
<FORM> and </FORM> tags, to the program on the web <INPUT> tag in Fig. 1 to a PHP variable $value.
server. More information about HTML tags can be $value=$_GET['keyword'];
referenced at w3schools.com [10]. 4) Access MySQL using PHP
PHP has built-in functions to work with MySQL DB [12].
<HTML> The basic steps are as follow: 1. Establish a connection to the
<TITLE>Test</TITLE>
DB on a server with a valid login and password. 2. Send the
Web DB application: search department
<BR> query string. 3. Receive the results and place them in a PHP
<FORM action='check.php'> array. 4. Check the number of rows in the array. 5. Fetch all
Department: the records in the result array. 6. Free the result array and
<INPUT type='text' name='keyword' method=’GET’> close the connection. A sample program that follows this
<INPUT type='submit' value='Check'> procedure is shown in Fig. 3. The program connects to the
</FORM>
“test” database on the “localhost” server using the login
</HTML>
“tester” and password “1234”.
Fig. 1. The HTML source code of test.html.
If the DB connection fails, the program can use
mysqli_connect_error() to show the error message returned
from the DB; the program should terminate immediately and
display the error message. It will be a waste of the web
server’s resources if the login fails but the program continues
to run the rest of the statements.
As shown in the following PHP statement, a variable $sql
Fig. 2. The display on the browser from the HTML code shown in Fig. 1. stores the query string which selects the names and salaries
from the Staff table with condition dept='$value' (as assigned
2) Common gateway interface (CGI) in section 2.3.3).
A browser uses the CGI protocol to send the information to
a web server [11]. When the input values and other $sql= "SELECT name, salary FROM Staff where dept='$value'";
information shown on the browser are passed to the web
Please note that if the attribute is not a numeric type, the
server using the <FORM> tag, the browser also tells the web
PHP variable $value should be surrounded with single quotes
server to call the specific program defined in the action
for logic comparisons, and numeric values should not be
attribute of the <FORM> tag. For example, the PHP program
wrapped with single quotes for comparison. For example, to
check.php, which is defined inside the <FORM> tag in Fig. 1,
compare the salary column with the PHP variable, the correct
is called with the specific input. In order for this to work, the
comparison statement is salary=$value (to see if they are
program should be able to be executed directly by the OS
equal).
where the web server is running. Most of the CGI programs
The function mysqli_query() will send the query string to
are nowadays written in scripting languages because it is
the DB through the variable $conn. If the query is
easier to maintain. When the web server receives a message
successfully executed by the DB server, the returned results
from the browser, it will trigger the OS to call the language
are saved into the 2-D array $result. The function
interpreter to run the program.
mysqli_num_rows() returns the number of records stored in
There are two methods can be defined in the <FORM> to
the array. The function mysqli_fetch_assoc() will fetch a
pass the information from the browser to the web server –
record each time from the 2-D array and the record is saved to
GET and POST. The GET method will show the message
a 1-D array $row. The while loop will read every record in
string (name/value pairs) that is sent in the URL. Many
the 2-D array. The values of name and salary can be retrieved
search functions are implemented in the GET method so
from the 1-D array $row using the table column name
people can change keyword values in the URL. This will
“name” and “salary” as the keys. The results are displayed on
allow other programs to call the URL directly and obtain the
the browser using the <TABLE> tag and its related tags:
results. The POST method delivers a message in the HTTP
table row tag <TR>, table header tag <TH>, and table
message body, which is not visible from the URL. If the
data/cell tag <TD>.
message contains confidential information, such as a
Fig. 4 shows the results of a search with the keyword “HR”,
password or other information that should not be visible, it is
displayed in a table with 2 columns (name and salary) and 3
better to use the POST method.
rows. If the search does not yield any results, a warning
3) Web CGI programming using PHP message should be displayed so users know what happened.
The PHP program that the programmer wishes to call Fig. 5 shows the results if no record is found (i.e. if the search
should be written between the strings “<?php” and “?>”. For keyword is “XY”). For the 3-tier architecture commonly used
more PHP reference materials, tutorials for PHP can be found with web DB applications, bugs and problems might arise at
at w3schools.com [11]. Once the CGI program is called, it front-end, middle-end, or back-end. Therefore, it is important
will receive all the <input> tags’ names and values. In PHP, to keep users informed with meaningful messages about the
special arrays $_POST[] and $_GET[] are used to get the running status.

243
 International Journal of Information and Education Technology, Vol. 9, No. 4, April 2019

Students should take Quiz 1 (preferably 60 minutes long)

which should cover the first 3 weeks of material, including
homework 1 and project 1. Instructor should review the
answers and discuss the homework and project assignments.
Project 1 should require the students to create their first DB,
complete with at least two components: 1) the ability to login
with a username and password, and 2) the ability to retrieve
information correctly. Note that this is a basic framework for
a project, and details and implementation should be up to the
instructor.
1) Retrieve all data from a “User” table provided by the
instructor and display the results on the browser, as
shown in Fig. 7.

Fig. 3. PHP source code receives the input department value from the
browser and displays all staff names and salaries if the input department is
found in the DB.
Fig. 7. Display all the users and their columns from the DB.

2) A sample login page is shown in Fig. 8, and lets people

enter a login ID and password. The program will verify
the login ID and password with the information in the
Users table in the DB.

Fig. 4. The results displayed on the browser when the input department is
“HR”. The “keyword” and its associated input value “HR” are displayed in Fig. 8. Login page.
the URL.
3) If the login ID and password match the records in the
database, the program should show the user’s IP and
other information, as shown in Fig. 9.

Fig. 5. No record is found when the input department is “XY”.

Fig. 9. User home page.

Fig. 6. The PHP program should show error messages if “keyword” is not E. SQL — Constraints; Creating Tables and Temporary
used, or if there are no input values passed through GET method. Tables; Alter and DROP
Students should learn how to create simple tables in this
If the PHP program check.php is run without using the
week as well as how to use constraints - datatype, primary
GET method with “keyword” and an input value, the
key, foreign key, auto increment, NOT NULL, default values,
program should terminate immediately and display an error
and possible range. Please note that it is very important to
message “No keyword is entered!”, as shown in Fig. 6. This
educate students on the impact of the data size. One extra
way, the web server will not waste any resources, as
byte of a column could result in an additional 1GB if there are
aforementioned. Since the web application can be accessed
1 billion records. Therefore, students should properly choose
by many people, it can be costly if preventative measures are
the data types; i.e. picking between char and varchar types, or
not taken. It is very important to teach students how to protect
picking between ints and strings.
the servers in the client-server architecture.
1) The following SQL statement details how to create a
The ALTER command can change the table structure; for
table named “Test” with 2 columns (id, name), where id
instance, it can add or remove a column, or change the
is of int type and name is of varchar type. Note that id
datatype and constraints. The DROP command will remove
will be set as the primary key and the value will
the entire table or any object from the DB. The commands
auto-increment.
CREATE, ALTER, and DROP are called Data Definition
CREATE TABLE Test (id int primary key auto_increment,
Languages (DDL) because their effects can be seen at the
name varchar(50));
structure level.
2) The auto_increment rule will let DBMS automatically
D. QUIZ 1 and Project 1 increment the value of the “id” column. The value is

244
 International Journal of Information and Education Technology, Vol. 9, No. 4, April 2019

indexed from 1 and is increased sequentially. It is good (with 10 columns).

to use auto_increment when an attribute is required to SELECT * FROM Staff s, Department d WHERE
have unique and sequential values, i.e. when identifying d.dept=s.dept;
the objects, such as product ID, student ID, etc. When 3) Since the column name “dept” is the same in both Staff
designing the web interface for the user to enter data for and Department tables, the statement in 2.7.2 statement
the columns, the interface should not allow the user to can use a “NATURAL JOIN”, which will join the two
manually add the new value of the auto_increment field. tables but remove the duplicated column, “dept”. This
3) A temporary table can be created during the connection. results in only 9 columns.
Once the user logs out or the connection is closed, the SELECT * FROM Staff s NATURAL JOIN Department d;
temporary table will be released from memory. Since 4) The following statement shows the address, city, and
SQL does not have arrays, temporary tables can be used state where the staff member “Tony” is working at.
like 2-D arrays to store the intermediate results SELECT s.name, d.address, d.city, d.state FROM Staff s,
generated by some queries. The following SQL Department d WHERE d.dept=s.dept and s.name='Tony';
statement will create a temporary table named Tmp. 5) The following statement uses LEFT OUTER JOIN to list
CREATE TEMPORARY TABLE Tmp(id int, name each department, along with the staff members working
varchar(100)); in each department. If the department has no staff, the
4) The following SQL statement adds a new column columns that correspond to staff members will be filled
“price” before the name column (and after id) in the Test with NULL values, as shown in Fig. 9.
table. SELECT * FROM Department d LEFT JOIN Staff s ON
ALTER TABLE Test add column price float after id; s.dept=d.dept;
5) The following SQL statement removes the entire table
from the DB.
DROP TABLE Test;
F. SQL — Join and Set Operations
In a properly designed DB, different information should be
kept in different tables, so tables won’t have irrelevant data. Fig. 9. The output of the LEFT JOIN query in 2.6.5.
The address, city, state, and other information of where a
staff member is working at is stored in a separate table named The LEFT JOIN will force all records in the table on the
“Department”, as shown in Table II. The reason why the data left side (e.g. for the sample, this is the Department table) to
should be separated into two tables will be discussed in the be shown in the output. There is also RIGHT OUTER JOIN -
normalization section later. In order to find the address that all records in the table on the right side will be displayed, and
the staff member “Tony” is working at, it should be required FULL JOIIN – all records in both tables will be displayed.
to first use his name to get his “dept” code “IT” in the Staff G. SQL — View, Aggregate Functions, and Null
table. Then, one should use the “IT” code to retrieve his In this week, students should learn about view, understand
address from the “address” column in the “Department” table. what an updatable view is, and know how to create views and
The process of getting information from two or more tables is updatable views. The instructor should cover how to use the
called “join”ing. 5 basic aggregate functions – COUNT, AVG, SUM, MIN,
There two kinds of “join” – inner join (also called MAX, how to handle NULL values, and how to use the
INTERSECT), which displays the common values between following set operations – INTERSECT, UNION and
tables, and outer join, which shows records in either table. DIFFERENCE. If a column value is unknown, it should be
Often, one will use the “join” operation with a common stored as NULL and should not be an empty string or ‘0’.
column to associate two tables at once. Without the common Instructors should explain and show that NULL values will
column condition, all combinations of two tables will be impact the results of aggregate functions. The following
displayed. This situation is called “Cartesian product”. examples are based on Table I.
TABLE II: DEPARTMENT TABLE 1) The following SQL statement shows the number of
dept address city state female staff. Please note that the output header will also
HR 12 Main Ave. Union NJ contain the aggregate function, COUNT, i.e.
IT 100 Broad St. New York NY “COUNT(sex)”, and NOT just “sex”. This is shown in
PJ 28 Union Ave. Boston MA
SL 66 Central Ave. Houston TX
Fig. 10 below.
SELECT COUNT(sex) FROM Staff WHERE sex='F';
1) The following statement shows all combinations of
joining the Staff and Department tables, which has 24
rows (6 rows from Staff x 4 rows from Department) and
10 columns (6 columns from Staff + 4 columns from Fig. 10. Result of 2.7.1.
Department).
2) When the web DB application needs to retrieve values
SELECT * FROM Staff, Department;
from an aggregate function, it is required to rename the
2) The following statement uses an inner join to display the
output header. The following PHP statement will change
columns in the Staff and Department tables that contain
the output header from “COUNT(sex)” to “ct” in the
common values in the “dept” column, a total of 6 records

245
 International Journal of Information and Education Technology, Vol. 9, No. 4, April 2019

SQL query. See the output in Fig. 11 below. id, in the “User” table. Vendor_id is also a foreign key
$sql="SELECT COUNT(sex) AS ct FROM Staff WHERE that references the primary key id in the “Vendor” table.
sex='F'"; Both the User and Vendor tables should be provided by
the instructor.
2) 2.8.2 Implement a function to search for products, as
shown in Fig. 12. The keyword should be pattern
Fig. 11. Result of 2.7.2. matched against the product name and description. If no
product is found, a proper message should be displayed.
After the name is changed, the following PHP statement
can use the meaningful key “ct” to retreive the value returned
from the DB.
Fig. 12. A user can enter a keyword to pattern match products by name or
$myCt=$row['ct']; description in the Student’s “Products” table.
3) The following SQL statement shows the maximum
salary of IT department. 3) Create an add product page, as shown in Fig. 13. The
SELECT MAX(salary) FROM Staff where dept = 'IT'; program should require that all data input fields not be
4) The following SQL statement shows the number of empty and for the numeric fields to not be negative (as
people that have an extension number. An instructor well as within a reasonable price range). The sell_price
should discuss how to use IS and IS NOT to find or must also be greater than the cost. These errors should be
exclude the NULL values. caught at the front-end, by JavaScript, to reduce the web
SELECT count(ext) FROM Staff; server’s load. If the same product already exists in the
The statement can also be written as: table, an error message should be displayed. If there are
SELECT COUNT(SID) FROM Staff WHERE ext IS NOT any errors about the data, the record should not be
NULL. inserted into the DB. The vendor id and name should be
5) The following SQL statement shows how to use the retrieved from the Vendor table and only the name
MAX, COUNT, SUM and AVG functions; remember should be displayed in the dropdown list on the web
that they are impacted by the presence of NULL values. page.
SELECT COUNT(salary), SUM(salary), AVG(salary),
MAX(salary) from Staff;
6) The following SQL statement shows the logic operations
AND and OR with NULL values. Please note that TRUE
is represented by 1 and FALSE is represented by 0 in
MySQL.
SELECT 1=1, 'a'='b', 1 AND 0, 1 OR 0, 1 AND NULL, 1 Fig. 13. An example implementation of 2.8.3.
OR NULL, NULL AND NULL, NULL OR NULL;
7) The following SQL statement will create a view vTest 4) Create an update product page, as shown in Fig. 14. The
that is not updatable. columns highlighted in yellow are not updatable. The
CREATE VIEW vTest AS SELECT dept, count(*) ct FROM Add Product checking rules (outlined in 2.8.3) should be
Staff GROUP BY dept; applied to the 4 updatable columns. Staff members
should be able to update multiple records at once. This
H. Midterm and Assignments
means that the instructor should teach students how to
The 75-minute midterm exam should cover topics from pass and receive <INPUT> in an array format, though
week 1 to 7. Instructor should also review the answers CGI. The following shows how one can use HTML to
following the exam so students know and understand the pass the product_id in an array format with the value 3
mistakes they made. Assignments Homework 1 and Project 1 and in hidden type.
should have been due in the end of the 6th and 7th week, <input type='hidden' name='product_id[]' value='3'>
respectively. The instructor should provide general feedback The following PHP code shows how to calculate the
for the students, so they know how to improve for the future. number of products passed from the browser, and how to
Homework 2 and Project 2 should be given to the students receive the product_id in an array format.
this week, and they are should be due in the 12th and 14th for($i=0;$i<count($_POST['product_id']);$i++) {
week, respectively. Homework 2 mainly covers SQL joins $product_id[$i]=$_POST['product_id'][$i];
and stored routines. }
Project 2 focuses on the integrating the learning in a
practical environment, with the following requirements I. SQL — Group by, Having, Insert, Update, Delete,
(2.8.1 to 2.8.4). The “search product” function should be Subquery, and in
available to anyone without login. For a user whole role is This week should cover how to group datasets using
“Staff”, they should be able to “Add” and “Update” product GROUP BY, add new records using INSERT, change data
pages. content using UPDATE, and remove a record using DELTE.
1) Create a “Product” table (id, name, description, From experience, grouping is one of the most challenging
sell_price, cost, quantity, user_id, vendor_id) where topics in SQL. The commands – SELECT, INSERT,
user_id is a foreign key that references the primary key, UPDATE, DELETE are called Data Manipulation Language

246
 International Journal of Information and Education Technology, Vol. 9, No. 4, April 2019

(DML), since their effects can be seen on the data level. (outer query). DBMS will need to execute the inner
query first to get the results for the outer query to use.
The following SQL statement uses subqueries to find the
person’s name who has the maximum salary in “IT”
department. The result is shown in Fig. 17, based on the
data in Table 1.
SELECT name, salary FROM Staff WHERE
salary=(SELECT MAX(salary) FROM Staff WHERE
dept='IT');
Fig. 14. An implementation of 2.8.4.

Students are often confused between DELETE and DROP,

and UPDATE and ALTER. It is very important that
instructors ask students to do more exercises in class so they
Fig. 17. The correct result of 2.9.6.
are able to grasp the differences between the two. The
following examples are based on Table 1. Please note that the following SQL query will produce the
1) The following SQL statement shows the number of staff wrong result, as shown in Fig. 18.
in each department. SELECT name, MAX(salary) FROM Staff WHERE
SELECT dept, COUNT(dept) ct FROM Staff GROUP BY dept='IT';
dept;
2) The following SQL statement shows the number of staff
in each department with at least 1 female staff member.
SELECT dept, sex, COUNT(dept) ct FROM Staff where Fig. 18. The wrong result of the incorrect SQL query in 2.9.6.
sex='M' GROUP BY dept, sex having ct >=2 ;
Based on Table I, the results of the previous two queries The DBMS will find the correct maximum salary in the
are shown in Fig. 15 and 16, respectively. “IT” department. Since MAX is an aggregate function that
produces a single value from populating several records,
DBMS is not able to associate the name with the MAX
function in one step. Therefore, it will pick the first name it
finds in the table that matches the maximum salary. It is
necessary to use a subquery to find the maximum salary of
Fig. 15. Result of 2.9.1. the ‘IT’ department, and then output all names that have this
maximum salary value. This is because it is possible to have
more than 1 maximum salary, as shown in Fig. 17.
Fig. 16. Result of 2.9.2. J. SQL — Variables and Stored Routines
So far, students have learned SQL by using singular
3) The following SQL statement shows how to increase statements. This is not real programming. Like learning any
“Sarah”’s by $1000. computer languages, it is very important to learn how to write
UPDATE Staff SET salary=salary+1000 where procedures and functions. In SQL, they are called stored
name='Sarah'; procedures and stored functions because programs will be
4) The following SQL statement shows how to add a new stored on a DB server after DBMS compiles the statements
record with id=107, name=’Will’, dept=’PJ’, sex=’F’, (if there are no syntax errors). In MySQL, stored routines is a
salary=37000, and ext is unknown. general term that refers to both routines and procedures.
INSERT INTO Staff (id,name,dept,sex,salary,ext) VAUES Before students can write a stored routine, they have to
(1, 'Will', 'PJ', 'F', 37000, NULL); learn about 3 types of SQL variables – local, session, and
Since all column values are provided, the SQL can also be: global. Students also must learn how to assign a value
INSERT INTO Staff VALUES (1, 'Will', 'PJ', 'F', 37000, (constant or variable) to a variable. There are 3 types of
NULL); arguments for a procedure – IN, OUT, and INOUT – but
5) The following SQL statement shows how to delete the functions only have an IN type. In addition, instructors
record added previously. should cover the SQL functions CONCAT() and
DELETE FROM Staff WHERE name='Will'; GROUP_CONCAT(), and how to use LIKE for pattern
Instructors should emphasize that all records will be matching an input argument in stored routines.
updated with the same values, or all records will be removed K. Security, Grant and Revoke
from the DB, if the WHERE condition is not provided. Web DB security can be classified into 4 the following
Since the INSERT, UPDATE, DELETE commands will types:
not return any results, it is very important to know if the
1) DB user and privilege management. This has to be
command was run successfully in the PHP program. The
done by the following SQL commands: GRANT, which
function mysqli_affected_rows() should be used to detect the
can give particular privileges to specific objects in the
number rows affected.
DB (i.e. also control which users can access the
6) A subquery is a query (inner query) inside another query

247
 International Journal of Information and Education Technology, Vol. 9, No. 4, April 2019

privileges from which hosts), and REVOKE, which can from the requirements, students should also learn how to
remove the privileges. The following SQL statement convert E-R diagrams to schema diagrams.
gives the SELECT privilege on the Staff table in the Test
O. Normalization and Physical Design
database to a user named ‘tester’, who can access the DB
from any host (%). For normalization, the instructor should cover traditional
GRANT SELECT ON Test.Staff to tester@% with grant decomposition, functional dependency, loss-less join, and 1st,
option; 2nd and 3rd normal forms. The Boyce–Codd normal form can
2) Web page authentication (e.g. login). This often be optional. Students should be able to break a big table into
requires the DB designer to store user login and several smaller tables and set the primary keys and foreign
password information in a table, for example, the “User” keys to link the smaller tables together.
table in the class project. The login will use the POST Physical designs include creating the following objects in
method to pass the login and password to the middle-end the DB: project database instances, user accounts with proper
program, which will verify with the data in the DB. The privileges, stored routines, and tables based on the schema
password should be encrypted by SHA256, or a better diagrams with the necessary columns to be indexed.
hash function. The password should not be readable Instructors should also teach how to estimate physical disk
directly select from the table. For example, Fig. 7 is a sizes for all the tables and recommend the partition size that
poor implementation of the password. will store the database files. It is also important to show
3) DB web page authorization through role control. students how to check where the database files are located on
Another way to improve web DB application security is the server. For example, the following SQL statement shows
to use roles to control who can access what web pages or the data folder for MySQL:
functions. The role can be a numeric level or a string, i.e. SHOW VARIABLES LIKE 'datadir';
“staff”, “student” shown in Fig. 7. The search function Physical design should not only cover database design, but
shown in Fig. 12 can be accessed by anyone, but the add should also include application design. Since project 2 is due
product page shown in Fig. 13 and update product page at the end of 14th week, the instructor should give overall
shown in Fig. 14 should be only accessed by “staff”. feedback about students’ projects and homework, especially
4) Using cookies and sessions to control when the web how the implementation relates to physical design – data type
page should expire. Authentication cookies are the most and validation, data flow between the 3-tier architecture, the
common method used by web servers to know whether relationship between tables, and date storage locations on the
the user is logged in or not, and which account they are DB server.
logged in with [13]. P. Final Exam
L. QUIZ 2 and Project 2 The exam should have two components – a take-home
The 60-minute Quiz 2 should cover materials from week 9 database design and in-class individual written exam. The
to 11, and the answers should be reviewed. Instructors should take-home is a teamwork assignment that requires a team to
use this week to check the status of project 2 and help have 5-8 students and design a mini database application.
students improve their web DB programming skills. Each team is given two forms – one for registration and one
for a report. The samples are shown in Fig. 19 and 20,
M. Planing and Requiremens respectively. Each team should have 4 different roles –
It is better to discuss DB planning and requirements after business staff, system engineer, database designer, and
students have completed their first web DB application software developer.
assignment, which will be discussed in section 3. This way, The business staff should write several business rules
they already have hands-on experience and know about the based on the two forms. The following are examples based on
DB features, function, and GUI firsthand and understand the the two forms:
importance of these requirements. In order to mimic the real 1) The registration should take 4 data fields.
world, instructors could have a group take-home exam after 2) The product information should not be shown in the
students learn all the basic SQL queries and let students play URL when they are passed to backend.
different roles (system engineer, database designer, software 3) The Graphic User Interface is based on the browser.
developer, and business staff) to plan a design for a 4) The ID should be automatically assigned by the system.
mini-online store and write the requirements. This way, The system engineer should write the detailed
students will have the opportunity to brainstorm together and requirements based on the business rules and the forms.
work as a team. Teamwork is very critical in web DB The database designer should draw the E-R diagram,
applications. create table statements, and perform any tasks related the
database.
N. Logic Design and E-R Model
The software developer should write the detailed code for
Traditional DB textbooks often introduce logic design and both front-end and back-end. The code should include the
the E-R model early in the course. However, from teaching following:
experiences, many students won’t be able to fully understand 1) HTML code to get user input and button to submit the
the real meaning of data modeling before they have any input to the webserver.
hands-on experience with DB. Therefore, this paper proposes 2) PHP code to validate the input data ranges before
to move the logic design and E-R model to later parts of the sending them to the database server.
curriculum. In addition to learning how to model the data

248
 International Journal of Information and Education Technology, Vol. 9, No. 4, April 2019

3) PHP code to send the query to the database for inserting proposal is to focus on hands-on experience. Students will
and retrieving records. need to write a two-phase and 3-tier comprehensive project
4) PHP code to display the results returned from the that integrates the web browser, web server, and database
database on the browser. management system. Several software programs were
The in-class exam should include all topics covered in the proposed to help students quickly complete the project.
semester, and be weighted 30% for topics covered before
midterm and 70% after the midterm. REFERENCES
[1] C. Coronel and S. Morris, Database Systems: Design, Implementation,
& Management, 13th Edition, 2018.
[2] P. DuBois, MySQL, 5 Edition, Addison-Wesley Professional, April 12,
2013.
[3] L. Ullman, PHP for the Web: Visual QuickStart Guide, 5th Edition,
Peachpit Press, July 25, 2016.
[4] A. Peicevic, Apache HTTP Server Introduction, 2nd Edition,
CreateSpace Independent Publishing Platform, February 9, 2017.
[5] L. Welling and L. Thomson, PHP and MySQL Web Development, 5th
Fig. 19. The form to register a product.
Edition, Addison-Wesley Professional, September 30, 2016.
[6] XAMPP Apache + MariaDB + PHP + Perl. (2018). Apache Friends.
[Online]. Available: https://www.apachefriends.org/
[7] M. Delisle, Mastering phpMyAdmin 3.4 for Effective MySQL
Management, Packt Publishing, February 7, 2012.
[8] J. Eckstein and B. R. Schultz, Introductory Relational Database
Design for Business, with Microsoft Access, Wiley, January 16, 2018.
[9] G. Gupta, Mastering HTML5 Forms, Packt Publishing, November 22,
2013.
Fig. 20. The report to list the products with id, name and price. [10] PHP 5 Tutorial. (2018). [Online]. Available:
https://www.w3schools.com/php/
[11] M. Wright, How To Setup A Linux Web Server, CreateSpace
III. STUDENT LEARNING OUTCOME (SLO) Independent Publishing Platform, January 16, 2014.
[12] L. Ullman, PHP and MySQL for Dynamic Web Sites, 5th Edition,
Upon completion of this course, the student will be able to: Peachpit Press, November 13, 2017.
1) Explain and write basic SQL statements using the [13] J. LeBlanc and T. Messerschmidt, Identity and Data Security for Web
Development: Best Practices, O'Reilly Media, June 20, 2016.
SELECT, INSERT, UPDATE, DELETE commands.
2) Explain and write basic SQL statements to create and Ching-Yu Huang is an assistant professor of the
drop tables, views, and stored routines. School of Computer Science at Kean University,
Union, New Jersey, USA since September 2014. Dr.
3) Explain and write SQL statements to join multiple tables Huang received a Ph.D. in computer & information
with conditions, and group and sort the results. science from New Jersey Institute of Technology,
4) Design and build web database applications with Newark, New Jersey, USA.
authentication to retrieve, display, and update data using Prior to joining Kean University, Dr. Huang had
more than 16 years of experience in the industry and
the PHP language. academics in software development and R&D in bioinformatics. His research
5) Explain and demonstrate the normalization concepts and focuses SNP genotype calling and cluster detection; image processing and
process. pattern recognition, especially in microarray and fingerprint; geotagged
images and location information reconstruction; database application
development; data processing automation; E-learning, educational
multimedia, methodology, and online tools for secondary schools and
IV. CONCLUSIONS colleges. Dr. Huang has more than 40 publications in journals and
conferences and more than 20 presentations in workshops and invited
This paper proposes a fundamental curriculum for an lectures.
undergraduate database course. The main goal of this

249

View publication stats