1) How to Store jpg in mySQL 2) mySQL not RDBMS? 3

[zariya]

I'm just starting mySQL first time .. I've 2 questions.

1) How can we store pictures (.bmp, .jpg, or etc) in mySQL ? Should we use BLOB type ? Once stored, how can we retrieve that (say, if we pull up John's record, his picture must also be pulled up with his phone# and address)

2) I read on dbForum that mySQL is not (or probably is) a RDBMS. Its confusing for me. Is mySQL RDBMS, i.e. can we define FK in its table.

Above questions are for mySQL 4.1.1 and 4.0.17.

 

[Dimandja]

You can store BLOB type columns. These will not be separate files however, and your table will quickly balloon in size.

My recommendation is to store 'the path to the picture' instead of the actual picture.

MySQL is described as "a Relational Database Management System (RDBMS) optimised for speed and interactivity".

Dimandja

 

[psemianonymous]

It depends on who you ask, as to what defines an RDBMS. Go to dbdebunk.com or certain newsgroups (B*b Bad-zzz-our, spelling mangled intentionally, on comp.databases), and nothing satisfies the requirements.

So, to answer your question, yes--MySQL supports enough features, like referential integrity, that most people say it's an RDBMS. You can indeed define and store foreign keys in MySQL.

 

[harebrain]

Foolio12 is right: the RDBMS "purists" make a similar case against almost any DBMS. Here's a sample argument:

A relational database requires that a table have a primary key. A DBMS that allows the creation of tables without a primary key defined are therefore not relational.

That simple argument "proves" that Oracle, SQL Server, etc. are not relational databases. It's kind of like saying that bumblebees are not aerodynamic and therefore can't possibly fly. The difference is between the academics and the practitioners.

 

[r937]

great analogy, harebrain

everybody knows bumblebees can't fly


i've been to dbdebunk.com

some of what they say actually makes good sense

however, the way they say it really p*sses me off, and i invariably leave the site after only one or two minutes

arrogant idjits


rudy

http://r937.com/

 

[psemianonymous]

I used to enjoy reading dbdebunk, and I think I got one positive thing[1] from reading it, but two things turned me off: a)dismissive email exchanges, in which Fabian refuses to argue at all, and instead uses big words to demean the other person, and b)the political stuff, recently added. I can't imagine arguing politics with this man (actually, I can).

[1]"The Final Null in the Coffin" - basically a way to build a relational db structure without allowing Null values. If you do it (as I have on my recent project) it unfortunately complicates the design in a way--but in another way, it forced me to do the relations right. If you want to use the database itself for more of your data validation, definitely look into this paper. I think most of your data validation can be database-enforced (which is good) if you use this method:

http://www.hughdarwen.freeola.com/TheThirdManifesto.web/Missing-info-without-nulls.pdf

(PDF file)

 

[r937]

thanks for that link, foolio12

the author starts with a simple table:

[tt]PERS_INFO
Id Name Job Salary
1234 Anne Lawyer 100,000
1235 Boris Banker ?
1236 Cindy ? 70,000
1237 Davinder ? ?[/tt]

and then proceeds to tear it to shreds, vertically and horizontally decomposinbg it so that all the "impossibilities" are eliminated

[tt]CALLED ( Id, Name )
DOES_JOB ( Id, Job )
JOB_UNK ( Id )
UNEMPLOYED ( Id )
EARNS ( Id, Salary )
SALARY_UNK ( Id )
UNSALARIED ( Id )[/tt]

then from all these itsy bitsy tables, he shows you how to "derive" the original PERS_INFO table --

[tt]WITH
(EXTEND JOB_UNK ADD ‘Job unknown’ AS Job_info) AS T1,
(EXTEND UNEMPLOYED ADD ‘Unemployed’ AS Job_info) AS T2,
(DOES_JOB RENAME (Job AS Job_info)) AS T3,
(EXTEND SALARY_UNK ADD ‘Salary unknown’ AS Sal_info) AS T4,
(EXTEND UNSALARIED ADD ‘Unsalaried’ AS Sal_info) AS T5,
(EXTEND EARNS ADD CHAR(Salary) AS Sal_info) AS T6,
(T6 { ALL BUT Salary }) AS T7,
(UNION ( T1, T2, T3 )) AS T8,
(UNION ( T4, T5, T7 )) AS T9,
(JOIN ( CALLED, T8, T9 )) AS PERS_INFO :
PERS_INFO[/tt]

and at the bottom of this, he has the gall to add "Q.E.D."

i don't think he realizes the irony of that little "Q.E.D."

i don't think he understands exactly what it is he has just demonstrated


rudy

 

[Dimandja]

I love this! One of the best philosophical debates I have tried to follow, in the software industry. "Geeks", as some of us have been called, are not supposed to be so 'aware' - well, it's not true at all.

Dimandja

 

[CajunCenturion]

I think that some are failing to distinguish between the DBMS and the DB.

No DBMS (DataBase Management System) is relational, because it's primarily software. Oracle, SQL Server, MySQL are not relational DB', they are DBMS that can and do support relational databases, but that do not require that the databases they support be relational.

Only databases can be relational. That database is relational if every table and relationahip conforms to the mathematical concepts of relations (as opposed to functions), and the associated set theory. They are not "relational" just because they are related. They are relational if their exists a mathematical relationship between them. ie r = y The process of normalization greatly aids in the bringing the tables together in this mathematical way, achieving the proper "relational" aspect, without having to resort to the rigors of math.

I'm sure that almost everyone here can build a database that is not relational, yet is under the control of SQL Server, Oracle, MySQL, and/or others. I'm sure that almost everyone here can build a database that is relational, and under the control of SQL Server, Oracle, MySQL, and/or others.

"Relational" is a property or attribute of a Database. SQL Server, Oracle, MySQL, and such are not databases, they are Database Management Systems.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Dimandja]

CajunCenturion hit the spot.

 

[harebrain]

CajunCenturion said:

That database is relational if every table and relationahip conforms to the mathematical concepts of relations (as opposed to functions), and the associated set theory. They are not "relational" just because they are related.

It's a common misconception that the word "relational" in RDBMS refers to the verb, "related," and that it is a description of associations among entities. In fact, "relational" derives from the noun, "relation," which is the term for "table" in relational algebra. So, a relational database is one that is expressed as a set of relations (tables.) (This is a starting point; relational algebra, keys, normalization, and everything else is what makes the DB work.)

Prior to Codd's work, databases conformed mostly either to a hierarchical model or a network model. One could see from the schemas of either model that entities were related to other entities, and the manner in which they were related conformed to the model supported by the DBMS. These were not RDBMSs: relationships among entities do not form the basis of the relational model.

Long story short: a database is relational because we view the data as a collection of tables.

 

[r937]

harebrain, i guess we've come full circle, then

"Long story short: a database is relational
because we view the data as a collection of tables."

that pretty much describes mysql tables, doesn't it

so: mysql is a relational database management system

QED

;-)

 

[harebrain]

r937,

Apparently CajunCenturion's admonition was lost on you. ;-)

Although I would quibble with him somewhat: an RDBMS would have to provide the tools to manipulate an RDB, e.g., relational algebra.

 

[r937]

lost on me?

not really -- i caught the important part, which was

"No DBMS is relational,
because it's primarily software."

then he/she went into some mumbo-jumbo about mathematics, which i felt was totally irrelevant, given your excellent summarization that "relational" means collection of tables, as opposed to hierarchical and network, both of which i have also had the pleasure of working with, and lemme tellya, i like relational a lot better

can you tell the theoretical stuff does not impress me at all?

ask a theorist to deliver a working database and you will see the deadline whoosh right on by, while they're still telling you that it can't be done because you haven't described all the mathematical "r=y" relations

like that guy with the PERS_INFO table, i would've implemented the table already, and he'd still be describing how to (EXTEND EARNS ADD CHAR(Salary) AS Sal_info) AS T6, T6 { ALL BUT Salary }) AS T7, (UNION ( T1, T2, T3 )) AS T8,
(UNION ( T4, T5, T7 )) AS T9, (JOIN ( CALLED, T8, T9 ))

for cryin' out pete's sake

and last time i looked, the only function where r=y is the identity function -- e.g. ADD 0 or MULTIPLY BY 1

;-)

 

[harebrain]

"and last time i looked, the only function where r=y is the identity function -- e.g. ADD 0 or MULTIPLY BY 1"

Here I am, distancing myself from those who stopped short of abstract algebra.

;-)  >>>>>>>>>>>>>>>>>  :-)

 

[CajunCenturion]

r937 - The fact that you think the mumbo-jumbo and mathematics, and more specifically, relational algebra, is totally irrelevant, is exactly why you, and others, constantly misunderstand what the term "relational" means with respect to databases. It is not irrelevant, in fact, it is the foundation upon which relational databases exists.

harebrain said, "So, a relational database is one that is expressed as a set of relations (tables.) (This is a starting point; relational algebra, keys, normalization, and everything else is what makes the DB work.)". True, unfortunately, the "long story short" summary is not saying the same thing.

A database is relational if it consists of a set of tables (relations) that conform to a mathematical provable seriers of relations, and relational algebraic operations. It is precisly these mathematical contstraints placed on the tables that make them relational. If you want to ignore all the theoretical stuff, that is your choice, and I'm certainly not trying to impress you. My only hope is all those who claim to be IT professionals know what they're talking about.

harebrain's statement about heirarchical or CODASYL ([network], but I prefer CODASYL so as not to introduce ambiguity with the term network) models vs the relational model is quite correct but the primary, and fundamental difference between these models is that heirarchical and CODASYL models are phyiscal models whereas the relational model is logical. The relational model is a mathematical model, and given that fact, I have trouble understanding how you can say that mathematical mumbo-jumbo that defines the relational model is rrelavent.

As you have worked with these others, you should like the relational better. That was one of its primary objectives. To provide a logical means of representing and manipulating the data without being bogged down in the physical details of storage and implementation requirements.

As harebrain has already alluded to, an RDBMS (no quibble on that point) is one which has the capability to resolve the logical relational algebra, as applied to the logical relational database, in order to perform the appropriate physical operations on the physical database.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[r937]

come on, cajuncenturion, don't take it so hard

i meant nothing towards you personally and i am sorry if you felt slighted

of course i understand the mumbo-jumbo, and i know how relevant and important it is

i love your comment that the relational model provides "a logical means of representing and manipulating the data without being bogged down in the physical details of storage and implementation requirements"

ever been in an argument, er, i mean, discussion, on the merits of natural versus surrogate keys with a relational DBA?


rudy

 

[CajunCenturion]

Yes, I've been in many discussions on surrogate keys.

My take on them is that they are a tool that has a time and a place. I believe that any table which has a reasonable canidate key should not use a surrogate key. That's the easy part. The hard part is defining "reasonable", and there are good points on both, or perhaps, on all sides.

I've seen people use them religously in each and every table, and I've seen others avoid them at all costs. I don't advocate either extreme. I think each situation needs to be evaluated on a case by case basis, weighing the possible introduction of a transitive dependancy, performance issues, overall database complexity, the nature of the canidate key(s), not to mention the overall application. It all boils down to having a full understanding of model and your objectives, and a full understanding of what should be happening inside the database engine, and what is actually happening inside the database engine.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[r937]

excellent reply, and one very close to my own feelings about keys

i especially like your comment about what's "reasonable"

i've met plenty of DBAs (i'm not a DBA, by the way, if that wasn't already apparent) who insist on surrogate keys because "the primary key might change"

 

[CajunCenturion]

I try very hard not to make judgements based on what is posted, but it's hard not make a judgement about a DBA who insists on surrogate keys because the primary key might change.

zariya - This thread has taken some twists and turns that you probably didn't expect when you posted your original question. If that has upset you, then I understand and apologize.

To specifically answer your questions:

1) Similar to Dimandja, I would not use a BLOB. I would store the pictures as stand alone files in an appropriate directory structure, and in the DB, store the full pathname. However, it's quite possible that the Image Root directory and/or structure would be stored in a configuration table(s), and in the elementary records would simply be the filename. This allows me to move the images into a different directory structure only having to updates the configuration table(s).

2) I do consider MySQL to be an RDBMS, as it meets the basic criteria, but it is not as rich in functionality as some other available DBMS's. But its cost is a real nice attribute.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein