Solved

Constraints (How They Affect Populating The Database)

Posted on 1998-11-19
15
232 Views
Last Modified: 2010-03-19
Below are the table creation statements that constitute my database.  I am having trouble entering the first tuples in the Employee table.  Employee inherits attributes from the superclass, "Person" by using the "under" statement of the Illustra DBMS.

Also, Administrator, Representitive and Cashier are subclasses of Employee (among others) that have foreign keys associated with them.  I couldn't enter data into the Employee table because of the constraint on Name. I'm guessing the error was related to the Administrator, Representitive and Cashier tables. If I start with the lowest subclass i.e. Administrator and try to add a tuple, I get the following error:

insert into Administrator values
('111-11-1111', 'Jill Smatt',
'101-101-1010', '1901-01-01','Northern',2500000,'5111');
X23C00:integrity constraint violation: Constraint Name _ON_INSERT_REFERENTIAL_IN
TEGRITY_FOR_00000054f3_barbacci_Employee_1_

The Illustra DBMS allows a value of Null in the foreign key
attribute.  Does this mean I have to enter Null in the tables that reference Administrator, Representitive and Cashier?

As I said, if I try starting with Employee, I have a constraint problem, if I start with Person, I don't know how to make the Person an Employee and Persons DON'T automatically become Employees.

Also, I don't want to restructure my tables to not have constraints and then add them, unless, this is CLEARLY the easier way.  Here are my tables:

-- XYZ Bus Company

create table Company       --Corprate Headquarters
(
  Branch_Name varchar(20)not null,  --Corp. name (xyz) included
  Employee_SSN char(11)not null,
  primary key(Branch_Name, Employee_SSN)
);

create table Person
(
  SSN char(11) not null,
  Name varchar(20) not null,
  Phone char(12),
  DOB date,
  primary key (SSN)
);

create table Branch_Offices
(
  Branch_Name varchar(20) not null,
  Address varchar(30),
  Corp_Office char(11),        --XYZ Company
  Secretary varchar(20),
  Manager varchar(20),
  Bus_No varchar(3),                
  primary key(Branch_Name),
  foreign key(Branch_Name) references Company(Branch_Name)
);

create table Passenger
(
  Ticket_No varchar (10)not null,
  Reserv_No varchar(10),
  primary key(Name, Ticket_No)
)under Person;

create table Employee
(
  Branch_Name varchar(20) not null,
  Salary decimal(10,2),
  primary key(SSN, Branch_Name),
  foreign key(Branch_Name) references Company(Branch_Name)
 )under Person;

create table Secretary
(
  Yrs_of_Work int,
  Office_Name char(10),
  primary key(Name, Branch_Name)
)under Employee;

create table Manager
(
  Bonus decimal(10,2),
  Hours decimal(3,1),
  primary key(Name, Branch_Name)
 )under Employee;


create table Cashier
(
  Ticket_No varchar(10) not null,  
  primary key(Name, Ticket_No)
)under Employee;


create table Officer under Employee;

create table Administrator
(
  Route_No varchar(10) not null,            
  primary key(Name, Route_No)
)under Officer;

create table Representative
(
  Reservation_No varchar (10) not null,      
  primary key(Name, Reservation_No)
)under Officer;

create table Driver
(
  Driver_No varchar(5) not null,  
  Trip_No varchar(10) not null,    
  primary key(Driver_No, Trip_No)
)under Employee;

create table Bus
(
  Bus_No varchar(3) not null,
  Branch_Name varchar(20) not null,
  No_of_Seats int,
  Trip_No varchar(10),
  primary key (Bus_No),
  foreign key(Branch_Name) references Branch_Offices(Branch_Name)
);

create table Reservation
(
  Reserv_No varchar(10)not null,
  Passenger char(11)not null,
  Trip_No varchar(10),
  Rep_Name varchar(20),
  primary key (Reserv_No, Passenger),
  foreign key (Passenger) references Passenger(SSN),
  foreign key (Rep_Name) references Representative(Name)
);


create table Route
(
  Route_No varchar(10) not null,
  Origin varchar (15) not null,
  Destination varchar(15) not null,
  Trip_No varchar(10) not null,
  Ticket_No varchar(10),
  Administrator varchar(20),
  primary key(Route_No),
  foreign key(Administrator) references Administrator(Name)
);

create table Trip
(
  Trip_No varchar(10) not null,
  Trip_Date date,
  Driver_No varchar(5),
  Route_No varchar(10),
  Ticket_No varchar(10),
  Reserv_No varchar(10),
  Bus_No varchar(3),
  primary key (Trip_No),
  foreign key(Bus_No) references Bus(Bus_No),
  foreign key(Driver_No, Trip_No) references Driver(Driver_No, Trip_No),
  foreign key(Route_No) references Route(Route_No),
  foreign key(Reserv_No) references Reservation(Reserv_No)
);

create table Ticket
(
  Ticket_No varchar(10) not null,
  Passenger varchar(20) not null,
  Cashier_Name varchar(20),
  Price decimal(10,2),
  Ticket_Date date,
  Trip_No varchar(10) not null,
  Route_No varchar(10) not null,
  primary key(Ticket_No, Passenger),
  foreign key(Passenger) references Passenger(Name),
  foreign key(Trip_No) references Trip(Trip_No),
  foreign key(Route_No) references Route(Route_No),
  foreign key(Cashier_Name, Ticket_No) references Cashier(Name, Ticket_No)
);

insert into Passenger values ('333-33-3333','Joe Shmoe', '101-303-3030', '1903-03-03','7111','6111');
insert into Reservation values ('6111','333-33-3333','8111', null);
insert into Route values ('5111', 'Pittsburgh PA', 'San Diego CA', '8111','7111',null);
insert into Ticket  values('7111','Joe Shmoe', null, 8500, '1998-11-19', '8111', '5111');
--insert into Passenger values ('333-33-3333','Joe Shmoe', '101-303-3030', '1903-03-03','7111','6111');
--insert into Reservation values ('6111','333-33-3333','8111', null);
select * from ml_tables('barbacci');
                                                       

The error I receive for trying to start with Employee looks like this:

insert into Employee values
('111-11-1111', 'Jill Smatt',
'101-101-1010', '1901-01-01','Northern',2500000);
X23C00:integrity constraint violation: Constraint Name _ON_INSERT_REFERENTIAL_IN
TEGRITY_FOR_00000054f3_barbacci_Employee_1_

I feel this is happening because the attribute 'Name' is a foreign key for three employee tables - from the Route table to Administrator, from Reservation to Representitive, from Ticket to Cashier.

Is this correct????  Any suggestions??
















                                                                               

0
Comment
Question by:John500
  • 10
  • 4
15 Comments
 

Author Comment

by:John500
Comment Utility
Edited text of question
0
 

Author Comment

by:John500
Comment Utility
Edited text of question
0
 
LVL 2

Expert Comment

by:formula
Comment Utility
What is "Illustra DBMS" ? It apparently is not Microsoft SQL Server, for one thing I note that you use an "Under" is the table create, which is invalid in SQL Server create table syntax.
So let's get this resolved, and I'll try to help you in the context of MS SQL Server.
0
 

Author Comment

by:John500
Comment Utility
Illustra is a DBMS which uses SQL, but as you said, is not MSQL.  The are part of Digital Equipment Corp.

The "under" statement will transfer the attributes of the superclass to the subclass.

I have made some changes to the tables and will repost them.  I'm still having the same problem however.  Can you make sense of the error?
0
 

Author Comment

by:John500
Comment Utility
Illustra is a DBMS which uses SQL, but as you said, is not MSQL.  The are part of Digital Equipment Corp.

The "under" statement will transfer the attributes of the superclass to the subclass.

I have made some changes to the tables and will repost them.  I'm still having the same problem however.  Can you make sense of the error?
0
 

Author Comment

by:John500
Comment Utility
Edited text of question
0
 

Author Comment

by:John500
Comment Utility
I've been making some progress.  Here is the lates error I can't figure out which is still part of the original problem.

insert into Ticket  values
('7111','Joe Shmoe',NULL, 8500, '1998-11-19', '8111', '5111');
X23C00:integrity constraint violation: Constraint Name _ON_INSERT_REFERENTIAL_IN
TEGRITY_FOR_000000555c_barbacci_Ticket_2_

Since Illustra allows NULL foreign key restraints, why would I get this error?  As you can see below the create statements, my first three insert statements are geared towards satisfying the foreign key issue.  Later I should update the appropriate attributes but I'm still getting an error when NULL is valid.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Accepted Solution

by:
pmteixeira earned 250 total points
Comment Utility
Hi John,

I've never used Illustra and some concepts are not used in common SQL/relational databases. However, by looking to your script I would say 2 things:

1 - First I thought that you would have to insert a row in 'Person' before you insert it in 'Employee'. But if your 'insert into Passenger' worked, then you shouldn't do it.

2 - The second point is that you want to put 'Northern' in 'Branch_name' and this column is a foreign-key to 'Company'. But it seems that you have not inserted a row in 'Company' whose 'Branch_name' is 'Northern'. I think you have to do it first!!!! Try it!


The other points you talk about (foreign-keys) don't seem to be right. You always have to insert in the referenced table before you insert in the referencing table (that has the foreign-keys), but not referencing before referenced. Besides, you may use NULL, as you said, and that way don't have to insert in the referenced table first. But in the statement above, you didn't use NULL, but a value ('Northern'), and this must be in the referenced table!


Hope this will help,

PMT
0
 

Author Comment

by:John500
Comment Utility
Yes, your answer helped greatly.  I think I'm on the way. I put a Branch_Name and Employee in the Company table as you suggested and that enabled me to insert into the Employee table the same employee's information.

The next area of concern is the following:  

If you look at my tables, the subclasses of Person start with Employee, then a subclass of Employee is Officer and then finally a subclass of Officer is Administrator (also Representitive).  How should I insert the information for Administrator?  I want to make the employee, which I have already inserted, an Administrator.  Since the Administrator inherits the Employee and Officer attributes and also adds one more attribute (Route_No) how do I associate Route_No with the Employee already inserted.  I know that the Administrator talbe will accept all the attributes of an Employee plus the added Route_No attribute, but will this create two employees?  Will the database realize this is the same employee?  Thanks
0
 

Author Comment

by:John500
Comment Utility
pmteixeira,

In continuation of my last comment to you, here are the errors I am receiving when I try insert the same person in Company, Person, Employee and Administrator:

insert into Company values('Northern','111-11-1111');
one row inserted

*
insert into Person values('111-11-1111', 'John Doe', '412-486-2489', '1962-08-20
');
one row inserted

*
insert into Employee values('111-11-1111', 'John Doe', '412-486-2489', '1962-08-
20', 'Northern', 25000.00);
X23C00:integrity constraint violation: Constraint Name _UNIQUE_CONSTRAINT_ON_INS
ERT_To_barbacci_Person_

*
insert into Administrator values('111-11-1111', 'John Doe', '412-486-2489', '196
2-08-20', 'Northern', 25000.00, '5111');
X23C00:integrity constraint violation: Constraint Name _UNIQUE_CONSTRAINT_ON_INS
ERT_To_barbacci_Person_

0
 
LVL 1

Expert Comment

by:pmteixeira
Comment Utility
Your questions are exactly my questions (remember I've never used Illustra)...

I think you could do the following (after the insertions you've already done):

  select * from Employee where SSN = '333-33-3333';

and

  select * from Person where SSN = '333-33-3333';

If both return a row, your insert into Employee lets you get 'Person' information from either table (if the DBMS manages it explicitly - inserts in both tables -, or implicitly - inserts only in the second, I don't know).

Before I enter a 2nd phase, let me make a comment about your Database Schema and the associated conceptual model:
you say, an employee IS A person. I ask, how do you identify a person?
With SSN (the primary key of Person).
So, why do you identify an employee with SSN AND Branch_Name? Shouldn't it be only the SSN? You should use both if wanted the same person to be an employee in several different branches.

I also advise you to use numeric columns in the primary keys (the indexes are much quicker).

Now the 2nd point:

  try to insert a row in Administrator table with SSN = '333-33-3333';

but first you should have 'SSN' in the primary key of Administrator and not 'Name', shouldn't you?

If the insert goes OK, I think that it will duplicate registers. If it gives an error, I don't really know how to solve the problem using this kind of model. I would then change the model a bit! But first try yours!


Regards,

PMT
0
 
LVL 1

Expert Comment

by:pmteixeira
Comment Utility
Hello again,

sorry about some of the tests I asked you to do in my last comment, but I didn't reload this question, and missed your last comment.

Your tests show that when you insert in a "sub-table", the DBMS tries to insert a row in the "base-table" (the unique constraint violated, because already exists a Person with the same ID). Using this "under" clause, I can't figure out how to solve the problem!

Either exists a special (non-standard/extended SQL) statement in Illustra, that lets you insert rows in a "sub-table" that point to an existing row of a "base-table", instead of creating it; or the "under" clause has another kind of goal, and you should redesign your database schema without using it.

If you put the attribute(s) (with foreign-key) in a "sub-table" that point to the corresponding "base-table" primay-key, you don't have this kind of problems. You won't have duplicate data (like "Name"), because this belongs only to a "Person". You could insert a row in "Person" and later in "Employee", pointing to that person (each table inserts only its own rows).

To fetch your data, you must cross the tables in the queries, everytime you want to obtain simultaneously atttibutes that exist in one table (like "Name") or in the other (like "Branch_office").


Regards,

PMT
0
 

Author Comment

by:John500
Comment Utility
pmteixeira,

Thanks for all the comments thus far.  I was able to populate every table in the database taking your first advice about inserting in the referenced table before you insert in the referencing table.

I will also change the primary key of the employee to SSN alone if you think that is best.  I will also make the primary key in the Company table the "Branch_Name" key instead of what I had.  If you see anything else that doesn't look right let me know.  I don't consider myself to be an expert on primary & foreign keys.  This project is part of a database modeling lesson so...

After your next comments of whether anything looks fishy or not, I'll close out this question.  Thanks!

John
0
 
LVL 1

Expert Comment

by:pmteixeira
Comment Utility
Hi John,

as a last comment I will not make any explicit references to your schema, but remember some important points when designing any kind of schema.
Suppose we've designed the following example:

create table Person
(
   SSN              char(11) not null,
   Name            varchar(50) not null,
   Birthdate        date,
   CountryName varchar(30) not null,    -- used like 'Nationality'
         -- ... other columns
   primary key(SSN),
   foreign key(CountryName) references Country(Name)  
);

create table ImportantPerson
(
   SSN              char(11) not null,
   Remarks        varchar(80) not null,
         -- ... other columns
   primary key(SSN),
   foreign key(SSN) references Person(SSN)
);

create table Country
(
   Name             char(30) not null,
         -- ... other columns
   primary key(Name)
);

create table Company
(
   Name            char(30) not null,
         -- ... other columns
   primary key(Name),
);

create table Curriculum
(
   SSN              char(11) not null,
   CompName    varchar(30) not null,
   BeginDate      date not null,
   EndDate        date,
         -- ... other columns
   primary key(SSN, CompName, BeginDate),
   foreign key(SSN) references Person(SSN),
   foreign key(CompName) references Company(Name),
);


This would be a nice database CONCEPTUAL schema, where there are Persons and some of them are ImportantPersons. A person was born in a country. There are also Companies where the Persons work from a BeginDate to an EndDate (Curriculum).
It would a good starting model.

When you go to the "physical/implementation model", some points are usually taken into consideration:
  1 - Your Primary Keys shouldn't have columns that are long strings;
  2 - Your Primary Keys shouldn't have columns which you let your users modify (for example, if an user enters a mistaken SSN for a person, it will want to change it later. But  at that point, other tables - like Curriculum - may already reference that Person by its SSN and you might have some problems to change the value)
  3 - To solve the points above, you can create additional columns (usually numeric) that are used as primary keys (and referenced by the other tables).

After this, the model could be changed to:

create table Person
(
   Id                  decimal(9,0) not null,    -- or any datatype like 'long int'
   SSN              char(11) not null,
   Name            varchar(50) not null,
   Birthdate        date,
   CountryId       decimal(9,0) not null,    -- used like 'Nationality'
         -- ... other columns
   primary key(Id),
   foreign key(CountryId) references Country(Id)  
);

create table ImportantPerson
(
   Id                  decimal(9,0) not null,
   Remarks        varchar(80) not null,
         -- ... other columns
   primary key(Id),
   foreign key(Id) references Person(Id)
);

create table Country
(
   Id                   decimal(9,0) not null,
   Name             char(30) not null,
         -- ... other columns
   primary key(Id)
);

create table Company
(
   Id                  decimal(9,0) not null,
   Name            char(11) not null,
         -- ... other columns
   primary key(Id),
);

create table Curriculum
(
   PersonId         decimal(9,0) not null,
   CompanyId     decimal(9,0) not null,
   PartialKey      int not null,
   BeginDate      date not null,
   EndDate        date,
         -- ... other columns
   primary key(PersonId, CompanyId, PartialKey)
   foreign key(PersonId) references Person(Id),
   foreign key(CompanyId) references Company(Id)
);


And that would be it...

Hope this helps your knowledge about tables and keys...

PMT
0
 

Author Comment

by:John500
Comment Utility
Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

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…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 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

9 Experts available now in Live!

Get 1:1 Help Now