Question

create a database, and create tables with the following requirements: 1) The physical data model is about the book tracking system with the following objects: Book Publisher Author Store 2) Relationship Different to last week assignment, the changes in the relationship are: Book and publisher have a many to many relationship Book and author have a many to many relationship 3) Change the physical data model of last to account to the many to many relationship using: Junction tables Foreign keys 4) Write DDL queries for table creation and table alteration 5) Write DML queries for data insertion Add 4-6 records in each table/entity 6) Document all these steps in an APA word document Describe how you refine the physical data model Include the refined physical data model Include the DDL Queries statement Include the DML Queries statement Include the screen shot end results (tables and columns) of tables as it shown in the SSMS

Answer 1

(1.) The name of the database will be Book tracking system,the database consist of tables-- Book, Publisher, Author, Store. in which BookID is the primary key of Book table, similarly PublisherID is the primary key of Publisher table, AuthorID for Author table and StoreID for Store table and BookID is also refered as the foreign key for the Publisher, Author, and Store table.

Where the relational schema of these tables are as follows:- Book (BookID, BookName, Type, Rate) Publisher (BookID, PublisherID, PublisherName, YearofPublish) Author (BookID, AuthorID, AuthorName) Store (BookID, StoreID, StoreName, Address)

Following are the relationship among the different entities of the given database which are following:- 1. Publisher publish the Books 2. Author writes the Book 3. Store sells the Books  

(2) The relationship of the entities are also describe in terms of the cardinality ratio which are following:- a. A Book can be publish by many Publishers and similarly a publisher can publish many Books b. Similarly a book can be written by many Author and a Author can write

(3.) Since the relation Book-Author and Book-Publisher has many to many relation there when we convert the ER- diagram into the equivalent tables then the along with table Author and Book a new table will be created (let say BookAuthorInfo) which will contain the primary key of both the table (that is BookID and AuthorID) this new table is called junction table, similar to Author-Book relation, for Book-Publisher table also a new table will be created (let say BookPublisherInfo) which will contain the primary key of both the table (that is BookID and PublisherID)

(4) DDL command in MYSQL to create table and add new table in exixting database:- CREATE DATABASE BookTrackingSystem;  

CREATE TABLE Book (
BookID int(10) NOT NULL,
BookName varchar(50) ,
Type varchar(20),
Rate int(4),
    PRIMARY KEY (BookID)
);

CREATE TABLE Publisher (
PublisherID int(10) NOT NULL,
PublisherName varchar(50),
BookID int(10), YearofPublish date(dd/mm/yyyy);
    PRIMARY KEY (PublisherID),
    FOREIGN KEY (BookID) REFERENCES Book(BookID)
);

CREATE TABLE Author(
AuthorID int(10) NOT NULL,
AuthorName varchar(50),
BookID int(10),
    PRIMARY KEY (AuthorID),
    FOREIGN KEY (BookID) REFERENCES Book(BookID)
);

CREATE TABLE Store (
StoreID int(10) NOT NULL,
StoreName varchar(50),
BookID int(10), Address varchar(100),
    PRIMARY KEY (StoreID),
    FOREIGN KEY (BookID) REFERENCES Book(BookID)
);

CREATE TABLE BookAuthorInfo (
AuthorID int(10),
BookID int(10),
  FOREIGN KEY (BookID) REFERENCES Book(BookID),
    FOREIGN KEY (AuthorID) REFERENCES Author(AuthorID)
);

CREATE TABLE BookPublisherInfo (
PublisherID int(10),
BookID int(10),
  FOREIGN KEY (BookID) REFERENCES Book(BookID),
    FOREIGN KEY (PublisherID) REFERENCES Publisher(PublisherID)
);

(5.) Sql query to insert data into existing tables:-

INSERT INTO Book VALUES (123,RD sharma, Maths, 500); INSERT INTO Book VALUES (121,RS Agrawal, Maths, 300); INSERT INTO Book VALUES (112,S.Chand, Economics, 250); INSERT INTO Book VALUES (021,Morris Manno, Computer, 550);

INSERT INTO Publisher VALUES (121, 989, Dharma, "12/07/2015"); INSERT INTO Publisher VALUES (123, 956, Dennis, "21/08/2012"); INSERT INTO Publisher VALUES (112, 944, D Publication, "2/07/2011"); INSERT INTO Publisher VALUES (021, 912, jalsa, "3/03/2017");

INSERT INTO Author VALUES (121, 554, Lewis); INSERT INTO Author VALUES (123, 567, J.chand,); INSERT INTO Author VALUES (112, 555, Dhillo);    INSERT INTO Author VALUES (021, 501, Amithabh);

INSERT INTO Store VALUES (123,332, Pustak, 5 jayantipur); INSERT INTO Store VALUES (121,231, Book centre, 31/2 johnpur); INSERT INTO Store VALUES (112,233, Book kendra, 25C strret road); INSERT INTO Store VALUES (021,123, Bookspot, 78/A noman);

INSERT INTO BookAuthorInfo VALUES (123,554); INSERT INTO BookAuthorInfo VALUES (121,501); INSERT INTO BookAuthorInfo VALUES (112,555); INSERT INTO BookAuthorInfo VALUES (021,501);

INSERT INTO BookPublishernfo VALUES (123,944); INSERT INTO BookPublishernfo VALUES (121,956); INSERT INTO BookPublishernfo VALUES (112,944); INSERT INTO BookPublishernfo VALUES (021,912);

These are the sql queries which are according to the given problem statement and hence when we run these queries we will get the 6 different tables along with 4 records