SYNOPC ER MODEL

I am currently taking a course in databases, and the first thing we are learning is to create ER models, which are used to map out our Entities (nouns), the Relationships between these entities as well as the attributes associated. I have decided to redevelop a website I had started using mongoDB, called SynopC. I will develop it using SQL instead, following along with the methodologies learned in the course and I shall blog my experience throughout. This first blog is the ER model of this site.

Entities

Book

This entity will allow books to be stored, will most likely define them from an API, but may just have users request books to be added (since this will mostly be a personal site). The main attributes associated are:

  • Primary key is ISBN (shown by underline), it is atomic, stored and single value.
  • Genre is multivalued (shown through double circles), atomic, stored.
  • Publish date is single, atomic, date.
  • There can be 1 first book with many sequels. But many sequels will only have one first book is the series

Synopsis

Users will write a synopsis on books they choose, attempting to be as concise as possible. They will be rated on the content and how dumbed down they are. The more concise and efficient the synopsis, the better. Notice synopsis is a Weak entity, so doesnt have its own primary key.

  • Uses Book ISBN and User ID as its Primary key {ISBN, User ID}. Thus foreign key ISBN references Book, foreign key userID references User.
  • Weak entity since its Primary key (foreign key) belongs to Book and User respectively, thus doesn’t exist without either of those entities.
  • Contains actual synopsis writing in ‘synopsis content’.
  • Rating is an integer dependant on the rating users give.

User

  • User ID will be given to new user, Primary key.
  • Name is a composite key (first and last name).
  • Email is a string, single, atomic and stored value.
  • Username is a string, single, atomic and stored value.

Author

  • Primary key is authorID.
  • Age is a derived value (from birthdate – currentdate).
  • Name is a composite value (composed of first name and last name attributes).
  • Description of the author will describe their life.
  • Birth date is the day they were born

Relations

Written By

  • This is a relationship between Author and Book entities
  • Many books can be written by many authors, many authors can write many books
  • In order tobe an author they must have written a book

Wrote

  • This is between user and synopsis, it was originally weak, but I switched it since like reddit, if someone deletes their account, the comment stays up but the user shows [deleted] if user deletes their account, the rating stays as well
  • One user can write many synopsis, specific synopsis can only be written by one user

Rated

  • Many users can rate many synopsis, many synopsis can be rated by many users. But user can only rate certain synopsis once (this is from the rating attribute)
  • Uses ISBN and synopsis_user_id and rating_user_id as foreign key to store the rating from the user (so cant rate same book synopsis again).See for rating system

Has Read

Many users can read many books, many books can be read by many users. Book has been read by User.

Follows

Many users can follow many authors, many authors can be followed by many users

Described By

One book can be described by many synopsis, many synopsis can describe one book (synopsis can only be associated with certain book)