Update DB Architecture

[Psyf]

Take the feedback from the alpha demo and fix the DB. Also, fix the ER.

[Psyf]

Awaiting reply on: https://ivle.nus.edu.sg/v1/forum/board_read.aspx?forumid=04b3c0f9-c700-4ce9-94c8-e898fd68f7c1&headingid=00000000-0000-0000-0000-000000000000&postid=21317e15-9700-49a6-85ed-6d2a5bd1a785&lpreferrer=

[Psyf]

Prof replied. Here is the discussion for reference:

Dear Profs,

Can weak entities be part of more relations than just one? Can weak entities have foreign keys that are not part of its primary key?

Example 1:

1. BOOKS HAS CHAPTERS. Chapters is a weak entity. Primary key of Chapters Table looks like (book_id, chapter_number)
2. ** USERS LIKES CHAPTERS. Here we assume USERS is a table with (user_id) and LIKES will hold (user_id, book_id, chapter_number). Notice here, USERS has a relationship with CHAPTERS (the weak entity). Question: Is this valid?

Example 2 (New and Separate Schema):

1. BOOKS HAS CHAPTERS. Chapters is a weak entity. Primary key of Chapters Table looks like (book_id, chapter_number).
2. Each Chapter is written by different authors.
3. AUTHORS WROTE CHAPTERS.
4. Instead of making the table WROTE in the implementaion, the DB designer decides a FOREIGN_KEY in CHAPTER called author_id that references AUTHORS is enough. Question: Is this valid?

Thanks.

Reply was the following:

Comment on Question 1: An entity set (whether or not it is a weak one) can participate in relationship sets. May I refer you to the discussions in Tutorial 2 where we have the weak entity set, Drugs, that is in involved in Prescribes and Sells relationships.

Comment on Question 2: If the assumption is that each chapter is written by exactly one author (i.e., there is a key constraint constraint on CHAPTERS w.r.t. WROTE as well as a total participation constraint on CHAPTERS w.r.t. WROTE, then it is possible to adopt the Second Approach (Slide 34 of Lecture 3) to derive the relational schema.

[Psyf]

Done in #24