Database Systems Final Project – FastCabs Case Study – Regita Isada, Sefira Karina, Nathania Kezia Barakati

Project name: FastCabs Case Study

Course name: Database Systems

Group members: Regita Isada, Sefira Karina, Nathania Kezia Barakati

For our Database Systems, our group had to make a database program for a case study. For my part of the project I was mostly in charge of the coding of the program.

I used Java for the programming language for this project, and the IDE i used to code is Netbeans IDE.

First, i made the login form which is in the picture below (with sample input):

I used ‘Staff ID’ from the Staff table because only the staff are allowed to open the database program. in the table i added a ‘password’ column so each staff has their own password.

This is the code for the login form:

try{
    String url = "jdbc:mysql://localhost:3306/final?zeroDateTimeBehavior=convertToNull";
    String user = "root";
    String password = "";
    con = DriverManager.getConnection(url, user, password);
    stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    String query = "select * from staff where staff_id = '" + jTextField1.getText() + "'" + "and password = '" + jPasswordField1.getText() + "'";
    rs = stmt.executeQuery(query);
    rs.next(); //to move the cursor to the first row
    rs.last(); //to check the number of rows in the query
 
    if(rs.getRow() == 1){ //to check if the cursor is in the correct row
        JOptionPane.showMessageDialog(this, "Welcome " + jTextField1.getText());
        Home frame = new Home();
        frame.setVisible(true);
        dispose();
    } else { //if the user inputs the wrong username and password, it will display a different message
        JOptionPane.showMessageDialog(this, "Incorrect username or password, please try again");
        jTextField1.setText("");
        jPasswordField1.setText("");
    }
 } catch (Exception e){
    JOptionPane.showMessageDialog(null, e.getMessage());
 }

Additionally i added a Sign Up form should anyone want to add more staff:

The staff ID in the sign-up form are randomized numbers in a specific range. But I distinguished ‘Manager’ and ‘Admin’ for the IDs so they don’t get mixed up.

This is basically the code I used for the sign-up form:

 String newName = jTextField2.getText();
 String newPhone = jTextField3.getText();
 String newType = jCheckBox1.getText();
 int newID = Integer.parseInt(jTextField1.getText());
 int newBranch = Integer.parseInt(jComboBox2.getSelectedItem().toString());
 String newPass = jPasswordField1.getText();
 
 rs = stmt.executeQuery(query1);
 rs.moveToInsertRow();
 rs.updateString("staff_name", newName);
 rs.updateString("staff_phone", newPhone);
 rs.updateString("staff_type", newType);
 rs.updateInt("staff_id", newID);
 rs.updateInt("branch_id", newBranch);
 rs.updateString("password", newPass);
 rs.insertRow();
 
 rs.next();
 JOptionPane.showMessageDialog(this, "Your account has been successfully registered");
 con.close();
 loginform frame = new loginform();
 frame.setVisible(true);
 dispose();

This is only for the Manager staff. for the Admin staff it’s basically the same but the jCheckBox is a different checkbox.

I also did most of the coding for the Query tables, this is the sample of the method I used for the queries:

public void QueryA(){
  try{
    String query = "select staff_name,staff_phone from staff where staff_type = 'manager'";
    rs = stmt.executeQuery(query);
    rs.absolute(0);
    DefaultTableModel model = (DefaultTableModel)queryA.getModel();
 
    while(rs.next()){
      name = rs.getString("staff_name");
      phone = rs.getString("staff_phone");
 
      Object[] content = {name, phone};
      model.addRow(content);
    }
  } catch (SQLException err){
    JOptionPane.showMessageDialog(this, err);
  }
 }

the query asked for the staff names and phone numbers that are in the Glasgow office, in which in this case, the branch ID is 1111, and it will display the results on a jTable. Some of the queries needed a search feature, I put the codes in the search button instead.

I also did most of the coding for the Tables, my friend provided the sample code and I just applied it to other tables.

Here are the tables:

Here are all of the queries: