SLIS A-Z Index
People Search
SLIS Calendar

Programs

Courses

Textbooks by Semester

Course Web Pages - Spring 2012 - LIBR 242-01/10 Greensheet - Assignments

LIBR 242
Database Management
Assignments

Dr. Geoffrey Z. Liu
E-mail


Course Links
Greensheet
Course Calendar
Group Project
Individual Assignment
Resources
D2L
D2L Tutorial
SLIS e-Bookstore
LIBR 242 Resources
Online Resources
Bibliography
Sample Codes

Group Project | Case Scenarios [1] [2] [3] [4] [5] [6] [7]

Individual Assignment

After the first class meeting, now you know to some extent that this course is not about full text databases, neither is it about bibliographical or OPAC databases. It is about non-text entity databases, the kind of databases that are used to support operations such as airline reservation, online retailing, patient record management in hospitals, and student record administration etc.

Find such a system on the World Wide Web, Avis Car Rental (http://www.avis.com/AvisWeb/home/AvisHome) for instance. (I know there are tons of them out there, so don't come back to me and say you can not find any.)  Carefully examine the web interface of the system of your choice, and do couple searches on the system to experiment with it. Then, write a report of analysis, or rather description and speculation, of the system. Your report should be no more than five pages and should cover the following aspects:

Try to make your best guess using whatever you know, and don't be afraid of making mistakes.

Group Project

The group project is essentially a series of exercises in various stages of designing and implementing a database application for a business scenario of your choice, as outlined below. At each stage, students work on well-defined tasks, produce and submit specific products by the deadlines posted in the course schedule. Each project group needs to turn in only one package for grading. All works should be word processed. Graphics and tables should be created on computer and submitted in hardcopy.

Case Scenarios for the Group Project

Case Scenario #1: The Shipping Enterprise Inc.

The Shipping Enterprise, Inc. company is in the business of coastal shipping in the United  States. Below is a brief description of the company's business operation.

Along the coast of the US are several port cities each of which have facilities for the  loading and unloading of cargo on to ships. There are many different types of cargo (Oil, Grain, Toilet Paper), and many different types of ships (Tankers, General Cargo,  Containerized Cargo). Some ships can carry only one type of cargo (Tankers), while others can carry many different types (Freighters). Some ships can carry only one cargo at a time, while others can carry many different cargoes simultaneously. Each cargo has an owner, and a destination and an origin.

Ships travel up and down the coast loading and unloading all or some of their cargo at the various ports they visit. All ships have names, and countries of registry. They also have captains, officers and crew who serve aboard them. These are people who receive a salary, have names, and also have health insurance policies that cover them, and their dependents.

Unfortunately ships are also sometimes involved in accidents when entering or leaving a harbor (we will ignore accidents that take place at sea). When these occur a government investigator is assigned to write a report on the accident, what ship(s) it involved, where it took place, and what cargo was involved.

Case Scenario #2: The Wuhan University Travel Center

Wuhan University is a major institution of higher education in Hubei Province, P.R. China. Since individuals there do not own vehicles, the University has acquired a large number of vehicles to be used for official business. The University Travel Center (UTC) is responsible for managing these vehicles and making travel arrangements for all departments. A brief description of the University Travel Center's operation follows.

Faculty members may use the University-owned vehicles for officially sanctioned travel. For example, its vehicles may be used by faculty members to travel to off-campus learning centers, to travel to locations at which research papers are presented, to transport students to officially sanctioned locations, and to travel for public service purposes. The vehicles used for such purposes are managed by the UTC.

Using reservation forms with department chair's signature, each department may reserve vehicles for its faculty, who are responsible for filling out the appropriate trip completion form at the end of each trip. The reservation form includes the expected departure date, vehicle type required, destination, and the name of the authorized faculty member. When the faculty member arrives to pick up the vehicle, (s)he must sign a check-out form to log the vehicle out and pick up a trip completion form. (The UTC employee who releases the vehicle for use also signs the check-out form.) The faculty member's trip completion form includes the faculty member's identification code, the vehicle's identification, the odometer readings at the start and end of the trip, maintenance complaints (if any), gallons of fuel purchased (if any), and the UTC credit card used to pay for the fuel. If fuel has been purchased, the credit card receipt must be stapled to the trip completion form. Upon receipt of the faculty trip completion form, the faculty member's department is billed at a mileage rate based on the vehicle type (sedan, station wagon, panel truck, minivan, minibus) used. HINT: Do NOT use more entities than are necessary. Remember the difference between attributes and entities!

All vehicle maintenance is performed by UTC. Each time a vehicle requires maintenance, a maintenance log entry is completed on a prenumbered maintenance log form. The maintenance log form includes the vehicle identification, a brief description of the type of maintenance required, the initial log entry date, the date on which the maintenance was completed, and the identification of the mechanic who released the vehicle back into service. (Only mechanics who have an inspection authorization may release the vehicle back into service).

As soon as the log form has been initiated, the log form number is transferred to a maintenance detail form. The log form number is also forwarded to the parts department manager, who fills out a parts usage form on which the maintenance log number is recorded. The maintenance detail form contains separate lines for each maintenance item performed, the parts used, and the identification of the mechanic who performed the maintenance item. When all the maintenance items have been completed, the maintenance detail form is dated and stapled to the maintenance log form, and the mechanic who releases the vehicle back to service signs the form. The stapled forms are then filed, to be used later as the source for various maintenance reports.

UTC maintains a parts inventory, including oil, oil filters, air filters, belts of various types, and so on. The parts inventory is monitored daily to monitor parts usage and to reorder parts that reach the "minimum quantity on hand" level. To track parts usage, the parts manager requires each mechanic to sign out the parts that are used to perform each vehicle's maintenance; the parts manager records the maintenance log number under which the part is used.

Each month, UTC issues a set of reports. These reports include mileage driven by vehicle, by department, and by faculty members within the department. In addition, various revenue reports are generated by vehicle and department. A detailed parts usage report is also filed each month. Finally, a vehicle maintenance summary is produced.

Case Scenario #3: Management of the Climax Experience

In Northern California is a place called Silicon Valley, and in that valley is a university called Tiny University. The university has a graduate school called Twenty-First Century Information School, and the school runs a program called Virtual Library Program. Every student in that program is required to pass a final exam to finish their degree, and that final exam is called "the climax experience". The experience is a really complicated business, and the director of the school wants a database to help managing it. Here is a brief summary of what you may find out after interviewing the faculty.

The climax experience exam is offered twice a year, once in the spring and once in the fall. Students are required to write and pass two essays to survive the climax experience, and they may have two but no more than two attempts at the exam. The faculty are considering to let students have the experience as many times as they want in the future, but have not made up their mind yet. The rule of the game is still no more than two attempts at this time.

If students pass two essays in their first try, everyone is happy. If a student passes only one essay or none, the student can have a second try within a year to pass one or two essays, depending on how it turned out last time. If a student passed none last time, the student can turn in two this time. If a student passed one last time, the student can only turn in one. If a student passes two essays after the second try, everyone is still happy. If a student passes none or only one essay after having exhausted his or her two attempts, this student is kicked out of the program and may come after the School with a law suit.

At the beginning of each semester, students come to the School Office to sign up for the exam and provide their current mailing addresses. The office checks on their academic status to make sure that they are qualified to take the exam. Being qualified means that 1) the student has earned enough credits by taking required and selective courses, and 2) that the student has cleared all the Incompletes. The office maintains a list of all the students who are currently taking the exam, and for each student it keeps track of whether it's a first or second try and whether he or she passed anything last time if it's a second try.

The climax experience is coordinated by a faculty member, and the faculty take turns to be the coordinator. At the beginning of each semester, the coordinator goes after the faculty to collect questions for the exam, and the resulting questions, called "topics", are mailed together with instructional materials to the participating students. The topics are also posted on the School's website for easy access.

Students may choose any one or two of the posted topics to work on. (Yes, they may choose to work on only one topic even if they need to pass two). The students have three months to finish their essays, and when the deadline comes up, they turn in their works to the School Office. If a student can not finish the essay(s), he or she has to notify the coordinator 72 hours prior to the deadline to cancel his or her attempt. Otherwise, it will be automatically counted as a try and a failure. When students come to submit their papers, the office makes each of them to sign on a submission form. The form includes items such as student name, student social security number, total number of essays turned in, topic number, date and time of submission, name of the person in office who handled the transaction, and serial number of the receipt issued for the submission.

Every submitted essay is assigned a unique code number. Cover pages of student papers are stripped off, and the code number is the only way to trace which essay is written by whom. The coordinator mixes all of the papers into one big pile and then randomly assigns to individual faculty members for grading. This is done to make the grading a double blinded process, which means that no one knows who is grading whose paper except for the coordinator.

If a paper is determined to be adequate to pass, then it's done. Otherwise, the paper is returned to the coordinator with grading comments, and the coordinator randomly assigns the paper to another faculty member for second reading. If the second reader says it fails, it fails. If the second reader says it passes, it passes. Simple like that. Needless to say that the coordinator needs to keep track of all of the routings to make sure the faculty members do their job properly.

Keep in mind that all kinds of statistics about the climax experience are collected and reported at faculty meetings throughout the semester, so that the director and the faculty can stay informed of what is going on.

Case Scenario #4: The Happy Face Doll Home Business

Rosemary Goldernburg in Santa Barbara has been running a home business of making hand-tailored Happy Face dolls for years. As most home business owners do, she did not employ anyone. Occasionally, things would get really busy and she would nag her husband into working as a hourly-paid temp after he comes back from his office. The business started small and she was able to manage everything on a notepad. Last year she created a website to advertise and sell her products on the Internet, and the business has been booming ever since. It didn't take long for her to realize that her business could no longer fit into a notepad and that she needed a computer system to help managing the business more efficiently. She thought about getting a computer consulting company to build the system at one time, but the price they offered was too ridiculous to take.

Several days before the Christmas, Kiyomi, a relative of Rosemary's next-door neighbor, stopped by to pick up her Happy Face doll that she ordered for her niece Jennifer. While doing some final touch on the doll, Rosemary chatted with Kiyomi and learned that she is currently working on her MLIS degree at San Jose State University. Soon they started talking about how awful a student's life is and what students are learning at school nowadays. Kiyomi casually mentioned that she planned to take a database management course from a certain Dr. Liu next semester which requires every student to do a database project, and Rosemary instantly saw what she wants. They quickly made a deal. Kiyomi will design and implement a database system for Rosemary's doll business. In return, Rosemary offered Kiyomi the doll for free and promised to pay her five hundred bucks as a token of appreciation upon her completion of the project. Since Kiyomi had to rush to the post office right away, they agreed to discuss the details some other day.

One day in later January, Rosemary invited Kiyomi over for some tea in the evening, and Kiyomi showed up right after dinner, the two sat down to enjoy some fine Chinese green tea prepared with a special kind of tea pot bought by one of Rosemary's friends in Beijing. After briefly commenting on how the tea smelled and tasted differently from the regular kind of stuff, Rosemary turned the conversation to her business operation.

"My Happy Face doll business is really simple," Rosemary started.  "Hold on second please, I need to write it down." Kiyomi fished out a pen and a notepad from her purse and started taking notes furiously.

1. Customers phone in or email through the website to place an order. At the initial contact, they are asked to give out their name, mailing address, email, telephone number, email address if they have one, and when they want the doll(s) to be delivered. (Of course, Rosemary charges them more if they want to have the doll(s) sooner than regular delivery schedule). The date of order is also recorded for later reference.

2. Customers may come to Rosemary's house to pick up the dolls in person, or they can have dolls delivered through regular or express mail. To make her business more appealing, Rosemary also offers to send the doll directly to the gift recipient with professional wrapping free of charge. So, the billing address and delivery address may be different.

3. For each order, customers are asked to specify the quantity (of dolls of the same design), size preferred, general style, gender, hair color, hair style, eye color, complexion, dress color, type and quality of material for garment, shoe style and color, and finally, the name and birthday for the doll.

4. To make the doll more personal, Rosemary also needs to know for whom the doll is ordered, age of the child (if it is a child), gender, and information on the child's appearance if the doll is to be modeled after the child.

5. To get discounts and wholesale price, Rosemary orders materials in bundles from a number of different sources. Some are directly ordered from manufacturers, such as eye balls, faces, shoes, ..., and some are purchased from special shops. For each kind of material, she keeps a file of as many providers as she knows, listing their current prices, discount rates, quality ratings etc., so that she can always shop around for better deal.

6. Rosemary is a smart woman and is always looking for a better way to do things. Last summer, she "re-engineered" her doll-making process. It used to be that she would not start working on a new doll until she was completely done with the one in process, and everything was done on the same table. Now the process is different. Instead of doing everything on one single table, she now uses six tables, each dedicated to one special thing. First, she sorts all of the orders according to the delivery dates. Then she takes all the orders due in the same week as one batch, and push them through the production process altogether, step by step. An image is created on computer for each doll to be made according to the specification. The image is printed to a card with order number and other relevant information. The card is tied to a doll frame and will move along from stage to stage as the doll acquires its shape. Then she moves everything to next table to work on the bodies, then to next table to work on the heads, so on and on, until she is done with the final stage. Then all of the newly-made dolls are "personalized" by attaching a name tag, wrapped professionally, and ready to deliver.

7. Once the dolls are done, the corresponding entries of orders are transferred from the work list to the delivery list. An invoice is printed out for each order and packed with the doll if they are to be sent to the same person.

8. Rosemary does her accounting in Win95 Quicken. She is pretty happy about that, and Kiyomi was told not to worry about this part.

Case Scenario #5: The Automata Inc.

Automata Inc. produces specialty vehicles by contract. The company operates several departments, each one of which builds a particular vehicle, such as a limousine, a truck, a van, or an RV. Incoming orders are handles by the Customer Service Department. Transactions of delivery and payment are also done by this department. Once an order is processed and registered, it  is dispatched to a construction department based on the vehicle type. The Customer Service Department keeps customer- and order-related information and also maintains a file of which vehicle is being built by which department.

Each construction department maintains its own job list in order of incoming sequence. Although the jobs are processed one by one, there are often several vehicles being built in the production line, each at a different stage. The department keeps a progress log on each vehicle. Technicians will sign on the log after they are done with their parts on one vehicle and then move on to the next. Once a vehicle is finished, it is turned over to the Customer Service Department together with all the records.

When a new vehicle is built, the construction department places an order with the purchasing department to request specific components. The order received by the purchasing department can contain several different items. An inventory is maintained so that the most frequently requested items are delivered almost immediately. When an order comes in, it is checked to determine whether the requested item is in inventory. If an item is not in inventory, it must be ordered from a  supplier. Each item may have several suppliers.

The personnel and accounting departments already have their database systems to keep things going and they are not considering to upgrade their systems any time soon.

Case Scenario #6: James and His Baseball Card Collection

James, a 12-year old boy living next door, has a passion for baseball games.  I have never seen him actually playing baseball but I do know that he collects baseball cards. He started collecting baseball cards at the age of three when his uncle gave him a bunch of baseball cards as Christmas gift, and he has developed quite a collection during the past years. Every time we ran into each other, he bragged about his baseball card collection and wanted to show me, but I never had time for it.

Yesterday we ran into each other again. This time James did not invite me to see his collection. James looked really upset and depressed. I asked why and found out that the culprit was a baseball card. About a week ago he traded a card with a kid at school, thinking the player was lousy and the card wasn't going to worth much, but only to find out later that the player hit couple home runs in a recent game and is now rated as a player with great potential. I told him it's no use to cry over spilt milk and he ought to think about how to avoid making the same mistake again. We ended up brainstorming on how to solve the problem and decided that keeping records of games and scores of players would help, and this is better to be done with a computer database system. James does have a home computer with Personal Oracle 8.0 installed, but the only problem is that he doesn't know how to use it to do such a fancy thing.

So, as a kind and generous citizen, would you help James please?

James wants to keep track of everything about all baseball teams in this country, games they have played, and players on each team. For each team, he wants to know what state/university it represents, who is the coach, how many games it has won and how many it has lost. For each game, he wants to know who is playing against whom, where and when the game is played, who won and who lost, and what are the final scores on each side. For each player, he wants to know which team he is on, what is his name, how old he is, what is his counts of strikes and home runs. He also wants to know if a player is married or not, and if married, whether he has any kids and how many, figuring that the wives and kids may take some energy away from baseball players and make their field performance suffer. What else? I can't think up more at this moment. But if you think any other information may help James better manage his baseball card collection and trading, please feel free to include it in your design.

Case Scenario #7: The Learning Land

A university’s center of instructional technologies wants to develop a web-based teaching tool, called “Learning Land”, that helps students master detailed materials by a method similar to flash cards.  Students will be able to enroll in one or more course sections, where courses are defined as subjects (Spanish III, French I, Calculus IV) and course sections are instances of courses.  Instructors will also be able to enroll in one or more course sections, though not in sections they teach.  An instructor will be able to teach more than one section, and more than one instructor can teach a single section.  Both students and instructors would have a profile which includes personal information such as name, address, ID number and e-mail address.

Each course will have a set of flash cards associated with it and within each course the flash cards will have multiple levels of difficulty.  A student must master the flash cards for a particular level before they will be able to advance to the next level.  When a flash card is displayed to the student, the student will have a specified amount of time to examine the flash card and to type in a response to the card.  Their responses will be recorded and the website will track whether the student's response was correct or incorrect, maintaining the last 5 responses of the student for each card, discarding older responses.  Once a student has mastered a difficulty level and has advanced to the next level of flash cards, he or she will have the ability to return to previous difficulty levels in order to review those flash cards.  In this way, the site assists the students in the learning processes by teaching both new information and reinforcing what has already been taught.  A student will accumulate scores for each level which will be calculated when the student reaches the end of the course for a final overall grade.

Students will be able to log into the site and their progress can be tracked over multiple sessions.  Instructors and their teaching assistants will be able to log in to check on each student's progress throughout an individual course.  Instructors will only be able to see the activity of the students enrolled in the course sections they are teaching or have taught.  The site will track which courses the student is enrolled in and what level they have achieved based on the number of correct responses to the flash cards. Each time the student logs in, their session information will be stored, including the date and time they logged in and logged out, as well as the number of flash cards the student has reviewed and the number they got correct.