Cart
[Home] [By Thread] [By Date] [Recent Entries]
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@r...] Sent: Tuesday, 22 March 2005 7:30 AM To: Marco Mastrocinque Cc: xml-dev@l... Subject: Re: 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>
|
