ITEC 324S Database Principles Assignment #4-The Relational Database Model-Introd

Question

ITEC 324S Database Principles Assignment #4-The Relational Database Model-Introduction Reund chapter S carefully and do the following tasks I. The Henry Books Store (Updated) Ray Henry is the owner of a bookstore chain named Henry Books. Ray wants to keep track of his dats in a database Note the following facts about the environment - The database keeps track of branches, publishers, books, book types, authors, and inventory of books in each branch. It also keeps track of the authors of each book Both branch number and branch name are unique in the BRANCH relation. - Both publisher number and publisher - Both book code and book title are unique in the BOOK relation. name are unique in the PUBLISHER relation author can write many books and a book can be written by many authors. A record in the WROTE relation is identified by the combination of book code and author number - A book falls into a type. - A book can be in many branches and each branch can have many books. An inventory is klentified by the combination of book code and branch number a. BRANCH Relation TION NUM EMPLOYy .PUBLISHER Relation C. AUTHOR Relation d. BOOK TYPE Relation e. BOOK Relation PUBLISHER COETYPEPR?CE PAPERBACK . INVENTORY Relation g, WROTE Relation DE AUTHOR NUM SEQUENCE First, create a database using the attached sql file (henry bookstore modified.sqh and MySQL Workbench. Then answer the following questions (Please refer to the tutorial video, MySQL Installation and How to Run Script, if you don't know how to create a database using MySOL Workbench).

Explanation / Answer

Answers:

a. BRANCH_NUM, BRANCH_NAME, PUBLISHER_CODE, PUBLISHER_NAME, AUTHOR_NUM, TYPE, BOOK_CODE, TITLE
b. BRANCH_NUM, BRANCH_NAME, PUBLISHER_CODE, PUBLISHER_NAME, AUTHOR_NUM, TYPE, BOOK_CODE, TITLE
c. AUTHOR_NUM, TYPE, BOOK_CODE, BRANCH_NUM, PUBLISHER_CODE
e. MANY-to-MANY: Inventory and Wrote relation,
PK: BOOK_CODE, BRANCH_NAME, AUTHOR_NUM
Intersection Data: BOOK_CODE
f. i) select * from BOOK where BOOK_CODE = 2226;
ii) select * from BOOK where TYPE like 'History';
iii) select * from BOOK where price between 10 and 20;
iv) select BOOK.BOOK_CODE, TITLE, PUBLISHER_CODE, TYPE, PRICE, BRANCH_NUM, BRANCH_NAME from BOOK, INVENTORY, BRANCH
where BOOK.BOOK_CODE = INVENTORY.BOOK_CODE and INVENTORY.BRANCH_NUM=BRANCH.BRANCH_NUM and BRANCH_NAME LIKE 'Henry Brentwood';
v) select count(*) from Inventory, Branch where INVENTORY.BRANCH_NUM=BRANCH.BRANCH_NUM and BRANCH_NAME LIKE 'Henry Brentwood';

vi) select BOOK.BOOK_CODE, TITLE, PUBLISHER_CODE, TYPE, PRICE, AUTHOR_NUM, AUTHOR_LAST, AUTHOR_FIRST from BOOK, WROTE, AUTHOR
where WROTE.AUTHOR_NUM = AUTHOR.AUTHOR_NUM and WROTE.BOOK_CODE = BOOK.BOOK_CODE and AUTHOR_FIRST LIKE 'Peter' and AUTHOR_LAST LIKE 'Straub';

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

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