Answer to exercise Feb.3, 2011

DELIMITER $$

DROP PROCEDURE IF EXISTS `company`.`Modulusby3` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Modulusby3`(IN parameter1 integer)
BEGIN /* start of block */

IF parameter1%3=TRUE then
Select customerName, productName, count(o.orderNumber)
from customers c, products p, orders o, orderdetails od
where c.customerNumber=o.customerNumber and p.productCode=od.productCode and o.orderNumber=od.orderNumber
group by customerName;

ELSEIF parameter1%3=1 then
Select  productName,customerName, count(o.orderNumber)
from customers c, products p, orders o, orderdetails od
where c.customerNumber=o.customerNumber and p.productCode=od.productCode and o.orderNumber=od.orderNumber
group by customerName;

ELSE
Select 'NOTHING';
END IF;

END $$

DELIMITER ;

The Answer for number2 will be posted later...

Lecture Notes (midterms)

Kindly download a copy of your lecture notes in IT 135(Commercial DBMS) for the midterms



Click here to download


:-)
Class prepare for our exercise on Stored Procedure on Thursday February 3, 2011..

Sample Exercises on Stored Procedure

                                         1.)Create a stored procedure that will display office city as well as number of employees assigned     which is the same city with the employeeNumber entered.

Call  GetOffice(1002)

Output:

Office                   Number_of_employees

San Francisco     6

Answer:

DELIMITER $$

DROP PROCEDURE IF EXISTS `company`.`GetOffice` $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetOffice`(IN parameter1 integer)

BEGIN /* start of block */

Select city, count(employeeNumber) as Number_of_Employees from offices c,employees e

where c.officeCode=e.officeCode and c.officeCode=(Select officeCode from employees where employeeNumber=parameter1);/* statement */

END $$

DELIMITER ;




2.) Create a stored procedure that will display the customerName, contactlastname and contactfirstName of the customers by entering the employees firstname and lastname and whose creditlimit is greather than the creditlimit entered.

CALL GetCustomers('Gerard', 'Hernandez',12000);

Output:

Your Customer                                 Contactlastname             ContactFirstname

'Atelier graphique'                         , 'Schmitt',                                          'Carine '

'La Rochelle Gifts'                           , 'Labrune',                                         'Janine '

'Euro+ Shopping Channel',          'Freyre',                                              'Diego '

'Daedalus Designs Imports',       'Rancé',                                                                'Martine '

'Mini Caravy',                                    'Citeaux',                                           'Frédérique '

'Alpha Cognac',                                'Roulet',                                              'Annette '

'Auto Associés & Cie.',                  'Tonini',                                              'Daniel '

Answer:

DELIMITER $$

DROP PROCEDURE IF EXISTS `company`.`GetCustomers` $$

CREATE PROCEDURE `GetCustomers`(IN parameter1 varchar(15),parameter2 varchar(15), parameter3 integer)

BEGIN /* start of block */

Select customerName as YourCustomers, contactlastname,contactfirstname from customers c,employees e

where c.salesRepEmployeeNumber=e.employeeNumber and firstname=parameter1 and lastname=parameter2 and creditLimit>parameter3;/* statement */

END $$

DELIMITER ;

Stored Procedure in MySql

MySQL stored procedures are programs that are not only stored, but executed on the MySQL server. They are, for the most part, logic that has been removed from the application and placed on the database server. Once called from an application over a distributed network, MySQL stored procedures provide a means of interacting in a prescribed way with the database without placing any additional traffic on the network.

Advantages of Stored Procedure

žAs alluded to above, MySQL stored procedures can greatly cut down on the amount of traffic going back and forth over your network.

žStored procedures can greatly improve the security of your database server. SQL that is executed on the server is not subject to SQL injection attacks. 

 žStored procedures provide a way to abstract and separate data access routines from the business logic of your application. 

žStored procedures allow these routines to be accessed by programs using different platforms and API's, and make your applications more portable.  

Lab exam answers

PRELIM LABORATORY EXAM ANSWERS

1.)    Select productName,buyPrice,MSRP,(MSRP-buyPrice) as Profit

from products p

having (MSRP-buyPrice) > all (Select (MSRP-buyPrice)

                  from products p

                  where p.productline=(Select p.productline

 from products p

where productName="1996 Moto Guzzi 1100i"));

Answes:

ProductName	buyPrice	MSRP	Profit
'1952 Alpine Renault 1300'	98.58	214.3	115.72
'2001 Ferrari Enzo'	95.59	207.8	112.21

2.)    SELECT productName, ProductVendor

from products

where productVendor=(Select productVendor from products where productName="1969 Corvair Monza")

and productName!="1969 Corvair Monza";

Product Name	ProductVendor
'1958 Setra Bus'	'Welly Diecast Productions'
'1968 Dodge Charger'	'Welly Diecast Productions'
'1969 Dodge Charger'	'Welly Diecast Productions'
'1917 Grand Touring Sedan'	'Welly Diecast Productions'
'1936 Harley Davidson El Knucklehead'	'Welly Diecast Productions'
'1971 Alpine Renault 1600s'	'Welly Diecast Productions'
'The Queen Mary'	'Welly Diecast Productions'

3.)    SELECT productName,sum(quantityinStock) as ProductInStock,sum(quantityOrdered)as TotalQuantityOrdered,sum(quantityinStock)-sum(quantityOrdered) as QuantityLeft

from products p,orderdetails od, orders o

where p.productCode=od.productCode

and od.orderNumber=o.orderNumber

and status="Shipped"

group by productName;

Rows Fetch(109 rows)

4.)    SELECT productName,orderNumber

from products p LEFT JOIN orderdetails od

ON p.productCode=od.productCode

where orderNumber is NULL;

'1985 Toyota Supra'

NULL

5.)    SELECT customerName,creditLimit

from customers c

where creditLimit < all (Select creditLimit from customers c, employees e

                        where c.salesRepEmployeeNumber=e.employeeNumber and lastname="Vanauf" and firstname="George")

and creditLimit!=0;

'Atelier graphique'	21000
'Auto-Moto Classics Inc.'	23000
'Boards & Toys Co.'	11000
'Gifts4AllAges.com'	41900
'Royale Belge'	23500
'Microscale Inc.'	39800
'Frau da Collezione'	34800

6.)    SELECT customerName,sum(quantityordered)

from customers c, orders o, orderdetails od

where c.customerNumber=o.customerNumber and o.orderNumber=od.orderNumber

and country="USA"

group by customerName;

Rows Fetch(36 rows)

Functional Dependency and Normalization

Class click the link below for a copy of the presentation in Functional Dependency and Normalization



presentation_01
presentation_02

Merry Christmas and Happy New Year..Enjoy the holidays..
Your prelim exam will be on January 5,2010..

Exercises on Functional Dependency and Normalization will be posted here..

Functional Dependency

Functional dependency describes the relationship between

attributes in a relation.

For example, if A and B are attributes of relation R, and B is

functionally dependent on A ( denoted A     B), if each value of

A is associated with exactly one value of B. ( A and B may each

consist of one or more attributes.)