08-02-2022

1625 words 9 minutes

Today's Foci

Focus 1

  1. Make a bash script to aut-create this file
  2. Muahahahahaa (it is done)

Focus 2

  1. learn SQL relationships
  2. write them down

Focus 3

  1. setup a postgres database

Topic

Bash script

So here is, potentially the most obtuse bash script - but hey, scripting is hard, we've gotta start somewhere.

BEWARE: something is wrong here and needs to be fixed - the "conditional guard clause" doesn't work - files will be overwritten, don't use on the same day!

#!/bin/bash

date_format=`date +"%d-%m-%Y"`

filename="$date_format.md"

filepath="~/path/to/directory/$filename"

if [ -f "$filepath" ];
then
	echo "$filename already exists"
else
	cp template.md ./to/directory/$filename
	echo "template copied to ~/path/to/directory/$filename"
fi

RESTful

  • Representational State Transger

Consider the web as state machines that have resource attributes. A state-transition (method) would change the state. This change will show to the user.

A well designed application is a state machine - a machine capable of being in various states.

use GET/POST/PUT/DELETE, with well designed URL's is how a user would navigate through a system

remember that GET/PUT/DELETE are idempotent.

REST constraints

  1. Client-server architecture. Seperates UI concerns from data-storage concerns. allows for independent component extension.

  2. Statelessness - Comms protocol where no session information is retained by the receiver, usually a server. Every packet should be understood in isolation. Good for high-load applications.

  3. Cacheability - clients and intermediaries can cache responses. Responses must implicitly or explicitly, define themselves as cacheable or non-cacheable. Good caching can remove some operations entirely.

  4. Layers - like ogres ans onions, a good web app has layers. No change if a client is requesting from a proxy or load balancer or server. Comms are maintained. Can also add security policies.

  5. Code on demand - Servers can extend or customize the funtionality of a client by transferring executable code; Java applets, or JavaScript, or WASM.

  6. Uniform Interface - 4 constraints

    1. Resource identifications within requests - ( such as favicon.ico? ) the resources themselves are seperate from the views - static files?

    2. Resource manipulation through representations. Whilst a client holds a representation of a resource, including metadata, the client can modify or change the resources state.

    3. Self descriptive messages - Each message includes all required info to describe how to process the message - which parser to use, for example.

    4. Hypermedia as the engine of application state (HATEOAS) - havin accessed an initial URI for the REST application, a client should be able to use server provided links to find, allocate, and return all necessary resources. The client doesn't need to be hard-coded

Database Relationships - Cardinality

databases are sets of interlinked tables.

Each table MUST HAVE a primary key

Other columns are considered foreign keys - these foreign keys can be other table's primary keys

One to One Relationships

only one record on each side of the relationship - a spouse to a spouse

One to Many Relationships

one record on one side, many records on another side

A restauraunt's customers, and their multiple orders

a single order however cannot be used by multiple customers

Many to Many Relationship

The restaurant might have dishes - a dish can have many orders; an order can contain many dishes

Authors and Books - A book can have several authors, AND and author can have several books

Try thinking in terms of instances?

Types of RDBMS

  • current - the database holds data accurate at present

  • temporal - each row has a valid timestamp

    • usually includes valid time - whilst data is true
    • transaction time - when the data was into the DB
    • decision time - when a decision was made about the data

two datestamp columns can be bound together using

PERIOD FOR

Why use RDBMS

Codd's 12 rules? - but no implementation uses all 12 in one instance

  • Present the data to the user as relations
  • Provide relational operators to manipulate the data in tabular form

Keys

Interesting article, 2012

Primary

Usually, one for each row in a table

Foreign

The other columns in the table that reference another table'

The table containing the foreign key is the child table, the table containing the candidate key is the parent key

Usually the foreign key will be a candidate key within a parent table - referential integrity constraint

Two options when woring with outdated foreign keys, are

  1. inactivate master-table foreign keys
  2. create a complex update programme to monitor which foreign keys are required.

Unique

Natural/Domain/Business

attributes used outside of the RDBMS related to application logic?

means of data identification, whilst imposing a uniqueness constraint

An example of a Natural Key might be a National Insurance Number

Surrogate

attributes used outside of RDBMS for an entity in the modelled world or object in the database.

A surrogate key is not derived from application data

Normal Forms

Data Domain

The collection of values a data element may contain

seems database wiki is jargon filled, and inconsistent. Frustrating, inefficient.

ergo:

Database Design for Mere Mortals

I own a copy of this book. This covers what I want to know in plain English. Its not following the Apprenticeship Providers curriculum, but I have some SQL experience, and am more interested in using my time the best I can.

Let's make notes.

Objectives of Good Design

  1. DB supports required and ad-hoc info

  2. Tables constructed properly and efficiently

  3. Data integrity at the field, table, and relationship levels

  4. Database business rules are relevant to the organization

  5. Database is extensible

Benefits of Good Design

  1. Structure is easy to modify and maintain

  2. Data is easy to modify

  3. Information is easy to retrieve

  4. End-User apps are easy to develop and build

Standard Database Design

  1. Requirement analysis

  2. Data modelling

  3. Normalization

Requirement analysis

"What is the goal?"

examination of the business model, interviews ( understanding ) of the users and management to 'assess current systems' and 'analyze future needs'. p.24

Assess information requirements for the business as a whole.

Data modelling

"Diagrams!"

'Apply a data modelling method such as Entity-Relationship diagrams (ER), semantic-object modelling, and object-role modelling, or UML modelling.' p.25

Fields and tables are associated in this phase

Essentially make diagrams of the database and its relations. This looks like the crows feet thing - one to many, etc

Normalization

"Diagram a RDBMS representation of your diagram!"

Decompose large tables into smaller tables - SRP, Seperation of Concerns.

Should eliminate redundant and duplicate data

Normal forms are standards against which a database might be tested, and subsequently modified if a rule is broken.o

There are many normal forms, and they each test against a particular problem domain.

Normal Forms exist: I through VI, Boyce-Codd, and Domain/Key.

OOO, Spicy

We're contravene all the above rules. Interesting. But I agree with illiminating the jargon. There's examples of such jargon within databases. I saw it hugely in music academia also. Jargon is stupid, and disrepectful toward others' time. I think jargon is fuelled by jealousy - attempting to deny others knowledge, for fear that the knowledge-holder may be replaced. Hmmm. Seems to be a common theme. Let's de-jargonify!

I suppose jargon is a kind of language game. I wonder what Wittgenstein has to say on it? My own thoughts are, Jargon: "An intellectual's game played by fools" : Plain Native Language (PNL?): "A fool's game played by intellectuals"?

Hmmm. Needs some refinement.

Ewwww Terminology

Well, I'd stated the above case. Now I state the case for jargon. Wonder where I'll be at the end?

  1. Expresses and defines ideas unique to a domain

  2. expresses and defines the database design process

  3. terminology used anywhere RDBMS exists.

Data

Something stored in a variable - mostly meaningless on its own

Data is what you store

Information

Data, with meaning

Information is what you retrieve

Pair Programming

I think today was productive. I need to shutup more - again I'm using 100 bad words in place of 5 words.

I think it's something to do with how I formulate language and conceptualise as mutually exclusive processes' - I realised this when writing earlier. The concept comes, then language, but sometimes presents in an obtuse order. Perhaps, whilst mainting intuitive understanding and conception, I need to create processes for communicating those thoughts. Meta-communication? Meta. The Meta Verse. I'm tired.

Also, if I'm gonna try to teach people stuff, I need to be clearer on the concepts. With music, I understood concepts at such a deep level, they almost became clear. I knew what I could safely leave out. I could convey only what was needed. I could help a lot of people because of that.

But music and computers are obviously different problem domains. It would be fair to say i shouldn't teach because it's not my place/job etc, but that would be a cop out.

It would be true to say I'm making a lot of assumptions that some people wish to be taught. Both cruel and arrogant in some ways.

But it's morally repugnant to abandon people. We are a team. We go in together, we leave together. One persons failure is the failure of all; One persons success is the success of all.

Only when teaching/sharing is required, I have to try. Is there a way I can better teach this stuff to people?

I don't know. Progress was made today - but I explained poorly, and took a significant amount of time.

Post Postgresql

it's after hours, but I'm looking to the pg_hba.conf

the advice, for now, is to change the authentication from scram-sha-256 to trust

essentially turn off postgres' authentication of the connecting client. Hmmm.

Ah; I changed it to trust. seems simplest for now. I need to figure that out at a later date.

I imagine that's something we would never do in production.

Useful Tactics

Remember to use join/associative tables in many to many relationships