The Notion Press Publishers

The Notion Press started their journey on the New Year day of 2012. Since then, they have established themselves as one of the highest quality publishing service provider companies in India. At present, Notion Press offers numerous services to the writing industry. They provide book printing, publishing and distribution services to both authors and publishers all around the globe. They operate from Chennai, India as a self-publishing company (Ltd and Notion Press 2017).

Since their inception, they have sold Books in more than 100 countries. They have proudly published works of about 2000 authors from all over the world. The company was founded by Naveen Valasakumar, Janarthanan and Bhargava with an initial investment of 3.67 Lakh and as of latest news from 2016, they had received a whooping 1 Million US Dollars from the HINs (High Net Worth Individuals).The Notion Press has published various kinds of Books, from different sections over the past 5 years. They publish both fictional and non-fictional works on a variety of subjects like Science, Literature, Arts, Commerce and so on. They aim to capture the publishing industry, which they have already achieved according to the Newindianexpress (2016), and they thrive to publish quality materials and provide the best reading experience to their customers.

Need for a Database Management System

Managing one such vast operational organization is a tough ask. Using a well-designed relational database structure would prove to be extremely fruitful for the company. The database needs to adhere to the rules and policies of the company. Each section of the publishing process must be represented using the unique entity tables in the database.There must be tables in the database that store details of all necessary parts of the publishing chain (Shete and Kilkarni 2015). The publisher’s basic details and branches are to be stored in one table. The books that they published must hold a separate table with each book having an unique id for recognition. The authors of the respective books must be labeled with their unique id as well and presented with basic details. A relationship table must also be kept to relate between the authors and the books that they have written. The tables should also hold necessary information about the Editors and suppliers who work alongside to produce flaw-free and neatly designed articles. The database must define a structure that allows the database administrator or the handler to easily and efficiently enter data. The database must also allow the users to efficiently retrieve data from it using the wide variety of queries available.

Therefore, the company can keep a track of the number of books they are publishing and every details on them. The supplier and items table will provide the list of manufacturing resources and cost involved in the process. It is useful for all small and large scale business to maintain a database (Asong 2016).

Enterprise Rules

The company publishes books on a variety of subjects and provides assistance to various authors to publish their materials. The company also employs Editors. The role of the suppliers are to provide the company with items that they need to publish the Books and finally the shop owners purchase the published books.

The enterprise rules for the database to follow are listed below:

1. Each book must have at least one author.
2. One author can write one or more books.
3. An author can only specialize in one subject.
4. A subject can have one or more authors who have specialized in them.
5. Each book must be about only one specific subject.
6. One subject can have multiple books written on them.
7. Each editor has the responsibility of editing one or more books.
8. One book can be edited by one editor only.
9. Editors need not be specialized in a specific subject.
10. Each book requires many items for publication.
11. One item type can be used to produce many books.
12. One supplier can supply many items.
13. One shop owner can buy many books from the publisher.
14. One book can be sold to only one shop owner.

The ER Diagram will be designed below, based on the above listed enterprise rules.

Books

BookID (Primary Key)

Name

PublicationYear

EditorID (Foreign key)

Authors

AuthorID (Primary key)

First Name

Last Name

Address

Specialization

Editor

EditorID (Primary Key)

Name

Address

Subject

Subjects

Subject Name

Type

Suppliers

SupplierID (Primary key)

Name

Address

Email (Primary key)- It is estimated that the email id of each supplier would be unique and is hence marked as a primary key.

Items

ItemID (Primary key)

Item type

SupplierID (Foreign key)

Amount

ShopOwners

ShopID (Primary key)

ShopName

OwnerName

Address

SoldTo- It is considered that the M-N relationship between the Books and the Shop tables will require an intermediate relationship table with two foreign keys of the respective tables.

BookID (Foreign key)

ShopID (Foreign key)

WrittenBy

BookID (Foreign key)

AuthorID (Foreign key)

Conceptual Design

P16238072Editor

Create table P16238072Editor (

EditorID number primary key, Name varchar(20), Addressvarchar(20), subject varchar(20)

);

P16238072Books

Create table P16238072Books (

bookID number primary key, Name varchar(20), pubYear number, MRP number, EditorID  number foreign key references P16238072Editor(EditorID)

);

P16238072Authors

Create table P16238072Authors (

authorID number primary key, FirstNamevarchar(20),LastNamevarchar(20),address varchar(20), specialization varchar(20)

);

P16238072WrittenBy

Create table P16238072WrittenBy(

Foreign key bookID references number P16238072Books(bookID), authorID number foreign key references P16238072Authors(authorID)

);

P16238072Suppliers

Create table P16238072Suppliers(

supplierID number primary key, Name varchar(20), address varchar(20), email varchar(20)

);

P16238072Items

Create table P16238072Items (

itemID number primary key, itemTypevarchar(20), supplierID number, supplierID number foreign key references P16238072Suppliers(supplierID),amount number

);

P16238072ShopOwners

Create table P16238072ShopOwners(

shopID number primary key, shopNamevarchar(20), ownerNamevarchar(20), addressvarchar(20)

);

P16238072SoldTo

Create table P16238072SoldTo(

bookID number foreign key references P16238072Books(bookID), shopID number foreign key references P16238072ShopOwners(shopID),

);

P16238072Subjects

Create table P16238072Subjects (

subName varchar(20), Types varchar(20)

);

Indexing

1. P16238072Requirements table:

CREATE INDEX idx_req ON P16238072Requirements (BooksID, intemID);

This creates unique requirement sets.

2. P16238072WrittenBy table:

CREATE INDEX idx_writt ON P16238072WrittenBy (BooksID, authorID);

No two entries can mark the same set of author and book IDs, also these are two crucial foreign keys.

3. CREATE INDEX idx_sub ON P16238072Subjects(subject, type);
4. P16238072SoldTo table:

CREATE INDEX idx_sold ON P16238072SoldTo (BooksID, shopID);

Uniquely identify the sets of the book and shop ID foreign keys.

1) Display the list of P16238072Books written by Jason Harris.

SQL>Select Name from P16238072Books where bookID in (select bookID from P16238072WrittenBy where authorID in (select authorID from P16238072Authors where firstName like ‘Jason’ AND lastName like ‘Harris’));

Big Data Innovation

The Cloud Gen

2) How many Books have been bought by the shop whose id is 10?

SQL> select count(shopID) from SoldTo where shopID=10;

COUNT(SHOPID)

3) Display details of Editors who have specialized in a subject.

SQL>Select *from P16238072Editor where Subject is NOT NULL;

P16238072EDITORID NAME                 ADDRESS      SUBJECT

         1 Kevin               Sydney Science

         3 Jayasurya Chennai IT

         4 Amrita             Mumbai              Geography

4) Display names of all Editors who have edited atleast one book.

SQL>select P16238072Books.name as BookName, P16238072Editor.name as Editor from P16238072Books inner join P16238072Editor on P16238072Books.EditorID=P16238072Editor.EditorID;

BOOKNAME          EDITOR

——————– —————–

New Year Night                  Scarllett

Rocks                                    Amrita

Big Data Innovation          Jayasurya

The Cloud Gen                  Jayasurya

History of Fine Art              Scarllett

Anthology of Stories            Amrita

5) Display the Books in ascending order of their MRP.

SQL> select * from P16238072Books order by MRP;

    BOOKID NAME                    PUBYEAR         MRP

         1 New Year Night              2012          150

         4 The Postmaster              2013           200

         3 Big Data Innovation         2001           300

         2 Rocks                       2011           850

         5 History of Fine Art         2012   1000

6)  Display names of all suppliers who have or have not supplied any item.

SQL> select P16238072Items.itemid, P16238072Suppliers.name as supplier from P16238072Items right join P16238072Suppliers on P16238072Items.supplierid=P16238072Suppliers.supplierid;

References

Asong, K. K., 2016. Development of Employees Leave Database Management System. Development, 1(5).

Darwen, H., 2012. The relational model: Beginning of an era. IEEE Annals of the History of Computing, 34(4), 30-37.

Feng, X., Kumar, A., Recht, B. and Ré, C., 2012, May. Towards a unified architecture for in-RDBMS analytics. In Proceedings of the 2012 ACM SIGMOD International Conference on Management of Data (pp. 325-336). ACM.

Ltd, & Notion Press, I., 2017. Self Publishing Company in India | Publishing Houses in India. Notionpress.com. Retrieved 26 December 2017, from https://notionpress.com/about

Newindianexpress.com, S., cause, B., ahead, C., recall, T., silence, H., record, O. and consciousness, T., 2017. Self-publishing: Meet the new booksellers – Indulge. Indulge. Retrieved 26 December 2017, from https://indulge.newindianexpress.com/the-new-booksellers/section/11998

Shete, S. S. and Kulakrni, C. S., 2015. Role-Based Access Control within RDBMS. International Journal of Advanced Research in Computer Science, 6(7).