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:
- Each book must have at least one author.
- One author can write one or more books.
- An author can only specialize in one subject.
- A subject can have one or more authors who have specialized in them.
- Each book must be about only one specific subject.
- One subject can have multiple books written on them.
- Each editor has the responsibility of editing one or more books.
- One book can be edited by one editor only.
- Editors need not be specialized in a specific subject.
- Each book requires many items for publication.
- One item type can be used to produce many books.
- One supplier can supply many items.
- One shop owner can buy many books from the publisher.
- 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
- P16238072Requirements table:
CREATE INDEX idx_req ON P16238072Requirements (BooksID, intemID);
This creates unique requirement sets.
- 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.
- CREATE INDEX idx_sub ON P16238072Subjects(subject, type);
- 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).