Translate your E-R diagram for problem given (attached image) into a relational

Question

Translate your E-R diagram for problem given (attached image) into a relational model, i.e., a set of CREATE TABLE/ASSERTION statements enforcing all stated constraints. In addition, write a CREATE ASSERTION statement to ensure that each user cannot read more than 5 messages under each category

.

Translate your E-R diagram for problem given (attached image) into a relational model, i.e., a set of CREATE TABLE/ASSERTION statements enforcing all stated constraints. In addition, write a CREATE ASSERTION statement to ensure that each user cannot read more than 5 messages under each category

Explanation / Answer

1. Create Table Category ( category_id Number(5), cat_name VARCHAR2(10), cat_keylist VARCHAR2(10), CONSTRAINT userid_pk PRIMARY_KEY(category_id ),CONSTRAINT msgid_fk FOREIGN_KEY(message_id) REFERENCES Message(message_id),);

2. Create Table User ( user_id Number(5), password VARCHAR2(12), name VARCHAR2(10), email VARCHAR2(10), category_id Number(5), CONSTRAINT userid_pk PRIMARY_KEY(user_id) , CONSTRAINT catid_fk FOREIGN_KEY(category_id ) REFERENCES Category(category_id));

3. Create Table Message(message_id Number(5), subject VARCHAR2(10), msg_keywords VARCHAR2(10), CONSTRAINT userid_pk PRIMARY_KEY(message_id),CONSTRAINT msg_limit CHECK( COUNT(message_id ) = 5);

As per the constraint that each user cannot read more than 5 messages from each category it is clear that messages should be of five in count so we have a CHECK constraint to acheive this.

Category table can only have 5 or less than 5 messages for each category.

So cannot read more than 5 messages under each category.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.