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.

  • Whenever you search in PBworks or on the Web, Dokkio Sidebar (from the makers of PBworks) will run the same search in your Drive, Dropbox, OneDrive, Gmail, Slack, and browsed web pages. Now you can find what you're looking for wherever it lives. Try Dokkio Sidebar for free.

View
 

Spring 2012 Assignment 4 - Instructor Solution

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

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.

Comments (0)

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

Printable version