database course homework 1

a car company wants to setup a database to store its customers data, data about their cars and garages that it has and the deals it has made, the following tables are needed:

Customer: first name, family name, id, town address, street, house number, phone number, and customer type.
Car: car number, car type, manufacturing company, production year, engine number, miles travelled, status.
CarOwner: customer id, car number, start date, end date.
Garage: garage name, town address, street, house number, phone number, company, car type specialty.
CarMaintenance: maintenance number, car number, type of maintenance, maintenance date, name of garage, name of responsible worker.
ReplacedParts: maintenance number, name of replaced part.

the following constraints on the data need to be applied:

customers: id number contains 9 digits only (ids never repeat). the possible cities are: tel aviv, netanya, herzliya, arael, ashdod, haifa, beer sheva, jerusalem, ramat gan. the type of customer can be either private or business.
cars: car number is 8 digits (doesnt repeat), type is private/commerical/motorcycle. company is renault/pazo/alpha/fiat/ford/honda/audi/bmw. production year is ba number greater or equal to 1950 and smaller or equal to 2030, engine number is a string of 10 digits and letters in english and doesnt repeat. kilometers travelled is a number between 0 and 1m. status is new/off/maintenance.
when a car is deleted from the database all the data on it is deleted, including ownership records, maintenance records, and records of replaced parts.
car ownerships: the id of the customer is taken from the customers table, the car number is taken from the cars table, dates are made up of day,month and year only, end date has to be greater than the start date, end date cannot be in the future, incase there is an end date it means that the customer is not the owner of the car anymore. a customer can be an owner of more than one car and a car can be owned by more than one customer.
garages: the name of a garage is unique, the list of possible cities is the same as the one for customers, and the companies allowed are the same as for car entries, the specialty in cars is one of private/commercial/motorcycle.
car maintenances: the number of a maintenance is automatic, types of maintenances are 10000-maintenance/accident/spoilage/proactive. the id of a responsible worker is from the customer list.
replaced parts: the table contains the parts that were replaced during maintenance.

write ddl commands to define a schema with the proper constraints (primary keys, foreign keys, data types and length, additional tests and constraints stemmed by the requirements. every string field should be a varchar2 of length 22.

here, i make use of mariadb, im on nixos (a flavor of linux), and have it installed.
creating the database:

create database homework1

line used for deleting the database (for testing):

drop database homework1;

drop table if exists customer cascade;
drop table if exists car cascade;
drop table if exists car_owner cascade;
drop table if exists garage cascade;
drop table if exists car_maintenance cascade;
drop table if exists replaced_part cascade;

creating the tables:

schema

CREATE TABLE customer (
  first_name    VARCHAR(22) NOT NULL,
  family_name   VARCHAR(22) NOT NULL,
  customer_id   VARCHAR(9)  NOT NULL,
  hometown      VARCHAR(10) CHECK (LOWER(hometown) IN ('tel aviv', 'netanya', 'herzliya', 'arael', 'ashdod', 'haifa', 'beer sheva', 'jerusalem', 'ramat gan')),
  street        VARCHAR(22) NOT NULL,
  house_number  VARCHAR(22) NOT NULL,
  phone_number  VARCHAR(9)  CHECK (phone_number ~ '^-?[0-9]+$'),
  customer_type VARCHAR(10) CHECK (LOWER(customer_type) IN ('business', 'private')),
  primary key (customer_id)
);

CREATE TABLE car (
  car_number      VARCHAR(8)  NOT NULL,
  car_type        VARCHAR(10) CHECK (LOWER(car_type) IN ('private', 'commercial', 'motorcycle')),
  company         VARCHAR(10) CHECK (LOWER(company) IN ('renault', 'pazo', 'alpha', 'fiat', 'ford', 'honda', 'audi', 'bmw')),
  production_year INT         CHECK ((1950 <= production_year) AND (production_year <= 2030)),
  engine_number   VARCHAR(10) NOT NULL UNIQUE,
  kilometers      INT         CHECK ((0 <= kilometers) AND (kilometers <= 1000000)),
  status          VARCHAR(10) CHECK (LOWER(status) IN ('new', 'maintenance', 'off')),
  PRIMARY KEY (car_number)
);

CREATE TABLE car_owner (
  customer_id VARCHAR(9) NOT NULL,
  car_number  VARCHAR(8) NOT NULL,
  start_date  DATE       NOT NULL,
  end_date    DATE CHECK (end_date > start_date OR end_date IS NULL),
  FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
  FOREIGN KEY (car_number) REFERENCES car(car_number) ON DELETE CASCADE
);

-- DELIMITER //

-- CREATE TRIGGER date_check BEFORE INSERT ON car_owner FOR EACH ROW
-- BEGIN
-- IF NEW.end_date >= CURDATE() THEN
-- SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid date!';
-- END IF;
-- END; //

-- DELIMITER ;

CREATE TABLE garage (
  garage_name  VARCHAR(22) NOT NULL,
  town         VARCHAR(10) CHECK (LOWER(town) IN ('tel aviv', 'netanya', 'herzliya', 'arael', 'ashdod', 'haifa', 'beer sheva', 'jerusalem', 'ramat gan')),
  street       VARCHAR(22) NOT NULL,
  company      VARCHAR(10) CHECK (LOWER(company) IN ('renault', 'pazo', 'alpha', 'fiat', 'ford', 'honda', 'audi', 'bmw')),
  house_number VARCHAR(22) NOT NULL,
  phone_number VARCHAR(9)  CHECK (phone_number ~ '^-?[0-9]+$'),
  specialty    VARCHAR(10) CHECK (LOWER(specialty) IN ('private', 'commercial', 'motorcycle')),
  PRIMARY KEY (garage_name)
);

CREATE TABLE car_maintenance (
  maintenance_number    SERIAL,
  car_number            VARCHAR(8)  NOT NULL,
  maintenance_type      VARCHAR(10) CHECK (LOWER(maintenance_type) IN ('10000', 'accident', 'spoilage', 'proactive')) ,
  maintenance_date      DATE        NOT NULL,
  garage_name           VARCHAR(22) NOT NULL,
  responsible_worker_id VARCHAR(9)  NOT NULL,
  PRIMARY KEY (maintenance_number),
  FOREIGN KEY (car_number) REFERENCES car(car_number) ON DELETE CASCADE,
  FOREIGN KEY (garage_name) REFERENCES garage(garage_name),
  FOREIGN KEY (responsible_worker_id) REFERENCES customer(customer_id)
);

CREATE TABLE replaced_part (
  maintenance_number INT         NOT NULL,
  part_name          VARCHAR(22) NOT NULL,
  FOREIGN KEY (maintenance_number) REFERENCES car_maintenance(maintenance_number)
);

some tests to make sure the database works as expected:

describe car;

Field	Type	Null	Key	Default	Extra
car_number	varchar(8)	NO	PRI	NULL
car_type	varchar(10)	YES		NULL
company	varchar(10)	YES		NULL
production_year	int(11)	YES		NULL
engine_number	varchar(10)	NO	UNI	NULL
kilometers	int(11)	YES		NULL
status	varchar(10)	YES		NULL

describe replaced_part

Field	Type	Null	Key	Default	Extra
maintenance_number	int(11)	NO	MUL	NULL
part_name	varchar(22)	NO		NULL

describe car_owner;

Field	Type	Null	Key	Default	Extra
customer_id	varchar(9)	NO	MUL	NULL
car_number	varchar(8)	NO	MUL	NULL
start_date	date	NO		NULL
end_date	date	YES		NULL

describe garage;

Field	Type	Null	Key	Default	Extra
garage_name	varchar(22)	NO	PRI	NULL
town	varchar(10)	YES		NULL
street	varchar(22)	NO		NULL
company	varchar(10)	YES		NULL
house_number	varchar(22)	NO		NULL
phone_number	varchar(9)	YES		NULL
specialty	varchar(10)	YES		NULL

describe car_maintenance

Field	Type	Null	Key	Default	Extra
maintenance_number	int(11)	NO	PRI	NULL	auto_increment
car_number	varchar(8)	NO	MUL	NULL
maintenance_type	varchar(10)	YES		NULL
maintenance_date	date	NO		NULL
garage_name	varchar(22)	NO	MUL	NULL
responsible_worker_id	varchar(9)	NO	MUL	NULL

describe customer

Field	Type	Null	Key	Default	Extra
first_name	varchar(22)	NO		NULL
family_name	varchar(22)	NO		NULL
customer_id	varchar(9)	NO	PRI	NULL
hometown	varchar(10)	YES		NULL
street	varchar(22)	NO		NULL
house_number	varchar(22)	NO		NULL
phone_number	varchar(9)	YES		NULL
customer_type	varchar(10)	YES		NULL

i was too lazy to conduct further modification tests, i'll leave it for next homework.