?
Solved

Relational Database Cardinality (How is it expressed)

Posted on 1998-11-13
10
Medium Priority
?
1,563 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 280 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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…
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.
Suggested Courses

800 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