COP4710 Spring 19 Project 3 - PostgreSQL Functions

Due: 11:30pm, March 18, 2019 (Monday)

This is the layout of the table

• coaches_season, each tuple of which describes the performance of one coach in one season;
• teams, each tuple of which gives the basic information of a team;
• players, each tuple of which gives the basic information of one player;
• player_rs, each tuple of which gives the detailed performance of one player in one regular season;
• player_rs_career, each tuple of which gives the detailed regular-season performance of one player in his career;
• draft, each tuple of which shows the information of an NBA draft.

I. Introduction In this project, you are expected to write two pgplsql functions and run them against the same NBA database you used for Project 2.

II. Getting started Your project should be done and tested in element.cse.usf.edu. Before you start, you need to do two things: the first is to create a new database of your own. Upon logging into catalyst via ssh, you can do so by typing the following command from your Linux console:

createdb NBA-xxx

where xxx is your NetID. The next thing is to copy all tables (including the data in them) from the 'NBA' database to your database. This can be done by copying tables one by one. For example, you can type

pg_dump -t players_rs NBA | psql NBA-xxx

to copy the players_rs table. Remember to copy all tables you want to use. After the above steps, you should be able to log into this new database by

psql NBA-xxx

and query all the data.

III. Functions to write

Your task in this project is to write the following two functions in plpgsql and test them in the NBA-xxx database:

1. Write a function named nba_efficiency that, given the first name and last name of a player, returns the NBA Efficiency. NBA Efficiency, invented by Martin Manley, is being considered the first ever player evaluation metric which indicates player's linear efficiency. It is defined as the following:

NBA Efficiency Formula= (Points)+(Rebounds)+(Steals)+(Assists)+(Blocked Shots)-(Turnovers)-(Missed Shots)

Note that the player_rs_career table has all the statistics for this formula: (Points): pts, (Rebounds): reb, (Steals): stl, (Assists): asts, (Blocked Shots): blk, (Turnovers): turnover, (Missed Shots): (fga-fgm) + (fta-ftm) + (tpa-tpm).

2. Write a function named body_mass_index that, given the first name and last name of a player, returns the Body Mass Index (BMI) of that player. The formula for BMI is weight in kilograms divided by height in meters squared, and then multiplying the result by 10,000, i.e., [weight (kg) / height (cm) / height (cm)] x 10,000. Note that the players table stores height information in feet and inches and weight information in pounds, thus you have to convert them into metric system (hint: 1ft. = 30.48cm, 1in. = 2.54cm, and 1lb. = 0.45kg). If the name given does not match any player in the playerstable, the function returns 0.