Creating a XML Schema for an existing database

["Marco Mastrocinque" <mmfive@netspace.net.au>]

Here is the guts of the assignment. Please note his gone on a conference
overseas - read not available. I removed parts that are not important.

Thanks Marco.
 
A simple database for a company is described below:
1. The database is part of a company information system, and contains four
tables; Division
describing the divisions of the company, Project describing the projects
within the
divisions, Employee containing information about employees within the
divisions, and
Assign describing employees' involvement in projects. The schema for each of
the tables
is shown below, with the primary key attributes underlined, and some more
information
about each table.
Division (DID, DNAME, LOCATION)
The company has several divisions identified by DID.
Each division has a name (DNAME) and a location
(LOCATION)
Project (PID, PNAME, BUDGET, DID)
Each project has an identifier PID, a name (PNAME), a
budget (BUDGET) and belongs to a division which is in the
Division table.
Employee (EID, ENAME, OFFICE, BIRTHDATE, SALARY, DID)
Each employee has an identifier EID, a name (ENAME), an
office room (OFFICE), a birth date (BIRTHDATE), a salary
(SALARY) and belongs to a division which is in the Division
table.
Assign (PID, EID, HOURS)
Each employee may be assigned to one or more projects
that belong to the same division as the employee. Each
project has one or more employees. The number of hours is
recorded.




**** This is the referential part ******* 


3. In addition to the primary keys underlined in 1, the database should
implement these
integrity constraints:
. DNAME in Division is an alternate key (i.e. DNAME also uniquely identify
the
division).
. BUDGET in Project must always be specified. The value is always positive
but
less than 10 million
. All non-null values of DID in Project (foreign key) must match a DID in
Division.
. All values of DID in Employee (foreign key) must match a DID in Division.
. All values of PID in Assign (foreign key) must match a PID in Project.
. All values of EID in Assign (foreign key) must match an EID in Employee.
. Ensure that the value of SALARY in Employee and HOURS in Assign do not
take
negative values.
The main tasks in this assignment are,
1. Map the above relational database schema into a proper schema in XML
Schema. The
schema must have a proper tree (nested) structure. No redundant information
shall be
introduced in the XML documents that comply with the schema. The schema must
also capture all the requirements given above.


-----Original Message-----
From: Ronald Bourret [mailto:rpbourret@rpbourret.com] 
Sent: Tuesday, 22 March 2005 7:30 AM
To: Marco Mastrocinque
Cc: xml-dev@lists.xml.org
Subject: Re: [xml-dev] Creating a XML Schema for an existing database

Maintaining primary key / foreign key relationships and enforcing 
referential integrity are slightly different things.

Primary key / foreign key relationships are usually maintained in XML 
documents by nesting or by explicit references. For example, suppose I 
have the following tables:

    Master (MasterID, ...)
    Detail (MasterID, DetailID, ...)

You can show this relationship in XML through nesting:

    <Master MasterID="...">
       ...
       <Detail DetailID="...">
          ...
       </Detail>
       <Detail DetailID="...">
          ...
       </Detail>
    </Master>

or through explicit references:

    <Document>
       <Master MasterID="...">
          ...
       </Master>
       ...
       <Detail MasterIDRef="..." DetailID="...">
          ...
       </Detail>
       ...
    </Document>

Note that in the first case, the MasterID is listed only once. In the 
second case, it is listed for each detail record.

As to referential integrity, the nesting structure essentially enforces 
it, but the reference structure does not. That is, it is perfectly legal 
for a MasterIDRef attribute to have a value not listed in any MasterID 
attribute. Neither structure requires MasterID or DetailID attributes to 
be unique.

I suspect your instructor wants the relationships to be maintained, 
rather than integrity to be enforced.

-- Ron

Marco Mastrocinque wrote:
> Hi All,
>        Thanks for your help. It's for an assignment I'm doing. The
lecturer
> states that I have to maintain referential integrity i.e. the Primary Key
> and Foreign Key relationships must be maintained in the resultant XML
file.


-----------------------------------------------------------------
The xml-dev list is sponsored by XML.org <http://www.xml.org>, an
initiative of OASIS <http://www.oasis-open.org>

The list archives are at http://lists.xml.org/archives/xml-dev/

To subscribe or unsubscribe from this list use the subscription
manager: <http://www.oasis-open.org/mlmanage/index.php>