Madhavan Mukund

Assignment 3

28 October, 2024, due 8 November 10 November 2024

Setup

You are setting up a database for Indian Railways. You start with a table capturing all the relevant fields.

Train (TrainNo, TrainName, DayOfWeek, StationCode,
       StationName, City, Arrival, Departure, Class)
    

1. Each train has a name, TrainName, and is identified by a unique train number, TrainNo.

2. Each station has a name, StationName, and is identified by a unique station code, StationCode. Each station is located in town/city City.

3. A town/city may have more than one station, but any given train stops in at most one station in any town/city.

4. Not all trains run daily. DayOfWeek takes values Sun, Mon, …, Sat. The combination (TrainNo, DayOfWeek, StationCode, Arrival, Departure) indicates the schedule of the train at a particular station on a particular day of the week.

5. Class has codes indicating the different classes of accommodation (Sleeper, AC-3, AC-2, …) available on this train.

6. For each train, the schedule and the different classes of accommodation available is the same on all the days that it runs.

Here are some sample table entries and their interpretations.

• (22157, Mumbai-Chennai Mail, Mon,
   CSTM, Chhatrapati Shivaji Maharaj Terminus, 
   Mumbai, null, 22:52, SL)
  	

  Train 22157 is "Mumbai-Chennai Mail". On Mondays, it leaves from CSTM, Mumbai at 22:52. This is the origin station, so the arrival time is null. It has Sleeper Class (SL) accommodation.

• (22157, Mumbai-Chennai Mail, Tue,
   GTL, Guntakal Junction,
   Guntakal, 13:50, 13:55, AC2)
  	

  On Wednesdays, train 22157 arrives at Guntakal Junction at 13:50 and leaves at 13:55. It has AC Two Tier (AC2) accommodation.

• (22157, Mumbai-Chennai Mail, Fri,
   MS, Chennai Egmore,
   Chennai, 22:15, null, AC3)
  	

  On Fridays, train 22157 arrives at Chennai Egmore at 22:15. This is the terminus for the train, so the departure time is null. It has AC Three Tier (AC3) accommodation.

Questions

1. Identify all functional dependencies in this table.

2. Compute a BCNF decomposition that avoids storing null values.

3. Explain whether your BCNF decomposition is dependency preserving.

4. If your BCNF decomposition is not dependency preserving, compute a dependency preserving 3NF decomposition.

How to submit

• Submit your solutions on Moodle.

• Your submission should be a pdf file. It can either be generated using some document writing software, or a scan of a handwritten document.

• Use the name YourRollNumber-assignment3.pdf for your submission — for instance, MDS202499-assignment3.pdf.