FileStructure and Database CourseConsider the LIBRARY relational database schema shown in Figure 8.14, which is used to keep track of books, borrowers, and book loans. Referential integrity constraints are shown as directed arcs in Figure 8.14, as in the notation of Figure 5.7. Write down relational expressions also write the queries in SQL statements. for the following queriesFigure 8.18 will be attached a. How many copies of the book titled The Lost Tribe are owned by the library branch whose name is ‘Sharpstown’?c. Retrieve the names of all borrowers who do not have any books checked out.e. For each library branch, retrieve the branch name and the total number of books loaned out from that branch.g. For each book authored (or coauthored) by Stephen King, retrieve the title and the number of copies owned by the library branch whose name is Central. This diagram is an Entity-Relationship (ER) model for a library database system. It demonstrates the relationships between various entities within the system, such as books, authors, publishers, and borrowers. Below is a detailed transcription and explanation of each part:1. **Entities:** - **BOOK:** - Attributes: - `Book_id` - `Title` - `Publisher_name` - **BOOK_AUTHORS:** - Attributes: - `Book_id` - `Author_name` - **PUBLISHER:** - Attributes: - `Name` - `Address` - `Phone` - **BOOK_COPIES:** - Attributes: - `Book_id` - `Branch_id` - `No_of_copies` - **BOOK_LOANS:** - Attributes: - `Book_id` - `Branch_id` - `Card_no` - `Date_out` - `Due_date` - **LIBRARY_BRANCH:** - Attributes: - `Branch_id` - `Branch_name` - `Address` - **BORROWER:** - Attributes: - `Card_no` - `Name` - `Address` - `Phone`2. **Relationships and Connections:** - A `Book` can have multiple authors, as shown by the connection between `BOOK` and `BOOK_AUTHORS` on the `Book_id` attribute. - `BOOK_COPIES` links books to library branches, indicating how many copies of each book are available in different branches. - The `PUBLISHER` entity is connected to `BOOK`, detailing the association between books and their publishers. - `BOOK_LOANS` associates borrowed books with borrowers. It keeps track of which book (identified by `Book_id` and `Branch_id`) is borrowed by which borrower, identified by `Card_no`. - `LIBRARY_BRANCH` provides details of each branch in the library network, linked through `Branch_id`. - `BORROWER` defines individuals who borrow books, linked to book loans via the `Card_no`.This ER model serves as a blueprint for the database structure, allowing efficient management and retrieval of data regarding library operations.

SOLUTION-The CREATE TABLE statement is used to create a new table in a database.The PRIMARY KEY…

Answered: onsider the LIBRARY relational database…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

FileStructure and Database Course

Consider the LIBRARY relational database schema shown in Figure 8.14, which is used to keep track of books, borrowers, and book loans. Referential integrity constraints are shown as directed arcs in Figure 8.14, as in the notation of Figure 5.7. Write down relational expressions also write the queries in SQL statements. for the following queries

Figure 8.18 will be attached

a. How many copies of the book titled The Lost Tribe are owned by the library branch whose name is ‘Sharpstown’?

c. Retrieve the names of all borrowers who do not have any books checked out.

e. For each library branch, retrieve the branch name and the total number of books loaned out from that branch.

g. For each book authored (or coauthored) by Stephen King, retrieve the title and the number of copies owned by the library branch whose name is Central.

This diagram is an Entity-Relationship (ER) model for a library database system. It demonstrates the relationships between various entities within the system, such as books, authors, publishers, and borrowers. Below is a detailed transcription and explanation of each part:

1. **Entities:**
- **BOOK:**
- Attributes:
- `Book_id`
- `Title`
- `Publisher_name`
- **BOOK_AUTHORS:**
- Attributes:
- `Book_id`
- `Author_name`
- **PUBLISHER:**
- Attributes:
- `Name`
- `Address`
- `Phone`
- **BOOK_COPIES:**
- Attributes:
- `Book_id`
- `Branch_id`
- `No_of_copies`
- **BOOK_LOANS:**
- Attributes:
- `Book_id`
- `Branch_id`
- `Card_no`
- `Date_out`
- `Due_date`
- **LIBRARY_BRANCH:**
- Attributes:
- `Branch_id`
- `Branch_name`
- `Address`
- **BORROWER:**
- Attributes:
- `Card_no`
- `Name`
- `Address`
- `Phone`

2. **Relationships and Connections:**
- A `Book` can have multiple authors, as shown by the connection between `BOOK` and `BOOK_AUTHORS` on the `Book_id` attribute.
- `BOOK_COPIES` links books to library branches, indicating how many copies of each book are available in different branches.
- The `PUBLISHER` entity is connected to `BOOK`, detailing the association between books and their publishers.
- `BOOK_LOANS` associates borrowed books with borrowers. It keeps track of which book (identified by `Book_id` and `Branch_id`) is borrowed by which borrower, identified by `Card_no`.
- `LIBRARY_BRANCH` provides details of each branch in the library network, linked through `Branch_id`.
- `BORROWER` defines individuals who borrow books, linked to book loans via the `Card_no`.

This ER model serves as a blueprint for the database structure, allowing efficient management and retrieval of data regarding library operations.

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

Follow-up Questions

Read through expert solutions to related follow-up questions below.

Follow-up Question

can you write them also in relational algrebra

Solution

by Bartleby Expert

SEE SOLUTION

Follow-up Question

Write them in relational expressions/algebra

Solution

by Bartleby Expert

SEE SOLUTION

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions