Lex L- Profile

Dockerfile contains the code to setup the PostgreSQL database.

init.sql contains the SQL statements to create the database schema.

The tables created are:

1. members
2. items
3. transactions

• populate_tables.sql contains SQL to populate the tables with sample data.
• queries.sql contain the SQL statements for the 2 tasks.
• The entity-relationship diagram is provided in jpg and xml formats:
  • ERdiagram_drawio.xml can be uploaded to draw.io.
  • er_diagram_picture.jpg is the same diagram saved in picture format.
• I included a powerpoint DE_section2_databases.pptx with the screenshots of the SQL statements output and Docker initialisation.

Software used

• I used command line to build the Docker image and run the Docker container.
• I used Docker Desktop for the container for the PostgreSQL database.
• I used pgAdmin 4 to connect to the database to run the SQL queries.

FAQ

1. What is the purpose of init.sql and populate.sql?

• init.sql sets up the database table schema
• populate_tables.sql populates the tables with initial sample data.

1. Which are the DDL statements to create the database tables and define the schema?

• The DDL statements are below.

CREATE TABLE members (
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(100),
    date_of_birth VARCHAR(8),  
    mobile_no CHAR(8),         
    above_18 BOOLEAN,
    membership_id VARCHAR(50) PRIMARY KEY
);

CREATE TABLE items (
    item_id VARCHAR(10) PRIMARY KEY,
    item_name VARCHAR(100),
    manufacturer_name VARCHAR(100),
    cost DECIMAL(10, 2),
    weight_kg DECIMAL(6, 2)
);

CREATE TABLE transactions (
    transaction_id SERIAL PRIMARY KEY,
    membership_id VARCHAR(50) REFERENCES members(membership_id),
    items_bought TEXT[],
    total_items_price DECIMAL(10, 2),
    total_items_weight_kg DECIMAL(6, 2)
);

• The members table schema takes reference from Section 1.
• For items, I use the data type VARCHAR to store item ids, e.g. Item1.
• For transactions, the items_bought is an array of text (string) values, e.g. ARRAY[‘Item1’, ‘Item2’]

1. Which are the SQL statements for the tasks described?

• I use the below SQL statements:

-- 1. Which are the top 10 members by spending

SELECT
    membership_id,
    SUM(total_items_price) AS total_spending
FROM
    transactions
GROUP BY
    membership_id
ORDER BY
    total_spending DESC
LIMIT 10;



-- 2. Which are the top 3 items that are frequently bought by members

SELECT
    item_id,
    COUNT(*) AS purchase_count
FROM
    transactions,
    UNNEST(items_bought) AS item_id
GROUP BY
    item_id
ORDER BY
    purchase_count DESC
LIMIT 3;

4. Which are the queries in populate_tables.sql?
   For the members table, 19 rows of member data from the csv file of successful members from Section 1 are inserted.

INSERT INTO members (first_name, last_name, email, date_of_birth, mobile_no, above_18, membership_id) VALUES
('Aaron', 'Wilson', 'Aaron_Wilson@burke-benton.net', '19870517', '75053391', TRUE, 'Wilson_6845c'),
('Samuel', 'Lee', 'Samuel_Lee@gardner-rodriguez.net', '19740902', '57636831', TRUE, 'Lee_d3d5c'),

-- and so on

For the transactions table, 50 rows of mock data are inserted.

INSERT INTO transactions (transaction_id, membership_id, items_bought, total_items_price, total_items_weight_kg) VALUES
(21, 'Wilson_6845c', ARRAY['Item1', 'Item2'], 50.75, 2.5),
(22, 'Lee_d3d5c', ARRAY['Item3'], 15.50, 1.0),
(23, 'Weaver_feb81', ARRAY['Item2', 'Item4', 'Item5'], 90.25, 4.0),

-- and so on

10 sample items are inserted in the items table.

INSERT INTO items (item_id, item_name, manufacturer_name, cost, weight_kg) VALUES
('Item1', 'USB Cable', 'Anker', 5.00, 0.05),
('Item2', 'Mouse Pad', 'SteelSeries', 10.00, 0.20),
('Item3', 'Notebook', 'Moleskine', 15.00, 0.30),
-- and so on

5. Which commands are used to initialise the docker container with postgres?
   The commands used on the command line interface on command prompt are:

ecommerce-postgres>docker build -t section2_postgres_db .
ecommerce-postgres>docker run -d –name section2-postgres-db-container -p 5432:5432 section2_postgres_db