-2
I need to create, in the database, the table below, and insert the values shown.
My question is **what would be the primary key in this table ?
**ID_NF ? In exercise asks to only create this table, but still I don’t know if I should create another table to have felt the id_nf, id_item and cod_prod.
Could you help me with that ?
I have no other table data, only this and from it will be made queries as for example Search the items that were sold without discount.
0
A primary key uniquely identifies a row in the table. In your case, it would be composed of ID_NF and ID_ITEM. In any database (and Mysql is no exception) there are functions to create primary keys through functions. However, this could lead to future performance problems and unwanted initial complexity. Thus, a basic idea would be to create an additional column, called, for example id -- which would be automatically generated by the database manager itself.
There is not a very rigid standardization in these cases, but it is common to use as the primary key a name in the style id_TABELA. For example, if the table name is notas, your primary key would be id_notas.
A suggestion for the table structure is
CREATE TABLE notas (
notas_id INT AUTO_INCREMENT PRIMARY KEY,
id_nf INT NOT NULL,
id_item INT NOT NULL,
cod_prod INT NOT NULL,
valor_unit DECIMAL (10, 2) NOT NULL,
quantidade INT NOT NULL,
desconto FLOAT
);
If you don’t want to create an additional key, the second form of the table could be
CREATE TABLE notas (
id_nf INT NOT NULL,
id_item INT NOT NULL,
cod_prod INT NOT NULL,
valor_unit DECIMAL (10, 2) NOT NULL,
quantidade INT NOT NULL,
desconto FLOAT,
PRIMARY KEY (id_nf, id_item)
);
A tutorial in Portuguese that explains much of the items (if not all) of these structures can be found in Data Definition - DDL
There are some precautions that were not mentioned here: creation of a database that will accommodate the table, and destruction of the table if it exists. But there should be enough for a good start.
Browser other questions tagged mysql sql database modeling
You are not signed in. Login or sign up in order to post.
As there are several records with the same value of
ID_NFthen this field could not be a primary key as it does not uniquely identify a table row. Does your teacher not want you to apply the normalization process?– anonimo
It may be, but looking at it I don’t know how I would do the normalization because for me it is already normalized. I think it would be thing to see composite primary key or if I need to make the other tables to use Foreign key in this.
– Debora Cunha
You consider that, for example,
valor_unitdoes not depend solely on thecod_prodbut may vary with eachid_nforid_item, to conclude that the table presented is standardised?– anonimo
I’m starting in a database, I don’t know much. That’s why I said I don’t know how to normalize because I don’t know how to normalize anymore. As much as I found something, I wouldn’t know how to normalize this table.
– Debora Cunha