CSC 280 Introduction to Database Management Systems

Dr. Adam Anthony Fall 2012

Homework Assignments

Homework 1

Goals for this assignment: Develop a familiarity with the relational model, thinking through various motivations for why the relational model is used, and how it works.  Complete a brief tutorial in using the SQLITE3 command line tool.

ITEMS TO SUBMIT (Pack all files into a single file YOURNAME.zip):

  • A copy of written answers in .pdf format.  I recommend developing the entire solution in word and then selecting .pdf for the file type in the 'save as' dialog box.  
  • The University.db file you will create.

Solutions should be typed, and submitted in .pdf format only.  Diagrams are best when made in powerpoint and copy/pasted into Word (word diagram tools just don't work as well). 

  1. Complete Textbook problems 2.9 -- 2.15.
  2. Complete the following tasks using SQLITE3

  1. Create a new folder YOURNAME_DBHW1 on your computer
  2. If you are using microsoft windows, Download the sqlite3 command line executable, and extract the sqlite3.exe file to the folder you created in step 1.
  3. If you are using a mac, the sqlite3 program should already be installed, but if it is not find and install the developer tools from your installation DVD.
  4. If you are using linux, sqlite3 should also be either pre- or easy-to- install.
  5. Open a command line window (in Win7, enter 'cmd' into the search pane of the start menu.
  6. Use CD commands to navigate to the folder that you created. 
  7. Enter the following command: sqlite3 university.db
  8. Download the database creation scripts from the book's website. You will need both the DDL and the SQL code for creating small relations. 
  9. Use the command  ".read FILENAME" leaving out the quotes and replacing FILENAME first with the DDL program, and second with the SQL code program. 
  10. execute the command ".tables" to see all the tables in the current database. 
  11. Execute the simple statement: "select * from student" to verify that the data was imported successfully. 
  12. Issue the commands ".echo on" and then,  ".output YOURNAME_OUT.TXT"   replacing YOURNAME with, of course, your own name. 
  13. For each table in the database, execute the simple query: "select * from TABLE;"  Since output is going to a file, you won't see the results in the window.   Confirm that each command's output shows up in the text file. 
  14. When you are finished, upload the .pdf for the written answers above, the university.db file, and the output text file from step 12 in the Homework 1 submission area on Blackbaord. 

Homework 2

STOP!  Before you continue, read this really really useful document about the windows command line program and SQLITE3. 

This homework builds off of Homework 1, so if you had trouble with that assignment, contact Dr. Anthony for assistance in obtaining a correct solution before you continue. 

Goals for this assignment: To develop comfort with the basic form of an SQL query and to learn the basics of SQL DDL commands. 

ITEMS TO SUBMIT (Pack all files into a single file YOURNAME.zip):

  • Plain-text file, UniversityQueriesHW2.sql that contains the 6 queries below for the University database.  I should be able to copy/paste the queries into the command line window to test them for correctness. 
  • Plain-text file, AutoInsuranceDDL.sql that contains the DDL commands from exercise 3.13.

 

  1. Complete exercise 3.13 in the text book, making sure to test your solution in SQLITE3, since that's what I'll be doing to grade it.
  2. Using your University.db from Homework 1, complete the following queries, submitting the queries as a plain text .sql file on the homework 2 submission link in Blackboard.  Please be advised that there are no empty results in this list--if you imported the data correctly in homework 1, then all of these queries will have at least one line of output.
    1. Find the names of all the instructors from the Biology department.
    2. Find the names of all the students whose names are 6 letters or longer.
    3. Find the list of courses that start with 'Intro.'
    4. List all departments with a budget anywhere from $50,000 to $80,000, inclusive. 
    5. Find the names of courses in the Computer Science department that have 3 credits. 
    6. For the student with ID 12345 (or any other value), show all course_id and title of all courses registered for by student.
    7. List the IDs of all the instructors who have taught a course outside the computer science department. 
    8. Same as above, but include the names of the instructors. 
    9. Find a grade listing of students' ID, name and grade for CSC-101 in Fall 2009.
    10. Find a listing of all students who have taken any Comp. Sci. course ever (there should be no duplicate names)
    11. List all course info for courses that have been taught in an even numbered year.  (DO NOT CHEAT THIS QUERY!! MY GRADING DATABASE MAY HAVE ANY POSSIBLE YEAR PAST PRESENT OR FUTURE)

   

Homework 3

Goals for this assignment: To develop an intermediate-to-advanced level of proficiency in SQL query development, including concepts in aggregation, nested queries and set operations.  You will also learn the basics of data modification--inserts, updates, and deletes. 

ITEMS TO SUBMIT:

  • UniversityQueriesHW3.sql for the university database queries. 

In this assignment, you will be using the University.db file you created in homework 1 (copy it since you'll be adding and deleting data).  Again, contact me if you had problems with previous homework assignments so that I can help you get the correct data files configured.

When SQL gets complex enough, you will always find that one system or another requires you to solve a problem differently.  Here is a list of differences between the SQL in the book and the SQL that SQLITE recognizes.  However, be assured that I have already written the solution key for this assignment, so ALL of these queries can be done using the particular dialect of SQLITE3. 

University Database Queries

  1. Complete question 3.11 parts b,c and d
  2. Complete all queries in question 3.12 .  If the statement you write seems correct but fails, provide a comment to explain why it fails and give an updated query that does work.  Hint: use the .schema command to view all the information about a table and its columns.
  3. Complete the following supplemental queries (some of these may come back empty if you do them right!  Others will come back empty if you do them wrong!):
    1. Find the enrollment numbers for all sections, considering only sections that had some enrollment, don't worry about those that had no students taking that section.
    2. Find all sections that have the maximum enrollment from #1.  Use a subquery, so that this query will always be right even if the max enrollment changes. 
    3. As in in Q1, but now also include sections with no students taking them; the enrollment for such sections should be treated as 0.  If no courses with 0 enrollees exist, modify the database so that there will be one. 
    4. Find all courses whose identifier starts with the string "CS-1"
    5. Find instructors who have taught all the above courses (there is more than one way to do this, but the NOT EXISTS...EXCEPT approach in the book would work nicely. 
    6. Find a list of all courses that were offered in the same fall-spring academic year, for any possible year in the database. 
    7. Find a listing of course_id, semester and year values for all past offerings of any course that is being taught in the Spring 2010 semester. 
    8. Some of you may have noticed that the tot_creds value for students did not match the credits from courses they have taken. Write and execute query to update tot_creds based on the credits passed, to bring the database back to consistency. Note that none of the students' tot_creds should be null.  Anywhere you might see a null should be set to 0.  For full points, you need to do this in a single query. 

Homework 4

Goals for this assignment : Practice using joins, views, and Integrity constraints and data types

Deliverables (packed in a single zip file):

  • Word file with solutions for items 1--6.
  • Text file UniversityQueriesHW4.sql for item 7. 
  • Your modified University.db file with data to make queries come up with results. 
  1. Complete exercise 4.12
  2. Complete exercise 4.13
  3. Oh, No!  SQLITE only does left outer joins, and not right- or full-outer-joins!.  Re-write the following queries using only LEFT OUTER JOIN syntax:
    1. select * from takes right outer join student on student.ID = takes.ID;
    2. select * from takes natural full outer join course;
  4. List 3 reasons that you might want to use a view in a database system.
  5. Answer question 4.7
  6. Answer question 4.19
  7. Complete the following queries on the University database.  If any of them come up empty, add data to your database to return a non-empty result:
    1. Repeat question 3.3 from Homework 3 above, but this time use aggregation and a left outer join. 
    2. Create a view faculty showing only the ID, name, and department of instructors.
    3. The university rules allow an F grade to be overridden by any pass grade (A, B, C, D). Now, create a view that lists information about all fail grades that have not been overridden (the view should contain all attributes from the takes relation).
    4. Find all students who have 2 or more non-overridden F grades as per the takes relation, and list them along with the F grades.
    5. Find all rooms that have been assigned to more than one section at the same time. Display the rooms along with the assigned sections; I suggest you use a view to simplify this query.

Homework 5 -- Programming with SQLITE

Use the SQLITE C API as covered in class to create a C++ console application in visual studio that does the following, using the University database. 

  1. Prompts the user for a database file location for the University database (5 Points)
  2. Opens the database (15 Points)
  3. Lists the contents of the Student table (20 Points)
  4. Prompts the user for a student ID value (5 Points)
  5. For the given student, display the following information in a reader-friendly format:
    1. All courses the student has taken, ordered by semester and year (10 Points)
    2. Contact information for the student's advisor (10 Points)
    3. The student's current GPA (20 Points)
  6. The screen for #5 should also prompt the user to quit, or return to the student list.  (5 Points)

Your program should safely handle all errors that might arise through the use of a database, as covered in class.  It should also use a parameterized query when handling the user's input of the student ID. 

Homework 6 -- ER Diagrams

Complete the following exercises from the text book: 7.2, 7.18,7.19, 7.20, 7.22

There is a lot of diagramming in this homework.  There are no free tools available that make drawing these diagrams easy.  Powerpoint is your best bet.  If you wish to NEATLY hand-draw your diagrams, you may turn them in on the due date in class, or scan them and submit online.  Diagrams that are messy and hard to read will receive point deductions.

 

Homework 7 -- Functional Dependencies

Complete the following exercises from the text book:  8.22, 8.28, 8.29, 8.31

 

 

 

Dr. Adam Anthony

Adam P. Anthony,
Assistant Professor
of Computer Science

Baldwin Wallace University
Department of Mathematics and
Computer Science
275 Eastland Rd.
Berea, OH 44017

    Office: MaCs 160
    Phone: (440) 826-2059 
    Email: apanthon@bw.edu

Office Hours (Spring 2013):
    T-TH 2:30 -- 5:00 PM