Eup Logo

CSCI 313 Database Management
Spring 2023



Introduction to the course & Chapter 1

Zoom Link
Meeting ID: 997 0946 5286
  • Go over syllabus
  • What you'll learn
  • What you need
  • Assignments
  • Grading
  • Expectations -> New book this semester
  • Take roll

Chapter 1 & 2


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
  • 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
2/6 Recordings
  • Monday class video - Finish Chapter 2 & Start Chapter 3 Notes
  • Wednesday's class video - Look at ER Diagram builder, Discuss Making ER Diagrams & Chapter 3 Notes
  • Friday's class video - Make a PHP web page that connects to our database
  • 2/13
  • Monday's class video - Finish Chapter 3, Start on Chapter 4
  • Wednesday's class video - Review for the exam
  • 2/20
    • 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
  • Monday's class video - Finish Chapter 4's notes
  • Wednesday's class video - Start Chapter 5
  • Friday's class video - Did some SQL from Chapter 5
  • 2/27
    • 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
      • Your web page link:
    • 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.
  • Monday's class video - Discuss the project ER Diagram, continue with SQL in Chapter 5 (started recording late)
  • Wednesday's class video - Did some more SQL, then started to create a web page that will display a query
  • Friday's class video - Display a query in HTML & PHP
  • 3/6

    Spring Break

    • 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
  • Monday's class video - Added some sample queries to our web page & started a form
  • Add a worker to the worker table
  • Work on the update page
  • 3/20
  • Let's step back answer questions and review what we've done so far
  • Revisit the SQL Script for the -> Worker-Projects Database
  • Order of table creation matters if you are making constraints
  • Note the dropping of constraints before the tables
  • Also note the difference between ` and '

  • SQL Script (MySQL) Ver. 2 Latest version includes the Primary & Foreign Keys to build the Worker Project project used in the chapter 5 exercises
  • Let's finish our update page
  • Create a page to delete a worker in the worker table
  • Continue looking at Chapter 5 SQL problems from the book.
  • Create a View
  • No class Friday March 25th -> Heading to the PACISE conference
  • Monday's class video - Finish the update & add a delete record page
  • Discuss the homework and make a view
  • no class Friday
  • 3/27
    • Look at some other SQL
    *** It's time for another Exam! - Next Week: SQL Queries, SQL to create Tables & Keys ***
    • Finish the Chapter 5 SQL examples
      • Create a Trigger
      • Look at normalizing tables -> exercises at end of the chapter
    • Exam 2


  • We finish chapter 6 then did some denormalization examples
  • Nice Tutorial on Stored procedures
  • 4/24

    Final Exam
    Friday @ 10:00

    Due Date Description


    Automobile body Repair Shop. #3.7 page 89
    2/20 Continue with the Automobile body repair Shop for Chapter 4 : #4.11 page 137


    Make the script for your Auto Repair database based off of the ER handout
    Add some dummy data via your script


    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


    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.

    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.
    Capstone Project

    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
    Home Page:
    - Make the initial web page for your Auto Body Repair Shop. Make a link from your homework page
    - Have a link to go back to this screen on every web page
    Already done
    Customer page:
    - Web page to add a new customer
    - Web Page to update a existing customer
    Already done
    Automobile page:
    - Simple screen to add a new automobile
    - Add drop down to select the customer

    Should be done by 4/19
    Estimate page:
    - Simple screen to add a new Estimate
    - Drop down for the Automobile
    Should be done by 4/21
    Add a Job Page:
    - Drop down for customer
    - Drop down for the automobile
    - Drop down to select a mechanic
    - Rest of the fields
    - then add to the Repair_Job table
    - add to the Repair Table
    - add to the Part_Repair table

    Version 1 → Can have one repair only per job
    Version 2 → Can have multiple repairs per job (Bonus points if you can do this)

    Version 2 Hints:
    • 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 a Report page that shows all the jobs with the:
    - Customers
    - Cars
    - Description
    - Date assigned
    - Date completed (if not completed put something to signify that this is still in progress)
    By the end of the semester Bonus features:
    • Add Mechanic
    • Add Parts
    • Tie in the Estimate



    Student Pages: