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

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 Linux Debian world, there is tons of software and lots of it has dependencies. Everything's marked with a version and somehow this keys off whatever is compatible with what in the apt downloader/installer program and/or other programs. Software versions are compatible with certain versions of other software, lots of times in a context like, 'anything under version 5.2 is compatible', etc. (just a fake hypothetical example of no in particular software.)

The example from class is salaries, who makes more money than someone.

So I guess to make a parallel example, I guess I will pretend the software versioning system works like this:

A list of software lists the minimum version required of a component to work.

Maybe I'll just go the direction of hardware chip dependencies to keep it simple:

Like:

	Table Chip Dependencies
id	name	dependency
1	screen	chip 2
2	mouse	chip 3
3	keyboard	chip 1
4	webcam	chip 5
5	mic	chip 4
6	sound out	chip 4
		(listed as ints 1,2,3 etc)

So in english I will say my query is going to be something like:

"Name all the components that are compatible with what the mouse requires:"

MySQL:

create database arlons_join_on_inequality_example;
use arlons_join_on_inequality_example;

create table hardware_chip_dependencies (
	id int unique not null
	,name varchar(50)
	,dependency int
	,primary key (id)
)
;

insert into hardware_chip_dependencies values
    (1,'screen',2)
    ,(2,'mouse',3)
    ,(3,'keyboard',1)
    ,(4,'webcam',5)
    ,(5,'mic',4)
    ,(6,'sound out',4)
    ,(7,'dvd',5)
;

-- "Name all the components that are compatible with what the mouse requires:"
select  t2.name from hardware_chip_dependencies as t1
    join hardware_chip_dependencies as t2
    on (t1.dependency >= t2.dependency)
where t1.name='mouse'
and t2.name!='mouse';

mysql> select  t2.name from hardware_chip_dependencies as t1
    ->   join hardware_chip_dependencies as t2
    ->   on (t1.dependency >= t2.dependency)
    -> where t1.name='mouse'
    -> and t2.name!='mouse';
+----------+
| name     |
+----------+
| screen   |
| keyboard |
+----------+
2 rows in set (0.00 sec)

Neato!

I love "advanced" MySQL and all things like this. I liked last weeks homework but I liked this weeks homework even better. People actually get paid to do this stuff? Sign me up! Doing the MySQL query questions is like doing a bunch of puzzles for fun. I almost like it as much as I like Java programming. It's a little different because each query is almost like it's own little computer program.

A good paradigm winds up being applicable in multiple scenarios, more the better it is, the more arbitrary it is. MySQL is a complete paradigm and so winds up being applicable in all kinds of obscure scenarios, and is the world's choice because it can apparently model anything you want it to!

The homeworks were super good I'm going to save them to re-do again and again later, for sure. This is the meat right here. Before this I knew just enough MySQL from using it in my game for several years and building the game off a PHP with MySQL book and some other resources - I went with bare minimalism in database design and access as I built it since I was going from knowing nothing about database access to knowing just enough to make it work in a reversi tournament game. I was forced to learn joins, aggregate functions, nested queries, the bare basics and how to connect with PHP where I did all computation and analysis. I had hints you could and should do more with the MySQL but did not know enough to put anything more than exactly what I knew into the working program. And my style of learning on my own is to set my sights on the end objective, needle all the way out until I hit that and then fill in the details as needed, leaning heavily on "if it ain't broke, don't fix it" which left me with tons of room for improvement in my database design and access paradigm of my game. I already went through several revisions on my own - the very first versions had terrible stuff like

select * from games;

in there. That didn't last long, but I didn't go far - not nearly as far as we do in this class - with learning how to really do stuff with MySQL queries. Pretty soon I probably found

select * from players; 

Like that - I wasn't changing the MySQL any more than I had to - there was enough PHP and JavaScript to change - but now that I know how to do stuff with the very first layer - MySQL - I can go back and fix a lot of stuff, I am sure! So I am really, really appreciating the knowledge additions regarding databases and have immediate personal application for the knowledge.

I had dug a little into foreign keys I remember because I did design one system similar to my AccountBlaster in which, if I remember correctly, it had self-referring foreign keys that let those objects have the same objects within them, like for accounts. A world account has a continent account has a country account has a state account has a city account has a town account has a person account has a bank account has a ledger account has individual transaction modification ledger accounts, etc. and it's just one object, account, that can have accounts. But now I can revisit that too, and see if I can make it better. In fact, that sounds like a really fun thing to revisit - I'll have to dig it out - I was calling it MyBucks but I didn't go particularly far with it because I wound up thinking it was dumb compared to other projects I had a the time, and I was using it more as a learning tool anyways than trying to make something finished. It did have a cool mini-interface animated with JQuery - that was right about as I was discovering JQuery as well.

I was making my game, discovered foreign keys, had the AccountBlaster concept and several rough stabs at making it but nothing finished - not even the concept of accounting really so that temorarily dead-ended for me, until later. I wound up taking an accounting class for Real Estate Appraisal licensing requirements, (two accounting classes actually) went all the way through 100% without any consideration of my AccountBlaster concept and when the classes were done, completely finished - the concept missing from AccountBlaster clicked for me - I figured out what I had been missing all along - the label for what that is is liabilities with automatic liabilities - that concept was scrambled in my brain until real accounting class taught us how international accounting standard accounting does it - that clicked finally with me - then I was able to translate it into my own concept method - and finally found spare time in 2016 to hit the concept really hard using JavaScript which I was really good at at the time because of making my game all the time, updating it all the time, at the time.

So I switched over to AccountBlaster, finished that at the beginning of 2017 and started working again. Not long after that I built AppraisalBlaster, in 2018. That let me work faster. But not much - it let me realize how dead-ended of a job Real Estate Appraisal truly is. I can still add to my software and make the job easier and easier - but still - then again - all the appraisers just got off'ed so fees might be skyrocketing here - I could be speaking too soon. Well, for now I'm hitting the database tricks anyway, so I can just wait and see what happens.

My friends and family and I also made a list this week of thirty+ reasons why human rights are exponentially important right now.

Thanks for reading!