PostgreSQL Database Development & Data Analysis
Budget: €30 – €250 EUR
My project requires a skilled individual knowledgeable in both designing efficient database architecture and data analysis using SQL queries. Here's what you'll be working on:
- We're utilizing PostgreSQL as our database management system. As such, familiarization and prior experience is a must.
REQUIREMENTS
- Aim (10%)
You must supply a README.txt (plain text, not word or PDF) file that contains the following information:
A brief (1 paragraph) introduction summarising the data source you will be using, links to where the data sources may be obtained from, 3-5 specific outputs / functionality.
- Database design (20%)
Data should be analysed with a view to normalising to at least 3NF.
ER diagram to show normalised entities and relationships This can be hand-drawn, drawn on a computer or produced from a database tool. ER diagram must be single page, A4, PDF named er_diagram.pdf.
- Database setup (10%)
Write appropriate SQL to generate table(s) suitable to load data into. Table(s) should be defined in file database_setup.sql.
For full marks, tables should have:
correct column names, suitable data types set on columns, primary key set explicitly in Database constraints: NOT NULL, UNIQUE, CHECK as appropriate.
other suitable features such as generated columns if needed
- Data Load (20%)
Write a Python script to load data into your database data_load.py.
You must include your raw data file (CSV, XML, JSON, etc) in your submission.
Alternatively your data loading program can load data directly from the internet.
- Python query program (30%)
You are to provide a Python program data_query.py to query the database. You may employ pandas but you should try to select only the data needed from the database. It should permit the user to generate outputs identified in your README file. You may use either interactive prompting and/or command-line arguments.
- Version control (10%)
Your git repository should have a number of meaningful atomic commits with appropriate messages.
You'll need to design effective and efficient data models. Experience in data structuring in a PostgreSQL environment will be highly valued.
Creation of complex SQL queries is crucial to the role. So, you'll be expected to have adequate experience and skills in SQL.
Your focus on performance, adept analysis skills and keen attention to detail will be key to the success of this project. Having a proactive approach, proposing improvements, and ensuring data accuracy are essential components of the work. If you think you can excel at these, get in touch!
- We're utilizing PostgreSQL as our database management system. As such, familiarization and prior experience is a must.
REQUIREMENTS
- Aim (10%)
You must supply a README.txt (plain text, not word or PDF) file that contains the following information:
A brief (1 paragraph) introduction summarising the data source you will be using, links to where the data sources may be obtained from, 3-5 specific outputs / functionality.
- Database design (20%)
Data should be analysed with a view to normalising to at least 3NF.
ER diagram to show normalised entities and relationships This can be hand-drawn, drawn on a computer or produced from a database tool. ER diagram must be single page, A4, PDF named er_diagram.pdf.
- Database setup (10%)
Write appropriate SQL to generate table(s) suitable to load data into. Table(s) should be defined in file database_setup.sql.
For full marks, tables should have:
correct column names, suitable data types set on columns, primary key set explicitly in Database constraints: NOT NULL, UNIQUE, CHECK as appropriate.
other suitable features such as generated columns if needed
- Data Load (20%)
Write a Python script to load data into your database data_load.py.
You must include your raw data file (CSV, XML, JSON, etc) in your submission.
Alternatively your data loading program can load data directly from the internet.
- Python query program (30%)
You are to provide a Python program data_query.py to query the database. You may employ pandas but you should try to select only the data needed from the database. It should permit the user to generate outputs identified in your README file. You may use either interactive prompting and/or command-line arguments.
- Version control (10%)
Your git repository should have a number of meaningful atomic commits with appropriate messages.
You'll need to design effective and efficient data models. Experience in data structuring in a PostgreSQL environment will be highly valued.
Creation of complex SQL queries is crucial to the role. So, you'll be expected to have adequate experience and skills in SQL.
Your focus on performance, adept analysis skills and keen attention to detail will be key to the success of this project. Having a proactive approach, proposing improvements, and ensuring data accuracy are essential components of the work. If you think you can excel at these, get in touch!