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