DBMS Lab.

Project Guidelines

A final demonstration of the projects will be scheduled on the week preceding endsem. Submitting a report is compulsory. Timeline for intermediate checkpoints will be set and evaluated.

Plagiarism will be strictly penalised. Please report any code, system, reports you have borrowed from the internet or elsewhere.


Project Ideas for Spring 2008

Presentation File


1. Indian Cricket League Auction Portal
2. Job/Resume Posting Service
3. Tourism Information System
4. Web Based Bilingual Dictionary
5. Financial Portfolio Manager
6. Train Reservation Management System
7. Sequence Database and Query System
8. Automated Time Table and Course Management System
9. Checking a SQL query for SQL injection vulnerability
10. MashUp
11. Spatial database systems
12. Data warehousing
13. Data mining
14. Multimedia databases
15. Text mining
16. Handling uncertain data
17. Concurrency control
18. Indexing
19. Stream data processing
20. TinyDB
21. XML
22. Object oriented databases
23. Query optimization





Project Ideas for Spring 2007

General Areas:

Information System Design
Transaction Management and Concurrency Control
Query Optimization
Distributed Databases
Object Oriented Databases
XML databases
Domain Specific Databases
Data Warehousing and Mining
Data Stream Processing
Mobile Databases
Multimedia Databases

Projects from any of (but not restricted to) the above areas may be proposed. Each group should present a project proposal and get it aproved by the instructors within a week. The proposal should include, (i) title, (ii) brief description, and (iii) list of features to be supported by the system. Instructors will set intermediate project goals with timeline. Some sample projects are described below.


Title: Blogging Site Design

Description: Dissatisfied with the level functionality provided by popular blogging websites like blogspot and myspace, you and your friends decide to build your own blogging website. You need to to build a servlet-based application, that will run on an Apache Tomcat server, which will connect to another machine running an Oracle database. The service your application will provide is managing and aggregating blog posts created by multiple registered users in the system, as well providing basic search capabilities.

Here is a basic outline of the project:

1. Build a servlet application that runs on Apache Tomcat or equivalent webserver. Tomcat allows for generation of HTML pages dynamically using JSP pages (HTML pages with embedded calls to Java servlets).

2. Develop user account and blog entry storage capabilities by designing a relational database. You must use the JDBC interface to connect to the Orcale database.

3. Develop routines for parsing of plain text and building an inverted index over multiple blog entries.

4. Users should be able to register for the system and create at least one blog and post blog entries. For extra credit: allow users to create multiple blogs from one account.

5. A blog entry consists of a title and body of text (no html formatting).

6. Each registered user should have the capability of creating multiple groups of ”friends” and add other registed users to one or many of those groups.

7. When posting an entry, a user might label it as ”public” (visible by everyone), or ”private” to a specified group, restricting the visibility of the entry to members of that group only.

8. An entry might be edited or deleted by the user who posted it.

9. All registered users should be able to leave ”reviews” of the blog entries they can read. A review consists of some text and a score on a 0 to 5 scale. For extra credit: implement a simple recommendation scheme based on the score. A recommendation should look like: ”Users who liked this entry also liked the following entries...”, while displaying links to some other entries which received high scores by users which gave high scores to this entry. For even more extra credit: allow a review to be edited or deleted by the user who posted it.

10. There must exist one administrator account, which will be used for moderation of the blogs. Using the administrator account, all user accounts, blog entries and reviews of entries might be edited or removed.

11. The system should have the capability of searching for and displaying blog entries containing multiple keywords. For extra credit: results should be ordered using their average score. For even more extra credit: extend searching to reviews as well.

12. The system should allow for the generation of an RSS feed containing the recent entries posted by members of a particular group of registered users, which can in turn be viewed using some external RSS aggregator.

13. A shiny user interface will likely help attract customers willing to pay millions of dollars for your system. However as honest students you realize that the functionality and correctness of the system are more important than its looks. Nonetheless, you must try to present a reasonable user interface, which is relatively easy to navigate through.



Title: eBay Sniper

Description:
The goal of this project is to build
an application which will be used to place a bid on an eBay auction in the final seconds of the auction.

One of the most frustrating aspects of the online auction community as a bidder is being outbid in the final seconds of an auction leaving one with no time to place a counter-bid. Another typical situation is when an auction a user would like to win is set to end while at an inconvenient time for a user to watch for these last second bids such as in the middle of the night or when he/she is at work. A final related problem is that of connection speed or type such as a user on a narrowband connection or who loses his/her connection at an inopportune time such as at the auction’s end.

The objective of this project is to increase an eBay buyer’s chances of winning an auction by allowing him/her to place his/her bid during the final moments of the auction automatically. This will solve the problem described above concerning the time of the auction’s end. In addition to an automatic system to perform the task, another main benefit of my application will be user security and ease of use. The proposed application will reside on the user’s personal computer which will yield the highest level of security as a tradeoff for the problem of connection speed. However, it will be possible for the application to account for available bandwidth and latency in order to place the bid at the optimal moment desired by the user. This leaves only the problem of loss of connection which will be traded off for security.

Bid timing is an exceptionally important in successful online bidding. Approximately 75 percent of final bids are submitted in the final 3 percent of an auction’s duration. This technique is commonly referred to as sniping and is considered legal by eBay’s terms of service, “Any bid placed before the auction ends is "legal" on eBay” . There are many services such as and which offer users a way in which to snipe auctions. The main problem with such a service is its lack of security. Conversely, the main advantage of such a system is that its connection is centralized and, therefore, the likelihood that it will lose connection to eBay is greatly decreased. Additionally, it is not necessary that your computer be running or connected to the internet to complete the task.

This project will implement an application in which will gather data from a user-defined eBay auction page and react accordingly. The program will take as input an eBay username and password, an eBay auction number, a time variable (most likely seconds) within which the bid will be placed, and the user’s maximum bid. The program will then retrieve the data specified by the auction number and continually update itself until the auction reaches the specified threshold at which point it will place the user’s maximum bid assuming that it is greater than the current bid.


References:
  1. “Sniping.” eBay Glossary. <http://pages.ebay.com/help/basics/g-sniping.html>.
  2. “Bidnapper.” 2005. Abercrombie Online. <http://www.bidnapper.com/>.
  3. “eBay Snipe, eBay Bidding Software, eBay Sniping – Hammertap.com”. <http://www.hammertap.com/powertool/>.


Title: Indian Railways on Google Earth

Description:

The goal of this project is to integrate any of the Map Web Services like Google Eart, Wikimapia, TerraServer with the Indian Railway Train Enquiry System at www.indianrailway.gov.in. Two type of queries should be supported:

(i) Users may specify source and destination on Map Servers. The system should return a page in indianrailways displaying a list of trains between the nearest railway stations to source and destinations.

(ii) Users may specify a train number or name, and the systems tracks the route of the train on the map server with schedule of arrival and departure at different stations.

Title: Evacuation Planning

Description:
Assume you have population figures for all railway stations in India and the carrying capacity of the trains. There are certain shelters in certain big cities having certain capacities. A natural calamity occurs in certain region (which can be marked out by user on the maps). You have to build an evacuation plan which will evacuate people from the effected region to the shelters in minimum time. You should make announcement to the people in the affected region to board certain trains at their scheduled departures.


Title: Video Search Engine

Description:
Build a content based video search engine.

Title: Distributed Database

Description:
Consider a situation where the tables of a schema are distributed over multiple machines. Implement a middle layer which will have a query processor to migrate queries. The results needs to be collated and presentd to the user. Also, implement optimization for the outer join query. Implement appropiate concurrency control strategies.

Title: Personalized Travel Agent

Description.
Given a travel plan the agent should look up appropiate web sites and book airplane tickets.

Title: eTrading Portal

Description:
Build an eTrading portal which allows investors to invest in stocks. Try to implement collaborative recommendation.
You may visit the site https://us.etrade.com

Title: Online Auction Portal

Description:
Build an online auction portal.

Title: Data Stream Processing for Network Data

Description:
Build a sophisticated network monitoring tool using streaming data algorithms. The Lawrence Berkeley Laboratory has some wide-area TCP traces available here. Scientific data such as meteorological measurements (see, e.g., NOAA) can also work, although the data rates tend to be quite slow (e.g., one measurement every ten minutes). You are also free to collect your own data by instrumenting computer equipment to obtain network traffic traces, video game control messages (great for spatial data), etc. Make sure to respect the privacy of any persons involved.

References:
http://infolab.stanford.edu/stream/

Title: Data Warehousing



Title: XML Data Integration


Title: Biological Database



Title: Data Mining

Description:
Here are a number of data mining challenges.

http://www.netflixprize.com/
http://www.cs.uic.edu/Netflix-KDD-Cup-2007
http://www.cs.ucr.edu/~eamonn/SIGKDD2007TimeSeries.html



Title: Mobile Data Management



Title: SMS based Health Monitoring System for Telemedicine




SQL Servers


10.3.19.113    MySQL
10.3.19.114    MSSQL
10.3.19.115    Oracle 9i

Install the respective client tools in your machines.

Login names: group1, ..., group25
Password: group1, ...., group25

Assignment 1.1

1. Consider the following set of requirements for a university database used to maintain student grade reports (transcripts).

A. The university records each student's name, social security number (SSN), address, phone, birthdate, major department, and degree program (B.A., B.S., etc.). An SSN uniquely designates a student.

B. Each department is described by a name, department code, office number, office phone, and college. Name and code values each uniquely characterize a department.

C. Each course has a course name, description, course number, number of quarter hours credit, and offering department. The combination of a course number and an offering department uniquely designates a course.

D. Each section has an instructor, semester, year, course and section number. The section number distinguishes different sections of the same course that are taught during the same quarter and year.

E. A grade report has a student, section, and grade (A,B,C, and D). The combination of a student and section uniquely characterizes a grade report.


Draw the E-R diagram. Create the database in systems like Oracle, MySQL, MSSQL. Compare the features of each of these systems.

Frame and execute the SQL queries for the following:

1. List name of all students who have registered for courses in more than one department.

2. List name and SSN of all students who have registered for more than one course in a specified department.

3. List name of all students who have got atleast B grade in all the courses taken by him/her.

4. List name of all students who have got A in all the courses offered by CS department.

5. List the highest grade obtained in each course by students majoring in each of the departments.


Assignment 1.2

Write a C program which connects to the SQL servers using ODBCstandard and prints (formatted) the result of the queries in assignment 1.1.

Repeat this in Java using JDBC.


Assignment 2

Online Book Store

Design a Database System for an online book store (e.g., Amazon), based on the following specifications.

1. Books are represented by, ISBN, Title, Author, Publisher, Edition, Year of Publication, Price, Short Reviews if available, Table of Contents if available., an image of the book cover, category e.g., computer science -> operating systems -> MacOS

2. Customers will use an web based interface to browse books based on categories, search books using keywords. Initially only the title and author of the book(s) are displayed, on click other attributes are displayed. Customers can buy books using their e-purse. The store also displays the number of copies of the book left in stock. Out of stock books cannot be purchased immediately, but can be ordered.

3. Customers create accounts in the book store. Each account contains customer profile informations: name, age, geographical location, categories of interest, email. Each account has an e-purse. Customers can specify the amount of money to be deposited with the e-purse. Profile and e-purse informations can be updated by the customer. Customers will login to the book store using an account name and password.

4. All online sales data are recorded in the database with timestamp.

5. Owner of the bookstore can give requisition for buying of books to publishers based on the amount of stock remaining. For each book the owner maintains a stock which is atleast the number of copies of the book sold over last 3 months. Books ordered by some customer are immediately requisitioned. Requistions are placed in a requisition table. The publishers inspect the table on the 1st of every month and immediately supplies the books. Once a book is supplied it is cleared from the requistion table.

Design tables for the above system. Create a separate view for customers. Design suitable forms. Implement authorizations.  Store the functions and procedures necessary in the database itself. You may also use other languages, besides SQL. The front end may be designed using Java/PHP or any other suitable language.

Extra Credits

1. Recommendation systems: Display to the customers a list of books which might be of interest. This may be done based on user profile, or in a collaborative manner - "people who have bought this book have also bought".

2. Send emails to customers when, the ordered book has arrived, new books of interest have arrived in the store.