Homeworks

HW# 1: Interview some users (or yourself if you wish) of database systems. Which DBMS feature do they find most userful and why? Which DBMS facilities do they find least useful and why? What do these users precieve to be the advantages and disadvantages of DBMS? Submit the document (preferably .txt file) with your answers.

HW# 2: Download and install ArgoUML (or any other UML modeling tool). You can find ArgoUML at argouml.tigris.org; it's a free UML modeling tool. For the homework, using ArgoUML (or any other UML modeling tool), create the "Employee", "WorksFor", "Company" model mentioned in class (and in "modeling" class notes). Submit the argouml (or any other modeling tool) project file.

HW# 3: Design a Bank. Build Use Cases, as well as a class diagram, database schema, etc., using ArgoUML (or any other tool, etc.). Consider functionality of an average bank (like depositing money, transfers, etc.). Look into ``Double Entry Accounting'', and use that in your bank. Put in as much detail as you think is appropriate. Submit the file created by UML tool, as well as any relevant documentation (how the bank would work, how it would talk to other banks, etc.)

HW# 4: For every entity from HW3, write a `create table' statement. Write a SQL statement for every operation on the database from HW3. (ie: creating accounts, updating accounts, etc.,). Save everything in a text file, and submit that.

HW# 5: Do `Sample Questions' at the end of: sql2.pdf

Also, for the same database, answer the following questions:

1) Find the company with most employees.

2) Find companies with above average salaries.

3) Find the company with most non-managing employees.

4) Find the company with highest average difference between manager salary and non-manager employee salary.

Write answers in text file, and upload that. (btw, I know some of these are ambiguous---base your answers on whichever interpretation you think is most logical).

HW# 6: Consider your homework #3. Normalize the database from HW3 to be in BCNF. What limitations present themselves as you normalize? List 10 good/bad points behind normalizing. Submit the new schema, as well as a .txt file with the answers/dicussion, etc.

HW# 7: Using the PHP notes and the ``Sample Guest Book'' (http://particle.homeip.net/gbook/) website, create a FORUM (or just the database schema and queries for one), similar to the one found on this website. Note, you don't need to provide for user logins.

The following tips are only if you're unable to do the forum yourself:

Tip 1: You only need 2 tables.

Tip 2: You can call tables ``Thread'' and ``Post''.

Tip 3: Thread would have id, name, subject, postdate (among other things you may think of).

Tip 4: Post would have id, threadid, name, subject, postdate, and message.

Tip 5: The code is very similar to the guest book; however, while in a guest book you only had 2 files, for a forum, you will need 4:
threads.php (view all threads)
threads_action.php (creates a thread)
thread.php (view an individual thread---all posts of that thread)
thread_action.php (reply to a particular thread)

Tip 6: Play around with this site's Forum to get ideas.

Tip 7: If still lost, e-mail me for Tip 8, 9, 10.

Note: You don't need to create the forum itself, just the database schema, and the SQL queries that will 1) insert a new post, 2) insert a reply, 3) query all threads, 4) query individual thread, 5) search posts (by subject, content, author), etc. You can save everything in dbcode.txt file and submit that.

You should only do the actual forum (with .php, or whichever) code if you're feeling upto it; I think it would be an interesting and valuable experience, but it's a bit outside the scope of this class.

HW# 8: Build a contacts book website. Users login, and can manage their contacts. You can either: Design the site (how it will work; write out stuff in Word; database schema, etc.), or actually build the site.

HW# 9: Knowing how a file system works (directories, files, etc.), design a database system to manage files (with directories, etc.,). ie: a database that can be used to store and work with files. Specify the database schema, how files will be stored, etc., and how various trade-offs of your approach.

HW# 10: Design a website to manage user's files. An internet hard-drive. Write queries, as well as explain how the site will function. I'm only looking for a document here. If feeling ambitious, implement the site.

HW# 11: Create a table that just has 1 number as a field. Write a stored procedure to populate that table with the first 10000 prime numbers. Prime numbers are those numbers that are only divisible by themselves and 1. This will essentially will give you some exercise with loops and if statements in a database of your choice.

© 2006, Particle