Solved

Relational Database Cardinality (How is it expressed)

Posted on 1998-11-13
10
1,557 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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
 

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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

732 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