Dear students, get fully solved assignments by professionals
Do send your query at :
or call us at :08263069601
MCS-023 ASSIGNMENT SOLUTION (2020-21)
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 Name | Data Type | Constraint | Description |
| cust_id | int | Primary Key | Uniquely identify each Customer in this table |
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 : –
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
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)
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
(ii) List the parts whose colour is BLUE.
Answer : – SELECT p_id, p_name
(iii) List those parts which are RED and whose cost is more than Rs.2000/- .
Answer : – SELECT Parts.p_id, p_name
(iv) List all the suppliers who are from GUJARAT.
Answer : – SELECT s_id, s_name FROM
(v) List all the part_ids, colour, cost from the supplier EZPREX.
Answer : – SELECT DISTINCT Parts.p_id, p_color, p_cost
(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.
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.
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.
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;
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.
Dear students, get fully solved assignments by professionals
Do send your query at :
or call us at :08263069601