SQL is a standard language for storing, manipulating and retrieving data in databases. I want create a new database in two different way: in this article I use Microsoft Access, in another I explain how I program it in SQLlite.
To create a new SQL database, I would use the CREATE DATABASE statement, but creating a database in MS Access is done by creating a new file.
Either way, the clause would be:
CREATE DATABASE Customers-Cars-Insurances
To program in SQL in MS Access you have to Create a new Query. To do this, go to the Create menu and click on Query Structure.
Then click on SQL View at the top left of the window. A work environment will open.
Enter the SQL commands into the editor, as in the following figure, and click! Run to test and execute them.
Write 3 queries to create 3 tables and their constraints relationships: Customers (PK CodiceFiscale, ...), Cars (PK ChassisNumber, FK CodiceFiscale, ...) and Insurances (PK ChassisNumber, ...). Codice fiscale is an alphanumeric code of 16 characters and serves to unambiguously identify individuals residing in Italy. The chassis number is a unique code for each car.
Customers and Cars have a 1: N (one to many) relationship, Insurance and Cars have a 1: 1 (one to one) relationship.
CREATE TABLE Customers ( CodiceFiscale CHAR (16) NOT NULL, Lastname CHAR (20), Name CHAR (20), BirthdayDate DATE, Address CHAR (80), PRIMARY KEY (CodiceFiscale));
CREATE TABLE Cars ( ChassisNumber CHAR (20) NOT NULL, Brand CHAR (20), Model CHAR (20), RegistrationDate DATE, CodiceFiscale CHAR (16) CONSTRAINT CustomerCar REFERENCES Customers (CodiceFiscale), PRIMARY KEY (ChassisNumber));
CREATE TABLE Insurances ( ChassisNumber CHAR (20) NOT NULL UNIQUE CONSTRAINT CarsInsurances REFERENCES Cars (ChassisNumber), InsurancesDate DATE, InsurancesNumber INT, AnnualCost MONEY, PRIMARY KEY (ChassisNumber));
Now you can populate the tables by entering data into Access or by using new SQL queries. In this last case You can see my article in SQLlite: https://www.filipposfactory.com/index.php/21-sql/40-database-customers-cars-insurances-using-sqlite