Bca Science Slip Sem II C & Dbms Feb2017

CONFIDENTIAL
Savitribai Phule Pune University

(Formerly University of Pune)
LOGO
Booklet of Practical Skeleton Papers for

B.C.A (Science) Semester Pattern
(From April -2017)
F.Y.B.C.A (Science)
SEMESTER – II
LAB COURSE I & II

Instructions to College
Important to note about laboratory courses:

1. It is absolutely necessary and essential that all the
practical’s for lab I and II be conducted on windows
or open source operating system like Linux
2. Subject teacher/practical in charge should only be an
internal examiner
3. One section should be checked by each examiner
4. All the slips are to be used for every batch
5. Only one change of slip is allowed on the basis of
10% marks deduction out of total marks obtained.
LOGO

(From November-2016)
F.Y.B.C.A (Science) SEMESTER – II
BCA 205 : Lab Course-I
Advanced Programming in C
F.Y.B.C.A. (Science) Practical Examination. MAR / APR - 2017
Lab Course I
BCA205: Lab I: Advanced Programming in C
Duration: 3Hrs. Max Marks: 70+30=100
Q1. Write a macro to compute x raise to n. Write the c program that will use this macro to
computer x raise to n for given value of x and n.
[10]
Q2.Write a function slength( ) that will return the length of given string. Pass the string as
parameter to the function.
[20]
Q3. Write a function fcopy( ) which will have 2 filenames as parameters. Function will copy
source file to destination file.
[30]
Q4. External Viva [10]
Q5. Internal Viva [10]
Q6. Journal (10 Assignments. Each assignment-2 Marks) (Internal) [20]
Slip 1
Lab Course I
Q1. Write a macro MAX3 that will computer the greatest of given 3 numbers. Use this macro to
computer the greatest of given 3 numbers. [10]
Q2. Write a function replace(str, ch1, ch2) that will replace all the occurrences given character
ch1 by ch2 in a string str.
[20]
Q3. Write a program that will count the number of lines, characters and digits in given file.
[30]

Slip 2

Lab Course I
Q1. Write a ‘C’ program to read n integers in an array. Display all numbers in array on separate
line and also find their sum an average.
[10]
Q2. Write a Boolean function scomp( ) that will compare 2 strings. Pass two strings as
parameters to the function. Compare two strings by using this function.
[20]
Q3. Write a ‘C’ program to read a matrix of size n x n. Display it in proper format. Find its
transpose of matrix and also display it in proper format.
[30]

Slip 3

Lab Course I
Q1. Write a ‘C’ program to accept a string and count the number of vowels in it.
[10]
Q2. Write a ‘C’ program to read the matrix of size n x n. Display it in proper format along with
the sum of each row at the end of row.
. [20]
Q3. Write a ‘C’ program to read the student details such as Rno, Name, and marks of 3 subjects.
Read the details of n number of students. Display student details along with total, percentage
and class.
[30]

Slip 4

Lab Course I
Q1. Write a macro CUBE that will computer the cube of given number. Using this macro find the
cube of given number.
[10]
Q2. Write a ‘C’ program to find the length of a given string without using library function.
. [20]
Q3. Write a ‘C’ program to accept the book details such as BookID, Tital, Author, Price. Read the
details of n number of books. Display the details of those books which are priced above
Rs.100
[30]

Slip 5

Lab Course I
Q1. Write a macro MIN3 that will computer the minimum of 3 numbers. Using this macro find
the smallest of given 3 numbers.
[10]
Q2. Write a ‘C’ program to accept two strings. Join them without using any library function.
Display all 3 strings.
[20]
Q3. Write a ‘C’ program that will create a text file by given name. Display the contents of same
file.
[30]

Slip 6

Lab Course I
Q1. Write a ‘C’ program to read n integers in an array. Display odd integers on one line and even
on another line.
[10]
Q2. Write a program to read n number of strings. Find the length of smallest and largest string.
[20]
Q3. Write a ‘C’ program to accept file name from command prompt. Display the contents of that
file along with its size.
[30]

Slip 7

Lab Course I
Q1. Write a macro ISODD that will computer the given number is odd or not. Using this macro
find the given number is odd or even.
[10]
Q2. Write ‘C’ program accept n integers from user. Place odd integers in on array and even in
another. Finally display both the arrays.
[20]
Q3. Write a ‘C’ program accept employee details such as EmpID, Name BasicSal. Read the
details of n number of employees. Display the employee details along with DA, HRA and
Gross Salary. The DA is 113% of Basic and DA is 15% of Basic if Basic is greater than
25000 and otherwise 10%
[30]

Slip 8

Lab Course I
Q1. Write a macro AREA that will computer the area of circle. Using this macro find the area of
circle for given radius.
[10]
Q2. Write a function getavg( ) that will return the average of sum of the integers in given array.
Pass array as parameter to the function.
[20]
Q3. Write a ‘C’ program to read the voters details such as VoterId, Name, BoothNo, City,
Mobilno. Read the details of n number of voters. Display the details of the voters in given
booth number.
[30]

Slip 9

Lab Course I
Q1. Write a macro VOLUME that will compute the volume a box. Using this macro find the
volume of a box for given length, breadth and height.
[10]
Q2. Write a function that will return the middle letter in given string. Pass string as parameter to
the function.
[20]
Q3. Write a program to accept a file name from command prompt and display that file along with
number of vowels in it.
[30]

Slip 10

Lab Course I
Q1. Write a ‘C’ language program to accept n integer in an array. Display the array in reverse
order. Also display the sum of the elements in array.
[10]
Q2. Write a Program to accept the names of n number of cities. Accept a character from user and
display only those city names which begin with given character.
[20]
Q3. Write a ‘C’ language program to accept the material details as ItemID, Name, Quantity and
Rate. Read the details of n number of items. Display the details of all the items along with
total amount ( qty X rate). Also display the total valuation of all the items.
[30]

Slip 11

Lab Course I
Q1. Write a program to accept characters in array until the given character is ‘$’. Display the
string.
[10]
Q2. Write a function getsum( ) that will return the sum of first, last and middle numbers in given
array. Pass array of integers as parameters to the function.
[20]
Q3. Write a program to read the sequence of integers from user and write them in file until the
given number is -999. Display the numbers in that file. Also count the number of odds and
evens.
[30]

Slip 162

Lab Course I
Q1. Write a macro CALC that will compute the circumference of a circle. Using this macro find
the circumference for the given radius.
[10]
Q2. Write a function swap(x,y) that will interchange the values of given variables x and y. Write
a program that will accept 2 numbers and interchange their values by using the function
swap. Print the values before and after the swap in main function.
[20]
Q3. Write a program that will accept the file name and a string from user and display only those
lines from the file which contains the given patters. Also display the number of lines on
which the pattern was found.
[30]

Slip 13

Lab Course I
Q1. Write a program to accept a string and count the number of uppercase and lowercase letters
in it.
[10]
Q2. Read the matrix of size n x n. Display it in proper format. Also find the sum of its diagonal
elements and display it.
[20]
Q3. Write a program to accept a file name from user. Also read 2 characters. Create new file in
which all occurrences of character ch1 are replaced by ch2. Open this newly created file and
display it s contents.
[30]

Slip 14

Lab Course I
Q1. Write a program to accept a string and count the number of alphabets, digits in it.
[10]
Q2. Write a program that will read the strings until the given string is “Good Bye”. Display each
string on separate line along with its length.
[20]
Q3. Write a program to accept the student details such as Rollno, Name, Category and Class.
Read the details of n number of students. Display the count of students in each category for
a given class name.
[30]

Slip 15

Lab Course I
Q1. Write a macro SIGN that will check given number is positive or negative. Using this macro
display given number is positive or negative.
[10]
Q2. Write a program to accept a string. Write a function to which pass that string as parameter.
The function will replace all the blank spaces by ‘$’ sign. Display the string after
replacement in main function.
[20]
Q3. Write a program to accept a file name from user open that file for reading. Write function
fcount(fp,flag) that will count the characters depending on the value of flag.
If status is 1 function will return the number of alphabets.
If status is 2 function will return the number of digits.
If status is 3 function will return the number of whitespaces.
Other wise it will return the count of number of other letters in given file.
[30]

Slip 16

Lab Course I
Q1. Write a ‘C’ program to accept a string and display each word on separate line.
[10]
Q2. Write a ‘C’ function scompare( ) that allows to compare 2 strings and accordingly it will
return the value true(1) or false(0). Pass 2 strings as parameter to the function. Do not use any
built in function
[20]
Q3. Write a program to read a text file and display only those lines which contains some digit in
it. Also count number of such lines.
[30]

Slip 17

Lab Course I
Q1. Write a program to read sequence of integers in an array. Display the array elements and sum
of odd and even integers in an array.
[10]
Q2. Write a function that will return the count of positive, negative numbers in given array. Pass
array as parameter to the function.
[20]
Q3. Write a menu driven program to perform following operations on strings by using standard
library functions.
1. Read 2 Strings 2. Display Strings 3) Length
4. Copy 5. Compare 6) Concatenate 7) Exit
[30]

Slip 228

Lab Course I
Q1. Write a ‘C’ program to accept a string and count number of words in it.
[10]
Q2. Write a ‘C’ to open a text file and display it in reverse with its size.
[20]
Q3. Define the functions readmatrix() and showmatrix() that will read or display the given
matrix in proper format. Pass 2-D array as parameter to these functions. Read two matrices
using this function. Perform their addition and also display all the matrix by using function.
[30]

Slip 19

Lab Course I
Q1. Write a ‘C’ program to read n numbers in an array. Display only the number at odd index
positions.
[10]
Q2. Write a function that will return count of whitespaces in a given string. Using this function
write a program that will count the number of white spaces in given string.
[20]
Q3. Write a function getsum( ) that will return the sum of the integers in given matrix.
Read to matrices and find the difference of their sum. Also display both the matrices in
proper format.
[30]

Slip 20

Lab Course I
Q1. Write a ‘C’ program to read n integers in an array. Display the contents of an array by using
pointer and pointer arithmetic.
[10]
Q2. Write a function changecase( ) that will change the case of the letter in given string. Pass the
string as parameter to the function.
[20]
Q3. Write a program to accept the customer details such as CustID, Name, City, Phone, Email.
Read the details of n number of customers. Write a function that will display the details of
particular customer. Pass customer ID as parameter to this function.
[30]

Slip 21

Lab Course I
Q1. Write a Program that will dynamically allocate the memory for an integer, float and
character. Read values in it and display them.
[10]
Q2 Write a Program to read the string until the given character is ‘^’. Display the string. Also
count the vowels in it.
[20]
Q3. Write a program that will accept the 4 file names and add the contents of first 3 files into 4th
file. Display 4th file.
. [30]

Slip 22

Lab Course I
Q1. Write a macro CALC that will compute the circumference of a circle. Using this macro find
the circumference for the given radius. [10]
Q2. Write a program that will read the strings until the given string is “NoMore”. Display each
string on separate line along with its length.
[20]
Q3. Write a program to accept a file name from user. Also read 2 characters. Create new file in
which all occurrences of character ch1 are replaced by ch2. Open this newly created file and
display it s contents. [30]

Slip 23

Lab Course I
Q1. Write a ‘C’ program read and print the array of integers using pointers.
[10]
Q2. Write a program to declare and initialize 2 matrices of size 3 x 5. Display them on screen in
proper format along with their addition.
[20]
Q3. Write a program that will accept the list of file names from command prompt and display all
files one after another.
[30]

Slip 24

Lab Course I
Q1. Write a Program to accept any character. Check that it is a alphabet and if it is in upper case
the display it in lower case but if it is in lower case then display it in upper case. [10]
Q2. Write a program that will read a string. If ASCII value of any character in string is between
97 to 122 then subtract 32 from it. Display new string.
[20]
Q3. Write program to accept a file name and string pattern from command prompt. Display only
those line in a file which contains the given pattern.
[30]

Slip 25

Lab Course I
Q1. Write a macro MAX3 that will computer the greatest of given 3 numbers. Use this macro to
computer the greatest of given 3 numbers.
[10]
Q2. Write a Program to read a string and copy it in reverse order into another string. Print both
the strings.
[20]
Q3. Write a program to create a file of integers. Write integers in file until the given number is
greater not -9999. Display the file contents and the sum of the integers.
[30]

Slip 26

Lab Course I
Q1. Write a macro that will generate the code to make the sum of given 3 integers.
[10]
Q2. Write a program to read the matrix of size 7 x 7. Display it in proper format. Count the
number of negative values on each row and display the counts.
[20]
Q3. Write a program to read the array of n integers. Copy the address if each element in an array
to another array. Display the contents of an array integers by using the array of addresses.
[30]

Slip 27

Lab Course I
Q1. Write a Program read the n integers in an array. Find and display the sum of first an lat 3
integers in an array. Read at least 4 integer in an array.
[10]
Q2. Write a function strupper( ) that will convert the given string to upper case. Do not use any
library function. Pass string as parameter to the function.
[20]
Q3. Write a ‘C’ program to accept the book details such as BookID, Tital, Author, Price. Read the
details of n number of books. Display the details of those books which are priced below
Rs.100
[30]

Slip 28

Lab Course I
Q1. Write a program to read a string and find its length without using any library function.
[10]
Q2. Write a Program to accept n integers in an array. Pass array and a flag having value ‘F’ or ‘R’
to the function. The function will display the contents of an array in forward direction if flag
value is ‘F’. Display it in reverse order if flag value is ‘R’.
[20]
Q3. Write a program to accept 2 file names from command prompt. Copy the contents of first
file to second file.
[30]

Slip 29

Lab Course I
Q1. Write a macro ALLOCINT which can be used to allocate the memory for integer.
[10]
Q2. Write a Boolean function scomp( ) that will compare 2 strings. Pass two strings as
parameters to the function. Compare two strings by using this function.
[20]
Q3. Write a program to read the matrix of size 5 x 5. Write a function that will computer the sum
of each row and store it in one-D array of size 5. Pass matrix and one-D array as parameters
to the function. Display the sum of each row in main function.
. [30]

Slip 30

LOGO

(From April-2017)
F.Y.B.C.A (Science) SEMESTER – II
BCA 206: Lab Course-II
Relational Database Management System

Instructions to College
Important to note about laboratory courses:

1. It is absolutely necessary and essential that all the
practical’s for lab I and II be conducted on windows
or open source operating system like Linux
2. Subject teacher/practical in charge should only be an
internal examiner
3. One section should be checked by each examiner
4. All the slips are to be used for every batch
5. Only one change of slip is allowed on the basis of
10 % marks deduction out of total marks obtained.
6. Internal Evaluation for 30 Marks is –
a) Internal Viva: - 10 Marks
b) Journal (Internal):- 20 Marks
(10 Assignments. Each Assignment: - 2 Marks)

Lab Course II
BCA206: Lab II: Relational Database Management System

Practical Question on PostGreSQL (Total Marks 70)
Note:- 1. Insert sufficient number of appropriate records in the database.
2. Read the question carefully and insert data in the database accordingly.
3. Count queries output should be more than 2 records. (if asked)
4. No question should generate empty output.
Q.1) Create the following relations (Primary keys are underlined): [Total Marks: 25]
Sales_order (s_order_no , s_order_date)

Customer (cid, name, caddress, city, state)
Distributer (id, name, address, contactno)
A client can give one or more sales_orders, but a sales_order belongs to exactly one client. A
distributer can give one or more orders.
a) Draw E-R diagram for given relational schema and show normalization on paper. [10]
b) Create database in normalized form (3NF). [10]
c) Insert 5 records into each table [5]
Q 2.Create the following tables (Primary keys are underlined) [Total Marks 35]
Employee (emp_no, emp_name, address, city, birth_date, designation, salary)
Project (project_no, project_name, status)
Department (Dept_no, dept_name, location)
Constraints: Designation of an employee is ‘Manager’, ‘worker' or ‘clerk’.
Salary should be greater than zero.
There exists a one-to-many relationship between Department and Employee. Many
employees can work on many projects controlled by a department. Create the relations
accordingly, so that the relationship is handled properly and the relations are in normalized
form (3NF) and insert 5 records into each table. Draw E-R diagram for given relational
schema and show normalization. [20]
Solve the following queries:
a) Increase salary of “managers” by 10%. [5]
b) List details of Projects. [5]
c) List names of employee(s) with maximum salary. [5]
Q3.Viva [10]
Slip 1

Lab Course II

Q 1. Create the following tables (Primary keys are underlined) [Total Marks 25]
Customer (cust_no , cust_name , cust_address , cust_city)

Account (acc_no , acc_type , balance)
Loan (loan_no , loan_amt , no_of_years )
There exists a ternary relationship between Customer, account and Loan.
a) Draw E-R diagram for given relational schema and show normalization. [10]
c) Insert 5 records into each table. [5]
Employee (emp_no, emp_name, address, city, birth_date, designation)

Project (project no, project_name, status)
Constraints: 1. Employee name should be in uppercase.
2. Projects are of type inventory, IT, manufacturing.
schema and show normalization [20]
a) Add column salary in the employee table and check salary is in between 23000 to 56000.
[5]
b) List the names of employees working on inventory project. [5]
c) List the details of employees who are from CS department. [5]
Q3.Viva [10]
Slip 2

Lab Course II

Q 1. Create the following tables (Primary keys are underlined) [Total Marks25]
Property (Property_no, description, area)

Owner (owner_name, address, phone)
District (dno , dname )
An owner can have one or more properties, but a property belongs to exactly one owner.
A district can have one or more properties in it.
Employee (emp_no, emp_name, address, city, birth_date, designation,salary)
Project (project_no, project_name, status)
Constraints: Employee designation is either ‘manager’, ‘staff’ , ‘worker’.
schema and show normalization [20]
a) Find the details of employee who is having highest salary. [5]
b) Delete all employees of department 20. [5]
c) List the names and salary of employees sorted by their salary. [5]
Q3. Viva [10]
Slip 3

Lab Course II


Doctor (doctor_code,doctor_name)
Patient (Patient_code, Patient_name)
Doctor and patient are related with many to many relationship.
Bus (bus_no, capacity, depot_name)
Route (route_no, source, destination, no_of_stations)
Driver (driver_no, driver_name, license_no, address, age, salary)
Constraints: License number must be unique.
Relationships : Route - Bus : 1 to Many.
Bus - Driver : Many to Many with descriptive attribute shift(‘morning’ or ‘evening’) and date of
duty.
Create the relations accordingly, so that the relationship is handled properly and the relations are
in normalized form (3NF) and insert 5 records into each table. Draw E-R diagram for given
relational schema and show normalization. [20]
a) List buses with capacity greater than 20. [5]
b) List driver details with maximum salary. [5]
c) List the details of all drivers working on _____ date. [5]
Q3. Viva [10]
Slip 4

Lab Course II

Game (g_no , g_name,no_of_players,coach_name,captain)
Players (p_no , p_name)
There exists a many-to-many relationship between Game and Players.
Constraints: 1) Bus capacity is not null. 2) Salary is greater than zero.
Relationships: Route - Bus: 1 to Many.
Bus - Driver: Many to Many with descriptive attribute shift (‘morning’ or ‘evening’) and date of
duty.
1) Update salary of driver by 15 %. [5]
2) Give count of drivers working in evening shift. [5]
3) List details of all of drivers whose name starts with ‘___’ [5]
Q3.Viva [10]
Slip 5

Lab Course II

Musician(m_no, m_name,age,m_city)
Instrument(i_no , i_name , company_name)
There exists a many-to-many relationship between Musician & Instrument.
Constraints: 1) Bus capacity is not null. 2) Salary is greater than zero.
Relationships: Route - Bus: 1 to Many.
Bus - Driver: Many to Many with descriptive attribute shift (‘morning’ or ‘evening’) and date of
duty.
1) Change the capacity to 55 of buses having route number 23; [5]
2) Find average salary of drivers. [5]
3) List the details of buses with source ‘shivajinagar’ & destination ‘sangvi’. [5]
Q3. Viva [10]
Slip 6

Lab Course II

Doctor(docno, name, specialization)

Hospital(hospno, name, address , contactno)
Doctor and Hospital are related with many to many relationship.
Movie (movie_name, release_year, budget)
Actor (actor_name, role, charges, address)
Producer (producer_no, name, address, age)
Constraint: Budget is greater than zero & charges not null.
Many actors act in many movies. Many producers produce many movies. Create the relations
accordingly, so that the relationship is handled properly and the relations are in normalized form
(3NF) and insert 5 records into each table. Draw E-R diagram for given relational schema and
show normalization. [20]
a) List the movie name starting with ‘a’; [5]

b) Give count of movies whose budget is greater than ______ [5]
c) List details of actors who have acted in movie _____ [5]
Q3. Viva [10]
Slip 7

Lab Course II

book (bid , bname , bprice , publication)
author(ano , aname , acity , qualification)
book and author is related with many to many relationship.
Constraints: Budget is greater than zero & charges are not null.
a) List the actor name and release year of movie in which they have acted. [5]
b) Find details of oldest producer. [5]
c) Find all details of movies except which are released after 2014. [5]
Q3. Viva [10]
Slip 8

Lab Course II

Q1. Create the following tables (Primary keys are underlined) [Total Marks25]
Doctor (docno, name, specialization)

Hospital (hospno, name, addr , contactno)
Patient (pid, name , age , address )
Doctor and Hospital are related with many to many relationship. Doctor and patient are related
with one to many relationship.
Constraint: Budget is greater than zero & charges are not null.
a) Find all details of movies who are either released in a year 2014 or budget is greater
than Rs.________. [5]
b) List details of the actor with highest charges. [5]
c) List Movie names produced by producer ______ . [5]
Q3. Viva [10]
Slip 9

Lab Course II

Car (car_code, c_name, c_price , color , model)

Customer (cust_code, cust_name, cust_address , lic_no )
Company (cname, address, city)
There exists a many-to-many relationship between car and customer. Company and car are
related with one to many relationship.
Student (student_ no, student_name, address, status, class)
Teacher (Teacher_no, teacher_name, address)
Subject (subject_no, subject_name)
Constraints: Status should be Pass or Fail.
There exists a one-to-many relationship between teacher and subject. Student and subject are
related with many to many relationship with descriptive attribute marks. Create the relations
a) Find details of all teachers who teaches either ‘Database’ or ‘c’ subject. [5]
b) Find the student name having highest marks. [5]
c) List teacher details sorted by their name. [5]
Q3. Viva [10]
Slip 10

Lab Course II

Q.1) Create the following relation (Primary keys are underlined): [Total Mark: 25]
Consider the following Entities and Relationships

Employee (empno, empname, salary, commission, designation)
Department (deptno, deptname,location)
Relationship between Employee and Department is many-to-one.
b) Create database in normalized form.(3NF) [10]
Q.2) Create the following tables(Primary keys are underlined): [Total Mark:35]
Customer (custno, custname, custaddress)

account (actno, acctype, balance)
Relationship between Customer and account is one-to-many.

Constraints: balance should be > 100
in normalized form(3NF) and insert 5 records into each table. Draw ER diagram for given
a) List the details of customer whose account type saving. [5]
b) List the details of account whose balance between 4000 and 6000. [5]
c) List the details of customer with maximum account balance. [5]
Q.3) Viva [10]
Slip 11

Lab Course II


Employee (empno, empname, salary, commission, designation)
Department (deptno, deptname,location)
Relationship between Employee and Department is many-to-one.
b) Create database in normalized form. (3NF) [10]
Movie (mvno, mvname, releaseyear)

Actor (actno, actname)
Relationship between Movie and Actor is many-to-one.
Constraints: Primary Key,

releaseyear should be > 0.
in normalized form(3NF) and insert 5 records into each table.
Draw ER diagram for given relational schema and show normalization. [20]
Solve the following quesries:
a. Count all the movie names released in the year 2000. [5]
b. Display all actor details of movie ‘Dhoom’ [5]
c. Display all movies of ‘Abhishek’ [5]
Q.3) Viva [10]
Slip 12

Lab Course II

Q.1) Create the following relation(Primary keys are underlined): [Total Mark:25]

Machine (mno, mname, mtype, mcost)
Part(pno, pnmae, pdesc)
Machine and parts are related with one to many relationships

Politician(pno,pname,pdesc)
Party(party_code,party_name)
Politician & party are related with many-to-one.
Constraints : Primary key pno, party_code ,Party_name Not NULL

Create the relations accordingly,so that the relationship is handled properly and the relations
are in normalized form(3NF) and insert 5 records into each table.
a) Display details of all politician of party ‘BJP’. [5]
b) Count the total number of politicians for each party [5]
c) Count the number of politicians having political description as ‘AP’. [5]
Q.3) Viva [10]
Slip 13

Lab Course II


Country (con-code,name,capital)
Population (pop-code,population)
Country & Population are related with one-to-one relationship.

b) Create database in normalized form. (3NF) [10]
Q.2) Create the following tables (Primary keys are underlined): [Total Mark: 35]
Machine (mno, mname, mtype, mcost)

Part (pno, pnmae, pdesc)
Machine and parts are related with one to many relationships
Constraints:
Primary Key mno ,pno,
Machine name not be null.
a) Increase the cost of machine by 10 % [5]
b) List all the parts of ‘CNC’ machine [5]
c) Delete the record from part table whose part name is wheel. [5]
Q.3) Viva [10]
Slip 14

Lab Course II


Item (item_no,name,quantity)
Sup(no,name,addr,city,phone)
Item & sup are related with many-to-many relationships with rate, discount.
Wholesaler (wno,wname,addr,city)
Product (pno,pname)
Wholesaler & Product are related with many-to-many relationship.
Constraints :
Primary key wno,pno
pname should starts with letter ’A’
wname should not be null
a ) Display wholesaler wise product details [5]
b) Display the details of wholesalers living in the ‘Mumbai’ City [5]
c) Count the number of products sold by wholesaler ‘Dev Enterprise’. [5]
Q.3) Viva [10]
Slip 15

Lab Course II


Patient (pno, pname, addr)
Bed (bno, roomno, description);
Relationship between Patient and Bed is one to one

Q.2) Create the following tables (Primary keys are underlined): [Total Mark: 35]
Item (item_no,name,quantity)
Sup(sno,name,addr,city,phone)
Item & sup are related with many-to-many relationships with rate, discount.
Constraints: Primary key item_no,sno and item qty > 5 and rate > 0.
a ) List all the details of items having quantity > 500 [5]
b) Increase the 5% rate of the item mouse. [5]
c) Count the number of suppliers from each city. [5]
Q.3) Viva [10]
Slip 16

Lab Course II

Q1) Create the following relations. (Primary keys are underlined) [Total Marks 25]
Researcher(researcher_id, researcher_name, address, birthdate)

Paper(paper_id, title)
There exists a many to many relationship between Researcher and Paper with Journal name and
date of publication as descriptive attributes.
a) Draw E-R diagram for the given relational schema and show the normalized schema on paper
[10]
b) Create a normalized database for the schema (3NF) [10]
Customer (cust_id, cust_name, cust_addr, contactph,area_type)
Vehicle (registration_no, make, dt_of_manufacture, vehicle_type)
Constraints: cust_name is not null

area_type can be ('R', 'U') where R is for Rural and U is for Urban
vehicle_type can be ('P' , 'D', 'E') where P stands for Petrol, D for Diesel
and E for Electric
A customer can own many vehicles and a vehicle can belong to exactly one customer.
Draw an ER Diagram for the given relational schema. and Create the normalized tables (in
3NF) accordingly and insert 5 records in each table. Solve the following querries. [20]
a) List the Names of all Customers living in Rural area. [5]

b) List the details of all vehicles manufactured after '21-07-1990' [5]
c) List the Names of all Customers who own Electric vehicles. [5]
Q3) Viva [10]
Slip 17

Lab Course II

4. No question should generate empty output
Watchman (watchmanid, watchman_name, address, birthdate, contact)

Society(society_id, societyname, address)
There exists a many to many relationship between Watchman and area with shift ('M' for
morning, 'A' for Afternoon and 'N' for night) as descriptive attributes.
[10]

Team (Team_id, team_name, Manager_name, coach_name)
Player (Player_id, player_name, date_of_birth, player_type)
Constraints: team_name and player_name are not null

player_type can be ('A', 'B', 'O') where A is for all-rounder, B is for batsman
and O is for bowler
A Team can have many players. A player belongs to exactly one Team. Draw an ER Diagram for
the given relational schema. and Create the normalized tables (in 3NF) accordingly and insert 5
records in each table. Solve the following queries. [20]
a) List the Names of all players sorted on player name [5]
b) List the Team name whose coach is 'Bijoy Kumar' [5]
c) List the Names of all players playing for the team 'Centurions'. [5]
Q3) Viva [10]
Slip 18

Lab Course II

Room (roomid, room_desc, room_type, floor)
Guest(guest_id, guestname, address)
There exists a one to one relationship between Room and guest with room_type as ('U' for
private suite, 'S' for Single and 'D' for double).
[10]
Book(Bookid, book_title, ISBN_No, Price)
Author (Author_id, Author_name, date_of_birth, email)
Publisher(publisher_id, publisher_name, address)
Constraints: book_title, author_name and publisher_name are not null

price should be greater than 0.
An author can write many books, a book can be written by many authors, a book has exactly one
publisher and a publisher publishes many books. Draw an ER Diagram for the given relational
schema. and Create the normalized tables (in 3NF) accordingly and insert 5 records in each table.
Solve the following queries. [20]
a) Count the number of books written by 'Larry Gomes' [5]
b) List all authors sorted by name [5]
c) Change the publisher_name from 'Niyati Publications' to 'Jagruti Publications' [5]
Q3) Viva [10]
Slip 19

Lab Course II

Machine (machine_id, machine_desc, machine_type)

Parts(guest_id, guestname, address)
There exists a many to many relationship between Machine and Parts. Machine type depends on
the way it is operated and can be ('A' for fully automatic, 'S' for semi-automatic and 'M' for
manual).
[10]
Salesman(Saleamanid, name, commission_amt)
Customer (cust_id, cust_name, city, contact)
Constraints: salesman name and customer name not null

commission_amt should be greater than 0.
There exists a many-to-many relationship between customer and salesman with descriptive
attributes purchase amount and order date. One customer can order form many salesman and one
salesman can accept orders from many customers. Draw an ER Diagram for the given relational
schema. and Create the normalized tables (in 3NF) accordingly and insert 5 records in each table.
Solve the following queries. [20]
a) Count the number of salesman whose commission is less than 50000 [5]
b) List salesman details sorted on commission_amt [5]
c) Display all orders having order date as 10-9-2016 and issued by the salesman whose ID is 505
[5]
Q3) Viva [10]
Slip 20

Lab Course II

Customer (cust_id, cust_name, contact_Ph, cust_type)
Fixed Deposit(Receit_id, amount, period, rate_of_interest)

Constraints: Customer name is not null and
amount, Rate_of_interest and period should be greater than 0.
There exists a many to many relationship between Customer and Fixed Deposit with descriptive
attribute date_of_FD and maturity_date. A customer can have many FDs and one FD can belong
to many customers.
[10]
Recipe(Recipe_id, Recipe name, cooking_time)
Ingredients(ingredient_id, ingredient_name, manufacturer_name, rate)
Constraints: Recipe name and ingredient name are not null

rate should be greater than 0.
There exists a many-to-many relationship between recipe and ingredients. A recipe can have
many ingredients and an ingredient can belong to many recipes. Draw an ER Diagram for the
given relational schema. and Create the normalized tables (in 3NF) accordingly and insert 5
records in each table. Solve the following queries. [20]
a) Count the number of ingredients required in the recipe 'Veg Biryani' [5]
b) List all ingredients sorted on rate [5]
c) Display the manufacturers of all ingredients [5]
Q3) Viva [10]
Slip 21

Lab Course II

Employee(empno, empname, salary, designation)

Department(deptno, deptname, location)
Project(pno, pname, status)
Many employee works in one department. Many employee can work on many project
controlled by a department.
a)Draw E-R diagram for given relational schema and show normalization on paper. [10]
b)Create database in normalized form.(3NF) [10]
c)Insert 5 records into each table. [5]
Q.2)Create the following tables(Primary keys are underlined): [Total Mark:35]

Customer (cno, cname, city)
Account (ano, acc_type, balance)
Constraints: Primary key,
Balance should be >100
Relationship between Customer and Account is one to many.
a)List the details of customer whose account type saving. [5]
b)List the details of account whose balance between 4000 and 6000. [5]
c)List the details of customer with maximum account balance. [5]
Q.3) Viva [10]
Slip 22

Lab Course II

Book (bno, bname, price)
Dealer (dno, dname, addr)
Dept (deptno, deptname, head)
One dealer can sale one or many book and one department have many books.

Machine (mno, name, mtype, mcost)
Part (pno, pname, pdesc)
Constraints : Primary Key constraints, machine name not null, foreign key
Machine & Parts are related with one-to-many relationship.
a) Increase the cost of machine by 10% [5]
b) List all parts whose machine cost is greater than 1000. [5]
c)Find minimum cost of machine. [5]
Q.3) Viva [10]
Slip 23

Lab Course II

Client(client_no,name,pin,ph_no,address)
Trucks(t_no,model,make,year)
Route(r_no,source,destination)
One or more client can take one or more trucks on rent.
A truck can use one or more routes.

Passenger(p_id,name,age,address)
Ticket(t_no,amount)
Employee(e_no,name,address,salary)
Flight(f_no,source,destination,departure_time,arrival_time)
Constraints: age should be >5 for ticket booking

Flight and tickets are related with one to many.
Flight and Passenger are related with one to many and Employee are related with one to one
Relationship.
a)List the name of passenger whose f_no=205 [5]
b)List the flight number whose ticket amount=2000 [5]
c)List the flight details whose source=”Pune” [5]
Q.3) Viva [10]
Slip 24

Lab Course II

Customer(cno,name,address)
Quotation(q_no,desc,am_qut)
A customer can give one or more quotation but quotation belongs
to exactly one customer.

Sailors(sid,sname,rate,age)
Boats(bid,bname,colour)
Reserves(sid,bid,date)
Sailors and boats are related many to many.
a)Find all the sailors with a rating above 8. [5]
b)Find the ages of sailors whose name begins and ends with ‘P’. [5]
c)Find name of sailors who have reserved red and green boats. [5]
Q.3) Viva [10]
Slip 25

Lab Course II

Movies(Mname,release_year,budget)
Actor(Aname,role,charges,A_Address)
Producer(Pid,name,P_address)
Each actor has acted in one or more movies.Each producer has produced many movies and
Each movie can be produced by more than one producers.Each movie has one or more actors
acting in it,in different roles.

Bus (Bno, capacity ,depot_name)
Route (Rno,source,destination,no_of_station)
Driver (Dno ,dname,License_no,d_address, d_age,Salary)
The relationships are as follows:
BUS_ROUTE : M-1
BUS_DRIVER : M-M with descriptive attributes Date of duty allotted and Shift — it can be 1
(Morning) 0r 2 ( Evening ).
Constraints :
1. License_no is unique.
2. Bus capacity is not null.
a) Find out the drivers working in shift 1. [5]
b) Find the names and their license no. of drivers working on 12-01-2016 in both the shifts.
[5]
c) Update the salary of driver by 1000 if his age > 35. [5]
Q.3) Viva [10]
Slip 26

Lab Course II

Property(Pno,description,area)
District(dcode,dname,tax_rate)
Owner(oname,address,phno)
One or more property is belongs to only one owner and one or more property is present
in one district.

Car(cno,cmodel,ownername)
Driver(dno,dname,daddr,dcity)
Accidents(a_id,place,year)
Car and drivers relationship is many to many and car and accident relationship one to many.
Constraints : primary key
Accidents year >1990
a)Delete all records from driver relation who live in “Pune”. [5]
b)List the drivers invoved in accidents which took place in “Mumbai”. [5]
c)Find the number of accidents in which the cars belonging to “Rahul” were involved. [5]
Q.3) Viva [10]
Slip 27

Lab Course II
BCA206: Lab II Relational Database Management System

Employee(Eno,ename,DOB,DOJ,Qualification,designation)
Posting(Pno,Pdate)
Reward(Rno,Rdecripation,RewardAct)
A employee have one or more Reward.One employee is belongs to one posting.
Customer(Cno,Cname,addr)
Plant(Pcode,Pname,Ptype,Pcost)
Nutrients(Nname,Nquantity,time)
Cutomer and plants are related with one to many and plant and nutrients are related
one to many relationship.
Plant type flowering and non flowering
a)List the customer who purchase plant ‘Rose’. [5]
b)List the cutomer name who purchase plant type flowering. [5]
c)List all those plants to whom nutrient ‘manure’is given. [5]
Q.3) Viva [10]
Slip 28

Lab Course II

Employee(Eno,ename,DOB,DOJ,Qualification,designation)
Posting(Pno,Pdate)
Reward(Rno,Rdecripation,RewardAct)
A employee have one or more Reward.One employee is belongs to one posting.
Person (pnumber integer, pname varchar (20), birthdate date, income money)
Area ( aname varchar (20), area-type varchar (5) )
An area can have one or more persons living in it, but a person belongs to exactly one area. The
attribute ‘area_type’ can have values either ‘urban’ or ‘rural’.
Create the relations accordingly,so that the relationship is handled properly and the relations are
Draw ER diagram for given relational schema and show normalization.
[20]
a. List the names of all people living in ‘Pune’ area. [5]

b. List the details of all people whose names start with the alphabet ‘A’ [5]
c. Count area wise people whose income is below 3000. [5]
Q.3) Viva [10]
Slip 29

Lab Course II

Department( dno,dname,HOD)
Project (pno, pname, status)
Department and Project are related with one to many relationships
c) add new column location to the table department. Change the data type for column dno and
pno as varchar. Insert 5 records into each table. [5]

Car(cno,cmodel,ownername)
Driver(dno,dname,daddr,dcity)
Accidents(a_id,place,year)
Car and drivers relationship is many to many and car and accident relationship one to many.
Accidents year >1990
a)Delete all records from driver relation who live in “Pune”. [5]
b)List the drivers invoved in accidents which took place in “Mumbai”. [5]
c)Find the number of accidents in which the cars belonging to “Rahul” were involved. [5]
Q.3) Viva [10]
Slip 30

Bca Science Slip Sem II C & Dbms Feb2017

Uploaded by

Copyright:

Available Formats

Bca Science Slip Sem II C & Dbms Feb2017

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Bca Science Slip Sem II C & Dbms Feb2017

Uploaded by

Copyright:

Available Formats

CONFIDENTIAL

Savitribai Phule Pune University

Booklet of Practical Skeleton Papers for

LAB COURSE I & II

Important to note about laboratory courses:

Booklet of Practical Skeleton Papers for

F.Y.B.C.A (Science) SEMESTER – II

BCA 205 : Lab Course-I

Duration: 3Hrs. Max Marks: 70+30=100

Q4. External Viva [10]

Q5. Internal Viva [10]

Q6. Journal (10 Assignments. Each assignment-2 Marks) (Internal) [20]

Duration: 3Hrs. Max Marks: 70+30=100

Q4. External Viva [10]

Q5. Internal Viva [10]

Q6. Journal (10 Assignments. Each assignment-2 Marks) (Internal) [20]

Savitribai Phule Pune University

Duration: 3Hrs. Max Marks: 70+30=100

Q4. External Viva [10]

Q5. Internal Viva [10]

Q6. Journal (10 Assignments. Each assignment-2 Marks) (Internal) [20]

Savitribai Phule Pune University

Duration: 3Hrs. Max Marks: 70+30=100

Q4. External Viva [10]

Q5. Internal Viva [10]

Q6. Journal (10 Assignments. Each assignment-2 Marks) (Internal) [20]

Savitribai Phule Pune University

Duration: 3Hrs. Max Marks: 70+30=100

Q4. External Viva [10]

Q5. Internal Viva [10]

Q6. Journal (10 Assignments. Each assignment-2 Marks) (Internal) [20]

Savitribai Phule Pune University

Duration: 3Hrs. Max Marks: 70+30=100

Q4. External Viva [10]

Q5. Internal Viva [10]

Q6. Journal (10 Assignments. Each assignment-2 Marks) (Internal) [20]

Savitribai Phule Pune University

Duration: 3Hrs. Max Marks: 70+30=100

Q4. External Viva [10]

Q5. Internal Viva [10]

Q6. Journal (10 Assignments. Each assignment-2 Marks) (Internal) [20]

Savitribai Phule Pune University

Duration: 3Hrs. Max Marks: 70+30=100

Q4. External Viva [10]

Q5. Internal Viva [10]

Q6. Journal (10 Assignments. Each assignment-2 Marks) (Internal) [20]

Savitribai Phule Pune University

Duration: 3Hrs. Max Marks: 70+30=100

Q4. External Viva [10]

Q5. Internal Viva [10]

Q6. Journal (10 Assignments. Each assignment-2 Marks) (Internal) [20]

Savitribai Phule Pune University

Duration: 3Hrs. Max Marks: 70+30=100

Q4. External Viva [10]

Q5. Internal Viva [10]

Q6. Journal (10 Assignments. Each assignment-2 Marks) (Internal) [20]

Savitribai Phule Pune University

Duration: 3Hrs. Max Marks: 70+30=100