MCSL-045 ASSIGNMENT SOLUTION (2019-20)

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



PART-I : MCS-041


Q1. Write the UNIX commands for the following :

(a) To search for text using specific regular expressions in file.

Answer : - grep is an acronym that stands for Global Regular Expression Print.

The grep filter searches a file for a particular pattern of characters, and displays all lines that contain that pattern. The pattern that is searched in the file is referred to as the regular expression.

Syntax :
grep [options] Pattern [files]

Example :
grep Computer Test.txt
grep Computer Test.txt Data.txt Index.txt




(b) To sort the contents of a file in reverse alphabetical order.

Answer : - sort command is used to sort a file, arranging the records in a particular order. By default, the sort command sorts file assuming the contents are ASCII. Using options in sort command, it can also be used to sort numerically.

Syntax :
sort [options] file

Example :
sort -r Index.txt
[sort Index.txt file in reverse order]




(c) To count the no. of characters and words in a given text file.

Answer : - wc command is used to find out number of lines, words and characters present in the files specified in the file arguments.

By default it displays four-columnar output. First column shows number of lines present in a file specified, second column shows number of words present in the file, third column shows number of characters present in file and fourth column itself is the file name which are given as argument.

Syntax :
wc [options] [files]

Example :
wc Test.txt
wc Data.txt Index.txt

wc -c Test.txt
[count the number of characters present in the file]

wc -w Test.txt
[count the number of words present in the file]




(d) Create a small text file from your console.

Answer : -

Creating a file using "echo" command

echo 'My name is Debabrata' > myfile.txt

Creating a file using "cat" command
cat command is used to immediately add some text to your new file.

Type the following command at the terminal prompt and then press Enter :

cat > myfile.txt

After pressing Enter, you are not returned to the terminal prompt. Instead, the cursor is placed on the next line, and you can start entering text directly into your file. Type your lines of text, pressing Enter after each line. When you are done, press Ctrl+D to exit the file and return to the prompt.

Creating a file using "touch" command
Using touch command we can create one or multiple empty files.

touch myfile.txt
touch Test.txt Demo.txt

Creating a file using "vi / vim" text editor

vi myfile.txt
[After pressing Enter, press i to insert new text into the file. Press ESC and then press :x to save the file].




(e) Change the file permissions to Read, Write and Execute for everyone on a data file created by you.

Answer : - To change file and directory permissions, use the command chmod (change mode). The owner of a file can change the permissions for user (u), group (g), or others (o) by adding (+) or subtracting (-) the read, write, and execute permissions.

There are two basic ways of using chmod to change file permissions: The symbolic method and the absolute form.

Symbolic Method

Access classOperatorAccess Type
u (user)+ (add access)r (read)
g (group)- (remove access)w (write)
o (other)= (set exact access)x (execute)
a (everyone or all : u, g, and o)

Example :
chmod a+rwx myfile.txt
[Add permission for everyone to read, write and execute a file]

Absolute Form - The other way to use the chmod command is the absolute form, in which you specify a set of three numbers that together determine all the access classes and types. Rather than being able to change only particular attributes, you must specify the entire state of the file's permissions.

The three numbers are specified in the order : user (or owner), group, and other. Each number is the sum of values that specify read, write, and execute access :

PermissionNumber
Read (r)4
Write (w)2
Execute (x)1

Example :
chmod 777 myfile.txt
[Add permission for everyone to read, write and execute a file]




(f) To create a physical link between two files f1 and f2.

Answer : - A link in UNIX is a pointer to a file. Like pointers in any programming languages, links in UNIX are pointers pointing to a file or a directory. Creating links is a kind of shortcuts to access a file. Links allow more than one file name to refer to the same file, elsewhere.

There are two types of links :

  1. Hard Links
  2. Soft Link or Symbolic links

Hard Links - Users are not allowed to create hard links for directories. This might transform the directory tree into a graph with cycles, thus making it impossible to locate a file according to its name.

Links can be created only among files included in the same filesystem.

Syntax :
ln [original filename] [link name]

Example :
ln Demo.txt Test.txt




(g) Compare two text files and display the first difference.

Answer : -

"cmp" command - This command is used to compare two files character by character, and if they differ, it tells the first byte and line number where they differ.

Syntax :
cmp [option] file1 file2

Example :
cmp Demo.txt Test.txt

"comm" command - This command is used to compare two sorted files.

Syntax :
comm [option] file1 file2

Example :
comm Demo.txt Test.txt

"diff" command - This command is used to compare two files line by line.

Syntax :
diff [option] file1 file2

Example :
diff Demo.txt Test.txt




(h) To display the disk usage.

Answer : - du command is used to estimate file space usage. The du command can be used to track the files and directories which are consuming excessive amount of space on hard disk drive.

Syntax :
du [options] [directories and/or files]

Example :
du /shell/Test.txt
[Show disk usage of "Test.txt" file]

du -h /shell
[Show disk usage of "shell" directory in human readable format]




(i) Create a file containing 20 lines, split it into two files containing 10 lines each.

Answer : - split command is use to split a file.

Syntax :
split [options] filename prefix

Replace "filename" with the name of the large file you wish to split. Replace "prefix" with the name you wish to give the small output files. You can exclude [options], or replace it with either of the following :

Example :
assume Index.txt is 20 lines long :
split -l 10 Index.txt subfile
[This will output 2 10-line files : subfileaa and subfileab]




(j) To change the owner of a file.

Answer : - chown command is used to change the file Owner or group. Whenever you want to change ownership you can use chown command.

Syntax :
chown [options] [owner][:[group]] [files]

Example :
chown Debabrata Demo.txt
[change the owner of Demo.txt file]

chown :mygroup Demo.txt
[change group ownership]

chown Debabrata:mygroup Demo.txt
[change the owner as well as group]




Q2. (a) Write a shell program to count and print the no. of positive and negative integers in a list of integers given as input by the user.

Answer : -

echo "How many numbers you want to take input... "
read n
p_no=0
n_no=0
for((i=0;i<n;i++))
do
echo "Enter the number... "
read a[$i]
if test ${a[i]} -ge 0
then
p_no=`expr $p_no + 1`
else
n_no=`expr $n_no + 1`
fi
done
echo "Number of Positive Integer in the List = $p_no"
echo "Number of Negative Integer in the List = $n_no"

Output
How many numbers you want to take input...
5
Enter the number...
10
Enter the number...
-60
Enter the number...
45
Enter the number...
92
Enter the number...
-56
Number of Positive Integer in the List = 3
Number of Negative Integer in the List = 2




Q2. (b) Given two filenames by the user as the input, write a shell script to append the contents of file2 to file1.

Answer : -

echo "Enter the file name which you want to append..."
read file1
echo "Enter the file name from which content is copy..."
read file2
# Check whether the file1 has write permission or not
if test -w $file1
then
# Check whether the file2 has read permission or not
if test -r $file2
then
while read line
do
# Copy each line from file2 to file1
`echo ${line} >> $file1`
done < $file2
else
echo "$file2 does not exist or not have read permission"
fi
else
echo "$file1 does not exist or not have write permission"
fi

Output
Enter the file name which you want to append...
/shell/Demo.txt
Enter the file name from which content is copy...
/shell/Index.txt




Q2. (c) Write a shell script to display the no. of times the given pattern occurs in a .dat file and display the count.

Answer : -

echo "Enter the pattern..."
read pattern
file="/shell/a.dat"
patternLen=${#pattern}
count=0
while read line
do
# Pick each line from the given file
line=`echo ${line}`
# Calculate the length of each line
len=${#line}
start=1
end=$patternLen
while test $end -le $len
do
s=`echo $line | cut -c$start-$end`
if [ "$s" = "$pattern" ]
then
count=`expr $count + 1`
fi
start=`expr $start + 1`
end=`expr $end + 1`
done
done < $file
echo "Pattern Occurs = $count"

Output
Enter the pattern...
name is
Pattern Occurs = 2

Content of a.dat file
My name is Debabrata
My mother name is Jyotsna






PART-II : MCS-043

NOTE - I use Oracle 12c for all the SQL Queries, Triggers and Procedure


Q1. Design a database for a Mobile Phones’ Sales and Service Showroom which deals with sales and service of various brands of mobile phones. It also sells various accessories pertaining to mobile phones. You are required to perform the following activities for the maintenance of the above :

(a) Draw an enhanced entity relationship (EER) diagram.

Answer : -




(b) Create the complete database.

Answer : -

Before create a user run the command given below -
SQL> ALTER SESSION SET "_ORACLE_SCRIPT"=true;
SQL> COMMIT;

Create a new user -
SQL> CREATE USER showroom IDENTIFIED BY Pass1234

SQL> GRANT UNLIMITED TABLESPACE TO Showroom
SQL> GRANT CREATE SESSION TO showroom;
SQL> GRANT CREATE TABLE TO showroom;
SQL> GRANT CREATE VIEW TO showroom;
SQL> GRANT CREATE ANY TRIGGER TO showroom;
SQL> GRANT CREATE ANY PROCEDURE TO showroom;
SQL> GRANT CREATE SEQUENCE TO showroom;
SQL> GRANT CREATE SYNONYM TO showroom;
SQL> COMMIT;

SQL> CONNECT showroom
Enter password : Pass1234
Connected.
SQL>

SQL> CREATE TABLE shop (shop_id INT, shop_name VARCHAR2(50) NOT NULL, shop_phone INT NOT NULL, PRIMARY KEY(shop_id));

SQL> CREATE TABLE product (p_code VARCHAR2(10), p_name VARCHAR2(25) NOT NULL, brand VARCHAR2(20) NOT NULL, model VARCHAR2(20) NOT NULL, unit_price INT NOT NULL, stock INT NOT NULL, PRIMARY KEY(p_code));

SQL> CREATE TABLE sells (shop_id INT NOT NULL, p_code VARCHAR2(10) NOT NULL);

SQL> CREATE TABLE bill_master (bill_no INT, bill_amount INT NOT NULL, bill_date DATE NOT NULL, shop_id INT NOT NULL, cust_id VARCHAR2(10) NOT NULL, PRIMARY KEY(bill_no));

SQL> CREATE TABLE customer (cust_id VARCHAR2(10), cust_name VARCHAR2(50) NOT NULL, cust_address VARCHAR2(100) NOT NULL, cust_phone INT NOT NULL, PRIMARY KEY(cust_id));

SQL> CREATE TABLE bill_details (bill_no INT NOT NULL, p_code VARCHAR2(10) NOT NULL, serial_no VARCHAR2(25) NOT NULL);

SQL> CREATE TABLE service_order (service_no INT, cust_id VARCHAR2(10) NOT NULL, shop_id INT NOT NULL, submit_date DATE NOT NULL, PRIMARY KEY(service_no));

SQL> CREATE TABLE device_details (service_no INT NOT NULL, phone_brand VARCHAR2(20) NOT NULL, phone_model VARCHAR2(20) NOT NULL, serial_no VARCHAR2(25) NOT NULL, service_description VARCHAR2(200) NOT NULL);

SQL> CREATE TABLE service_bill (service_bill_no INT, total_amount INT NOT NULL, service_bill_date DATE NOT NULL, shop_id INT NOT NULL, cust_id VARCHAR2(10) NOT NULL, service_no INT NOT NULL, PRIMARY KEY(service_bill_no));

SQL> CREATE TABLE service_bill_details (service_bill_no INT NOT NULL, service_no INT NOT NULL, phone_brand VARCHAR2(20) NOT NULL, phone_model VARCHAR2(20) NOT NULL, serial_no VARCHAR2(25) NOT NULL, repairing_cost INT NOT NULL, repair_description VARCHAR2(200) NOT NULL);




(c) Write the following queries using SQL :

(i) To display the Brand, Model and Price of the all the mobile phones sold on a particular date.

Answer : - SELECT DISTINCT brand, model, unit_price FROM product, bill_details, bill_master WHERE product.p_code=bill_details.p_code AND bill_details.bill_no=bill_master.bill_no AND bill_master.bill_date=TO_DATE('04-10-2019','DD-MM-YYYY');


(ii) To find the details of the mobile phones which were purchased in the same shop and were serviced on a particular date.

Answer : - SELECT DISTINCT product.p_code, brand, model, unit_price FROM product, bill_details, bill_master, service_bill_details, service_bill WHERE service_bill.shop_id=bill_master.shop_id AND service_bill.service_bill_no=service_bill_details.service_bill_no AND bill_master.bill_no=bill_details.bill_no AND service_bill_details.serial_no=bill_details.serial_no AND bill_details.p_code=product.p_code AND service_bill_date=TO_DATE('01-10-2019','DD-MM-YYYY');


(iii) To display all the Brands and Models of the mobile phones whose price is more than Rs.15000/-.

Answer : - SELECT brand, model FROM product WHERE unit_price > 15000;


(iv) Create a view of the items for the manager showing overall performance of the week for each Brand and Model of the mobile phones.

Answer : - CREATE OR REPLACE VIEW performance AS SELECT product.p_code, brand, model, bill_master.bill_no, bill_date, shop_id, cust_id FROM product, bill_master, bill_details WHERE bill_master.bill_no=bill_details.bill_no AND bill_details.p_code=product.p_code;




(d) Perform the following activities :

(i) Create a trigger that prints the daily catalogue on change of a price of any mobile phone or accessory.

Answer : -

price_update_alert.sql

CREATE OR REPLACE TRIGGER price_update_alert
AFTER UPDATE OF unit_price ON product
FOR EACH ROW
WHEN (NEW.unit_price != OLD.unit_price)
BEGIN
dbms_output.put_line('Product Code - ' || :OLD.p_code);
dbms_output.put_line('Brand - ' || :OLD.brand);
dbms_output.put_line('Model - ' || :OLD.model);
dbms_output.put_line('Old Price - ' || :OLD.unit_price);
dbms_output.put_line('New Price - ' || :NEW.unit_price);
END;
/

Run the trigger in oracle using the following command -
SQL> @ File_Location\File_name;
Example -
SQL> @ D:\Oracle\price_update_alert.sql;

To display the output of a procedure in oracle use the following command at once -
SQL> SET SERVEROUTPUT ON;




(ii) Create a trigger whenever there is a new entry (of a new model) in the mobile phone table. Also design a trigger whenever there is a deletion of the record.

Answer : -

insert_alert.sql

CREATE OR REPLACE TRIGGER insert_alert
AFTER INSERT ON product
FOR EACH ROW
WHEN(NEW.p_code IS NOT NULL)
BEGIN
dbms_output.put_line('Product Code - ' || :NEW.p_code);
dbms_output.put_line('Brand - ' || :NEW.brand);
dbms_output.put_line('Model - ' || :NEW.model);
dbms_output.put_line('Price - ' || :NEW.unit_price);
END;
/


delete_alert.sql

CREATE OR REPLACE TRIGGER delete_alert
AFTER DELETE ON product
FOR EACH ROW
BEGIN
dbms_output.put_line('Product Code - ' || :OLD.p_code);
dbms_output.put_line('Brand - ' || :OLD.brand);
dbms_output.put_line('Model - ' || :OLD.model);
dbms_output.put_line('Price - ' || :OLD.unit_price);
END;
/




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

Answer : -

transaction_details.sql

CREATE OR REPLACE PROCEDURE transaction_details (start_date IN DATE, end_date IN DATE) AS
quantity INT;
revenue INT;
BEGIN
SELECT COUNT(bill_details.p_code) INTO quantity FROM bill_details, bill_master WHERE bill_details.bill_no=bill_master.bill_no AND bill_master.bill_date>=start_date AND bill_master.bill_date<=end_date;
SELECT SUM(bill_amount) INTO revenue FROM bill_master WHERE bill_master.bill_date>=start_date AND bill_master.bill_date<=end_date;
dbms_output.put_line('Total Item Sold = ' || quantity);
dbms_output.put_line('Overall Revenue Generated = ' || revenue);
COMMIT;
END;
/

Run the procedure in oracle using the following command -
SQL> @ File_Location\File_name;
Example -
SQL> @ D:\Oracle\transaction_details.sql;

To display the output of a procedure in oracle use the following command at once -
SQL> SET SERVEROUTPUT ON;

Execute the procedure in oracle using the following command -
SQL> EXEC Procedure_Name;
SQL> EXEC Procedure_Name(Parameter List);

Example -
SQL> EXEC transaction_details(TO_DATE('30-09-2019','DD-MM-YYYY'),TO_DATE('06-10-2019','DD-MM-YYYY'));




(g) 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 (a salesperson) who sells these items.

Answer : - Solve it Yourself



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

Solutions of Different Questions


WHAT WE DO


Website Devlopment

Training

Home Learning

Provide BCA, MCA Projects

Provide Assignment & Question Paper Solution


CONTACT US


Follow Us