Database Programming #

In this lab you will practice SQL commands and create tables for a relational database.

Syllabus Topics [SL] #

• A3.1.1 Explain the features, benefits and limitations of a relational database.
• A3.2.1 Describe database schemas.
• A3.2.2 Construct ERDs.
• A3.2.3 Outline the different data types used in relational databases.
• A3.2.4 Construct tables for relational databases.

Key Vocabulary #

[0] Setup #

💻 Download sqlite3 to run SQL commands from the command line. The documentation is at sqlite.org/docs.html

brew install sqlite3

💻 Go to your dpcs folder and create a new folder for this unit.

cd ~/desktop/dpcs/
mkdir unit04_databases
cd unit04_databases

💻 Clone your repo. This will copy it onto your computer. Be sure to replace yourgithubusername with your actual username.

git clone https://github.com/isf-dp-cs/lab_db_programming_yourgithubusername

cd lab_db_programming_yourgithubusername

💻 Enter the Poetry Shell to start the lab. As a reminder, we will run this command at the start of each lab, but only when we are inside a lab folder.

poetry shell

👾 💬 Exiting the poetry shell

When you want to exit the shell, you can type exit or ^D

[1] Riddle Schema #

The first example is a simple database with one table. It stores Riddles.

erDiagram

    RIDDLES {
        
    }

erDiagram

    RIDDLES {
        id integer PK
        question text
        answer text
        total_guesses integer
        correct_guesses integer
        difficulty text
    }

[2] Riddles Worksheet #

💻 Open a new database file in the sqlite3 shell.

sqlite3 database_riddles.db

💻 Create the riddles table. The semicolon ; MUST be used to denote the end of your command.

CREATE TABLE IF NOT EXISTS riddles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    question TEXT NOT NULL,
    answer TEXT NOT NULL,
    total_guesses INTEGER DEFAULT 0,
    correct_guesses INTEGER DEFAULT 0,
    difficulty TEXT DEFAULT 'easy'
);

💻 Exit the sqlite3 shell

control + c OR control + d

💻 List the files. You should see your newly created database_riddles.db in the directory!

ls

👀 Take a look at init_db.py. This is where the new rows are added to the riddles table. The riddles are populated from the riddles.json file.

💻 Run init_db.py to fill your database.

python init_db.py

💻 Enter the sqlite3 shell again.

sqlite3 database_riddles.db

💻 Turn on headers to be able to view the columns names`

.headers on

💻 Turn on column mode to display queries in clear columns

.mode column

✏️ Write out a query to answer each question on the worksheet.

💻 Test each query out in your shell to make sure it works as expected. Don’t forget the semi-colon ; to end each statement.

💻 Exit sqlite3

control + c OR control + d

[3] Team Schema #

In this lab you will create a relational database representing sports teams and players.

erDiagram

    TEAM ||--|{ PLAYER: ""
    
    TEAM {
        
    }

    PLAYER {
      
    }

flowchart TD

    TEAM[TEAM] -->|1| B{has}
    B --> |N| PLAYER[PLAYER]
    

erDiagram
    TEAM ||--|{ PLAYER: ""
    
    TEAM {
        team_id integer PK
        team_name text
        sport text
    }

    PLAYER {
        player_id integer PK
        team_id integer FK
        first_name text
        last_name text
        position text
        games_played integer
    }

[4] Create a Database #

💻 Create a new database file

sqlite3 database.db

💻 Turn on headers to be able to view the columns names`

.headers on

💻 Turn on column mode to display queries in clear columns

.mode column

💻 Create the teams table. The semicolon ; MUST be used to denote the end of your command.

create table teams(
    team_id integer primary key AUTOINCREMENT,
    team_name char,
    sport char
);

💻 Insert 5 records Because the team_id will auto-increment, we do not need to enter it. You can use the up arrow to cycle through previous commands.

insert into teams (team_name, sport) values ("ISF", "Basketball");

💻 Query for all records. Don’t forget the semi-colon ;

select * from teams;

💻 Test the query commands you learned in the last lab.

SELECT, DISTINCT, FROM, WHERE, BETWEEN, ORDER BY, GROUP BY, HAVING, ASC,
DESC, LIKE with % wildcard, AND, OR, NOT

[5] Modify your Database #

💻 Delete a record with a where query. It will delete all records that match the query. Try to delete multiple rows at at time.

delete from teams 
where team_id=0;

💻 Update a record with a where query. It will delete all records that match the query. Try to delete multiple rows at at time.

update teams 
set team_name="Hong Kong"
where team_id=1;

[6] Add the Relational Database #

💻 Create a new table for the players

CREATE TABLE players (
    player_id integer primary key autoincrement,
    team_id integer,
    first_name char NOT NULL,
    last_name char NOT NULL,
    position char NOT NULL,
    games_played INTEGER DEFAULT 0,
    foreign key (team_id) references teams(team_id)
);

💻 Insert 5 records into players

💻 User JOIN to view both of the tables combined.

[7] HL: Create a View #

💻 Create a new view based on a SELECT command. You can then use the view_name as the table_name in commands.

create view b_sports as
select sport from teams
where sport like "%b%";

💻 Create a new view with both tables combined.

💻 On your view, test all aggregation commands

AVERAGE, COUNT, MAX, MIN, SUM

[8] Deliverables #

⚡✨ Once you finish the lab, be sure to complete these two steps:

📋 Update Syllabus Checklist: Go to your Syllabus Content Checklist in your Google Drive and update it accordingly.

💻 Push your work to Github

• git status
• git add -A
• git status
• git commit -m “describe your code here”
• git push
• remote