Arlon's CSUMB Blog

Arlon's CSUMB Intro to Database Systems CST-363 Module 4 Learning Journal #4/#20 for the week Wed 05/19-Tues 05/25, year 2021 Prompt for week 4: When coding a Java program that will perform a SELECT statement that return multiple rows, what are the steps needed? The first is to make a connection to the database and the last is close the connection. What are the other steps? // Write the query as a string in MySQL String sql_query_string=" SELECT * FROM `everything` where name like ? ; -- (etc.)"; // Make a prepared statement with the string PreparedStatement preparedStatement = connection.prepareStatement( sql_query_string); // Replace the ?'s in the prepared statement with setString() and setInt() preparedStatement.setString(1, name.trim()+"%"); // Execute the query with MySQL: ResultSet resultSet=preparedStatement.executeQuery(); // Iterate the rows, get each column: while (resultSet.next()) { // Row details acc...

Arlon's CSUMB Intro to Database Systems CST-363 Module 3 Learning Journal #3/#19 for the week Wed 05/12-Tues 05/18, year 2021 Prompt for week 3: Someone described normalization of a DB design as "a column depends on the key, the whole key, and nothing but the key, so help me Codd." Explain in your words what 3rd normal form is and why it is important. Normalization to me is sort of like Java objects - like how you wouldn't duplicate variables in objects that are already there. It's easy to do - two things for the same one thing. Normalization is the word for putting everything where it goes to match reality properly. Reality doesn't make a new ocean everytime it makes a new fish, just a new fish. The example in the book is perfect, vet records. You don't have customer in the same table as pet because customer has lots of pets. That would be the pet table, that gets all the pets, keyed by customer by foreign key. You can make one big table with ev...

Arlon's CSUMB Intro to Database Systems CST-363 Module 2 Learning Journal #2/#18 for the week Wed 05/05-Tues 05/11, year 2021 Prompt for week 2: SQL has the flexibility to join tables on any column(s) using any predicate (=, >, < >=, <= ). As long the column(s) are type compatible. 98% of the time the join uses primary key of one table, foreign key of the other table and equal predicate. Think of example where joining on something other than keys would be needed. Write the query both as English sentence and SQL. If you can't think of your own example, search the internet for an example. My example is software and hardware dependency version compatibility. I don't know exactly how all the automatic version compatibility stuff works exactly, so I will completely generalize here and actually wind up using hardware components for simplicity but I think the concept is essentially the same, maybe. Basically the way I understand it, for example in the Li...

Arlon's CSUMB Intro to Database Systems CST-363 Module 1 Learning Journal 1/17 for the week Wed 04/28-Tues 05/04, year 2021 Prescribed questions: Relational tables and spreadsheets look similar with both having rows and columns. What are some important differences between the two? Database colums have a specified data type, spreadsheet data cells might, but don't have to, and columns are formatted instead of stored as a certain type. Spreadsheets include (generally) the front-end application - databases may not necessarily come with a front-end application. A database front end application could possibly look like a spreadsheet but not vice versa because databases don't inherently look like something, they are just the data stored in a certain format. Spreadsheets can get really big but nobody wants to deal with one as big as databases can get. Spreadsheets are general purpose and very useful for calculations on data, databases are general purpose for storing dat...