o Phase #1: Database conceptual design

Project: Tools rental storeSection A – Setup Python for database connectionProject overviewThe project includes three phases:o Phase #1: Database conceptual designSubmission: ERD diagram with entities…

Project: Tools rental storeSection A – Setup Python for database connectionProject overviewThe project includes three phases:o Phase #1: Database conceptual designSubmission: ERD diagram with entities and relationships between them. ERD diagramcan be prepared in draw.io, for example.Entity: entity name, attributes and their types, constraints such as primary and foreignkeys should be indicated with PK and FK.Relationships: each relationship should indicate cardinality and optionality in crow footnotation.o Phase #2: Database creation, which includeso Database creationo Table creationo Table populationo Data management: Queries (select), Insert, DeleteSubmission: Text files with extension *.sql with SQL statementso Phase #3: Python program to manage databaseSubmission: Python script, test plan, demoPhase #1 – Database conceptual designData requirementsThe 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 customerso Name: first, lasto Postal codeo Phone numbero Membership date tools:o Titleo Jobs that can require a given tool: to be modeled as a separate entity. Forexample, tool name – snake, job – plumming.o Rental duration in days rentalso Rental datep Return dateHints: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 3tables: 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 usethe serial“type” instead of the integer data type when declaring a primary key: it is equivalent toan unsigned integer, not null, and autoincrementing; it is based on the setup of asequence. Another advantage of the “surrogate” key is that your primary key will bemade up only of one attribute, otherwise, for example, in table that describes toolrentals you will have to create a composite (key made up of several attributes) primarykey 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 inthe related table should be declared with integer data type. Inserting tuples with primary keys: If you are using serial in your INSERT statementyou do not need to specify value corresponding to attribute declared with SERIAL, theattribute will obtain value automatically based on the next available sequence numbero 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 valueof the primary key) use an SQL query that will retrieve the primary key from theparent table.o Syntax: INSERT INTO table (field1, field2_foreign_key)VALUES ( ‘value’, (SELECT field_primary_key FROM tableWHERE field = ‘value’);oExample: 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 tablewhich corresponds to tool with name ‘Hammer’. Weinsert it into tool_jobs table where tool_id is a FKthat references table tools.Submission1. ERD diagram2. 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, PKtool_name: varchar(50)rental_days: smallinto Foreign key example for relation “tool_jobs” on the ERD diagram:tool_id: integer (FK)Phase #2 – Setting up databaseDatabase creationDatabaseDatabase name: You can select a name for your tool rental store or call it “tool_store_MN”.TablesSetup all required tables using SQL statements written to one or more text file(s). Use .sqlextension to save files. Use the psql input feature (i file) to setup the database inPostgreSQL. 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, rentaldate, 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 tocurrent date:current_date + rental_days).o Delete a tool rental based on tool ID and customer ID.SubmissionAll 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 jobso One for any one of the other relationso Note: You can partner up with another student to exchange the other tablepopulation 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 toolo Display all overdue rentals with customer phone number.Phase #3 – Create Python scriptNew concepts: Database connectionYou will need to install a driver to connect from Python program to the database Install the database driver: psycopg2o 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 importdriver with “import psycopg2”. You should not begetting any import errors. Exit the interpreter. In Python scripto Import the driverImport the driver into your python program with: import psycopg2o Create the database connectionSyntax: 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 isthe 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 elementscan be accessed by indexExample: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-drivenThe program displays a main menu with the following menu options:1. Queries (sub-menu)2. Insert tool rental3. Delete tool rentalx.ExitIf the first option is selected a submenu is displayed with the following query options:1. Display all customers2. Display all tools (optionally by job)3. Display all rentals (optionally by customer)4. Display all overdue rentalsThe program runs until the user selects the exit option ‘x’.#2. FunctionsDecompositionTo give you an idea I used the following functions (function parameters not listed) for mysolution:

 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() # optionalo query_rentals()o query_rentals_by_customer()o query_overdue_rentals()o is_tool_rented_by_customer() Insert/delete functionso delete_rental()o insert_rental()Function details Display functions: odisplays datamay have a function argument;may have a return value. Getter functions:o returns a valuemay 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 willreturn the result of a query (see example below):Example: def get_tool_id(tool_name):sqlQuery = “SELECT id FROM toolsWHERE tool_name = ” + tool_namecursor.execute(sqlQuery)tuple = cursor.fetchone()return tuple[0] Query functions:o returns the result of a querymay have a function argument;may have a return value.o The display a query result you can loop through the result set by fetching onetuple at a time using the fetchone() cursor method (there are other cursormethods).Example: tuple = cursor.fetchone()while tuple is not None:print tuplecursor.fetchone() Insert/delete functiono Sample PDL for insert functionality:Get customer nameGet customer ID (based on customer name)Display tools currently rented by customer (based on customerid). Display all tools.Get tool name to rentGet tool ID of tool name to rent (based ontool name) If tool is rented by customerdisplay error messageOtherwise insert the tool rental record (using tool id and customerid). 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

Let’s block ads! (Why?)

Do you need any assistance with this question?
Send us your paper details now
We’ll find the best professional writer for you!

 



error: Content is protected !!