MIST 310/510 Database Management

Course Information

Class:
Classroom:
Credit Hours:
Prerequisites:

Instructor

Tsangyao Chen, Ph.D.
Office:
Office Hours:
Email: ty@tychen.org

Course Overview

Modern organizations rely on data-driven decision-making to stay competitive. Database systems therefore have become central to organizations’ decision-making at operational, managerial, and strategic levels. The main objective of this course is hence to equip students the capability and skills in understanding and utilizing database systems to be able to access, retrieve, analyze, and utilize data and information effectively to support decision-making in organizations. Students will begin with using SQL language to query databases, followed by conceptualizing, designing, and implementing databases in a real-world context for tasks from information processing to managerial decision-making.
This course introduces the theory and practice of database design, management, and application. Topics covered include basic database concepts, SQL queries, relational algebra, data modeling with entities and relationships, normalization, and writing code to interface with databases. To ensure the learners' capability to design and implement an information systems with a relational database server backend, a 3-tier client-server interactive web database application term project integrates the knowledge and skills learned throughout the semester.
DBMS, Relational DBMS, SQL, MySQL, SQLite, Database Modeling, Database Design, SQL Queries

Learning Objectives

Upon successful completion of this course, students will be able to:

  1. Operate the selected functional features of a database management system.
  2. Understand data management concepts and explain the role of databases in organizations.
  3. Analyze the role of databases in a functional information system.
  4. Explain how a database management system supports common functional information systems in organizations.
  5. Work successfully individually and in a group on a database-driven application system.

Course Materials

No textbook is required for this course. Required and suggested reading materials, if any, will be provided. The following textbooks are recommended as resources for more complete and in-depth investigation on the topics covered in class.

General Introduction

Hoffer, J.A., Ramesh, V., & Topi, H. (2021). Modern database management. Pearson Education. (13th; or recent editions)

SQL Practice

Shellman, M., Afyouni, H., Pratt, P.J., & Last, M.Z. (2020). A guide to SQL. Cengage Learning. (10th; or recent editions)

Equipment

  • Laptop Computer: You are encouraged to bring a laptop computer to the class meetings for use in the hands-on activities.
  • Software: Software applications needed for this course is available via https://its.university.edu.

Course Assignments

Submission Guidelines:
  • All assignment submissions will be accepted during the scheduled assignment submission period.
  • Late submissions will be granted only in excused situations per university attendance policy with necessary documentation.
  • Note that some assignments must be done in order. For example, in order to analyze data using certain applications, system configuration and dataset import may need to be completed in a prior assignment.

Homework

Homework assignments are designed to give students the opportunity to practice the learning from the lectures and Lab activities. Homework assignment instructions are detailed separately in each assignment.

Lab

Lab instructions are provided in the form of detailed step-by-step lab documents. The instructor will lead the lab activities by providing short lectures followed by demonstrations before students working on the exercises.
Lab exercises provide opportunities for students to:
  • learn and practice technical skills;
  • increase conceptual understanding related to skills practiced;
  • gain knowledge and skills needed for answering homework questions

Project

The project will require you to work individually or as a group to develop a three-tier client-server application with database backend. The details on the project will be issued in a separate handout.

Examination

Exams are comprehensive assessments of student learning over a period of time. Each of the exams:
  • Will cover the materials from the lectures, lab activities, and homework assignments;
  • Will mainly not be cumulative. However, the learner will need the knowledge and skills from earlier assignments to complete the exam questions successfully.
If a makeup exam is granted, an alternative format (e.g., essay, oral, or lab assessment) may be used.

Attendance/Participation

In-class short assignments and quizzes are administered during class meetings to:
  • take class attendance; and
  • assess participation and diagnose student learning.
Note: No late or makeup submissions for attendance assignments/quizzes.

Grading Scheme

Tis course intends to enable students to complete all of these activities following the “learning by doing” principle. The grading scale is based on the assumption that the students will work independently and collaboratively to complete all the activities with very few errors. Generally, a student attending all the class meetings and complete all the assignments by schedule will do very well in this course, even with minimal prior technical experience.
Grade Categories
Course Requirement Number of Items Points per Item Total Points
Homework 10 10 100
Lab 10 10 100
Project 1 50 50
Exam 2 75 150
Attendance/Participation   100
      500

The final grade will be calculated based on the total points earned by the student. The final grade will be determined by the following scale:
Grade Scale
Letter Grade Range
A 100% to ≥ 90%
B < 90% to ≥ 80%
C < 80% to ≥ 70%
D < 70% to ≥ 60%
F < 60% to ≥ 0%

Course Policies

Late Assignment Submission

Assignments will be accepted following the set assignment schedule. Late submissions will be granted only in excused cases.

Communications

  1. Treat others with respect.
  2. Please communicate with your instructor and peers in a timely manner and avoid last-minute requests whenever possible.
  3. Report any inappropriate communication considered to be of a serious nature, as it may be a violation of University policy, to your instructor.
  4. Text messaging for technical help:
    • When permitted by institutional policy, text messaging is considered a professional channel for administering quick problem-solving sessions.
    • Please include screenshots of your full code, and briefly describe the issue and how you have tried to solve the problem.
    • Phone numbers of current students are not added to the instructor's contact list for privacy concerns.
    • Text messages are deleted periodically. The instructor does not keep prior problem-solving conversations.
    • Avoid giving personal information when seeking technical assistance. Just:
      • state the course and topic,
      • describe the problem and your thoughts, and
      • present screenshots.
  5. Emailing: Typically, emails are responded to within 24 hours. If you do not see a response in 24 hours, assume the email is lost and resend the email or text a reminder message the instructor.  
  6. Grading: Assignments are typically graded within 3 days after each due date.
  7. Please place a headshot in your online learning system profile, so the instructor can better match names and faces.

Attendance

  1. Expectation: Class meetings are business meetings. You are expected to attend all class meetings and participate in the class activities.
  2. Definition of Absence: Excused absences, per university policy, include documented illness, deaths in the family, and other documented crises, call to active military duty or jury duty, religious holy days, and official University activities. These absences will be accommodated in a way that does not arbitrarily penalize students. Consideration will also be given to students whose dependent children experience serious illness.
  3. Communication: Please communicate with the instructor professionally in a timely manner (which usually means notifying the instructor 24 hours or more, unless it's an emergency, before the absence) and with clear message and required documentation .
  4. Excuse Documents: In case of an excused absence, please attach your excuse documents in your communication with the instructor for recordkeeping purposes.
  5. Attendance Assignments/Quiz: The in-class attendance assignments/quiz are for formative assessment and taking attendance. Points from these assignments/quizzes are for the students who attend the class meetings only.
  6. Free Passes: Two to three lowest scores from the attendance assignments may be discounted at the end of the semester. 
  7. Multitasking: Unless you are being disruptive, multitasking (e.g., googling for fast fact-checks) is allowed in class. However, you are expected to take a seat in the back of the classroom if you intend to multitask often to avoid distracting the instructor and your peers.

Academic Honor

We follow the University 's Academic Honor Policy to prohibit:

  1. plagiarism,
  2. cheating,
  3. unauthorized group work,
  4. fabrication, falsification, and misinterpretation,
  5. multiple submission, 
  6. abuse of academic materials,
  7.  complicity in academic dishonesty, and 
  8. attempting to commit any above offense.  
Failing to comply with the academic honor policy will receive a zero point for the assignment for all students involved, and the University offices will be notified.

Course Schedule

Week Module Topic Technology Reading Assignment
1 Course Overview
  • Course Introduction
  • DB Overview
  • Business processes
  • SQL DBMS
  • SQL engines
  • DB job market
2 Technology Overview
  • Workflow/Business Rules
  • Enterprise Systems
  • 3-tier client-server application architecture
  • Unix-like OSs
  • DML (SELECT) syntax
  • Linux user management
  • Linux file management
  • SQLite & SQLiteStudio
HVT01
3 Database Operations w/ SQLite 1/2
  • CRUD
  • Language Documentation
  • DML (in SQLite)
  • Functions & Operators
  • Aggregate Functions (built-in)
  • Linux user management
  • Linux file management
  • SQLite & SQLiteStudio
HVT06 LinkedIn SQLite jobs
4 Database Operations w/ SQLite 2/2
  • SQL Operators
  • Tables and databases
  • Search (LIKE, regex)
HVT06
5 Database Management 1/2
  • Relational Algebra
  • RDBMSs & DB tools
  • DDL/DML (in Workbench)
  • Schema vs. Data Model
  • MySQL RDBMS
  • MySQL Workbench
  • DB/Table operations
  • Database modeling
  • Viewing MySQL sample DB
HVT02
6 Database Management 2/2
  • Logical DB design
  • Data Modeling (ERD): notations
  • Normalization
  • Business rules
  • E-R model, cardinality, entries/attributes
  • Modeling relationships
  • HVT04
  • Modeling
7 Conceptual DB Design
  • Schema vs model
  • Physical DB design
  • Optimization: Index
  • JOIN
  • Subqueries
  • Indexing
  • Partitioning
  • Index: HVT-II-5-264
8 Logical DB Design 1/3
  • Data types
  • Data integrity
  • Transaction
  • Subqueries
Midterm
9 Logical DB Design 2/3
  • View
  • CREATE VIEW AS v1
10 Logical DB Design 3/3
  • Trigger
  • Stored Procedure
  • Indexing
  • INNER JOIN
  • dump/import
  • Stored Procedure
11 Database Application Development 1/3
  • Technology stack
  • Static vs. Dynamic webpages
  • (Business process/rules/workflow; 3-tier)
  • Project: SDLC (waterfall & agile)
  • HTML, CSS, JS
  • Form-DB processing (HVT 398)
  • DB connection
Project plan (scenario, BP, business rules, DB model, schema)
12 Database Application Development 2/3
  • Form processing
  • Workflow with business rules
  • CRUD databases
IDE vs Editor: PhpStorm vs VSCode
13 Database Application Development 3/3
  • Sessions
  • DML in CRUD databases
14 NoSQL
  • JavaScript
  • CRUD
15 XML
  • JavaScript
  • CRUD
Final Exam
16 Project Presentation Final Project PPT

Assignment Schedule

All course assignments with due dates are listed below. To be successful in this course, be sure to complete and submit all required assignments by the due date.
Date Assignment Due
Assignment VM List 11:59pm
SCP test.txt to the VM and local class directory 11:59pm
VM Account Info 11:59pm
Assignment SQLite-chnook.db Screenshot 11:59pm
Assignment SQLite CRUD Lab 01 11:59pm
Assignment SQLite CRUD Lab 02 10:30am
Assignment SQLite CRUD Lab 03 10:30am
Assignment SQL Keywords of Different DBMS 11:59pm
Assignment Syllabus and Course Policies 11:59pm
Assignment MySQL Admin 10:30am
Assignment CRUD with Workbench 11:59pm
Assignment CRUD Lab 3 11:59pm
Assignment CRUD with Workbench HW 11:59pm
Assignment CRUD Lab SQL Script 10:30am
Assignment source employees.sql 9am
Assignment Attendace 02-11 10:30am
Assignment IBM HR Employees DB 11:59pm
Assignment MySQL Example employee DB CRUD w07 10:30am
Assignment MySQL employees DB CRUD w07 HW 11:59pm
Assignment DB SQL Queries w07-2 (MySQl employees DB) Retake 10:30am
Assignment ER Conceptual Model with Relationships University DB 11:59pm
Assignment Midterm 10:10am
Assignment ERD Physical Model & Implementation 11:59pm
Assignment Mary vs. FBI (INNER JOIN) 11:59pm
Assignment Salary Report 11:59pm
Assignment Current Employees Salary Report 11:59pm
Assignment Order & Customer Tables 11:59pm
Assignment Joint Table Mini Lab (Reverse Engineering) 11:59pm
Assignment SQL Query Review 11:59pm
Assignment The Car Table 11:59pm
Assignment SELECT Stmt 11:59pm
Assignment index.html Products Orders page 11:59pm
Assignment something/test.html 11:59pm
Assignment SQL Practice 11:59pm
Assignment PHP MySQL Connection Script 11:59pm
Assignment SQL Review w12 11:59pm
Assignment Project Ideas 11:59pm
Assignment Insert Form Data to DB 11:59pm
Assignment SQL Practice: INNER JOIN 11:59pm