Daniel Caraway

Daniel Caraway

I put the fun in functions.

MSDS IST 659: Database Admin & MGMT

3 minute read

Database Administration Concepts and Database Management

MSDS - Q2: IST659

Description:

IST 659 is an introductory course to database management systems. This course examines data structures, file organizations, concepts, and principles of database management systems (DBMS) as well as data analysis, database design, data modeling, database management, and database implementation. More specifically, it introduces hierarchical, network, and relational data models; entity-relationship modeling; basics of Structured Query Language (SQL); data normalization; and database design.

Using Microsoft’s Access and SQL Server DBMSs as implementation vehicles, this course provides hands-on experience in database design and implementation through assignments, lab exercises, and course projects. This course also introduces advanced database concepts such as transaction management and concurrency control, distributed databases, multitier client/server architectures, web-based database applications, data warehousing, and NoSQL.

Learning Objectives:

After taking this course, the students will be able to:

  • Describe fundamental data and database concepts
  • Explain and use the database development lifecycle
  • Create databases and database objects using popular database management system products
  • Solve problems by constructing database queries using Structured Query Language (SQL)
  • Design databases using data modeling and data normalization techniques
  • Develop insights into future data management tool and technique trends
  • Recommend and justify strategies for managing data security, privacy, audit/control, fraud detection, backup and recovery
  • Critique the effectiveness of DBMS in computer information systems

Deliverables:

Class Outline:

Assignments and Deliverables

  • Week 4: Project 1 Deliverable
  • Week 10: Project 2 Deliverable
  • Week 1 Lab
  • Week 2 Lab
  • Week 3 Lab
  • Week 4 Lab
  • Week 5 Lab
  • Week 6 Lab
  • Week 7 Lab
  • Week 8 Lab
  • Week 9 Lab
  • Week 10 Lab

Week 1 | The Relational Data Model

  • 1.1 Introduction
  • 1.2 Readings
  • 1.3 Basic Concepts and Definitions
  • 1.4 The Database Approach
  • 1.5 Popular Database Management Systems
  • 1.6 Advantages of the Database
  • 1.7 A Data Modeling Exercise
  • 1.8 Lab Guidance 1
  • 1.9 Lab Guidance 2
  • 1.10 Lab DiscussionForum

Week 2 | Conceptual Modeling

  • 2.1 Introduction
  • 2.2 Readings
  • 2.3 Data Modeling
  • 2.4 The Conceptual Model
  • 2.5 ER Model Overview
  • 2.6 Modeling Organizational Rules
  • 2.7 Modeling Entities and Attributes
  • 2.8 Modeling Relationships
  • 2.9 Lab Guidance 1
  • 2.10 Project Update
  • 2.11 Lab Guidance 2
  • 2.11.1 Lab DiscussionForum
  • 2.12 Life as a Data Scientist

Week 3 | Logical Modeling

  • 3.1 Introduction
  • 3.2 Readings
  • 3.3 The Logical Model
  • 3.4 The Relational Data Model
  • 3.5 Integrity Constraints
  • 3.6 Transforming ER Diagrams Into Relations, Part 1
  • 3.7 Transforming ER Diagrams Into Relations, Part 2
  • 3.8 Data Types
  • 3.9 Lab Guidance 1
  • 3.10 Project Update
  • 3.11 Lab Guidance 2
  • 3.11.1 Lab Discussion Forum

Week 4 | Normalization

  • 4.1 Introduction
  • 4.2 Readings
  • 4.3 What Is Normalization?
  • 4.4 The Normal Forms
  • 4.5 Dependencies and Keys
  • 4.6 Converting to First Normal Form (1NF)
  • 4.7 Converting to Second Normal Form (2NF)
  • 4.8 Converting to Third Normal Form (3NF)
  • 4.9 Foreign Keys
  • 4.10 Beyond 3NF
  • 4.11 Lab Guidance 1
  • 4.12 Project Update
  • 4.13 Lab Guidance 2
  • 4.13.1 Lab Discussion Forum

Week 5 | Physical Database Design

  • 5.1 Introduction
  • 5.2 Readings
  • 5.3 An Overview of SQL
  • 5.4 The Physical Domain
  • 5.5 SQL Table Creation
  • 5.6 Lab Guidance 1
  • 5.7 Project Update
  • 5.8 Lab Guidance 2
  • 5.8.1 Lab DiscussionForum

Week 6 | Querying, Inserting, Updating, and Deleting Data

  • 6.1 Introduction
  • 6.2 Readings
  • 6.3 The Internal Data Model
  • 6.4 SQL INSERT Statement
  • 6.5 SQL SELECT Statement
  • 6.6 SQL WHERE and ORDER BY Clauses
  • 6.7 SQL FROM Clause (with JOINs)
  • 6.8 SQL UPDATE Statement
  • 6.9 SQL DELETE Statement
  • 6.10 Project Update
  • 6.11 Lab Guidance
  • 6.11.1 Lab DiscussionForum

Week 7 | Advanced Querying

  • 7.1 Introduction
  • 7.2 Readings
  • 7.3 JOIN Types
  • 7.4 SQL Aggregates
  • 7.5 SQL GROUP BY and HAVING Clauses
  • 7.6 SQL SUM()
  • 7.7 SQL COUNT()
  • 7.8 SQL MIN(), MAX(), and AVERAGE()
  • 7.9 Aliasing Columns and Tables
  • 7.10 Project Update
  • 7.11 Lab Guidance
  • 7.11.1 Lab DiscussionForum

Week 8 | Database Programming

  • 8.1 Introduction
  • 8.2 Readings
  • 8.3 The External Data Model
  • 8.4 SQL Programming Objects
  • 8.5 SQL Views
  • 8.6 SQL User-Defined Functions
  • 8.7 SQL Stored Procedures
  • 8.8 SQL Transactions
  • 8.9 Lab Guidance 1
  • 8.10 Project Update
  • 8.11 Lab Guidance 2
  • 8.11.1 Lab DiscussionForum

Week 9 | Database Administration

  • 9.1 Introduction
  • 9.2 Readings
  • 9.3 SQL Security
  • 9.4 Backup and Recovery: Simple Model
  • 9.5 Backup and Recovery: Full Model
  • 9.6 Database Performance and Tuning
  • 9.7 Concurrency Control
  • 9.8 Lab Guidance 1
  • 9.9 Project Update
  • 9.10 Lab Guidance 2
  • 9.10.1 Lab Discussion Forum

Week 10 | User Interface Design

  • 10.1 Introduction
  • 10.2 Readings
  • 10.3 Open Database Connectivity (ODBC)
  • 10.4 MS Access: Connecting Tables
  • 10.5 MS Access: Query Designer
  • 10.6 MS Access: Forms
  • 10.7 MS Access: Reports
  • 10.8 Revisiting the Levels of Data Model Abstraction
  • 10.9 Project Update
  • 10.10 Lab DiscussionForum

You may also enjoy