CSCI 313 Database Management
Spring 2023

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
• Look at exercises at the end of the chapter
• My in class working page
• Wednesday, January 20th class video

Chapter 1 & 2

• May look at some of the end of chapter 1 exercises.
• Chapter 2 Notes
• Start creating a Database & Table via MariaDB
• Make initial web page in our public_html directory and test






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


• Monday, January 23rd class video - Continue with notes
• Wednesday, January 25th class video - We cover how to login to the server, make and publish our initial web page
• Friday, January 27th class video - We login to the server, login to MariaDB, connect to our DATABASE, make a table & data

Chapter 2 & 3

• Chapter 2 Notes
• 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
• Take a look at PhpMyAdmin https://csdb01.cs.edinboro.edu/phpmyadmin/

Scripts:

1. Create a .sql script file, you can use any text editor like notepad or visual studio code
2. FTP it over using an FTP client such as WinSCP or FileZilla, (image below). Login using your CS domain credentials. You may want to set up the default directories. following using your info.

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

• Monday class video - Chapter 2 Notes
• Wednesday class video - E-R Diagrams and create tables via command line
• Friday class video - We make an SQL script

• 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 the tables on page 135 & 136
  Add some data
  Run some queries from p. 136
  Sql for the Chapter 4 Emp-Assign-Proj questions
• Discuss exam - Chapters 1, 2, 3 & 4
• On Friday have the exam - In Class

• 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
• Ch 5 exercises & Build the Worker tables from the SQL script.
• Run a bunch of the queries from Chapter 5

• Add to our web page
• 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 csdb01.cs.edinboro.edu
• Your web page link: http://csdb01.cs.edinboro.edu/~yourlogin
• 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

No class Friday March 25th -> Heading to the PACISE conference

• Look at some other SQL

• Finish the Chapter 5 SQL examples
• Create a Trigger
• Look at normalizing tables -> exercises at end of the chapter
• Exam 2

• Normalization & Denormalization Notes
• Do some sample problems in class
• Questions on Project

4/17

• 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
Friday @ 10:00

3/8

3/22

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/5

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.

Complete your auto repair project:

Some of this you did in previous assignments
I may ask you to put your php files into D2L, you may need to rename the extension to txt.

Should be done by 4/15

Already done

Already done

Should be done by 4/19

Should be done by 4/21

• 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