can someone help me with this 1. The ERD below is a conceptual model of a bookst

Question

can someone help me with this

1. The ERD below is a conceptual model of a bookstore. Most attributes should be clear to understand and may not require additional explanation. The attribute sup category represents preference of the company for a particular supplier. The values for this attribute 5 could be "highly preferred" or "not at all preferred" or some other labels. The attribute sup type represents what kind of content a supplier can provide. The company retails different content types, such as books, magazines, games, multimedia, and so on. 2. Map the given E-R diagram to a Relational model. Use all standard conventions. Establish associations between the tables by inserting "Foreign Keys." Now assign domains to all attributes. This involves choosing appropriate data types and a range of acceptable values tor each attributes SUPPLIER sup Name sup Address sup City sup_Phone sup-Email sup_Type sup Category STORE SHELF st Name st-Address st Phone st Zip st_Region Located-in- shf Num shf_Location BOOK SBN Title Genre Carry -_--Edition Fulfills Year Cost PORDER PO Number Date Delivery_Date

Explanation / Answer

Ans 1-

CREATE TABLE SUPPLIER(SUP_NAME VARCHAR(10) PRIMARY KEY,SUP_ADDRESS VARCHAR(20),SUP_CITY VARCHAR(10),SUP_PHONE NUMBER(10),SUP_EMAIL VARCHAR(10),SUP_TYPE VARCHAR(20) CHECK(SUP_TYPE='Highly Preferred'||SUP_TYPE='not at all preferred'),SUP_CATEGORY VARCHAR(10));

CREATE TABLE PORDER(PO_NUMBER VARCHAR(10) PRIMAY KEY,DATE_ORDER DATE,DELIVERY_DATE DATE);

CREATE TABLE STORE(ST_NAME VARCHAR(10) PRIMARY KEY,ST_CITY VARCHAR(10),ST_ADDRESS VARCHAR(20),ST_PHONE NUMBER(10),ST_ZIP NUMBER(6),ST_REGION VARCHAR(20));

CREATE TABLE BOOK(ISBN VARCHAR(10) PRIMARY KEY,TITLE VARCHAR(20),GENRE VARCHAR(20),EDITION VARCHAR(15),YEAR NUMBER(4),COST NUMBER(7,2),SALE_PRICE NUMBER(7,2));

CREATE TABLE SHELF(SHF_NUM VARCHAR(10) PRIMARY KEY,SHF_LOCATION VARCHAR(20));

CREATE TABLE ORDERS(PO_NUMBER VARCHAR(10) FOREIGN KEY REFERENCES PORDER(PO_NUMBER), ISBN VARCHAR(10) FOREIGN KEY REFERENCES BOOK(ISBN));

CREATE TABLE FULFILLS(SUP_NAME VARCHAR(10) F0REIGN KEY REFERENCES SUPPLIER(SUP_NAME), PO_ORDER VARCHAR(10) FOREIGN KEY REFERENCES PORDER(PO_NUMBER));

CREATE TABLE LOCATED_IN(SHF_NUM VARCHAR(10) FOREIGN KEY REFERENCES SHELF( SHF_NUM),ST_NAME VARCHAR(10) FOREIGN KEY REFERENCES STORE(ST_NAME));

CREATE TABLE CARRY(SHF_NUM VARCHAR(10) FOREIGN KEY REFERENCES SHELF( SHF_NUM),ISBN VARCHAR(10) FOREIGN KEY REFERENCES BOOK(ISBN));

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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