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]

1. Create physical database model using the forward engineering process on MySQL Workbench.
2. 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)
3. 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

1. List the title of each book published by LB Books USA.
2. List the title of each book that has the type HOR and that is in paperback.
3. List the title of each book that is in the type of SCI or TRA.
4. 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 -

1. List the title of each book that has the type MYS and that was written by Francis Dick.
2. Title
3. Second Wind The Edge Slay Ride
4. Show how many book copies available at the JM Eastshore branch have price that is greater than $10 but less than $20.
5. List the branch name, copy number, quality, and price for each copy of Second Wind.
6. Price
7. 25.95 25.95 19.95 25.95 25.95 19.95
8. 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