代做Sql|Java代做|代写Postgresql|代做Database – Database System

2019-03-13 写评论

作业Homework|Project代做|Linux代写|代做Assignment|代做Sql|Java代做|代写Postgresql|代做Database: 这是一个涉及java和数据库相关的题目任务

database Systems

homework assignment 5

Recall the relational schema youve designed for an art gallery in assignment 2. A database company called ArtBase builds a product for art galleries. The core of this product is a database with a schema that captures all the information that galleries need to maintain. Galleries keep information about artists, their names (which are unique), birthplaces, age and style of art. For each piece of artwork, the artist, the year it was made, its unique title, its type of art (e.g. painting, lithograph, sculpture, photograph), and its price must be stored. Pieces of artwork are also classified into groups of various kinds, for example, portraits, still lifes, works by Picaso, or works of the 19th century; a given piece may belong to more than one group. Each group is identified by a name (like those just given) that describes the group. Finally, galleries keep information about customers. For each customer, galleries keep that persons unique name, address, total amount of dollars spent in the gallery, and the artists and groups of art that the customer tends to like. The ER diagram for this application is as follows.

A relational schema corresponding to the above ER diagram is given below.

artist(aname:string, birthplace:string, age:int, style:string) artwork(title:string, year:int, type:string, price:real, aname:string) customer(custid:string, cname:string, address:string, amount:real)

agroup(gname:string) classify(title:string, gname:string) likegroup(custid:string, gname:string) likeartist(custid:string, aname:string)

  1. Implement this schema in the Postgre sql instance running in the CS depart- ment.Consider the following points while working on this section. – Its important that both primary key constraints as well as foreign key constraints are enforced. – You can assume that all String type attribute values will contain less than 100 characters. – For non integer numeric fields(priceandamount) use the postgresql data typedouble precisionto inter-operate with Javafloatvalues. More on PostgreSQL data type is available inhttp://www.postgresql.org/docs/9.1/static/datatype.html
  2. Write a client side implementation to access this database with JDBC. The guidelines for this implementation is given below. Its important that these guidelines are strictly followed. – There will be an archive calledassignment5.tarthat you should download which contains the necessary files. Itll extract into a directory named assignment 5. It contains the following directories. – src – the source directory. – lib – the directory for third party libraries. – Makefile – Make file for compiling the source. – scripts – the directory to include the database scripts you will to writing as part of this assignment. Its important that you do not change this directory structure. Grading scripts are written assuming this directory structure. – There will be a set of functionality that you need to implement as described later in this document. Each of this functionality requires you to implement a java method at the client side. Some of these tasks require you to write some procedures at the database as well. There is a class structure provided to be used with this assignment. It contains a stub class and a set of supporting classes. – Stub Class This class resides in the filesrc/cs430/a5/Stub.javaThis class contains empty methods for each of the tasks that needs to be implemented. You should implement the body of the each method as per the instructions provided later in this document. This class has a default constructor which should not be removed due to any reason. Also the signatures of the given methods should not be modified. The grading scripts assumes that the default constructor and the method signatures remain intact. – Supporting Classes Entity sets and relationship sets are modeled as a set of classes. An object of one of these classes represents a record in the corresponding table. These supporting classes are introduced to reduce the complexity of having to pass around several
primitive data types. These classes are available insidesrc/cs430/a5/entitydi-
rectory. There will be seven classes corresponding to the seven tables in the relational
schema. These classes contain attributes representing the fields in the corresponding
table. For instance, the classcs430.a5.entity.Artistcontains four attributes.
name(String),birthPlace(String),age(int) andstyle(String) modeling the four
fieldsaname,birthplace,ageandstylein the artist table respectively.
Do not change these supporting classes.
  • You can implement your own classes while implementing these tasks. But do not change the method signatures of theStubclass or the supporting classes.
  • Compiling the source A make file is provided for compiling the code. The commandmake allwill compile the code. You can use this file as it is. If you introduce packages beyond the depth ofcs430.a5.**, then you will have to modify the line #9 of the file. This file is self explanatory. If you need any help modifying this file, please talk to the TA.
  • Use of third party libraries Its required to use the PostgreSQL JDBC driver as a runtime library. You may need to download it and copy it to thelibdirectory. The make file provided for the building the project will include the content inside thelibdirectory to the classpath.

Following functionality needs to be implemented.

i). Add an artist to the database.
This task should be implemented inside the body of the
public void addArtist(Artist artist) throws SQLException
method of theStubclass. And object of typeArtistwill be passed as an argument.
You may use thegettermethods of this object to access it attribute values.
ii). Add a customer to the database.
This task should be implemented inside the body of the
public void addCustomer(Customer customer) throws SQLException
method of theStubclass.
iii). Add an artwork to the database.
This task should be implemented inside the body of the
public void addArtwork(Artwork artwork, String group) throws SQLException
method of theStubclass. The requirement is different than the previous two tasks. For
this task you should implement astored procedureat the database. This stored
procedure should execute the following three tasks.
  • Add the new artwork to the tableartwork.
  • Check if there is a group in theagrouptable with the given group name. If not add a record with the given group name.
  • Finally add an entry to the tableclassifywith thetitleof the artwork and the group name. At the client side, inside theaddArtworkmethod, you should invoke this stored proce- dure.
When the stored procedure is added to the database, it will persist. But for grading
purposes, save this stored procedure into a file calledq3.sqland include it inside the
scriptsdirectory.

iv). Add records tolikegrouptable. This task should be implemented inside the body of the public void addLikeGroup(String customerId, String likeGroup) throws SQLException method of theStubclass. As part of this task, you need to implement atriggerat the database. This trigger should be set to theINSERToperation of thelikegrouptable. The corresponding procedure should be invoked after inserting a record to the table. This procedure should carry out the following tasks.

  • It queries theclassifytable for the records with the same group name and extracts out the artworks for those records.
  • Then it queries for the artist names of these artworks.
  • Finally it should add records to thelikeartisttable combining the artists name and customer id if that combination is not already recorded. At the client side, implement a regular insert operation. At the database, itll automat- ically invoke the trigger which results in an invocation of the above procedure. Similar to stored procedures, triggers and associated functions will be persisted in the database after they are registered for the first time. For grading purposes, copy the code of the trigger and the associated function into a file namedq4.sqland include inside thescriptsdirectory. v). Implementing update functionality for thestylefield of artist This functionality should be implemented inside the method public void updateArtistStyle(String artistName, String newStyle) throws SQLException of theStubclass. Upon invoking this method, it should update thestylefield of the artist record identified by the given artists name to the new style value.

vi). Reading from the database. There is a set of read operations that should be implemented for certain tables. They should return an array of the corresponding object types for the given table.

  • Get the list of artists. (Method:public Artist[] getArtists() throws SQLException)
  • Get the list of artworks. (Method:public Artwork[] getArtworks() throws SQLException)
  • Get the list of groups. (Method:public Group[] getGroups() throws SQLException)
  • Get the list ofclassifyentries. (Method:public Classify[] getClassifyEntries() throws SQLException)
  • Get the list oflikegroupentries. (Method:public LikeGroup[] getLikeGroupEntries() throws SQLException)
  • Get the list oflikeartistentries. (Method:public LikeArtist[] getLikeArtistEntries() throws SQLException)
Please pay attention to the following points when implementing the above functionality. There
will be points allocated for them.
  • Use parameterized queries. Check the slides on JDBC for more information.
  • Efficiently manage connections. Its not required to use any connection pooling library. Its recommended to use a single connection throughout the program and close it at the end. The test script will be invokingclose()of theStubclass at the end of the program. Link your connection termination logic with this method. A database server can only handle a finite number of connections at a given time. So its really crucial that the connections are properly closed.
  • The code you write to deal with the database will throwSQL Exceptions. The signa- tures of the Stub defines them to be thrown out. So its not required to handle them inside your code. Throwing them out of the method will be helpful to isolate issues while grading your program.
  • If you have any initialization code, place them inside theinit()of theStubclass. It will be invoked immediately after aStubobject is constructed.
Submission Instructions
  • Your final deliverable should include the following.
    • Completed source code.
    • A working make file. The provided make file works out of the box. But if you have deep package structure, you may need to update it.
    • Any third party library used should be placed inside thelibdirectory.
    • Code written at the database(part iii and iv) as separate files inside thescriptsdirec- tory.
    • If you feel that some additional information should be included for making grading easier, feel free to include them in a README file.
  • Make sure that the code runs on the department linux machines.
  • Do not make changes to the tables in your database until the grading is completed from the due date. These code will be tested against your database instances in the department PostgreSQL server.
  • Rename the out most directory(assignment 5 ) tofirstnamelastname. Then create a tar archive by issuing the command;tar -cvf firnamelastname.tar firstnamelastname. If the student name is John Doe, then the directory name will be johndoe and the tar file will be johndoe.tar.
  • Upload the final tar file to RamCT by April 8, midnight.
  • Failing to adhere to conventions and guidelines may result in penalties or delays in grades.

Resources Following resources will be useful while working on this assignment.

  • PostgreSQL Data Types -http://www.postgresql.org/docs/9.1/static/datatype.html

发表评论

电子邮件地址不会被公开。 必填项已用*标注