Using Workbench create the following database, EER diagram and SQL script.
The database tracks books and authors. In addition, it classifies books into genres such as fiction or non-fiction.
Business Rules and Guidelines:
1) An author may write one or many books and a book can be written by one or more authors.
2) A book can belong to only one genre (category) and a genre can include many books.
3) Do not worry about optionality – the little circle on top of the crows feet.
4) MySql does not have a money datatype – research what the substitute would be. And is text datatype appropriate for this design? Remember MySqltutorial.com is a good place to research along with StackOverFlow and of course the Mysql manual.
Basic database design rules:
All tables must have an auto increment, non null primary key except junction tables.
All labeling of names should follow the lowercase, underscore naming convention.
All PK and FK columns should be noted.
All foreign key constraints should be named by you not be default. Be consistent.
All relationship lines should be drawn.
All datatypes should be specified and make sense. All tables should use INNODB engine.
DATA
Each table must have at least five rows of data. Use INSERT statements to add data to the tables. DO NOT use edit grid feature in Workbench to add data.
Add data rows to the parent or the One side of a relationship first. You can’t add children records and then add Parent records.
Here is the data:
Category
ID
Code
Name
1
F
Fiction
2
NF
Non – Fiction
3
SF
Science Fiction
4
M
Mystery
5
R
Romance
Book
Id
Title
Description
Price
Category_id
1
Fahrenheit 451
In a far future world, television dominates, and books are outlawed.
$29.95
3
2
The Preacher’s Promise
The Preacher’s Promise finds newly hired school teacher Amanda Stewart going toe to toe with the small Georgia town’s mayor.
$19.95
5
3
The Millionaire Next Door
The seven common qualities that appear over and over among this exclusive demographic.
$18.17
2
4
Twisted Crimes
Full of twists and turns, this crime thriller will keep you turning the pages
$7.99
4
5
For Your Eyes Only
Places him in the dangerous company of adversaries of all varieties.
$9.92
1
6
You Only Live Twice
The tragic end to James Bond’s last mission
$9.93
1
Author
Id
Name
1
Thomas J. Stanley
2
Ray Bradbury
3
Piper Huguley
4
Michael Hambling
5
Ian Fleming
Book_Author
Author_id
Book_id
2
1
1
3
3
2
4
4
5
5
5
6
SQL Queries
Write SQL query(s) to show all records of each table.
Scoring Rubric: Queries 20 pts, Tables 20Pts, Relationships 20 pts, Insert records 20 pts and Attributes 20 pts
