jordyhop6 Posted January 28, 2015 Share Posted January 28, 2015 I need help on creating a procedure for my library database. all i need is a basic outline so i can include it into my work. The procedure has to produce a new loan within my current existing tables. The procedure should accept a book isbn and a student number as arguments then being able to search for an a current copy of a book before adding a new row to the loan table. suitable errors such as no copies of the book being available are acceptable. please help Link to comment Share on other sites More sharing options...
Klaynos Posted January 28, 2015 Share Posted January 28, 2015 How experienced are you with SQL? What progress have you made thus far? Link to comment Share on other sites More sharing options...
Sensei Posted January 28, 2015 Share Posted January 28, 2015 (edited) You didn't said in what language it should be.. C/C++/PHP? Read this f.e. http://www.w3schools.com/php/php_mysql_insert.asp http://www.w3schools.com/sql/sql_insert.asp Install MySQL Admin that allows executing SQL commands in text field. And experiment with different commands. You will see what effect they have on table and database. Edited January 28, 2015 by Sensei Link to comment Share on other sites More sharing options...
jordyhop6 Posted January 28, 2015 Author Share Posted January 28, 2015 this is whats been done so far USE jordan; CREATE TABLE book( `isbn` CHAR(17) NOT NULL, `title` VARCHAR(30) NOT NULL, `author` VARCHAR(30) NOT NULL DEFAULT 'N/A', PRIMARY KEY (`isbn`)); CREATE TABLE IF NOT EXISTS copy( `code` INT NOT NULL, `isbn` CHAR(17) NOT NULL, `duration` TINYINT NOT NULL, PRIMARY KEY (`code`), CONSTRAINT `isbn` FOREIGN KEY (`isbn`) REFERENCES `book` (`isbn`) ON DELETE CASCADE ON UPDATE CASCADE); CREATE TABLE student( `no` INT NOT NULL, `name` VARCHAR(30) NOT NULL, `school` CHAR(3) NOT NULL, `embargo` BIT NOT NULL, PRIMARY KEY (`no`)); CREATE TABLE loan( `code` INT NOT NULL, `no` INT NOT NULL, `taken` DATE NOT NULL, `due` DATE NOT NULL, `return` DATE NULL, PRIMARY KEY (`no`, `taken`, `code`), CONSTRAINT `code` FOREIGN KEY (`code`) REFERENCES `copy` (`code`) ON DELETE RESTRICT ON UPDATE CASCADE); SELECT `jordan`.`student`.`no` AS `no`, `jordan`.`student`.`name` AS `name`, `jordan`.`student`.`school` AS `school`, `jordan`.`student`.`embargo` AS `embargo` FROM `jordan`.`student` WHERE (`jordan`.`student`.`school` = 'CMP') Link to comment Share on other sites More sharing options...
Sensei Posted January 28, 2015 Share Posted January 28, 2015 (edited) I run your commands on my MySQL server. All commands executed fine. Although I would add to them IF NOT EXISTS (if it'll be real program). You should read about INSERT INTO manual, if you have not already: http://dev.mysql.com/doc/refman/5.6/en/insert.html Edited January 28, 2015 by Sensei Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now