Question about senior project I am doing.

[Qua Sar]

Hello,
I'm not sure exactly if this should go in html/css, php or mysql but I am making a mock-up of a library system website. it has customers who are able to check-out books, view the books they have checked out and make employee complaints (i assume there is more but not positive i don't have it in front of me) As well I have a employee login area and this is where they can add,remove,update,view customer complaints, view checked out books etc.

My professor (advisor) for the project wants me to add advanced database features (stored procedures, triggers, cursors) but I am unsure how they work (learning about them now) but more importantly what to use them for in a mock-up library system. Do you guys have any ideas? She honestly is not the most helpful with this project. She's all about database systems but does not speak good english so it is hard for me to learn from her as I do not understand what she says sometimes and her teaching skills in general are not the best for the way i learn,and also is is downplaying the html/php stuff and says i should make the cursors, triggers and stored procedures in both mysql and micrsofot sql server 2005 (which i am going to do i think) to show that I am familiar with two database systems. Yet, I want to use them on the mock-up website to show also I can use them practically... not just make them.

So, again, I am unsure what to use these for and thought I would ask here. I hope all this makes sense and if you have any questions please tell me. I know one thing that i will include overdue notifications by comparing dates in the databse.. i assume i could use a trigger for this it think once i learn how it works.

Thanks for the input and again, any questions please ask.

[Mike_O]

Hey Qua Sar,

What you are asking is kind of like a new physics student asking his teacher to explain the theory of relativity in a few sentences, know what I mean? I suggest you go to this site: http://www.w3schools.com/. There, you will find tutorials for everything you need. Take your time reading the tutorials you need and practice. Overtime, everything will start to come together, and you'll make your site in no time. It's no big deal, really.

Mike

[Qua Sar]

Well the site is done. Literally all of it except stored procedures, triggers and cursors... I just can't think what I can do in my library mock-up system with these three database ideas. I'm not saying I don't know what to do with my senior project or how to do html/php etc. ... just these three ideas... and i'm not saying i don't need to read up on them... because I do, but i still feel i won't be able to figure out how to incorporate them into my library system... I'm going to read up on them tonight... just thought i'd see if anybody had any ideas on a cool/practical way to use them in a website library system.

[MattF]

just thought i'd see if anybody had any ideas on a cool/practical way to use them in a website library system.

There's nothing 'cool' you can do with triggers, stored procedures etc. They are functional things for functional purposes.

[Qua Sar]

Well that's really what I meant by cool. Practical and functional purposes i could use them for my library system in. I just used the word cool instead of functional.

[Old Pedant]

Well, give an example of an ad hoc SQL statement or two or three that you are currently using.

Or maybe show us the basics of the DB schema.

In my own opinion, if you have to resort to using a cursor in a stored procedure, you probably don't have your DB designed right. Not always true, by any means, but more often than not it is.

As for triggers...<shrug>You may have to invent something mildly artificial to find a use for them.

One possible idea: You add a complaint to the DB. And part of the complaint is, say, the department the complaint is filed against. Okay, so you could have a trigger that adds something to that department's queue. Is it a hack? Yeah, because surely you could get the same info by making a query against the complaints table if it's designed right. But at least it would demo that you understand the concept.

I just looked at a *LARGE* database here, and it has only *ONE* trigger (amongst about 100 tables and I dunno how many SPs). And all that is doing is a "fix" to make sure data is consistent when it is inserted into one of the tables. If the INSERT were done via a SP, the SP could do the work of the trigger. Pointless.

EDIT: (165 tables, 582 Stored Procs...I just checked. And 1 trigger. Enough said?)

[guelphdad]

Consider what you are asking is akin to saying, "Well I've had some student driver lessons and have been driving for a year. What do I need to do now to win a Formula 1 race?"

At the same time I understand that you'll need to use these items to get a good mark.

Consider learning one of them at a time, figuring out how they work and how to implement them as part of your project.

You'll also probably want to know WHY you'd use them. There's a healthy debate out there on the use of Stored Procedures for sure.

On this topic, while I'd normally agree with Mike on the use of w3schools, they have a bunch of stuff on SQL but none on the three items you want to use.

Once you have started working on them and want to ask a specific question you can post them here. Also note that the mysql website itself has specific forums for each of those items, while here at coding forums you have just the one.

That doesn't mean you won't get answers here, just that over at the mysql site itself, you can view other questions and answers that are going to be more specific to your immediate needs.

[Qua Sar]

I feel like there's random stuff you can use cursors and triggers for i will just have to figure it out.

[Qua Sar]

I did not realize that there was a form itself for each of these on the mysql site. Apologies i may try over there and look through there threads and some database tutorials online and in some books if i can find any good ones.

[MattF]

I did not realize that there was a form itself for each of these on the mysql site. Apologies i may try over there and look through there threads and some database tutorials online and in some books if i can find any good ones.

Get yourself over on postgresql.org too. Their documentation tends to be, in my opinion, far superior, (as is the DB system itself), and easier to follow than the MySQL site documentation. Just remember to allow for any differences between the two, syntax wise.