Database Management

CSCI 317

Winter 2019

Credits: 3

Requirements Met: CSCI Elective

Class Meeting Metadata
Meets: T/R 1:30 - 3:00pm
Classroom: Science Addition 114
Instructor's Metadata
Instructor: Jason T. Mickel, Ph.D.
E-Mail: mickelj@wlu.edu How to Email a Professor
Phone: (540) 458-8653
Office: Leyburn M33
Office Hours: M 11:00 - 12:00
T 11:00 - 12:00
W 1:00 - 2:00
Or by appointment

Overview

This course introduces database design using the entity- relationship (ER) model, the relational database model including normal forms and functional dependencies, the SQL database query language, database management systems and security, and emerging topics and trends in databases. A major semester-long project will have student teams acting as both client and database developer.

Learning Outcomes

  • Discuss and explain the importance of data and the difference between file management and databases
  • Discuss, explain, and create entity-relationship diagrams
  • Discuss, explain and develop database schemas for data representation in the relational model
  • Use SQL to implement database schemas and to query data
  • Understand the principles of managing a database system
  • Perform the interview process to gather requirements and specifications from a client
  • Work as part of a group to design and implement a relational database based on client-driven specifications

Work & Time Expectations

Expect to spend on average 9 hours per week of time outside of class meetings on assignments and preparation. This is keeping with a normal undergraduate expectation of 3 hours of out-of-class work for each hour spent in class.

Prerequisites

CSCI 209, Software Development

Texts

Required

Principles of Database Management
2018
Wilfried Lemahieu; Seppe Vanden Broucke; Bart Baesens
Links:
W&L Bookstore
Amazon
Google Link for Other Sources
Learning PostgreSQL
2015
Salahaldin Juba; Achim Vannahme; Andrey Volkov

Free access online from Safari Books (through the VIVA consortium)
Additional required readings will be noted in the schedule below.

Software

ERDPlus
Access and register for free @
https://erdplus.com/
Heroku
Register for free @
https://www.heroku.com/
pgweb
Click on the 'Deploy to Heroku' button once your Heroku account is set up @
https://github.com/sosedoff/pgweb/

Evaluation

Exercises 20%

There will be a handful of weekly exercises early in the course to cement your knowledge of the concepts we discuss. They will be submitted through the course for CSCI 317 that is set up in Sakai. Students working on the same assigned questions may work together in order to learn from each other; however, each student will submit individual work noting the partners (if any) with whom they have worked.

Please refer to the links in the menu above for assignment details.

In addition, there will be several in-class exercises as noted in the schedule below. In-class exercises can only be made up for if an absence is noted in advance.

Exams 25%

There will be two exams: one in Week 4 and a second in Week 9 (two weeks after Washington Break) designed to further test the database knowledge you have acquired.

The exams will be open book, note, and Web; however, you may not consult classmates or any others besides your professor through any form of communication (included but not limited to: written, spoken, electronic, telepathic, osmotic, semaphore, hand signals, or Morse code) during the period that the exam is available.

Each also will be comprehensive of all information presented up to that point. Consequently, the exams will be designed to test your ability to apply knowledge versus your capability to memorize.

Both exams will be given outside of our scheduled class meetings, and students will have ample time to complete them.

Major Project 45%

By the end of the term, you and your team members will produce a completed database with functioning, efficient queries. The database will be developed based on a design built from specifications gathered from another team. The project simulates real-world scenarios in which a company communicates with an external contractor for system development.

The majority of project work will occur after Washington Break, and throughout the second half of the term, there will be various deliverables to keep your team on track toward building a functional solution.

For additional details, please see the various deliverable assignments in the 'Assignment' menu above as they become available.

Participation 10%

During the lecture portion of the course, you can gain participation points by engaging through asking and answering questions. While working in your project groups during class time, I expect to witness engagement in what your team is working on through any combination of active discussion, note-taking, diagramming, or coding.

I have been working with this material professionally for many years, so I know what I'm doing. I need YOU to be brave and converse with me when a concept isn't clear.

Missing class, consistently arriving late, or being unprepared for discussion of readings will negatively impact your participation grade.

Tentative Schedule

Dates of activities and due dates for assignments are always subject to minor shifting. Refer to back to this schedule regularly.

Week 1

Jan 7 - 13

Warning: Undefined array key "daily" in /home/jasontmi/public_html/static/php/template-syllabus.php on line 229
Topics
Readings
Hands-On Activities
Assignments
  • Principles of Database Management (PDM) Chapters 1 & 4

  • Warning: Undefined array key "book" in /home/jasontmi/public_html/static/php/template-syllabus.php on line 290
  • Starting the Process

  • Warning: Undefined array key "book" in /home/jasontmi/public_html/static/php/template-syllabus.php on line 290
  • Analyzing the Current Database
  • Creating Requirements
  • Getting to Know ERDPlus
  • Exercise In-Class #1: Reading and Reacting to a Database Development Scenario
    (Due: Thu, Jan 10 @ 11:55pm)
  • Exercise #1: ER Diagrams and Requirements
    (Due: Wed, Jan 16 @ 11:55pm)

Week 2

Jan 14 - 20

Warning: Undefined array key "daily" in /home/jasontmi/public_html/static/php/template-syllabus.php on line 229
Topics
Readings
Hands-On Activities
Assignments
  • PDM Chapters 3 & 6
  • Updating Requirements
  • Using ERDPlus to Map Relational Schemas
  • Exercise In-Class #2: Updating Your Design Based on New Information
    (Due: Thu, Jan 17 @ 11:55pm)
  • Exercise #2: Mapping Relational Schemas
    (Due: Wed, Jan 23 @ 11:55pm)
  • Project Deliverable #1: Preparing to Communicate Requirements
    (Due: Fri, Feb 15 @ 11:55pm)

Week 3

Jan 21 - 27

Warning: Undefined array key "daily" in /home/jasontmi/public_html/static/php/template-syllabus.php on line 229
Topics
Readings
Hands-On Activities
Assignments
  • PDM Chapter 6
  • Normalizing Relational Schemas
  • Exercise In-Class #3: Normalizing Practice
    (Due: Thu, Jan 24 @ 11:55pm)
  • Exercise #3: Normalization
    (Due: Wed, Jan 30 @ 11:55pm)

Week 4

Jan 28 - Feb 3

Warning: Undefined array key "daily" in /home/jasontmi/public_html/static/php/template-syllabus.php on line 229
Topics
Readings
Hands-On Activities
Assignments
  • PDM Chapters 2 & 7
  • Learning PostgreSQL (PGSQL) Chapter 5
  • Working with SQL
  • Exam #1: Database Design Concepts
    (Opens Thu, Jan 31 @ 5:00pm - Closes Mon, Feb 4 @ 11:55pm)

Week 5

Feb 4 - 10

Warning: Undefined array key "daily" in /home/jasontmi/public_html/static/php/template-syllabus.php on line 229
Topics
Readings
Hands-On Activities
Assignments
  • PDM Chapters 7, 9, & 14
  • Working with SQL
  • Exercise #4: SQL
    (Due: Fri, Feb 15 @ 11:55pm)

Week 6

Feb 11 - 17

Warning: Undefined array key "daily" in /home/jasontmi/public_html/static/php/template-syllabus.php on line 229
Topics
Readings
Hands-On Activities
Assignments
  • PDM Chapter 13.2
  • PGSQL Chapter 8
  • Database Administration Plan
  • Exercise In-Class #4: Administration Plan
    (Due: Thu, Feb 14 @ 11:55pm)
  • REMINDER — Project Deliverable #1: Preparing to Communicate Requirements
    (Due: Fri, Feb 15 @ 11:55pm)

Week 7

Feb 18 - 24
Washington Break

Warning: Undefined array key "daily" in /home/jasontmi/public_html/static/php/template-syllabus.php on line 229
Topics
Readings
Hands-On Activities
Assignments

Week 8

Feb 25 - Mar 3

Warning: Undefined array key "daily" in /home/jasontmi/public_html/static/php/template-syllabus.php on line 229
Topics
Readings
Hands-On Activities
Assignments
  • Meeting with Your Client Group to Start the ERD
  • Project Deliverable #2: Development Group ERD
    (Due: Fri, Mar 8 @ 11:55pm)

Week 9

Mar 4 - 10

Warning: Undefined array key "daily" in /home/jasontmi/public_html/static/php/template-syllabus.php on line 229
Topics
Readings
Hands-On Activities
Assignments
  • Exam #2 Prep
  • NoSQL and Big Data

  • Warning: Undefined array key "url" in /home/jasontmi/public_html/static/php/template-syllabus.php on line 276
    Project Work
  • PDM Chapter 10
  • Refining the ERD
  • Exam #2: SQL and Database Management
    (Opens Sat, Mar 9 @ 5:00pm - Closes Wed, Mar 13 @ 11:55pm)

Week 10

Mar 11 - 17

Warning: Undefined array key "daily" in /home/jasontmi/public_html/static/php/template-syllabus.php on line 229
Topics
Readings
Hands-On Activities
Assignments
  • PDM Chapters 11 & 19
  • Revising the ERD and Mapping to the Relational Model
  • Project Deliverable #3: Delivering the Database Structure
    (Due: Fri, Mar 22 @ 11:55pm)

Week 11

Mar 18 - 24

Warning: Undefined array key "daily" in /home/jasontmi/public_html/static/php/template-syllabus.php on line 229
Topics
Readings
Hands-On Activities
Assignments
  • Applying NoSQL and Big Data

  • Warning: Undefined array key "url" in /home/jasontmi/public_html/static/php/template-syllabus.php on line 276
    Project Work
  • Creating the Database
  • Planning for Big Data
  • Exercise #5: Planning for Big Data
    (Due: Sun, Mar 24 @ 11:55pm)

Week 12

Mar 25 - Mar 31

Warning: Undefined array key "daily" in /home/jasontmi/public_html/static/php/template-syllabus.php on line 229
Topics
Readings
Hands-On Activities
Assignments

  • Warning: Undefined array key "url" in /home/jasontmi/public_html/static/php/template-syllabus.php on line 276
    Exam #2 Review
  • Project Deliverable #4: Populate the Database

  • Warning: Undefined array key "url" in /home/jasontmi/public_html/static/php/template-syllabus.php on line 276
    Project Work
  • Refining and Populating the Database
  • Project Deliverable #4: Populated Database
    (Due: Mon, Apr 1 @ 11:55pm)

Week 13

Apr 1 - 9

Warning: Undefined array key "daily" in /home/jasontmi/public_html/static/php/template-syllabus.php on line 229
Topics
Readings
Hands-On Activities
Assignments
  • Project Deliverable #5: Queries and Performance
  • Programming using a Database Backend

  • Warning: Undefined array key "url" in /home/jasontmi/public_html/static/php/template-syllabus.php on line 276
    Project Work

  • Warning: Undefined array key "url" in /home/jasontmi/public_html/static/php/template-syllabus.php on line 276
    Course Wrap-up
  • PDM Chapter 15
  • Building Queries and Improving Query Performance
  • Project Deliverable #5: Queries with Performance
    (Due: Tue, Apr 9 @ 11:55pm)