Lab Assignment Sheet- Odd SEM 2013 Database Management System Lab (10B17CI372) Lab C!dinat!" Megha #athi LAB $%IDELI&ES 1. Every Every Bat Batch ch will will hav havee one LAB LAB per wee week. k. The assignment evaluation day and the particular week is the deadline. If you are absent in your evaluation day or fail to get it evaluated in that particular week evaluation will not be proceeded to second corresponding week and would be awarded !. If it is urgent you can get it "#$E% Evaluated& by respective Lab teacher. '. (opied (opied assign assignmen mentt or plagia plagiaris rism m would be dealt severel severely y and would would be straig straight ht awarded !. ). The marks marks for particular particular evaluat evaluated ed assignment assignment should should be fed fed in that particul particular ar week by the teacher. If your marks are not entered in the evaluation week it would be automatically filled as !. *. '!ets" Total ) evaluations will be there by respective lab teachers. Each batch would be given a domain in which groups in that batch will do pro+ects on different topics lying in that domain. ,roup si-e min) ma/*. Intra batches not allowed. 0. #ro+ec #ro+ects ts would would be thin thin client client pro+ect pro+ects s more more emphases emphases would would be on oracle oracle '* S+* code. #ro+ects made in Access ,I based editor2s net beans and /ampp etc are strictly prohibited and would be awarded !.
Assignment 1 ('!atie Assignment) '!atie Lab and Date" ,ee 1 " 1. /* t 22 /*
Sena!i" An efficient computeri-ed library management system will enhance the effectiveness of the library. The system makes the recording of borrowing simple and provides useful information about library use. This assignment aimed at developing the prototype of Library 3anagement system using the concepts of 4ile 5andling. ntina* mnents the system" A typical library management system includes the following functions6modules to process library operation. 7a8 (ataloging (b) (irculation This is also an essential function that handles all aspects of borrowing and returning of library materials. In addition the system should have the capability of supporting fines management and other relevant processes such as (heck in (heck out $enewal 7c8 Administration This function allows the librarian to define system parameters 7i.e. fine calculation renewal procedure8 manage and maintain any system setting. 7d8 9#A(6:eb 9#A( 9#A( is 9nline #ublic Access (atalog. It is the module to provide a public interface for readers to search the library catalog. :eb 9#A( is the web version of 9#A( to allow readers to access the catalog via Internet. 9ther considerable features of this module are;
Assignment 2 ('!atie Assignment) '!atie Lab and Date" ,ee 2" 23 /* t 24 /* MS-Aess
5as I" 4ollow the instructions given in $eference
5as II" Sena!i" >ou have been asked to create an application to support the sales and marketing functions of a company that manufactures and sells snowboards. As part of that application you will create a 3icrosoft Access database that will support recording sales transactions. As a proof of concept you will create some sample forms and =ueries that will demonstrate how this application might function as a decision support system for the
• •
• •
(reate Tables 1. #roduct7pnumber desc price8 '. (ustomer7custno cname address city state -ip8 ). 9rder79no custno pnumber saleamt orddate payment cardtype8 Insert appropriate data into the three tables 7atleast 0 rows8. After you have entered the data into the three tables create th!ee reports for displaying the table data. 4orm $elationships between tables. #rint the =ueries results that answer the following =uestions; 1. List the name of all customers who makes order on 1! @uly '!1). '. List the customer information 7all fields8 for customers who purchased a 5alfpipe snowboard. ). List the customer number for customers who purchased a snowboard using a ?ebit card or (redit (ard. *. List the name of customers who buy product whose price is greater than 1!!!.
Assignment 3 (E6a*ati6e Assignment Ma! 10) Lab and Date" ,ee 3 " 30 /* t 0 Ag MS-Aess
>our new +ob is assistant to the manager of the oise works 3usic Agency. The agency books bands for various functions and the manager has asked you to maintain the list of bands for which the company handles bookings. This is a sample of the information to be kept;
BA&D &AME
MEMBE#S
'#ICE
M%SIC 58'E
&O5ES
5I,5 I ?$>
*
0!!
$9(C D $9LL
weddings balls dances preferred
<A$E ?A(E T$I9
)
F0!
(9T$> D :E
prefers (D: gigs and concerts suitable for weddings
BLE <9G A$TET
*
*!!
@AHH
+a-- festivals dances. #refers smaller gigs.
>9, A? #$9?
0
0!
$9(C D $9LL
young band suits concerts and school functions
3ATT5E: A? <9
)
0!
$9(C D $9LL
a young band prefers gigs with a younger audience
1. (reate a database file called Bands and a table called 9I
'. Enter the data for the bands. ). Add one more band to your file; o
4lying (olors 0 members costing J!!. The band plays +a-- and the notes in the file indicate that the band is suitable for dances and mature audiences.
*. 3ake these changes to the data in your file; o
5igh in ?ry has increased its price to 00!.
o
Blue
0. 3atthew and
List all the $ock D $oll bands that cost less than !!.
J. List all the band names and the price for bands which play (ountry D :estern.
Assignment 9 ('!atie Assignment) Lab and Date" ,ee 9 : " 0. Ag t 14 Ag S;L I<
B!anh (b!anh&= st!eet= ity= stde)
II<
Sta (sta&= &ame=*&ame= sitin= se>= DOB= sa*a!y= b!anh&)
III<
'!e!ty!#ent (!e!ty&= st!eet= ity= stde= tye= !ms= !ent= ?ne!&= sta&= b!anh&)
I@<
C*ient (*ient&= &ame= *&ame= te*&= !e5ye= ma>#ent)
@<
'!i6ateO?ne!(?ne!&= &ame= *&ame= add!ess= te*&)
@I<
@ie?ing (*ient&= !e!ty&= 6ie?Date= mment)
@II<
#egist!atin (*ient&= b!anh&= sta&= date/ined )
1. (reate the above relations including all primary and foreign key integrity constraint with <L. '. Add Tuples to all relations using I
default value M?elhi2. Add (onstraint that salary of staff is greater than 1!!!!. :rite an <L H
K. Alter $elation MOiewing2 add constraint that attribute (omment will take default value MIL2. 1!. :rite <L
Assignment ('!atie Assignment) Lab and Date" ,ee ." 20 Ag t 2. Ag S;L Employee 4ame
3init
Lame
<<
B?ate
Address
?epo
?epartment ?ame ?epo 3gr<<
3gr
?eptPLocations ?epo
?Location
#ro+ect #ame
#umber
#Location
?epo
:orksP9n E<<
#o
5ours
?ependent E<<
?ependentPame
B?ate
$elationship
1. $efer to the schema given above create all tables with primary foreign key not null default and check constraints and populate all the tables 7atleast 0 rows in each table8. '. pdate the relation :orksP9n change the datatype of hours attribute from char to integer.
0. pdate the relation ?epartment change 3gr
Assignment . (E6a*ati6e Assignment Ma! 10) Lab and Date" ,ee 7" 27 Ag t 02 Se S;L
(onsider the following schema definitions; B!anh (b!anh&= st!eet= ity= stde) Sta (sta&= &ame= sitin= se>= DOB= sa*a!y= b!anh&) '!e!ty!#ent (!e!ty&= st!eet= ity= stde= tye= !ms= !ent= ?ne!&= sta&= b!anh&) C*ient (*ient&= &ame= te*&= !e5ye= ma>#ent) '!i6ateO?ne! (?ne!&= &ame= add!ess= te*&) @ie?ing (*ient&= !e!ty&= 6ie?Date= mment) #egist!atin (*ient&= b!anh&= sta&= date/ined)
I. ,iven the following schema definitions create the tables and include constraints 1. '. ). *.
#rimary key and foreign key. By default view?ate is M<>
II. :rite <L for the given =ueries; •
List the name of staff who work in the branch whose street address is M1F) 3ain
•
street2. 4or each branch list the branch numberstaff number and staff name who manage properties including the city in which the branch is located and the properties that the staff manage. ,ive all 3anagers 0N hike to their salary.
•
4ind the number of different staff whose ?9B is 2!1%4eb%1KK12 and whose name
•
starts with MA2. 4or each branch office with more than one member of staff find the total number of staff working in a branch with average of their salaries.
Assignment 7 ('!atie Assignment) Lab and Date" ,ee " 17Se - Se t 23 Se S;L Stdent(sid=sname=se>=age=yea!=ga) Det(dname=nmhds) '!(name=dname) C!se(n=dname=name) Ma!(dname=sid) Setin(dname=n=setn=name) En!**(sid=dname=n=setn=g!ade)
I.,iven the following schema definitions create the tables and include constraints 1. #rimary key and 4oreign key. '. By default grade is M42. II. :rite <L for the given =ueries; •
?isplay the name of #rofessors who work in the department that have fewer than 0!
• •
#h? students. ?isplay the name of students with lowest (,#A. 4or each ?atabase (ourse print the course number section number and average
•
(,#A of students enrolled in the class. $etrieve the name and section number of all classes with more than si/ students
•
enrolled in them. ?isplay the name of department that have one or more ma+ors who are under 1J
•
year old. $etrieve the name of students who are taking both (omputer
•
3athematics (ourse. 4or those department that have no ma+or taking a (omputer ,raphics (ourse
•
dispaly the department name and number of #hd2s in the department. ?ispaly the sidnameand (,#A of students who are currently taking all (ivil Engineering (ourses.
(LAB 5ES5 1) Lab and Date" ,ee 4" 29 Se 30 Se Sy**abs" S;L
Assignment ('!atie Assignment) Lab and Date" ,ee 10" 01 Ot 07 Ot S;L I 4or the following 3ovies database schema
3ovies7Title >ear filmType ?irectedBy8 ear ear 9scarAwardType AwardedTo8 Bo/9ffice7Title >ear (ity ,rossear $eviewer ear8 form a key in the 3ovies relation. The ear8. The 9scars relation has a tuple for 9scar awarded to a movie eg the Best Actor Best Actress and Best ?irector awards. The Bo/9ffice relation records for each movie its gross sales at the bo/ office for each city. Also each movie is rated at a scale of 1%1! by many reviewers. The rating information is kept in the relation $atings. 7i8 (reate the above relations with key and other constraints. 7ii8 Insert relevant tuples in the relations. 7iii8 :rite <L statements to retrieve the following R
R
R
R
R
R
the movie that has the ma/imum Total
II
7i8 sing <L depno c.
:5E$E departments.depno workfor.depno ,$9# B> depname d. depno e. one of the above.
V
Assignment 4 ('!atie Assignment) Lab and Date" ,ee 11" 0 Ot 19 Ot S;L and &!ma*iatin
7a8 The university keeps track of each studentQs name 7<A3E8 student number 7<38 social security number 7<<<8 current address 7<(A??$8 and phone 7<(#59E8 permanent address 7<#A??$8 and phone 7<##59E8 birthdate 7B?ATE8 se/ 7EA$8 course 7imm nmbe! *asses< #et!ie6e the g!ade *ist a** stdents ! eah dea!tment< ! die!ent !ses dis*ay the name !se ! ?hih the !edit and *e6e* ne !se is g!eate! than the !edit and *e6e* anthe! !se<
Assignment 10 ('!atie Assignment) Lab and Date" ,ee 12 : 13" 23 Ot 01 &6 '!et 'hase II E6a*atin and 'LS;L
#ractice the given code and rectify the error if any; .1 (onsider the given table 51 and e/ecute the #L6<L codes given below and check for the output identify the error if any
7a8
e
1
)
'
*
DECLARE
a NUMBER; b NUMBER; BEGIN
SELECT e, f INTO a, b FROM T1 WHERE e>1; IF b=1 THEN INSERT INTO T1 VALUES ( b, a ); ELSE INSERT INTO T1 VALUES ( b+10, a+10 ); EN IF; END;
7b8 DECLARE
a NUMBER; b NUMBER; BEGIN
SELECT e, f INTO a, b FROM T1 WHERE e>1; IF b=1 THEN INSERT INTO T1 VALUES ( b, a ); ELSE INSERT INTO T1 VALUES ( b+10, a+10 ); EN IF; END;
7c8 DECLA#E i 3BE$ ; 1 BE$I& L99# I#E b T1.fNT>#E ($<9$ 51C!s! I<
todaysPdate date begin todaysPdate ;sysdate dbmsPoutput.putPline7QTodays date is;Q8 dbmsPoutput.putPline7todaysPdate8 end 7f8 declare
todaysPdate date currentPday varchar'7K8 begin todaysPdate ;sysdate %%e/tract day portion from current date and trim %%trailing blank spaces currentPday ;toPchar7todaysPdateQdayQ8 currentPday ;initcap7currentPday8 currentPday ;rtrim7currentPday8 if currentPday Q4ridayQ then dbmsPoutput.putPline7QToday is 4ridayQ8 elsif currentPday QsundayQ then dbmsPoutput.putPline7QToday is
7g8 declare n number;1! fact number;1 temp number begin temp;n while nX! loop fact;factUn n;n%1 end loop dbmsPoutput.putPline7Q4actorial ofQZZtempZZQisQZZfact8 end .' :rite #L6<L #rocedure for the generation of 4ibonacci series. .) :rite a #L6<L #rogram using cursor to display the second highest salary of an employee. $elation Employee is EM' 7eid ename salary dept8 (LAB 5ES5 II) Lab and Date" ,ee 19" 11 &6 1. &6 Sy**abs" 'LS;L
Assignment 11 (E6a*ati6e Assignment and '!et 'hase III E6a*atin) Lab and Date" ,ee 1 : 1." 1 &6 2 &6 'LS;L and '!et 'hase III E6a*atin Cnside! the gi6en !e*atins and ?!ite 'LS;L '!g!am ! the **?ing" EM' (EID= E&AME= '= BASICGSAL=&E5GSAL=H#A=DA=D&O) DE'5 (D&AME= LOC= D&O= S%'E#@ISO#= EID) ;<1 :rite a #L6<L block which accepts employee name basic and should display Employee name #4 and net salary. 5$A)1N of basic salary ?A10N of basic salary et salary basic W 5$A W ?A [ #4 If the basic is less than )!!! #4 is 0N of basic salary. If the basic is between )!!! and 0!!! #4 is N of basic salary. If the basic is between 0!!! and J!!! #4 is JN of basic salary. ;<2 :rite a #L6<L stored procedure to add a record into the department table 7dept8. >ou use three input parameters to pass the departmentQs columns 7?epartment number "?9& department name "?A3E& and department location "L9(&8 and use one output parameter to
check the status of the insert transaction. Also an e/ception is raised if any data type mismatch occurs at the time of insertion. ;<3 :rite a Trigger to automatically add Eid and display the department of employee in department table once an employee record is inserted into the employee table. ;<9 ?eclare a cursor to list the department name 7dname8 total number of employees 7ttemp8 total salary 7ttsal8 and average salary 7avsal8 for each department from the department table and employee table order by the department name. In the e/ception section add the "no data found& e/ception and display the error message.. Add the "9thers& e/ception to detect other problems.
