Web services assignment option 4 – database info

Some students chose to do “Option 4” for their web services 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 Information and Communications Technology. 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 Web API 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 subjects
  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 of a subject) in a semester, and their related data

In this database “version 2”, there are no views or stored procedures. They may 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 2

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.

Subjects – 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).

Courses (which are subject instances) – only two (WSA500 and APD601 for the Fall 2010 semester) were added.

Course 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 current Fall 2011 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 ProgramSubject table is a “junction table”, which enables the many-to-many relationship between the program and subject 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 Subject table defines a School of Computer Studies “subject” entity, the Course table is for a specific offering of that subject in a semester. For example, IPC144 is a subject, but a course defines the fact that it is offered in a specific semester. Additionally, a course 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’s professor.

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

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

  • Course 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.

The easiest way to do this is to create an ASP.NET Dynamic Data (for entities) web site. The template will include pages that let you easily maintain the data.

For bulk data import, you can use DTSWizard.exe, on any computer that has SQL Server Management Studio installed. This technique enables you to, for example, build data in an Excel worksheet, and then import it into a database table.

.

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 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 Web API 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
  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: