/* MS SQL Server uses Transact-SQL, which is Microsoft's enhanced version of standard SQL. To create the tables in this file, log in to your SQL Server Management Studio account and select your account name left pane. Open a new query window. */ /* Below are examples of creating tables with Transact-SQL and these includes some examples of various ways to define columns and to set CONSTRAINTs for the tables in this relational database. This simple database consists of a STUDENTS, TEAMS, EVAL_ITEMS, EVALUATIONS, and EVAL_ITEMS_SCORES. This database is used by an instructor in a systems analysis and design class to keep track of teams and projects, team members, and peer evaluations for team members. Each team has a full name, a team ID and may have a project assigned. Each student is assigned a team for a project though, initially, a student may not be assigned a team yet. Each student eventually has an evaluation done by each of his/her other team members. Each student eventually completes an evaluation for each of his/her team members. Each evaluation has a set of evaluation items and scores. Each evaluation item has an evaluation item description. */ /* This CREATE TABLE statement defines 3 columns in a TEAMS table and defines the PRIMARY KEY field for this table. Even though the team name is not the primary key field, a unique constraint has been assigned to this field. */ CREATE TABLE teams ( teamid VARCHAR(8) NOT NULL, team_name VARCHAR(30) NOT NULL, project VARCHAR(45), CONSTRAINT pk_teamid PRIMARY KEY (teamid), CONSTRAINT uniq_team UNIQUE (team_name) ); /* The following SQL code illustrates how to designate a FOREIGN KEY CONSTRAINT that states which field is related to a PRIMARY KEY field in another table. An entry in the std_teamID field must match a value in the related primary key field in the TEAMS table. */ CREATE TABLE students ( stdid VARCHAR(10) NOT NULL, stdfname VARCHAR(12) NOT NULL, stdlname VARCHAR(13) NOT NULL, stdmajor VARCHAR(6), std_teamID VARCHAR(8), CONSTRAINT pk_stdid PRIMARY KEY (stdid), CONSTRAINT fk_teamid FOREIGN KEY (std_teamid) REFERENCES teams ); /* The CREATE TABLE statement in this file are in a generic SQL format. These statements would execute in other DBMS platforms as well, such as Oracle. */ CREATE TABLE eval_items ( eval_item_ID VARCHAR(10) NOT NULL, eval_descrip VARCHAR(35) NOT NULL, CONSTRAINT pk_eval_items_id PRIMARY KEY (eval_item_id) ); CREATE TABLE evaluations ( eval_ID int NOT NULL, evaluatorID VARCHAR(10) NOT NULL, evaluateeID VARCHAR(10) NOT NULL, evalsemester VARCHAR(10), evalyear int, CONSTRAINT pk_eval PRIMARY KEY (eval_ID), CONSTRAINT fk_evaluatorID FOREIGN KEY (evaluatorID) REFERENCES students, CONSTRAINT fk_evaluateeID FOREIGN KEY (evaluateeID) REFERENCES students ); /* The EVAL_SCORE_ID column is defined as data type INT and IDENTITY which means a value for the item score ID will be generated by SQL Server whenever a row is inserted. The value will start at 1000 and increment by 1 each time a number is used. */ CREATE TABLE eval_items_scores ( eval_score_ID int IDENTITY (1000,1) NOT NULL, eval_ID int NOT NULL, eval_item_ID VARCHAR(10) NOT NULL, score int NOT NULL, CONSTRAINT pk_eval_item_score PRIMARY KEY (eval_score_id), CONSTRAINT fk_eval_id FOREIGN KEY (eval_id) REFERENCES evaluations, CONSTRAINT fk_eval_item_id FOREIGN KEY (eval_item_ID) REFERENCES eval_items )