Home > 2010 Fall DPS907 > Database for the web services programming assignment

Database for the web services programming assignment

November 24, 2010 Leave a comment Go to comments

Many students chose to do “Option 4” for their web service programming assignment. In this post, you will learn about the database upon which you will build your WCF WebHttp Service. If you didn’t choose “Option 4”, you will still find this topic very useful, as it will guide you in the design and execution of your own project.

The database, upon which you will build your WCF WebHttp Service, stores operational data for the School of Computer Studies. The professor’s intention is to deliver a solution that will meet a number of objectives:

  • The database will be used for the DPS907/WSA500 programming assignment, “Option 4”
  • It will also be used as the base for a WCF WebHttp Service that was authored by the professor, and is available for iOS applications (created in another course offering)
  • Finally, as a proof-of-concept, it will enable us to gather experience about offering a publicly-accessible web service, so that we can plan for the future

.

SQL Server and LINQ help information

Use the following links to help you understand the database, and/or to help you create your own design.

.

SQL Server resources

Working with Relationships
This page introduces relationships, and has links to a number of useful topics

Types of Table Relationships
Describes one-to-one, one-to-many, and many-to-many relationships

How to: Create Relationships Between Tables

.

LINQ resources

LINQ (Language-Integrated Query)
This page introduces LINQ, and has links to a number of useful topics

Introduction to LINQ
A brief introduction to LINQ

Getting Started with LINQ in C#
This page has links to a number of useful topics
We suggest that you use the left-side topic navigator to select the topic you’re interested in

Walkthrough: Writing Queries in C#
Very useful tutorial/walkthrough, which nicely introduces LINQ in C#

LINQ Query Expressions (C# Programming Guide)
Another very useful resource
We suggest that you use the left-side topic navigator to select the topic you’re interested in

.

LINQ to Entities resources

LINQ to Entities
This page introduces LINQ to Entities, and has links to a number of useful topics
These topics complement the “LINQ resources” topics, with entity-specific information
We suggest that you use the left-side topic navigator to select the topic you’re interested in

How to: Navigation Relationships Using Navigation Properties
This topic shows how to navigate relationships through navigation properties

.

The general design of the database

There are three entity “clusters” that can be seen in the collection of tables:

  1. Static entities for programs and courses
  2. Mostly static entities for employees (including faculty) and related information
  3. Entities that support the dynamic nature of “semester operations”, including course offerings (instances) in a semester, and their related data

In this database “version 1”, there are no views or stored procedures. They will probably be added in a following semester, so you don’t have to consider them for your programming assignment.

.

Create the database’s tables in your own warp-hosted database

We have packaged the table definitions, and some starter data, in a SQL Server script. It is available for you to download from the following link:

Assignment database, version 1

Before running the script, you should scan through it, so that you understand what it’s doing. If you have existing tables with the same name as the tables that the script will create, then you must rename your existing tables to avoid conflicts and problems.

To use the script, do the following:

  1. Run SQL Server Management Studio, and connect to your warp-hosted database
  2. Open the script (File > Open)
  3. Edit the database name in the script’s first line, and then execute the script

.

The database includes the following sample data:

Employees – all School of Computer Studies (SCS) employees, as shown on the web site, are included. All known Job Titles, and the important additional Job Duties, have been entered.

Programs – all SCS programs are included, however, only some of the data for each program has been entered.

Courses – a selection of SCS courses is included, however, only some of the data for each course has been entered.

Semesters – the current Fall 2010 semester has been added, and the following two semesters.

Rooms – a handful of rooms were entered (S2149, T2110, and T2109).

Course instances – only two (WSA500 and APD601 for the Fall 2010 semester) were added.

Course instance sessions – the sessions for these two courses were added.

Course events – only the course events for WSA500 were added.

Your professor suggests that you add additional data to the database. Use your own course enrollment information for the Fall 2010 semester as a model, and add any other data that you feel will ultimately help the quality of your web service.

.

Tables for programs and courses

The following diagram shows the (fairly static) tables for the program and course entities.

The ProgramCourse table is a “junction table”, which enables the many-to-many relationship between the program and course entities. (The “Type” column enables us to specify that, for example, IPC144 is a “Core” course in the CPD and CPA programs, but in contrast, it is a “Pro Option” course in the CTY program.)

.

Tables for employee-related data

The following diagram shows the (fairly static) tables for employee-related entities.

The EmployeeJobDuty is a “junction table”, to enable a many-to-many relationship.

.

Tables to support semester operations

The following diagram shows the tables that support semester operations.

.

The following will help you understand this cluster of tables:

While the Course table defines a School of Computer Studies “course” entity, the CourseInstance table is for a specific offering of that course in a semester. For example, IPC144 is a course, but a course instance defines the fact that it is offered in a specific semester. Additionally, a course instance includes a “section identifier”, for large-enrollment courses that have more than one section (n.b. the number of students in a section is approximately 35). Finally, it includes information about the course instance’s professor.

There are a couple of fairly static tables in this cluster: Room and Semester.

A course instance is related to two other tables, which map back to real-life entities:

  • Course instance sessions – for the days and times of the course’s lecture and lab sessions
  • Course events – for the notable events that include graded work (assignments, tests, etc.), and other events

.

Modifying the tables, and adding your own data

Feel free to modify the tables and their structure.

Additionally, some of the tables have very little data. Feel free to add your own. In fact, you MUST add more data to some of the tables, so that your web service can be used in a meaningful manner.

.

Creating your web service

As you can see from your inspection of the tables, there are ten (10) with data (and two (2) junction tables). When you create your Entity Framework data model, these ten tables will surface as entities.

Your inspection will suggest that you will have two web service URIs for each table – one to return a collection of all objects, and one to return a single object – for a base total of twenty (20) URIs.

In addition to these URIs, you will probably create another 10, 20, 30, or more URIs. Each one will expose data in a way that will be useful to consumers.

The design of your API is very important. You must understand your data, and the kind of things that consumers may want to do with the data. That will help you design resource URIs that deliver on your intentions.

.

A separate post will detail the design and operation of your professor’s version of a WCF WebHttp Service that exposes this database.

.

Summary

In this post, you were introduced to the database for Option 4 of the web services programming assignment. You learned about its structure, and how to create its tables (with some sample data) in your own warp-hosted database.

.


Advertisements
Categories: 2010 Fall DPS907
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: