CSCI 313 Database
Spring 2016


Week
Starting
Monday
Wednesday
1/18

Useful Links:

Introduction to the course

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

Chapter 1 Introduction
1/25

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

 
2/1

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.

Chapter 2

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
  • Do some multi table queries (2.40 -  2.60)

2/8

Chapter 3

  • Questions on SQL from Ch2
  • Chapter 3
  • Practice Problem normalizing tables

Chapter 3

 
2/15

Review Chapter 2 & 3

  • Answer questions about Inner joing and sub queries

Review

  • 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). Lets do the Queen Ann Curiosity shop problem page 173 in class.
2/22

Exam 1

  • Chapters 1, 2 & 3

On-Line Database
& Chapter 11

2/29

Chapter 11

Connect On-Line

  • Continue wiht last class
  • connecting to our database
  • Start implementing the Chapter 11 Marcia's Dry Cleaning project.
3/7

Spring Break
3/14

Chapter 11

  • Finish Chapter 11
    • XML
    • SQL Injection
  • Make sure everyone able to display and add a record via a web page.

  • Notes for Connecting via putty:

    connect to csci313.cs.edinboro.edu
    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:
    (SELECT * FROM CUSTOMERS)

    ! Be careful about your case

    mysql command line help

Chapter 4

  • Chapter 4 Notes
  • Answer our sql to xml question,
    use the --xml parameter when starting mysql on the server. Then all the quieries are outuput in xml. Not sure if that does us any good on line though.

    type: mysql -u username -p --xml

  • Chatper 4 Database to work with

 
3/21

Chapter 5

  • Notes
  • Do some Ch 5 problems

Chapter 5

  • Look at DB desgin patterns
  • Do some Ch 5 problems
  • Build or Subscription Form
  • Writer's Patrol Case Questions
  • On-Line ER Builder Tools
3/28

Chapter 6

  • Notes
  • Talk about Team Project
  • In-Class:
    • Writer's State Patrol Case
    • Using Access

 

Review for Exam 2

  • 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.
4/4

Exam 2

Talk about the project

  • Work on Phase 1
  • Use MS Access for our prototype
  • Email me your URL for the Marcia's Dry Cleaner assignment
4/11

Chapter 7

 Work on Project

4/18

&

4/25

Chapter 7

  • Answers some review questions by making a script file

Scripts:

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

Some more SQL

  • UPDATE
  • INSERT
  • Bult INSERT
  • DELETE
  • VIEWS
  • Triggers - INSERT, UPDATE, DELETE
  • Nice Tutorial on Stored procedures
5/2 Final Review  


Final Exam Wednesday @ 6:00 pm - 8:00 pm

 

 

  Assignments
Due Date Description
1/29 [40-11] Project 1 - Chapter 1
Place your .accdb file in the drop box in D2L when finished

2/5

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

2/12

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

2/22

 [30-17] Answer the questions for the Morgan Importing Case Questions.  These start on page 175.

3/23

[50-20] CH-11: Marcia's Dry Cleaning Case Question, Page 531. Do the following parts & page:

  • First you need to go to Appendix 10C to get the description and data to create the INVOICE table. (may want to do a CTRL-F and search for "Marcia") Find where it talks about the INVOICE table. Create it using PhPMyAdmin or command line and also add the sample data they have on page 10C-111
  • C: Edit the INVOICE table you just made and add the new field. (probably using PhPMyadmin)
  • For the next part, 1st display the CustomerInvoice query with no filter.
  • Then grab a date from the user and run a query based on that (greater than or equal to).
  • Same for E, display all the customers, then display a table with the results of the query (phone last name and first name of the person you entered)
  • D & E: (Done at same time) Instead of making a VIEW, create and run a query for the results of the VIEW specified in part D.
  • F: For part F & G you can either rmake 2 pages or use the PHP_SELF feature.
  • G:
  • Make one web page with links to your answers, name it index.html and place it in the root of your public_html directory.

[10] A,B,C → Create Invoice and Customers Tables add status field, add data
[5] Extra → Display Invoice Data
[10] D, E → Display LName, FName, Phone, Invoice#, DateIn, DateOut, TotalSales
[5] Extra → Display Customer Data
[10] F → Display Query based on data entered
[10] G → Display Query based on Phone, LName, FName.

 

May 4th

 [50-23] Group Project