DatabaseMidtermAssignment

Tables (13)

Assets

  • Vehicle Id — VARCHAR(10) — Primary key — NOT NULL
  • Registration Id — VARCHAR(10)
  • Manufacturer — VARCHAR(10)
  • Model — VARCHAR(10)
  • Color — VARCHAR(10)
  • Odometer — INT
  • Passenger Capacity — INT

Can Use

  • Flag — INT — Primary key — NOT NULL
  • Vehicle Id — VARCHAR(10) — Foreign key → Assets.Vehicle Id — NOT NULL

Country

  • Country Name — VARCHAR(10) — Primary key — NOT NULL
  • Language — VARCHAR(10) — Foreign key → Language.Language — NOT NULL

Date

  • Reservation Id — INT — Foreign key → Reservation.Reservation Id — NOT NULL
  • Start Datetime — DATETIME
  • End DateTime — DATETIME

Driver Info

  • License Number — VARCHAR(18) — Primary key — NOT NULL
  • Language — VARCHAR(10) — Foreign key → Language.Language — NOT NULL
  • Name — VARCHAR(10)
  • Clearance Level — INT — An ITS driver's securityclearance level can be digits from 1 to 4

FATL

  • License Number — VARCHAR(18) — Foreign key → Driver Info.License Number — NOT NULL
  • FATL level — INT — FATL level (1 to 10)
  • FATL qualification date — DATE

Language

  • Language — VARCHAR(10) — Primary key — NOT NULL

Location

  • Reservation Id — INT — Foreign key → Reservation.Reservation Id — NOT NULL
  • Pickup — VARCHAR(30) — such as street number and city
  • Dropoff — VARCHAR(30)
  • Location Type — VARCHAR(20) — types such as Hotel, Airport, and Aquatic Center.

Odometer

  • Reservation Id — INT — Foreign key → Reservation.Reservation Id — NOT NULL
  • Start Odometer — INT
  • End Odometer — INT

Officials

  • City Id — VARCHAR(8) — Primary key — NOT NULL — official's Incheon 8characters based City ID
  • Country Name — VARCHAR(10) — Foreign key → Country.Country Name — NOT NULL
  • Language — VARCHAR(10) — Foreign key → Country.Language — NOT NULL
  • Name — VARCHAR(10)
  • Role — VARCHAR(30) — e. g., coach,judge and physician

Reservation

  • Reservation Id — INT — Primary key — NOT NULL
  • License Number — VARCHAR(18) — Foreign key → Driver Info.License Number — NOT NULL
  • Language — VARCHAR(10) — Foreign key — NOT NULL
  • Vehicle Id — VARCHAR(10) — Foreign key → Status.Vehicle Id — NOT NULL
  • Flag — INT — Foreign key → Status.Flag — NOT NULL

STLVT

  • License Number — VARCHAR(18) — Foreign key → Driver Info.License Number — NOT NULL
  • STLVT level — INT
  • STLVT qualificationdate — DATE
  • Certifying Authority — VARCHAR(20)

Status

  • Vehicle Id — VARCHAR(10) — Foreign key → Can Use.Vehicle Id — NOT NULL
  • Flag — INT — Foreign key → Can Use.Flag — NOT NULL
  • Status — VARCHAR(2) — type as Maintenance (M) orRepair (R)
  • Date — DATE — NOT NULL
  • Odometer — INT — NOT NULL
  • Final Cost — INT — NOT NULL
  • Description — VARCHAR(1000) — NOT NULL