Syllabus

CLASS TIME:
August 26 through December 20, 2019

LOCATION:
Online: https://zoom.us/j/835537196 (~2:00 – 3:30 pm Wednesdays)

CONTACT:

Email:

Text Message:

Office:

Office Hours:

richardh@hawaii.edu

(808) 371-7424

303G

2:00 pm – 3:30 pm Wednesdays

TA: Ms. Lingchih Yao <lingchih@hawaii.edu>

TEXTBOOK:

Relational Theory for Computer Professionals: What Relational Databases Are Really All About (Theory in Practice),
1st Edition by C. J. Date (O’Reilly) Available at Amazon.com
– $33.00 new paperback. (ISBN: 978-1449369439)

Database Design and Relational Theory: Normal Forms and All That Jazz,
(Recommended but not required)
1st Edition by C. J. Date (O’Reilly) Available at Amazon.com
– $39.25 new paperback. (ISBN: 978-1449328016)

STUDY.COM

Short 10-minute modules on data storage, databases and data science. Five multiple choice questions at the end of each module. (No credit card necessary.)

URL: https://study.com/join/classroom.html

If your last name starts with A-K, use code: orange-kitten-6806

If your last name starts with L-Z, use code: gold-bee-4977

COURSE OUTCOMES

At the end of this class, the successful student will:

Understand what a database system is

  • You understand what a DBMS is and what it provides
  • You understand how DBMS provides data independence and protects the data from the user.
  • You understand security, concurrency, integrity and recovery controls.
  • You can compare database systems with programming systems.

Understand the relational model of data

  • You understand what the relational model is
  • You can conceptualize data using the relational model.
  • You understand what basic relational algebra operators under set semantics.
  • You understand matching, not matching, extend operations and image relations.
  • You can express queries using relational algebra.
  • You can create tables using SQL Data Definition Language (DDL)
  • You understand integrity constraints
  • You can specify primary key and foreign key constraints in CREATE TABLE statements

Be proficient in SQL

  • You can write SQL queries with restrictions, projections and joins.
  • You can compose SQL queries using set operators.
  • You can write SQL queries with correlated subqueries.
  • You can write SQL aggregation queries involving GROUP BY and HAVING clauses.
  • You can write SQL queries implementing set union, intersection and difference operations.
  • You can write SQL queries implementing matching and image relations.
  • You can translate a query in English to SQL.

Be able to write a program that interacts with a DBMS server

  • You understand client-server computing in the context of DBMSs
  • You understand how a host programming language environment interacts with the SQL environment
  • You can implement simple APIs that interact with a DBMS server.

Structure Data using Data Models

  • You can use data modeling tools like Entity-Relationship Diagrams.
  • You can translate data models into relational table schema (DDLs)

Understand Data Normalization

  • You understand functional dependencies and their relationship to keys
  • You understand multi-valued dependencies and join dependencies.
  • You understand normal forms 1NF, 2NF, 3NF, BCNF, 4NF and 5NF.
  • You can find keys given a set of functional dependencies
  • You can identify tables that are not normalized
  • You can decompose unnormalized tables.

Understand constraints, views, triggers, stored procedures and indexes in Databases

  • You understand what constraints are for and how to use them.
  • You understand what views are for and how to use them.
  • You understand what triggers are for and how to use them.
  • You understand what stored procedures are for and how to use them.
  • You understand what indexes are for and how to use them.

Understand basic transaction processing concepts

  • You understand transactions and their properties of Atomicity, Consistency, Isolation and Durability (ACID)
  • You understand the anomalies that occur without ACID.
  • You can implement stored procedures that use transactions to ensure ACID.

Understand how data is stored and indexed in a DBMS

  • You understand storage media and their basic properties
  • You understand how data is stored using storage media in a DBMS
  • You understand how different indexing techniques work

In addition to the ICS department’s undergraduate course outcomes, which are students will have:

  • An ability to apply mathematical foundations, algorithmic principles, and computer science theory to the modeling and design of computer-based systems.
  • An ability to define the computing requirements of a problem and to design appropriate solutions based on established design principles and with an understanding of the tradeoffs involved in design choices.
  • An ability to use sound development principles to implement computer-based and software systems of varying complexity, and to evaluate such systems.
  • An ability to use current techniques, skills, and tools necessary for computing practice.

SCHEDULE:
The weekly schedule is as follows. There are video lectures here and here for each chapter. There is also a quiz associated with each day. Quizzes are on Laulima in either in the Tests & Quizzes section or the Assignments section.

Week

Reading and Watching

Assigned

1
8/26

Date Ch 1. Basic Database Concepts;
Study.com Ch 1. Data Storage 1, 2

Quiz 01 – Basic Database Concepts
Study.com Ch 1. Data Storage 1, 2

2
9/2

Date Ch 2. Relations and Relvars;
Study.com Ch 1. Data Storage 3, 4, 5

Quiz 02 – Relations and Relvars
Study.com Ch 1. Data Storage 3, 4, 5

3
9/9

Date Ch 3. Keys, Foreign Keys, and Related Matters;
Study.com Ch 1. Data Storage 6, 7

Quiz 03 – Keys
Quiz 04 – Foreign Keys
Study.com Ch 1. Data Storage 6, 7

4
9/16

Date Ch 4. Relational Operators I;
Study.com Ch 1. Data Storage 8, 9

Quiz 05 – Project, Restrict
Quiz 06 – Intersection, Union, Minus
Study.com Ch 1. Data Storage 8, 9

5
9/23

Date Ch 10. SQL Tables;
Study.com Ch 1. Data Storage 10, 11, 12, 13

SQL Assignment 1 – SQL Tables (5)
Study.com Ch 1. Data Storage 10, 11, 12, 13

6
9/30

Date Ch 11. SQL Operators I;
Study.com Ch 1. Data Storage 14, 15, 16

Quiz 07 – Join
Study.com Ch 1. Data Storage 14, 15, 16

7
10/7

Date Ch 8. Transactions;
Study.com Ch 2. Databases 1, 2, 3

Quiz 08 – Transactions
Study.com Ch 2. Databases 1, 2, 3

8
10/14

Date Ch 9. Database Design;
Study.com Ch 2. Databases 4, 5, 6

SQL Assignment 2 – Normalization (5)
Study.com Ch 2. Databases 4, 5, 6

9
10/21

Date Ch *3,*4. Normalization: Generalities, FDs and BCNF;
Study.com Ch 2. Databases 7, 8, 9

Quiz 09 – Functional Dependencies
Study.com Ch 2. Databases 7, 8, 9

10
10/28

Date Ch *9. Normalization – JDs and 5NF;
Study.com Ch 2. Databases 10, 11, 12

SQL Assignment 3 – Procedures (15)
Study.com Ch 2. Databases 10, 11, 12

11
11/4

Date Ch *12. Normalization – MVDs and 4NF;
Study.com Ch 2. Databases 13, 14

Quiz 10 – Functional Dependencies
Study.com Ch 2. Databases 13, 14

12
11/11

Date Ch 6. Constraints and Predicates;
Study.com Ch 2. Databases 15, 16

Quiz 11 – Constraints
Study.com Ch 2. Databases 15, 16

13
11/18

Date Ch 13. SQL Constraints;
Study.com Ch 3. Data Science 1, 2, 3

SQL Assignment 4 – Triggers (10)
Study.com Ch 3. Data Science 1, 2, 3

14
11/25

Date Ch 5. Relational Operators II;
Study.com Ch 3. Data Science 4, 5, 6

Quiz 12 – Matching, Image Relations
Study.com Ch 3. Data Science 4, 5, 6

15
12/2

Date Ch 12. SQL Operators II;
Study.com Ch 3. Data Science 7, 8, 9

SQL Assignment 5 – Relational Assignments (15)
Study.com Ch 3. Data Science 7, 8, 9

16
12/9

Date Ch 7, 14. SQL vs. the Relational Model;
Study.com Ch 3. Data Science 10, 11, 12

Study.com Ch 3. Data Science 10, 11, 12

17
12/16

FINALS WEEK

Chapter numbers without asterisks refer to Relational Theory for Computer Professionals: What Relational Databases Are Really All About (Theory in Practice). Video Lectures
Chapter numbers with asterisks refer to: Database Design and Relational Theory: Normal Forms and All That Jazz. Video Lectures

GRADING POLICY:
See the Gradebook in Laulima for the percentage breakdown of each score. Each daily quiz is 1 percentage point. Each Study.com quiz is 1/2 percentage point. The first and second SQL assignments are 5 percentage points. The fourth SQL assignment is 10 points. The third and fifth SQL assignments are 15 points each.

Type

#

%

Description

Laulima Quizzes

12

24

12 weekly topic quizzes from the lectures, videos and text book. Quizzes will be multiple choice administered on Laulima.

SQL Assignments

5

50

Two 5 point, one 10 point and two 15-point SQL assignments. You will be provided with a MySQL server in the cloud that I have access for grading.

Study.com Quizzes

44

22

44 five question multiple choice quizzes from Study.com chapters covering data storage, databases and data science. Approximately 10 minutes each. Grading is automatic.

Class Participation

4

4

Post four significant contributions. Significant items include (1) an introduction of yourself including why you’re taking the course and what you expect to get out of it, (2) a question or request for help on an assignment or quiz, (3) answering someone’s question.

Weekly topic quizzes submitted after one week will be subject to a points reduction. Assignments over two weeks late will be subject to a points reduction.

No credit will be given for daily quizzes or assignments submitted after the last day of classes except Assignment 5.

Letter grades will be based on a rounded percentage and assigned as follows:

A

100% – 95%

A-

95% – 90%

B+

90% – 87%

B

87% – 83%

B-

83% – 80%

C+

80% – 77%

C

77% – 73%

C-

73% – 70%

D

70% – 60%

F

Below 60%