In this assignment, you procure scheme the consultations to abide basis in the CSVs, significance the CSVs into a SQL basisbase, and defense questions about the basis. In other tone, you procure perform:
Note: You may heed the statement "Data Modeling" in establish of "Data Engineering," but they are the identical statements. Basis Engineering is the over later wording instead of Basis Modeling.
Inspect the CSVs and harborray out an ERD of the consultations. Feel gratuitous to use a instrument relish http://www.quickdatabasediagrams.com.
Use the knowledge you accept to engender a consultation schema for each of the six CSV rasps. Remember to determine basis types, principal keys, extraneous keys, and other constraints.
For the principal keys stay to see if the column is singular, differently engender a composite key. Which engages to principal keys in ordain to singularly ordeal a row.
Be unfailing to engender consultations in the imverify ordain to manipulate extraneous keys.
Imharbor each CSV rasp into the identical SQL consultation. Note be unfailing to significance the basis in the identical ordain that the consultations were engenderd and statement for the headers when significanceing to fly errors.
Once you accept a accomplished basisbase, do the forthcoming:
List the forthcoming details of each employee: employee sum, decisive call, principal call, sex, and stipend.
List principal call, decisive call, and engage determination for employees who were engaged in 1986.
List the overseer of each function delay the forthcoming knowledge: function sum, function call, the overseer's employee sum, decisive call, principal call.
List the function of each employee delay the forthcoming knowledge: employee sum, decisive call, principal call, and function call.
List principal call, decisive call, and sex for employees whose principal call is "Hercules" and decisive calls start delay "B."
List all employees in the Sales function, including their employee sum, decisive call, principal call, and function call.
List all employees in the Sales and Development functions, including their employee sum, decisive call, principal call, and function call.
In descending ordain, schedule the abundance calculate of employee decisive calls, i.e., how frequent employees harborion-out each decisive call.
As you discuss the basis, you are overpower delay a creeping mistrust that the basisset is fake. You fancy that your boss indexed you counterfeit basis in ordain to ordeal the basis engineering skills of a new employee. To stabilitate your slice, you determine to engage the forthcoming steps to originate a visualization of the basis, delay which you procure encounter your boss:
Imharbor the SQL basisbase into Pandas. (Yes, you could unravel the CSVs straightly in Pandas, but you are, following all, involved to verify your technical ardor.) This step may demand some lore. Feel gratuitous to use the mode underneath to get instituted. Be unfailing to fabricate any ineviconsideration modifications for your username, password, army, harbor, and basisbase call:
from sqlalchemy significance engender_engine engine = engender_engine('postgresql://localhost:5432/<your_db_name>') concatenation = engine.connect()
Consult SQLAlchemy documentation for over knowledge.
If using a password, do not upload your password to your GitHub magazine. See https://www.youtube.com/watch?v=2uaTPmNvH0I and https://help.github.com/en/github/using-git/ignoring-files for over knowledge.
Create a histogram to visualize the most sordid stipend ranges for employees.
Create a bar chart of mediocre stipend by appellation.
Evidence in index, you retreat into your boss's station and bestow the visualization. Delay a sly cachinnation, your boss thanks you for your result. On your way out of the station, you heed the tone, "Search your ID sum." You face down at your device to see that your employee ID sum is 499942.
Create an copy rasp of your ERD.
Create a .sql rasp of your consultation schemata.
Create a .sql rasp of your queries.
(Optional) Engender a Jupyter Notebook of the benefit dissection.