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

How experienced are you with SQL?

 

What progress have you made thus far?

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, 201510 yr by Sensei

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')

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, 201510 yr by Sensei