Introduction to the course
- Go over syllabus
- What you'll learn
- What you need
- Take roll
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
- Continue with SQL
- go over the notes of the SQL commands
- Performing Calculations with SQL Queries
- 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
- 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)
- SQL joins from Ch2
- Chapter 3
- Practice Problem normalizing tables
- 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.
- Feb 26th - Chapters 1, 2 & 3
& 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 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:
- Finish Chapter 11
- 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 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
run a query like:
(SELECT * FROM CUSTOMERS)
! Be careful about your case
mysql command line help
MySQL Cheat Sheet
- Help with the Video Store Rental Homwork
- Look at DB design patterns
- Look at some Ch 5 problems
- Build a Subscription Form
- Writer's Patrol Case Questions
- On-Line ER Builder Tools
- If time, talk about Team Project
- 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.
- 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
- 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
- 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(..........
- Create a .sql script file
- FTP it over
- Putty in
- get into mysql mode
- to run it:
- mysql>SOURCE /home/CS/dtucker/script.sql
Some more SQL
- Built INSERT
- Triggers - INSERT, UPDATE, DELETE
- 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.
Monday May 7th @ 10:15
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.
 Part A: Give an example and explanation of when each of these dependencies fail.
but implement the following tables with sample data in PhyMyAdmin
- STORE (StoreName, City, Country)
- STORE_OWNER (StoreName, City, OwnerName)
- STORE_SPECIALTY (StoreName, City, Specialty)
- Explain what multivalued dependencies there could be in this table, give real-life example
- Do part 7, convert the SHIPMENT table to BCNF & 4NF
- 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:
- Show all movies rented for a given customer, show customer name and movie title
- Show all movies by any one rented between given dates
- Show all movies rented for a given customer between given dates
- 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
|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
Due the Wednesday of Finals week