Which Database Software?

  • Paul Gardner
    Participant

    Re Validation:
    I teach the pupils how to write/edit a script that creates the tables with validation rules defined in the SQL create table commands. These can validate presence check, field length, range check, list of values and PK/FK constraints.

    https://repl.it/@paulatdunedin/N5201819

    shows how this can be done in SQLite, with some code to test that it works.

    Paul

    Richard Scott
    Participant

    Regarding Validation then that is implemented as follows when using sqlite3:

    Validation
    The four required types of validation are added as follows:
    1. presence check
    use NOT NULL in the table definition
    petName TEXT(20) NOT NULL, a petName is required

    2. restricted choice
    add a check constraint in the table definition:
    CHECK( house IN(‘Assisi’,’Galilee’,’Jericho’) ) house must be Assisi, Galilee or Jericho

    3. field length
    add a check constraint in the table definition:
    CHECK( LENGTH(dateBorn) = 10 ) checks that a dateBorn has exactly 10 characters
    CHECK( LENGTH(password) >= 8 ) checks that password is at least 8 characters

    4. range check
    add a check constraint in the table definition:
    CHECK( weight BETWEEN 0.1 AND 200 ) min weight 0.1 and max weight 200 are allowed

    Richard Scott
    Participant

    Consider a portion of the database code I posted previously.
    This could implement the following validation rules:

      Every record must have an artist name
      genre can only be Rock or Metal
      country must be 2 characters or more
    
    CREATE TABLE artist(
        artistname     TEXT NOT NULL,
        country        TEXT,
        genre          TEXT,
        PRIMARY KEY    (artistname),
            CHECK( genre IN ('Rock','Metal') ),
            CHECK( LENGTH(country) >= 2 )
    );
    


    BTW – I’m not actually a fan of CAPITALISING SQL commands but when creating sheets to be printed in black ink and also when typed into a Python editor they are formatted as text (usually green) I think capitalisation makes the keywords stand out in a more readable way.

    Richard Scott
    Participant

    This is a repeat of the previous code but hopefully formatted better. Just copy and paste this into any Python editor and run it…

    
    import sqlite3
    db = sqlite3.connect(':memory:')
    
    db.execute( '''
        CREATE TABLE artist(
            artistname TEXT PRIMARY KEY,
            country    TEXT,
            genre    TEXT
        )
    ''')
    
    db.execute( '''
        INSERT INTO artist(artistname, country, genre)
        VALUES
            ( 'ACDC' ,'Australia' ,'Rock'),
            ( 'Beatles','UK' ,'Pop'),
            ( 'Cramps' ,'USA' ,'Psychobilly'),
            ( 'Danzig' ,'USA' ,'Rock')
    ''')
    
    results = db.execute('''
        SELECT artistname, country
        FROM artist
        WHERE artist.genre = 'Rock'
    ''')
    
    print ('Rock bands…')
    for record in results:
        print( f'The band {record[0]} is from {record[1]}')
    
    db.close()
    
Viewing 4 posts - 31 through 34 (of 34 total)

You must be logged in to reply to this topic.