Em Andamento

Inventory tracking database - PostgreSQL Queries

This is a part of a program that handles library books. Each book has an integer code. Books are signed out and returned by clients on a daily basis. There are two tables. Each time a book is signed out or returned, an invoice (or ticket) is made and entered into the database. The ticket includes a date stamp, the book code for each book, client name, comments. One single ticket may carry details of up to 50 books at a time. Tracking table: Each entry in the tracking table corresponds to transaction of one single book. CREATE TABLE tracking ( ID integer PRIMARY KEY, book integer NOT NULL, Signout date, Signout_inv character varying(10), Signin date, Signin_inv character varying(10) ) Invoices table: Each entry corresponds to a single invoice. CREATE TABLE invoices ( invoice character varying(10) NOT NULL, client character varying(40) , comments character varying(150) ) example. If two books are sold, two transaction entries are made, e.g. INTO TRACKING {ID: 31928,Book: 1019, Signout: 22/12/2010, Signout_inv: LA11201, Signin: (null), Signin_inv: (null)} {ID: 31929,Book: 991, Signout: 22/12/2010, Signout_inv: LA11201, Signin: (null), Signin_inv: (null)} AND into INVOICES {invoice:'LA11201',client:'Dave',comments:'Some comment'} When Book 1019 is returned, ID 31928 is updated INTO TRACKING {ID: 31928,Book: 1019, Signout: 22/12/2010, Signout_inv: 'LA11201', Signin: 15/01/2011, Signin_inv: 'RET10912'} AND INTO INVOICES {invoice:'RET10912',client:'Mike',comments:'Client may be unknown'} All books that have their last (chronologically) signin_inv transactions as 'non-null' can be assumed to be on the shelf. All books that have their last signin_inv transactions as null are automatically outside with client (referred by signout_inv) I shall include a Postgre SQL dump backup file for testing purposes. Schema/design changes for optimization may be made by mutual agreement.

## Deliverables

1) All deliverables will be considered "work made for hire" under U.S. Copyright law. Employer will receive exclusive and complete copyrights to all work purchased. (No 3rd party components unless all copyright ramifications are explained AND AGREED TO by the employer on the site per the worker's Worker Legal Agreement).

2) As efficiently as possible, the following SQL queries to: a. View all invoices in format [invoice, client, date, total# of books, comments] i. Allow to specify date range for invoices ii. Allow to specify client / group of clients iii. Ignore all dummy entries. b. View total books on shelf [totalNum] c. View all books on shelf in format [bookNo] i. Allow to specify single date (which should give books on shelf on that date) d. View books outside in format [ book, date, invoice, comments, client] i. Allow to specify client / groups of clients ii. Allow final sort by date range ( to remove books that have been out before x date and after y date) e. View book transaction history in format [invoice, client, date, comments] 3) If any mutually agreed changes are made to the schema or database design, an upgrade script to move from current database to new one. A subquery to generate tables in the old format will also be required for backward compatibility.

## Platform

PostgreSQL 8.4 ONLY.

Habilidades: Administração de Bancos de Dados, MySQL, SQL

Ver mais: y should i hire u, schema design, example of inventory database, design dump, database hire, c library sort, backward design, postgresql any, mysql postgresql, transaction history, tracking s, time-tracking, ticket id, sql queries, signout, queries, postgresql c++, postgre 2 mysql, mike d, inventory program, inventory database, database upgrade, database queries, database optimization, database design sql

Acerca do Empregador:
( 1 comentário ) Canada

ID do Projeto: #2996467

Premiar a:

rylkov

See private message.

$17 USD em 14 dias
(42 Avaliações)
5.0

5 freelancers estão ofertando em média $62 para este trabalho

ExpertSolver

See private message.

$51 USD in 14 dias
(16 Comentários)
6.0
khalidamin

See private message.

$74.8 USD in 14 dias
(36 Comentários)
5.0
softproz

See private message.

$68 USD in 14 dias
(6 Comentários)
3.1
trishulpani

See private message.

$72.25 USD in 14 dias
(0 Comentários)
0.0
AGNEWTON1

See private message.

$76.5 USD in 14 dias
(3 Comentários)
0.0