University project management data service example

Brief description of the database

This database is used for managing projects in a university. The entities are project, department, student and instructor. Relationships between these entities are as follows.

• Each project may have multiple students.
• Each student can participate in multiple projects.
• Each project may have number of instructors.
• Each instructor may belong to multiple projects.
• Each project belongs to one department.
• A department can have multiple projects.
• Each student belongs to one department.
• Each department may have multiple students.

Databases tables

student

• name
• index - primary key
• dep_no - department number (foreign key to department.no)
• e-mail

department

• name
• no - primary key

project

• proj_no - primary key
• dep_no - department number (foreign key to department.no)
• name

instructor

• id - primary key
• name
• e-mail

student_project

• index - student index number (foreign key to student.index)
• proj_no - project number (foreign key to project.no)

both of these columns together is the primary key of this table.

project_instructor

• ints_id - instructor id (foreign key to instructor.inst_id)
• proj_no - project number (foreign key to project.no)

both of these columns taken together is the primary key of this table.

 
Lets say user provides the project number as the input and expects the following XML request.

XML resoponse expected<project> <id>10</id>
<students>
<student>
<name>Kamal</name>
<index>014254</index>
<e-mail>kamal@abc.com</e-mail>
</student>
</students>
</project>

DSLR query specification<data baseURI="xs:anyURI" name="StudentInfoService">
<config>
<property name="driver">org.apache.derby.jdbc.EmbeddedDriver</property>
<property name="protocol">jdbc:derby:../database/WSO2WSAS_DB</property>
<property name="user">wso2wsas</property>
<property name="password">wso2wsas</property>
</config>

<query id="studentInfo">
<param name="index" sqlType="INTEGER"/>
<sql>select name, index, e-mail from student where student.index=?</sql>
<result element="Students" rowName="student">
<element name="Name" column="name"/>
<element name="Index" column="index"/>
<element name="E-mail" column="email"/>
</result>
</query>

<operation name="getStudentByProjectId">
<call-query href="studentInfo" >
<with-param name="indexParam" part="index"/>
</call-query>
</operation>
</data>