LIBRary Database management system

Design, development, and implementation of a relational database in SQL to track lending records in a library.

Development of front-end application in Python to allow users to interact with a menu interface to obtain information related to the patrons and products that comprise the library system.

Part 1 - Identification of relationship types and their participation and cardinality constraints

PArt 2

After performing my first approach at trying to understand relationship types and their participation and cardinality constraints, I realized I needed to add more tables and attributes in order to have a more detailed design that will help me later on. The following tables represent a more final overview of my database, that I will need in order to run SQL queries later on.

Identification of attributes and association of attributes with entity or relationship types

  • Patron(patronID, cardID, fName, lName, address, pNumber)

  • LibraryCard(cardID, dateIssued, dateExpires)

  • Item(itemID, categoryID, itemName, type, replacementCost)

  • ItemCategory(categoryID, categoryName, maxRenewals, checkoutPeriod)

  • ItemCopy(copyID, itemID, status)

  • Fine(fineID, fineTypeID, transactionID, dateIssued, fineAmount)

  • FineType(fineTypeID, fineName, fineAmount)

  • Request(requestID, patronID, itemID, dateRequested, dateClosed, status)

  • Transaction(transactionID, transactionTypeID, copyID, patronID, date)

  • TransactionType(transactionTypeID, tName)

Determination of candidate and primary key attributes of entity types

  • Patron: patronID, cardID

  • LibraryCard: cardID

  • Item: itemID, categoryID

  • ItemCategory: categoryID

  • ItemCopy: copyID, itemID

  • Fine: fineID, fineTypeID, transactionID

  • FineType: fineTypeID

  • Request: requestID, patronID, itemID

  • Transaction: transactionID, transactionTypeID, copyID, patronID

  • TransactionType: transactionTypeID

PART 3 - Enhanced Entity-Relationship (EER) diagram

Initial diagram without taking into consideration new tables.

Final diagram after taking into consideration all and final tables and attributes.

Part 4 - DEVElopment of global logical data model

The logical model takes on many if not all attributes from the conceptual model. Although confusing, all tables are essential in order to have the library database perform as expected. The only table that could be omitted, if wanted, would be the “LibraryCard” table. I decided to keep it, as it is not a very complex one, and helps me have a better visual understanding of how the database will be implemented and its performance.

All primary keys identified for each table are ID attributes, and therefore are non-null ones. I tried to use ID attributes as primary keys as much as possible, as it is better to avoid varchar. To do so, I made sure to create ID keys for each table when appropriate. Foreign keys for each table are a reflection of other primary keys and are referenced accordingly.

Part 5 - Translating global logical data model for the MYSQL

For this final ERR diagram implemented into MYSQL, I performed some slight variations compared to my logical model just showed above. I was able to have a better visual idea of the relationships between each table and the different primary/foreign keys. I had to add a foreign key to the “Fine” table that would reflect the primary key of the “Patron” table, in order to allow that relationship to happen as expected.

PArt 6 - sql queries and python menu code

The remaining of the project consisted in writing SQL queries in order to obtain the required information from the library management system, which were then used and implemented in a Python code to create a menu interface for the user to interact with. Queries and code can be found on my GitHub profile linked below.

GitHub Link

 
Next

Clinic Database Management System