[SOLVED] SQL database HW2-W4111

HW2-W4111
COMSW 4111 Introduction to Databases: Homework 2 Specification
v1.1 Overview
Learning Objectives
HW2 has the following learning objectives:
1. Develop initial experience with database centric web applications, specifically microservices that surface data through a REST API. 2. Understand and apply advanced SQL query capabilities, e.g.
a. Joins.
b. Subqueries.
c. Group By.
d. Views.
e. Accessing relational database metadata.
3. The use of REST APIs to extend the web from simple page viewing to a web of linked, data and APIs, and understanding how to surface data sources and applications via the web.
https://www.programmableweb.com/sites/default/files/growth-in-web-apis-since-2005_0.png
Web Application Architecture
The figure below is a simple architecture view of a web application. HW2 focuses on the REST API component and the Data Access Layer component. This is a database course, and the data layer should be the focus. Developing mobile applications and useful web browser applications is an entire semester course in itself. Developing a microservice Business Logic Layer is also a semester course by itself.
The project template will include a simple web browser application that uses a subset of HW2 APIs. The application allows students to get some insight into how web applications implement the user interface layer.

Project Structure
The figure below is an overview of the structure of the HW2 solution.
Students will develop and extend:
The REST API component. RDBDataTable
Students are recommended to install Postman for testing, but this is optional. There is a simple set of content implementing a simple web UI. Students can test using Lahman 2017 or the Classic Cars tutorial database.
There is a project template for HW2 on GitHub. Students should start by cloning the repository, or downloading and unzipping the project contents. Students will have to add Flask to their project environment. The Flask site provides general instructions. There are online instructions for adding Flask to a PyCharm project.
The figure above provides an overview of the project template and an explanation of the directories and some files. Students will integrate and extend: aeneid.py
RDBDataTable.py: Students will use the RDBDataTable from HW1. They can copy their code into the projects RDBDataTable file or replace the file. dataservice.py (optionally)
Students can add additional files in their implementation.
The file aeneid.py has sample code for processing REST requests.
Deliverables
Your application must implement the following operations/paths:
GET
/api/ / / ?fields=f1, f2, f3 The fields query parameter is optional.
/api/lahman2017/appearances/willite01_BOS_1960?fields=G_all,GS would return the G_all and GS fields for playerID willite01, teamID BOS, yearID 1960 from the Lahman 2017 Appearances table.
/api/ / q=
The query string may contain the following parameters:
children=table1,table2: Specifies that data additional comes from tables table1 and table2 related to table_name by a foreign key relationship.

fields=table1.field1,f2,table2.f3: Would return the following fields: table_name.f2
table1.field1 from child table table1.
table2.field3 from child table table2.
tablen.fieldm = v: The result should only contain data in which the value of field m in tablen is the value m. limit=n: Return at most n elements in the result set.
offset=m: Return data starting at offset m in the result set.
An example is:
/api/lahman/people? children=appearances,batting,nameLast=Williams,batting.yearID=1960,appearances.yearID=1960&fields=playerID,nameLast,nameFirst,batting.AB,batting.H,appearan
Would return the following data:
[ {
}, batting: [
{
AB: 310, H: 98
} ],
appearances: [ {
G_all: 113,
GS: 87 }
] }
]
/api/ / / / ?query_string:
Query string may contain:
fields=field1,f2,f3: Fields from table2.
field1=v1&field2=v2: The result should only contain data in which the value of field1 in table2 is the value v1, etc. limit=n: Return at most n elements in the result set.
offset=m: Return data starting at offset m in the result set.
An example is:
http://127.0.0.1:5000/api/lahman2017/people/willite01/batting?fields=ab,h&yearid=1960
Would return
{
data: [
{
ab: 310, h: 98
} ],
links: [ {
rel: current,
href: http://127.0.0.1:5000/api/lahman2017/people/willite01/batting?fields=ab,h&yearid=1960 }
] }
DELETE and PUT
DELETE and PUT must work on the following paths:
/api/ / / POST
POST must work on the following paths:
/api/ / /api/ / / /table_name
Responses
Responses will have the form:
people: {
playerID: willite01, nameLast: Williams, namefirst: Ted
[
{
data: { }, links: { }
c

}
}
The data section contains the query response data. The links section contains URLs referencing: The current result.
The next page in pagination.
The previous page in pagination.
An example is below. Your application should set a default offset of 10.
{
data: [
{
playerID: {
value: williar01, link: {
rel: people,
href: /api/people/williar01 }
},
nameFirst: Art, nameLast: Williams
}, {
}, {
} ],
links: [ {
rel: current,
href: http://127.0.0.1:5000/api/lahman2017/people?nameLast=Williams&fields=playerID,nameFirst,nameLast&offset=3&limit=3 },
{
rel: next,
href: http://127.0.0.1:5000/api/lahman2017/people?nameLast=Williams&fields=playerID,nameFirst,nameLast&offset=6&limit=3
}, {
href: http://127.0.0.1:5000/api/lahman2017/people?nameLast=Williams&fields=playerID,nameFirst,nameLast&offset=0&limit=3 }
] }
You must test your code!
playerID: {
value: willibe01, link: {
rel: people,
href: /api/people/willibe01 }
},
nameFirst: Bernie, nameLast: Williams
playerID: {
value: willibe02, link: {
rel: people,
href: /api/people/willibe02 }
},
nameFirst: Bernie, nameLast: Williams
rel: previous,
Published by Google Drive Report Abuse Updated automatically every 5 minutes