Assessment Item 3: Normalisation Back To Top Value 15 Due Da

Assessment Item 3 Normalisationback To Topvalue15due Date: 03 May 2020

In this task, you will perform normalization on a crude table so that it could be stored as a relational database. The staff at 'Franklin Consulting' have to routinely visit their clients in other cities. Franklin have a fleet of cars available for staff travels. During their trips, the staff sometimes need to fill up the car fuel. Upon returning from the trip, the staff claim that expenses back by providing the fueling receipt and some other essential information.

The accountant records all of that information in a spreadsheet. Below are the spreadsheet column headers and a sample of data from each column:

  • Trip ID: 4129
  • Staff name: Sarah James
  • Car details: Toyota Land Cruiser 2015
  • License Plate: 1CR3KT
  • Odometer reading: 25,067
  • Service station: Coles Express
  • Station address: 27 Queen St, Campbelltown, NSW 2560
  • Fill up time: 30 Jan 2020, 2:45 pm
  • Fuel type: Unleaded 95
  • Quantity litres: 55
  • Cost per litre: $1.753
  • Total paid: $96.42

Given the information in the above table, answer the following:

  1. Draw a dependency diagram to show the functional dependencies between columns. State any assumptions you make about the data and attributes.
  2. Show the step-by-step process of decomposing the table into a set of 3NF relations.
  3. Review the design of your 3NF relations and make necessary amendments to define proper primary keys, foreign keys, and atomic attributes. Any additional relations may also be defined at this stage. Ensure all attributes follow naming conventions.
  4. Draw the Crow’s Foot ERD to illustrate your final design, including all entities, primary keys, attributes, relationships, cardinalities, and optionalities.

Paper For Above instruction

Normalisation is a critical process in designing a relational database, aimed at reducing redundancy and dependency by organizing fields and tables. Based on the provided data, the initial table contains several repeating and composite attributes, such as car details, fueling information, and trip specifics. The goal is to decompose this table into relations that abide by the Third Normal Form (3NF), ensuring each relation has unique primary keys, non-transitive dependencies, and atomic attributes.

1. Analyzing Functional Dependencies and Drawing Dependency Diagram

Initial examination reveals multiple potential dependencies within the dataset. For example, the Trip ID uniquely identifies each trip, so it functions as a primary key candidate. The car details (make, model, year) are linked to the license plate, which itself is unique, implying that License Plate can serve as a unique identifier for a vehicle. Staff name may be associated with multiple trips; thus, staff details such as staff name and possibly staff ID could be dependent on a staff entity.

The fuel information such as station address and service station name could depend on the station, which in turn relates to the station's name and address. Fuel type and quantity are specific to each fill-up event linked to a trip. Dependencies include:

  • Trip ID → Staff Name, Car Details, License Plate, Odometer Reading, Fill-up details
  • License Plate → Car Make, Car Model, Year
  • Service Station Name → Station Address
  • Fill-up Time → Fuel Type, Quantity, Cost per Liter, Total Paid

Assumptions made include:

  • Each trip has a unique Trip ID.
  • License Plate uniquely identifies a specific vehicle.
  • Service stations are uniquely identified by name, and each has a distinct address.
  • Fill-up details are specific to each trip and fuel event.

2. Decomposing into 3NF Relations

The decomposition process involves creating relations that eliminate partial and transitive dependencies.

  1. Trips
    • TripID (PK)
    • StaffID (FK)
    • LicensePlate (FK)
    • OdometerReading
  2. Staff
    • StaffID (PK)
    • StaffName
  3. Vehicles
    • LicensePlate (PK)
    • Make
    • Model
    • Year
  4. Fueling
    • FillUpID (PK, auto-generated)
    • TripID (FK)
    • StationName (FK)
    • FillUpTime
    • FuelType
    • QuantityLitres
    • CostPerLitre
    • TotalPaid
  5. ServiceStations
    • StationName (PK)
    • StationAddress

3. Review and Amendments for Proper Keys and Atomic Attributes

In the above relations, primary keys (PK) and foreign keys (FK) are clearly identified. For example, TripID uniquely identifies trips, while LicensePlate links to the Vehicles table, and StationName links to ServiceStations.

Attributes such as Make, Model, Year, FuelType, QuantityLitres, CostPerLitre, and TotalPaid are atomic and conform to naming conventions. Additional relations can include entities for staff if more staff details are needed, or vehicle maintenance logs if relevant. Each relation maintains data integrity through referential constraints ensuring consistency among related entities.

4. Drawing the Crow’s Foot ERD

The final ERD includes entities such as Staff, Vehicles, Trips, Fueling, and ServiceStations. Relationships are defined as follows:

  • Staff to Trips: One staff member can have many trips (1:N), with StaffID as the foreign key in Trips.
  • Vehicles to Trips: One vehicle can be associated with many trips (1:N), linked by LicensePlate.
  • Trips to Fueling: One trip can have multiple fueling records (1:N), linked by TripID.
  • Fueling to ServiceStations: Each fueling record is associated with one service station (many-to-1), linked by StationName.

This ERD clearly illustrates the normalized structure, capturing all key entities, their relationships, and optionalities, conducive to efficient data management and query performance.

References

Related Assignments