LIS 7008 - Information Technologies
Fall 2010 - Section 01
Assignment 5

Note: This should be done using Microsoft Access for Windows, version 2000-2003, 2007, or 2010. Microsoft Office products are available from Tigerware through PAWS. If you do not have Microsoft Office, you can download and install OpenOffice from www.openoffice.org. OpenOffice (and MS products, PHP/MySQL/Perl) tutorials are available at inpics.net. Mac computer users should either use OpenOffice or find a Windows computer.

• The name of each student in the school.
• The title of each book in the library.
• Which students have which books charged out.
• The due date of each book that is charged out.

Enter the appropriate information for at least 10 students and 10 books into the database and arrange it so that exactly 5 of the books are charged out, 3 of them to the same student. At least two of your books must have the same title (but be different books), and at least two of your students must have the same name (yes, same first name, middle name, and last name! but, of course, be different students!).

Construct two queries. The first should show the title of each book that is charged out, the name of the student to whom it is charged out, and the due date. The second query should simply show the name of each student at the school.

Many of the things you will need to decide when designing your database have not been specified in this assignment. For example, the form of a student's name (e.g., first and last, nickname, middle initial, etc.) has not been specified. Nor has the maximum length of a book's title. You may make any reasonable decisions about things which are not specified in this assignment.

Please post your database (i.e., the tables and two queries in one database file) on your homework Web page (7008.html) by making a link from "Homework 5" to your database file, before class on the date that this assignment is due. Please make a note on your Web page which software - Microsoft Access 2000-2003, Microsoft Access 2007, OpenOffice, etc. - you use to create the database.

The basic steps for using Microsoft Access are addressed in the slides for this class. Regardless of the specific software you use to create a database, the basic steps are generally the same: create a database, create tables, define fields, create records by inputing data, create relationships, then create queries and run the queries. There are a lot of tutorials for Microsoft Access and OpenOffice on the Internet. See the syllabus page for some of the tutorials. I have not looked at any tutorials for OpenOffice yet, but you can find them for yourself.

FAQ:

• Q: I have Macs at home and at work, and therefore do not have MS Access, as they don't make it for Mac. What should I use?

  A: two options:

  • use a Windows machine at the SLIS lab or a library or anywhere.
  • learn to use OpenOffice, and bring your computer to my office for grading. Several students of earlier semesters did this successfully, but please plan to invest significant amount of time in learning OpenOffice unless you are technologically strong.

• Q: I do not have MS-Access on my Windows laptop.

  A: MS-Office is available from LSU Tigerware.

• Q: Every time I run a query it says "Type mismatch in query." I based my query off the one shown in the example database (RideFinder). How do I find what's wrong with my relationships/query to fix the problem?

  My answer: In order to remain data integrity, databases define the types of data. For instance, if the StudentID in Table1 is a "string" type, and the StudentID in Table2 is a "number" type, when you want to relate the two fields, the DBMS reports a "type mismatch" error. So to correct such a mistake, you need to go back to the structures of the both tables, and check the types of the two fields to make sure their types match.

  A student's answer: I think it had something to do with the automatic numbering. I changed the "Autonumbers" to "Numbers" and my query worked perfectly thereafter.

• Q: Every time I try to upload my database file I get an error message that says: "open for write: permission denied. File transfer failed." When I click the failed transfers tab at the bottom of the Filezilla screen, the reason column reads "Could not start transfer." I don't know what I'm doing wrong.

  A: There are two possibilities that causes the problem: (1) the server administrator did not assign a "write permission" to your account (which is very unlikely because you could upload your earlier homework; (2) very likely you dragged the database file to the wrong area on the Filezilla screen.

• Common problems:
  • Designating a wrong primary or foreign key. A primary key is a unique identifier of an entity (i.e., this table). A foreign key is a unique identifier of another entity (i.e., another table related to this table). For instance, StudentID is a primary key of the Student table; StudentName is not.
  • Duplicating a field (an attribute) in this table, which belongs to another table. For instance, some students assigned StudentName as an attribute of the Book table. One strength of RDBMS is to relate two tables to avoid the duplication of fields in the two tables. If you did not relate (link) your tables in the querying stage, you had made a mistake in your design.

Grading rubric:

• Note: please do not email me any database file as attachment. The LSU email system treats it as a virus. So please post your database on your course Web site no matter whether it works or not.
• +50: the tables
  • wrong PK/FK designation: -5
  • two students with same names not found: -5
  • two books with same titles not found: -5
  • duplication of fields in two tables (except foreign keys): -15.
    In other words, copyring columns across tables is a wrong thing to do.
• +50: the two queries (25 points each)
  • 1st query: retrieving students who have not charged out books, or not linking tables: -25
  • 2nd query: no problem found so far. :-)