A book is written by one or multiple authors. Each author is identified by an au
ID: 3827231 • Letter: A
Question
A book is written by one or multiple authors. Each author is identified by an author number and has a name. Each author has either one or multiple books; in addition, occasionally data are needed regarding prospective authors who have not yet published any books. It is also important to record the percentage of the royalties that belongs to a specific author. Below is an ERD for those business rules.
Map ERD to RSD
Create all relevant tables with the following constraints:
Author Number < 10000 and Author Number > 0
Royalty Pct <= 15%
Show all table definitions
Enter sample data for 2 books and for 2 authors
A book is written by one or multiple authors. Each author is identified by an author number and has a name. Each author has either one or multiple books; in addition, occasionally data are needed regarding prospective authors who have not yet published any books. It is also important to record the percentage of the royalties that belongs to a specific author. Below is an ERD for those business rules.
Map ERD to RSD
Create all relevant tables with the following constraints:
Author Number < 10000 and Author Number > 0
Royalty Pct <= 15%
Show all table definitions
Enter sample data for 2 books and for 2 authors
Explanation / Answer
The Relational schema for the following ERD :
BOOK(ISBN,Book Title)
AUTHOR(Author Number,Name)
The cardinality for BOOK is 0 to many, and for AUTHOR is 1 or many.
Creating Relevant tables with constraints
CREATE TABLE AUTHOR(
Author_Number int,
Name varchar(50),
CONSTRAINT CHK_Author CHECK (Author_Number > 0 AND Author_Number < 10000)
);
CREATE TABLE BOOK(
ISBN int NOT NULL,
Book_Title varchar(100)
);
CREATE TABLE ROYALTY_PACT(
Royalty_pact float(10,2),
CONSTRAINT CHK_pact CHECK (Royalty_pact <= 0.15
);
The tables are created.
Now let's enter data into these tables.
INSERT INTO BOOK VALUES (8000,'50 shades of grey');
INSERT INTO BOOK VALUES (5000,'Kamasutra');
INSERT INTO AUTHOR VALUES(12,'Henry Cabbagehead');
INSERT INTO AUTHOR VALUES(19,'Diddy dodat');
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.