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
- 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).
- Complete Textbook problems 2.9 -- 2.15.
Complete the following tasks using SQLITE3
- Create a new folder YOURNAME_DBHW1 on your computer
- 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.
- 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.
- If you are using linux, sqlite3 should also be either pre-
or easy-to- install.
- Open a command line window (in Win7, enter 'cmd' into the
search pane of the start menu.
- Use CD commands to navigate to the folder that you created.
- Enter the following command: sqlite3 university.db
- Download the database creation scripts from the
book's website. You will need both the DDL and the SQL code
for creating small relations.
the command ".read FILENAME" leaving out the quotes and
replacing FILENAME first with the DDL program, and second with
the SQL code program.
- execute the command ".tables" to see all the
tables in the current database.
- Execute the simple statement: "select * from student" to
verify that the data was imported successfully.
- Issue the commands ".echo on" and then, ".output YOURNAME_OUT.TXT"
replacing YOURNAME with, of course, your own name.
- 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.
- 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.
STOP! Before you continue, read
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
ITEMS TO SUBMIT (Pack all files into a single file
- 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.
- 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
- 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.
- Find the names of all the instructors from the Biology
- Find the names of all the students whose names
are 6 letters or longer.
- Find the list of courses that start with 'Intro.'
all departments with a budget anywhere from $50,000 to $80,000,
- Find the names of courses in the Computer Science department
that have 3 credits.
- For the student with ID 12345 (or any other value), show all
course_id and title of all courses registered for by student.
- List the IDs of all the instructors who have taught a course
outside the computer science department.
- Same as above, but include the names of the instructors.
- Find a grade listing of students' ID, name and grade for
CSC-101 in Fall 2009.
- Find a listing of all students who have taken any Comp. Sci.
course ever (there should be no duplicate names)
- 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)
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
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
- Complete question 3.11 parts b,c and d
- 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.
- 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!):
- 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.
- 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.
- 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.
- Find all courses whose identifier starts with the string
- 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.
- Find a list of all courses that were offered in the same
fall-spring academic year, for any possible year in the
- 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.
- 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
Goals for this assignment : Practice using joins, views, and Integrity constraints and
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.
- Complete exercise 4.12
- Complete exercise 4.13
- 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:
- select * from takes right outer join student on
student.ID = takes.ID;
- select * from takes natural full outer join course;
- List 3 reasons that you might want to use a view in a
- Answer question 4.7
- Answer question 4.19
- 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:
- Repeat question 3.3 from Homework 3 above, but this time
use aggregation and a left outer join.
- Create a view faculty showing only the ID, name, and
department of instructors.
- 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
- 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.
- 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
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
- Prompts the user for a database file location for the
University database (5 Points)
- Opens the database (15 Points)
- Lists the contents of the Student table (20 Points)
- Prompts the user for a student ID value (5 Points)
- For the given student, display the following information in
a reader-friendly format:
- All courses the student has taken, ordered by semester
and year (10 Points)
- Contact information for the student's advisor (10
- The student's current GPA (20 Points)
- 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
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