Appendix 1: The State University Application Architecture

Article
06/14/2014

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Acey J. Bunch and Mark Roberts
Microsoft Corporation

February 2000

Applies To: Microsoft® Access 2000

Summary: This appendix to Developing Client/Server Solutions with Access 2000 Projects, an online book that explains in detail how to create and work with Microsoft Access project (.adp) files, details the various database objects that make up the State University sample application and provides a listing of all the sample files included with this online book. (6 printed pages)

Tables

This section details the tables of the State University sample application.

Courses

Column	Data type	Constraint	Description
CourseID	int (identity(100,10))	PRIMARY KEY	The ID for each course.
Name	varchar(255)	NOT NULL	The course name.
Description	varchar(255)	NOT NULL	The course description.
InstructorID	integer	NULLS ALLOWED	Foreign key to the Instructors table.
Quarter	varchar(10)	NOT NULL	The course quarter (fall, winter, spring, or summer).
Year	varchar(4)	NOT NULL	The starting year the course is offered in.
StartDate	datetime	NOT NULL	The starting date of the course.
EndDate	datetime	NOT NULL	The ending date of the course.
StartTime	datetime	NOT NULL	The starting time of the course.
EndTime	datetime	NOT NULL	The ending time of the course.

Enrollment

Column	Data type	Constraint	Description
StudentID	integer	PRIMARY KEY	Part of the concatenated primary key, relates to Students table.
CourseID	integer	PRIMARY KEY	Part of the concatenated primary key, relates to Courses table.

Instructors

Column	Data type	Constraint	Description
InstructorID	Int (identity(1,1))	PRIMARY KEY	The ID for each instructor.
FirstName	varchar(25)	NOT NULL	The first name.
MiddleName	varchar(25)	NULLS ALLOWED	The middle name or initial.
LastName	varchar(25)	NOT NULL	The last name.
Address	varchar(255)	NOT NULL	The street address.
City	varchar(50)	NOT NULL	The city.
State	varchar(2)	NOT NULL	The state.
Zip	varchar(10)	NOT NULL	The ZIP Code.
HomePhone	phone (varchar)	NOT NULL	The home phone number.
Email	varchar(100)	NULLS ALLOWED	The e-mail address.

Students

Column	Data type	Constraint	Description
StudentID	int (identity(1,1))	PRIMARY KEY	The ID for each student.
FirstName	varchar(25)	NOT NULL	The first name.
MiddleName	varchar(25)	NULLS ALLOWED	The middle name or initial.
LastName	varchar(25)	NOT NULL	The last name.
Address	varchar(255)	NOT NULL	The street address.
City	varchar(50)	NOT NULL	The city.
State	varchar(2)	NOT NULL	The state.
Zip	varchar(10)	NOT NULL	The ZIP Code.
HomePhone	phone (varchar)	NOT NULL	The home phone number.
WorkPhone	phone (varchar)	NULLS ALLOWED	The work phone number.
Email	varchar(100)	NULLS ALLOWED	The e-mail address.

User-Defined Data Types

This section details the user-defined data types of the State University sample application.

Name	Data type	Constraint	Description
Phone	varchar(14)	NOT NULL	Used for all columns that store phone numbers.

Relationships

This section details the table relationships of the State University sample application.

Relationship	Column	Description
FK_Courses_Instructors	InstructorID	Instructors table related to Courses table.
FK_Enrollment_Courses	CourseID	Courses table related to Enrollment table.
FK_Enrollment_Students	StudentID	Students table related to Enrollment table.

Indexes

This section details the table indexes of the State University sample application.

Name	Table	Columns	Description
IX_Students_HomePhone	Students	HomePhone	Non-clustered index.
PK_Courses	Courses	CourseID	The primary key.
PK_Enrollment	Enrollment	StudentID & CourseID	The primary key.
PK_Instructors	Instructors	InstructorID	The primary key.
PK_Students	Students	StudentID	The primary key.

CHECK Constraints

This section details the CHECK constraints of the State University sample application.

Constraint	Table	Column	Expression
CK_Courses_Quarter	Courses	Quarter	`([Quarter] = 'SUMMER' OR [Quarter] = 'SPRING' OR [Quarter] = 'WINTER' OR [Quarter] = 'FALL')`
CK_Instructors_ HomePhone	Instructors	HomePhone	`(([HomePhone] LIKE '([0- 9][0-9][0-9]) [0-9][0-9][0- 9]-[0-9][0-9][0-9][0-9]'))`
CK_Instructors_Zip	Instructors	Zip	`(([Zip] LIKE '[0-9][0-9][0- 9][0-9][0-9]-[0-9][0-9][0- 9][0-9]'))`
CK_Students_ HomePhone	Students	HomePhone	`(([HomePhone] LIKE '([0- 9][0-9][0-9]) [0-9][0-9][0- 9]-[0-9][0-9][0-9][0-9]'))`
CK_Students_ WorkPhone	Students	WorkPhone	`(([WorkPhone] LIKE '([0- 9][0-9][0-9]) [0-9][0-9][0- 9]-[0-9][0-9][0-9][0-9]'))`
CK_Students_Zip	Students	Zip	`(([Zip] LIKE '[0-9][0-9][0- 9][0-9][0-9]-[0-9][0-9][0- 9][0-9]'))`

Triggers

Trigger	Description
trgEnrollment_Insert_Student	Enrolls the student in a given course. Enforces that students cannot enroll in more then three courses per quarter.
trgStudent_Delete_Enrollment	Deletes related enrollment records for the student being deleted.

Stored Procedures

Name	Description
CourseCatalog	Retrieves data for the Course Catalog report.
CourseEnrollment	Selects all of the courses that a student is enrolled in using the StudentID as a parameter.
CourseWithInstructorInfo	Retrieves all courses along with their instructors.
CreatePhoneDataType	Creates the user defined phone data type.
GetInstructor	Retrieves an instructor record using the InstructorID as a parameter.

Views

View	Description
vwCourses	Used to work with data from the Courses table.

Database Diagrams

This section details the database diagrams of the State University sample application.

Diagram	Description
StateU_Diagram	The primary database diagram, contains all tables and relationships.

Forms

Form	Description
Courses	Used add/modify/delete courses data.
CoursesWithInstructorInfo	Used to demonstrate UniqueTable and ResyncCommand properties.
Customers (Recordset Property)	Used to demonstrate MSDataShape provider with the NorthwindCS database.
Instructors	Used to add/modify/delete instructors data.
Instructors (SQL)	Used to demonstrate using a SQL statement as a form's record source.
Instructors (Stored Procedure)	Used to demonstrate using a stored procedure as a form's record source.
Main	The main entry point to the application.
Reports	Used to print or preview reports.
Students	Used add/modify/delete students data.
Template	Used to create new State University forms.

Reports

Report	Description
Course Catalog	A list of available courses for a given year.
Student Transcript	A list of courses the student is enrolled in.

List of Files

File name	Description
Courses.txt	Text file that contains the sample Courses data.
Instructors.xls	Excel file that contains the sample Instructors data.
StateU.adp	The Access project file.
StateU.bmp	The application splash screen.
StateU.mdf	The SQL Server™/MSDE database file.
Students.mdb	Microsoft® Access database that contains the sample Student data.
SUBlend.gif	The StateU background graphic.
SUIcon.ico	The application icon.
SULogo.gif	The StateU logo.
Template.mdb	Contains the Template form used to build the State University forms.

--------------------------------------------

The names of companies, products, people, characters, and/or data mentioned herein are fictitious and are in no way intended to represent any real individual, company, product, or event, unless otherwise noted.