SQL SCRIPT PROJECT

Question

Good afternoon, I have this project as a college job, I wish someone could give me a light, telling me if there is something wrong, or something I can improve. Follow the print of what is requested, and my script done.

Answer 1

Let’s go to some things I’ve observed.

The id of your table situacao is without type, so change the declaration of that id to id_situacao INTEGER NOT NULL.

The column id_socio on the table carro is of a different kind from id_socio on the table socio, and this will cause errors. The solution is to keep id_socio on the table carro also as INTEGER

In your FK CAR table, there is a small logic problem where your foreign key is being mounted as CONSTRAINT carro_FK FOREIGN KEY (id_socio, id_marca) REFERENCES socio (id_socio, id_marca) and in this way the system will try to link the id_marca of your table carro with id_marca on the table socio, but how socio(id_marca) does not exist, will not work. Right, to work according to your model, this fk should be mounted like this:

CONSTRAINT  carro_socio_FK FOREIGN KEY (id_socio) REFERENCES socio (id_socio),
CONSTRAINT  carro_marca_FK FOREIGN KEY (id_marca) REFERENCES marca (id_marca)

Thus, your table is even more organized, with a Constraint representing each key separately.

After all, with the changes I mentioned, your script would look like this:

CREATE TABLE situacao (
id_situacao INTEGER NOT NULL,
situacao VARCHAR(10) NOT NULL,
CONSTRAINT situacao_id_situacao_PK PRIMARY KEY (id_situacao)
);

CREATE TABLE socio (
id_socio  INTEGER PRIMARY KEY AUTOINCREMENT,
nome         VARCHAR(256) NOT NULL,  
cpf          CHAR(11) NOT NULL,
email        VARCHAR(256),
id_situacao  INTEGER,
CONSTRAINT socio_cpf_UN UNIQUE(cpf),
CONSTRAINT socio_id_situacao_FK FOREIGN KEY (id_situacao) REFERENCES situacao (id_situacao)
);

CREATE TABLE marca (
id_marca     INTEGER  PRIMARY KEY AUTOINCREMENT,
marca        VARCHAR (128) NOT NULL
);

CREATE TABLE carro (
id_carro    INTEGER PRIMARY KEY AUTOINCREMENT,
modelo      VARCHAR(128) NOT NULL,
cor         VARCHAR(64),        
placa       VARCHAR(10) NOT NULL,
id_socio    INTEGER,
id_marca    INTEGER,
CONSTRAINT  carro_placa_UN UNIQUE (placa),
CONSTRAINT  carro_socio_FK FOREIGN KEY (id_socio) REFERENCES socio 
(id_socio),
CONSTRAINT  carro_marca_FK FOREIGN KEY (id_marca) REFERENCES marca 
(id_marca)
);