Answered: https://drive.google.com/drive/folders/…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

https://drive.google.com/drive/folders/1Ghu-oV9atvNe3Dgrrm4C901wcdI8xUPD?usp=sharing file needed

Process:
The user (client) requests data from the (server) database. The database sends back the data to the user. At acquisition of the data an XYPlot is drawn.

DataFile:
USAStates2020.csv

User Layer:
The user selects a country, and passes the country name to the Business Layer. Use TKinter to produce a UI for the user to select a country. Send the selected country to the Business Layer.

Business Layer:
Receives the information from the User Layer and constructs a SQL query to send to the Data Layer. The query extracts the yearly data (1970,2020) for the requested country. The data may be queried either country year-by-year or in one query for year range. After receiving the JSON string back from the Data Layer, send the data to the Graphic Layer for plotting.

Data Layer:
Construct a SQL Database based on the data from the DataFile. Processes the queries from the Business Layer. Sends back a JSON string for the requested query.

Graphic Layer:
Create a graphics class to plot the MatPlotLib XYPlot.

Server Layer:
The database access is controlled by the Server Socket. The client sends a query, and the server sends a JSON string.

Client Socket:
Requests data from the server. After receiving the data from the server, the client displays the data.

code :

# Import required libraries
import tkinter as tk
import json
import sqlite3
import matplotlib.pyplot as plt
# Define the database file name and table name
DATABASE_FILE = 'usa_states_2020.db'
TABLE_NAME = 'states_data'
# Define the SQL query to retrieve yearly data for a given country
SQL_QUERY = 'SELECT year, value FROM {table} WHERE country = ? AND year BETWEEN
1970 AND 2020'
# Define the function to create the SQL database from the CSV file
def create_database():
# Read the CSV file
with open('USAStates2020.csv') as file:
lines = file.readlines()
# Extract the header and data rows
header = lines[0].strip().split(',')
data_rows = [line.strip().split(',') for line in lines[1:]]
# Create the database and table
conn = sqlite3.connect(DATABASE_FILE)
cursor = conn.cursor()
cursor.execute(f'CREATE TABLE {TABLE_NAME} ({header[0]} TEXT, {header[1]} INT,
{header[2]} FLOAT)')
# Insert the data rows into the table
for row in data_rows:
cursor.execute(f'INSERT INTO {TABLE_NAME} VALUES (?, ?, ?)', row)
# Commit and close the connection
conn.commit()
conn.close()
# Define the function to retrieve the data for a given country
def get_data_for_country(country):
# Connect to the database
conn = sqlite3.connect(DATABASE_FILE)
cursor = conn.cursor()
# Execute the SQL query with the given country name
cursor.execute(SQL_QUERY.format(table=TABLE_NAME), (country,))
# Fetch all rows and create a dictionary
data = {row[0]: row[1] for row in cursor.fetchall()}
# Close the connection
conn.close()
# Return the data dictionary as a JSON string
return json.dumps(data)
# Define the function to plot the data using Matplotlib
def plot_data(data):
# Convert the JSON string to a dictionary
data_dict = json.loads(data)
# Extract the years and values as lists
years = list(data_dict.keys())
values = list(data_dict.values())
# Create the XY plot
plt.plot(years, values)
plt.xlabel('Year')
plt.ylabel('Value')

plt.title('Data for selected country')
plt.show()
# Define the function to handle the user input and interaction
def select_country():
# Create the UI with a label and a text input
root = tk.Tk()
root.title('Select a country')
tk.Label(root, text='Enter a country name:').pack()
entry = tk.Entry(root)
entry.pack()
# Define the callback function to retrieve the data and plot it
def on_button_click():
country = entry.get()
data = get_data_for_country(country)
plot_data(data)
# Add a button to trigger the callback function
tk.Button(root, text='Plot data', command=on_button_click).pack()
# Start the UI event loop
root.mainloop()
# Define the main function to create the database and start the UI
def main():
create_database()
select_country()
# Call the main function
if __name__ == '__main__':
main()

SyntaxError: unterminated string literal (detected at line 10)

doesnt run

help fix

Expert Solution

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions