Reference no: EM132408789
Project: Tools rental store
Section A – Setup Python for database connection
Project overview
The project includes three phases:
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
2. List of relations, including attributes and data type, constraints (such as FK, PK), relationships – cardinality and optionality in Crow foot notation.
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.
Phase #3 – Create Python script
New concepts: Database connection
You will need to install a driver to connect from Python program to the database
In Python script
o Import the driver
Import the driver into your python program with: import psycopg2
o Create the database connection
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’.
Attachment:- Project Details.rar