Database Systems

		Database Systems
CISC 3500
Professor:	Sam Sultan [sam.sultan@fordham.edu]
TA:	Aron Connors [aconnors9fordham.edu] - Office hour: Thu 5-6pm. Make appt. first
Class website:	[storm.cis.fordham.edu/~sultan/db] (or) [fh.samsultan.com/db]
Course Days:	Wednesdays 1/15-5/7 (No class 3/19) - Final Exam 5/7 @ 4:00pm
Course Hours:	2:30pm - 5:15pm
Location:	Rose Hill - John Mulcahy Hall room 330

Announcement(s):

+syllabus
+course outline

+grades & evaluation
+final project

+examples & demos

+student listing
+attendance Sheet

+student feedback
+student evaluation & comments

Session - 1 2 3 4 5 6 7 M 8 9 10 11 12 13 14 F - Extra X

SQL Tutorial - MySql Server (download) / MySql Workbench - SQL*Tester^© - Storm SQL*Tester^© - MongoDB^©

Site Helpful?

COURSE DESCRIPTION:

This course begins with the introduction of the characteristics of the database approach and the advantages of using database systems. Course topics include the basic concepts and architecture of database systems, the Relational Data Model concepts, integrity constraints, schemas, views, SQL, data modeling using the Entity-Relationship (ER) model as well as using the Enhanced ER model (EER), UML diagram, practical database design methodology, normalization process, physical design and system implementation and tuning. Database security issues will also be discussed.

The course will also cover extensive use of SQL (Structured Query Language). SQL is the language used to manipulate data in relational databases. SQL will be used to select, update, insert, and delete data from database tables. During the course, you will acquire hands-on experience with both MySQL and SQLite and additional knowledge in Oracle. Learn how to select data from multiple tables using joins and unions, understand how to create sub-queries to develop more complex retrieval capabilities, and use DDL to create your own database and populate tables. In addition, learn about database design, table relationships, and normalization techniques. This course prepares you to work with any relational database, such as MySQL, Oracle, SQL Server, PostgreSQL, SQLite, and even the relational aspect of Snowflake.

COURSE LEARNING GOALS:

1. Course Objectives:

The objective of this course is to teach you the fundamentals behind the SQL language. We will discuss the concepts of databases in general, and more specifically the relational database model. We'll examine the various database engines that support this model. We will learn about SQL (Structured Query language) both as an ANSI standard language and more some vendor specific extensions. We will also use DDL (Data Definition Language) to create and delete database objects, and DML (Data Manipulation Language) to access and manipulate those objects.

This course will teach you all relevant SQL concepts more from an ANSI SQL2 standard point of view. Some topics, and examples however will need to be discussed with a specific database engine in mind. This course will cover SQL from both an Oracle and a MySQL point of view

The focus of the course will be on the following topics:

The concepts and advantages of databases.
The relational database model RDBMS.
SQL - Structured Query Language.
DDL - Data Definition Language.
DML - Data Manipulation Language.
Database design and data normalization.

2. Student Learning Outcomes:

Learn what databases are and the difference between the various database models
Appreciate the power and flexibility of the relational database model
Understand the various terms used in conjunction with SQL
Learn how to work with SQL as it related to both Oracle and MySql
Master how to join tables to access data from multiple database tables
Differentiate between inner and outer joins and when to use each
Obtain a working knowledge of the various SQL functions
Become proficient with queries and sub-queries
Learn how to design databases and how to build databases tables and indexes
Learn how to insert, update and delete data from database tables
Become familiar with both Oracle and MySql data dictionary tables

BOOKS - (Required / Suggested)

Required Books -

Teach Yourself SQL in 24 Hours (7th Edition)
- Authors - Ryan Stephens
- Publisher - SAMS

GRADE ASSIGNMENT AND EVALUATION:

Attendance & Class Participation - 10%
Homeworks - 20%
Final Project - 10%
Midterm Exam - 30%
Final Exam - 30%

Details of Assignment, Exam and Grade Evaluation.

Class Attendance and Participation: To receive full credit for the course, you must attend all classes since much of the learning occurs during class presentation and discussions. Please contact the instructor if you anticipate missing any part of the class. Grades will be based on:
- Involvement in class activities
- Participation which demonstrates integration of reading, class work, relevance and application.
- Willingness to learn by accepting feedback, trying new skills and approaches, etc.
- Quality/quantity of providing effective and balanced feedback.
- You must ask at least one question, or respond to professor or student inquiry at least once per session
Homework: Homework is always due one week from assignment date.
- Student should complete the assignment and bring their answers to class the following week.
- Instructor will ask students to present and discuss their answers prior to start of lecture of the following week.
- Incompleted homeworks will severly impact your learning experience and overall grade.
Midterm Exam: There will be a midterm exam. The exam will be an open book, open notes style exam. The exam will test the student's acquisition of topics, concepts and competencies learned in the first half of the semester.
Final Exam: There will be a final exam. The exam will be an open book, open notes style exam. The exam will test the student's acquisition of topics, concepts and competencies learned in the second half of the semester.

[Exercise/Homework Answers]

[Fordham Grade Scale]

Grades are FINAL.

Please do not negotiate for a better grade. Instructor will compute grades to 2 decimal places. If you are expecting to receive a grade of an "A" at the end of the semester, then I expect you to attend all sessions, to participate in class, to turn in your homeworks on time, and to keep up with the class reading material. If you see yourself falling behind do not hesitate to ask for help. This will ensure that you stay current with the class, and will ensure that you get a good grade on your work.

Please Note: Professor will not entertain any request for assignment "redo" or extra credit assignment to improve grade

Statement on Academic Integrity:

Fordham University is a high level academic institution that takes academic integrity very seriously. Students must present their own original work. Students suspected of violating this policy including cheating and/or plagiarism and/or copying from others or published materials on assignments or exams will be severely penalized for their action.

COURSE OUTLINE:

DATE SESSION TOPIC[s] COVERED

[Week 1] 1 Introduction to databases
The various database models
Relational databases
Creating tables
Insert, update and delete rows
Query data from a single table
The JOIN command
Inner vs. outer joins
Query from multiple tables
noSQL databases

Reading: Chapter 1

[Week 2] 2 Introduction to databases
What is a database?
The History of databases
The various database models
Hierarchical databases
Network databases
Relational databases
Object & Object relational databases
NoSQL and Big Data databases

Reading: Chapter 1

[Week 3] 3 Introduction to SQL
Flavors of SQL
DDL - Data Definition Language
DML - Data Manipulation Language
The SELECT statement
Choosing distinct values
The WHERE clause
Comparison operators
Comparing with LIKE
Logical operators, AND, OR, NOT
Numeric operators
Creating computational columns

Reading: Chapters 2, 3

[Week 4] 4 Selecting data from multiple tables
The join construct
Old vs. new join syntax
Normal or Inner join
Cross join - Cartesian product
Outer join vs. Inner join
What is a Self Join
Set operators, UNION, INTERSECT, MINUS
Combining Join with UNION
Performance considerations

Reading: Chapter 5

[Week 5] 5 SQL built-in Functions
Numeric functions - CEIL, FLOOR, ROUND, TRUNCATE, etc.
String functions - CONCAT, LENGTH, SUBSTR, REPLACE, etc.
The CASE expression, 2 flavors
Date functions - MySQL and Oracle
Current date, date manipulation, date formatting

Reading: Chapter 7 (Skip Aggregate functions), 12

[Week 6] 6 Aggregating and Grouping
Aggregate functions - SUM, COUNT, AVG, MIN, MAX
The GROUP BY clause
The HAVING clause
Finding Duplicate Records
GROUP BY with ROLLUP feature
The ORDER BY clause Pivoting rows into columns

Reading: Chapters 7 (Aggregate functions), 4

[Week 7] 7 Using SELECT Sub-Queries
Subqueries as filters
Subqueries as inline views
Subqueries as additional derived columns
Correlated Subqueries
Where [NOT} EXISTS in Subquery
Finding the last record from a set
Pivoting rows into columns

Reading: Chapter 6

[Week 8] M - Midterm Exam -

[Week 8] 9 Database Design
The Logical and Physical Model
Understanding data normalization
First normal form
Second normal form
Third normal form
Pros & cons of data normalization
Denormalizing data
Entity relationships
One-to-one relationship
One-to-many relationship
Many-to-many relationship
Designing Self-join relationship
Designing for an ODS (Reporting Database)
Designing for a Data Warehouse

Reading: Chapter 8

[Week 9] 9 (Optional detail on Data Warehouse modeling)
Data Warehouse Schemas
Dimensions and Dimension Tables
Facts and Fact Tables
The Star Schema
The Snowflake Schema
The Data Warehouse Bus Architecture
Conformed Dimensions and Standard Facts
Data Granularity
Slowly Changing Dimensions

Reading: Professor Notes & Internet

[Week 10] 10 Creating database objects
What is a primary key?
What is a foreign key?
What is an index?
Creating tables
SQL data types
Adding a primary key
Adding constraints
Creating Indexes
Altering table definition
Droping tables
MySql Auto Increment
Oracle Sequences

Reading: Chapters 9, 10, 15

[Week 11] 11 Manipulating data in tables
Adding data with the INSERT statement
INSERT with a SELECT statement
Changing data with the UPDATE statement
UPDATE with a SELECT statement
Removing data with the DELETE statement
DELETE with a SELECT statement
The TRUNCATE statement
The REPLACE statement (MySql)
The MERGE statement (Oracle)

Reading: Chapter 11

[Week 12] 12 Advanced Topics
Creating and using views
Using the Data Dictionary - MySql
Using the Data Dictionary - Oracle
The show statement (MySql)
Loading data from a file
Unloading data into a file
Importing a database or table(s)
Exporting a database of table(s)

Reading: Chapters 13, 18

[Week 13] 13 MySQL - Procedural Language
Creating Stored Procedures
Creating Stored Functions
Creating database Triggers

Reading: Professor Notes & Internet

[Week 14] 14 Accessing a DB from within a Programming Language
Database Drivers
Database Cursors
Python DB insert/query examples
PHP DB insert/query examples
Java DB insert/query examples
C DB insert/query examples
C++ DB insert/query examples

Reading: Professor Notes & Internet

[Week 15] F - Final Exam -

[Extra] X Non-Relational Databases
NoSQL Databases various architectures
MongoDB

Reading: Professor Notes & Internet

DATE	SESSION	TOPIC[s] COVERED

[Week 1]	`1`	Introduction to databases The various database models Relational databases Creating tables Insert, update and delete rows Query data from a single table The JOIN command Inner vs. outer joins Query from multiple tables noSQL databases
	Reading:	Chapter 1

[Week 2]	`2`	Introduction to databases What is a database? The History of databases The various database models Hierarchical databases Network databases Relational databases Object & Object relational databases NoSQL and Big Data databases
	Reading:	Chapter 1

[Week 3]	`3`	Introduction to SQL Flavors of SQL DDL - Data Definition Language DML - Data Manipulation Language The SELECT statement Choosing distinct values The WHERE clause Comparison operators Comparing with LIKE Logical operators, AND, OR, NOT Numeric operators Creating computational columns
	Reading:	Chapters 2, 3

[Week 4]	`4`	Selecting data from multiple tables The join construct Old vs. new join syntax Normal or Inner join Cross join - Cartesian product Outer join vs. Inner join What is a Self Join Set operators, UNION, INTERSECT, MINUS Combining Join with UNION Performance considerations
	Reading:	Chapter 5

[Week 5]	`5`	SQL built-in Functions Numeric functions - CEIL, FLOOR, ROUND, TRUNCATE, etc. String functions - CONCAT, LENGTH, SUBSTR, REPLACE, etc. The CASE expression, 2 flavors Date functions - MySQL and Oracle Current date, date manipulation, date formatting
	Reading:	Chapter 7 (Skip Aggregate functions), 12

[Week 6]	`6`	Aggregating and Grouping Aggregate functions - SUM, COUNT, AVG, MIN, MAX The GROUP BY clause The HAVING clause Finding Duplicate Records GROUP BY with ROLLUP feature The ORDER BY clause Pivoting rows into columns
	Reading:	Chapters 7 (Aggregate functions), 4

[Week 7]	`7`	Using SELECT Sub-Queries Subqueries as filters Subqueries as inline views Subqueries as additional derived columns Correlated Subqueries Where [NOT} EXISTS in Subquery Finding the last record from a set Pivoting rows into columns
	Reading:	Chapter 6

[Week 8]	M	- Midterm Exam -

[Week 8]	`9`	Database Design The Logical and Physical Model Understanding data normalization First normal form Second normal form Third normal form Pros & cons of data normalization Denormalizing data Entity relationships One-to-one relationship One-to-many relationship Many-to-many relationship Designing Self-join relationship Designing for an ODS (Reporting Database) Designing for a Data Warehouse
	Reading:	Chapter 8

[Week 9]	`9`	(Optional detail on Data Warehouse modeling) Data Warehouse Schemas Dimensions and Dimension Tables Facts and Fact Tables The Star Schema The Snowflake Schema The Data Warehouse Bus Architecture Conformed Dimensions and Standard Facts Data Granularity Slowly Changing Dimensions
	Reading:		Professor Notes & Internet

[Week 10]	`10`	Creating database objects What is a primary key? What is a foreign key? What is an index? Creating tables SQL data types Adding a primary key Adding constraints Creating Indexes Altering table definition Droping tables MySql Auto Increment Oracle Sequences
	Reading:	Chapters 9, 10, 15

[Week 11]	`11`	Manipulating data in tables Adding data with the INSERT statement INSERT with a SELECT statement Changing data with the UPDATE statement UPDATE with a SELECT statement Removing data with the DELETE statement DELETE with a SELECT statement The TRUNCATE statement The REPLACE statement (MySql) The MERGE statement (Oracle)
	Reading:	Chapter 11

[Week 12]	`12`	Advanced Topics Creating and using views Using the Data Dictionary - MySql Using the Data Dictionary - Oracle The show statement (MySql) Loading data from a file Unloading data into a file Importing a database or table(s) Exporting a database of table(s)
	Reading:	Chapters 13, 18

[Week 13]	`13`	MySQL - Procedural Language Creating Stored Procedures Creating Stored Functions Creating database Triggers
	Reading:	Professor Notes & Internet

[Week 14]	`14`	Accessing a DB from within a Programming Language Database Drivers Database Cursors Python DB insert/query examples PHP DB insert/query examples Java DB insert/query examples C DB insert/query examples C++ DB insert/query examples
	Reading:	Professor Notes & Internet

[Week 15]	F	- Final Exam -

[Extra]	`X`	Non-Relational Databases NoSQL Databases various architectures MongoDB
	Reading:	Professor Notes & Internet

CISC 3500