ER Diagram

To start in creating a database, you first need to identify what tables will be in it, what columns they will have, and how they will all connect. To do this we create an ER Diagram, or Entity Relationship Diagram to show these connections easier.

To represent the connections between each table we use crows foot notation, which is the arrows connecting each table market in purple. To understand crows foot notation, you need to know that there are two symbols in each connection. The one closest to the receiving table is the maximum, which is either a vertical line for a maximum of 1, or three lines (like a crows foot) for multiple. The second line is the minimum, which can be a circle for zero or a line for a minimum of one. For example, in this diagram you can see that every store doesn't need to have any inventory records, marked with a circle, but it can have multiple inventory records market with three lines.

Image of the ER diagram for a Costco store.

The firt step after drafting the ER Diagram is to make sure that what you're creating doesn't already exist. To do this we attempt to remove the tables and schema, which holds all the tables.


                DROP VIEW IF EXISTS vw_order_info;


                DROP TABLE IF EXISTS costco.food_court;


                DROP TABLE IF EXISTS costco.order_item;


                DROP TABLE IF EXISTS costco.order;


                DROP TABLE IF EXISTS costco.member;


                DROP TABLE IF EXISTS costco.inventory;


                DROP TABLE IF EXISTS costco.employee;


                DROP TABLE IF EXISTS costco.store;


                DROP TYPE IF EXISTS costco_job;

Then we recreate the schema to hold all our tables

CREATE SCHEMA IF NOT EXISTS costco;

The last piece of preperation we need to do before creating the tables is specifying a new type. Python recognizes the standard types such as strings for words, and integers for numbers, but if we want a column to only contain certain values we can make a new type and tell it what it can be set to. For this database project we need to create a type that limits the entrys into that column to only the occupations of the store.

CREATE TYPE costco_job AS ENUM ('restocker', 'manager', 'cook', 'receipt checker', 'cashier');

Tables

Now, we can create the tables in our database. Its important that we leave the foreign keys until the end so that they have the tables they need to connect to already made.


                CREATE TABLE costco.store(
                store_id SERIAL PRIMARY KEY,
                store_address VARCHAR(255)
                );


                CREATE TABLE costco.employee(
                employee_id INTEGER PRIMARY KEY,
                employee_name VARCHAR(255),
                store INTEGER,
                occupation costco_job,
                FOREIGN KEY (store) REFERENCES costco.store(store_id)
                );


                CREATE TABLE costco.inventory(
                store_id INTEGER,
                item_id SERIAL,
                stock INTEGER,
                cost MONEY,
                product_name VARCHAR(255),
                FOREIGN KEY (store_id) REFERENCES costco.store(store_id),
                PRIMARY KEY(store_id, item_id)
                );


                CREATE TABLE costco.member(
                member_id SERIAL,
                expiration_date DATE,
                member_name VARCHAR(255),
                member_address VARCHAR(255),
                PRIMARY KEY(member_id, expiration_date)
                );


                CREATE TABLE costco.order(
                order_id SERIAL PRIMARY KEY,
                store_id INTEGER,
                customer_id INTEGER,
                cost MONEY,
                before_date DATE,
                FOREIGN KEY (store_id) REFERENCES costco.store(store_id),
                FOREIGN KEY (customer_id, before_date) REFERENCES costco.member(member_id, expiration_date)
                );


                CREATE TABLE costco.order_item(
                order_id INTEGER,
                store_id INTEGER,
                item_id INTEGER,
                FOREIGN KEY (order_id) REFERENCES costco.order(order_id),
                FOREIGN KEY (store_id, item_id) REFERENCES costco.inventory(store_id, item_id),
                PRIMARY KEY(order_id, item_id)
                );


                CREATE TABLE costco.food_court(
                store_id INTEGER,
                food_id INTEGER,
                food_name VARCHAR(255),
                price MONEY,
                FOREIGN KEY (store_id) REFERENCES costco.store(store_id),
                PRIMARY KEY(store_id, food_id)
                );

The final step in the set up of our database, is to create a view, which you can query and connects different tables for you so you can see the data easier. For this table, we will create a view that allows you to see all the information about any specific order.


                CREATE VIEW vw_order_info  AS select costco.order.cost, costco.member.member_id, 
                costco.member.member_name, costco.member.expiration_date, costco.member.member_address, 
                costco.store.store_id, costco.store.store_address from costco.order JOIN costco.member ON 
                (costco.member.member_id = costco.order.customer_id) JOIN costco.store ON (costco.order.store_id 
                = costco.store.store_id) limit 20;

Excel

Often times viewing data through a code window can be confusing, so we can convert our data into an excel document. You may wonder why we don't store the data in excel originally, but that is because that often takes a large amount of storage compared to storing it in a database.

Costco Database Project

ER Diagram

Tables

Excel