log inhelp

 | 

  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • You already know Dokkio is an AI-powered assistant to organize & manage your digital files & messages. Very soon, Dokkio will support Outlook as well as One Drive. Check it out today!

View
 

Course Registration Transaction Example

Page history last edited by Dr. Ron Eaglin 15 years, 3 months ago

 Course Registration Transaction Example

 

 

 

 

 

 

Download Link 

 

 

 

Tables

 

 

CREATE TABLE [dbo].[Address](
        [AddressID] [int] IDENTITY(1,1) NOT NULL,
        [AddressText] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED 
(
        [AddressID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[ContactInformation](
        [PersonContactInformationID] [int] IDENTITY(1,1) NOT NULL,
        [PersonCurrentAddressID] [int] NULL,
        [PersonHomeAddressID] [int] NULL,
        [EmailIDText] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [MobileTelephoneNumberText] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [TelephoneNumberText] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_ContactInformation] PRIMARY KEY CLUSTERED 
(
        [PersonContactInformationID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ContactInformation]  WITH CHECK ADD  CONSTRAINT [FK_ContactInformation_Address] FOREIGN KEY([PersonCurrentAddressID])
REFERENCES [dbo].[Address] ([AddressID])
GO
ALTER TABLE [dbo].[ContactInformation] CHECK CONSTRAINT [FK_ContactInformation_Address]
GO
ALTER TABLE [dbo].[ContactInformation]  WITH CHECK ADD  CONSTRAINT [FK_ContactInformation_Address1] FOREIGN KEY([PersonHomeAddressID])
REFERENCES [dbo].[Address] ([AddressID])
GO
ALTER TABLE [dbo].[ContactInformation] CHECK CONSTRAINT [FK_ContactInformation_Address1]


CREATE TABLE [dbo].[Course](
        [CourseID] [int] IDENTITY(1,1) NOT NULL,
        [CoursePrefixText] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
        [CourseNumberText] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [CourseShortDescriptionText] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [CourseLongDescriptionText] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED 
(
        [CourseID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [dbo].[CourseOffering](
        [CourseOfferingID] [int] IDENTITY(1,1) NOT NULL,
        [InstructorID] [int] NULL,
        [CourseID] [int] NULL,
        [CourseOfferingSectionText] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [CourseOfferingSemesterID] [int] NULL,
        [CourseOfferingDaysOFWeekText] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [CourseOfferingStartTimeText] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [CourseOfferingEndTimeText] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [CourseLimit] [int] NULL,
 CONSTRAINT [PK_CourseOffering] PRIMARY KEY CLUSTERED 
(
        [CourseOfferingID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[CourseOffering]  WITH CHECK ADD  CONSTRAINT [FK_CourseOffering_Course] FOREIGN KEY([CourseID])
REFERENCES [dbo].[Course] ([CourseID])
GO
ALTER TABLE [dbo].[CourseOffering] CHECK CONSTRAINT [FK_CourseOffering_Course]
GO
ALTER TABLE [dbo].[CourseOffering]  WITH CHECK ADD  CONSTRAINT [FK_CourseOffering_CourseOffering] FOREIGN KEY([InstructorID])
REFERENCES [dbo].[Faculty] ([FacultyID])
GO
ALTER TABLE [dbo].[CourseOffering] CHECK CONSTRAINT [FK_CourseOffering_CourseOffering]
GO
ALTER TABLE [dbo].[CourseOffering]  WITH CHECK ADD  CONSTRAINT [FK_CourseOffering_Semester] FOREIGN KEY([CourseOfferingSemesterID])
REFERENCES [dbo].[Semester] ([SemesterID])
GO
ALTER TABLE [dbo].[CourseOffering] CHECK CONSTRAINT [FK_CourseOffering_Semester]




CREATE TABLE [dbo].[Faculty](
        [FacultyID] [int] IDENTITY(1,1) NOT NULL,
        [FacultyNameID] [int] NOT NULL,
        [FacultyContactInformationID] [int] NOT NULL,
        [FacultyType] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Faculty] PRIMARY KEY CLUSTERED 
(
        [FacultyID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Faculty]  WITH CHECK ADD  CONSTRAINT [FK_Faculty_ContactInformation] FOREIGN KEY([FacultyContactInformationID])
REFERENCES [dbo].[ContactInformation] ([PersonContactInformationID])
GO
ALTER TABLE [dbo].[Faculty] CHECK CONSTRAINT [FK_Faculty_ContactInformation]
GO
ALTER TABLE [dbo].[Faculty]  WITH CHECK ADD  CONSTRAINT [FK_Faculty_PersonName] FOREIGN KEY([FacultyNameID])
REFERENCES [dbo].[PersonName] ([NameID])
GO
ALTER TABLE [dbo].[Faculty] CHECK CONSTRAINT [FK_Faculty_PersonName]



CREATE TABLE [dbo].[PersonName](
        [NameID] [int] IDENTITY(1,1) NOT NULL,
        [PersonNamePrefixText] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [PersonGivenNameText] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [PersonMiddleNameText] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [PersonSurNameText] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
        [PersonNameSuffixText] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_PersonName] PRIMARY KEY CLUSTERED 
(
        [NameID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [dbo].[Semester](
        [SemesterID] [int] IDENTITY(1,1) NOT NULL,
        [SemesterStartDate] [datetime] NULL,
        [SemesterEndDate] [datetime] NULL,
        [SemesterDesignationText] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Semester] PRIMARY KEY CLUSTERED 
(
        [SemesterID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [dbo].[Student](
        [StudentID] [int] IDENTITY(1,1) NOT NULL,
        [StudentNameID] [int] NULL,
        [StudentContactInformationID] [int] NULL,
 CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED 
(
        [StudentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Student]  WITH CHECK ADD  CONSTRAINT [FK_Student_ContactInformation] FOREIGN KEY([StudentContactInformationID])
REFERENCES [dbo].[ContactInformation] ([PersonContactInformationID])
GO
ALTER TABLE [dbo].[Student] CHECK CONSTRAINT [FK_Student_ContactInformation]
GO
ALTER TABLE [dbo].[Student]  WITH CHECK ADD  CONSTRAINT [FK_Student_PersonName] FOREIGN KEY([StudentNameID])
REFERENCES [dbo].[PersonName] ([NameID])
GO
ALTER TABLE [dbo].[Student] CHECK CONSTRAINT [FK_Student_PersonName]



CREATE TABLE [dbo].[StudentCourseRegistration](
        [StudentRegistrationID] [int] IDENTITY(1,1) NOT NULL,
        [StudentID] [int] NOT NULL,
        [CourseOfferingID] [int] NOT NULL,
        [RegistrationDate] [datetime] NULL,
        [WithdrawnDate] [datetime] NULL,
 CONSTRAINT [PK_StudentCourseRegistration] PRIMARY KEY CLUSTERED 
(
        [StudentRegistrationID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[StudentCourseRegistration]  WITH CHECK ADD  CONSTRAINT [FK_StudentCourseRegistration_CourseOffering] FOREIGN KEY([CourseOfferingID])
REFERENCES [dbo].[CourseOffering] ([CourseOfferingID])
GO
ALTER TABLE [dbo].[StudentCourseRegistration] CHECK CONSTRAINT [FK_StudentCourseRegistration_CourseOffering]
GO
ALTER TABLE [dbo].[StudentCourseRegistration]  WITH CHECK ADD  CONSTRAINT [FK_StudentCourseRegistration_Student] FOREIGN KEY([StudentID])
REFERENCES [dbo].[Student] ([StudentID])
GO
ALTER TABLE [dbo].[StudentCourseRegistration] CHECK CONSTRAINT [FK_StudentCourseRegistration_Student]

 

 

 

 

Stored Procedures

 

CREATE PROCEDURE RegisterStudent (

  @StudentID int,

  @CourseOfferingID int

)

AS

 

DECLARE @EnrollmentCount INT

DECLARE @EnrollmentLimit INT

 

BEGIN

BEGIN TRANSACTION

  SELECT @EnrollmentCount = COUNT(*) FROM StudentCourseRegistration

  WHERE CourseOfferingID = @CourseOfferingID

 

  SELECT @EnrollmentLimit = CourseLimit FROM CourseOffering

  WHERE CourseOfferingID = @CourseOfferingID

 

  IF @EnrollmentLimit > @EnrollmentCount

   BEGIN

     INSERT INTO StudentCourseRegistration

      (StudentID, CourseOfferingID)

     VALUES

      (@StudentID, @CourseOfferingID)

   END

  COMMIT

END

Comments (0)

You don't have permission to comment on this page.

Printable version