ERD diagram assignment

ERD FOR UC Online

Requirement: Each book has an id, title, publication date, publisher, ISBN, functional category, page count, price (publisher), and product description

. Please make ‘id’ for ‘BookID’. All columns name in lower case.

. Price is missing.

. Please Remove table name ‘Book’ in columns in same table.

Like BookTitle to title.

. Please edit int(100) to just int. and varchar(100) to varchar

Requirement: There are 3 categories of book: fiction, non-fiction, and specialty

. Why all the relationships have (o) optional, a book must have a category. (|) required in the fork?

Requirement: An author can write one or more books.

. Same issue optional? It’s one or many. (o) to (|).

Requirement: A musician can be a solo artist, part of a group, or both

. Do we need a group entity where an artist might be in a group? Assume if I need all artist from a same group, how can I find?

Requirement: An author can also be associated with one or more publishers.

. Where’s the relationships between author and publisher?

Requirement: A musician/group/act can be associated with one or more music houses

. Why optional? It says one or many.

Requirement: An author’s book can have one or more editions.

. Do we need another Book_Edition entity to maintain this relationship?

Requirement: Many ISBNs can also be associated with a given publisher but only one ISBN per form of medium (ie the ISBN is the PK no matter what).

. Do we need to make isbn primary key as well? A table can have multiple primary key.

Requirement: UCOnline needs to keep track of user ratings for each item sold in the online store (e.g., 1-5 and 1 for ‘Dislike’, to 5, for ‘Awesome!’).

. Book, Song, Album must be associated with Ratings?

Requirement: A particular user rates a particular item at a given time.

. Relationship?

Requirement: A user CANNOT rate the same multiple times unless there are multiple editions of the item. Each edition can be rated separately.

. Relationship?

Requirement: Each user who submits a rating will be identified by a unique id and has a name, gender, age, and location. Nothing is anonymous.

. Need a new entity to have this feature and relationships with customer, Book, Song, Album?