The small town mentioned in Part 1 is growing and now has three library branches

Question

The small town mentioned in Part 1 is growing and now has three library branches from which residents can borrow items. Each branch has a full-time supervisor and both full- and part-time employees. All non-reference media can be checked out, but if the media is not returned by the due date a warning is sent and the member is charged 10 cents for each day the item is late.

The library database is for staffing and circulation. You are not responsible for storing information about computers or computer usage. Library maintenance is also outside the scope of this database.

Add to or edit and enhance the corrected diagram from Part 1 to include this additional information. Then, describe the normalized tables: primary keys, foreign keys, and data types.

Begin with the corrected diagram from Part 1, using Word or any software that allows you to enhance the diagram. You may also hand draw the UML and use a scanner or digital camera to capture the image.

Uploaded the old uml diagram and the new one with the changes made. Reference questions above pertaining to the diagrams.

Old UML Diagram

New UML Diagram with the changes

EMP NO (PK) NAMI ADDRESS AGE registers RECORD NO (PK) CARD NO (PK) OUTDATE NAMI DUE DATE ADDRESS RETURN DATE CONTACT generate DATE WARNINGNO MESSAGE PUBLISH PUB NO (PK) NAM ADDRESS SBN NO published by AUTHOR CATEGORY PUBLICATION DATE COPY NO COPY NO READER NUMBER PAGES LENGTH SUPP NO (PK) NAME ADDRESS CONTACT supplies is MEDIA NO (PK) NAME DATE OF REQUISITION DATE OF DISPOSAL REFERENCE ONLY NAMI DEO NO VOLUME COPY NO ISSUE LEAD ACTOR DATE DIRECTOR CATEGORY TYPE LENGTH NEWS DATE LEAD ARTICLE

Explanation / Answer

CREATE TABLE LIBRARY_DETAILS(LIB_NO NUMBER PRIMARY KEY,
L_NAME VARCHAR2(50),
ADDR VARCHAR2(100));

CREATE TABLE LIBRARIAN(EMP_NO NUMBER PRIMARY KEY,
L_NAME VARCHAR2(50),
ADDR VARCHAR2(100),
L_NO NUMBER ,
CONSTRAINT FOREIGN KEY L_NO REFERENCES LIBRARY_DETAILS(LIB_NO));

CREATE TABLE SUPPLIER(SUPP_NO NUMBER PRIMARY KEY,
L_NAME VARCHAR2(50),
ADDR VARCHAR2(100),
L_NO NUMBER
);

CREATE TABLE MEMBER(CARD_NO NUMBER PRIMARY KEY,
L_NAME VARCHAR2(50),
ADDR VARCHAR2(100),
CONTACT NUMBER,
EMP_NO NUMBER,
CONSTRAINT FOREIGN KEY EMP_NO REFERENCES LIBRARIAN(EMP_NO)
);

CREATE TABLE MEDIA(MEDIA_NO NUMBER PRIMARY KEY,
L_NAME VARCHAR2(50),
DATE_OF_RETURN DATE,
DATEOF_ISSUE DATE,
REFERENCE_ONLY VARCHAR2(10));

CREATE TABLE CHECKOUT(RECORD_NO NUMBER PRIMARY KEY,
OUTDATE DATE,
DUE_DATE DATE,
RETURN_DATE DATE,
MEDIA_NO NUMBER,
CONSTRAINT FOREIGN KEY MEDIA_NO REFERENCES MEDIA(MEDIA_NO));

CREATE TABLE WARNING(CHECKOUT_NO NUMBER,
DATE_D DATE,
WARNING VARCHAR2(10),
MESSAGE VARCHAR2(100),
CONSTRAINT FOREIGN KEY CHECKOUT_NO REFERENCES CHECK_OUT(RECORD_NO));

CREATE TABLE PUBLISHER(PUB_NO NUMBER PRIMARY KEY,
PNAME VARCGAR2(10),
ADDR VARCHAR2(100));

CREATE TABLE RETURN_TABLE(RECORD_NO NUMBER ,
CHARGES_PER_DAY NUMBER,
CONSTRAINT FOREIGN KEY RECORD_NO REFERENCES CHECK_OUT(RECORD_NO));

CREATE TABLE BOOKS(ISBN_NO NUMBER ,
AUTHOR VARCHAR2(100),
CATEGORY1 VARCHAR2(100),
PUB_DATE DATE);

CREATE TABLE AUDIO_BOOKS(COPY_NO NUMBER ,
READER VARCHAR2(100),
LENGHT NUMBER,
CONSTRAINT FOREIGN KEY COPY_NO REFERENCES BOOKS(ISBN_NO));

CREATE TABLE AUDIO_BOOKS(COPY_NO NUMBER ,
NO_OF_PAGES NUMBER,
CONSTRAINT FOREIGN KEY COPY_NO REFERENCES BOOKS(ISBN_NO));

ALTER TABLE BOOKS ADD CONSTRAINT FOREING KEY PUB_NO REFERENCES PUBLISHER(PUB_NO);

SIMILARLY YOU CAN AMKE THE TABLES VIDEOS,MAGAZINES AND NEWPAPER.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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