Database Implementation and Formulating Queries


Note: This is an individual assignment. While it is expected that students will discuss their ideas with one another, students need to be aware of their responsibilities in ensuring that they do not deliberately or inadvertently plagiarize the work of others.

Assignment 2 – Database Implementation and Formulating Queries

Due date: 27/05/2018

Assessment Weight: 20%

Rationale

This assignment has been designed to give students experience using SQL and other DBMS facilities to create/alter a relational database and to query the DBMS. This assignment addresses the following two subject learning outcomes (as presented in the Subject Guide):

  1. Apply a database design to a database management system
  2. Formulate queries using a database query language

Task 1 Creating the Database [70 marks]

  1. Use MS Access to create tables identified in the relational data model supplied below. This is sample ERD for a bookshop (named JM Books) database. Primary keys should be correctly specified. All necessary attributes should be specified by setting correct data types and appropriate field lengths.
  1. Insert the data supplied (as MS Excel files) into the database. You may choose to use the SQL INSERT syntax or alternatively the import utility provided by MS Access. (Note: the name or order of attributes in the raw data files may not be exactly matching with those presented in the ERD)
  2. Establish relationships between the tables. The final relationship diagrams should correctly correspond with the JM Books ERD supplied.

Task 2 Creating SQL Queries [40 marks]

Using the database you constructed in Task 1, create the following SQL queries (Q1 ~Q20) in MS Access. The result table of each query should look like the one provided for each question.

  1. List the name of each publisher that’s not located in New York. (Save the query as Q1)
PublisherName
Arkham House
Basic Books
Berkley Publishing
Course Technology
Jeremy P. Tarcher
McPherson and Co.
Taunton Press
Touchstone Books
Westview Press
  1. List the title of each book published by Penguin USA. (Save the query as Q2)
Title
Of Mice and Men
Travels with Charley
East of Eden
The Grapes of Wrath
  1. List the title of each book that has the type SFI and that is in paperback. (Save the query as Q3)
Title
A Deepness in the Sky
  1. List the title of each book that is in the type of CMP, HIS, or SCI. (Save the query as Q4)
Title
The Soul of a New Machine
Band of Brothers
A Guide to SQL
  1. Show how many books are written by Vernor Vintage or Dick Francis? (Save the query as Q5)
Book Count
4
  1. List the title of each book that has the type FIC and that was written by John Steinbeck. (Save the query as Q6)
Title
Of Mice and Men
East of Eden
The Grapes of Wrath
  1. Show how many book copies available at the JM Downtown branch have price that is greater than $10 but less than $20. (Save the query as Q7)
Book Count
11
  1. List the branch name, copy number, quality, and price for each copy of The Stranger. (Save the query as Q8)
BranchNameCopyNumQualityPrice
JM Downtown1Excellent8.00
JM on the Hill1Excellent8.00
JM on the Hill2Fair3.50
JM on the Hill3Poor2.25
  1. For each book title (in an alphabetical order) with more than four copies, show how many copies in total the JM bookshop has and what the average price is. (Save the query as Q9)
TitleCountAverage Price
Dreamcatcher: A Novel6$20.62
Harry Potter and the Prisoner of Azkaban6$12.12
Jazz7$11.78
Second Wind6$23.95
Song of Solomon7$11.86
The Catcher in the Rye5$4.78
The Grapes of Wrath8$8.60
  1. For each book copy available at the “JM on the Hill” branch whose quality is excellent, list the book’s title and author names (in the order listed on the cover). (Save the query as Q10)
TitleAuthorFirstAuthorLast
A Guide to SQLPhilipPratt
Band of BrothersStephen E.Ambrose
Band of BrothersStephen E.Ambrose
BelovedToniMorrison
BelovedToniMorrison
Dreamcatcher: A NovelStephenKing
Dreamcatcher: A NovelStephenKing
Dreamcatcher: A NovelStephenKing
Dreamcatcher: A NovelStephenKing
Franny and ZooeyJ.D.Salinger
Franny and ZooeyJ.D.Salinger
JazzToniMorrison
JazzToniMorrison
JazzToniMorrison
JazzToniMorrison
Magic TerrorPeterStraub
Nine StoriesJ.D.Salinger
Second WindDickFrancis
The Catcher in the RyeJ.D.Salinger
The Catcher in the RyeJ.D.Salinger
The EdgeDickFrancis
The Grapes of WrathJohnSteinbeck
The Soul of a New MachineTracyKidder
The StrangerAlbertCamus
  1. Create a new table named FictionCopies using the data in the BookCode, Title, BranchNum, CopyNum, Quality, and Price columns for those books that have the type FIC. (Save the query as Q11)

The newly created FictionCopies table should look like the table as shown below:

BookCodeTitleBranchNumCopyNumQualityPrice
0200The Stranger11Excellent8.00
0200The Stranger21Excellent8.00
0200The Stranger22Fair3.50
0200The Stranger23Poor2.25
138XBeloved21Excellent12.95
138XBeloved22Excellent12.95
138XBeloved23Good6.95
2766Of Mice and Men31Excellent7.95
2766Of Mice and Men32Good3.95
3743Nine Stories21Excellent5.99
  1. 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 10%. 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 110% of the current price, which is 100% plus a 10% increase.) The increased price column should be null for the copy who’s quality is “Excellent” but price has no change. (Save the query as Q12)
TitleBranchNumCopyNumQualityOldPriceIncreasedPrice
The Stranger11Excellent$8.008.8
The Stranger21Excellent$8.008.8
Beloved21Excellent$12.95
Beloved22Excellent$12.95
Of Mice and Men31Excellent$7.958.745
Nine Stories21Excellent$5.996.589
Catch-2241Excellent$12.00
Jazz21Excellent$12.95
Jazz22Excellent$12.95
  1. List the BookCode and title of all books that are published by Vintage Books or that are available in the JM Brentwood branch or both. (Save the query as Q13)
BookCodeTitle
0200The Stranger
0378Venice
079XSecond Wind
1351Dreamcatcher: A Novel
2226Harry Potter and the Prisoner of Azkaban
2766Of Mice and Men
3906The Soul of a New Machine
6128Jazz
7405East of Eden
7559The Fall
8092Godel, Escher, Bach
9627Song of Solomon
9701The Grapes of Wrath
9882Slay Ride
  1. List the title, publisher name, type, and author names of each book that has two authors. (assuming every book kept by ABC bookshop has maximum two authors) (Save the query as Q14)
titlePublisherNameAuthorLastAuthorFirst
Treasure ChestsTaunton PressO’RourkeRandy
Treasure ChestsTaunton PressSchleiningLon
Van Gogh and GauguinWestview PressCollinsBradley
Van Gogh and GauguinWestview PressCollins, Jr.Bradley
Black HouseRandom HouseStraubPeter
Black HouseRandom HouseKingStephen
  1. List the name of all publishers whose books are not available in any branch of the JM bookshop. (Save the query as Q15)
PublisherName
Arkham House
Arcade Publishing
Jeremy P. Tarcher
McPherson and Co.
Schoken Books
Thames and Hudson
W.W. Norton
  1. List each branch name and the total cost of computers which are currently hired by employees worked at the branch. (Save the query as Q16)
Branch NameTotal Cost of Computers
JM Brentwood$6518
JM Downtown$5807
JM Eastshore$1900
JM on the Hill$6090


  1. List the Comp_Num and the purchase year of computers that were purchased in 2008 or before and have not been hired by Marie Chopping or Ronald Smith. (Save the query as Q17)
Comp_NumYear Purchased
I0022008
I0032007
I0052008
I0062006
I0072007
I0082003
I0122008
I0142003
I0162005
  1. List each branch name and the number of employees of the branch in an ascending order of the number of employees. (Save the query as Q18)
BranchNum of Employees
JM Downtown5
JM Brentwood5
JM on the Hill3
JM Eastshore3
  1. List Book_Code and Book_Title of each book that is possessed by both branches; JM Brentwood and JM on the Hill. (Save the query as Q19)
Book_CodeTitle
079XSecond Wind
1351Dreamcatcher: A Novel
3906The Soul of a New Machine
6128Jazz
9701The Grapes of Wrath
  1. List all publishers that published any book of the branch that has the most books. Note: the branch that has the most books doesn’t mean the branch that has the biggest number of copies but the branch that has the biggest number of different books. (Save the query as Q20)
Publisher_Name
Back Bay Books
Course Technology
Fawcett Books
Jove Publications
Lb Books
Penguin USA
Plume
Putnam Publishing Group
Scribner
Taunton Press
Touchstone Books
Vintage Books

Submission

  1. Submit an MS ACCESS database file to LearnJCU. (The final database file should contain all tables and SQL queries constructed for Task 1 or Task 2)
  2. The timestamp shown on LearnJCU assignment submission will be used to determine if the submission is late or not. Late submissions will subject to penalty.

Marking Criteria


CriteriaExemplaryCompetentMarginalUnacceptable
Task 1:
Creating Database


Attributes (Fields) (For each table)3 All attributes are included and correctly named. AND Data type is defined correctly on all attributes.
2
All attributes are included and most of them are correctly named but data type is defined incorrectly on one or two attributes.
1 Some attributes are missing, not correctly named or data type is defined incorrectly on many attributes.0 Most attributes are missing or mostly named incorrectly or data type is defined incorrectly on the most attributes.
Primary Keys (For each table)
1 An attribute (field) has been selected as a primary key correctly
0.5
Attempted to set a primary key but not correctly.
0 No primary key is set
Data added (For each table)2 Data is imported or entered correctly as required.1
Attempted to enter or import data but not correctly or completely.
0 No data is entered or imported
Relationships (For each relationship)2
All relationships are constructed correctly as required
1
Attempted to construct the relationship but not correctly or completely
0 Not attempted
Task 2:
SQL Queries
For each query2 Produce correct results with correct logic1.5 The produced results are not fully correct but logic was mostly correct (due to simple mistake or missing)1 Produce correct results but with incorrect logic0 Not attempted or the produced results are mostly incorrect


Total Marks


____________ (Out of 110)

Notes:

Learning SQL is an essential part of this subject. Using the Access GUI (QBE facilities using Query Wizard) to produce your SQL queries (for Task 2) is not acceptable. No marks will be awarded where it is deemed that the MS Access query builder has been used to obtain results for Task 2.
Your knowledge of SQL will be also tested in the final exam.
Queries should be written so that they would work with all reasonable sets of test data, not just that which has been supplied as a sample data.
Marks may be deducted if your SQL is excessively complicated.
Full marks will be awarded where the solution provided is correct in all respects.
Partial marks may be allocated where students are deemed to have provided a significant effort toward a correct result, but the solution contains some error.
No marks are awarded where either no solution is provided, or the solution provided is deemed to be mostly incorrect.

The post Database Implementation and Formulating Queries appeared first on My Assignment Online.



This essay is written by:

Prof. SirMojo Verified writer

Finished papers: 435

Proficient in:

English, History, Business and Entrepreneurship, Nursing, Psychology, Management

You can get writing help to write an essay on these topics
100% plagiarism-free

Hire This Writer
© 2017 theacademicessays. All Rights Reserved. Design & Developed by theacademicessays.
How to Avoid Plagiarism
  • Use multiple resourses when assembling your essay
  • Use Plagiarism Checker to double check your essay
  • Get help from professional writers when not sure you can do it yourself
  • Do not copy and paste free to download essays
Get plagiarism free essay
Loading...