Description
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.
http://www.vertabelo.com/blog/technical-articles/how-to-store-authentication-data-in-a-database-part-2-email-confirmation-and-recovering-passwords
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.
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
