CSCI 313 Database Management
Spring 2018

Week Starting	Topics
1/22	Introduction to the course Go over syllabus What you'll learn What you need Assignments Grading Expectations Take roll Useful Links: Assignments Companion web site for the book download the Cape Codd MS Access database Chapter 1 Introduction
1/29	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 Finish Chapter 1 Brief look at SQL on our Ch1 Database in Access Some MS Access criteria Examples Chapter 2 Part 1 Chapter 2 Part 2 Look at some review questions
2/5	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)
2/12	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
2/19	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.
2/26	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: https://csci313.cs.edinboro.edu/phpmyadmin/ You'll have to go past and accept the security warning (click on advanced) UN: cs domain login (use CS username ex: d123456t) PW: birthdate (6 digit birthdate - mmddyy) Modify your database using phpMyAdmin Create Table (for example CUSTOMER) Add data Create web page Simple PHP Page to display data Using a HTML editor (notepad++ or Dreamweaver) create this page Transfer this to your public_html folder in cslab100.cs.edinboro.edu Your web page link: http://csci313.cs.edinboro.edu/~yourlogin Should look something like this
3/5	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:
3/12	Spring Break
3/19	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 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 MySQL Cheat Sheet Chapter 4 Chapter 4 Notes Chapter 4 Database to work with
3/26	Chapter 5 Notes Help with the Video Store Rental Homwork Helper Page with sample code Look at the <option> HTML tag Code for the sample page 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 https://www.draw.io/
4/2	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.
4/9	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
4/16	Chapter 7 Chapter 7 Notes Do some sample problems in class Work on Project
4/23	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(.......... Scripts: 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 UPDATE INSERT Built INSERT DELETE VIEWS 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.
4/30	Final Review
Final Exam Monday May 7th @ 10:15

	Assignments
Due Date	Description
2/2 end of 2nd week	Project 1 - Chapter 1 Place your .accdb file in the drop box in D2L when finished
2/9 end of 3rd week	Exercises in Chapter 2: 2.17 - 2.39 Place in a text file and put in drop box in D2L.
2/16 end of 4th week	Exercises in Chapter 2: 2.40 - 2.60 Place in a text file and put in drop box in D2L.
3/9 end of 6th week [39] Solution	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
4/6 end of 10th week [50]	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 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.
Due: 5/9 11:30 PM	Group Project Due the Wednesday of Finals week