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

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:

1. 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 data in a specially designed format which isn't general purpose but any design can be created.
   • A Spreadsheet could be used as a desktop application to do spreadsheet type stuff - a database is only the collection of data you might find or use on a spreadsheet in sections.
   • Spreadsheets dead end at being a spreadsheet, they can have macros and lots of people can look at them but databases can be the underlying force behind a great computer program or system.

2. What are some important reasons for using a database rather than just using files? (from orientation slides)

   I can't find direct reference to why you would use files in the orientation slides, but I can definitely talk about why using a database for data storage is superior to using files. I think the most important reason to use a database over just choosing to use files is the rich interface front-end application you can build more easily with a database than you can with just files. If you tried to do the same thing with files you would have to parse and write every file, and in the end wind up re-writing database functionality anyway.

3. An SQL select statements has SELECT, FROM, WHERE, ORDER BY, LIMIT parts. Which are optional and which are required?

   https://dev.mysql.com/doc/refman/8.0/en/select.html Just select is, you can say select 1+1; -- and mysql says 2:

   mysql> select 1+1;
   +-----+
   | 1+1 |
   +-----+
   |   2 |
   +-----+
   1 row in set (0.00 sec)
   
   

   Neato! Select can't be select without select so select is required and everything else is optional. You can't select from a table without 'from' and you need 'where' as a filter. The shortest table select I know of is something like

   select * from [tablename]; -- and that gets you everything unless your client app sneaks in a limit.
   

This week we learned all kinds of stuff about databases. I haven't gotten 64 bit programs like MySQLWorkbench going in Knoppix for some reason but they usually run fine on Mint which was the case for Workbench, so I got a couple Mints going with MySQL, PHPMyAdmin, MySQL Workbench, Java, JDBC connection all going on Mint 20.1 running in VNC in Knoppix 9.1, syncing with a file server with Samba and FreeFileSync for crash safety.

Knoppix on the other hand, while I can't get Workbench on it, it does come default preloaded with a full LAMP server including PHPMyAdmin, and I've figured out how to host http to other computers with it by changing the hosts file on the client computer that sees it - I'm not quite sure how to use Workbench from Mint on the Database on Knoppix, but almost - I think you change the host file on the client and give Workbench the IP and port - I plan on trying later to see if that works.

For some reason sometimes when I VNC into Mint the panel menu gets messed up - so finally I figured out I could copy the files back and it fixes it - so I got a script going that does that when it gets messed up.

So then I started playing with MySQL Workbench and I really like the lightning bolt buttons, that is a neat and effective concept. I learned a lot about joins this week, foreign keys, and I learned in orientation MySQL knows arithmetic which I did not realize until then. I said I was experienced but that got me exactly half way through orientation and after that I was past my original knowledge already, before the class even officially started.

I was really anxious about making sure the JDBC connection was going to work on Mint so I went through the JDBC how-to tutorial and got the JDBC connection set up already. Then I saw we were going to use Eclipse with Spring tools too - Eclipse won't go onto Mint through the regular software manager for some reason - but I did get it off the Eclipse website just fine - and Spring tools too, and they both seem to work, so I am excited about that.

So now I just need to figure out exactly what Spring Tools does, presumably some JDBC connection stuff.

Other things I'm working on are getting the show desktop paradigm from Knoppix to work in Mint because it works really good in Knoppix and I can't figure it out in Mint. I'd still really like to know exactly how to control routing configuration for hosting behind several routers, and how to ssh in as well, from an outside IP address. I think I partly figured out the Knoppix login paradigm I was trying to figure out a couple months ago. I still haven't finished my Java MVC Remoter but my bash remoter works fine for now and I have an NTShell version too that works from Windows.

This week's homework was also super fun, especially making joins and figuring out how to connect the tables. After I turned it in I rewatched the orientation for this journal because of the reference in question 2 and saw the professor making joins leaving off the keyword inner, I guess that is default, and I see the book doing it without even the word join too, so I will have to practice those. I am really looking forward to next week's lessons and got just enough ahead this week to squeeze in a couple of days to work on and finish my taxes using my favorite software, AccountBlaster! (use the free link for taxes arithmetic!) (AccountBlaster is perfect for straightening out your accounts structure when you're doing your taxes and all of a sudden you realize the categories on the IRS form aren't exactly mapped to the categories you set up, you can just use the 'move account' feature in AccountBlaster to match what the IRS expects - so smooth!!!)