Posts by tesuji

Hi kutta_vin

this is really a hard task. Finally I have got the following solution:

select transaction, credit, debit, credit+coalesce((select sum(credit) 
  from act b where b.transaction < a.transaction),0) as sumCredit, 
    debit+coalesce((select sum(debit) from act b 
      where b.transaction < a.transaction),0) as sumDebit,  
        sumCredit - sumDebit  as Balance  from act a order by transaction;

/********* result:
transaction credit  debit   sumCredit   sumDebit    Balance
-----------------------------------------------------------
 DB10004    233.00  0.00    233.00      0.00        233.00
 DB10005    0.00    33.00   233.00      33.00       200.00
 DB10006    40.00   0.00    273.00      33.00       240.00
 DB10007    0.00    20.00   273.00      53.00       220.00
*********/

It is important that the rows be uniquely sequenced by Transaction column.

This is a modification of a well-known Celko-solution for running totals I have rewritten to meet your requirements. However, I haven't tested it completely, yet it seems that this solution meets at least your small sample. Tell me whether above solution is right to solve your problem.

krs,
tesu

p.s. Oh sorry, I really hope that MySQL would also be able to deal with subselects as specified in SQL standard 1999!

... I have tried your code but it does have a problem with the word (join)...
HB25

Sorry, I thought Access would have been able to handle joins. The inner joins you need can be replaced by cross products and join conditions in where clause, try this:

select c.CustomerID, c.Surname, c.Address, c.Town, c.Postcode, 
o.OrderID, o.Date, i.ProductID, i.Qty, p.Description 
 from Customer c, Order o, Item i, Product p  
   where c.CustomerID = o.CustomerID
    and  o.OrderID = i.OrderID
      and i.ProductID = p.ProductID
        and WarehouseID='Manchester' AND Date='02/06/2008'

krs,
tesu

Hi HB25,

Your first select should look like:

select c.CustomerID, c.Surname, c.Address, c.Town, c.Postcode, 
o.OrderID, o.Date, 
 i.ProductID, i.Qty, p.Description from Customer c 
   join Order o on c.CustomerID = o.CustomerID
     join Item i on o.OrderID = i.OrderID
       join Product p on i.ProductID = p.ProductID
         WHERE WarehouseID='Manchester' AND Date='02/06/2008'

I didn't test it. It should work on all databases which supply standard SQL 1999. As far as I know, Access also supports inner joins. If not, let me know. In this case the joins must be replaced.

Btw, there should be done some improvement of your data model:
There must be an 1:m relationship between Stock and Product: Stock ->------- Product
Primary key of Item must be ProductID and OrderID (the thing with composite and foreign keys)
What is CustID of Item?
Table name Order should be replaced by another name because ORDER is SQL word ( ORDER BY ...)

krs,
tesu

Welcome kutta_vin,

I really can't understand anything of your code and of that you are asking for. Perhaps you can give a small sample, for example on how the output of your select statement should look like (you can reduce it to the essential facts only).

krs,
tesu

Dear friend

. . .
Task 1: Formulate a query and prepare a report to find order details for a selected warehouse on a specific date, this will help the warehouse gather and pack all goods ready for the courier.

Task2: Formulate a query and prepare a report so that a specific warehouse can check their stock if any quantities of stock fall below 5, stock description should also be listed.
. . .
HB25

One can help you only if you show the corresponding data model your tasks are based on!

krs,
tesu

p.s. your selects seem to be rather imperfect.

Hi yap,
replace cin.get() by these

void waitReturn()
{ char c;
cout << "Return to continue...";
//////////////c=cin.get();
cin.read(&c,1);
if (cin.peek() == 0xA) {;}
}
yap

This works fine for gcc programs.

krs,
tesu

so tesuji can you suggest an algorithm better than FFT for getting large numbers(of the order of 10 power 30 or more)?????????

If I were God first I would create new universe to be able to store quintillion figures of those numbers because Milky Way obviously too small. Then I would install just one quintillion hyper light-speed computers everywhere in that new universe to do multiplication recursively by massive parallel computing. Maybe that will help.

Oh, not that large, only 10 power 30 !
Possibly you need something badly better than fft :icon_mrgreen: (n*ld(n))

hi tanha,

standard SQL only allows one object per GRANT command. Possibly MySql also does not allow enumerative objects. If so, you should try

GRANT SELECT ON mysql.db TO 'user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT ON mysql.user TO 'user'@'localhost' IDENTIFIED BY 'password';

krs,
tesu

This is what I came up with. Still can't get it to run.

. . .
void binaryAdd( int* sum, int&cBit, const int* bin1, const int* bin2 )
{
       cBit = 0;
       int r;

       for ( int i = 0; i < 8; i++ )
       {
               r = bin1[i] + bin2[i] + cBit;
               if ( r % 2 == 0 ) sum[i] = 0;
               else sum[i] = 1;

               if ( r < 2 )
                       cBit = 0;

               else
                       cBit = 1;
       }
}

Possibly, you don't know what you should change in your function to get it function correctly. These are your problems in binaryAdd of posting #7:

1. Serious problem (where you begin when you are doing addition manually, left or right side?):
for ( int i = 0; i < 8; i++ ) --> for ( int i = 7; i >=0; i-- ) // see posting #9

2. better, for allowing continuous additions:
cBit = 0 --> should be set in main()

3. more beautiful:
if ( r % 2 == 0 ) sum = 0;
else sum = 1; --> sum = r % 2;

krs,
tesu

The c++ function void binaryAdd(int* sum, int& cBit, const int* bin1, const int* bin2 ) cannot be altered. I would be thankful for any help solving this problem.

I am not that sure whether your question deals with my post #9, does it? Are there problems like how-to-change formal parameters of the function declaration or don't fit your actual parameters when calling this function? Are there any error messages?

...If all redundant attributes are removed you get a subset what is called primary key...

I have to make an extension:
Sometimes there are more than one superkey of a relation. In such case also all redundant attributes have to be removed and the remaining subsets of attributes are then called CANDIDATE KEYS. Now one can freely select ONE of these candidate keys to identify the relation's tuples. In such case, this selected subset is called PRIMARY KEY.

krs,
tesu

Sorry, corretion:

Hi greeny,
... Our categories table (it slightly differs from below simple example) works great, and the table now has over 4000 categories and subcategories, still increasing...
tesu

We don't have 4000, sorry, only about 400 categories!

Need a little help for drawing the sample tree: it consists of one main category, three sub categories, two sub sub categories, and one sub sub sub category.

krs,
tesu

Hi greeny,

There is no doubt that your doubt tells me you didn't understood anything of my sample code
neither you have drawn the tree of the example categories tree. If you don't understand this code, especially the primary, foreign keys and the insert statements, you may freely ask me.

First, I would advice you that you try to draw the categories tree from the given insert statements. Then compare it with your green bean example. What will you assess then? Yes, this very plain categories tree is already much more complex than your green bean example.

krs,
tesu

Hi buddy1,

here is some code what might meet your assignment.

In your main() you write:

// Binary addition
const int a[8] = { 0, 1, 1, 1, 1, 1, 1, 1 };
const int b[8] = { 0, 0, 1, 1, 1, 1, 1, 1 };
int nb = 8, carry = 0, sum[8];

binaryAddint(nb, carry, sum, a, b);

cout << "\nOperand a:   "; for (int i = 0; i < nb; cout << a[i++]);
cout << "\nOperand b:   "; for (int i = 0; i < nb; cout << b[i++]);
cout << "\na plus b:    "; for (int i = 0; i < nb; cout << sum[i++]);
cout << "\nLast carray: " << carry << endl;

/* Result:
   Operand a:   01111111
   Operand b:   00111111
   a plus b:    10111110
   Last carray: 0
*/

Function to do the additions:

void binaryAddint( int nb, int& carry, int* sum, const int* bin1, const int* bin2 ){
  for ( int s, i = nb-1; i >= 0; i--){
    s = bin1[i] + bin2[i] + carry;
    sum[i] = s % 2; carry = s / 2;
  }
}

First, both binary figures and the carry will be added. That gives the sum s. sum s remainder 2 gives the new figure (e.g. 1 + 1 + 1 = 3 % 2 = 1). There is only a new carry if the sum is 2 or 3. So dividing the sum s by 2 gives carry (e.g. 0 + 1 + 1 = 2 / 2 = 1).

I hope this clarifies the problem.

krs,
tesu

Hi greeny,

a way to solve this completely independently from the number of categories and subcategories are (recursive) trees in SQL. There is a famous book: Transact-SQL Cookbook by Ales Spetic, Jonathan Gennick what has an extra chapter only dealing with that topic. They also explain how to traverse trees recursively by means of transact SQL.

Below table I once designed for a similar problem you are facing with is based on an example from this book. Our categories table (it slightly differs from below simple example) works great, and the table now has over 4000 categories and subcategories, still increasing.

create table categories (    -- father of main knot is NULL
ucat integer null,    -- a father knot of a tree, it has many son knots
lcat integer not null,     -- the son knot. has only one father knot
name varchar (30), 
primary key (lcat),
foreign key (ucat) references categories);   -- recursive definition

-- Some samples
insert into categories values (null,1,'Categories');
insert into categories values (1,2,'Software');
insert into categories values (1,3,'Hardware');
insert into categories values (1,4,'Network');
insert into categories values (2,5,'Linux');
insert into categories values (3,6,'PC');
insert into categories values (3,7,'Printer');
insert into categories values (6,8,'LCD');
commit

-- for example select all subcategories of category hardware
select * from categories
where ucat = all (select lcat from categories
where name = 'Hardware');

/*  Result
ucat   lcat   name
------------------
3      6      PC
3      7      Printer
*/

You may draw the tree to see how it works.
…

hi mertucci,

are in doing data modelling?
Superkey is any subset of attributes that uniquely identifies the tuples of a relation. This subset need not be minimal, that is, one can remove some attributes from it and it is still uniquely identifying. If all redundant attributes are removed you get a subset what is called primary key. A key (better: primary key) is the minimal subset of attributes that uniquely identifies the tuples of a relation, that is, you can't remove further attributes from this subset without losing the property of unique Identification. Therefore, the super superkey (like superman) is always the set of all attributes of a relation.

krs,
tesu

Hi, is there a data type larger than unsigned long long int except double? I don't want double or float because I need to keep it as precise as possible. I also need it to be able to do modulus. How would I do this?

GMP is uniquely great. Yet, how large is very large integer?

hi shknahs,
you may also try this:

void rev_str(string str,int n)
{
	if(str[n]!='\0')
		rev_str(str,n+1);
	cout<<str[n-1];
}
// result: shknahs

krs,
tesu

hi,
static member must be initialized outside of class declaration, for example below }; of class def:

class C
{
   public:
      static int st;
   ...
};

int C::st = 2008; // must be outside class declaration

krs,
tesu

hi,

"17" ascii ?
"1"  = 41hex   (4th column, 1st row)  = 0100  0001bin
"7"  = 47hex   (4th column, 7th row)  = 0100  0111bin

17   
76543210
00010001  = 16 + 1  = 2^4  + 2^0

-17
00010001
11101110   (invert all digits --> this is one s complement)
+      1       (add 1 to get two s compl.)
-----------
11101111 


0100  1011  1111    (form groups of 4 digits, starting at right)
4     B     F

3       F               
0011    1111

Maybe it s that what you are looking for :)

krs,
tesu

hi recursiveNugget,

bad news! I have checked your join operations on those 6 tables, and the (wrong) results are:

1. contact.id
id 2 will be counted 20 times
id 5 will be counted 16 times
id 8 will be counted 4 times

2. Indeed, you need LEFT OUTER JOIN. With inner join (which would be the appropriate one
for such tasks, but...) you would lose all information about contact.id 2 and 8.

3. However, the result is wrong because you join four tables (tdocumentsA..D) independently
together. The information you have spread over these 4 table MUST be arranged in ONE
table only. Then applying of INNER JOIN on tusers INNER JOIN tContacts INNER JOIN
tdocuments would work properly.

Long story short, you need to redesign your tables.

krs,
tesu

Btw, below is the result of the last join, where you can check out why inner join would produce poorer results (all rows containing (NULL) would then disappear)

#     DocA DocB   DocC    DocD
1  1  2  C2  A1  B3     (NULL)  D1   
1  1  2  C2  A1  B4     (NULL)  D1
1  1  2  C2  A1  B6     (NULL)  D1
1  1  2  C2  A1  B7     (NULL)  D1
1  1  2  C2  A1  B8     (NULL)  D1

1  1  2  C2  A1  B3     (NULL)  D3   
1  1  2  C2  A1  B4     (NULL)  D3
1  1  2  C2  A1  B6     (NULL)  D3
1  1 …

Hi,

Left outer join should be replaced by inner join, as already stated. Can you post the create- table statements? I have got the feeling that something could be wrong with the relationships of your tables. As for example, table tUser contains foreign key contact_id what comes from tContacts. If tUser.id is the primary key then one certain user can only have ONE contact. Maybe that s correct? If a user should have more then one contact, the tUser.id must be foreign key of tContacts. Furthermore, primary key of tContacts, obviously tContacts.id, should then be non-identifying foreign key of the tdocumentsA...D instead of user_id.

As for your four document-tables, it would be a good idea to think of better normalization: What if you get further categories, say tdocumentsE ... ? So all documents should be stored in ONE table. This approach leads to simpler SQL statements, too.

krs,
tesu

Hi friend,

I am viewing your data dictionary and erm. Well, something seems to be wrong: some trouble with keys, composite keys, also the graphic of the erm emerges in a disordered manner. The relationships between orders and items and items and products, respectively should be redrawn. Maybe the disordered graphical elements are an result of my open office doc viewer (I don't use MS but Linux). I also suggest to post pdf files only (you may use freepdf to convert ms doc into pdf).

Btw, when is deadline, when should you hand in your results?

Ok, let's start to emend your design. There is a general rule to get the right foreign keys (and often the (composite) primary key): In an one-to-many relationship, the primary key of the entity from the one-side is moved into the entity what is located on the many-side, e.g. Between orders and items exists a one-to-many relationship, that is, orders is on the one-side and items on the many-side. Therefore, the primary key of orders must move into items, and there it becomes (at first) a foreign key.
Between products and items also exists an one-to-many relationship. Again, the primary key of products has to be moved into items. There it becomes a foreign key. Now that items has got two new attributes, finally we have to determine the primary key of items.

Do the move-in attributes order_ID and product_ID uniquely identify items ( you should keep in mind …

ofstream is for output, which can be combined with fstream::out | fstream::app // for append
but not with fstream::in | fstream::out

if you want to do both, use fstream.
you may have a look at http://www.daniweb.com/forums/thread6542.html

krs, cliff

hi

i ve just checked your erd und dd:

1. items must have primary key, e.g. foreign key order_id + item_nb (1,2,3..)
item_nb is necessary, if a certain order contains more then 1 item.

2. relationship between suppliers and products should be many-to-many, that is a certain product could be delivered by many suppliers and a certain suppliers could deliver many products.

3. data type of qty should be integer.

4. I would drop entity warehouse, as already stated.

further on, your erd seems to be ok, especially all crowfeet are placed correctly.

brs, cliff

Dear tesuji

Thank you for your reply to my post.

Please find attached my ERD and Data Dictionary I would be very grateful if you could correct any Errors I have made especially with Primary, Foreign and composed key.

Thanks for your help

BTW thanks for pointing out the website.

HB25

hi,
if you design an entity relationship model (ERM) there are only entities and relationships. There aren't any "tables" there. Once you might map your ERM into relational model, which consists of tables (relations) only.

Your shown relationship ' (ONE) Warehouse-> could have many -> orders' expresses the trivial fact that a warehouse has many orders. It is already clear that those orders belong to a sole warehouse because your given task is headlined by "Design a ERM datamodel for a warehouse". Thus, we should omit the entity warehouse.

Your warehouse datamodel may have these entities: products, customers, orders, orderlines, suppliers, stock (to record products on stock)

where the following relationships can be found:

customers ------ one-to-many ----<- orders

orders ----- one-to-many -----<- orderlines

products ----- one-to-many ----<- orderlines

products ->---- many-to-many ----<- suppliers

products ----- one-to-many ----<- Stock

If you are to design the relationship between suppliers and products more detailed in an analogous way to customers/orders, you have to adopt similar entities such us purchase and purchase_orderline.

You also may have a look at http://www.databaseanswers.org/data_models/index.htm.

brs, cliff