Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1665 > unrolled thread

A question about relationship

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  A question about relationship "Tony Johansson" <johansson.andersson@telia.com> - 2014-02-10 15:18 +0100
    Re: A question about relationship Erland Sommarskog <esquel@sommarskog.se> - 2014-02-10 23:39 +0100
      Re: A question about relationship "Tony Johansson" <johansson.andersson@telia.com> - 2014-02-11 15:39 +0100
        Re: A question about relationship Ross Presser <rpresser@gmail.com> - 2014-02-11 07:11 -0800
          Re: A question about relationship bradbury9 <ray.bradbury9@gmail.com> - 2014-02-11 08:43 -0800
            Re: A question about relationship Ross Presser <rpresser@gmail.com> - 2014-02-11 09:33 -0800
          Re: A question about relationship Lennart Jonsson <erik.lennart.jonsson@gmail.com> - 2014-02-11 18:01 +0100
        Re: A question about relationship Erland Sommarskog <esquel@sommarskog.se> - 2014-02-11 21:54 +0100
          Re: A question about relationship rja.carnegie@gmail.com - 2014-02-11 13:49 -0800
            Re: A question about relationship Erland Sommarskog <esquel@sommarskog.se> - 2014-02-11 23:23 +0100

#1665 — A question about relationship

I read a book about  ASP.NET that talk about Entity Framework and here they 
use the Northwind database as an example.

What I find strange is the relationship between Customer and Order.
The book print the relationship between Customer and Order like this
!--------------!                           !-------------------!
!   Customer   ! 0.1 ................ * !           Order       !
!--------------!                           !-------------------!
I found this strange because it means you can have orders without a 
Customer.

//Tony

[toc] | [next] | [standalone]

#1669

Tony Johansson (johansson.andersson@telia.com) writes:
> What I find strange is the relationship between Customer and Order.
> The book print the relationship between Customer and Order like this
> !--------------!                           !-------------------!
> !   Customer   ! 0.1 ................ * !           Order       !
> !--------------!                           !-------------------!
> I found this strange because it means you can have orders without a 
> Customer.
 
Not sure how to interpret that, but there can certainly be customers 
without orders. I believe that Northwind has three of them.


-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [next] | [standalone]

#1671

Yes a customer without order sounds ok
But order without customer is incorrect.

//Tony

"Erland Sommarskog" <esquel@sommarskog.se> skrev i meddelandet 
news:XnsA2D0F0AEE3E6Yazorman@127.0.0.1...
> Tony Johansson (johansson.andersson@telia.com) writes:
>> What I find strange is the relationship between Customer and Order.
>> The book print the relationship between Customer and Order like this
>> !--------------!                           !-------------------!
>> !   Customer   ! 0.1 ................ * !           Order       !
>> !--------------!                           !-------------------!
>> I found this strange because it means you can have orders without a
>> Customer.
>
> Not sure how to interpret that, but there can certainly be customers
> without orders. I believe that Northwind has three of them.
>
>
> -- 
> Erland Sommarskog, Stockholm, esquel@sommarskog.se 

[toc] | [prev] | [next] | [standalone]

#1672

On Tuesday, February 11, 2014 9:39:17 AM UTC-5, Tony Johansson wrote:
> Yes a customer without order sounds ok
> 
> But order without customer is incorrect.

It isn't true. The book is describing the database incorrectly.

The Northwind database can be downloaded here
http://www.microsoft.com/en-us/download/confirmation.aspx?id=23654

And it can be seen from the scripts that each Order must relate 
to one Customer. See the section I have marked out with hyphens:

CREATE TABLE "Orders" (
	"OrderID" "int" IDENTITY (1, 1) NOT NULL ,
	"CustomerID" nchar (5) NULL ,
	"EmployeeID" "int" NULL ,
	"OrderDate" "datetime" NULL ,
	"RequiredDate" "datetime" NULL ,
	"ShippedDate" "datetime" NULL ,
	"ShipVia" "int" NULL ,
	"Freight" "money" NULL CONSTRAINT "DF_Orders_Freight" DEFAULT (0),
	"ShipName" nvarchar (40) NULL ,
	"ShipAddress" nvarchar (60) NULL ,
	"ShipCity" nvarchar (15) NULL ,
	"ShipRegion" nvarchar (15) NULL ,
	"ShipPostalCode" nvarchar (10) NULL ,
	"ShipCountry" nvarchar (15) NULL ,
	CONSTRAINT "PK_Orders" PRIMARY KEY  CLUSTERED 
	(
		"OrderID"
	),
------------------------------------------------------    
	CONSTRAINT "FK_Orders_Customers" FOREIGN KEY 
	(
		"CustomerID"
	) REFERENCES "dbo"."Customers" (
		"CustomerID
	),
------------------------------------------------------    
	CONSTRAINT "FK_Orders_Employees" FOREIGN KEY 
	(
		"EmployeeID"
	) REFERENCES "dbo"."Employees" (
		"EmployeeID"
	),
	CONSTRAINT "FK_Orders_Shippers" FOREIGN KEY 
	(
		"ShipVia"
	) REFERENCES "dbo"."Shippers" (
		"ShipperID"
	)
)

[toc] | [prev] | [next] | [standalone]

#1673

Well, in fact Orders.CustomerID should be NOT NULL. Althought a demo database
 
> CREATE TABLE "Orders" (
> 	"OrderID" "int" IDENTITY (1, 1) NOT NULL ,
> 	"CustomerID" nchar (5) NULL , /* This seems to be wrong */
> 	"EmployeeID" "int" NULL ,
> 	"OrderDate" "datetime" NULL ,

[toc] | [prev] | [next] | [standalone]

#1675

On Tuesday, February 11, 2014 11:43:44 AM UTC-5, bradbury9 wrote:
> Well, in fact Orders.CustomerID should be NOT NULL. Althought a demo database
> 
> > CREATE TABLE "Orders" (
> > 	"OrderID" "int" IDENTITY (1, 1) NOT NULL ,
> > 	"CustomerID" nchar (5) NULL , /* This seems to be wrong */
> > 	"EmployeeID" "int" NULL ,
> > 	"OrderDate" "datetime" NULL ,

Oops, I missed that. Good point.

[toc] | [prev] | [next] | [standalone]

#1674

On 02/11/2014 04:11 PM, Ross Presser wrote:
[...]
> And it can be seen from the scripts that each Order must relate 
> to one Customer. See the section I have marked out with hyphens:
> 

CustomerId can be null, and therefore an order must not relate to one
Customer.


Cheers
/Lennart

[toc] | [prev] | [next] | [standalone]

#1677

Tony Johansson (johansson.andersson@telia.com) writes:
> Yes a customer without order sounds ok
> But order without customer is incorrect.
 
I would say that it depends on the business rules. :-) But, yes, it
would be a bit peculiar.

In Northwind CustomerID is nullable, although there is no such order 
in the data. Unfortunately, Northwind leaves a whole lot desired when it 
comes to nullability, indexes etc.

-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [next] | [standalone]

#1679

On Tuesday, 11 February 2014 20:54:38 UTC, Erland Sommarskog  wrote:
> Tony Johansson (johansson.andersson@telia.com) writes:
> > Yes a customer without order sounds ok
> > But order without customer is incorrect.
> 
> I would say that it depends on the business rules. :-) But, yes, it
> would be a bit peculiar.
> 
> In Northwind CustomerID is nullable, although there is no such order 
> in the data. Unfortunately, Northwind leaves a whole lot desired when it 
> comes to nullability, indexes etc.

I don't remember if I ever worked in the Northwind database.
(My actual training was some of the authentic courses for 
SQL Server 7.)  So I don't know if some of its features are
included intentionally to attract criticism for extra credit
in class - you know, spot the little design mistakes.  
That can be good practice for real work.  Or if it's just
not particularly well done.

It bothers me a little that most database courses I've seen
are about developing yet another new customer and order database 
for a business, when that's the last thing that the world needs - 
unless you have an amazing new idea about how to do it.  
Maybe that's what they're looking for?

But I suppose that (1) it's really about understanding how
data table relationships work and how to use SQL to talk to
your database, and (2) it's businessy.  Whereas a course 
based around recording a kindergarten class's personal 
achievements, family relationships and friend and unfriendships, 
allergies, and favourite colours, TV shows, etc., for instance, 
might be too "out there".  Although curiously, I run the 
database for - :-)

Also...  I /actually/ work in a room of around 30 men 
and, as far as I've noticed, 1 woman.  That's not right.
I should have my own office.

[toc] | [prev] | [next] | [standalone]

#1680

 (rja.carnegie@gmail.com) writes:
> I don't remember if I ever worked in the Northwind database.
> (My actual training was some of the authentic courses for 
> SQL Server 7.)  So I don't know if some of its features are
> included intentionally to attract criticism for extra credit
> in class - you know, spot the little design mistakes.  
> That can be good practice for real work.  Or if it's just
> not particularly well done.

As I understand it, Northwind comes from Acesss. 'nuff said. :-)
 
> It bothers me a little that most database courses I've seen
> are about developing yet another new customer and order database 
> for a business, when that's the last thing that the world needs - 
> unless you have an amazing new idea about how to do it.  
> Maybe that's what they're looking for?

I think that an order database is an excellent choice for a database
course, because most people can grasp the business rules fairly
easily. One of the most difficult challenges in database design is
to understand the problem domain. And when you are teaching the basics,
you don't want the students to get lost in understanding how a more
complicated scenario works.
 


-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web