Question: This assignment has been designed to give students experience using Structured Query Language (SQL) and other database management systems (DBMS) facilities to create/alter a relational
This assignment has been designed to give students experience using Structured Query Language (SQL) and other database management systems (DBMS) facilities to create/alter a relational database and to query the DBMS. This assignment addresses the following learning objectives for this subject:
- Develop and implement a database model using the E-R model and facilities provided by a DBMS
- Formulate queries using a database query language This assignment consists of two main tasks:
1. Creating the database (by following three subtasks):
o Create relational database for a given conceptual model (ERD) using MySQL
Workbench
o Create physical database model on MySQL Workbench by applying the
forward engineer process
o Import raw data from the external file to a table using the MySQL Workbench
facility or using SQL queries
2. Writing SQL queries for given problems.
Further details about each task are presented in the following pages.
Submission
- An MySQL Workbench file containing the ERD you created (.mwb)
- A database dump file built and exported on MySQL Workbench (.sql)
- A WORD or text file containing all SQL query codes and result tables (.doc, .docx, or .txt)
- Page 1 -
Task 1: Creating the database
1. Use MySQL Workbench to create relational database model as presented in the following ERD. This is a simplified database model designed for a bookshop company named 'JM Books'.
Copy
PK PK,FK2 PK,FK1
Copy_Num Branch_Num Book_Code
Copy_Quality Copy_Price
Author
PK
Author_Num
Author_Last Author_First
PK
Publisher
Publisher_Code
Publisher_Name Publisher_City
has
JM Books
published
has
has
has has
Wrote
PK,FK1 PK,FK2
Author_Num Book_Code
Wrote_Sequence
Book
PK
Book_Code
FK1
Book_Title Book_Type Book_Paperback Publisher_Code
Branch
PK
Branch_Num
Branch_Name Branch_Location
Computer
PK
Comp_Num
Comp_PurchaseDate Comp_Cost Comp_Description
JM Books runs multiple branches and has employees working for each branch. This database was designed for keeping relevant records of books, authors and publishers. It also stores relevant information about copies of a book which are kept by each branch. Employees working for JM Books can use a rental service to hire personal computers which should be returned back when they leave this company.
PKs should be correctly specified. All necessary attributes should be specified by setting appropriate data types and appropriate field lengths. [Save the completed model as a .mwb file]
- Create physical database model using the forward engineering process on MySQL Workbench.
- Insert the data supplied into the database. You may choose to use either the SQL INSERT syntax or the import facility provided by MySQL Workbench. (Note: the names or order of attributes in the data files may not exactly match those shown in the ERD)
- Finally, dump the database into one integrated file on MySQL Workbench. [Save the file as .sql file]
- Page 2 -
has
employed
Hire
PK,FK1 PK
Comp_Num Hire_Start
FK2
Hire_End
Emp_Num
Employee
PK
Emp_Num
FK1
Emp_Last Emp_First Branch_Num
Task 2: Creating queries [40 marks]
Using the database you constructed in Task 1, create the following SQL queries in MySQL Workbench. The resulting table for each query should look like the one provided for each question if you created the database correctly as required. [Paste your SQL query into a Word document to submit]
1. List the name of each publisher that's not located in Boston. (Name the query as Q1)
PublisherName
Arkham House
Arcade Publishing
Basic Books
Back Bay Books
Fawcett Books
Farrar Straus & Giroux
HarperCollins Publishers
Jove Publications
Jeremy P. Tarcher
- List the title of each book published by LB Books USA.
- List the title of each book that has the type HOR and that is in paperback.
- List the title of each book that is in the type of SCI or TRA.
- Show how many books are written by Solotaroff Paul or King Stephen?
Title
Nine Stories
Franny and Zooey
The Catcher in the Rye
Title
Magic Terror
Title
Travels with Charley
The Soul of a New Machine
Book Count
3
- Page 3 -
- List the title of each book that has the type MYS and that was written by Francis Dick.
- Title
- Second Wind The Edge Slay Ride
- Show how many book copies available at the JM Eastshore branch have price that is greater than $10 but less than $20.
- List the branch name, copy number, quality, and price for each copy of Second Wind.
- Price
- 25.95 25.95 19.95 25.95 25.95 19.95
- For each book code with more than 10 copies, show how many copies in total the JM bookshop has and what the average price is.
10.For each book copy available at the "JM Downtown" branch whose quality is good, list the book's title and author names.
11.Create new table named FictionSelection using the data in the BookCode, Title, BranchNum, Quality, and Price columns for those books that have the type FIC. The newly created FictionSelection table should look like the table as shown below:
Book Count
6
BranchName
CopyNum
Quality
JM on the Hill
1
Excellent
JM Brentwood
1
Excellent
JM Brentwood
2
Good
JM Eastshore
1
Excellent
JM Eastshore
2
Excellent
JM Eastshore
3
Good
Book Code
Total Copies
Average Price
1351
13
$20.62
6128
16
$11.78
9627
18
$11.86
9701
13
$8.60
Title
AuthorLast
AuthorFirst
Harry Potter and the Prisoner of Azkaban
King
Stephen
Electric Light
Heaney
Seamus
When Rabbit Howls
Chase
Truddi
Black House
Straub
Peter
- Page 4 -
CP2404 / CP5633 Database Modelling - Assignment #2
BookCode
Title
BranchNum
Quality
0200
The Stranger
1
Excellent
0200
The Stranger
2
Excellent
0200
The Stranger
2
Fair
0200
The Stranger
2
Poor
138X
Beloved
2
Excellent
138X
Beloved
2
Excellent
138X
Beloved
2
Good
2766
Of Mice and Men
3
Excellent
2766
Of Mice and Men
3
Good
3743
Nine Stories
2
Excellent
Price
8.00 8.00 3.50 2.25
12.95 12.95 6.95 7.95 3.95 5.99
12.JM Bookshop is considering increasing the price of all copies of fiction books whose current price is under $10.00 and quality is Excellent by 20%. To determine the new prices for the copy, list the book title, branch number, copy number and increased price of every book (copy) with "Excellent" quality in the FictionCopies table as well as the copy's original price. (Your computed column should determine 120% of the current price, which is 100% plus a 20% increase.) The increased price column should be null for the copy who's quality is "Excellent" but price has no change.
Title
BranchNum
Quality
OldPrice
IncreasedPrice
The Stranger
1
Excellent
$8.00
9.6
The Stranger
2
Excellent
$8.00
9.6
Beloved
2
Excellent
$12.95
Beloved
2
Excellent
$12.95
Of Mice and Men
3
Excellent
$7.95
9.54
Nine Stories
2
Excellent
$5.99
7.188
Catch-22
4
Excellent
$12.00
Jazz
2
Excellent
$12.95
Jazz
2
Excellent
$12.95
....
13. List the BookCode and title of all books that are published by Vintage Books or that are available in the JM Eastshore branch or both.
BookCode
Book Title
079X
Second Wind
2281
Van Gogh and Gauguin
5790
Catch-22
9627
Song of Solomon
9701
The Grapes of Wrath
9883
The Catcher in the Rye
2226
Harry Potter and the Prisoner of Azkaban
2908
Electric Light
7443
Harry Potter and the Goblet of Fire
- Page 5 -
CP2404 / CP5633 Database Modelling - Assignment #2
BookCode
Book Title
0200
The Stranger
7559
The Fall
14. List the title, publisher name, and author names of each book that has two authors.
title
PublisherName
AuthorLast
AuthorFirst
Treasure Chests
Taunton Press
O'Rourke
Randy
Treasure Chests
Taunton Press
Schleining
Lon
Van Gogh and Gauguin
Westview Press
Collins
Bradley
Van Gogh and Gauguin
Westview Press
Collins, Jr.
Bradley
Black House
Random House
Straub
Peter
Black House
Random House
King
Stephen
15. List the code and name of all publishers whose books are not available in any branch of the JM bookshop.
PublisherCode
PublisherName
AH
Arkham House
AP
Arcade Publishing
JT
Jeremy P. Tarcher
MP
McPherson and Co.
SB
Schoken Books
TH
Thames and Hudson
WN
W.W. Norton
Branch Name
Total Cost of Computers
JM Brentwood
$6518
JM Downtown
$5807
16.List the total cost of computers which are currently hired by employees worked at the JM Brentwood and JM Downtown branch.
17.List the Comp_Num and the purchase year of computers that were purchased in 2008 and have not been hired by Marie Chopping or Ronald Smith.
18.List each branch name and the number of employees of the branch in a descending order of the number of employees.
Comp_Num
Year Purchased
I002
2008
I005
2008
I012
2008
Branch
Num of Employees
JM Downtown
5
JM Brentwood
5
JM on the Hill
3
JM Eastshore
3
19.List Book_Code and Book_Title of each book that is possessed by both branches; JM Downtown and JM on the Hill.
20. List all publishers that published any book of the branch that has the least books. Note: the branch that has the least books does not mean the branch that has the smallest number of copies but the branch that has the lowest number of different books.
BookCode
Book Title
0200
The Stranger
669X
A Guide to SQL
9701
The Grapes of Wrath
Publisher
Publisher Name
WP
Westview Press
SC
Scribner
PU
Putnam Publishing Group
FS
Farrar Straus & Giroux
ST
Scholastic Trade
PL
Plume
LB
Lb Books
PE
Penguin USA
Step by Step Solution
There are 3 Steps involved in it
1 Publishers not located in Boston SELECT PublisherName FROM Publisher WHERE PublisherCity Boston 2 Titles of books published by LB Books USA SELECT BookTitle FROM Book JOIN Publisher ON BookPublisher... View full answer
Get step-by-step solutions from verified subject matter experts
Study Help