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