Let’s say we want to run some data collection on the Golden Gate bridge.

<ol>
<li>What would the table schema look like if we wanted to track how long each car took coming into San Francisco to enter and exit the bridge? Let’s say we want to track additional descriptives like the car model and license plate.</li>

<li>Write a query on the given tables to get the time of the fastest car on the current day.</li>

<li>Write a query on the given tables to get the car model with the average fastest times for the current day.</li>
</ol>

Example: Let’s say three Ferraris crossed the bridge in an average of one minute.

In the context of tracking bridge crossings, what could be a major advantage of storing the car model data in a separate table and linking it with a foreign key?

In the context of tracking bridge crossings, what could be a major advantage of storing the car model data in a separate table and linking it with a foreign key?

Crossing Bridges I

Facilitates faster insertion of new records

Efficient use of storage by avoiding repetitive data

Allows deletion of data without constraints

What would be a SQL performance consideration when joining a large <code>bridge_crossings</code> table with a smaller <code>car_models</code> table, if the <code>car_models</code> table is updated frequently?

What would be a SQL performance consideration when joining a large `bridge_crossings` table with a smaller `car_models` table, if the `car_models` table is updated frequently?

Crossing Bridges II

The join operation may become slower or cause locks due to frequent updates to the `car_models` table

The `crossings` table will become smaller in size due to the join operation

The `bridge_crossings` table will automatically update whenever the `car_models` table is updated

Join operations become faster with frequent updates

The `car_models` table will become larger in size due to the join operation

Crossing Bridges

Comments