Project 2 – Database Programming Solved

Description

In this project, you will learn how to write a program that can interact with a database.

Deliverables

1. The source code of your program that can interact with MySQL.

2. The output of your program (text file and screenshot).

STEP 1 – Choose the programming language and install the library

In this project, we recommend you use Python to write your program. Before starting, you need to download the corresponding MySQL library. We provide sample templates for Python below.

You should use Python 3.7.0 to work with MySQL. Install mysql connector here: https://dev.mysql.com/downloads/connector/python/. A tutorial on how to install MySQL connector can be found on blackboard. Please watch carefully and follow the instructions step-by-step.

We also provide template codes for mysql interaction in Python:

Here is the example code, in the example codes, the program establishes the connection to the database.

STEP 2 – Write the program

Use your localhost in MySQL as the server.

Open your MySQL. In the schemas area, right click to create a schema name it anything you prefer (here we use ‘project2’), and then double click on “project2” to make it a default DB to be used.

Remember the database is called “project2” or anything you named it. Use username(root) and password you created to login. Modify the database name, user name and password in the code template.

There are five tables in the database “project2”:

publishers(pubID, pname, email, phone)

subjects(subID,sName)

authors(auID, aName, email, phone)

titles(titleID, title, pubID, subID, pubDate,cover,price)

titleauthors(titleID, auID, importance)

Create tables ‘publishers’, ‘subjects’, ‘authors’, ‘titles’ and ‘titleauthors.’ See below for attribute names and data types.

Add the following data into your tables.

INSERT INTO SUBJECTS VALUES (‘ORA’,’ORACLE DATABASE’);

INSERT INTO SUBJECTS VALUES (‘JAVA’,’JAVA LANGUAGE’); INSERT INTO SUBJECTS VALUES (‘JEE’,’JAVA ENTEPRISE EDITION’); INSERT INTO SUBJECTS VALUES (‘VB’,’VISUAL BASIC.NET’); INSERT INTO SUBJECTS VALUES (‘ASP’,’ASP.NET’);

INSERT INTO PUBLISHERS VALUES (1,’WILLEY’,’WDT@VSNL.NET’,’9112326087′); INSERT INTO PUBLISHERS VALUES (2,’WROX’,’INFO@WROX.COM’,NULL);

INSERT INTO PUBLISHERS VALUES (3,’TATA MCGRAW-HILL’,’FEEDBACK@TATAMCGRAWHILL.COM’,’9133333322′);

INSERT INTO PUBLISHERS VALUES (4,’TECHMEDIA’,’BOOKS@TECHMEDIA.COM’,’9133257660′);

INSERT INTO AUTHORS VALUES (101, ‘HERBERT SCHILD’,’HERBERT@YAHOO.COM’, ‘2137823450’);

INSERT INTO AUTHORS VALUES (102, ‘JAMES GOODWILL’,’GOODWILL@HOTMAIL.COM’, ‘9095871243’);

INSERT INTO AUTHORS VALUES (103, ‘DAVAID HUNTER’,’HUNTER@HOTMAIL.COM’, ‘9094235581’);

INSERT INTO AUTHORS VALUES (104, ‘STEPHEN WALTHER’,’WALTHER@GMAIL.COM’, ‘2138773902’);

INSERT INTO AUTHORS VALUES (105, ‘KEVIN LONEY’,’LONEY@ORACLE.COM’, ‘9493423410’);

INSERT INTO AUTHORS VALUES (106, ‘ED. ROMANS’, ‘ROMANS@THESERVERSIDE.COM’, ‘9495012201’);

INSERT INTO TITLES VALUES (1001,’ASP.NET UNLEASHED’,4,’ASP’,’2002-04-02′,’HARD COVER’,540);

INSERT INTO TITLES VALUES (1002,’ORACLE10G COMP. REF.’,3,’ORA’,’2005-05-01′,’PAPER BACK’,575);

INSERT INTO TITLES VALUES (1003,’MASTERING EJB’,1,’JEE’,’2005-02-03′,’PAPER BACK’,475);

INSERT INTO TITLES VALUES (1004,’JAVA COMP. REF’,3,’JAVA’,’2005-04-03′,’PAPER BACK’,499);

INSERT INTO TITLES VALUES (1005,’PRO. VB.NET’,2,’VB’,’2005-06-15′,HARD COVER,450); INSERT INTO TITLES VALUES (1006,’INTRO. VB.NET’,2,’VB’,’2002-12-02′,’PAPER BACK’,425);

INSERT INTO TITLEAUTHORS VALUES (1001,104,1);

INSERT INTO TITLEAUTHORS VALUES (1002,105,1);

INSERT INTO TITLEAUTHORS VALUES (1003,106,1);

INSERT INTO TITLEAUTHORS VALUES (1004,103,1);

INSERT INTO TITLEAUTHORS VALUES (1005,103,1);

INSERT INTO TITLEAUTHORS VALUES (1005,102,2);

Write a program in python to run the following functions in order:

1. In table “publisher”, there is already some data: A list of publisher IDs, names, emails and phone numbers. Your program should print out all the data in this table. Example output:

Publisher:

1, WILEY, WDT@VSNL.NET, 9092326081 2, WLOX, INFO@WROX.COM

3, JAMESON MCGRAWHILL, FEEDBACK@JAMESONMCGRAWHILL.COM, 9133333322

4, ITMEDIA, BOOKS@ITMEDIA.COM, 2133257660

2. Create a table customer (custID, custName, zip, city, state).

3. Insert 5 customers (‘ABRAHAM SILBERSCHATZ’, ‘ HENRY KORTH ‘, ‘CALVIN HARRIS’, ‘MARTIN GARRIX’ and ‘ JAMES GOODWILL’.) into table “customer” with the custID, custName, zip, city and state. If you want to execute your program multiple times and don’t want to see errors of trying to insert duplicate entries, you may use “INSERT IGNORE INTO” statement, which will do nothing if there is already the same entry in the table.

3. Find the author who has written the most number of books.

3. List all the publishers and the total price of their published titles.

3. Find the names of all authors who have written a book in a subject that has the word “Java” in its name.

3. Find the names of all authors who have written a book with a price between than $475 and $500 and a cover type of “Paper back”.

3. Write a query to retrieve the names of all authors who have written books on the subject ” VISUAL BASIC.NET ” but have not written any books on the subject “

ORACLE DATABASE”.

3. Write a query to retrieve the names of all whose email address contains the domain “gmail.com”.

3. Form a query to decrease the price of all the books published before 2003 by 25% and decrease the price of all the books published after 2004 by 10%.

Save your outputs to a text file using the name “output.txt”, a screenshot of your output, and then archive with your source code (Use the name project2.py) to the file “project2-xxxxxxxxx.zip”, xxxxxxxxx being your student id, and turn it in on Canvas.

Note: Not submitting the file under the correct folder may cause a deduction in your credit.