Home » SQL & PL/SQL » SQL & PL/SQL » What do they mean by 8 integrity constraints? (Windox XP)
What do they mean by 8 integrity constraints? [message #425622] Sat, 10 October 2009 09:25 Go to next message
snow111
Messages: 2
Registered: October 2009
Junior Member
I am not sure if i am in the right section but i couldn't find a beginners section. This is my first post.

I am suppose to use SQL DDL to create suppliers, parts and sp databse. And properly define primary keys and foreign keys with other 8 integrity constraints.

Table Suppliers has S# (primary key), sname, status, city
==> inside my create table suppliers, i create a constraint for primary key s#

Table Parts has p#(primary key), pname, color, weight, city
==> inside my create table Parts, i create a constraint for primary key p#
Table Sp has s#(foreign key), p#(foreign key), pname, color, weight, city.
==> inside my create table Sp, i create a constraint on foreign key s#
and a constraint on foreign key p#.

My problem: What does the instruction mean to create 8 integrity constraints when i am only able to have two primary keys and two foreign keys???

[Updated on: Sat, 10 October 2009 09:33]

Report message to a moderator

Re: What do they mean by 8 integrity constraints? [message #425623 is a reply to message #425622] Sat, 10 October 2009 10:05 Go to previous messageGo to next message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
There are other types of integrity constraints than just primary/foreign keys.

Not Null
Primary Key
Foreign Key
Check
Unique

See Data Integrity in the documentation.

So the "other eight" I suppose are supposed to be of the other three types.
Re: What do they mean by 8 integrity constraints? [message #425625 is a reply to message #425623] Sat, 10 October 2009 12:24 Go to previous messageGo to next message
snow111
Messages: 2
Registered: October 2009
Junior Member
That's what i was thinking but my notes list

Types of constraints.

- integrity constraints: primary key and foreign key.

- values constraints : not null and unique

Thus, i think these are two different constraints. I only want to create 8 integrity constraints.

Shocked

Edit:

Oh, Okay, i looked up wikipedia and includes more types including not null.

I'll just include those mentioned in there.

Thanks for your help. I was just confused by the notes teacher gave us. Smile

[Updated on: Sat, 10 October 2009 12:53]

Report message to a moderator

Re: What do they mean by 8 integrity constraints? [message #425643 is a reply to message #425625] Sun, 11 October 2009 03:33 Go to previous messageGo to next message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I have found that the oracle documentation is less confusing than teachers notes most of the time.

Of course there is always those pesky teachers that base their grades on what they want to hear, not on what's actually correct. ./fa/917/0/
Re: What do they mean by 8 integrity constraints? [message #425841 is a reply to message #425622] Mon, 12 October 2009 08:03 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Integrity is a loose term. All the types of constraints you decribed:

primary key
foreign key
unique key
not null
check

are all integrity constraints. They all improve the quality (eg. integrity) of your data. There are other kinds of constraints too, for example:

cardinality
dependence

These do not have native mechanisms of enforcement in most databases and so people do not often talk about them. Indeed, in the end it is all about defining the rules your data are supposed to follow and then finding ways to enforce it. Think of integrity that way and it will open your mind to improved discussions about it.

You should have three primary keys not two. Each table should have a primary key, each table should ideally also have a unique key though sometimes not. Thus 3 primary keys + 3 unique keys + 2 foreign keys = 8 constraints. Maybe that is what your teacher is looking for? Who knows? Have you tried asking your teacher?

Many of us who have had some significant modeling background prefer to use the terms SYSTEM KEY and BUSINESS KEY rather than PRIMARY KEY and UNIQUE KEY. UNIQUE KEYs can become loose as your database design develops, for example your SP table will have a unique key which technically is not composed of business identifiers.

Think for a moment about the difference between SYSTEM key and BUSINESS key. What would be the system key be for your PART table? What would be the business key be for your PART table? Hmm.. I am guessing but maybe PART_ID vs. PART_NUMBER.

What is a system key?
What is a business key?

Should the business ever see a system key or even care they exist?

Good luck, Kevin

[Updated on: Mon, 12 October 2009 08:05]

Report message to a moderator

Re: What do they mean by 8 integrity constraints? [message #426023 is a reply to message #425622] Tue, 13 October 2009 16:17 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Consider these three tables:

create table part
(
    part_id number not null
  , part_number varchar2(30) not null
  , part_desc varchar2(4000)
)
/

create table supplier
(
    supplier_id number not null
  , supplier_identifier varchar2(30) not null
  , supplier_name varchar2(61) not null
)
/

create table inventory
(
    inventory_id number not null
  , part_id number not null
  , supplier_id number not null
  , qty_on_hand number not null
)
/


You build ALTER TABLE statements that create the primary keys, unique keys, and foreign keys.

Kevin
Previous Topic: without distinct
Next Topic: Asynchronous call using UTL_HTTP in Oracle 9i
Goto Forum:
  


Current Time: Sun Sep 25 06:08:36 CDT 2016

Total time taken to generate the page: 0.12853 seconds