XYZ Publishing Company produces scientific books on various subjects. They need

Question

XYZ Publishing Company produces scientific books on various subjects. They need a database to maintain information about their authors, editors and books. The author information would include the name of the author, their phone# and email address, and the subject area that they specialize in. Each author only specializes in one subject. Those subjects may include Biology, Physics, Geology, etc. Each author may be working with more than one editor at any given in time. Each author may have written many books. The editor information would include the name of the editor, their phone# and email address, the books that they are editing, and the authors they are working with. Each editor may be working with more than one author at any given time. Each editor may be editing many books at any given time.

The book information would include the title of the book, ISBN number, and the subject area that the book covers. Each book has only one author but may have many editors. Some of the goals that XYZ wants to accomplish with their database include the following information: Names of the authors and the names of the editors. Books that have been published for a particular subject area. Editors that have worked on books for a particular subject.

Explanation / Answer

Table Structure

There are 3 main entities Authors,Books and Editors and the relations like Editor-Book and Editor-Author.

Book_Editor

Steps

1. Create database named XYZPublisher

        MySql query to create db

         CREATE DATABASE IF NOT EXISTS XYZPublisher;

         USE XYZPublisher;

2. Create Tables

Sample query to create table Authors :

CREATE TABLE Authors (

   AuthID int NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    Name Varchar(75),
    Phone Varchar(20),
    Email Varchar(75),

    Subject Varchar(50)

    );

3.Insert few records to the tables.

Use select statements to test/search for records.

Eg:-

Select Name from Authors ;

Select Name from Editors ;

Select ISBN,Title from Books where Subject=’ Physics’;

Select Name from Editors e, Book_Editor eb,Books b where b.Subject=’ Physics’ and b.ISBN=eb.ISBN and e. EID=eb. EID ;

Related Questions

Navigate

• Browse (All)
• Browse X
• Subjects

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