Question: 1. (a)- Consider that you are asked to design an entity relationship diagram based on the below scenario:
A university consists of a number of departments (id, d_name) and each department offers some courses. A number of modules make up each course. Students enroll their Id, full name, and gender in a particular course and take modules towards the completion of that course. Each module is taught by a lecturer from the appropriate department.
(b)- We can convert any weak entity set to a strong entity set by simply adding appropriate attributes. Why, then do we have weak entity sets? Write about the weak entities for several reasons.
2. Assume we have a Library Database consists of the following relations:
author(author_id, first_name, last_name)
author_publication(author_id, publication_id, author_position)
book(book_id, book_title, month, year, editor)
publication(publication_id, title, book_id)
Note: (editor) in book's relation refers to (author_id) in author's relation. Some of authors are book editors.
(a) Underline the appropriate primary keys/foreign keys in the above relations.
(b) Write two relational algebra expressions that retrieve:
- The last names of all authors who are book editors only.
- The first name of all authors who are not book editors.