Database Design and Implementation Assignment Example | Topics and Well Written Essays - 2500 words

Database Design and Implementation - Assignment ExampleFrequently, there are many presenters who borrow copies of bears. each time when a patron borrow a copy of a book, the placement automatically generates a transaction identification associated with the borrowing date and collectable date of the book loan. Each patron has a patron number, patrons name, patrons contact information such as postal address, phone number, and email address.Library system is a simple solution. However, when a library has several single outes and all need to share same system, it adds another dimension and hence makes the task bit much complex. Library is a place where several books are present where patrons can issue them. A table each for storing branch, patron, and book lucubrate are needed. An supernumerary table is needed to store list of book authors as there can be multiple authors of a book. As defined in problem several copies of book can be present in one or different branches of libra ry. This adds need of another table which can store details of copies of a book. Apart from these two additional tables are needed to store patron book issue history and second to keep track of which branch contains which copies of book. In all seven different tables are needed to store information per mentioned problem. ER Diagram section shows various(a) tables and relationships between them.Library - in that respect are several branches in a library system, and... The branch number is a unique identifier for each branch.Book - Each book has a book number, title, the number of pages of the books, the name of the publisher, the year of publishing, and list of authors of the book.Patron - A patron borrow a copy of a book. Each patron has a patron number, patrons name, patrons contact information such as postal address, phone number, and email address.Copy - Each library branch owns a large volume of copies of various books, and each copy of a book has a copy number and associated cost.CSDLSchema LIBRARYEntity BRANCHAttributes BRANCHNUMBER NUMBER(10,0)NAME VARCHAR2(50)LOCATION VARCHAR2(50)PHONENUMBER NUMBER(10,0)Identifiers BRANCHNUMBEREntity BOOKAttributes BOOKNUMBER NUMBER(10,0)TITLE VARCHAR2(100)PAGECOUNT NUMBER(10,0)PUBLISHERNAME VARCHAR2(50) grade NUMBER(4,0)(1, N) AUTHORNAME VARCHAR2(50)Identifiers BOOKNUMBEREntity COPYAttributes COPYNUMBER NUMBER(10,0)COST NUMBER(10,2)Identifiers COPYNUMBEREntity PATRONAttributes PATRONID NUMBER(10,0)NAME VARCHAR2(50)ADDRESS VARCHAR2(100)PHONE NUMBER(10,0)EMAIL VARCHAR2(30)Identifiers PATRONIDEntity TRANSACTIONAttributes TRANSACTIONID NUMBER(10,0)BORROWDATE DATEDUEDATE DATEIdentifiers TRANSACTIONIDRelationship OWNSConnected Entities (1,n) BRANCH(1,n) COPYER DiagramDB Creation ScriptsCREATE TABLE BRANCH ( BRANCHNUMBER NUMBER(10,0) NOT NULL, NAME VARCHAR2(50) NOT NULL,