Solved

Relational Database Cardinality (How is it expressed)

Posted on 1998-11-13
10
1,549 Views
Last Modified: 2012-06-22
Greetings,

I am trying to understand how cardinality is expressed in a relational database schema as opposed to a schema which is object oriented.

For instance, when creating a few tables(relational) for a bus company where the corprate office table has a relation with branch offices table, how can the one to many relation be expressed?  Here are the two tables:

create table XYZ_Bus_Company   --Corp. Office
(
  Branch_Name varchar(20)not null,  
  Employee_SSN char(9)not null,
  primary key(Branch_Name, Employee_SSN),
  foreign key(Branch_Name) references(Branch_Offices),
  foreign key(Employee_SSN) references(Employee)
);

create table Branch_Offices
(
  Branch_Name varchar(20),
  Address varchar(30),

The XYZ Bus Co. has many branches but the Branch Office table has only one corprate office.  With the attributes I have above, how does the Branch_Offices table relate to the corprate office?

In other words, what field/attribute do I need to add to complete the relation?  I want to add the following line to the Branch_Offices table but I don't know if it would be the best way because the field would be somewhat redundant in each tuple:

Corp_Office char(11), --XYZ Company

 Thanks

John



0
Comment
Question by:John500
10 Comments
 

Author Comment

by:John500
ID: 1091257
Edited text of question
0
 
LVL 2

Expert Comment

by:aliciaam
ID: 1091258
First of all cardinality is the number of rows in an index or table. Relationship is a different story.
the tables relate to each other with the field branch_name. For each Branch_Name on Branch office there are many branch_names on the xyz_bus_company, meaning more than one row holding that branchname.
such as:
branchname
new york
california

branchname      employee-ssn
new york           111-11-1111
new york            222-22-2222
california            333-33-3333
california            444-44-4444

there you have the relation one to many for each record on the first table, you may have many on the second table
0
 

Author Comment

by:John500
ID: 1091259
Adjusted points to 70
0
 

Author Comment

by:John500
ID: 1091260
Ok,

How about the line I propose to complete the relation:

Corp_Office (11), --XYX Company

Also, wouldn't it make sense to make the primary key of Branch_Offices, Branch_Name?  Thanks

John
0
 
LVL 2

Expert Comment

by:aliciaam
ID: 1091261
Yes, as long as every field on that table depend on both. You can do that.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:John500
ID: 1091262
When you said yes, as long as every field on that table depends on both, you can do that.  What question were you answering in my comment added?

Also, you may be interested to know that in terms of database modeling, cardinality does refer to relationship alone!  That is, one-to-one, one-to-many, many-to-many.  A quote from a text I have goes, "cardinality ratio specifies the number of relationship instances that an entity can participate in."  The text goes on to give examples using 1:1, 1:m, m:m.

However, that subject refers to object oriented database modeling, so maybe cardinality has a dual meaning depending on what kind of modeling is being done.

I'm taking an object oriented model and converting it to a relational model/database, that's why I've jumped back and forth with these terms.  Since a relational model has single valued attributes, and the model I'm trying to convert has multivalued attributes, I asked the original question...

John
0
 

Author Comment

by:John500
ID: 1091263
Too vague, answers should refer and use my terms so that I can follow the answer.

Answers like "Yes, as long as every field on that table depend on both. You can do that"  don't answer the question when I've posed more than one question in that sentence.

Also, responses shouldn't take days to get, otherwise you may as well pack-up your "service" and hit the road!
0
 
LVL 1

Accepted Solution

by:
pmteixeira earned 70 total points
ID: 1091264
Hi John,

let's see if I can make it clear to you...

1 - Your 2 tables...

I think you want to have a "XYZ_Company" that has many "Branch_Offices", and one of them is the "Corporate_Office" (like a main office).

If it is like that, you must have 2 tables (you already have them).
You want a relationship between them:  "Company" - 1:m - "Branch_Office". How do you represent it on the tables?

From the Company side you would have to link it to a variable number of branches; can't do it with an attribute (column). But from the Branch_Office side you have to link it to 1 Company. That can be done by putting the "Company" key/id attribute in the Branch_Office table, making it a foreign key to the "Company" table.
You solved your problem! A "Branch_Office" points to a "Company".

How can you get all the offices of the company? You search the "Branch_Office" table for every row that "points" to the "Company" you want!

Now, let's go to the point where a "Company has one corporate office":
you want a relationship "Company" - 1:1 - "Branch_Office". For this, you put the "Branch_Office" key/id attribute (koreign key to Branch_Office) in the "Company" table.
Like this, any Company you insert points to a Branch_Office (the corporate office). You could also put the relationship attribute in the other side, but why would you do this when most of the Branch_Offices are not main offices, and would have the relationship attribute filled with NULL value?

As you might see, this 2 kinds of relationship are represented with an attribute. You can only figure out what kind of relationship it is, by knowing/looking the conceptual model.

Hope this helps,

PMT

0
 

Author Comment

by:John500
ID: 1091265
Thanks!
0
 

Expert Comment

by:kstal
ID: 2599237
how create relation btween tow table same of db access 97 thank's
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now