How to ER Diagram MYSQL

Admin 1
Viewed 220

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

entity-relationship erd mysql
2 Answers

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

user_51935360

The problem is that your tables do not have any FOREIGN KEYS set. MySQL has no idea that the authorno column from one table should be linked to the authorno column from another table. So no relationship links are generated.

To generate the relationship links to your ER diagram, you have to use FOREIGN KEY entries in your CREATE TABLE query to specify which column from one table is referencing a column from a different table:

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 (bookno),
    FOREIGN KEY (authorno) REFERENCES author(authorno)
);

With the added FOREIGN KEY entries MySQL now knows that the authorno column in book must reference a value from the authorno column of the author table. When generating the ER diagram you should get the lines between the tables to see the relationship.

user_2869340
Related