Welcome to CPS 5740 (Database Systems)

In this course, students will learn about advanced SQL commands, database designs, relational algebra, relational calculus, anomalies, functional dependency, normalization, stored routines, triggers, transactions, concurrency control, personalization, query processing, emerging database technology, database designs, basic DBA, how to develop web database applications as well as obtain hands-on experiences.

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
Updatable and not updatable views, materialized views
Data Definition Language (DDL): create, drop, alter, rename
Data Control Language (DCL): grant, revoke
Foreign key and other constraints
Stored Routines: local variable, session variable, stored function, stored procedure, parameters (IN, OUT, INOUT)
Web technology, 3-tier architecture, client/server
Web database application: HTML FORM inputs, CGI, data entry validation, select to display, insert, update, and delete data from the database through the browser.
Web database security: SQL injection, cookie, session, log in with authentication and authorization (role)
Database designs: Entity-Relationship (ER) model and diagrams, logical, physical, case study
Query processing: query processing, query plan, optimization
Emerging database technology: GIS, NoSQL, Google Maps, data warehouse, data mining, data visualization, online analytical processing (OLAP)
Database application development lifecycle, roles, and case study
DBA, XAMPP, phpMyAdmin

Prerequisite
Admission to CIS Graduate Program with some database concepts.

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. This is a graduate course so students are expected to quickly pick up basic database skills if they didn't take an undergraduate database. Please refer to CPS3740 for our undergraduate database course content.

Note: This course is only available in fall semesters.

Instructor: Dr. Ching-yu (Austin) Huang

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.
Students can access the latest syllabus here.
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: plese see course syllabus.
Grading policy: Total 1000 points.
A: >=940, A-: 939-890, B+: 889-840, B: 839-800, B-: 799-760, C+: 759-720, C: 719-680, D: 679-640, F: below 640
B- or better is needed for CIS Graduate program.

CPS 5740 Course Description
Enterprise database management systems; database design; database implementation; storage and indexing; transaction processing concepts; data warehousing, data mining, and online analytical processing (OLAP); emerging database applications.

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

Measure the role of databases in enterprise information systems. Compare and summarize diverse database models
Design and build web-based applications using database management systems
Explain the role of a database in data mining and OLAP.
Write and execute SQL statements and implement PL/SQL programs. Illustrate transaction processing concepts
Summarize the emerging database applications

Books and resources

Required Textbook: Fundamentals of Database Systems (7th Edition), by Elmasri, R. and Navathe, S., Pearson, 2015. ISBN-13: 9780133970777
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 CPS5740 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.

For remote learning, we will be doing exercises using the BlackBoard Live Session in most of the classes and students need a computer connecting to obi.kean.edu to access my database server. Please log in to Blackboard and join the class Live Session a few minutes earlier so we can start the class on time. I look forward to seeing you on the first day of class.

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.