1. Using the database design given below, create the database RegistrationSystem and create the tables as listed below. Be sure to also create the primary and foreign keys as noted in the database.
OK - let's start with the tables
USE RegistrationDatabase CREATE TABLE Students ( id INT NOT NULL IDENTITY(1,1) , LastNameText VARCHAR(200) , FirstNameText VARCHAR(200) , CONSTRAINT pk_Students PRIMARY KEY (id) ) GO CREATE TABLE Courses ( id INT NOT NULL IDENTITY(1,1) , Prefix VARCHAR(3) , Number VARCHAR(4) , short_description VARCHAR(25) , long_description VARCHAR(500) , [hours] INT NOT NULL , CONSTRAINT pk_Courses PRIMARY KEY (id) ) GO CREATE TABLE Semesters ( id INT NOT NULL IDENTITY(1,1) , term_identifier VARCHAR(2) , [Year] VARCHAR(4) , [start_date] Datetime , [end_date] Datetime , CONSTRAINT pk_Semesters PRIMARY KEY (id) , CONSTRAINT chk_term CHECK (term_identifier IN ('FA', 'SP', 'SU')) ) GO CREATE TABLE CourseOffering ( id INT NOT NULL IDENTITY(1,1) , SemesterID INT NOT NULL , CourseID INT NOT NULL , CONSTRAINT pk_CourseOffering PRIMARY KEY (id) , CONSTRAINT fk_CourseOffering_Semester FOREIGN KEY (SemesterID) REFERENCES Semesters (id), CONSTRAINT fk_CourseOffering_Course FOREIGN KEY (CourseID) REFERENCES Courses(id) ) GO CREATE TABLE StudentEnrollment ( id INT NOT NULL IDENTITY(1,1) , StudentID INT NOT NULL , CourseOfferingID INT NOT NULL , CONSTRAINT pk_StudentEnrollment PRIMARY KEY (id) , CONSTRAINT fk_StudentEnrollment_Student FOREIGN KEY (StudentID) REFERENCES Students (id), CONSTRAINT fk_StudentEnrollment_CourseOffering FOREIGN KEY (CourseOfferingID) REFERENCES CourseOffering(id) ) GO |
2. Create a series of stored procedures for your database with the proper inputs
sp_addStudent - inputs: First Name, Last Name
USE RegistrationDatabase GO CREATE PROCEDURE SP_addStudent @FirstName VARCHAR(200), @LastName VARCHAR(200) AS INSERT INTO Students (LastNameText, FirstNameText) VALUES (@LastName, @FirstName) GO |
sp_addCourse - inputs: Course Prefix, Course Number, Short Description, Long Description, hours
USE RegistrationDatabase GO CREATE PROCEDURE SP_addCourse @CoursePrefix VARCHAR(3), @CourseNumber VARCHAR(4), @ShortDescription VARCHAR(25), @LongDescription VARCHAR(500), @Hours INT AS INSERT INTO [RegistrationDatabase].[dbo].[Courses] ([Prefix] ,[Number] ,[short_description] ,[long_description] ,[hours]) VALUES ( @CoursePrefix, @CourseNumber, @ShortDescription, @LongDescription, @Hours) GO |
sp_addSemester - inputs: Term Identifier, Year, Start Date, End Date
USE RegistrationDatabase GO CREATE PROCEDURE SP_addSemester @TermIdentifier VARCHAR(2), @Year VARCHAR(4), @StartDate DATETIME, @EndDate DATETIME AS INSERT INTO [RegistrationDatabase].[dbo].[Semesters] ([term_identifier] ,[Year] ,[start_date] ,[end_date]) VALUES ( @TermIdentifier, @Year, @StartDate, @EndDate) GO |
sp_addCourseOffering - inputs: semesterID, CourseID (note that these are keys and must match id's of entered course and semesters)
USE RegistrationDatabase GO CREATE PROCEDURE SP_addCourseOffering @SemesterID INT, @CourseID INT AS INSERT INTO [RegistrationDatabase].[dbo].[CourseOffering] ([SemesterID] ,[CourseID]) VALUES ( @SemesterID, @CourseID) GO |
These stored procedures will require no inputs - they simply give a list of the entries in the table
sp_viewStudents
sp_viewCourses
sp_viewSemesters
The first 3 procedures here simply use a SELECT * FROM tableName in the procedure. I put a little more into third one, since the ID's are a little cryptic. |
sp_viewCourseOfferings
USE RegistrationDatabase GO CREATE PROCEDURE SP_viewCourseOffering AS SELECT s.[term_identifier] + ' ' + s.[Year] AS 'Semester', c.Prefix + c.Number AS 'Course' FROM CourseOffering co, Semesters s, Courses c WHERE co.CourseID = c.id AND co.SemesterID = s.id GO |
Inserting data is now simply a matter of executing the stored procedures.
USE [RegistrationDatabase] GO EXEC [dbo].[SP_addStudent]'Sheldon','Cooper' EXEC [dbo].[SP_addStudent]'Fred','Flintstone' EXEC [dbo].[SP_addStudent]'Homer','Simpson' EXEC [dbo].[SP_addStudent]'Sherlock','Holmes' EXEC [dbo].[SP_addStudent]'Walter','Bishop' EXEC [dbo].[SP_addCourse]'COP','4709', 'Applied Database II', 'Focus on programmability of databases', 3 EXEC [dbo].[SP_addSemester]'SP','2012', '1/17/2012', '5/11/2012' EXEC [dbo].[SP_addCourseOffering] 1,1 |
Note: Do not lose this database or procedures - you will use them in a later assignment.