logo
 
Assignment - More SQLing
CS 5800 - Database Systems
Utah State University
Home
Calendar
Homework   
Lectures
Syllabus
Resources
People

Due date: Thursday, Nov. 16 (23:59), late turnins will have 20% deducted.
Turnin: Use Canvas.

Overview

This assignment is to write SQL constraints and optimize SQL queries.

Grading

Good programming style is always important. Your code should be clear, concise, and have appropriate comments.

Groups

The assignment permits you to work in groups of at most two. I will assume that the same groups for the SQL assignment. E-mail me if your group has dissolved or if you are in a new group.

Deliverables and Submission Instructions

The assignment turnin will be a zipped folder called sql that contains two files: constraints.sql and optimize.pdf (or optimize.doc or optimize.odl). Please use Canvas to turnin the assignment.

Case-insensitive Postgres

Use the postgres2.sql script to create the baseball database to work with the queries below. It removes the double quotes from the create table field names to permit case-insensitive processing.

Postgres

Postgres has an excellent wiki on optimization at http://wiki.postgresql.org/wiki/Performance_Optimization.

MySQL caveats

The CHECK constraint in MySQL is broken. You can specify it but it will not check anything (in some releases). So pretend that is does work, use it for a constraint and include an INSERT/MODIFY that would violate the constraint as if CHECK did work.

Optimization in MySQL is explained in detail in the MySQL Reference Manual chapter on Optimization.

MySQL supports only row-level triggers.

If you are using MySQL and want to catch all events to fire a trigger, you need to specify more than one trigger for a table, i.e., one trigger for INSERT, one trigger for DELETE, etc.

In MySQL, call the Fail routine to force a trigger to fail (a failed trigger aborts updates). For example, add the following to the trigger to force it to fail.

  call Fail("Baseball delete denied!");

Creating and Loading the Database

Use the baseball database in the previous assignment.

Constraints

Create a file called constraints.sql constraints.sql - A text file containing your solutions to the following constraints as ALTER TABLE statement or CREATE TRIGGER statements. (Note that creating a constraint may or may not affect tuples already in the database, it depends on the DBMS you are using and whether you specify ENABLE NOVALIDATE/VALIDATE.)
  1. The default number of ABs is 20.
  2. A player cannot have more H (hits) than AB (at bats).
  3. In the Teams table, the league can be only one of the values: NL or AL.
  4. When a team loses more than 161 games in a season, the fans want to forget about the team forever, so all batting records for the team for that year should be deleted.
  5. If a player wins the MVP, WS MVP, and a Gold Glove in the same season, they are automatically inducted into the Hall of Fame.
  6. All teams must have some name, i.e., it cannot be null.
  7. Everybody has a unique name (combined first and last names).

The text file should be formatted as follows.

-- Constraint 1
-- The default number of at bats is 20.
ALTER TABLE ... 
-- Insertion/update/or delete to test the constraint
INSERT INTO ...
-- Constraint 2
...

Optimize

Examine the following queries. For each query, you will describe how to improve the speed of the query by reducing the block I/O (or rows in the estimated query plan for MySQL, see EXPLAIN). Create a document (Word, PDF, or HTML) that includes for each query.
  1. The cost of evaluating the query.
  2. What you did to optimize the query.
  3. The cost re-evaluated after optimization.
You may include screen shots of a Query Analyzer tool to help make your case. One caveat, it may be the case that the query can not be optimized.
  1. SELECT 
        DISTINCT m.nameFirst, m.nameLast
    FROM
        master m,
        pitching a,
        teams t
    WHERE
        m.masterId = a.masterId
            AND t.teamId = a.teamId
            AND t.lgID = a.lgID
            AND t.name like "%Montreal Expos%"
            AND t.yearID = a.yearId
            AND a.w > 20
    
  2. SELECT 
        h / ab as Average, h as "Hits", ab as "At Bats", nameFirst as "First Name", nameLast as "Last Name", batting.yearID as Year
    FROM
        batting,
        master
    WHERE
        ab is not null
            and batting.masterID = master.masterID
            AND master.masterID IN (SELECT 
                masterID
            FROM
                schoolsplayers
            WHERE
                schoolID in (SELECT 
                        schoolID
                    FROM
                        schools
                    WHERE
                        schoolName like 
    %Utah State%
    )) order by year
  3. SELECT distinct jeter.masterID, jeterT.masterID, jeterTY.masterID, jeterTT.masterID
    FROM
        master m,
        appearances jeter,
        appearances jeterT,
    	appearances jeterTY,
        appearances jeterTT
    WHERE
             m.masterID = jeter.masterID
            AND m.nameLast = 
    Jeter
    AND m.nameFirst =
    Derek
    AND jeter.teamID = jeterT.teamID AND jeter.yearID = jeterT.yearID AND jeter.lgID = jeterT.lgID AND jeter.masterID <> jeterT.masterID

    AND jeterT.masterID = jeterTY.masterID

    AND jeterTY.teamID = jeterTT.teamID and jeterTY.yearID = jeterTT.yearID AND jeterTY.lgID = jeterTT.lgID AND jeterTY.masterID <> jeterTT.masterID

    AND jeterTT.masterID <> jeter.masterID

    AND jeter.teamID <> jeterTY.teamID

  4. SELECT 
        name, yearId, W
    FROM
        teams T
    WHERE
        W = (SELECT 
                MAX(W)
            FROM
                teams y
            WHERE
                t.yearID = y.yearID
                    )
    
  5. SELECT
        C.yearID as year,
        name as teamName,
        C.lgID as league,
        D.cnt as totalBatters,
        C.cnt as aboveAverageBatters
    FROM
        (SELECT 
            count(masterID) as cnt, A.yearID, A.teamID, A.lgID
        FROM
            (select 
            masterID,
                teamID,
                yearID,
                lgID,
                sum(AB),
                sum(H),
                sum(H) / sum(AB) as avg
        FROM
            batting
        GROUP BY teamID , yearID , lgID , masterID) B, (select 
            teamID,
                yearID,
                lgID,
                sum(AB),
                sum(H),
                sum(H) / sum(AB) as avg
        FROM
            batting
        WHERE ab is not null
        GROUP BY teamID , yearID , lgID) A
        WHERE
            A.avg >= B.avg AND A.teamID = B.teamID
                AND A.yearID = B.yearID
                AND A.lgID = B.lgID
        GROUP BY teamID , yearID , lgID) C,
        (SELECT 
            count(masterID) as cnt, yearID, teamID, lgID
        FROM
            batting
        WHERE ab is not null
        GROUP BY yearID , teamID , lgID) D, 
        teams
    WHERE
        C.cnt / D.cnt >= 0.75
            AND C.yearID = D.yearID
            AND C.teamID = D.teamID
            AND C.lgID = D.lgID
            AND teams.yearID = C.yearID
            AND teams.lgID = C.lgID
            AND teams.teamID = C.teamID
    
  6. SELECT distinct
        master.nameFirst as "First Name", master.nameLast as "Last Name"
    FROM
        (SELECT 
            b.masterID as ID, b.yearID as year
        FROM
            batting b, teams t
        WHERE
            name like "%New York Yankees%"
                and b.teamID = t.teamID
                and b.yearID = t.yearID
                and t.lgID = b.lgID) y1,
        (SELECT 
            b.masterID as ID, b.yearID as year
        FROM
            batting b, teams t
        WHERE
            name like "%New York Yankees%"
                and b.teamID = t.teamID
                and b.yearID = t.yearID
                and t.lgID = b.lgID) y2,
        (SELECT 
            b.masterID as ID, b.yearID as year
        FROM
            batting b, teams t
        WHERE
            name like "%New York Yankees%"
                and b.teamID = t.teamID
                and b.yearID = t.yearID
                and t.lgID = b.lgID) y3,
        (SELECT 
            b.masterID as ID, b.yearID as year
        FROM
            batting b, teams t
        WHERE
            name like "%New York Yankees%"
                and b.teamID = t.teamID
                and b.yearID = t.yearID
                and t.lgID = b.lgID) y4,
        master
    WHERE
        y1.id = y2.id and y2.id = y3.id
            and y3.id = y4.id
            and y1.year + 1 = y2.year
            and y2.year + 1 = y3.year
            and y3.year + 1 = y4.year
            and y4.id = master.masterID
    ORDER BY master.nameLast, master.nameFirst
    

  7. SELECT 
        name,
        A.lgID,
        A.S as TotalSalary,
        A.yearID as Year,
        B.S as PreviousYearSalary,
        B.yearID as PreviousYear
    FROM
        (SELECT 
            sum(salary) as S, yearID, teamID, lgID
        FROM
            salaries
        group by yearID , teamID , lgID) A,
        (SELECT 
            sum(salary) as S, yearID, teamID, lgID
        FROM
            salaries
        group by yearID , teamID , lgID) B,
        teams
    WHERE
        A.yearID = B.yearID + 1
            AND (A.S * 2) <= (B.S)
            AND A.teamID = B.teamID
            AND A.lgID = B.lgID
            AND teams.yearID = A.yearID
            AND teams.lgID = A.lgID
            AND teams.teamID = A.teamID
    

                                                                                                                                                                                                                                                                                                                                             
  Copyright © 2017 by Curtis Dyreson. All rights reserved.