Lab Assignment Sheet- Odd SEM 2013Database Management System Lab (10B17CI372)Lab Coordinator: Megha RathiLAB GUIDELINES1. Every Batch will have one LAB per week. 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 0. If it is urgent, you can get it PRE-Evaluated by respective Lab teacher.2. Copied assignment or plagiarism would be dealt severely and would be straight awarded 0.3. The marks for particular evaluated assignment should be fed in that particular week by the teacher. If your marks are not entered in the evaluation week, it would be automatically filled as 0.4. Projects: Total 3 evaluations will be there by respective lab teachers. Each batch would be given a domain in which groups in that batch will do projects on different topics lying in that domain. Group size min=3 max=4. Intra batches not allowed.5. Projects would be thin client projects, more emphases would be on oracle, Pl Sql code. Projects made in Access, GUI based editors net beans and xampp etc are strictly prohibited and would be awarded 0.

Assignment 1 (Practice Assignment)Practice Lab and Date: Week 1 : 16 Jul to 22 Jul

Scenario: An efficient computerized 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 Management system using the concepts of File Handling.Functional components of the system:A typical library management system includes the following functions/modules to process library operation.(a) Cataloging(b) Circulation 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 Check in Check out Renewal (c) AdministrationThis function allows the librarian to define system parameters (i.e. fine calculation, renewal procedure), manage and maintain any system setting. (d) OPAC/Web OPACOPAC is Online Public Access Catalog. It is the module to provide a public interface for readers to search the library catalog. Web OPAC is the web version of OPAC to allow readers to access the catalog via Internet. Other considerable features of this module are: Searching methods Record display Online application if any

Problem statementWrite a menu-driven program in C that uses a file to store the above mentioned component of library management system and perform the listed operations. To display all the records in the system. To add/drop record from the system. Search any book within the system. Fine calculation if book is deposited after due date.

Assignment 2 (Practice Assignment)Practice Lab and Date: Week 2: 23 Jul to 29 JulMS-Access

Task I: Follow the instructions given in Reference Sheet I (//studymaterial//Ms Access) create new database, new tables in Ms Access also design queries as per given in the reference sheet.

Task II:Scenario: You 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 Microsoft Access database that will support recording sales transactions. As a proof of concept, you will create some sample forms and queries that will demonstrate how this application might function as a decision support system for the Sales and Marketing Departments. Perform the following task listed below: Create Tables1. Product(pnumber, desc, price)2. Customer(custno, cname, address, city, state, zip)3. Order(Ono, custno, pnumber, saleamt, orddate, payment, cardtype) Insert appropriate data into the three tables (atleast 5 rows). After you have entered the data into the three tables, create three reports for displaying the table data. Form Relationships between tables. Print the queries results that answer the following questions:1. List the name of all customers who makes order on 10 July 2013.2. List the customer information (all fields) for customers who purchased a Halfpipe snowboard. 3. List the customer number for customers who purchased a snowboard using a Debit card or Credit Card.4. List the name of customers who buy product whose price is greater than $1000.

Assignment 3 (Evaluative Assignment Mark 10) Lab and Date: Week 3 : 30 Jul to 05 AugMS-Access

Your new job is assistant to the manager of the Noise works Music 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:BAND NAMEMEMBERSPRICEMUSIC TYPENOTES

HIGH IN DRY4500ROCK & ROLLweddings, balls, dances preferred

SQUARE DANCE TRIO3650COUNTRY & WESTERNprefers C&W gigs and concerts suitable for weddings,

BLUE SOX QUARTET4400JAZZjazz festivals, dances. Prefers smaller gigs.

YOUNG AND PROUD5750ROCK & ROLLyoung band, suits concerts and school functions

MATTHEW AND SON3750ROCK & ROLLa young band, prefers gigs with a younger audience

1. Create a database file called Bands and a table called NOISEWRKS within the file, to contain the information above. 2. Enter the data for the bands. 3. Add one more band to your file: Flying Colors, 5 members costing $800. The band plays jazz and the notes in the file indicate that the band is suitable for dances and mature audiences. 4. Make these changes to the data in your file: High in Dry has increased its price to $550. Blue Sox has increased its price to $700. 5. Matthew and Son has split up.Remove details of this band from the file. 6. List all the bands that play jazz. Save the Query7. List all the Rock & Roll bands that cost less than $700. Save the Query.8. List all the band names and the price for bands which play Country & Western. Save the Query.9. List all the bands which play either Rock & Roll or jazz. Save the Query.10. How many bands cost more than $650? Save the query11. What is the average number of members in the jazz bands? Save the Query.12. Create and print a report for your file. Include in your report the band name, the price and the notes kept on each band, grouped by music type. Add your name, the assignment number, question number and date to the bottom right corner of the report. Format the report professionally on the page. 13. Print the report above for only those bands which have five members. 14. Also create the given two tables and populate with the appropriate data.1) Music Director (Director Name: string, Band Name: String, Number of Albums: Integer, Number of Hit Songs: Integer) and 2) Album (album identifier: integer, director name: string, number of songs: integer, album name: string)15. Specify the relationships also specify the primary and foreign keys for all the tables mentioned above.

Assignment 4 (Practice Assignment) Lab and Date: Week 4 & 5: 06 Aug to 19 AugSQL

I .Branch (branchNo, street, city, postcode)II. Staff (staffNo, fName,lName, position, sex, DOB, salary, branchNo)III. PropertyforRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo)IV. Client (clientNo, fName, lName, telNo, prefType, maxRent)V. PrivateOwner(ownerNo, fName, lName, address, telNo)VI. Viewing (clientNo, propertyNo, viewDate, comment)VII. Registration (clientNo, branchNo, staffNo, dateJoined) 1. Create the above relations including all primary and foreign key integrity constraint , with SQL.2. Add Tuples to all relations using INSERT statement.3. Add constraint that if city name is not provided in the Branch relation it takes default value Delhi.4. Add Constraint that salary of staff is greater than 10000.5. Write an SQL Statement to update the rent by 10% of the existing rent.6. Write SQL statment to dispaly all the tables stored in the database.7. Write SQL Statement to delete the XYZ Staff from the staff relation.8. Alter Relation Client, add a new column dob of type date.9. Alter Relation Viewing add constraint that attribute Comment will take default value NIL.10. Write SQL Statement to delete all the tuples of relation Registration but table will not be deleted from the database.

Assignment 5 (Practice Assignment) Lab and Date: Week 6: 20 Aug to 26 AugSQL

1. Refer to the schema given above create all tables with primary, foreign key, not null, default and check constraints and populate all the tables (atleast 5 rows in each table).2. Update the relation Works_On change the datatype of hours attribute from char to integer.3. List the name of employees whose salary is between 30000 and 50000.4. List the name of all employees who work in department 5.5. Update the relation Department change MgrSDate as default SYSDATE.6. List the name of all employees who work in dept no.2 and whose name starts with A.7. List the name of all Female employees.8. List the total salary of all employees who lives in Noida.

Assignment 6 (Evaluative Assignment Mark 10) Lab and Date: Week 7: 27 Aug to 02 SepSQL

Consider the following schema definitions:Branch (branchNo, street, city, postcode)Staff (staffNo, Name, position, sex, DOB, salary, branchNo)PropertyforRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo)Client (clientNo, Name, telNo, prefType, maxRent)PrivateOwner (ownerNo, Name, address, telNo)Viewing (clientNo, propertyNo, viewDate, comment)Registration (clientNo, branchNo, staffNo, dateJoined) I. Given the following schema definitions create the tables and include constraints1. Primary key and foreign key.2. By default viewDate is SYSDATE, and address is JIIT62 NOIDA3. Content of attribute comment is always unique.4. Null values are not allowed in the attribute name.II. Write SQL for the given queries: List the name of staff who work in the branch whose street address is 163 Main street. For each branch list the branch number,staff number and staff name who manage properties including the city in which the branch is located and the properties that the staff manage. Give all Managers 5% hike to their salary. Find the number of different staff whose DOB is 01-Feb-1991 and whose name starts with A. For 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 (Practice Assignment)Lab and Date: Week 8: 17Sep - Sep to 23 SepSQL

Student(sid,sname,sex,age,year,cgpa)Dept(dname,numphds)Prof(pname,dname)Course(cno,dname,cname)Major(dname,sid)Section(dname,cno,sectno,pname)Enroll(sid,dname,cno,sectno,grade)I.Given the following schema definitions create the tables and include constraints1. Primary key and Foreign key.2. By default grade is F.

II. Write SQL for the given queries: Display the name of Professors who work in the department that have fewer than 50 PhD students. Display the name of students with lowest CGPA. For each Database Course print the course number, section number and average CGPA of students enrolled in the class. Retrieve the name and section number of all classes with more than six students enrolled in them. Display the name of department that have one or more majors who are under 18 year old. Retrieve the name of students who are taking both Computer Science Course and Mathematics Course. For those department that have no major taking a Computer Graphics Course dispaly the department name and number of Phds in the department. Dispaly the sid,name,and CGPA of students who are currently taking all Civil Engineering Courses.

(LAB TEST 1)Lab and Date: Week 9: 24 Sep 30 SepSyllabus: SQL

Assignment 8 (Practice Assignment)Lab and Date: Week 10: 01 Oct 07 OctSQL

I For the following Movies database schema

Movies(Title, Year, filmType, DirectedBy) Stars(Title, Year, StarName, Salary)Oscars(Title, Year, OscarAwardType, AwardedTo) BoxOffice(Title, Year, City, GrossSales) Ratings(Title, Year, Reviewer, Score)

The Movies relation contains the list of all movies. The value of the filmType attribute may be 'horror', 'action', 'thriller' etc. (Title, Year) form a key in the Movies relation. The Salary attribute in the Stars relation is the amount paid to StarName for working in the movie: (Title, Year). The Oscars relation has a tuple for Oscar awarded to a movie eg the Best Actor, Best Actress, and Best Director awards. The BoxOffice relation records for each movie its gross sales at the box office for each city. Also, each movie is rated at a scale of 1-10 by many reviewers. The rating information is kept in the relation Ratings.

(i) Create the above relations with key and other constraints. (ii) Insert relevant tuples in the relations.(iii) Write SQL statements to retrieve the following

the movie that has the maximum TotalSalaryCost (total salary paid to stars).total gross sales of all movies that won some Oscar award in the 1960s.for each director, the total number of his/her movies that have won an Oscar.for each star, the total number of movies for which he/she has won a BestActor/Actress Oscar AND that have won at least two Oscars.the list of all directors who received the "Best Director" award, even though the movie for which they received the award had an average rating of less than 5.Write an UPDATE statement that changes the filmType of a movie that has won an Oscar to 'OscarWinner'.

II

(i) Using SQL Scenario above, which of the following SQL queries gives a list of departments and their employees?a. SELECT depname,empnameFROM departments, employeesWHERE departments.depno = employees.empno;b. SELECT depname,empnameFROM departments, employees, workfor WHERE departments.depno = workfor.depno AND workfor.empno = employees.empno;c. SELECT depname,empnameFROM departments, workforWHERE departments.depno = workfor.depno;d. SELECT depno,empnoFROM workfor;e. None of the above.

(ii) which of the following SQL queries gives the number of employees in each department?a. SELECT depname,COUNT(depno) FROM departments, employees, workfor WHERE departments.depno = workfor.depno AND workfor.empno = employees.empno;b. SELECT empno,COUNT(*) FROM departments, workforWHERE departments.depno = workfor.depnoGROUP BY depno;c. SELECT depname,COUNT(empno) FROM departments, workfor

WHERE departments.depno = workfor.depnoGROUP BY depname;d. SELECT depname,COUNT(empno) FROM departments, employees, workfor WHERE departments.depno = workfor.depno AND workfor.empno = employees.depno GROUP BY depno

e. None of the above.

\

Assignment 9 (Practice Assignment)Lab and Date: Week 11: 08 Oct 14 OctSQL and Normalization

Suppose we have the following requirements for a university database that is used to keep track of students transcripts:(a) The university keeps track of each student's name (SNAME), student number (SNUM), social security number (SSSN), current address (SCADDR) and phone (SCPHONE), permanent address (SPADDR) and phone (SPPHONE), birthdate (BDATE), sex (SEX), class (CLASS) (freshman, sophomore, ..., graduate), major department (MAJORDEPTCODE), minor department (MINORDEPTCODE) (if any), and degree program (PROG) (B.A., B.S., ..., Ph.D.). Both SSSN and student number have unique values for each student.(b) Each department is described by a name (DEPTNAME), department code (DEPTCODE), office number (DEPTOFFICE), office phone (DEPTPHONE), and college (DEPTCOLLEGE). Both name and code have unique values for each department.(c) Each course has a course name (CNAME), description (CDESC), code number (CNUM), number of semester hours (CREDIT), level (LEVEL), and offering department (CDEPT). The value of code number is unique for each course.(d) Each section has an instructor (INSTUCTORNAME), semester (SEMESTER), year (YEAR), course (SECCOURSE), and section number (SECNUM). Section numbers distinguish different sections of the same course that are taught during the same semester/year; its values are 1, 2, 3, ...; up to the number of sections taught during each semester.(e) A transcript refers to a student (SSSN), refers to a particular section, and grade (GRADE).Design a relational database schema for this database application. First show all the functional dependencies that should hold among the attributes. Then, design relation schemas for the database. Create Normalized (upto III NF) relations in SQL and add primary and foreign key constraints and answer the following queries: Display the name of students whose grade is A+ for the Database course. Display the course name run at the same time for two different departments. For each department display the Instructor name taking maximum number of classes. Retrieve the grade list of all students for each department. For different courses display the name of course for which the credit and level of one course is greater than the credit and level of another course.

Assignment 10 (Practice Assignment)Lab and Date: Week 12 & 13: 23 Oct 01 NovProject Phase II Evaluation and PL/SQL

Practice the given code and rectify the error, if any:Q.1 Consider the given table T1 and execute the PL/SQL codes given below and check for the output, identify the error if any;ef

13

24

(a) 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 ); END IF; END; (b)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 ); END IF; END;

(c)DECLARE i NUMBER := 1; BEGIN LOOP INSERT INTO T1 VALUES ( i, i ); i := i+1; EXIT WHEN i>100; END LOOP; END;

(d)DECLARE a T1.e%TYPE;b T1.f%TYPE; CURSOR T1Cursor IS SELECT e, f FROM T1 WHERE e < f FOR UPDATE; BEGIN OPEN T1Cursor; LOOP FETCH T1Cursor INTO a, b; EXIT WHEN T1Cursor%NOTFOUND; DELETE FROM T1 WHERE CURRENT OF T1Cursor; INSERT INTO T1 VALUES ( b, a ); END LOOP; CLOSE T1Cursor; END; (e) declare todays_date date; begin todays_date :=sysdate; dbms_output.put_line('Todays date is:'); dbms_output.put_line(todays_date); end;

(f) declare todays_date date; current_day varchar2(9); begin todays_date :=sysdate; --extract day portion from current date and trim --trailing blank spaces current_day :=to_char(todays_date,'day'); current_day :=initcap(current_day); current_day :=rtrim(current_day); if current_day = 'Friday' then dbms_output.put_line('Today is Friday'); elsif current_day = 'sunday' then dbms_output.put_line('Today is Sunday'); elsif current_day ='Tuesday' then dbms_output.put_line('Today is Tuesday '); else dbms_output.put_line('current day not found'); end if; end;

(g) declare n number:=10; fact number:=1; temp number; begin temp:=n; while n>0 loop fact:=fact*n; n:=n-1; end loop; dbms_output.put_line('Factorial of'||temp||'is'||fact); end;

Q.2 Write PL/SQL Procedure for the generation of Fibonacci series.Q.3 Write a PL/SQL Program using cursor to display the second highest salary of an employee. Relation Employee is EMP (eid, ename, salary, dept)

(LAB TEST II)Lab and Date: Week 14: 11 Nov 16 NovSyllabus: PL/SQL

Assignment 11 (Evaluative Assignment and Project Phase III Evaluation)Lab and Date: Week 15 & 16: 18 Nov 28 NovPL/SQL and Project Phase III Evaluation

Consider the given relations and write PL/SQL Program for the following:EMP (EID, ENAME, PF, BASIC_SAL,NET_SAL,HRA,DA,DNO)DEPT (DNAME, LOC, DNO, SUPERVISOR, EID)

Q.1 Write a PL/SQLblock which accepts employee name, basic and should displayEmployee name, PF and net salary.HRA=31% of basic salaryDA=15% of basic salaryNet salary= basic + HRA + DA PFIf the basic is less than 3000 PF is 5% of basic salary.If the basic is between 3000 and 5000 PF is 7% of basic salary.If the basic is between 5000 and 8000 PF is 8% of basic salary.

Q.2 Write a PL/SQL stored procedure to add a record into the department table (dept). You use three input parameters to pass the department's columns (Department number DNO, department name DNAME, and department location LOC); and use one output parameter to check the status of the insert transaction. Also an exception is raised if any data type mismatch occurs at the time of insertion.

Q.3 Write 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.

Q.4 Declare a cursor to list the department name (dname), total number of employees (ttemp), total salary (ttsal), and average salary (avsal) for each department from the department table and employee table order by the department name. In the exception section, add the no data found exception and display the error message.. Add the Others exception to detect other problems.

FName

Minit

LName

SSN

BDate

Address

Sex

Salary

SuperSSN

DepNo

FName

Minit

LName

SSN

BDate

Address

Sex

Salary

SuperSSN

DName

DepNo

MgrSSN

DName

DepNo

MgrSSN

MgrSDate

DepNo

DLocation

DLocation

PName

PNumber

PLocation

PName

PNumber

PLocation

ESSN

PNo

Hours

ESSN

PNo

Hours

ESSN

Dependent_Name

Sex

BDate

Relationship

ESSN

Dependent_Name

Sex

BDate

Relationship

Employee

Department

Dept_Locations

Project

Works_On

Dependent