Part 2: Converting a Spreadsheet to a Database You might encounter something aki
ID: 3729077 • Letter: P
Question
Part 2: Converting a Spreadsheet to a Database
You might encounter something akin to the table below in a spreadsheet or file-based application.
Make
Model
ID
Miles Driven
Date Driven
Mileage Cost
Driver ID
License Number
Honda
Accord
10
38.5
10/23/2012
$20.60
101
032 592 173
Honda
Accord
10
38.5
1/17/2012
$20.60
101
032 592 173
Jaguar
I-PACE Concept
20
90
3/1/2012
$48.15
119
331 997 012
Jaguar
I-PACE Concept
20
90
2/22/2012
$48.15
237
419 953 551
Jaguar
I-PACE Concept
209
20
3/6/2012
$10.70
119
331 997 012
Porsche
718
30
18
10/23/2012
$9.63
119
331 997 012
Lamborghini
Roadster
40
40
1/17/2012
$21.40
213
945 459 123
Toyota
Camry
50
35
1/17/2012
$18.73
314
123 476 991
Kia
Optima
60
40
1/17/2012
$21.40
101
032 592 173
Kia
Optima
60
40
1/24/2012
$21.40
149
842 248 842
Imagine that you want to convert this to a relational database. Answer the following questions, which are progressively more difficult:
How many records does this file contain?
How many fields are there per record?
What are the field names?
What do the records in this table probably represent?
Is there data redundancy in the table? If so, describe it.
Does this table contain data that is part of different entities? If your answer is “yes”, identify and briefly describe the entities.
Are there any fields you would add when converting this spreadsheet to a database? If your answer is “yes”, identify each field then briefly describe the reason why you would choose to add it.
Are there other problems you would you need to address in converting this data to a relational database? If your answer is “yes”, identify and briefly describe each problem.
Make
Model
ID
Miles Driven
Date Driven
Mileage Cost
Driver ID
License Number
Honda
Accord
10
38.5
10/23/2012
$20.60
101
032 592 173
Honda
Accord
10
38.5
1/17/2012
$20.60
101
032 592 173
Jaguar
I-PACE Concept
20
90
3/1/2012
$48.15
119
331 997 012
Jaguar
I-PACE Concept
20
90
2/22/2012
$48.15
237
419 953 551
Jaguar
I-PACE Concept
209
20
3/6/2012
$10.70
119
331 997 012
Porsche
718
30
18
10/23/2012
$9.63
119
331 997 012
Lamborghini
Roadster
40
40
1/17/2012
$21.40
213
945 459 123
Toyota
Camry
50
35
1/17/2012
$18.73
314
123 476 991
Kia
Optima
60
40
1/17/2012
$21.40
101
032 592 173
Kia
Optima
60
40
1/24/2012
$21.40
149
842 248 842
Explanation / Answer
How many records this file contains? It has 10 records of the car driven by the different drivers over the time.
How many fields are there per record? 8
What are the field names? Make , Model, Id, Miles driven, Date driven, Millage cost , Driver Id, License number.
What do the records in this table probably represent? Records represent car models that are driven by a driver and when it's driven.
Is there data redundancy in the table? If so, describe it . Yes there is data redundancy. Driver details and car details repeats itself over all records which can be avoided with table design.
Does this table contain data that is part of different entities? If your answer is “yes”, identify and briefly describe the entities. - Yes there are three different entities are available. 1. Driver data. 2 Car data. 3 Trip details.
Driver Data - Driver ID and License number
Car Data - Car ID, Make and Model
Trip Details : Miles Driven, Date driven, Millage cost
Are there any fields you would add when converting this spreadsheet to a database? Yes totally 3 different tables can be created. One for storing car details but this doesn't require any additional fields. Two for storing Driver details this also doesn't require any additional fields. But the last one trip details to hold the trip details need trip_id to uniquely identify the records.
There is no additional problem in converting this into table.
Table 1 : Car - carid, make and model
Table 2 : Driver - driverid, licensenumber
Table 3 : Trip - tripid,drivers,carid,milesdriven,datedriven,millagecost