MCSL-045 ASSIGNMENT SOLUTION (2018-19)

If you have any queries please leave a message here
Your Message
×


PART-1 : MCS-041

Question 1 :

Write the UNIX commands for the following :

(a) To wait for a specified number of seconds before exit.

Answer : -

In Unix shells, wait is a command which pauses until execution of a background process has ended.
Syntax :
wait pid
Example :
root@ubuntu:~# wait 421

sleep is a command in Unix, that suspends program execution for a specified time.
Syntax :
sleep number
Example :
root@ubuntu:~# sleep 20


(d) To change the command prompt from $ to ?.

Answer : - user@ubuntu:~$ PS1='\u@\h:\w?'

PS1 is a primary prompt variable which holds '\u@\h:\w?' special bash characters. This is the default structure of the bash prompt and is displayed every time a user logs in using a terminal.

The special characters in the default prompt are as follows :


(e) To grant the permissions of read, write and execute to the user and read only to the group and others for any file using chmod.

Answer : - root@ubuntu:~# chmod 744 File1
chmod OwnerGroupOthers File/Directory Name

NumberPermission TypeSymbol
0No Permission---
1Execute--x
2Write-w-
3Execute + Write-wx
4Readr--
5Read + Executer-x
6Read + Writerw-
7Read + Write + Executerwx


(f) To direct a standard output to any file

Answer : - If a command has its output redirected to a file and the file already contains some data, that data will be lost. Consider the following example −
root@ubuntu:~# echo Computer > File1

You can use >> operator to append the output in an existing file as follows −
root@ubuntu:~# echo Database >> File1


(g) To print all the filenames in the current directory that doesn’t contain the temp.

Answer : -


(h) To list all the filenames that others can read and write

Answer : - root@ubuntu:~# ls -l


(i) To split a file test, which is containing 100 lines into 25 lines each

Answer : - root@ubuntu:~# split -l 25 test
[This will output four 25-line files : "xaa", "xab", "xac" and "xad".]


(j) To display those lines that are common to file1 and file2.

Answer : - root@ubuntu:~# comm -12 file1 file2
[Compare sorted files "file1" and "file2" line by line]




Question 2 :

(a) Write a shell program to translate all the lower case letters in any text file to the upper case letters.

Answer : -

# Input file name from the user
echo "Enter the file name....."
read SourceFile
# Check whether the inputed file is present or not
if test -f $SourceFile
then
# Create a temporary file
TempFile="/Temp"
touch $TempFile
# Pick all the contents of "SourceFile"
# Convert all the lowercase character into uppercase
# Store the converted contents into "TempFile"
tr '[[a-z]]' '[[A-Z]]' < $SourceFile > $TempFile
# Copy "TempFile" data into "SourceFile"
cp $TempFile $SourceFile
# Delete the temporary file
rm -rf $TempFile
else
echo "File Not Found"
fi




(b) Given the filename by the user as the input, write a shell script to display the first five lines of the file.

Answer : -

# Input file name from the user
echo "Enter the file name....."
read file
# Check whether the inputed file is present or not
if test -f $file
then
count=0;
# Read each line from the inputed file
while read line
do
# Pick each line from the file and store it into a variable
currentLine=`echo ${line}`
echo "$currentLine"
count=`expr $count + 1`
if test $count -eq 5
then
break
fi
done < $file
else
echo "File Not Found"
fi




(c) Write a shell script to display the list of the files whose filename consists of 4 characters (with any file extension) and filename starts with the alphabet f.

Answer : -

# Input directory name from the user
echo "Enter the directory name....."
read directory
# Check whether the inputed directory is present or not
if test -d $directory
then
# Create a temporary file
TempFile="/Temp"
touch $TempFile
# Create a list of all the files and directories
# whose filename consists of 4 characters (with any file extension) and
# filename starts with the alphabet f

# Store the created list into "TempFile"
ls $directory | grep '^f...$' > $TempFile
ls $directory | grep '^f...\..*$' >> $TempFile
# Read each line from the "TempFile"
while read line
do
currentLine=`echo ${line}`
File="$directory/$currentLine"
if test -f $File
then
echo "$File"
fi
done < $TempFile
else
echo "Directory Not Found"
fi






PART-2 : MCS-043

Question 1 :

Design a database for maintaining inventory of a retail shop. You are required to perform the following activities for the maintenance of the above :

(a) Create the database.

Answer : -

Tables
items (item_code, item_name, price, stock)
customer (cust_id, cust_name, address, phone)
sales_master (bill_no, cust_id, bill_amount, bill_date)
bill_details (bill_no, item_code, unit_price, quantity, total_price)

CREATE TABLE items
(item_codeVARCHAR2(25),
item_nameVARCHAR2(50),
priceNUMBER(7,2),
stockINT,
PRIMARY KEY (item_code));

CREATE TABLE customer
(cust_idINT,
cust_nameVARCHAR2(100),
addressVARCHAR2(100),
phoneINT,
PRIMARY KEY (cust_id));

CREATE TABLE sales_master
(bill_noINT,
cust_idINT,
bill_amountNUMBER(10,2),
bill_dateDATE,
week_noINT,
PRIMARY KEY (bill_no));

CREATE TABLE bill_details
(bill_noINT,
item_codeVARCHAR2(25),
unit_priceNUMBER(7,2),
quantityINT,
total_priceNUMBER(10,2));




(b) Write the following queries using SQL:

  1. Find the details of the items whose sales have exceeded Rs. 2,00,000.

    Answer : - SELECT * FROM items WHERE (SELECT SUM(total_price) FROM bill_details WHERE items.item_code=bill_details.item_code GROUP BY item_code) > 200000;

  2. Find the details of the six items in terms of numbers/quantity in alphabetical order that have got the maximum sale.

    Answer : -

    CREATE TABLE maximum_sale
    (item_codeVARCHAR2(25),
    total_quantity_saleINT);

    INSERT INTO maximum_sale SELECT item_code, sum(quentity) FROM bill_details GROUP BY item_code;

    SELECT * FROM maximum_sale WHERE ROWNUM <=6 ORDER BY item_code ASC;

  3. Find the names of those items that have an overall sale of 40% of what have been procured.

    Answer : -

  4. Create a view of the items for the manager showing overall performance of the week for each item.

    Answer : - CREATE OR REPLACE VIEW item_performance AS SELECT bill_details.bill_no, item_code, unit_price, quantity, total_price, bill_date FROM bill_details, sales_master WHERE sales_master.bill_no = bill_details.bill_no AND week_no=14;




(c) Create the procedures for the queries (i) to (iii) above

Answer : -

sales_report.sql

CREATE OR REPLACE PROCEDURE sales_report AS
items_row items%ROWTYPE;
CURSOR items_cursor is SELECT * FROM items WHERE (SELECT SUM(total_price) FROM bill_details WHERE items.item_code=bill_details.item_code GROUP BY item_code) > 200000;
BEGIN
OPEN items_cursor;
LOOP
FETCH items_cursor INTO items_row;
EXIT WHEN items_cursor%NOTFOUND;
dbms_output.put_line(items_row.item_code || ' ' || items_row.item_name || ' ' || items_row.price || ' ' || items_row.stock);
END LOOP;
CLOSE items_cursor;
COMMIT;
END;
/




(d) Perform the following activities :

  1. Create a trigger that prints the daily catalog on change of a price of an item.

    Answer : - daily_catalog.sql

    CREATE OR REPLACE TRIGGER daily_catalog
    AFTER UPDATE OF price ON items
    FOR EACH ROW
    WHEN (NEW.price > 0)
    BEGIN
    dbms_output.put_line('Item Code : ' || :OLD.item_code);
    dbms_output.put_line('Old Price : ' || :OLD.price);
    dbms_output.put_line('New Price : ' || :NEW.price);
    END;
    /

  2. Create a trigger that increases the price of a specific item by a certain percentage on a specific weekend.

    Answer : -




(e) Create a transaction that finds the total items sold per week and prints the overall revenue generated.

Answer : - SELECT week_no, SUM(quantity) AS total_items, SUM(total_price) AS overall_revenue FROM sales_master, bill_details WHERE sales_master.bill_no = bill_details.bill_no GROUP BY week_no ORDER BY week_no;




(f) Create two different types of users: the first user – a manager who can see reports and change the items and its price value and second user who sells these items.

Answer : -

In Oracle 12c before creating the user run :
ALTER session SET "_ORACLE_SCRIPT"=true;

CREATE USER manager IDENTIFIED BY AdminPassword;
CREATE USER seller IDENTIFIED BY SalesPassword;

GRANT CONNECT TO manager;
GRANT CONNECT TO seller;

GRANT SELECT, UPDATE ON items TO manager;
GRANT SELECT ON items TO seller;
GRANT SELECT, INSERT ON sales_master TO seller;

GRANT SELECT, INSERT ON bill_details TO seller;



ABOUT US

QuestionSolves.com is an educational website that helps worldwide students in solving computer education related queries.

Also, different software like Visual Studio, SQL Server, Oracle etc. are available to download in different versions.

Moreover, QuestionSolves.com provides solutions to your questions and assignments also.


MORE TOPIC


Windows Command

UNIX Command

IGNOU Assignment Solution

IGNOU Question Paper Solution


WHAT WE DO


Website Devlopment

Training

Home Learning

Provide BCA, MCA Projects

Provide Assignment & Question Paper Solution


CONTACT US


Follow Us