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.

Cliente–Auto-Assicurazione 01.jpg

Then click on SQL View at the top left of the window. A work environment will open.

Cliente–Auto-Assicurazione 02.jpg

Enter the SQL commands into the editor, as in the following figure, and click! Run to test and execute them. 

Cliente–Auto-Assicurazione 03.jpg

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)
);

Cliente–Auto-Assicurazione 04.jpg

 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


Advertising

Advertising

Advertising

We use cookies

We use cookies on our website. Some of them are essential for the operation of the site, while others help us to improve this site and the user experience (tracking cookies). You can decide for yourself whether you want to allow cookies or not. Please note that if you reject them, you may not be able to use all the functionalities of the site.