Light

lexglu / test_smart Goto Github PK

View Code? Open in Web Editor NEW

0.0 1.0 0.0 231 KB

Design of database for sports web portal and data manipulation script (SQL + Python)

Shell 22.51% Python 77.49%

azure-data-studio docker docker-compose pyspark python sql sql-server-2022 t-sql bash bash-scripting

test_smart's Introduction

Setup and requirements

Clone the repository
Create a virtual environment by running python3 -m venv venv
Activate the virtual environment by running source venv/bin/activate
Run pip install -r requirements.txt to install the dependencies for task2

Task 1

Description of the database and scripts in accordance with the task1

Task1 description is available in the task1 directory task1.docx
ER diagram is available in the task1 directory ERD.pdf and via link (interactive): https://dbdiagram.io/d/64773a59722eb774942440cb
DB schema is available in the task1 directory db_schema.png
SQL scripts that create tables, keys, and constraints in the database are here create_tables.sql
SQL scripts that populate the tables with sample data are here insert_data.sql
SQL scripts for the stored procedures are here stored_procedures.sql
SQL scripts for 10 KPIs are here kpi.sql

Deploying the database via docker-compose

Navigate to the task1 directory by running cd task1
Run docker-compose up -d to start the database container
Execute docker exec -it sqlserver bash /usr/src/app/scripts/init_db.sh to initialize the database, this will create the database (sports_web_portal) and tables (DDL scripts) and populate them with sample data (DML scripts).
The database is now ready to be used, the connection parameters are:
- Host: localhost (or db if running from a docker container)
- Port: 1433
- Username: sa
- Password: VeryStrongPassword123
To stop the database container, run docker-compose down -v

Task 2

Task2 description is available in the task2 directory task2.docx
Python script that reads data from CSV files via pyspark dataframe is here task2.py

Running the script

Navigate to the task2 directory by running cd task2
Run python3 task2.py to run the script
The script will write results CSV files located in subdirectories of task2 (task2/task2_domain_fraction and task2/task2_people_count_by_company).

test_smart's People

Contributors

Watchers

Recommend Projects

React

A declarative, efficient, and flexible JavaScript library for building user interfaces.
Vue.js

🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
Typescript

TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
TensorFlow

An Open Source Machine Learning Framework for Everyone
Django

The Web framework for perfectionists with deadlines.
Laravel

A PHP framework for web artisans
D3

Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

javascript

JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
web

Some thing interesting about web. New door for the world.
server

A server is a program made to process requests and deliver data to clients.
Machine learning

Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Visualization

Some thing interesting about visualization, use data art
Game

Some thing interesting about game, make everyone happy.

Recommend Org

Facebook

We are working to build community through open source technology. NB: members must have two-factor auth.
Microsoft

Open source projects and samples from Microsoft.
Google

Google ❤️ Open Source for everyone.
Alibaba

Alibaba Open Source for everyone
D3

Data-Driven Documents codes.
Tencent

China tencent open source team.