USE Lab3and4;

-- DDL for Lab3; CS327E; Spring 2002

-- Set the default database. I have called it Lab3and4. In your case, it could

-- be dbuser10, for example.

USE Lab3and4;

-- These are cleanup statements, and assume that all tables and RI are created -- from scratch. Make sure you use these statements with care.

ALTER TABLE STUDSCORE DROP CONSTRAINT FK_ExamID;

ALTER TABLE STUDSCORE DROP CONSTRAINT FK_Last4SSN;

ALTER TABLE PROJECTGROUP DROP CONSTRAINT FK_SQLServerGroupID;

ALTER TABLE STUDENT DROP CONSTRAINT FK_SQLServerUserID;

ALTER TABLE STUDENT DROP CONSTRAINT FK_GroupID;

ALTER TABLE STUDENT DROP CONSTRAINT FK_MajorID;

DROP TABLE SQLSERVERACCT;

DROP TABLE PROJECTGROUP;

DROP TABLE STUDSCORE;

DROP TABLE EXAM;

DROP TABLE STUDMAJOR;

DROP TABLE STUDENT;

-- Assume that the EXAMDATA and STUDENTDATA tables already exist and

-- are populated.

-- Create the STUDENT table

-- Have used INTEGER for SSN, since in future, there might

-- be 2 students with same last 4 digits, and we may need to expand.

-- Instead of using fixed-length char, I use variable length

-- so disk space is not wasted. Also, a standard size of 40

-- chars suffices for my real world.

-- MajorID is SMALLINT as I don’t anticipate many Majors.

-- As a general note, I create PK only (and not FK) with the

-- CREATE TABLE statements, and then ADD FK with ALTER TABLE

-- statements. This way, I don’t have to remember which table

-- needs the FK first etc. It’s a matter of choice.

CREATE TABLE STUDENT

(

Last4SSN INTEGER PRIMARY KEY,

LastName VARCHAR (40) NOT NULL,

FirstName VARCHAR (40) NOT NULL,

Email VARCHAR (255) NULL,

MajorID SMALLINT,

GroupID VARCHAR (40),

SQLServerUserID VARCHAR (40)

);

-- Create the STUDMAJOR table

CREATE TABLE STUDMAJOR

(

MajorID SMALLINT PRIMARY KEY,

MajorDesc VARCHAR (40)

);

-- Create the EXAM table

-- Note the default values

CREATE TABLE EXAM

(

ExamID VARCHAR (40) PRIMARY KEY,

ExamDesc VARCHAR (255) NULL,

ExamMaxScore DECIMAL (8,2) DEFAULT 100.00 NOT NULL,

ExamExtraCredits DECIMAL (8,2) DEFAULT 0.00 NOT NULL,

ExamPcntOfTotal DECIMAL (8,2) NOT NULL

);

-- Create the STUDSCORE table

-- Note the syntax for composite PK

CREATE TABLE STUDSCORE

(

Last4SSN INTEGER NOT NULL,

ExamID VARCHAR (40) NOT NULL,

ExamScore DECIMAL (8,2) DEFAULT 0.00 NOT NULL,

PRIMARY KEY (Last4SSN, ExamID)

);

-- Create the PROJECTGROUP table

-- Note the data type for ProjectDesc

CREATE TABLE PROJECTGROUP

(

GroupID VARCHAR (40) PRIMARY KEY,

GroupName VARCHAR (40) NULL,

ProjectDesc VARCHAR (1000) NULL,

SQLServerGroupID VARCHAR (40)

);

-- Create the SQLSERVERACCT table

-- Note the check constraint.

CREATE TABLE SQLSERVERACCT

(

SQLServerID VARCHAR (40) PRIMARY KEY,

SQLServerPswd VARCHAR (40) CHECK

(

(SQLServerPswd IS NULL)

((LEN (SQLServerPswd) <= 8) AND ((ASCII (SQLServerPswd) BETWEEN 65 and 90) OR (ASCII (SQLServerPswd) BETWEEN 97 and 122)))

)

);

-- ADD all the FK

ALTER TABLE STUDENT ADD CONSTRAINT FK_MajorID FOREIGN KEY (MajorID) REFERENCES STUDMAJOR (MajorID)

ON DELETE NO ACTION

ON UPDATE CASCADE;

ALTER TABLE STUDENT ADD CONSTRAINT FK_GroupID FOREIGN KEY (GroupID) REFERENCES PROJECTGROUP (GroupID)

ON DELETE NO ACTION

ON UPDATE CASCADE;

ALTER TABLE STUDENT ADD CONSTRAINT FK_SQLServerUserID FOREIGN KEY (SQLServerUserID) REFERENCES SQLSERVERACCT (SQLServerID)

ON DELETE NO ACTION

ON UPDATE NO ACTION;

ALTER TABLE PROJECTGROUP ADD CONSTRAINT FK_SQLServerGroupID FOREIGN KEY (SQLServerGroupID) REFERENCES SQLSERVERACCT (SQLServerID)

ON DELETE NO ACTION

ON UPDATE NO ACTION;

ALTER TABLE STUDSCORE ADD CONSTRAINT FK_Last4SSN FOREIGN KEY (Last4SSN) REFERENCES STUDENT (Last4SSN)

ON DELETE CASCADE

ON UPDATE CASCADE;

ALTER TABLE STUDSCORE ADD CONSTRAINT FK_ExamID FOREIGN KEY (ExamID) REFERENCES EXAM (ExamID)

ON DELETE CASCADE

ON UPDATE CASCADE;

-- Populate all the tables

-- Note the use of DISTINCT

INSERT INTO STUDMAJOR (MajorID, MajorDesc)

SELECT DISTINCT MajorID, MajorDesc

FROM STUDENTDATA;

INSERT INTO SQLSERVERACCT (SQLServerID)

SELECT DISTINCT SQLServerUserID

FROM STUDENTDATA;

INSERT INTO SQLSERVERACCT (SQLServerID)

SELECT DISTINCT SQLServerGroupID

FROM STUDENTDATA;

UPDATE SQLSERVERACCT

SET SQLServerPswd= SQLServerID;

INSERT INTO PROJECTGROUP (GroupID, GroupName, ProjectDesc, SQLServerGroupID)

SELECT DISTINCT GroupID, GroupName, ProjectDesc, SQLServerGroupID

FROM STUDENTDATA;

INSERT INTO STUDENT (Last4SSN, LastName, FirstName, Email, MajorID, GroupID, SQLServerUserID)

SELECT DISTINCT Last4SSN, LastName, FirstName, Email, MajorID, GroupID, SQLServerUserID

FROM STUDENTDATA;

INSERT INTO EXAM (ExamID, ExamDesc, ExamMaxScore, ExamExtraCredits, ExamPcntOfTotal)

SELECT DISTINCT ExamID, ExamDesc, ExamMaxScore, ExamExtraCredits, ExamPcntOfTotal

FROM EXAMDATA;