) What are the ACID (Atomicity, Consistency, Isolation, Durability) properties of a transaction? Explain each property with a suitable example related to an online banking fund transfer.

(a) Consider a Relation: Course(CourseCode, CourseName, ProgrammeCode, ProgrammeName, CourseCredit, CourseDuration, Programme Duration, ProgrammeCredit). Some of the constraints on the relation Courseare:

 
• CourseCode uniquely identifies a course.



 
• ProgrammeCode is a unique code of a programme. A programme consists of many courses. A course can be part of multiple programmes.



 
• A Programme consists of compulsory courses and optional courses. To complete a Programme, a student must complete all the compulsory courses and optional courses, as per the total credit requirements of a Programme.



 

Perform the following tasks for the relation given above:

(i) What is the key to the relation?

(ii) Identify and list the functional dependencies in the relation.

(iii) Make an instance of this relation consisting of at least 8 to 10 records, showing possible redundancies.

(iv) Decompose the relation Courseinto 2NF and 3NF relations.

(b) What is multi-valued dependency? Explain with the help of an example. How can it be used to decompose a relation into the 4th Normal Form? Explain with the help of an example. Also, explain the concept of the Join dependency with the help of an example.

(c) Explain the following terms with the help of an example of each: Views, Embedded SQL, Triggers and Dynamic SQL.

(d) Consider the following relational database:

Member (memberId,memberName, memberAddress, memberPhone)

BookIssued (memberID, BookID, Issue Date, returnDate)

Book(BookId, BookTitle, FirstAuthor)

The underlined attribute(s) in the relations given aboveform the primary key. In relation Member the attribute memberId is the unique identifier of a member. The purposes of other attributes in the Member relation are self-explanatory. You may define the domain of different attributes. Pleasenote that the BookIssued relation has two foreign keys,namely memberID and BookID, which reference the Member and Book relations, respectively. Write and run the following SQL queries on the database:

(i) Create the tables with the primary and foreign key constraints.

(ii) Insert at least 5 records each in the Member and Book tables and about 10 records in the BookIssuedtable.

(iii) List the name and phone number of all the members in lexicographical order.

(iv) Find all the Books issued to the member whose memberID is "0001".

(v) a book is not returned if returnDate is NULL) Find the list of those members who have not returned more than one book. (Assume that

(vi) Find the number of books whose first author's name is "XYZ".

(vii) Find the pair of members who have the same memberPhone.

(viii) Find the list of Books whose title includes the term "Database".

(ix) Find the title of the book that has been issued the most.

(x) Find the list of members who have not issued any books.

There are four questions in this assignment, which carries 80 marks. Rest 20 marks are for viva voce. You may use illustrations and diagrams to enhance the explanations. Please go through the guidelines regarding assignments given in the Programme Guide for the format of the presentation. The answer to each part of the question should be confined to about 300 words. Make suitable assumption, if any.

Question 1: (Covers Block 1)

(a) What are the limitations of file-based systems? How does a database approach resolve those limitations? Also, explain the physical architecture of a database management system (DBMS).

(b) Explain the following terms in the context of a relational model with the help of one example of each:

(i) Key constraints

(ii) Domain constraints

(iii) Candidate key

(iv) Select operation

(v) Project Operation

(vi) Equijoin Operation

(vii) Set Difference operation

(viii) Referential Integrity constraint

(c) A departmental store maintains the inventory of all the items that it sells using a database system. In addition, this system is used for keeping a record of the sales of items to its registered customers. This database is used to find the following details by the staff of the departmental store:

 
• List of the items that are out of stock.



 
• List of customers and the items purchased by them.



 
• List of all the items in the store.



 

Draw an ER diagram for the departmental store. Specify key attributes and constraints on each entity type and on each relationship type. Note any unspecified requirement and make appropriate assumptions to make the specification complete.

(d) Design normalised tables in 3NF for the ER diagram drawn in part (c), with the required integrity constraints.

(e) Explain the role of the primary index in a database system. Also, compare the primary indexes with the secondary indexes. What are clustering Indexes? Explain with the help of an example

 জি.সি. তোংব্রাগী 'নীংশিংলগা ইবা হায়বা ৱারেং অসি করম্বা মখগী ৱারেংনো? ৱারেং অসিনা পীনীংলিবা পাউজেল অদু মরিক চুন্না নৈনৌ।

মহাকাব্য কদায়বু কৌবগে? মহাকাব্য অমা ওইনা খম্ব- থৌইবী শৈরেং নৈনৌ। 

 ফোকলোর কদায়বু কৌবগে? ফোকলোরনা মণিপুরী সমাজদা লৌরিবা থৌদাং নৈনৌ। 

. ময়ায়চনবা মতমগী লাইরিক অমা লৌখতুনা লাইরিক অদুদা য়েক্লিবা লৈঙাক্লোনগী মশক চূন্না নৈনৌ। 

অঙনবা মতমগী নুংশি-নুংওন্দা য়ূম্ফম ওইবা লাইরিক অমা লৌখতুনা লাইরিক অদুদা য়েক্লিবা নুংশি-নুংওনগী মশক মরিক চূন্না নৈনৌ। 

. সিলেবল কদায়বু কৌবগে? সিলেবল অমদা তঙায়ফদনা য়াওদবা যাদবদু করিনো? সিলেবলগী মখলশিং অদু খৃদমগা লোয়ননা নৈনৌ। 

. ৱাহৈপরেং কদায়বু কৌবগে? ৱাহৈপরেংগী মখলশিং অদু খুদমগা লোয়ননা নৈনৌ। 

 রাজকুমার শীতলজিৎকী 'ইন্থোকপা' ৱারীমচাগী থিম নৈনৌ। 

 চিংখুবনা খম্বদা থোনখিবা মমিং অদু করিনো? মমিং অদু করিগীদমক থোনখিবনো শুগায়না নৈনৌ। 

 ফোকশোং কদায়বু কৌবগে? মণিপুরী ফোকশোংগী মশাজৎ নৈনৌ। 

ময়ায়চনবা মতমগী মণিপুরী সাহিত্যদা চঙশিল্লকখিবা লোনশিং অদু খৃদমগা লোয়ননা মশক তাকউ। 

. মৈতৈলোন্দা এফিক্স মখল কয়া ফংবগে? খৃদম পীদুনা তাকউ। 

রুট কদায়বু কৌবগে? মখলশিং অদু খৃদমগা লোয়ননা শন্দোক্লা তাকউ। 

 ইপুরোইবা য়াওনা চঙবা য়ারমদ্রবা ঘুমদুদা মাসু য়াউদ্রগা ঐনা করম চংফম থোকপগে

 তাউথরক্লে মতুম মতুম অমোৎপা অমাংবা, মহাজনগী ধুপকী কম্বীসু মদুগী মরক্তা 

 প্রোভার্ব (পাউরৌ) 

চৈথারোল কুম্বাবা