mysqlentity-relationshiperd

How to ER Diagram MYSQL


I'm trying to create simple library database in mysql. I have 5 tables students, entry, book, typebook, author. When I was trying to make er diagram with mysql reverse engineer my tables doesn't have any relationships on er diagram. But almost every databases have relationships on er diagram in the internet. What am i doing wrong and how to fix it?

CREATE TABLE student(
`stuNo` INT NOT NULL,
`stuname` VARCHAR(45) NULL,
`stusurname` VARCHAR(45) NULL,
`class` INT NULL,
`age` INT NULL,
PRIMARY KEY (`stuNo`));

 CREATE TABLE entry(
`stuNo` INT NOT NULL,
`entryno` INT NOT NULL,
`bookno` INT NOT NULL,
`borrowdate` DATE NULL,
`returndate` DATE NULL,
PRIMARY KEY (`bookno`));

 CREATE TABLE book(
`bookno` INT NOT NULL,
`bookname` VARCHAR(45) NULL,
 `authorno` INT NOT NULL,
`typeno` INT NOT NULL,
PRIMARY KEY (authorno , typeno));

 CREATE TABLE  typebook (
`typeno` INT NOT NULL,
`typename` VARCHAR(45) NULL,
PRIMARY KEY (`typeno`));

 CREATE TABLE author(
`authorno` INT NOT NULL,
`authorname` VARCHAR(45) NULL,
`autorname` VARCHAR(45) NULL,
PRIMARY KEY (`authorno`));

enter image description here


Solution

  • I had to switch the order of the CREATE TABLE

    What changes is the order you have to insert data. For example

    If you want to INSERT a book , you first have to insert the typebook and author, that corresponds to the book and so on.

    CREATE TABLE student(
    `stuNo` INT NOT NULL,
    `stuname` VARCHAR(45) NULL,
    `stusurname` VARCHAR(45) NULL,
    `class` INT NULL,
    `age` INT NULL,
    PRIMARY KEY (`stuNo`));
    
     CREATE TABLE  typebook (
    `typeno` INT NOT NULL,
    `typename` VARCHAR(45) NULL,
    PRIMARY KEY (`typeno`));
    
     CREATE TABLE author(
    `authorno` INT NOT NULL,
    `authorname` VARCHAR(45) NULL,
    `autorname` VARCHAR(45) NULL,
    PRIMARY KEY (`authorno`));
    
    CREATE TABLE book(
    `bookno` INT NOT NULL,
    `bookname` VARCHAR(45) NULL,
     `authorno` INT NOT NULL,
    `typeno` INT NOT NULL,
    PRIMARY KEY (authorno , typeno),
    INDEX(bookno),
    FOREIGN KEY (typeno)
            REFERENCES typebook(typeno),
    FOREIGN KEY (authorno)
            REFERENCES author(authorno));
    
     CREATE TABLE entry(
    `stuNo` INT NOT NULL,
    `entryno` INT NOT NULL,
    `bookno` INT NOT NULL,
    `borrowdate` DATE NULL,
    `returndate` DATE NULL,
    PRIMARY KEY (`bookno`),
    FOREIGN KEY (stuNo)
            REFERENCES student(stuNo),
    FOREIGN KEY (bookno)
            REFERENCES book(bookno)
            );
    

    Results in enter image description here