For those wondering, the primary key 'ID' on book table is used so that duplicate book names can be represented, with other fields to help the user differentiate which they are looking at.
@@DatabaseStar Also a good point! Primary key changes are to be avoided at all costs. Using a surrogate key as is done here is certainly preferable to using possibly-changing data in the key. Very nice work sir!
@@houstonfirefox " Primary key changes are to be avoided at all costs." That's a bit strong because you can define ON UPDATE CASCADE on the relations and any change in the PK value will be automatically passed to the related tables. This may result in an unexpected or undesirable amount of tuple-writes, but that is a different matter. You cannot break your data by changing a PK value, the "MS" bit in the RDBMS will not let you.
@@vinny142 True and for the most part, I agree. However, many of the so-called 'professional' pieces of software I deal with perform integrity-checking on the front end (application) and inconsistent/little-to-any on the back end (database side). As such, there are many times where there is no protection against a PK change in the primary table resulting in a lot of 'orphaned' data in the related tables. At our company we recently saw this happen to our $250,000+/year CRM system which created quite a nightmare to sort out (and retroactively update). Developers are a whole other story as you know hahaha. Of course, one of the downsides of a surrogate key is when transporting the data between databases using a script. If the 'auto-id' or 'identity' is not disabled prior to the script running then it may result in a renumbering of the surrogate key and all of the fun that ensues after that. Thanks for the reply! I enjoy the discussion!
For the fine system, one could apply a bit of accounting knowledge and keep it all in one table. To do this, I would structure the fines table like this: * (PK) id * (FK) Member_id * Loan_id //Remove FK constraint. Payments can be independent of a specific loan ID. * Fine_date * Fine_type //This is the first big change. Define whether the fine is the initial fine, or compounding interest. Some libraries do that. * Fine_debit //For increases to the fines owed, both initial and interest. * Fine_credit //For decreases to the fines owed. This would essentially structure the fines table to be like an accounting ledger, which is a tried and true layout for auditing any sort of money movement.
Thanks! Good question. I think you're right, you don't actually need the member_id in the fine table. You can find the member that it is for by looking at the related loan record. And having it in the fine table, in my design, leaves it open for the member ID to be different which could be different.
Great question! I'll add the code for this to this video (perhaps as a comment with a link to it). In short, you write a range of Create Table statements, but I can see how it can be confusing.
You could use a combination of the loan table and the book table to determine how many books are currently loaned out. This design doesn't track each individual copy of the book, but you could tell from this design how many of the 10 books are currently loaned out.
Very well done Sir. It would be beneficial if you could show Identifying and Non-Identifying relations in the ERD too. I still find it difficult to grasp. Or if you could please make a video about Strong & Weak Entities and Identifying & Non-Identifying relations.
Why fine_payment is not associated with loan ? If there is something like "Find top10 books for which max fine has been paid", with the current design that is not possible.
I chose not to relate fine_payment to loan because the fine table is already related to loan, and the fine payments could be recorded independently and be for one or many loans. But it would be just as valid to relate fine_payment to fine, which would be done by putting the fine.id into the fine_payment table. You could "find the top 10 books for which max fine has been paid" by joining through the member table to the fine_payment table to see the payments, but yes it would be a bit trickier.
Hi sir! Thanks for the video it really helped me and I learn a lot of basics about SQL from this project. However, I failed to create queries. Could you help me? I need some examples. Thank you for everything.
You can select from the fine table and filter on a specific member ID. There isn't a way to link the specific payment to the specific fine, but you can calculate a total to see the outstanding balance.
Hi, you can use the final design at the end of the video to create the tables. You can write a range of Create Table statements to create the tables in your database.
What if there is a rule that you can't loan more than two books at the same time and if you want to loan another book you have to return one of the two books you have already loaned, how can we add this in the Entity Relationship Diagram?
Good question. You could try to add a constraint for loans, but I don't think a constraint could handle that kind of logic. There are a few ways you could do this: a database trigger to check the table, a function or stored procedure that does the loan process and includes this logic, or adding the logic into the application code. I would suggest either a stored procedure on the database or in the application code.
Hello sir. I am sorry if i make mistake writing in english. Sir i want to add book with its author at the same time but i do not know how to do this. Can you please give me tip
Input: You can have check constraint on the fields like status, category etc. NO need to maintain another table for same. ALTER TABLE [TableName] ADD CONSTRAINT my_constraint CHECK (Status = 'in stock' OR status = 'borrowed')
Yeah that’s true, you can use a check constraint. They are harder to edit or add new values to as you need to run an Alter statement instead of just editing table data. But if the possible values rarely change then it’s not that much of an issue and a check constraint would be better.
Hi Nine, this video actually shows you how to make an ER diagram for this system. At the end of the video is the final ERD, and there's a link to the ERD in the description (a PNG file)
What happens when you click the link? I just tried it and it works. Here's the link: dbshostedfiles.s3.us-west-2.amazonaws.com/dbs/library_management_create.sql
Just for the record man, the way you speak sucks....It is not loud enough, super soft and you have this disappearing end, which makes listening to you frustrating...
Thanks for the feedback! Since recording this video I've worked to improve my audio quality (new microphone, better editing), so I hope the newer videos have better quality. What do you mean by "disappearing end"?
I liked the video a lot. Thanks so much for the time you put on making these, as it might be a hard labour. Being that said, I expected to see some follow up video(s) about querying this database to meet the proposed goals, maybe introducing some function(s) to automate the tasks at hand, etc. But it's ok.
Thanks, either for this or some other one. The good thing is one gets a full example, not only about the design, but also about using it for "real". One thing is understanding joins, or other facilities the language provides, and some other is how it all applies to real-life or real-time use. For example, I opted for trying to implement the digikey's store to begin with, and even though I could write the schema following your logic and it makes perfect sense to me, I'm really having a bad time extracting all the data to fill in the html elements (which can probably be done in a single query by using joins), let alone even figuring out what I should do when the user clicks on an html element or another.
