To Do

Today’s assignment is easy:

• Connect to team and say “Hi” in the “Database Systems – CSCI 3410 (Spring 2020)” group (email me if you don’t have access to it).
• You can start looking at this short intro to Java and Chapter 5 - Database Applications to get ready for next week.

Stay safe!

Last Time

Well, that was a while ago… We finished “How to design a database” by discussing normal forms, and decided that we would skip over UML diagrams. That means that today, we start a new chapter, on Database Applications.

Announcement

We need to re-organize the evaluation. My main goals are to:

• not throw any additional difficulty by using new technologies or requiring synchronous connections,
• makes this evaluation process interesting and empowering, so that you’ll learn something that you could re-use,
• offer flexible dead-lines.

I hope that I’ve managed to to fill those goals by offering the following. So far, you have taken:

• 2 quizzes (5%)
• 1 project (5%)
• 1 exam (20%)

I’d like to

• double the weight of the past evaluations (so that the past quizzes are now 10%, the project is now 10%, and the exam is 40%),
• offer a multi-step projects for 50% of your grade,
• offer a “take home” final exam for 20% of your grade.

Yes, that means that your grade will be computed out of 130%, so that the “take home” final exam is a bonus to boost your grade. Practically, the project will ask you to

1. Write a business statement,
2. Write the E.-R. model for the situation you are describing,
3. Write the sql code for that E.-R. model,
4. Write a small application in Java to interact with your database.

The idea being that, for the ~4 weeks we have left, you would complete 1 step every week, and report back to me so that I could advise and help you in this project. Yes, that is a lot of discussion and work, but we can do it asynchronously, and with technologies that are familiar to you.

We will discuss the modalities of the take home exam later, but I was thinking of giving you a couple of problems around 04/29, and collect them by 05/06. You could do them any way you see fit: on paper, via email, …

To Do, Today!

Here is what we’ll do today:

• Stop reading the news or check any nerve-wrecking feed of any kind for 2 hours (at least),
• Read Chapter 5 - Database Applications from the beginning to “Flash Intro to Java” (included),
• This last part points to this short intro to Java. You will need to
  • Install java,
  • Compile and execute a short program,
  • Realize that a java program looks pretty much exactly the same as a program in C# or any other object-oriented programming language.

Last, please:

• Listen to the small recording I shared at the beginning of this post,
• Say “hi” on team if you have not already,
• Let me know if you see any major challenge or injustice with the organization of the evaluation I offered at the beginning.

Last Time

Hopefully you’ve all managed to install Java, became more familiar with the command-line interface of your operating system, and managed to run your first Java program (not database-related). As a by-product, you’ve learned that there was nothing to be affraid of about Java, and that you could read a simple program directly.

You’ve also learned that there was multiple ways of interacting with a DBMS, and that we would focus on database applications written in Java, and using the “J” connector for mysql.

Announcement

Since you seem to agree with our new evaluation method, let me share some more details about the project with you.

To Do, Today!

Here is what we’ll do today:

• Stop reading the news or check any nerve-wrecking feed of any kind for 3 hours (at least, note that I’ve boosted my expectations),
• Read Chapter 5 - Database Applications from “A First Program” to “Differences Between executeQuery, executeUpdate and execute” (included),
• Compile and execute all the programs listed in those sections,
• Do Exercise 5.1–5.9

There is a lot of practise on that particular lecture, so make sure you follow the instructions closely, and you should get two nice Java applications working on your computer at the end of this class! As always, you can find the source code directly on-line, at

• https://rocketgit.com/user/caubert/CSCI_3410/source/tree/branch/master/blob/notes/code/sql/HW_EBOOKSHOP.sql,
• https://rocketgit.com/user/caubert/CSCI_3410/source/tree/branch/master/blob/notes/code/java/FirstProgBis.java,
• and https://rocketgit.com/user/caubert/CSCI_3410/source/tree/branch/master/blob/notes/code/java/FirstProg.java.

I’ve updated the notes to add more comments and explanation, but feel free to ask on team or via email if there’s something not clear. The programs themselves are fairly short, but they involve a new API and multiple new classes and methods, so make sure you walk through it slowly and carefully!

Thanks a lot, have a great week-end!

Last Time

Ok, this was a “long” time ago, but hopefully you remember that we are now exploring the world of databases interactions. We looked at the big picture, made a flash intro to Java, and made our first “proof-of-concept” application. Today’s main focus will be on two programs, that you can find in this archive, and that will be presented in detail during class.

We also looked at how to “map” datatypes between java and mysql, and at various method to execute statement in the DBMS from the Java program. Please, have a look back if you forgot about those.

Announcement

Please, remember that the business statement and the E.-R. model are due today! It is essential that I have a look at it before you move forward to the SQL implementation, especially since the project is open-ended: I’ve already discussed some interesting proposals with some of you, but I need to discuss them with all of you! So even if it is still drafty, send me something today (or, I should say, share it with me on box). I’ll try to get back to all of you before tomorrow ends, if I have not already. Feel free to ask questions in the chat if something is not clear.

For those who feels ready, start working on the SQL implementation: convert your E.-R. model to the relational model, and then start coding! Your schema name should be HW_<YourLastName>, and being executable with the usual testuser / password credentials. Make sure you save your commands as you go, as you’ll have to share a sql file with me containing all the instructions needed to re-create the same environment. Do not spend time on filling your database with data: the overall organization is what matters for now.

To Do, Today!

Here is what we’ll do today:

• Stop reading the news or check any nerve-wrecking feed of any kind for 4 hours (at least, note that I’ve boosted again my expectations),
• Get in touch with me about your project if you have not already,
• Read Chapter 5 - Database Applications from “Differences Between executeQuery, executeUpdate and execute” to … the end (yes, this is a big chunk, but Wednesday’s lecture will be lighter).
• Compile and execute the two programs listed in those sections, and shared in this archive,
• Do all the exercises and all the problems of Chapter 5.

There is, again, a lot of practise on that particular lecture, so make sure you follow the instructions closely, that you read all the code and the comments, and you should start to get a good idea on how to write “advanced” Java applications!

I’ve substantifically updated the notes to add more comments and explanation, but feel free to ask on team or via email if there’s something not clear. This is one of my favorite class, as we start to see the interplay of java and mysql in its full details, but it is also one of the hardest!

Thanks a lot, have a great day, and stay safe!

Last Time

We did some incredible Java programs. Hopefully you feel comfident with them, and will be able to read, understand, compile and execute today’s programs (shared in this archive) easily!

To Do, Today!

Here is what we’ll do today:

• Stop reading the news or check any nerve-wrecking feed of any kind for 5 hours (at least, note that I’ve boosted again my expectations),
• Get in touch with me if you need another round of comments on your project,
• Read Chapter 6 - A Bit About Security entirely.
• Compile and execute the Java examples shared in this archive (they are the SimpleInjection0X.java programs discussed in the lecture),
• Do all the exercises and all the problems of Chapter 6.

The notes have been, as always, updated to add more comments and explanations, but feel free to ask on team or via email if there’s something not clear. Security is an important topic, and it is important that you take it seriously. Thinking “What’s the point, there will always be someone smarter than me that can gain un-authorized access to my system” is like not washing your hands before eating because you may get sick in any case: that might be true, but that does not mean you should not actively try to lessen the chances of the bad thing happening!

Thanks a lot, have a great day, a great week-end, and stay safe!

Last Time

We discussed database security. You should have performed your first SQL injection on your own, and realized how easy it was to send commands to the DBMS through a poorly secured database application. Note that, generally speaking, not giving access to your database directly is the way to go: a good application should gather the information or request from the user, send it to an application on the server-side, which is the only one that can access the DBMS. Acceding directly the database from the application is a simplification we performed in this class that is still useful if you know who will run your database application and how (think of an internal tool). But if you are planning on developing an application that will be broadly distributed to the “outside world”, it should not make a direct access to your database.

To Do, Today!

Here is what we’ll do today:

• Stop reading the news or check any nerve-wrecking feed of any kind for … 5 hours (ok, let’s stick to 5 hours for now, that’s already quite a challenge),
• Get in touch with me once you added your SQL code to the box repository,
• Read Chapter 7 - Presentation of NoSQL from the beginning to “Categories of NoSQL Systems” (included). It is a “narrative” explaining why NoSQL was developped and the problems it was trying to solve.
• Read https://blog.panoply.io/sql-or-nosql-that-is-the-question at least from beginning to “Reasons to Use a NoSQL Database” (included), and the conclusion. It nicely completes the presentation I wrote.
• Have a look at https://scalegrid.io/blog/2019-database-trends-sql-vs-nosql-top-databases-single-vs-multiple-database-use/, to get a feeling of what today’s landscape is.

The notes have been, again, updated to add more comments and explanations, but feel free to ask on team or via email if there’s something not clear. It is important to have a good feeling of what NoSQL systems can and can not do, and of how popular they are. Next time, we’ll have a look at one of the most used NoSQL system, mongoDB.

Last Time

You’ve read about the story and development of the NoSQL approach. The main “take-home” message from that section was, in my opinion, that it’s not a binary conflict between NoSQL and SQL, and that you don’t have to “chose a camp”. Those are two different categories of tools, suited for different needs, and you can move from one to the other between projects, or even within a project. It is perfectly acceptable to start with “less structured data”, and, as your project grows, to “stiffen” the data, and to develop a more rigorous schema.

Oh, and, passing by, most of the course content applies to NoSQL as well. We will see for instance that security risks are very much the same, and that design plays an important role too: it’s not because you are not forced to be structured that your program should become completely structure-less!

To Do, Today!

Here is what we’ll do today:

• Stop reading the news. Like completely (Ok, this one’s hard, I don’t even know if I’ll succeed).
• Read the summary about project #2 if you have not already.
• Get in touch with me if you need another round of feedback on your project.
• Read Chapter 7 - Presentation of NoSQL from “MongoDB” to First Elements of Syntax, included.
• Complete the five steps (from 0. Switch Database to 4. Specify Fields to Return) of MongoDB’s “getting started” page.
• (Optional) Install the MongoDB client and server

The notes have been, again, updated to add more comments and explanations, but feel free to ask on team or via email if there’s something not clear. Please, do not start reading “MongoDB Database Program” yet, as this section needs a serious update.

Announcement

• Please, remember that your project is due today (before midnight), and those principles (where > means “is better than”):

  • Something > Nothing,
  • Something on time > Something late,
  • Something that compile > Something that does not compile,
  • A commented project > An un-commented project.

• There will be no new content on Wednesday: use this time to either review the material, or to correct your project (this is in no way an encouragment to submit your work late. It just mean that if you rushed on Monday and would like to make a couple of adjustments before Wednesday ends, you can).

• Please, remember that you can fill the evaluation for this class at https://augusta.campuslabs.com/eval-home/cei/csci/3410/a. Thanks to the few (!) of you who already took the time to complete it.

Last Time

We discussed mongoDB, which is a popular “No SQL” approach using documents. We discussed some intuitions on documents and the similarities between mongoDB and the “RDBMS”, and set-up your installation. We concluded with some CLI commands to perform simple tasks like creating a database, inserting, deleting and querrying it.

To Do, Today!

Here is what we’ll do today:

• Stop reading the news. Like completely. You’ll feel better.
• Re-read the summary about project #2 if you have not already, submit your work, and get in touch with me once you have submitted it in Box.
• Read the rest of Chapter 7 - Presentation of NoSQL, that is, the “MongoDB Database Program” section. It explores a simple Java application that performs simple tasks on the mongoDB server.
• Complete the Exercises and Problems of this last Chapter.