Solved

Relational Database Cardinality (How is it expressed)

Posted on 1998-11-13
10
1,561 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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

726 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