IGNOU MCS 207 2025 2026 - (a) Consider a Relation: Course(CourseCode, CourseName, ProgrammeCode,

Question

(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.

(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.

01 Dec 2025

Answer :

Word Count : 836

(i) The key to the relation Course is a composite key consisting of `(CourseCode, ProgrammeCode)` because a course can be part of multiple programmes and a programme has multiple courses, so neither `CourseCode` nor `ProgrammeCode` alone can uniquely identify a record in this relation. (ii) The functional dependencies in the relation are: * `CourseCode → CourseName, CourseCredit, CourseDuration` * `ProgrammeCode → ProgrammeName, ProgrammeDuration, ProgrammeCredit` * `(CourseCode, ProgrammeCode) → (all remaining attributes)` (iii) An instance of the relation with possible redundancies: | CourseCode | CourseName | ProgrammeCode | ProgrammeName | CourseCredit | CourseDuration | ProgrammeDuration | ProgrammeCredit | | ---------- | ------------- | ------------- | ------------- | ------------ | -------------- | ----------------- | --------------- | | C101 | DBMS | P01 | BSc CS | 4 | 4 months | 3 years | 120 | | C102 | OS | P01 | BSc CS | 3 | 3 months | 3 years | 120 | | C101 | DBMS | P02 | BSc IT | 4 | 4 months | 3 years | 118 | | C103 | Networks | P01 | BSc CS | 3 | 3 months | 3 years | 120 | | C104 | AI | P02 | BSc IT | 3 | 4 months | 3 years | 118 | | C102 | OS | P02 | BSc IT | 3 | 3 months | 3 years | 118 | | C105 | Software Eng. | P01 | BSc CS | 4 | 4 months | 3 years | 120 | | C105 | Software Eng. | P02 | BSc IT | 4 | 4 months | 3 years | 118 | (iv) Decomposition into 2NF and 3NF: * 2NF: Remove partial dependencies. Create separate relations: CourseDetails(CourseCode, CourseName, CourseCredit,

_________ _____ ________ ____ __________ _______ ________ _____ _________.
________ ___ _________ ___ _________ ______ __________ _____ __________ ____ ____.
__________ ________ ___ _______ ____ _________ _______ ______ _____ ________.
_______ ___ ___ _____ _____ _______ __________ ____.
___ ___ ____ _________ _______ ___.
__________ _________ ___ __________ ____ ____ ___.
____ ______ _________ ________ ________ ______ ____ ______ _________ _________.
_______ __________ ______ _________ __________ ___ _____ _____ _____ _________ ___.
________ ________ _________ _______ ________ ________ __________ ________ ______ __________.
____ ____ __________ ____ ________.
_________ _____ ______ __________ ___ _______.
_____ ____ __________ __________ _________ ______ ______ ______ _____ _______ ___ ___.
_________ _______ ________ _________ _____ _______ _____ ________.
__________ _______ _________ ___ ________.
_______ ______ _____ ___ ____ _______.
________ __________ ______ ___ ______ ________ _____.
__________ ______ ______ ______ ___ ________ _____ __________ ______ ________.
___ __________ __________ ______ _____.
__________ ______ _______ ______ ___ ________ _________ ________ ____ _______ _______ ___.
________ ____ _________ _______ _________ __________ _________ __________.
____ __________ __________ _____ ____ __________ _____ _______ ___ __________ _______.
__________ _______ ____ ___ ___ ______.
___ __________ ___ ____ ______.
_______ _____ __________ ___ _________ _________ ___ ___ __________ _______ ___ __________.
_____ _______ _________ _________ ___ _____ ______ _________ ________.
_________ _________ ________ _____ _______ ___ _____ ___.
___ _____ _____ ____ __________ ___ _______ _______ ____ ________ ________.
____ ______ ________ _______ ___ ____ _____ __________ ______ ________ ___ ___.
____ ___ ________ ____ ____ ____ ______ _________ _____ ____ ______ __________.
______ ____ ________ ______ _____ ________ _________ ____ ____.
_______ _____ _________ _____ ____ _________ ________ __________ ______ _______.
___ _______ __________ _______ _____ _________ ________ ______ ______ _____ ___ __________.
_________ ________ ______ ________ __________.
_______ ______ ______ _________ _______.
_________ ______ __________ _____ _______.
____ __________ ___ __________ ____ ________ _______ ________ _______.
_________ __________ ____ ___ _________ _________ ________ ____ ____.
____ _______ __________ __________ ________.
______ _________ _________ ______ ___ ____.
__________ ________ ______ ____ ________ ________ __________ ___ ______.
_____ _____ ___ _____ ___ ___.
__________ ____ __________ ____ ___ ____ _________ __________.
_________ _______ __________ ____ _________.
______ __________ _________ __________ _________ ________ _____ _________.
___ ___ ____ _______ __________ _______ __________ _____ _______ _________ __________.
________ _________ ______ _______ ____ __________ _____ ___ __________ _________ _________ _____.
_____ _______ ___ ________ ___ ___ _______ ________.
_____ ______ ______ ______ ____ _____.
_____ _____ _______ ___ ____ ______ ____ ________ _________ _______ _______.
_____ _____ __________ ______ ______ ___ ___ ______ ___.
__________ _____ ____ _______ ___ ____ ________.
___ ______ _____ __________ ____ ________ ___ ____ ______ ____.
________ ________ _________ ______ _________ ___ ______ __________.
__________ _______ ____ _______ ___ ___ ________ ________ ____.
______ _________ __________ ___ _______ _____ ______ __________ __________ ____ _________.
_____ ____ ___ _________ ______ ______ _____ ____ ____ ___ _____ __________.
_______ __________ ____ _______ _______ ___ ____ ________.
_____ ______ _______ _________ ________ _____ ___ _______ ________.
_________ ______ _______ ________ ________ ________ ___ _____ ______ ___ _____ _________.
_______ __________ ____ ______ ______ ________ _________ _______ ________ __________.
______ ______ ________ ________ _________ _______ ________ ____ _________ ________ ____ _______.
___ ______ _________ ____ ____ __________ _____.
______ ________ ___ ________ ________ ____ ______.
________.

IGNOU ACADEMY

Your IGNOU journey, made easier.

Solved Assignments
Study Notes
Guess Papers
Question Papers

Call Us WhatsApp

STUDY • PREPARE • SUCCEED

Skip in 10

Related Questions

me	T1	T2
t1	READ(A)
t2	A = A - 100
t3		READ(A)
t4		A = A * 1.1
t5		WRITE(A)
t6		READ(B)
t7	WRITE(A)
t8	READ(B)
t9	B = B + 100
t10	WRITE(B)

IGNOU Solved Assignments Assignments Ignou Projects Project Previous Year Solved Exam Papers Notes Notes Sample Question Papers Sample Question Papers