3.ICE.5: One to Many

Search…

Introduction
Create a command line app that tracks cats and owners. This exercise will be similar to the command line app in 3.POCE.4: SQL Meal Tracker.
Base
DB Setup
1.Create a new database called cat_owners.
2.Create the following 2 tables. Record the CREATE TABLE statements in a file init_tables.sql for debugging purposes.
To use psql to run all the SQL in a file, use the following command: psql -f <PATH_TO_FILE>
Owners Table (owners)
column name
data type
description
example
id
SERIAL PRIMARY KEY
ID
1
name
TEXT
Name of the owner
Kai
Cats Table (cats)
column name
data type
description
example
id
SERIAL PRIMARY KEY
ID
1
name
TEXT
Name of the cat
Kong Ming
owner_id
INTEGER
ID of the owner
1
SERIAL is "the same as integer except that PostgreSQL will automatically generate and populate values into the SERIAL column." - postgresqltutorial.com​
owner_id is a reference to the id column in the owners table. This is to represent in SQL the relationship where owners can have many cats and each cat belongs to a single owner.
Application Setup
1.Begin with the base Node repo.​
2.Install the pg library with NPM.
3.Use the code from 3.4.2: PostgreSQL Node App as a template for index.js. Reminder to update pgConnectionConfigs to reference the new DB name.
Create Owner
Create a new entry in the owners table.
Example Query
1
node index.js create-owner Jim
Copied!
Query Template
1
node index.js create-owner <OWNER_NAME>
Copied!
Create Cat and Associate Cat with Owner
This means that the owner_id column in the cats table corresponds to the id of the relevant owner's row in the owners table.
Example Query
1
node index.js create-cat 1 Fluffy
Copied!
Query Template
1
node index.js create-cat <OWNER_ID> <CAT_NAME>
Copied!
Get List of Cats and Respective Owners
For the following 2 features you will have to use nested SQL queries. Refer to 3.4.3: Nested SQL Queries for examples. Feel free to output the data in any readable format.
Example Query
1
node index.js cats
Copied!
Example Output
1
Cats:
2
1. Fluffy: Owner: Jim
3
2. Cathy: Owner: Jim
Copied!
Get List of Owners and Respective Cats
Example Query
1
node index.js owners
Copied!
Example Output
1
Owners:
2
1. Jim
3
    - Cats:
4
        - Fluffy
5
        - Furr Furr
6
2. Cathy
7
    - Cats:
8
        - Gordon
9
        - Susan Chan
Copied!
Comfortable
Use Owner's Name to Associate Cat with Owner
Specify the owner when creating a cat by using the owner's name instead of the owner's ID.
Example Query
1
node index.js create-cat Jim Gordon
Copied!
Query Template
1
node index.js create-cat <OWNER_NAME> <CAT_NAME>
Copied!
More Comfortable
Perform analytics on the data in our DB. For some of these you can simply do calculations in JavaScript instead of writing SQL. You should not need any SQL queries we did not already cover.
Get Owners with Specific Number of Cats
Add an optional argument to the owners feature. Get all the owners with NUM_CATS_OWNED cats.
Example Query
1
node index.js owners 3
Copied!
Query Template
1
node index.js owners <NUM_CATS_OWNED>
Copied!
Get Owners with Range of Numbers of Cats
Get all the owners with more than NUM_CATS_OWNED cats.
Example Query
1
node index.js owners '>3'
Copied!
Query Template
1
node index.js owners '><NUM_CATS_OWNED>'
Copied!
Get all the owners with less than NUM_CATS_OWNED cats.
Example Query
1
node index.js owners '<3'
Copied!
Query Template
1
node index.js owners '<<NUM_CATS_OWNED>'
Copied!
Get Other Cats Owned by Given Cat's Owner
Get all cats that Fluffy's owner owns.
Example Query
1
node index.js other-cats Fluffy
Copied!
Query Template
1
node index.js other-cats <CAT_NAME>
Copied!