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

Database Design and Implementation - Assignment Example Frequently, there are many patrons who borrow copies of books. Each time when a patron borrow a copy of a book, the system automatically generates a transaction identification associated with the borrowing date and due date of the book loan. Each patron has a patron number, patron's name, patron's contact information such as postal address, phone number, and email address. Library system is a simple solution. However, when a library has several branches and all need to share same system, it adds another dimension and hence makes the problem bit more complex. Library is a place where several books are present where patrons can issue them. A table each for storing branch, patron, and book details are needed. An additional 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 library. 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 tables and relationships between them. Library - There 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, patron's name, patron's 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. CSDL Schema : LIBRARY Entity: BRANCH Attributes: BRANCHNUMBER: NUMBER(10,0) NAME: VARCHAR2(50) LOCATION: VARCHAR2(50) PHONENUMBER: NUMBER(10,0) Identifiers: BRANCHNUMBER Entity: BOOK Attributes: BOOKNUMBER: NUMBER(10,0) TITLE: VARCHAR2(100) PAGECOUNT: NUMBER(10,0) PUBLISHERNAME: VARCHAR2(50) YEAR: NUMBER(4,0) (1, N) AUTHORNAME: VARCHAR2(50) Identifiers: BOOKNUMBER Entity: COPY Attributes: COPYNUMBER: NUMBER(10,0) COST: NUMBER(10,2) Identifiers: COPYNUMBER Entity: PATRON Attributes: PATRONID: NUMBER(10,0) NAME: VARCHAR2(50) ADDRESS: VARCHAR2(100) PHONE: NUMBER(10,0) EMAIL: VARCHAR2(30) Identifiers: PATRONID Entity: TRANSACTION Attributes: TRANSACTIONID: NUMBER(10,0) BORROWDATE: DATE DUEDATE: DATE Identifiers: TRANSACTIONID Relationship: OWNS Connected Entities: (1,n) BRANCH (1,n) COPY ER Diagram DB Creation Scripts CREATE TABLE BRANCH ( BRANCHNUMBER NUMBER(10,0) NOT NULL, NAME VARCHAR2(50) NOT NULL,