Please read through this page entirely and carefully, before beginning your work. All chapter and problem numbers refer to the Sixth Edition of the textbook.
Consider the following simple database of information about certain employees and companies for which they work.
|employee(ename, street, city)||— records residential addresses|
|company(cname, city)||— records business location|
|works(ename, cname, salary)||— records employment and salary information|
Write relational algebra expressions that generate the desired output in each of the following cases. For parts (a) and (b), use only the fundamental relational algebra operations. Elsewhere, you may use any of the relational algebra operations described in Chapters 2 and 6.
The file ~cs33/data/baseball_salaries_2003.txt contains salary information for certain professional baseball players from the year 2003. It should be self-explanatory. Write an SQL script that processes this file to determine, for each position, the average salary of the players in that position. Note that the seven positions represented in the input file are "Catcher", "First Baseman", "Outfielder", "Pitcher", "Second Baseman", "Shortstop" and "Third Baseman".
Your script should create an appropriate table in your database
and populate it using the data in the input file. It should then
execute a single SQL query whose output has the schema
The file ~cs33/data/baseball_salaries_2005.txt is very similar to the above and contains data for 2005, but for a different set of teams. However, there are a number of players who appear in both data sets. Write an SQL script that determines the set of players that appear in both files, along with their 2003 and 2005 salaries, and the resulting percentage change in their salary.
Your script should create two appropriate tables in your
database, populate them from the input files, and then execute a
single SQL query whose output has the schema
mysql -t -u uname -p dbname < hw1p7.sqlWe urge you to test your script using a similar command, many times, before you submit it.