MCS-023 ASSIGNMENT SOLUTION (2020-21)

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


Q1. Study online banking system requirements and design an ER diagram for an Online Banking System. List and write the entities, corresponding attributes, relationships and cardinality.

Answer : -


Table Name : customer

Field NameData TypeConstraintDescription
cust_idintPrimary KeyUniquely identify each Customer in this table
cust_namevarchar(100)Name of the customer
cust_addressvarchar(200)Address of the customer
cust_phonebigintCustomer's contact details
cust_emailvarchar(100)Mail id of the customer
PANvarchar(12)Customer's PAN details
aadhaar_nobigintAadhaar number of the customer
date_of_birthdateCustomer's date of birth
join_datedateCustomer's join date
usernamevarchar(20)Foreign KeyLogin username for online banking


Table Name : login

Field NameData TypeConstraintDescription
usernamevarchar(20)Primary KeyLogin username for online banking
passwordvarchar(32)Login password for online banking


Table Name : account

Field NameData TypeConstraintDescription
account_nobigintPrimary KeyUniquely identify each Account in this table
current_balancenumber(20,2)Current account balance
opening_datedateAccount opening date
IFSCvarchar(12)Foreign KeyBranch identification code


Table Name : account_holder

Field NameData TypeConstraintDescription
account_nobigintForeign KeyAccount number
cust_idintForeign KeyCustomer identification number


Table Name : savings

Field NameData TypeConstraintDescription
interest_ratenumber(4,2)Interest rate of the account


Table Name : current

Field NameData TypeConstraintDescription
trade_licensevarchar(20)Trade license of the business
business_PANvarchar(12)PAN details of the business


Table Name : branch

Field NameData TypeConstraintDescription
IFSCvarchar(12)Primary KeyUniquely identify each Branch in this table
branch_namevarchar(50)Name of the branch
branch_addressvarchar(100)Address of the branch
branch_phonebigintContact number of the branch


Table Name : passbook

Field NameData TypeConstraintDescription
account_nobigintForeign KeyAccount number
transaction_datedatetimeDate of the transaction
descriptionvarchar(50)Description of the transaction
withdrawal_amountnumber(20,2)Withdrawal amount
deposit_amountnumber(20,2)Deposit amount
balancenumber(20,2)balance after withdrawal or deposit


Table Name : transaction

Field NameData TypeConstraintDescription
transaction_idbigintPrimary KeyUniquely identify each Transaction in this table
transfer_datedatetimeDate of amount transfer
amountnumber(20,2)Transfer amount
account_nobigintForeign KeySender account number
receiver_account_nobigintReceiver account number
usernamevarchar(20)Foreign KeyLogin username for online banking


Table Name : services

Field NameData TypeConstraintDescription
usernamevarchar(10)Foreign KeyLogin username for online banking
messagevarchar(500)Service request/complaint
message_datedateDate of the service request/complaint




Q2. Design the Relational Schema for the E-R diagram that you have drawn for part Question 1. The relations must be at least in 2NF. Perform the following on the relations :

a) Enter about 5 sets of meaningful data in each of the relations.
b) Identify the domain of various attributes.
c) Identify the Primary keys of all the relations.
d) Identify the Foreign keys and referential integrity constraints in the relations.

Answer : - Solves it Yourself




Q3. a) “For creating this Online Banking System as shown in Question 1, using a DBMS is better option or file management system.” Justify the statement given above.

Answer : - According to me DBMS is better than File Management System for Online Banking.

Drawbacks of File Management System


Advantage of DBMS over File Management System




Q3. b) Talk to any Database Administrator (DBA) of any good s/w company and try to list all the key responsibilities that he have to handle in the said company.

Answer : - A database administrator’s (DBA) primary job is to ensure that data is available, protected from loss and corruption, and easily accessible as needed.

Functions/Roles of a Database Administrator (DBA) -

  1. Deciding the Hardware Device - Depending upon the cost, performance and efficiency of the hardware, it is DBA who have the duty of deciding which hardware device will suit the company requirement. It is hardware that is an interface between end users and database so it needed to be of best quality.

  2. Software installation and Maintenance - DBA is responsible for installing the database software. He configure the software of database and then upgrades it if needed. There are many database software like oracle, Microsoft SQL, MySQL etc. in the industry so DBA decides how the installing and configuring of these database software will take place.

    If a new server is needed, the DBA handles the transfer of data from the existing system to the new platform.

  3. Managing Data Integrity - Data integrity should be managed accurately because it protects the data from unauthorized use. DBA manages relationship between the data to maintain data consistency.

  4. Database Backup and Recovery - If any company is having a big database, then it is likely to happen that database may fail at any instance. It is require that a DBA takes backup of entire database in regular time span. DBA has to decide that how much data should be backed up and how frequently the backup should be taken. Also the recovery of data base is done by DBA if they have lost the database.

  5. Tuning Database Performance - Database performance plays an important role for any business. If user is not able to fetch data speedily then it may loss company business. So by tuning an modifying sql commands a DBA can improves the performance of database.

  6. Capacity Planning - All the databases have their limits of storing data in it and the physical memory also has some limitations. DBA has to decide the limit and capacity of database and all the issues related to it.

  7. Database Accessibility - DBA writes subschema to decide the accessibility of database. He decides the users of the database and also which data is to be used by which user. No user has to power to access the entire database without the permission of DBA.

  8. Security - A DBA needs to know potential weaknesses of the database software and the company’s overall system and work to minimise risks. No system is one hundred percent immune to attacks, but implementing best practices can minimise risks.

    In the case of a security breach or irregularity, the DBA can consult audit logs to see who has done what to the data. Audit trails are also important when working with regulated data.

  9. Decides Validation Checks on Data - DBA has to decide which data should be used and what kind of data is accurate for the company. So he always puts validation checks on data to make it more accurate and consistence.

  10. Performance Monitoring - Monitoring databases for performance issues is part of the on-going system maintenance a DBA performs. If some part of the system is slowing down processing, the DBA may need to make configuration changes to the software or add additional hardware capacity.




Q4. Given the relational schema:

Suppliers (s_id:integer, s_name:string, s_address:string)
Parts (p_id:integer, p_name:string, p_color:string)
Catalog (s_id:integer, p_id:integer, p_cost:real)

Write SQL statements for the following :

(i) List all the suppliers who supply the part with p_id=32.

Answer : - SELECT Suppliers.s_id, s_name, s_address FROM Suppliers, Catalog WHERE Suppliers.s_id=Catalog.s_id AND p_id=32;


(ii) List the parts whose colour is BLUE.

Answer : - SELECT p_id, p_name FROM Parts WHERE p_color='BLUE';


(iii) List those parts which are RED and whose cost is more than Rs.2000/- .

Answer : - SELECT Parts.p_id, p_name FROM Parts, Catalog WHERE Parts.p_id=Catalog.p_id AND p_color='RED' AND p_cost>2000;


(iv) List all the suppliers who are from GUJARAT.

Answer : - SELECT s_id, s_name FROM Suppliers WHERE s_address='GUJARAT';


(v) List all the part_ids, colour, cost from the supplier EZPREX.

Answer : - SELECT DISTINCT Parts.p_id, p_color, p_cost FROM Suppliers, Parts, Catalog WHERE Parts.p_id=Catalog.p_id AND Supliers.s_id=Catalog.s_id AND s_name='EZPREX';


(vi) List the cost of the part_id=41.

Answer : - SELECT DISTINCT p_cost FROM Catalog WHERE p_id=41;




Q5. Discuss all the file organization techniques with suitable examples.

Answer : - The File is a collection of records. Using the primary key, we can access the records. The type and frequency of access can be determined by the type of file organization which was used for a given set of records.

File organization is a logical relationship among various records. This method defines how file records are mapped onto disk blocks.

File organization is used to describe the way in which the records are stored in terms of blocks, and the blocks are placed on the storage medium.


Objective of File Organization


Types of file organization

File organization contains various methods. These particular methods have pros and cons on the basis of access or selection. In the file organization, the programmer decides the best-suited file organization method according to his requirement.

Types of file organization are as follows :


Sequential File Organization - This method is the easiest method for file organization. In this method, files are stored sequentially. This method can be implemented in two ways :

  1. Pile File Method

    It is a quite simple method. In this method, we store the record in a sequence, i.e., one after another. Here, the record will be inserted in the order in which they are inserted into tables.

    In case of updating or deleting of any record, the record will be searched in the memory blocks. When it is found, then it will be marked for deleting, and the new record is inserted.

    Starting of the File
    R1R3----------R8R5
    End of the File

    Suppose we want to insert a new record R2 in the sequence given above, then it will be placed at the end of the file.

    Starting of the File
    R1R3----------R8R5
    R2
    Starting of the File
    R1R3----------R8R5R2
    End of the File

  2. Sorted File Method

    In this method, the new record is always inserted at the file's end, and then it will sort the sequence in ascending or descending order. Sorting of records is based on any primary key or any other key.

    In the case of modification of any record, it will update the record and then sort the file, and lastly, the updated record is placed in the right place.

    Starting of the File
    R1R3----------R8R9
    End of the File

    Suppose a new record R2 has to be inserted in the sequence given above, then it will be inserted at the end of the file, and then it will sort the sequence.

    Starting of the File
    R1R3----------R8R9
    R2
    Starting of the File
    R1R2R3----------R8R9
    End of the File

Advantages of Sequential File Organization

Disadvantages of Sequential File Organization


Heap File Organization - Heap File Organization works with data blocks. In this method records are inserted at the end of the file, into the data blocks. No Sorting or Ordering is required in this method. If a data block is full, the new record is stored in some other block, Here the other data block need not be the very next data block, but it can be any block in the memory. It is the responsibility of DBMS to store and manage the new records.

Suppose we have five records R1, R4, R5, R3 and R8 in a heap and suppose we want to insert a new record R2 in a heap. If the last data block that is data block 3 is full then it will be inserted in any of the data block selected by the DBMS, let's say data block 1.

If we want to search, update or delete the data in heap file organization, then we need to traverse the data from staring of the file till we get the requested record.

If the database is very large then searching, updating or deleting of record will be time-consuming because there is no sorting or ordering of records. In the heap file organization, we need to check all the data until we get the requested record.

Advantages of Heap File Organization

Disadvantages of Heap File Organization


Hash File Organization - Hash File Organization uses the computation of hash function on some fields of the records. The hash function's output determines the location of disk block where the records are to be placed.

For example, let us consider the following table Student;

Student_idNameAgePhone
1001Amit Das409748516231
1005Sohini Das329073919231
1008Debabrata Panchadhyay298420392064

A hash function is a function which maps the large set of values into smaller set of files/locations/values. Let us organize the above table using the phone attribute value as input for the hash function.

Consider that the Hash Function for the Student table is → h(s_id mod 5)

In the above hash function, s_id is the Student_id attribute’s value of each record. 5 is the number of buckets/pages where we want to store our table. [5 buckets means bucket0, bucket1, . . . , bucket4].

For our example,
For 1st record, h(1001 mod 5) = 1 that is, the first record has to be stored in 1st bucket.
For 2nd record, h(1005 mod 5) = 0 that is, the second record has to be stored in 0th bucket.
For 3rd record, h(1008 mod 5) = 3 that is, the third record has to be stored in 3rd bucket.

Important points for consideration

Insert Record - When a new record has to be inserted, then the address is generated using the hash key and record is directly inserted.

Let us assume that the following query is executed.

INSERT INTO Student VALUES(1007, 'Payel Chatterjee', 28, 9073456582);

We use the same hash function to insert the record.
h(1007 mod 5) = 2.

Searching Record - When a record needs to be searched, The same hash function is used to retrieve the bucket address for the record.

Let us assume that the following query is executed.

SELECT * FROM Student WHERE Student_id = 1008;

For searching the record, we have to use the same hash function that we used for storing the records. Hence, h(1008 mod 5) = 3. And the result points to the 3rd bucket. It actually gives us the quick access to the required record.

Advantages of Hash File Organization

Disadvantages of Hash File Organization


B+ Tree File Organization - B+ tree file organization is the advanced method of an indexed sequential access method. It uses a tree-like structure to store records in File. It uses the same concept of key-index where the primary key is used to sort the records. For each primary key, the value of the index is generated and mapped with the record.

The B+ tree is similar to a binary search tree (BST), but it can have more than two children. In this method, all the records are stored only at the leaf node. Intermediate nodes act as a pointer to the leaf nodes. They do not contain any records.

Advantages of Hash File Organization

Disadvantages of Hash File Organization


Indexed Sequential Access Method (ISAM) - ISAM method is an advanced sequential file organization. In this method, records are stored in the file using the primary key. An index value is generated for each primary key and mapped with the record. This index contains the address of the record in the file.

If any record has to be retrieved based on its index value, then the address of the data block is fetched and the record is retrieved from the memory.

Advantages of Indexed Sequential Access Method

Disadvantages of Indexed Sequential Access Method


Cluster File Organization - When the two or more records are stored in the same file, it is known as clusters. These files will have two or more tables in the same data block, and key attributes which are used to map these tables together are stored only once. This method reduces the cost of searching for various records in different files.

The cluster file organization is used when there is a frequent need for joining the tables with the same condition. These joins will give only a few records from both tables. In the given example, we are retrieving the record for only particular departments. This method can't be used to retrieve the record for the entire department.

Table Name : employee

emp_idemp_nameemp_agedept_id
1001Amit Das40102
1005Sohini Das32105
1008Debabrata Panchadhyay29102
1010Manirash Das28103
1012Payel Chatterjee28105


Table Name : department
dept_iddept_name
102JAVA
103HTML & CSS
105PHP


Cluster Key : dept_id

dept_iddept_nameemp_idemp_nameemp_age
102JAVA1001Amit Das40
1008Debabrata Panchadhyay29
103HTML & CSS1010Manirash Das28
105PHP1005Sohini Das32
1012Payel Chatterjee28

In this method, we can directly insert, update or delete any record. Data is sorted based on the key with which searching is done. Cluster key is a type of key with which joining of the table is performed.

Types of Cluster File Organization

  1. Indexed Clusters - In indexed cluster, records are grouped based on the cluster key and stored together. The above EMPLOYEE and DEPARTMENT relationship is an example of an indexed cluster. Here, all the records are grouped based on the cluster key - dept_id and all the records are grouped.

  2. Hash Clusters - It is similar to the indexed cluster. In hash cluster, instead of storing the records based on the cluster key, we generate the value of the hash key for the cluster key and store the records with the same hash key value.

Advantages of Cluster File Organization

Disadvantages of Cluster File Organization




Q6. a) Discuss the ACID properties of a database transaction with appropriate examples.

Answer : - A transaction in a database system must maintain Atomicity, Consistency, Isolation, and Durability − commonly known as ACID properties − in order to ensure accuracy, completeness, and data integrity.

Atomicity - A transaction must be fully complete, saved (committed) or completely undone (rolled back). A sale in a retail store database illustrates a scenario which explains atomicity, e.g., the sale consists of an inventory reduction and a record of incoming cash. Both either happen together or do not happen - it's all or nothing.

Consistency - The transaction cannot break the database’s constraints. For example, if a database table’s Phone Number column can only contain numerals, then consistency dictates that any transaction attempting to enter an alphabetical letter may not commit.

Isolation - A transaction should not make its updates visible to other transactions until it is committed; this property, when enforced strictly, solves the temporary update problem and makes cascading rollbacks of transactions unnecessary.

Durability - When a transaction has made a change to the database state and the change is committed, this change is permanent and should be available to all other transactions.




Q6. b) How are views created and dropped? Explain, how the views are implemented and updated?

Answer : - A view is a virtual or logical table that allows to view or manipulate parts of the tables. Views are useful for security of data.

Syntax :

CREATE or REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name(s) WHERE condition;

A view can be dropped using a DROP statement as :
DROP VIEW view_name;

There are many advantages using view :


Example :

A student''s database may have the following tables :
student ( name, enrolment_no, date_of_birth )
marks ( enrolment_no, subject_code, marks )

CREATE VIEW subject_performance AS SELECT s.enrolment_no, name, subject_code, marks FROM student AS s, marks AS m WHERE s.enrolment_no=m.enrolment_no AND subject_code='MCS-043' ORDER BY s.enrolment_no;

Once a view has been created, it can be queried exactly like a base table. For example :




Q6. c) Discuss 3-tier architecture with necessary diagram and suggest an example application for the real world domain.

Answer : - A 3-tier architecture is a type of software architecture which is composed of three “tiers” or “layers” of logical computing. They are often used in applications as a specific type of client-server system. 3-tier architectures provide many benefits for production and development environments by modularizing the user interface, business logic, and data storage layers. Doing so gives greater flexibility to development teams by allowing them to update a specific part of an application independently of the other parts.

Presentation Tier - The presentation tier is the front end layer in the 3-tier system and consists of the user interface. This user interface is often a graphical one accessible through a web browser or web-based application and which displays content and information useful to an end user. This tier is often built on web technologies such as HTML5, JavaScript, CSS, or through other popular web development frameworks, and communicates with others layers through API calls.

Business Logic or Application Tier - This layer contains the business logic that drives the application’s core functionalities. Like making decisions, calculations, evaluations, and processing the data passing between the other two layers. It’s often written in Java, .NET, C#, Python, C++, etc.

Data Tier - This layer is responsible for interacting with database/data storage system to save and restore application data. Examples of such systems are MySQL, Oracle, PostgreSQL, Microsoft SQL Server, MongoDB, etc. Data is accessed by the application layer via API calls.

3-Tier Architecture Example in Real World - A simple example of a 3-tier architecture in action would be logging into a media account such as Netflix and watching a video. You start by logging in either via the web or via a mobile application. Once you’ve logged in you might access a specific video through the Netflix interface which is the presentation tier used by you as an end user. Once you’ve selected a video that information is passed on to the application tier which will query the data tier to call the information or in this case a video back up to the presentation tier. This happens every time you access a video from most media sites.



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