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

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:

1. 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 everything in it instead of normalizing - but if you normalize it out then get a logically structured paradigm which is better on so many levels - it makes it more human readable too, but mostly as everyone discusses, you don't want to have to update one thing in several places. Talking about the examples is so much more clear, so going back to the pet store example - just suppose you put all the pets, customers, and services that happened in one table instead of normalizing:

   	customers, pets, and services (Not Normalized)
   customers	pets	services
   Roger	Lil' Fluffy	Cyborg Augmentation
   Roger	Lil' Fluffy	Robo-Leg Attachment
   Roger	Lil' Fluffy	Laser-Eye Upgrade
   Roger	Lil' Fluffy	Spring-Legs Attachment
   Roger	Lil' Fluffy	Roller-Feet Attachments
   Roger	Lil' Fluffy	Drone-Propeller Attachment
   Roger	Lil' Fluffy	Parachute Attachment
   Sally	Big Fuzzy	Laser-Eye Upgrade

   Then suppose Roger changes Lil' Fluffy's name to Super-Fluffy, you would have to update 7 rows, one for every service Fluffy had gotten there, instead of having to update just one row, if it had been normalized, by making a pet table, and a services table. If there were a service where one dog donated blood to a pet possum or something - then you'd need a services-pets table with two foreign keys in it instead of putting the foreign key in services.

   Here is that table but normalized into four tables, you can still see Lil' Fluffy got a lot of stuff done but now that name and the owner's name are only written one time:

   	customers Table
   ID	Customer
   1	Roger
   2	Sally

   	Pets Table
   Pets ID	Pets Names	Owner ID
   1	Lil' Fluffy	1
   2	Big Fuzzy	2

   	Services Table
   Service ID	Service
   1	Cyborg Augmentation
   2	Robo-Leg Attachment
   3	Laser-Eye Upgrade
   4	Spring-Legs Attachment
   5	Roller-Feet Attachments
   6	Drone-Propeller Attachment
   7	Parachute Attachment

   	Pets - Services Table
   Pet ID	Service ID
   1	1
   1	2
   1	3
   1	4
   1	5
   1	6
   1	7
   2	3

   See how everything (except integer keys) is just written only once the second time around? That's like any other programming paradigm of not duplicating algorithms, variables, don't duplicate anything you don't have to duplicate.
   I mean on one hand, it's more clear the first time around, with everything in one table. But then wait until it gets messed up later because only one row gets changed when Roger changes Lil' Fluffy's name. All the rows where Lil' fluffy got something done are going to need to get changed, or it could be embarrassing next time they come back and you can't tell what Lil' Fluffy's name is anymore. So, that's why you really don't actually have to - but it's a million times better for a lot of reasons, if you do normalize, or at least go that direction with the design.

   You don't even need foreign keys - you can still index tables off each other without them - and as discussed in lecture - one good way is to overlap so a primary key doubles as a foreign key, for extra simplicity and clarity. That would be like Person - SSN keying off Contact SSN - the SSN is the primary key - and the foreign key in contact.

2. What is an SQL view and why is it useful ?

   An SQL view is a complicated query turned into a virtual table you can query simply like with a

   select * from pre_made_view;
   

   And then add even more complexity on top of that if you want to - instead of the complexity that would exist without it.

   Here's another example - instead of:

   -- list the patients under 25 that have been prescribed a certain 
   -- medication, ie 'Crunchy Taco Supreme'
   
   select Doctor.Name as Doctor, Patient.Name as Patient, Quantity as `Amt.`
      ,Drug_Trade_Name as `Drug` from Doctor, Prescription, Patient
   where 
     Prescription.Patient_SSN=Patient.SSN
     and Doctor.Patient_SSN=Patient.SSN 
       and Drug_Trade_Name='Crunchy Taco Supreme'
       and Patient.Name IN
       (
         -- List patients under 25
         select Name from Patient
         where (TRUNCATE(DATEDIFF(now(),Age)/365,0)) < 25
       )
   ;
   

   Could have just been:

    
   -- list the patients under 25 that have been prescribed a certain 
   -- medication, ie 'Crunchy Taco Supreme'
   
   select Doctor.Name as Doctor, Patient.Name as Patient, Quantity as `Amt.`
      ,Drug_Trade_Name as `Drug` from Doctor, Prescription, Patient
   where 
     Prescription.Patient_SSN=Patient.SSN
     and Doctor.Patient_SSN=Patient.SSN 
       and Drug_Trade_Name='Crunchy Taco Supreme'
       and Patient.Name IN
       (
         -- List patients under 25
         select * from list_patients_under_25
       )
   ;
   

   Had list_patients_under_25 been defined prior like:

   -- List patients under 25
   create view list_patients_under_25 as
       select Name from Patient -- List patients under 25
       where (TRUNCATE(DATEDIFF(now(),Age)/365,0)) < 25;
   

   
   But even better still, that could have been just one short line:

    
   -- list the patients under 25 that have been prescribed a certain 
   -- medication, ie 'Crunchy Taco Supreme'
   
   select*from list_patients_under_25_eating_crunchy_taco_supremes;
   

   A quick and simple one-liner, had list_patients_under_25 AND list_patients_under_25_eating_crunchy_taco_supremes been defined prior like:

   
   -- List patients under 25 eating crunchy taco supremes:
   create view list_patients_under_25_eating_crunchy_taco_supremes as
   select Doctor.Name as Doctor, Patient.Name as Patient, Quantity as `Amt.`
      ,Drug_Trade_Name as `Drug` from Doctor, Prescription, Patient
   where 
     Prescription.Patient_SSN=Patient.SSN
     and Doctor.Patient_SSN=Patient.SSN 
       and Drug_Trade_Name='Crunchy Taco Supreme'
       and Patient.Name IN
       (
         -- List patients under 25
         select * from list_patients_under_25
       );
   	
   -- List patients under 25
   create view list_patients_under_25 as
       select Name from Patient -- List patients under 25
       where (TRUNCATE(DATEDIFF(now(),Age)/365,0)) < 25;
   

   So I guess they're just like any other computer-program routine like a Java Method, JavaScript or C++ function, etc. MySQL views are stored procedures essentially.

This week I also re-discovered the Slax operating system as a bed-time project, it's based on Debian, and can do a lot more with it now that I know a little more about Debian after working so much with Knoppix and Mint. I can dual/multi boot, ssh in, VNC desktop server with multiple users, Android Studio, MySQL workbench, Spring Tools, Eclipse, JDBC, JNLP, Apache2 with MySQL, PHPMyAdmin, PHP 7, multiple virtual hosts, run Java, Perl and Python programs, host PHP/MySQL sites with Apache2 and MySQL but I am still trying to figure out the specifics of how to tunnel into my network from outside of my network, for hosting, vpn, and as an interesting and fun project. I'm not sure if it was this week or last week that I also made a paper-mache cat tower that actually held up when it rained last night.

Thanks for reading!