Project 1 Solution

1. The Problem




Autotrak Inc. is an Intelligent Transportation Systems contractor that undertakes the construction of an Electronic Toll Collection (ETC) for the state of Wonderland. A database is required to keep track of vehicle, tollway, toll gate, pricing, and billing information.




The state of Wonderland has about 100 tollways which goes about 2000 miles long in total. Each tollway has a unique number as identifier as well as some additional information such as completion year, constructors (multiple), length, number of lanes, etc. In the ETC system, toll gates are built at 5 miles intervals on every tollway. Therefore, the total charge will be related to the number of toll gates that a vehicle went through. All tollways in the system uses same toll rate standard. .




In the ETC system, vehicles are classified into three categories: small size (weight <= 3 tons), middle size ( 3 tons < weight <= 10 tons), large size ( weight 10 tons) . The toll rates are $0.1/mile for small class, $0.2/mile for middle class, $0.3/mile for large class. Each vehicle has a unique plate number (combination of letters and digits) and a unique E-tag number which identifies the car to the RFID sensors on tollgates.




Further, the system keeps all ETC sensor readings which include time, E-tag number, and tollgate information when a car goes through a tollgate. for billing and tracking.




the system stores all drivers’ information, which is same as the information on the information on driver’s license. It also stores vehicle registration information and owner’s information for billing and payment purposes.




All IDs are numbers with at most 6 digits. All names are at most 15 characters in length. Monetary figures are all in US dollars and involve no more than nine digits before the decimal point. Let all character strings in the database be stored entirely in lowercase.

2. Tasks







Task 1: Design an ER diagram for the ETC database according to the above problem statement.




Task 2: Map the ERD into a relational database schema.




Task 3: Create the tables for the above relational schema using SQL Server. Use meaningful relation names and data types for the columns, and use several column conditions such as CHECK, DEFAULT values, and NOT NULL. Primary keys must be defined for every table, and declare several foreign keys are required to enforce referential integrity.




Task 4: Populate the tables. Insert at least 5 records in each table. Make sure you populate the database in such a way that generates non-empty results for the queries listed on the next step.




Task 5 Write and execute SQL queries for:




List the numbers and the length for the tollways which are built before 1990.



List the names and their driver’s license numbers for the drivers who own more than 2 vehicles.



List the mileage and its tollway number of the top 3 tollgates which accumulate the most through-traffic so far.



List the plate numbers and the owner’s name of the vehicles which has travelled more than 10 miles on tollway #1.



List first and last names of the drivers who drove on more than 3 different tollways in 2017.



Task 6 Create and execute three of your own queries that involve sub-queries, multiples tables, including those tables that are part of the expansion. At least 1 of your queries must include GROUP BY clauses. You should explain the meaning of each query.

3. Submission




You could do this project at home if you have your own SQL Server, but you must create and populate your database in the school’s SQL Server. Every student has a database on the SQL Server (MSSQL) that use Windows login for authentication. Please, make sure you have access to your database




You should submit a zip file contains the following items through D2L dropbox.




ER diagram;
Relation schema with constraints and keys;
The scripts (for creating, populating and querying the tables).






Timeline:



Oct 9 : Task 1,2 done, see the instructor for progress check
Oct 12: Task 3 done
Oct 19: Task 4, 5, and 6 done and solution due






NOTE:




Tasks for this project can be done in group of two students.



By the time you have finished with an assignment, everyone in the group should understand the solution to each problem, and each group member should be able to explain how to solve each problem.