Solved

Relational Database Cardinality (How is it expressed)

Posted on 1998-11-13
10
1,546 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

758 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

18 Experts available now in Live!

Get 1:1 Help Now