assigmnet 1 121002110

|
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
 74 views
of 3

Please download to get full document.

View again

Description
Some database functions avialble
Share
Tags
Transcript
  Student Name : Rasika Sampath AgampodiIndex Number : 121002110Database system for Malith Book Shop –MataraDBMS: MySql Version 5.5Store details about books , authors ,customers , sales , Branches, sales . A separate table is createdto store these details within the bookstore data base. Use of Primary Keys Table “author” is created with primary key “aut_id”. CREATE TABLE author(aut_id varchar(8) NOT NULL ,aut_name varchar(50) NOT NULL, country varchar(25) NOT NULL,home_city varchar(25) NOT NULL , PRIMARY KEY (aut_id));  To check whether this is effective an insert statement is executed to violate the PK constraint.   Use of Not Null constraint  Book table has been created with book_price as NOT NULL   To check that constraint is working, following insert statement is executed with home_city value asnull value.   Use of UNIQE constraint  Book table has created with isbn as UNIQE So the ISBN can’t be duplicated.    Use of Cascade Statement  In the above MySQL statement a new table 'newpurchase' will be created. The PRIMARY KEY forthat table 'newpurchase' is 'invoice_no'. The one FOREIGN KEY for the table 'newpurchase' is acombination of 'ord_no' and 'book_id'. Another FOREIGN KEY for the table 'newpurchase' is 'cate_id'. The 'ord_no' and 'book_id'combination is the PRIMARY KEY for the table 'neworder'.The 'cate_id' is the PRIMARY KEY for the table 'category'. The FOREIGN KEY 'ord_no' and 'book_id'combination for the table 'newpurchase', which points to the PRIMARY KEY 'ord_no' and 'book_id'combination of the table 'neworder'. That means the distinct ('ord_no' and 'book_id') combinationwhich are present in the 'neworder' table only those unique 'order number' and 'book id'combination will come in the 'newpurchase' table. The another FOREIGN KEY 'cate_id' for the table'newpurchase' , which points to the PRIMARY KEY 'cate_id' of the table 'category'. That means the'cate_id' which are present in the 'category' table only those 'category' will come in the'newpurchase' table. The ON UPDATE CASCADE ensures that the records inside the child table 'newpurchase'always points to the PRIMARY KEY inside the parent table 'neworder'.  If any record gets deleted/updated from the 'neworder' table DBMS handles the deletion/updationof the records from 'newpurchase' table.ON DELETE RESTRICT prevents a record in a parent table 'neworder' being deleted or altered whenit is still referenced from a child table 'newpurchase'.  Triggers CREATE TRIGGER stockTrig BEFORE UPDATE ON books_sales FOR EACH ROW BEGIN IFNEW.quantity != OLD.quantity THEN INSERT INTO stock_log SET book_id = OLD. book_id, old_stock = OLD.quantity, new_stock = NEW.quantity, date = CURDATE() ,invoice_no=NEW.invoice_no;   This will ensure at each invoice the deduction in stock for auditing purposes.
Related Search
We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks