CSCI 313 Database Management
Spring 2022

Week
Starting

Introduction to the course & Chapter 1

• Go over syllabus
• What you'll learn
• What you need
• Assignments
• Grading
• Expectations -> New book this semester
• Take roll

• Start Chapter 1 an Introduction
• If time let's see if we can log into the system we'll be using:
• We are borrowing a machine used for Web Dev.
• https://cs323.cs.edinboro.edu/
• default password is CSWebPass!
• Look at exercises at the end of the chpater
• Using PHPMyAdmin create the sample tables in chapter 1



• My in class working page

Chapter 2

• Continue with the PHPMyAdmin exercise of creating the "My University" tables -> https://cs323.cs.edinboro.edu/phpmyadmin/
• May look at some of the end of chapter 1 exercises.
• Chapter 2 Notes

Chapter 3

• Finish Chapter 2
• Discuss tools, for example: On-Line ER Diagram builder
• Try out some SQL with our "My University" tables via the command line
• mysql command line help






Run some SQL like:
• SHOW TABLES;
• DESCRIBE Student;
• SELECT * FROM Student;
• .. and some more SQL from the command line.

Scripts:

1. Create a .sql script file, you can use any text editor like notepad or visual studio code
2. FTP it over (using FileZilla, image below) go to File -> Site Manager... then enter the following using your info.

3. Putty in
4. get into mysql mode
5. to run it:
   • mysql>SOURCE /home/dtucker/script.sql


• Start Chapter 3

• Finish Chapter 3 Notes
• Look at exercise 3.5
• Dentist's Office
• On-Line ER Diagram builder
• Start Chapter 4

• Look at some chapter 4 review exercises
• Make an SQL script
  Create tables on p. 149
  Add some data
  Run some queries from p. 150
  Sql for the Chapter 4 Emp-Assign-Proj questions
• Start Chapter 5
• Look at review questions in Chapter 5 (5.3 - 5.25) but not all
• SQL Script (MySQL) Ver. 1 to build the Worker Project project used in the chapter 5 exercises
• SQL Script (MySQL) Ver. 2 to build the Worker Project project used in the chapter 5 exercises

• Discuss exam - Chapters 1, 2, 3 & 4
• On Thursday have the exam - In Class
• Continue with Chapter 5
• Continue with Ch 5 exercises & Build the Worker tables from the SQL script.
• Run a bunch of the queries from Chapter 5

• Create web page
• 1^st we need to make sure we have a public_html directory and it's accessible to the world
• Make a simple PHP web page and move it to the public_html directory
• Expand on that and make a simple PHP Page to display data
• Transfer this to your public_html folder in cs323.cs.edinboro.edu
• Your web page link: http://cs323.cs.edinboro.edu/~yourlogin
• Video of Thursday's class
• Discuss what SQL Injection is
• Continue with connecting via a web server.
• Edit our database interaction web page
  • Make sure we can all see the data in our tables via a web page
  • Add a HTML page with a FORM to collect data
  • Use the INSERT INTO sql statement to add a record to a table
• Mysqli functions
• Using PhpMyadmin -> We're going to cover most of this material by example, building our web page that queries the database.

• Edit our database interaction web page
  • Continue with writing some of the SQL solutions from chapter 5
  • Add a HTML page with a FORM to collect data
  • Use the INSERT INTO sql statement to add a record to a table - We can look at 5.11
  • Looks like we should add primary keys to our tables
• 3/15 Meeting Recording

• Create a View
• Look at some other SQL
• Part of Thursday's class where we do some SQL problems from Chapter 5

• Finish the Chapter 5 SQL examples
• Create a Trigger
• Tuesday's class
• Look at normalizing tables -> exercises at end of the chapter
• Exam 2 - Take home for Thursday (no class meeting that day)

• Normalization & Denormalization Notes
• Do some sample problems in class
• Questions on Project
• Recording of Thursday's Class

4/18

• Some notes on Security Chapter 8
• What is/are:
  • Transaction Management
  • Distributed Databases
  • Data Warehouses and Data Mining
  • Big Data and NoSQL Chapter 12
• Final Exam Review (Probably will not have to write SQL on the final)

Final Exam
Tuesday @ 12:30

3/21

4/1

Make me a web page that has links to the following:

1. A page to add a customer
2. A page to add a car - for the customer field have a dropdown
3. A page to update a customer - Like in class make a drop down to select a customer

4/12

• A page to update a car - Have a dropdown that selects a customer, you then hit the submit button, then another dropdown shows that customer's cars. You select one of those and it fills out the fields for that car so the car can be edited.

4/12

Add to your auto repair project:

Add to your auto repair project:

1. trigger - Add a field in the mechanic table Num_of_Jobs_Assigned add a trigger that increments this field automatically whenever a mechanic is assigned a job.

4/28

Complete your auto repair project:

Please put your URL in the notes/message area of the drop box.

Should be done by 4/15

Should be done by 4/16

Should be done by 4/18

Should be done by 4/20

Should be done by 4/22

• Maybe add a button at the bottom of this page to add another repair to the active job.
• I would put some dummy data in the repairs table so you have something in your drop down to choose from for the repairs (Breaks, Oil Change, Struts, etc...)
• You'll be adding to the Repair_Job table, not the repair like in version 1. So you make a new repair_ID but use the active job_ID.
Image of notes from the other day on V2.

Should be done by 4/24

• Add Mechanic
• Add Parts
• Tie in the Estimate