INSY 3304 University of TX Arlington Physical Database Design Using MySQL Report

5.4 Physical database Design

After logical design of database I put data in database to create table and their attributes, I runfollowing queries in MYSQL console:.
Add Table “books”
————————

CREATE TABLE `books` (
`book_id` INTEGER NOT NULL,
`book_title` VARCHAR(40),
`edition` VARCHAR(40),
`pub_name` VARCHAR(40),
`pub_date` DATE,
`availability` BOOL,
`num_of_copies` INTEGER,
`state` VARCHAR(40),
`catalog_number` INTEGER,
`row_number` INTEGER,
`active` BOOL,
`created_by` VARCHAR(40),
`update_by` VARCHAR(40),
`created` VARCHAR(40),
`updated` VARCHAR(40),
CONSTRAINT `PK_books` PRIMARY KEY (`book_id`)

);

Add Table “borrow_transaction”——————————————CREATE TABLE `borrow_transaction` (

`borrow_transaction_id` INTEGER NOT NULL,`librarian_id` INTEGER NOT NULL,
`book_id` INTEGER NOT NULL,
`member_id` INTEGER NOT NULL,`borrowed_datetime` DATETIME,`returned_datetime` DATETIME,`borrowed_by` VARCHAR(40),

`returned_by` VARCHAR(40),
`date_due` DATE,
`is_returned` BOOL,
`created_by` VARCHAR(40),
`updated_by` VARCHAR(40),
`created` VARCHAR(40),
`updated` VARCHAR(40),
CONSTRAINT `PK_borrow_transaction` PRIMARY KEY (`borrow_transaction_id`)

);

Add Table “librarian”——————————

CREATE TABLE `librarian` (
`librarian_id` INTEGER NOT NULL,
`name` VARCHAR(40),
`user_name` VARCHAR(40),
`password` VARCHAR(40),
`created_by` VARCHAR(40),
`updated_by` VARCHAR(40),
`created` VARCHAR(40),
`updated` VARCHAR(40),
`active` BOOL,
`inactive_reason` VARCHAR(40),
CONSTRAINT `PK_librarian` PRIMARY KEY (`librarian_id`)

);

Add Table “member”—————————CREATE TABLE `members` (

`member_id` INTEGER NOT NULL,`external_id` INTEGER,`member_type` VARCHAR(40),`member_name` VARCHAR(40),`phone_number` INTEGER,

`gender` VARCHAR(40),
`email` VARCHAR(40),
`active` BOOL,
`created_by` VARCHAR(40),
`updated_by` VARCHAR(40),
`created` VARCHAR(40),
`updated` VARCHAR(40),
CONSTRAINT `PK_members` PRIMARY KEY (`member_id`)

);

Add Table “membership”——————————–CREATE TABLE `membership` (

`librarian_id` INTEGER NOT NULL,`member_id` INTEGER NOT NULL,`registred_datetime` DATETIME,`registred_by` VARCHAR(40),`membership_status` BOOL,`membership_expiry_date` DATE,
PRIMARY KEY (`librarian_id`, `member_id`)

);

Add Table “fined_transactions”—————————————

CREATE TABLE `fined_transactions` (

`fined_transaction_id` INTEGER NOT NULL,`member_id` INTEGER NOT NULL,`librarian_id` INTEGER NOT NULL,`borrow_transaction_id` INTEGER NOT NULL,`amount_added` VARCHAR(40),`amount_received` VARCHAR(40),

`notes` VARCHAR(40),
`deleted` VARCHAR(40),
`created` VARCHAR(40),
`created_by` VARCHAR(40),
CONSTRAINT `PK_fined_transactions` PRIMARY KEY (`fined_transaction_id`)

);

Foreign key constraints
——————————
>ALTER TABLE `borrow_transaction` ADD CONSTRAINT `books_borrow_transaction`

FOREIGN KEY (`book_id`) REFERENCES `books` (`book_id`);

>ALTER TABLE `borrow_transaction` ADD CONSTRAINT `librarian_borrow_transaction`FOREIGN KEY (`librarian_id`) REFERENCES `librarian` (`librarian_id`);

>ALTER TABLE `borrow_transaction` ADD CONSTRAINT `members_borrow_transaction`FOREIGN KEY (`member_id`) REFERENCES `members` (`member_id`);

>ALTER TABLE `membership` ADD CONSTRAINT `librarian_membership`FOREIGN KEY (`librarian_id`) REFERENCES `librarian` (`librarian_id`);

>ALTER TABLE `membership` ADD CONSTRAINT `members_membership`FOREIGN KEY (`member_id`) REFERENCES `members` (`member_id`);

>ALTER TABLE `fined_transactions` ADD CONSTRAINT `librarian_fined_transactions`FOREIGN KEY (`librarian_id`) REFERENCES `librarian` (`librarian_id`);

>ALTER TABLE `fined_transactions` ADD CONSTRAINT`borrow_transaction_fined_transactions`

FOREIGN KEY (`borrow_transaction_id`) REFERENCES `borrow_transaction`(`borrow_transaction_id`);

>ALTER TABLE `fined_transactions` ADD CONSTRAINT `members_fined_transactions`FOREIGN KEY (`member_id`) REFERENCES `members` (`member_id`);

>ALTER TABLE `author` ADD CONSTRAINT `books_author`FOREIGN KEY (`book_id`) REFERENCES `books` (`book_id`);

6.0 Data Manipulation Language (DML)

“is a family of computer languages used by computer programs and/or database users to insert, deleteand update data in a database. Currently the most popular data manipulation language is that of SQL,which is used to retrieve and manipulate data in a Relational database.”

Following queries are used to insert, update and delete data from database(shown few queries only):INSERT INTO `author` (`author_id`, `first_name`, `last_name`) VALUES (2006, ‘edoh’, ‘sanda’);

INSERT INTO `books` (`book_id`, `pub_id`, `book_title`, `edition`, `availability`, `num_of_copies`,`state`, `catalog_number`, `row_number`, `active`, `created_by`, `update_by`, `created`, `updated`)VALUES (1, 0, ‘COD’, ‘2nd’, 1, 10, ‘excellent’, 3, 5, 1, ‘saira’, ‘saira’, ‘2010-10-12 12:10:03′,’2010-10-12 12:10:03’);

INSERT INTO `books_by_author` (`author_id`, `book_id`) VALUES (2006, 3);
INSERT INTO `categories` (`category_id`, `category_name`) VALUES (1746, ‘Computing’);

INSERT INTO `fined_transactions` (`fined_transaction_id`, `member_id`, `librarian_id`,`borrow_transaction_id`, `amount_added`, `amount_received`, `notes`, `deleted`, `created`,`created_by`) VALUES (1, 4, 2, 1, ‘0’, ‘0’, ‘no fine’, ‘2011-02-06’, ‘2011-02-02 10:03:01’, ‘saira’);

UPDATE books SET book_title = ‘MCIP’ WHERE book_id= 3;DELETE from books WHERE book_title =’NE’;

6.1 Functionality of SQL Statements

To fetch available copies of a specific book

——————————————————
SELECT (books.num_of_copies- temp.borrowed_books_count ) as available_copiesFROM books LEFT JOIN (

SELECT count(*) as borrowed_books_countFROM borrow_transaction
WHERE book_id=1
AND is_returned=0

) as temp

ON books.book_id=1

WHERE books.book_id=1;

To find books with overdue date

——————————————

SELECT members.member_name, books.book_title, borrow_transaction.borrowed_datetime,borrow_transaction.date_due

FROM borrow_transaction
LEFT JOIN members ON borrow_transaction.member_id=members.member_idLEFT JOIN books ON borrow_transaction.book_id=books.book_id
WHERE Date_due <= ‘2011-02-07’ AND is_returned=0
Search fine payable against members—————————————————–

SELECT members.member_id, members.member_name,(sum(amount_added)-sum(amount_received) )payable_amount
FROM members
Left Join fined_transactions ON fined_transactions.member_id = members.member_idgroup by members.member_id having (sum(amount_added)-sum(amount_received) ) > 0

Some other functions
—————————
SELECT member_id,librarian_id,amount_added from fined_transactions

WHERE amount_added>5;

SELECT member_type, member_name from membersWHERE member_type!=’student’;

SELECT member_name,member_type,genderFROM members ORDER BY member_name DESC;

SELECT book_title, pub_name

FROM books WHERE pub_name LIKE ‘%william%’;