SQL

[fatima_7]

I am a beginner in this field and I have some mistakes in ((codes)) who can tell me what my mistakes.

Physical model (1)

Employee (SSN, Salary, Phone)

CREATE TABLE Employee (SSN integer PRIMRY KEY, Salary integer, phone integer);
 Department(DNO, dname, Budget, ssn_employee)
CREATE TABLE Department(DNO integer, dname varchar(10 )Budget Integer, ssn_employee int, CONSTRAINT pk_Department PRIMARY KEY (DNO) ,FOREIGN KEY (ssn_employee) REFERENCES employee(ssn_employee) ON UPDATE CASCADE ON DELETE SET NULL );

Child(IDChild, Name, Age, ssn_employee)

CREATE TABLE Child( IDChild Integer, Name varchar(40), Age Integer,  ssn_employee Integer
CONSTRAINT pk_Child PRIMARY KEY (IDChild),
FOREIGN KEY (ssn_employee) REFERENCES employee(ssn_employee)  ON UPDATE CASCADE ON DELETE SET NULL
    );

Employee_Department(SSN_DNO)

CREATE TABLE Employee_Department ( SSN Integer,  DNO Integer,
FOREIGN KEY (SSN) REFERENCES employee(SSN) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (DNO) REFERENCES department(dnumber) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT pk_sd PRIMARY KEY (SSN,DNO)
    );

Physical model (2)
Artist(IDArtist, Name, Birthplace, Age, style)

CREATE TABLE Artist(  IDArtist Integer PRIMARY KEY,Name varchar(30),  Birthplace varchar(20) Age Integer,  Style Integer     );
Customer(IDCustomer, Name, Address)CREATE TABLE Customer(  IDCustomer Integer PRIMARY KEY,  Name varchar(30), Address varchar(50)    );
Artwork(IDArtwork, Title, Type, Price)CREATE TABLE Artwork(  IDArtwork Integer PRIMARY KEY,  Title varchar(40), Type varchar(15),  Price Integer  );   
 Group(IDGroup, Name)CREATE TABLE Group(   IDGroup Integer PRIMARY KEY,  Name varchar(30)
    );
Artist_Artwork(IDArtist, IDArtwork) 

CREATE TABLE Artist_Artwork( IDArtist Integer,   IDArtwork Integer,  FOREIGN KEY (IDArtist) REFERENCES Artist(IDArtist) ON UPDATE CASCADE ON DELETE CASCADE,
  FOREIGN KEY (IDArtwork) REFERENCES Artwork(IDArtwork) ON UPDATE CASCADE ON DELETE CASCADE,  CONSTRAINT pk_Artist_Artwork PRIMARY KEY (IDArtist, IDArtowork) );
   Artwork_Group(IDArtwork, IDGroup) CREATE TABLE Artwork_Group(  IDArtwork Integer,
    IDGroup Integer, 
 FOREIGN KEY (IDArtwork) REFERENCES Artwork(IDArtwork) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (IDGroup) REFERENCES Group(IDGroup) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT pk_Artwork_Group PRIMARY KEY (IDArtwork, IDGroup)
    );
Customer_Artwork(IDCustomer, IDArtwork) 

CREATE TABLE Customer_Artwork (
    IDCustomer Integer,
    IDArtwork Integer,
    FOREIGN KEY (IDCustomer) REFERENCES Customer(IDCustomer) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (IDArtwork) REFERENCES Artwork(IDArtwork) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT pk_Customer_Artwork PRIMARY KEY (IDCustomer, IDArtwork)
    );

Physical model (3)
Employee(IDEmployee, Name, Birthplace, Age, IDManager)

CREATE TABLE Employee(
    IDEmployee Integer,
    Name vargar(15),
    Birthplace varchar(20),
    Age Integer,
    IDManager Integer
    CONSTRAINT pk_Employee PRIMARY KEY (IDEmployee),
    FOREIGN KEY (IDManager) REFERENCES Manager(IDManager)  ON UPDATE CASCADE ON DELETE SET NULL
    );

Product(IDProduct, Type, Price)

CREATE TABLE Product(
    IDProduct Integer PRIMARY KEY,
    Type varchar(25),
    Price Integer
    );

Employee_Product(IDEmployee, IDP)

CREATE TABLE Employee_Product(
    IDEmployee Integer,
    IDP Integer,
    FOREIGN KEY (IDEmployee) REFRENCES Employee(IDEmployee)ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (IDP) REFRENCES Product(IDProduct)ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT pk_Employee_Product PRIMARY KEY (IDEmployee, IDProduct)
    );

Product_Product(IDProduct_Composee, IDProduct_Composer)

CREATE TABLE Product_Product(
    IDProduct_Composee Integer,
    IDProduct_Composer Integer,
    FOREIGN KEY (IDproduct_Composee) REFRENCES EProduct_Composee(IDproduct_Composee)ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (IDproduct_Composee) REFRENCES EProduct_Composee(IDproduct_Composee)ON UPDATE CASCADE ON DELETE CASCADE,
);

[Reverend Jim]

Are we supposed to pore over the code blindly or are you going to give us any hints? What are the symptoms? Are you getting any feedback like "syntax error"? If so, please post the error message and associated information.

Or is it a logic error - the SQL code executes, but does not do what you expect/want?

[fatima_7]

This is my errors :

# Employee (IDEmployee, Name, Birthplace, Age, IDManager) 
Create table Employee (
IDEmployee integer,
Name varchar (30),
Birthplace date,
Age integer,
IDManager integer,
CONSTRAINT pk_employee PRIMARY key (IDEmployee),
  Foreign key (IDManager) references Employee (IDManager) on update cascade ON DELETE SET null
);

(cant create table model 3 employee (errno:150 “foreign key constraint is incorrectly formed”)

#Employee_product (IDEmployee,IDP) 
Create table Employee_product (
IDEmployee integer,
IDP integer,
Foreign key (IDEmployee) references Employee (IDEmployee) on update cascade on delete cascade,
Foreign key (IDP) references Product (IDP) on update cascade on delete cascade,
CONSTRAINT pk_EP primary key (IDEmployee,IDP)
);

-(cant create table model 3 employee (errno:150 “foreign key constraint is incorrectly formed”)

#Product_Product(IDProduct_Composee, IDProduct_Composer) 
Create table Product_Product (
IDProduct_Composee integer,
IDProduct_Composee integer,
Foreign key (IDProduct_Composee) references Employee (IDProduct_Composee) on update cascade on delete cascade,
Foreign key (IDProduct_Composee) references Product (IDProduct_Composee) on update cascade on delete cascade,
constraint pk_pp primary key (IDProduct_Composee, IDProduct_Composer)
);

-duplicate column name ‘IDProduct_Composee

[john_111]

When you reference another table as the place that a foreign key comes from, you have to have created that table before you reference it.
So order your code so that all the tables that will be the source of foreign keys are created first, THEN create the tables that reference then. THEN if a table references two or more other tables, create that last of all.

Trouble is, a lot of your tables reference foreign keys. It's late at night here, so I am not digging in to every bit of code, but do check that you don't have some tablesreferencing each other as the source of foreign keys. IE it's wrong if table A has a foreign key from table B and table B has one from table A.

[john_111]

also look at this bit

CREATE TABLE Product_Product(
    IDProduct_Composee Integer,
    IDProduct_Composer Integer,
    FOREIGN KEY (IDproduct_Composee) REFRENCES EProduct_Composee(IDproduct_Composee)ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (IDproduct_Composee) REFRENCES EProduct_Composee(IDproduct_Composee)ON UPDATE CASCADE ON DELETE CASCADE,
);

TWO lines mentioning IDProduct_Composee Integer, as an attribute in the same table, and then they twice reference EProduct_Composee as a foreign key.
I suspect that this is a bit of a problem. Sort this and things might work better.