Database design question

Posted by Lijo on Stack Overflow See other posts from Stack Overflow or by Lijo
Published on 2010-04-28T05:39:58Z Indexed on 2010/04/28 5:43 UTC
Read the original article Hit count: 208

Filed under:

rdbms

Hi Team,

I have an interesting database design problem that I formulated while travelling by a bus, coming back from my home.

Design a normalized database for a bus ticketing system (not reservation system). In each trip, the conductor of the bus will give tickets to its passengers after collecting fare from them. Passengers travel from a various source places to various destination places.

The system must be able to give a report of the places for which the number of passengers was more than 2.

Suppose the stops for the bus are L1,L2, L3 and L4 Suppose passenger P1 travels from L1 to L4. P2 travels from L2 to L4. P3 travels from L3 to L4.

The report should list only (L3-L4) for which it has more than 2 travelers.

Can you please help me to solve the following problems

1) Design a normalized database

2) Write a query for the report

3) Is there any site that gives these kinds of interesting database design questions and answers?

Thanks

Lijo

Related posts about rdbms

Studying MySQL, SQLite source code to learn about RDBMS implementation

as seen on Stack Overflow - Search for 'Stack Overflow'
I know implementing database is a huge topic, but I want to have a basic understanding of how database systems work (e.g. memory management, binary tree, transaction, sql parsing, multi-threading, partitions, etc) by investigating the source code of the database. Since there are a few already proven… >>> More
Many-to-many relations in RDBMS databases

as seen on Stack Overflow - Search for 'Stack Overflow'
What is the best way of handling many-to-many relations in a RDBMS database like mySQL? Have tried using a pivot table to keep track of the relationships, but it leads to either one of the following: Normalization gets left behind Columns that is empty or null What approach have you taken in… >>> More
Building an Oracle (and other RDBMS) Server Environment

as seen on Internet.com - Search for 'Internet.com'
In previous articles discussing Oracle and VMware, the hardware and software components consisted of a Windows-based PC and the Oracle RDBMS. Steve Callan expands upon the "Oracle in a virtualized environment" concept by looking at other alternatives for the hardware and software. >>> More
What's a good scheme for multi-user database synchronization?

as seen on Programmers - Search for 'Programmers'
I'm working on a system to allow multiple users to collaborate on an online project. Everything is fairly straightforward, except for keeping the users in sync. Each user has their own local copy of the project database, which allows them to make changes and test things out, and then send the updates… >>> More
Predicting advantages of database denormalization

as seen on Programmers - Search for 'Programmers'
I was always taught to strive for the highest Normal Form of database normalization, and we were taught Bernstein's Synthesis algorithm to achieve 3NF. This is all very well and it feels nice to normalize your database, knowing that fields can be modified while retaining consistency. However, performance… >>> More

Developer IT