Project: Tools rental store
Section A – Setup Python for database connection
Project overview
The project includes three phases:
o Phase #1: Database conceptual design
Submission: ERD diagram with entities and relationships between them. ERD diagram
can be prepared in draw.io, for example.
Entity: entity name, attributes and their types, constraints such as primary and foreign
keys should be indicated with PK and FK.
Relationships: each relationship should indicate cardinality and optionality in crow foot
notation.
o Phase #2: Database creation, which includes
o Database creation
o Table creation
o Table population
o Data management: Queries (select), Insert, Delete
Submission: Text files with extension *.sql with SQL statements
o Phase #3: Python program to manage database
Submission: Python script, test plan, demo
Phase #1 – Database conceptual design
Data requirements
The following data has to be managed for the tool rental store: customer information,
tools information, rental information. This will require 5 tables.
Entities with attributes
customers
o Name: first, last
o Postal code
o Phone number
o Membership date
tools:
o Title
o Jobs that can require a given tool: to be modeled as a separate entity. For
example, tool name – snake, job – plumming.
o Rental duration in days
rentals
o Rental date
p Return date
Hints:
Why 5 tables? Relationship between tools and jobs is many-to-many. Use “associative pattern”
(ERD lecture) to resolve many-to-many relationships. Out of tools and jobs we will have 3
tables: tools, jobs, and tool_jobs.
Useful concepts
Recall good naming conventions: it is good idea to name your primary key (PK) as “id”.
If some other table references this PK – give it name “tablename_id”. For example,
tool_id in table tool_rentals would reference PK id in table tools.
Auto-incrementing (“surrogate”) primary key: To facilitate primary key management use
the serial
“type” instead of the integer data type when declaring a primary key: it is equivalent to
an unsigned integer, not null, and autoincrementing; it is based on the setup of a
sequence. Another advantage of the “surrogate” key is that your primary key will be
made up only of one attribute, otherwise, for example, in table that describes tool
rentals you will have to create a composite (key made up of several attributes) primary
key made up of tool_id (foreign key to attribute id of tools table) and customer_id
(foreign key to attribute id of customers table).
o Example: CREATE TABLE tools (
id serial PRIMARY KEY,
tool_name varchar(200) not null unique,
rental_days smallint not null
);
Foreign key reference: Note that foreign key that references SERIAL primary key in
the related table should be declared with integer data type.
Inserting tuples with primary keys: If you are using serial in your INSERT statement
you do not need to specify value corresponding to attribute declared with SERIAL, the
attribute will obtain value automatically based on the next available sequence number
o Syntax: INSERT INTO table (field1, field2, … fieldN)
VALUES (value1, value2, … valueN);
o Example: INSERT INTO tools (tool_name,
| rental_days) VALUES | (‘hammer’, 14); |
| | Inserting tuples with foreign keys: The foreign key needs to have the same value as |
the primary key. In order to automate the process (let the DB system look up the value
of the primary key) use an SQL query that will retrieve the primary key from the
parent table.
o Syntax: INSERT INTO table (field1, field2_foreign_key)
VALUES ( ‘value’, (SELECT field_primary_key FROM table
WHERE field = ‘value’);
o
Example: INSERT INTO table tool_jobs(tool_id, job_id) VALUES
((SELECT id FROM tools WHERE tool_name = ‘Hammer’ ),
(SELECT id FROM jobs WHERE job_name
= ‘roofing’));
o Explanation: The SELECT id FROM tools WHERE tool_name =
‘Hammer’ will return value of PK from the tools table
which corresponds to tool with name ‘Hammer’. We
insert it into tool_jobs table where tool_id is a FK
that references table tools.
Submission
1. ERD diagram
2. List of relations, including attributes and data type, constraints (such as FK, PK),
relationships – cardinality and optionality in Crow foot notation.
o Attributes example for relation “tools” on the ERD diagram:
id: serial, PK
tool_name: varchar(50)
rental_days: smallint
o Foreign key example for relation “tool_jobs” on the ERD diagram:
tool_id: integer (FK)
Phase #2 – Setting up database
Database creation
Database
Database name: You can select a name for your tool rental store or call it “tool_store_MN”.
Tables
Setup all required tables using SQL statements written to one or more text file(s). Use .sql
extension to save files. Use the psql input feature (\i file) to setup the database in
PostgreSQL.
Set up all tables.
Populate all tables with a minimum of 8 tuples per table.
Prepare the following SQL queries to:
o Display all customer information.
o Display all tools information, including jobs (sorted by tool_name and jobs).
o Display all tools for a given job.
o Display all rentals, including: customer first and last name, tool_name, rental
date, rental return date.
o Display all rentals for a given customer.
o Display a rental for a given customer and given tool.
o Display all overdue rentals.
| | Prepare the following SQL data management statements: insert, delete: |
| o | Insert a tool rental based on: tool ID, customer ID, rental date (use SQL |
current_date function) and return date (based on rental days added to
current date:
current_date + rental_days).
o Delete a tool rental based on tool ID and customer ID.
Submission
All files on your Linux system that include your SQL statements to setup the database:
One (or more) text file(s) for the creation of all tables.
Two text files (extension .sql) to populate a table.
o One for tools OR customers OR jobs
o One for any one of the other relations
o Note: You can partner up with another student to exchange the other table
population files.
One (or two) text file(s) that contains the following statements:
o One insert statement to create a new rental record.
o One delete statement to delete an existing rental record.
One (or more) text file(s) that contains the following query statements (select):
o Display all customer information.
o Display all tools, including jobs, sorted by jobs.
o Display all tools for a given job.
o Display all rentals, including: customer first and last name, tool name,
rental date, rental return date.
o Display all rentals for a given customer.
o Display a rental for a given customer and given tool
o Display all overdue rentals with customer phone number.
Phase #3 – Create Python script
New concepts: Database connection
You will need to install a driver to connect from Python program to the database
Install the database driver: psycopg2
o Install postgresql-devel with yum.
p Install python-devel with yum.
o Install python-psycopg2 with yum.
• Enter the python interpreter and verify you can import
driver with “import psycopg2”. You should not be
getting any import errors. Exit the interpreter.
In Python script
o Import the driver
Import the driver into your python program with: import psycopg2
o Create the database connection
Syntax: connection = psycopg2.connect(database =
‘name’, user = ‘dbadmin’)
Example: connection = psycopg2.connect(database
= ‘tool_rental_db’, user = ‘dbadmin’)
Once you have connection object, you derive a cursor object from it. Cursor object is
the object which executes SQL statements. Typically you would derive cursor,
execute statement, read results if needed, close it.
Fetchone will return an object of type tuple – a special type of list which elements
can be accessed by index
Example:
Tup = (one, two, three)
print Tup[1]
#print statement will print two – second element of the tuple
Close the database connection (at the end of the script)
o Close the database connection with: connection.close()
Program requirements
#1. Menu-driven
The program displays a main menu with the following menu options:
1. Queries (sub-menu)
2. Insert tool rental
3. Delete tool rental
x.Exit
If the first option is selected a submenu is displayed with the following query options:
1. Display all customers
2. Display all tools (optionally by job)
3. Display all rentals (optionally by customer)
4. Display all overdue rentals
The program runs until the user selects the exit option ‘x’.
#2. Functions
Decomposition
To give you an idea I used the following functions (function parameters not listed) for my
solution:
| | Display functions: display_main_menu() display_query_menu() display_query() Getter functions |
| | |
| o | get_menu_selection() |
o get_customer_name()
o get_customer_id()
o get_tool_name()
o get_tool_id()
| o | get_rental_days() |
| | Query functions |
o query_customers()
o query_tools()
o query_tools_by_job() # optional
o query_rentals()
o query_rentals_by_customer()
o query_overdue_rentals()
o is_tool_rented_by_customer()
Insert/delete functions
o delete_rental()
o insert_rental()
Function details
Display functions: o
displays data
may have a function argument;
may have a return value.
Getter functions:
o returns a value
may have function arguments;
always has a return value.
Example in calling function: customerID =
get_customer_id(last_name, first_name)
o In this program some getter functions will return user input, and some will
return the result of a query (see example below):
Example: def get_tool_id(tool_name):
sqlQuery = “SELECT id FROM tools
WHERE tool_name = ” + tool_name
cursor.execute(sqlQuery)
tuple = cursor.fetchone()
return tuple[0]
Query functions:
o returns the result of a query
may have a function argument;
may have a return value.
o The display a query result you can loop through the result set by fetching one
tuple at a time using the fetchone() cursor method (there are other cursor
methods).
Example: tuple = cursor.fetchone()
while tuple is not None:
print tuple
cursor.fetchone()
Insert/delete function
o Sample PDL for insert functionality:
Get customer name
Get customer ID (based on customer name)
Display tools currently rented by customer (based on customer
id). Display all tools.
Get tool name to rent
Get tool ID of tool name to rent (based on
tool name) If tool is rented by customer
display error message
Otherwise insert the tool rental record (using tool id and customer
id). Commit change to database with: connection.commit()
Display tools currently rented by customer (based on customer id)
Submission
Python script with comments
Test plan
Demo based on marking guide