CS541 – Databases

Database Systems

Instructor: Scott Spetka, Ph.D.

Menu:

Homework 1:

I setup this new page for CS541 off of my main website. I also already had MYSQL installed on all of my Gentoo workstations, and have been using the installations with a few different applications. I have outlined the install process for MYSQL on Gentoo here.

Back to Menu

Homework 2:

Setup Sample Company Database

I downloaded the MYSQL dump files from chapter 4, setup a new database called company on my MYSQL server, and gave myself full permissions to it.

mysql  -u root -p
Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 398
Server version: 5.1.56 Gentoo Linux mysql-5.1.56

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database company;
Query OK, 1 row affected (0.06 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| company            |
+--------------------+
10 rows in set (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON company.* TO 'ronny'@'%' IDENTIFIED BY 'mypass' WITH GRANT OPTION;
Query OK, 0 rows affected (0.03 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>exit

I then sourced the sql files into the new company database. It complained about unknown tables since it was a clean source, but it still created all of the new tables and added the data.

mysql> use company
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> source source.sql
ERROR 1051 (42S02): Unknown table 'department'
Query OK, 0 rows affected (0.00 sec)

ERROR 1051 (42S02): Unknown table 'employee'
Query OK, 0 rows affected (0.02 sec)

ERROR 1051 (42S02): Unknown table 'project'
Query OK, 0 rows affected (0.01 sec)

ERROR 1051 (42S02): Unknown table 'dept_locations'
Query OK, 0 rows affected (0.01 sec)

ERROR 1051 (42S02): Unknown table 'dependent'
Query OK, 0 rows affected (0.00 sec)

ERROR 1051 (42S02): Unknown table 'works_on'
Query OK, 0 rows affected (0.00 sec)

Query OK, 6 rows affected (0.02 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

Query OK, 40 rows affected (0.00 sec)
Records: 40  Deleted: 0  Skipped: 0  Warnings: 0

Query OK, 11 rows affected (0.00 sec)
Records: 11  Deleted: 0  Skipped: 0  Warnings: 0

Query OK, 13 rows affected (0.01 sec)
Records: 13  Deleted: 0  Skipped: 0  Warnings: 0

Query OK, 11 rows affected (0.00 sec)
Records: 11  Deleted: 0  Skipped: 0  Warnings: 0

Query OK, 48 rows affected (0.00 sec)
Records: 48  Deleted: 0  Skipped: 0  Warnings: 0

mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| department        |
| dependent         |
| dept_locations    |
| employee          |
| project           |
| works_on          |
+-------------------+
6 rows in set (0.00 sec)

mysql> select fname from employee;
+----------+
| fname    |
+----------+
| Andy     |
| Brad     |
| Evan     |
| Josh     |
| Jared    |
| Justin   |
| Jon      |
| John     |
| Alex     |
| Ahmad    |
| Joyce    |
| Ramesh   |
| Alicia   |
| John     |
| Jennifer |
| Franklin |
| James    |
| Tom      |
| Jenny    |
| Chris    |
| Kim      |
| Jeff     |
| Bonnie   |
| Alec     |
| Sam      |
| Nandita  |
| Bob      |
| Jill     |
| Kate     |
| Lyle     |
| Billie   |
| Jon      |
| Ray      |
| Gerald   |
| Arnold   |
| Helga    |
| Naveen   |
| Carl     |
| Sammy    |
| Red      |
+----------+
40 rows in set (0.00 sec)

mysql> select * from department;
+----------------+---------+-----------+--------------+
| dname          | dnumber | mgrssn    | mgrstartdate |
+----------------+---------+-----------+--------------+
| Research       |       5 | 333445555 | 1978-05-22   |
| Administration |       4 | 987654321 | 1985-01-01   |
| Headquarters   |       1 | 888665555 | 1971-06-19   |
| Software       |       6 | 111111100 | 1999-05-15   |
| Hardware       |       7 | 444444400 | 1998-05-15   |
| Sales          |       8 | 555555500 | 1997-01-01   |
+----------------+---------+-----------+--------------+
6 rows in set (0.00 sec)

mysql> select * from dept_locations;
+---------+-------------+
| dnumber | dlocation   |
+---------+-------------+
|       1 | Houston     |
|       4 | Stafford    |
|       5 | Bellaire    |
|       5 | Houston     |
|       5 | Sugarland   |
|       6 | Atlanta     |
|       6 | Sacramento  |
|       7 | Milwaukee   |
|       8 | Chicago     |
|       8 | Dallas      |
|       8 | Miami       |
|       8 | Philadephia |
|       8 | Seattle     |
+---------+-------------+
13 rows in set (0.00 sec)

mysql> select * from dependent;
+-----------+----------------+------+------------+--------------+
| essn      | dependent_name | sex  | bdate      | relationship |
+-----------+----------------+------+------------+--------------+
| 333445555 | Alice          | F    | 1976-04-05 | Daughter     |
| 333445555 | Theodore       | M    | 1973-10-25 | Son          |
| 333445555 | Joy            | F    | 1948-05-03 | Spouse       |
| 987654321 | Abner          | M    | 1932-02-29 | Spouse       |
| 123456789 | Michael        | M    | 1978-01-01 | Son          |
| 123456789 | Alice          | F    | 1978-12-31 | Daughter     |
| 123456789 | Elizabeth      | F    | 0000-00-00 | Spouse       |
| 444444400 | Johnny         | M    | 1997-04-04 | Son          |
| 444444400 | Tommy          | M    | 1999-06-07 | Son          |
| 444444401 | Chris          | M    | 1969-04-19 | Spouse       |
| 444444402 | Sam            | M    | 1964-02-14 | Spouse       |
+-----------+----------------+------+------------+--------------+
11 rows in set (0.00 sec)

mysql> exit

Back to Menu

Problem 3.19

a) The area code is missing from each entry.

b) Store the information locally in the Local_phone and Cell_phone columns. If you were to add another column called area code it may work, but what if the cell phone and local phone use different area codes. For instance if the student lives in a different state but has a cell phone number that is local to the university.

c) The major advantage of splitting the name up into First, Middle, Last is sort-ability. By splitting up the name you can sort the data by any part of a student name rather than just the student name as a whole. The only disadvantage to this approach over the full name approach is that you need to maintain three more columns of data rather than just one.

d) A general guideline for splitting up information or leaving it whole would depend on the data. If it is a persons name it should be split, if it is an address for instance it should be kept whole. Company names should also be left whole as well as department names. Any string of numbers that makes up an ID or phone number should also be left whole to prevent otherwise how would you be able to reliably determine the correct order?

e) To support multiple phones the first implementation I would suggest would be to have the Local & Cell phone columns as well as additional columns called phone3, phone4, and phone5 for each student to hold the extra information. Another implementation could use a single column called extra_phones that houses up to three additional phone numbers.

Back to Menu

Problem 4.12

a)

SELECT Name,Class,Major FROM STUDENT
       WHERE Major = 'CS' AND Class = 4;

b)

SELECT Course_number,Year,Instructor FROM SECTION
       WHERE Instructor = 'King' AND
       (Year = 07 OR Year = 08);

c)

SELECT count(*),s.Section_identifier,s.Course_number,s.Semester,s.Year FROM SECTION s
       JOIN GRADE_REPORT g ON s.Section_identifier = g.Section_identifier
       WHERE s.Section_identifier = g.Section_identifier AND s.Instructor = 'King'
       GROUP BY s.Section_identifier;

d)

SELECT p.Name,c.Course_name,c.Course_number,c.Credit_hours,s.Semester,s.Year,g.Grade
       FROM STUDENT p, COURSE c, SECTION s, GRADE_REPORT g
       WHERE p.Class = 4 AND p.Major = 'CS' AND p.Student_number = g.Student_number
       AND g.Section_identifier = s.Section_identifier
       AND s.Course_number = c.Course_number
       ORDER BY p.Name,c.Course_name;

Back to Menu

Homework 4:

Problem 5.6

a)

SELECT s.Name,s.Major FROM STUDENT s
   JOIN GRADE_REPORT g ON s.Student_number = g.Student_number
   WHERE g.Grade = 'A' GROUP BY s.Name;

b)

SELECT s.Name,s.Major FROM STUDENT s
   JOIN GRADE_REPORT g ON s.Student_number = g.Student_number
   WHERE g.Grade != 'A' GROUP BY s.Name;

Back to Menu

Problem 5.7

a)

SELECT f.Lname,f.Fname,f.Dno FROM employee f
    JOIN employee e ON f.Dno = e.Dno
    WHERE e.Salary = (SELECT MAX(s.Salary) FROM employee s)
    ORDER BY Lname asc;

Output:

+--------+--------+------+
| Lname  | Fname  | Dno  |
+--------+--------+------+
| Bacher | Red    |    8 |
| Bender | Bob    |    8 |
| Drew   | Naveen |    8 |
| Hall   | Sammy  |    8 |
| Head   | Arnold |    8 |
| Jarvis | Jill   |    8 |
| King   | Kate   |    8 |
| King   | Billie |    8 |
| King   | Ray    |    8 |
| Kramer | Jon    |    8 |
| Leslie | Lyle   |    8 |
| Pataki | Helga  |    8 |
| Reedy  | Carl   |    8 |
| Small  | Gerald |    8 |
+--------+--------+------+
14 rows in set (0.01 sec)

mysql>

b)

SELECT e.Lname,e.Fname FROM employee e
   JOIN employee s ON e.Superssn = s.Ssn
   WHERE s.Superssn = 888665555
   ORDER BY e.Lname asc;

Output:

+---------+--------+
| Lname   | Fname  |
+---------+--------+
| English | Joyce  |
| Jabbar  | Ahmad  |
| Narayan | Ramesh |
| Smith   | John   |
| Zelaya  | Alicia |
+---------+--------+
5 rows in set, 15 warnings (0.01 sec)

mysql>

c)

SELECT e.Fname,e.Lname FROM employee e
   WHERE e.Salary >= (SELECT MIN(s.Salary) FROM employee s) + 10000;

Output:

+----------+---------+
| Fname    | Lname   |
+----------+---------+
| Andy     | Vile    |
| Brad     | Knight  |
| Evan     | Wallis  |
| Josh     | Zell    |
| Jared    | James   |
| Justin   | Mark    |
| Jon      | Jones   |
| John     | James   |
| Alex     | Freed   |
| Ramesh   | Narayan |
| Jennifer | Wallace |
| Franklin | Wong    |
| James    | Borg    |
| Tom      | Brand   |
| Jenny    | Vos     |
| Chris    | Carter  |
| Kim      | Grace   |
| Jeff     | Chase   |
| Bonnie   | Bays    |
| Alec     | Best    |
| Sam      | Snedden |
| Nandita  | Ball    |
| Bob      | Bender  |
| Jill     | Jarvis  |
| Kate     | King    |
| Lyle     | Leslie  |
| Billie   | King    |
| Jon      | Kramer  |
| Ray      | King    |
| Sammy    | Hall    |
+----------+---------+
30 rows in set (0.00 sec)

mysql>

Back to Menu

Homework 6:

Problem 7.19

Requirements & constraints for 7.20 schema

  • An airport is a stationary entity that has an Airport_code, a city, a state, and a name associated with it. Many types of airplanes can land at a single airport.
  • A single plane can only arrive at or depart from a single airport during a flight_leg
  • A flight may have multiple segments of its flight leg that will have it arrive and depart from multiple airports during the leg.
  • An airplane has an id and a fixed number of seats. There may be many of the same types of planes, ie there may be 30 – 747′s but they may all belong to different companies.
  • A flight consists of an airline, a number, and a weekday. A flight may be straight through or it may have multiple legs. There may me many instances of the same flight_leg, each instance would be assigned to a different airplane or a different time slot.
  • The fare can change depending on restrictions. The fare is made up of a code, the amount, and the restrictions.
  • A seat is based on a customer reservation. There are many seats and many reservations, however each leg instance only has a fixed number of seats that it can allocate. Each seat is assigned to an airplane which has a total_no_of_seats that it can fill.

Back to Menu

Problem 8.29

View As PDF

Back to Menu

Homework 8:

Problem 17.41

This is a work in progress:

Database File (dbA.dat):

1;Hammer;5.99;38
2;Drill;28.99;12
3;Bits;1.99;22
4;Screwdriver;4.99;44
5;Torx;5.99;14
6;Bucket;1.99;44
7;Light;10.99;12
8;Tape Measure;5.99;32
9;Band Saw;129.99;5
10;Table Saw;159.99;3
11;Belt Sander;132.99;6
12;Chain Saw;169.99;3
13;Dolly;65.99;12
14;Rake;12.99;42
15;Shovel;10.99;33
16;Hoe;9.99;21
17;Hinge;2.99;21
18;Bolt;.99;101
19;Nut;.99;121
20;Screw;.25;322

Program:

#!/usr/bin/perl

#Ronny Bull
#CS541 - Database Systems
#Problem 17.41a
#This program reads in a database file with a single record on each line.
#Each record is separated into fields by a ";".  The database consists of
#hardware store items with record fields: index, name, price, inventory.

#open the file
open(FILE, "dbA.dat") || die "Could not open the file: $!\n";
@DB = <FILE>;

#get record counts
$recordcnt = $#DB;
$records = $recordcnt + 1;

#prompt for user input
print "\nThere are $records records in the database\n";
print "Which record would you like to access? (1-$records)\n";

#store user request
$access = <>;

#access the record $access-1
print "Accessing record $access\n";

#initialize counter
$cnt = 0;

#split each record into fields by ; separator
foreach $record (@DB) {
        chomp;
        ($index, $name, $price, $inventory) = split(/;/, $record);

        $cnt++;

        #print individual record fields
        if($cnt == $access) {

                print "-------------------\n\n";
                print "Part Number: $index \n";
                print "Part Name: $name \n";
                print "Price: $price \n";
                print "In Stock: $inventory \n";
                print "-------------------\n\n";
        }
}

#close the file
close(FILE);

Output:

There are 20 records in the database
Which record would you like to access? (1-20)
5
Accessing record 5

-------------------

Part Number: 5
Part Name: Torx
Price: 5.99
In Stock: 14

-------------------

Back to Menu