RELATIONALIZE. PLEEEEAAASE!

I just found this table:
CREATE TABLE (
TipCode CHAR(3) NOT NULL,
TipGroup CHAR(1) , -- WTF?!?
FatigueDispo CHAR(1),
HWDesc NVARCHAR(100),
SWDesc NVARCHAR(100),
PRTDesc NVARCHAR(100),
STDesc NVARCHAR(100)
)
/*no primary key, no indexes */
Data looks like this:
TipCode TipGroup ...
010 1
020 2
030 ?
040 ?
Instead of making the TipGroup column not-null, and refusing to use empty
strings, they have represented the unknown values with this: '?'
[bonking head on desk]
Celko, please come here and beat my colleagues to death. You get overtime
pay if you beat the bosses too. I might even help.
"Here let's use a relational system to do nonsensical things like store '?'
which means 'unknown'" But NULL? NOOOOOO!!!! That would be too easy!
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneSr. developer:
"NULL? We can't use null... not in a char(1) column... there's no room! NULL
is like four characters long... futher more it might get truncated to 'N' an
d
that's already reserved... and even if it doesn't - who knows what NULL
menas... better make it a '?'... it's a question mark... everybody knows wha
t
*that* means..."
Jr. developer:
"Yeah, I think it's brilliant, sir. And I'd like to say that *you're*
brilliant, too, sir!"
Mike:
#$&% *!|||What do you mean you can't use NULL in char(1) column? Have you tried it?
=0}
Let's just say for grins, you try it. What happens?
"ML" <ML@.discussions.microsoft.com> wrote in message
news:BAD57E39-F45F-4547-8540-DA8943EC2B1A@.microsoft.com...
> Sr. developer:
> "NULL? We can't use null... not in a char(1) column... there's no room!
> NULL
> is like four characters long... futher more it might get truncated to 'N'
> and
> that's already reserved... and even if it doesn't - who knows what NULL
> menas... better make it a '?'... it's a question mark... everybody knows
> what
> *that* means..."
> Jr. developer:
> "Yeah, I think it's brilliant, sir. And I'd like to say that *you're*
> brilliant, too, sir!"
> Mike:
> #$&% *!|||Have you?
I guess Mike's co-workers did and they didn't like the fact that QA displays
all four letters suggesting something nasty and unpredictable. :)
ML|||He's not talking about the string "NULL"
He's talking about the NULL value.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:D3191D85-26D4-4187-81CB-B40E21E683B1@.microsoft.com...
> Have you?
> I guess Mike's co-workers did and they didn't like the fact that QA
> displays
> all four letters suggesting something nasty and unpredictable. :)
>
> ML|||Yes, I know it, you know it and Mike knows it. But do *they* know it? I mean
- just look at it. Sure looks like a string. And if it looks like a string,
then it must be a string. :)
ML
p.s. we are still joking here, right?|||yup. :)
so did you try it?
"ML" <ML@.discussions.microsoft.com> wrote in message
news:834F8E45-5C09-4A69-B9E7-5CBFEF9A3165@.microsoft.com...
> Yes, I know it, you know it and Mike knows it. But do *they* know it? I
> mean
> - just look at it. Sure looks like a string. And if it looks like a
> string,
> then it must be a string. :)
>
> ML
> p.s. we are still joking here, right?|||My QA displays NULL in the result-set. And if I copy and paste it as text it
becomes a string. That's what makes it nasty and unpredictable. :)
It's quite obvious why they decided on '?'. That is if one of them went
through the same process of analysis. LOL
ML|||> Sr. developer:
> "NULL? We can't use null... not in a char(1) column... there's no room!
> NULL
> is like four characters long... futher more it might get truncated to 'N'
> and
> that's already reserved... and even if it doesn't - who knows what NULL
> menas... better make it a '?'... it's a question mark... everybody knows
> what
> *that* means..."
> Jr. developer:
> "Yeah, I think it's brilliant, sir. And I'd like to say that *you're*
> brilliant, too, sir!"
ROFLMAO!
This is EXACTLY how they work here! OMG! I didn't even know that you
worked for TNS!
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conquerer.
Kill them all and you're a god." -- Dave Mustane
"ML" <ML@.discussions.microsoft.com> wrote in message
news:BAD57E39-F45F-4547-8540-DA8943EC2B1A@.microsoft.com...
> Mike:
> #$&% *!|||TNS is not an isolated case. :) If that's any consolation to you.
Overheard in an office:
"Relational model? Yes, we do have one. I'm married to the boss. Now go back
to work!"
ML

Registered Customers

This problem involves a company that only sells goods to customers that are registered

Two tables are concerned
1. reg_cust cust_id CHAR(6) PRIMARY KEY
2. sale_tran cust_id CHAR(6) REFERENCES reg_cust, sale_date DATE, inv_no INTEGER

A constraint must be applied so that before a record is appended to the sale_tran table a check is made to see if the customer is registered in the reg_cust table. If the customer is not registered then the sale is aborted.

One or more SQL statements need to be written to apply the constraint then to check that it is working

Has anyone got any ideas?? You don't need to do anything for this except create the foreign key constraint. At that point, they will only be able to enter a sale_tran if there is a reg_cust. There is no checking to do. It just works (unless of course you have certain flavors of mySQL).|||1 -- Create the reg_cust table --

CREATE TABLE reg_cust( cust_id CHAR(6) PRIMARY KEY);

2 -- Populate reg_cust --

INSERT INTO reg_cust VALUES('ABC123');
INSERT INTO reg_cust VALUES('DEF456');
INSERT INTO reg_cust VALUES('GHI123');
INSERT INTO reg_cust VALUES('JKL456');

3 -- Display the table --

SELECT * FROM reg_cust;

4 -- So far so good, so create the sale_tran table--

CREATE TABLE sale_tran (cust_id CHAR(6) REFERENCES reg_cust, sale_date DATE, inv_no INTEGER);

5 -- Populate sale_tran --

INSERT INTO sale_tran VALUES('DEF456', DATE('2004-06-15'), 200406123);
INSERT INTO sale_tran VALUES('GHI123', DATE('2004-06-15'), 200406124);
SELECT * FROM sale_tran;

6 -- This is the point where the constraint was requested --
The check could have been included at point 4 but what was needed was an alteration to an existing table

ALTER TABLE sale_tran
ADD CHECK (EXISTS(SELECT cust_id FROM reg_cust WHERE
cust_id = sale_tran.cust_id));

7 -- Alteration was successful - try an invalid entry --

INSERT INTO sale_tran VALUES('XYZ456', DATE('2004-06-15'), 200406125);

8 -- Constraint works, following message is given --

SQLSTATE 23000
[Sybase][ODBC Driver] Integrity constraint violation: Invalid value for column 'cust_id' in table 'sale_tran'

9 -- Try another valid value --

INSERT INTO sale_tran VALUES('JKL456', DATE('2004-06-15'), 200406126);
SELECT * FROM sale_tran;

10 -- Table is displayed as expected Task completed--