Welcome to TECH 3740 (Database System Concepts and Applications)

In this course, students will learn about basic SQL commands, Entity-Relationship (ER) model and diagrams, anomalies, normalization, stored routines, database designs, database development cycle, basic DBA, how to develop web database applications as well as obtain hands-on experiences. This course emphasizes development, which means a lot of codings. Students will need to have good programming skills for working on projects and good concepts about set operations and logical conditions. Please refresh the topics of conditions, if-else, while/for loop, 1-D/2-D array, and functions before the class starts.

TECH 3740 is a required course of the following programs:

28700 B.S. Information Technology
28701 B.S. Information Technology (Cybersecurity Option)
28702 B.S. Information Technology (5-year M.S. Computer Information Systems Option)
Please click here to see all CS/IT programs at Kean University.

After completing TECH 3740, students are encouraged to take CPS 4721/5721 (Data Mining) in the spring semester, and CPS 4745/5745 (Data Visualization) in the fall semester, if they are interested in Data Science.

We will cover the following topics:

Data Query Language (DQL): select, distinct, where conditions, Aggregation functions, inner join, outer join, natural join, in, exists, limit, subquery, group by, order by
Data Manipulation Language (DML): insert, update, delete, insert select, cascade
Data Definition Language (DDL): create, drop, alter, rename
Data Control Language (DCL): grant, revoke
Foreign key and other constraints
Updatable and not updatable views
Stored Routines: local variable, session variable, stored function, stored procedure, parameters (IN, OUT, INOUT)
Basic web database application: HTML FORM inputs, CGI, data entry validation, select to display, insert data into the database through the browser.
Web technology, 3-tier architecture, client/server
Database designs: Entity-Relationship (ER) model and diagrams, logical, physical, case study
Web database security: SQL injection, cookie, session, log in with authentication
Database application development lifecycle, roles, and case study
DBA, XAMPP, phpMyAdmin

Prerequisite
The prerequisite is CPS 2240 and students also need to pass Math 2110. If you have not completed the prerequisite, you should withdraw from the class.

Note: From fall 2020, this course is available in both fall and spring semesters.

Class information:

All the class materials (syllabus, slides, assignments, tools) are posted on the class Google drive. Students must use the Kean email account to access the folder.
This is a hands-on course. Students are required to bring a personal laptop to every class.
Assignments should be used MySQL connecting through a Linux server that is announced on the first day of the class.
Instructional Methods: lecture with slides, class discussions and exercises, and project creations.
Course grading: Please see instructor's syllabus.
(C or better is needed for CS and IT majors.)

TECH 3740 Course Description
Fundamental concepts and standards for database management systems; database design; database implementation; storage and performance considerations; application of database management systems.

TECH 3740 Student Learning Outcomes
Upon completion of this course, the student will be able to:

Understand the role of databases in information technology
Design and build applications using database management systems
Design database using various data models
Interact with the database using the structured query language
Build web-based database applications

Books and resources

Required Textbook: Database Systems: A Practical Approach to Design, Implementation, and Management. Author: Thomas Connelly and Carolyn Begg, 6th edition. Pearson, 2014. ISBN-13: 978-0132943260 You can also buy an ebook at this link which is much cheaper than the physical book.
Database resources on Samurai website.
Download dreamhome database.
Download deamhome SQL exercises with answers.

Requirements - Students will need the followings to do exercsies and assignments:

Students will work on the obi.kean.edu Linux server to do SQL exercises from the first day of class. All students' accounts will be created before the school starts.
Students will need the following tools to connect to a Linux server. hostname: obi.kean.edu, port: 22, protocol: ssh
Windows: Use "PuTTY" software to connect to the Linux server. You can download putty from http://www.putty.org/
Mac: Use "terminal" software. Please refer to this tutorial.
FileZilla-Client to transfer files between the Linux server and your desktop/laptop. You can download FileZilla-Client at https://filezilla-project.org/download.php?type=client
The hostname and port # is the same as above. Protocol should be "sftp". The logon type should be "normal". You need to enter the user login and password.
A good text editor: sublime, visual studeio code, or others. You can download sublime at https://www.sublimetext.com/3
Create public_html and TECH3740 folders under your home directory on obi.kean.edu, and set proper permission for the folders and files. Please refer to this eve web page procedures
Practice these Unix commands: cd, ls -la, more, pwd, mkdir, rm, mv, cp, chmod, etc. Please refer to this Unix command document.
Create and test your database account by following this procedure.

In addition, we will use PHP MySQL as the programming language for the projects. I will cover the basic HTML, PHP, PHP MySQL. Students can take a look at the following links:

HTML at W3Schools
SQL
PHP
PHP MySQL functions
XAMPP
Students need to know the basic Unix commands.
Students can see more resources on the Code Samurai website.

Students can get help from the Samurai program for SQL, PHP MySQL, Web Database Programming. Samurai will host group review sessions for some topics related to the web & database. Students can see the Samurai schedule and VIRTUAL walk-in hours at Code Samurai Program.