(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.
(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 | _________ ____ ___ ___ ___ ________ __________ ______ _________ _______ __________ ___.
___ ______ _________ _____ __________.
_____ ____ ______ ________ _______ ___ _________ _____ _____.
_____ _____ ____ _____ ___ ___ ________ ___ _____ ______.
__________ ______ ____ _________ ____ _________.
____ __________ __________ ______ _______.
________ _____ _________ _________ ______ ________ ______ ____.
__________ ____ ______ ____ _____ ___ ______.
__________ ____ _________ ________ __________ _________ _________ __________.
______ _____ _________ ________ ___ ___ ________ ___ ________ ______.
___ _______ ______ ______ _____ ________ ____ _________ ________.
______ ________ ______ ________ _____.
_________ ________ _______ __________ ___ ________ ___.
___ _________ ____ __________ ___ ____.
_________ _____ ___ ________ __________ ___ __________ _________ _______ __________.
________ _______ ________ ___ _____ ______ ___ ______ ______ _________ ______.
____ ___ ______ ______ ______ _____.
________ _____ ______ _____ ____ _________ ___ _________.
_______ ____ ______ __________ ___ _______ _________ ________ _______.
_________ ______ _______ _____ _____ __________.
___ _____ _____ __________ __________ _____ _____ ____ ________ __________.
___ _________ _________ ____ ____ ___ ___ ___.
__________ ________ _____ _________ ____ ______ ______ ___ ___ ____.
_________ _____ ___ ______ ________ ____ ______ __________.
____ ____ ____ ____ ________ ____ ____ ____ _________ ____.
____ ______ _______ ____ ___ ______ _____ ______ ___ ____ _______.
________ _____ _________ ___ _________ ______ ___ ________ _______ ____ ________.
________ _____ ________ _________ _________ ____ _______ ____ ________ _________ _______.
________ ________ _______ ____ __________ _____ ________ _________ ____ ________ _________ __________.
___ ______ ________ __________ _____ _______.
________ ____ ______ ________ ____ ______.
___ _____ __________ _________ _________ ____ ________ __________ ______ _____ _______.
____ ________ ________ _________ ______ _________.
________ ________ ___ _______ _______.
_____ ___ _________ ____ __________ ______ _____ _____ ______ _____ _________ ______.
______ _________ ________ _________ _____ _______ ________ _______.
_______ __________ ________ ___ ______ ____ __________.
_____ _____ ______ _______ ____ ____.
_________ __________ __________ __________ __________ ____.
_____ _____ ___ _____ _______ _______ _____ ____ ___ _________ _________ _______.
__________ ______ __________ __________ _______ ___ __________ ________ ___ ________.
_________ __________ _____ ______ _________ _________.
_______ _____ _____ ______ _______ _____ _______ __________ __________ __________ _________ ___.
____ __________ __________ ________ ________.
______ _________ ___ ____ __________ _________ ______ ___ ______ ________ _________ ___.
________ ___ _______ ___ ________ _________ ________ __________ ________.
_________ _______ __________ _________ ___ ________.
______ __________ __________ ___ __________ ________ ______ _________.
_______ __________ ________ _________ _______ ____ __________ _____ ______ ___ ____.
________ ___ ___ ______ ____ _______.
_______ ______ __________ ________ _____ _____ ______ _________.
_________ ___ __________ _________ _______ ___ ___ ________ _______ _______ _______ ______.
___ _________ ________ __________ ___ _______ _________ _____ _______.
__________ _____ _______ ________ ___.
___ _____ ______ ________ ____ __________.
___ ____ ___ ____ ______ _________ ____ ______ ___.
________ ______ ________ ___ _______ ______ ______ ___ _________ ________.
________ _______ ________ _________ ______ ________ ____.
___ ___ ________ ______ _____ ___ _______ ______ __________ ____ _________ _______.
_________ _____ _____ _________ ______ __________ ______ _______ _____ _____ _____ ______.
_____ _________ __________ __________ __________ _______ _______ ____ _____ ____ _______ ________.
_______ ______ _______ __________ _________ __________ _________ ___.
______ ___ _______ __________ _________ ________ __________ _______ ___ __________ __________.
______ __________ _________ ______ _______ ________ _________ __________ ______ _____.
______ _________ ______ ________ _____ _________ __________ ___ ____ ____.
_______ ___ ______ ____ ___ ________ ___ ____.
_________ ______ _________ ___ __________ ________ _______ _________.
___ _____ _______ ________ _________ _____ _______ ____.
___ ____ __________ _______ _______ ___ _______ _______ _______.
______ ______ ______ ____ _________ _____ _________ _________.
_________ __________ _______ ___ _________ ______ _______ ________ __________ ________.
___ ____ __________ _____ _______.
_______ _______ _______ _______ ___ __________ _________ ____ ____ ________ ___ ___.
Get Full Answer on WhatsApp