drklee3 / meteor-car-dealership

An automated record-maintenance system for a car repair department. Final project for COEN 178 (Intro to Database Systems) at SCU (Santa Clara University).

meteor-car-dealership's Issues

Change timestamps from VARCHAR to a TIMESTAMP

For time_in / time_out since we need to find a range of RepairJobs between two dates and to determine which repair jobs are in the same year. We can either use DATE or TIMESTAMP depending on if we want the date itself or date with time.

https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm

Edit:
use TIMESTAMP

[...] time (and date) the car brought in, datetime the car is ready...

Missing labour hours in Repair Jobs

The data that is maintained for a repair job includes the following information: the car license number, customer contact info (phone or email), time_in (the time car is brought in), time_out (the time when the car is ready to be picked up), the problems ids associated with the car, mechanic_Id(s), labor hours, name and the cost of the parts used.

The labour_hours attribute is not in /web/migrations/005_create_repair_jobs.sql

Fetch missing data for repair jobs on creation

Create a RepairJob for a car when it is brought in. This will have information for all the fields except for time_out(the datetime when the car is ready to be picked up), the problems ids associated with the car, labor hours, name and the cost of the parts used

UI apparently won't have the following fields provided by the user:

• time_out
• problem ids
• labor hours
• name / cost of parts

So in order to assign problems to a RepairJob we have a set number of problems.
To create n random problems, generate a random number n and for each of those n problems get a random problem. Then insert each (problem_id, repair_id) into repair_problems.

Pseudocode

def assign_problems(repair_id, problems_list):
    # random number of problems, upper bound could be anything
    num_problems = randint(0, 5)
    for i in num_problems:
        # rand between 0 to length
        prob_id = randint(0, len(problems_list))

        insert_into_problems(repair_id, prob_id)

Customer Primary Key

use address as primary key,
phone and email can be either or but cannot both be null.
(phone, null)
(null, email)
(phone, email)

not (null, null)

something with constraint yeah

CONSTRAINT phone_or_email
         CHECK (phone IS NOT NULL
               OR email IS NOT NULL)

Inserting and fetching repair jobs

Since we are now using TIMESTAMP attribute types, we will need to insert timestamps and query rows between two given timestamps. For inserting, follow Example 4-2 (Inserting Data into a TIMESTAMP Column) on the Datetime doc page. Assume it is the correct format in the SQL, it should be provided in the correct format from the front end.

For querying, we need to fetch repair jobs between a given start and end date.

Show a listing of all the repair jobs done between two given dates. The listing should include the model of the car, id and the description of the repair job and the mechanic in charge of the repair.

 SELECT *
   FROM repair_jobs
  WHERE timestamps
BETWEEN TO_DATE(:start_date, 'YYYY-MM-DD"T"HH24:MI:SS')
    AND TO_DATE(:end_date, 'YYYY-MM-DD"T"HH24:MI:SS');

Binds given should be character strings and it is uncertain if there is implicit type conversions. Try the methods listed in the example above such as BETWEN TIMESTAMP :start_date AND TIMESTAMP :end_date.

Integrity constraints and business rules to be enforced

• When RepairJob is completed on a car and before the information is deleted, it should be saved into a separate log table.

Can be done with a trigger on delete, or just make a procedure for both deleting + saving to log.

• A customer who has brought in a car for repair (within the same year) is given a 10% discount on subsequent repair jobs (need not be on the same car).

Can be done in the procedure for creating a bill, check if there has been a repair in the same year by the same customer.

SQL Consistent Case

Use snake case for all attributes and table names.

hello_world instead of helloWorld