University of Baghdad
College of Engineering
Department of Computer
Novels Electronic Library
database design
By the undergraduate student:
Ghadeer Hayder Abood
Submitted to:
Asst. Prof. Manal Fadhel
And Asst. Prof. Hussein Nasrullah
2020
2
Abstract
The paper proposes a suggestion for a database of an electronic library which is
concerned with novels only. The library contains an er_diagram, a code, and their
explanations.
For designing a database to a library, it is necessary to make primary tables which
are: book table, author table, and translator table (if the novel is a translated one).
Suppositions
A database that contains information about novels and it has the property of
downloading them as pdf or epub throughout a provided link
Arabic, English, and France books only.
The essential aim of the electronic library is to provide a large number of electronic
novels as possible and to provide detailed information about them; such information
can all be found in the database.
There are also things that cannot be found in the library database, such as the names
of authors that their novels are not included in the electronic library, and so on.
3
Figure 1. ER-diagram for E_library system.
Let us describe the tables shown in figure 1.
Author Table
4
The author's table contains all the needed information about the author, such as his
name, birth date, nationality, address, phone number, and email address. Each author
has an ID that distinguishes him/her from another author.
authorID
The data type of author ID is INT. It is a number and Autoincrement. It is unique
because it is related to a specific author only.
AuthorName
The data type of author name is varchar(45), it is not needed for an author's name
to be more than 45 characters, though not fewer characters to give an acceptable
amount of flexibility. The same data type varchar(45) is used for the title of the
novel.
Phone number
The data type of phone number is varchar(15). As far as the designer has
researched, there is no phones number longer than 15 characters [1].
5
Email address
The data type of email address is varchar(320), as it has researched [2] there
is no email address longer than 320 characters.
All the columns, except the name column, are optional as a result of the
Probability that not all the information can be available, but every book should
contain at least the author's name.
Gender
The data type of gender is enum, which consists of three options, M for male, F
for female, and U for unknown. Those options are obligatory, so that if the gender
is unknown, then there is an option to be chosen.
birthDate
The data type for a date is the date.
The relationship between author and books is one to many, and this relationship
is not optional, as mentioned before, each author in the database should have at least
one book, the same is that one or more books belong to the same author, whose name
should be included in the database.
6
Genre and author has genre tables
Each author writes novels in one or more genres, such as fiction novels, horror
novels, Etc. So, the designer connects the author's table with the genre's table
in a (many to many) relationship, so the author can have novels in different
genres. At the same time, one genre can be connected with authors. Also, in
this case, author ID and genre ID are connected together in a new table, and
together they represent the primary key for the author has genre table.
7
translator table
Some novels of a specific language have a translated edition. In this case, there
is at least a translator name (that why this column is obligatory).
Not all novels have a translated edition, and each novel either has one
translator or not. At the same time, each translator should translate one novel
or more so the relationship between translator and book table is one to many
and optional.
8
Publisher table
There is a publisher for each novel, and the publisher can publish more than
one novel, so the relationship between publisher and book tables is one to
many and not optional.
There is at least the name of the publisher should available, whether it was a
person or a company. Other columns are optional. If one is available, then it
will be added, and, if not, it will stay null.
In all the tables above, we notice that the address is not unique because it is
possible that more than one author/publisher/translator lives in the same
house.
9
book table
the book table contains all the needed information about the book.
10
ISBN: Each book has a unique ISBN, so it is perfect as a primary key, it is a
unique column and not null.
title: it is impossible for a novel not to have a title, so the title column is
obligatory. After it has researched in [3], it is possible for tow novels to have
the same title.
originLanguage: this column is obligatory; each book has an original
language.
language: this column is optional. If a novel is translated, then this is the
translated language.
The data type for the two above columns is enum with three options 'a' for
Arabic, 'e' for English, and 'f' for France.
year: Year of Publication the data type is YEAR.
Pages: INT data type.
The tow above columns is obligatory because it is available in all books.
URL: if there is an electronic book for a novel, this column to put the link in.
It is optional because not all novels have an ebook; it is possible for the
database to contain the needed information about a novel didn't have an ebook
edition. The data type is TEXT to give flexibility to the link field.
Description: description for the novel or an overview of it or a Brief Story,
the best data type for a description field is TEXT.
11
authorID and publisherId: obligatory columns (the relationships not
optional as mentioned before). These columns are the foreign keys of the
author and publisher tables.
translatorId : optional column (the relationship is optional). This column is
the foreign key of the translator table.
Book has genre
Each novel has one or more genres (a novel could be horror and fantasy at the
same time), and one genre could have many books, so the relationship is many
to many. ISBN and genreId are connected in a new table, and together they
represent the primary key for the book has genre table.
Conclusion
This a suggested database for novels elibrary, designed with MySQL
Workbench.
12
[1] "Telephone numbering plan."
https://en.wikipedia.org/wiki/Telephone_numbering_plan#:~:text=The
International Telecommunication Union (ITU,15 digits to telephone
numbers.
[2] "Application Techniques for Checking and Transformation of Names."
https://tools.ietf.org/html/rfc3696#section-3.
[3] "goodreads." https://www.goodreads.com/.
