CSCI 313 Database
Spring 2018



Introduction to the course

  • Go over syllabus
  • What you'll learn
  • What you need
  • Assignments
  • Grading
  • Expectations
  • Take roll

Useful Links:

Chapter 1 Introduction


Hands on Exercise & Chapter 1

  • Continue with Chapter 1
  • Introduce MS ACCESS
  • Work on the Chapter 1 Project p. 33
  • Add some SQL examples to our database

Chapter 2


Chapter 2

  • Continue with SQL
  • go over the notes of the SQL commands
  • Performing Calculations with SQL Queries
    • Built in
    • Arithmetic
  • Do some hands-on examples
  • Looks like in Access 13 the " % " 
    and " _ " work with the LIKE command.

Some insight on Joins

  • Part 2 of Chapter 2 (multiple tables)
  • Outer Joins
    • Left Outer Join -all data from left table & matching right
    • Right Outer Join - all data from right table & matching left
    • Full Outer Join - All data
  • Inner Joins
    • Has to be a match
  • NOTE: To test these types of joins you'd need tables where there is data in the left table that is not in the right and vise-versa. (with keys of course)
  • StudentLocker.accdb  -> practice joins
  • WPC-> practice joins
  • Do some multi table queries (2.40 -  2.60)


Chapter 3

  • SQL joins from Ch2
  • Chapter 3
  • Practice Problem normalizing tables
  • Questions
  • More problem normalizing tables
  • Look at some problems in the book
  • In Class Problems

Review Chapter 2 & 3

  • Continue with Chapter 3
  • In Class Problems and some notes
  • Answer questions about Inner joining and sub queries
  • Database design and prepare for exam 1.
  • Look at:
    • Review questions from Ch 1
    • Writing SQL Queries (Ch 2)
    • Normalization and the relational model (Ch 3). Queen Ann Curiosity shop problem page 173 in class.

Exam 1

  • Feb 26th - Chapters 1, 2 & 3

On-Line Database
& Chapter 11

  • See if we can log in to the server
    • To make use of phpMyAdmin:
  • Modify your database using phpMyAdmin
    • Create Table (for example CUSTOMER)
    • Add data
  • Create web page

Chapter 11

Connect On-Line

  • Chapter 11 Notes
  • SQL Injection
  • Continue with connecting via a web server.
  • Edit our database interaction web page
    • Make sure we can all see our CUSTOMER table
    • Add a HTML page with a FORM to collect data
    • Use the INSERT INTO sql statement to add a record to the CUSTOMER table
  • Mysqli functions
  • Online Chapter  10C
    • Using PhpMyadmin -> We're going to cover most of this material by example, building our web page that queries the database.
    • If time, discuss Marcia's Dry Cleaning Case Study in this on-line chapter.

Filezilla Information to connect to the web server:


Spring Break


Chapter 11

  • Finish Chapter 11
    • XML
    • How to display XML via SQL:
      use the --xml parameter when starting mysql on the server. Then all the queries are output in xml. Not sure if that does us any good on line though.

      type: mysql -u username -p --xml

  • Make sure everyone able to display and add a record via a web page.

  • Connecting via putty:

    connect to
    using your cs domain username and password

    type: mysql -u username -p

    login using your database username and password

    Connect to your database
    connect dt123456
    run a query like:

    ! Be careful about your case

    mysql command line help

MySQL Cheat Sheet

Chapter 4


Chapter 5



Chapter 6

  • Notes
  • If time, talk about Team Project
  • In-Class:
    • Writer's State Patrol Case ; p. 244
    • Using Access
  • Normalize a table
    • Look at tables on page 173, 174
    • Build an ER diagram
    • Build tables
  • Look at some questions in Ch 4.
  • Look at some questions in Ch 5.

Chatper 6

  • Talk about the group project
  • Work on Phase 1
  • Use MS Access for our prototype
  • Review for the Exam - Please do 5.62 ahead of time, we can look at this for the review.
  • Exam 2 - Friday the 13th



Chapter 7



Chapter 7

  • Answers some review questions by making a script file
  • Notes on command line sql:
    • Look at Chatper 11 block for more info
    • Everything is case sensitive
      • commands usually lower case
      • naming
    • You need to create a database or connect to one first
      • ex: connect COMPUTER_EMP;
      • may need to create database COMPUTER_EMP;
      • to test, do something like CREATE TABLE EMPLOYEE(..........


  1. Create a .sql script file
  2. FTP it over
  3. Putty in
  4. get into mysql mode
  5. to run it:
    • mysql>SOURCE /home/CS/dtucker/script.sql

Some more SQL

  • Built INSERT
  • Nice Tutorial on Stored procedures

Sample PHP Page to UPDATE

Code for the Update page

Discuss the Grading of the project, specifically how I plan to split the points.

4/30 Final Review

Final Exam Monday May 7th @ 10:15



Due Date Description


end of 2nd week

Project 1 - Chapter 1
Place your .accdb file in the drop box in D2L when finished


end of 3rd week

Exercises in Chapter 2:  2.17 - 2.39
Place in a text file and put in drop box in D2L.


end of 4th week

Exercises in Chapter 2:  2.40 - 2.60
Place in a text file and put in drop box in D2L.


end of 6th week



Modified version of the Morgan Importing Case Questions.  These start on page 175.

[14] Part A: Give an example and explanation of when each of these dependencies fail.
Part B: Skip
[9]but implement the following tables with sample data in PhyMyAdmin

  • STORE (StoreName, City, Country)
  • STORE_OWNER (StoreName, City, OwnerName)
  • STORE_SPECIALTY (StoreName, City, Specialty)

Part C:

  • [4]Explain what multivalued dependencies there could be in this table, give real-life example
  • [6]Do part 7, convert the SHIPMENT table to BCNF & 4NF
  • [6]Implement them in PhyMyAdmin


end of 10th week


Video Rental Database

  • Create a folder for a Video Rental Web Site in your public html directory
  • Create the tables in the ER diagram below in PHPMyAdmin
  • Write PHP/HTML programs that creates Web forms for entering the information for the 3 tabls in the ER Diagram.
  • Add some data
  • Write PHP/HTML pages for the following queries:
    1. Show all movies rented for a given customer, show customer name and movie title
    2. Show all movies by any one rented between given dates
    3. Show all movies rented for a given customer between given dates
    4. Show total cost for all rentals for a given customer. Show customer name and total
  • Make simple pages, no styling needed
  • Sample home page for your project

Video Rental ER Diagram

Grading Rubric

May 2nd
11:30 PM
Scripting Project - Starts on Page 366; 7.101 - 7.109, but you'll need to look at the previous questions and figure 7-44, 45, 46 to figure it out.

5/9 11:30 PM

Group Project

Due the Wednesday of Finals week