Entity Relation (E-R) Diagram and a Sequential Diagram Tips (Tools: SQL Management Stuido, Visual Stuido Architect)

E-R Diagram :  Library Check In Check Out 

E-R Diagram notations in SQL Management Studio or Visual Studio Architect: 

Understanding following table relationships in a database schema diagram created from either SQL Management Studio or Visual Studio Architect:

          Unlike other tools like Visio or Erwin, Microsoft uses 'Yellow Key' icon and a '8' or 'oo' icon that are shown at the both ends of a relationship lines connecting two tables.

• one to one (1:1)                    : 'Yellow Key icon' in both ends.

• one to many (1 : 0...*)          :  'Yellow Key icon' in one end and '8 icon' in other end

• many to many (0...* : 0...*)  : 'Yellow Key icon' in both ends.

Below is an example E-R diagram created for an use case of ' Library Check in and Check out'. This is just an instance of a whole System which is 'Library Management System' - that could include Employee Payroll, Catalog Search, Category,  Inventory, Accounting, Payment Methods, Supply Chain, Purchase Orders - what not !!! Guess you get the picture.

Lets Create an Database to do Library check in and check out.

This below diagram shows both normalized and de-normalized version of  a database schema for Library Check in and Check Out System.

You can go ahead and keep normalizing this above schema by doing the below.

• Create a Category master table
• Create a junction table Catalog_Category
• Reservation Table 
• Or Reservation can be handled as a transaction by adding a column in 'Reserved_Dt' in 'Loan_Items' Table and using 'Catalog.Available' column as a string type that holds values  like :- 'Yes'  / 'Hold' / etc.
• and so on depending on your performance (Read and Write) and maintainability needs , Volume of the transactions, size of the project, etc.

Brief details about Tables and possible Message calls (Stored procedures)

Tables:

Members:

·       Members are the Library customers. This table stores Member info, address, address proof, adult or child, etc.
A member creates a Loan Item transaction 

Employees:

·       Store for library staffs. This is included as a staff ID would be good to have if any staff help to check out

Catalog:

·       This is the whole inventory of the Library lending items, categorized, tagged, type assigned.

·       Every item of same Title or ISBN will have its own entry in this table.

·       The ID – is the Item ID that could be a Bar code printed on each item that can be checked out. 
- 'Available'  - Column can be a 'Varchar' and hold different values like :-  "Yes" , "Hold",
 

Member_Transactions:

·       This is the main loan transaction table.

·       Stores the Member Id who is checking out, the Employee who is assisting (If auto check out – this will be the kiosk machine number)

·       Maintains the master details of specific check outs, like total amount owed, paid or not.
- This is a One to many relationship between Member and Transactions. 
- Each transaction can have multiple Catalog Items.
- This is also an intersection (Junction) table between Members and Employees.

Loan_Items:

·       Individual Catalog Items checked out are stored in this table with Check out and Check in dates

·       This table is indexed with both TransactionID as wells as Catalog Item ID.

·       This helps fast retrieval of member’s transactions (MemberID + TransactionID) as well as updating individual Catalog Items during check in.

Types_Lookup:

·       A Catalog Item could be different types and  packages like –

·       Names: Books, DVD, CD, Blue Ray, cassettes (!), Reference Only, etc.

·       Material: Paper, CD,  etc.

·       Cover: plastic case , paper box, etc.

·       It could be packed in Double, triple or more.

·       This helps you to scale for the new additions like – SD Card or Tablets in the future.

·       This table also serves as a look up table while adding inventory and in search filters.

Loan_Rules:

·       This is our little ‘Library Items Lending Rules’ definitions that is applied by a rules engine (a ‘spCheck_Rules’ stored Procedure)

·       Rules are applied to the types.

·       Example:

·       Rule Name: Fine per day over due,   Lending Duration, Number of Waiver allowed, etc.

·       The Matrix field stores little formula that applies for the rule or value.

Stored Procedures:

spMember_Info:

·       Retrives Member info for the given Member Card Number.

·       Rertun info should include the last transaction (check out) info for the given member.  

spCheck_Out:

·       Here you check out final transaction – Insert transaction entries in Member_Transactions, Loan_Items,   update_catalog (a  function call), Payment info. (paid or waived)

·       Return a confirmation with complete transaction info. Including items , due date, etc.

spCheck_In:

·       Update_Catalog, Check_Rules, Check_Dues, Update_items in Loan_Items Table.

spItemCheck:

·       Check the status and availability of each item scanned against Catalog table and return – On Reserve, Available, Due Date, Deny, etc.

·       If the item is available this should Hold the item by setting ‘false’ in Available column to avoid false reservation or checkout.

spCheck_Rules:

·       This program can be considered as a ‘Rules Engine’ for the Library system.

·       This checks and apply rules for each item checked out  like Due Dated, any additional information or items checked in like – calculating penalty amounts, applying to the item

Sequential Diagram : Library Check In Check Out: 

The purpose of Sequential diagram is to communicate between both developers as well as business analysts.
 
Sequential Diagram shows the message relationships between the system components in a time sequence. ( A Collaboration Diagram shows Object roles to a context and message calls are numbered sequentially)


There are several notations you can pick from the below example sequential diagram.

Life Lines - are the vertical bars with header (in this diagram - Library system, spMember_info, etc)
A life line is could be a Component, Class, Actor or a Device which is included in a particular instance of a Use case.

Synchronous messages (one directional arrows), Asynchronous message (Arrows with return arrows), Self message calls (Arrows drawn within same lifeline and inside same execution time of a message call ) ,Fragments ( like the loop section),  Comments , Execution time (a thick bar shows time of a call start to end) and more.