COMSW 4111代做、Databases留学生代写、代做SQL代写、SQL程序语言调试

日期：2019-05-03 10:28

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 project’s 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/<dbname>/<table_name>/<primary_key_value>?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/<dbname>/<table_name>q=<some_query_string>

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,appearances

Would return the following data:

[

{

"people": {

"playerID": "willite01",

"nameLast": "Williams",

"namefirst": "Ted"

},

"batting": [

{

"AB": "310",

"H": "98"

}

],

"appearances": [

{

"G_all": "113",

"GS": "87"

}

]

}

]

/api/<dbname>/<table_name>/<primary_key>/<table2_name>?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/<dbname>/<table_name>/<primary_key>

POST

POST must work on the following paths:

● /api/<dbname>/<table_name>

● /api/<dbname>/<table_name>/<primary_key>/table_name

Responses

Responses will have the form:

[

{

data: { ... },

links: { … }

}

… …

}

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"

},

{

"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"

}

],

"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"

},

{

"rel": "previous",

"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!

Published by Google Drive – Report Abuse – Updated automatically every 5 minutes