Re: Meaning 1:n non identifying
A 1:M (one to many) non-identifying means that the associated record in the -one- table is not a parent of the associated record in the -many- table, but rather just related. An example: an identifying 1:M would be a building which has many rooms. The -one- table carries data about the building .. e.g., street address, number of floors, name. The -many- table carries data about the rooms in the building .. e.g., room number, capacity, special characteristics. The room cannot exist without the building, therefore this 1:M is an identifying relationship. a non-identifying 1:M would be a DVD which has many renters. The -one- table carries data about the DVD... e.g., the movie name, the artist. The -many- table carries data about each person who rents that DVD .. e.g., name, price paid. The DVD can exist on its own, without ever having been rented, and the person can exist on her own, without ever having rented any DVD, therefore this 1:M is a non-identifying relationship. Donna Hinshaw GH wrote: weird... about 1:! generalization and 1:m non identifying... I think that these examples can make it better to understand some of those terms... I am quoting from Database Systems -- Design, Implementation Management fouth edition by Rob Coronel (page 23) Conceptual Modules use three types of relationships to descrive associates amond data: one-to-many, many-to-many, and one-to-one. Database designers usually use shorthand notations 1:M, M:N, and 1:1 for them, respectfully. The following examples illustrate the distinctions among the three. 1. *ONE-TO-MANY Relationships* A painter pains many diffrent paintings, but each one of hem is painted by only that painter. Thus the painter (the one) is related to the paintings (the many). Therefore, database designers lable the relationship PAINTER paints PAINTINGS as 1:M. Simillarly, a customer account (the one) might contain many invoices, but those invoices (the many) are related to only a singe customer account. The CUSTOMER generates INVOICE relationship would also be labled 1:M 2 *MANY-TO-MANY Relationship* An employee might learn many job skills, ans each job skill might be learned by many employees. Database designers label the relationship EMPLOYEE learns SKILL as M:N. Similarly, a student can take many courses, and each course can be taken by many students, thus yielding the M:N relationship label for the relationship for the relationship expressed by STUDENT takes COURSE 3 *ONE-TO-ONE Relationship* A retail company's management structure may require that eaco one of its stores be managed by a single employee. In turn, each store manager -- who is an employee -- only manages a single store. Therefore the relationship EMPLOYEE manages STORE is labled 1:1 Hope that this helps... as per the non identifying and the generalizations... DUNNO On Thu, 30 Sep 2004 13:14:03 -0400, Joshua Beall [EMAIL PROTECTED] wrote: Hi All, I've been taking a look at DB Designer 4, and looking through the documentation (http://www.fabforce.net/dbdesigner4/doc/index.html) I am a little unclear on some of their nomenclature: '1:1' - Ok, one to one. Got it. '1:1' generalization - Don't know this. Obviously different somehow from one to one, but how? '1:n' - One to many, I assume. '1:n non identifying' - Nonidentifying? What does this mean? 'n:m' - Many to many? Again, not sure. Can anyone help clarify? Thanks! -Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: browser form question
within Java, you'll need to create a new String object, like String textCapitalized = new String; and then when you receive the web text input (say you call it webText) you do the capitalize function on webText and set it to textCapitalized, like textCapitalized = capitalizeFCT(webText); and then in your SQL insert statement you use thetextCapitalized string in the values clause, like statement = insert into yourTable (col1, col2, textCol) values (val1, val2, textCapitalized) you'll need to include around the strings, using the escape character of\of course. donna Chris Ripley wrote: I've been searching long and hard and have come across a few techniques for changing text entries with lower case (entered by web user) and changing them to have first letter capitalized. in php. ucfirst in java capitalize But I'm not having success on the database side. Everything they enter from the web form hits the database as entered, or raw, or lower cased. Is there a way to have the text fixed before it hits the database? -chris = Chris Ripley [EMAIL PROTECTED] KOZE Radio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
inserting null to not null columns
Hi folks: I have an InnoDB database, the tables created using MySQL Control Center 0.9.4-beta (winXP pro platform). Each table has some columns which I have checked as Nulls Allowed. I am building a pure Java GUI to the database. Got the SQL statements working fine, but have discovered that I can successfully insert rows into a table without including a value for a column which should be blocking nulls. e.g. Table A id (PK, auto increment) name type ssn ( nulls allowed specified) == name and type do not have nulls allowed specified, so I think they should be NOT NULL. they also have no default specified. then insert into A (id,name,ssn) values (NULL,Jane,9) this statement works fine, but I think it should give me an error by saying that I'm trying to insert a row without providing a value for the type column (which has no default specified). Looking at the create statement for the tables, MySQL Control Center has supplied defaults of blanks...can I turn off that preference ? using MySQL 4.0.18 Can anyone provide clarification? thanks... Donna
formating output
Hi folks - I have looked thru the documentation and the books I have on MySQL - but have not found anything on how to format column output from select statements. I'm trying to do several things - a) if a date is the default of -00-00 then just show a blank b) for a phone number of 99, format the output as 999-999- Hints? thanks, know how to do this in oracle, but in mysql ...? Donna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: crosstabs and pivot tables
Shawn - Maybe terminology here...but I think of a crosstab query as one which yields sums or averages or some such tabulation. What I need to do is just pivot from rows to columns. The way I've come up with is this: ORIGINAL TABLE: columns = id date datetype there are about 20 datetypes (eg. entered_date, modified_date, application_date) there can be up to 20 rows for each ID value DESIRED TABLE: columns = id entered_date modified_date application_date so there is just one row for each ID value SOLUTION: create temporary tables for each of the date types columns for TEMP1 = id entered_date columns for TEMP2 = id modified_date columns for TEMP3 = id application_date so on for all date types then, do an insert into the DESIRED TABLE for all columns, as select ( ... join all 20 tables on the ID value ) Since there are over 23000 ID values, and over 20 date types, this join on all 20 TEMP tables will be an overnight batch process, which is fine for the users - this DESIRED TABLE is for reporting somewhat after the fact, and one day out-of-synch is fine. Any other suggestions? Donna [EMAIL PROTECTED] wrote: I still see what you want as a crosstab query. The only difference, as you say very well, is that you want to pivot on the date type values and not the ID values. The only other thing you need to decide in order to make a crosstab report is what information goes in the position for the row ID=x and column datetype=y. Do you want to see the SUM of some value for each of the rows with that ID value and Date type? Or the average or maximum or minimum or the standard deviation for the set. I am asking you how do you want to calculate each of the values of x, y, z, and w as you listed them in your example output? It's the same pattern as the other crosstab queries (aka pivot tables) but you have to tell me which column you want to calculate values from and which calculation to use before I can give you an example using your data. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Donna Hinshaw [EMAIL PROTECTED] wrote on 08/20/2004 03:13:16 PM: Hi - I've read the threads about converting rows of data into columns, but those threads assume the number of distinct rows is very limited (say 7 for days of week). Instead, I have a table like this: iddate date type 1 ... a 1 ... b 2 a 2 d Where the number of distinct id values is in the 100,000 range but the distinct date types are limited to about 20. I want to get a table (not a view) like this: iddate a date b date d .. 1 x y null 2 z null w (based on the values in the first table above) So...I can't think how to do this. Help would be appreciated. TIA. dmh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
rows to columns - not crosstab
Hi - I've read the threads about converting rows of data into columns, but those threads assume the number of distinct rows is very limited (say 7 for days of week). Instead, I have a table like this: iddate date type 1 ... a 1 ... b 2 a 2 d Where the number of distinct id values is in the 100,000 range but the distinct date types are limited to about 20. I want to get a table (not a view) like this: iddate a date b date d .. 1 x y null 2 z null w (based on the values in the first table above) So...I can't think how to do this. Help would be appreciated. TIA. dmh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]