Eup Logo

CSCI 313 Database Management
Spring 2022



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:
  • 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


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:
    • 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
  • 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
  • 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.

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
  • 3/15 Meeting Recording
  • 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
  • We may need an HMTL primer, for example let's look at the SELECT statement
  • Let's finish our update page
  • Start the delete page
  • 3/28 *** It's time for another Exam! - Next Week: SQL Queries, SQL to create Tables & Keys ***
    • 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)


  • Video of Thursday's class: We finish chapter 6 then did some denormalization examples
  • Nice Tutorial on Stored procedures
  • 4/25

    Final Exam
    Tuesday @ 12:30

    Due Date Description


    Automobile body Repair Shop. #3.7 page 103
    2/18 Continue with the Automobile body repair Shop for Chapter 4 : #4.11 p. 151


    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.


    This has been incorporated into the final capstone project

    Add to your auto repair project:

    1. A page to add a an Estimate
    2. A page to add a job

    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:

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

    Should be done by 4/15
    Home Page:
    - Make the initial web page for your Auto Body Repair Shop (Probably edit your existing 1st page)
    - Have a link to go back to this screen on every web page
    Should be done by 4/16
    Customer page:
    - Simple screen to add a new customer

    Should be done by 4/18
    Automobile page:
    - Simple screen to add a new automobile
    - Add drop down to select the customer

    Should be done by 4/20
    Estimate page:
    - Simple screen to add a new Estimate
    - Drop down for the Automobile
    Should be done by 4/22
    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