This course covers the theory of a Database Management System, within the context of its utilization in an information system application. The primary focus will be on relational databases. Concepts will include design, optimization, and implementation. Security and data integrity in centralized and distributed systems are issues that will be addressed. SQL will be employed as a vehicle during the development of applications.  Prerequisite: CSC245: Objects and Data Abstraction: computer programming and MTH 201: Calculus III.

Students shall be able to:

1. Demonstrate an understanding of the major components of a database management system and their functions and purpose.
2. Demonstrate the ability to develop a data model for a database application using ER diagrams.
3. Ability to apply the concepts of data normalization in developing well-designed database applications.
4. Ability to implement a database application using an appropriate relational DBMS.
5. Ability to understand the major operational issues associated with database applications, including transaction management, security, and integrity.

August 28, 2018: Fall classes begin

October 4, 2018:  Last day for early withdrawal (WI)

October 25, 2018: Deadline for Incomplete grades

November 8, 2018: Last day for withdrawal from courses (WP/WF)

December 7, 2018: Fall classes end

Assignment 1: Conceptual Database Modeling using ER Diagrams Solution

Assignment 2: Relational Databases and SQL Solution

Assignment 3: Normalization and Indexing Solution

Assignment 4: Query Processing and Optimization Solution

Professor Haidar M. Harmanani

haidar@lau.edu.lb • http://vlsi.byblos.lau.edu.lb • http://harmanani.github.io

Office Hours:

Block A • Room 810

Tuesday, Thursday • 3:00pm – 4:30pm • 8:00pm – 9:30pm or by appointment

Lecture  01: Introduction to Databases?

Lecture  02: Relational Databases

Lecture  03: Conceptual Database Modeling Using ER Diagrams

Lecture  04: Conceptual Database Modeling Using UML

Lecture  05: Relational Algebra

Lecture  06: SQL: The Query Language

Lecture  07: Functional Dependencies, Schema Refinement, and Normalization

Lecture 08: Introduction to SQL Programming Techniques JDBC PHP  Example

Lecture  09: Buffer and File Management

Lecture 10: Indexing B+ Trees Example

Lecture 11: XML Representation [In Lab]

Lecture 12: JSON Representation, MongoDB  [MongoDB in 5 Minutes] [MongoDB CRUD Guide]

Lecture 13: Transaction Overview and Concurrency Control

Lecture 14: Cloud Computing

Lecture 15: Database Security

Lecture 16: Relational Query Optimization

Lecture 17: Information Retrieval (if time permits)

Lecture 18: Data Mining, Big Data and Analytics (if time permits)

Lecture 19: Crash Recovery (if time permits)

Lab 1: MySQL datafiles Slides

Lab 2: Oracle and JDBC Oracle JDBC API MySQL JDBC API

Lab 3: Normalization Tutorial

Lab 4: MySQL and Web Programming PHP MySQL Introduction

The project should include the following milestones:

1. Stage 1: Functional Description and ER Design [Due on September 25, 2017]
   1. The relational schema of your database.
   2. The ER Diagram.
2. Stage 2: Database Design [Due on October 25, 2017]
   1. Relational Schema
   2. Functional Dependencies
   3. Proof of Normal Form
   4. Functional Dependencies
   5. Sample Queries in SQL
3. Stage 3: Development Plan [Due on November 15, 2017]
   1. Describe the final choice of databases and software platforms/languages that you will be using.
   2. Describe where and how you will get data for your application
   3. Describe the labor division among group members.
   4. A project timeline with milestones.
4. Stage 3: Finalize Development Environment and Submit Report
   1. November 25,2017
5. Stage 4: Presentations and Demo
   1. December 5-7,2017

All students are expected to take exams during the scheduled time slots. With the permission of the instructor, you may be allowed to take an exam at an alternate time. However, you must request this rescheduling at least 2 weeks prior to the exam date. Exceptions will naturally be made for sudden problems such as serious illnesses/injury. Since the exam schedule is being published at the beginning of the semester, scheduling conflicts (e.g., job interviews, GREs, etc.) are not legitimate reasons to miss an exam.

Previous Exams

Fall 2013 Exam 1

Fall 2013 Exam 2

Midterm Exam

The first midterm exam is scheduled on Tuesday October 17, 2017 at 9:30 am. The  midterm exam will be closed book exam and will include all materials that were covered until Thursday before the exam. In principle, all topics discussed in class (whether on the lecture notes or not) and in the assigned readings are a legitimate source for exam questions.

Final Exam

The final will be comprehensive, with roughly 1/3 of the material devoted to material covered prior to the midterm. The Final Exam is scheduled on  December 19, 2017 from 11:00 am-2:00 pm, ENG 504.  The final exam will be comprehensive and will include all lectures that were covered in the course.  This includes: Abstract database modeling (ER Diagrams, UML), Queries (Relational Algebra and SQL), Database Applications (php, MySQL, and jdbc), Transactions Processing (Lecture 19, until page 44), Buffer Management, Indexing, Information Retrieval, Cloud Computing, and Normalization.

One Midterm: 35%  Grades

Final: 40%   Grades

Assignments and Labs: 15%   Grades

Course Project: 10%    Grades

Programming Tools

• MySQL Workbench
• SQL WorkBench Video Tutorial

Programming References

•  Kernighan C Programming Tutorial
• A Probably Incomplete, Comprehensive Guide to the Many Different Ways to JOIN Tables in SQL
• Intro to MySQL from Google Code
•  MySQL Tutorial
•  SQL for Web Nerds
•  Software Configuration Management

• A Relational Model of Data for Large Shared Data Banks by E.F. Codd
• Decomposition of A Relation Scheme into Boyce-Codd Normal Form, D-M. Tsou
• A Simple Guide to Five Normal Forms in Relational Database Theory, W. Kent
• Big Data and Cloud Computing: Current State and Future Opportunities, D. Agrawal, S. Das, A. El Abbadi