Create a single SQL script that creates the database described in the diagram in

Question

Create a single SQL script that creates the database described in the diagram in a SQL Server database. This means that you will be turning in an SQL script.

Table and column names should be exactly what the author shows in the diagram, except they should be in camel case because that is what is generally used in SQL Server. Create all the appropriate constraints. Assume that any column that current does not contain nulls will never contain nulls (you can't make this assumption in a real database). You do not need to create any check constraints.

Assume that database will be used for all semesters going forward, even though the data currently in the database is for a single semester. Also assume that section numbers can be reused each semester. There's currently a section "2714" for the "I-2008" semester, but there could also be a "2714" in "I-2009" and "I-2010".

Step 0 - Header

Include an appropriate header at the top of the script. This should include the name of the file, your name, due date, assignment name, and a two or three sentence description of the file.

Step 1 - Drop Objects

If any of the objects for this database already exist you must drop them first. Be sure to drop them in the correct order. Use the IF EXISTS command to prevent an error if the object does not exist already.

Step 2 - Create Tables

Create the tables and primary keys. You may create the primary keys when you create the tables, or do it as a separate step. Use Camel Case for both the table and column names (as this is the standard in SQL Server). Use the correct data type (from the ones we learned in class) for each column.

Step 3 - Create Foreign Key

Add the appropriate foreign keys to the database.

Step 4 - Insert Rows

Insert the rows from the diagram into the database. Be sure to insert the rows in the correct order.

Step 5 - Add some class

Change the database to store the class of each students. In the students table there should be Class column of type VARCHAR(2). Use an ALTER TABLEstatement do this. There should also be a Classes table with both a Class and ClassName column (of type VARCHAR(10)).

Here are the classes for the college:

Add the appropriate columns, tables, constraints, etc. to the to database. Insert the appropriate rows into the Classes table. Update the Students table to assign each student to a class (you can decided who is in which class).

Notes and Comments

Be sure to clearly mark each step in your script with a comment. For example:

Name your file in the form LoginYourNameLab04.sql, where Login is your login name, YourName is your first and last name.

I should be able to run you entire script repeatedly by pressing the F5 key. Be sure to test this.

You may want to create to create a quick ERD for the database before starting. You do not need to turn this in, but it may help you significantly.

Do not include a USE, CREATE DATABASE, or DROP DATABASE command in your script.

Class ClassName FR Freshman SO Sophomore JU Junior SR Senior MS Masters PH PhD

Explanation / Answer

Answer)

Table Creation and Data insertion script are as follows:

create table STUDENT(StudentID int primary key,StudentName varchar);

insert into STUDENT values(38214,'Letersky');

insert into STUDENT values(54907,'Alvater');

insert into STUDENT values(66334,'Aiken');

insert into STUDENT values(70542,'Marra');

create table COURSE(CourseID varchar primary key, CourseName varchar);

insert into COURSE VALUES ('ISM3113','SystAnalysis');

insert into COURSE VALUES ('ISM3112','Database');

insert into COURSE VALUES ('ISM4212','Syst Design');

insert into COURSE VALUES ('ISM4930','Networking');

create table QUALIFIED(FacultyID int,CourseID varchar FOREIGN KEY REFERENCES COURSE(CourseID),DateQualified varchar,primary key(FacultyID,CourseID));

insert into QUALIFIED values(2143,'ISM3112','9/2018');

insert into QUALIFIED values(2143,'ISM3113','9/2018');

insert into QUALIFIED values(3467,'ISM3112','9/2018');

insert into QUALIFIED values(3467,'ISM4212','9/2015');

insert into QUALIFIED values(7564,'ISM3113','9/2011');

insert into QUALIFIED values(2143,'ISM3112','9/2011');

create table FACULTY(FacultyID int primary key, FacultyName varchar);

insert into FACULTY values(2143,'Birkin');

insert into FACULTY values(3467,'Beendt');

insert into FACULTY values(7564,'Collins');

create table SECTION (SectionNo int, Semester varchar,CourseID varchar, primary key(SectionNo,Semester));

insert into SECTION values(2712,'I-2018','ISM3113');

insert into SECTION values(2713,'I-2018','ISM3113');

insert into SECTION values(2714,'I-2018','ISM4212');

insert into SECTION values(2715,'I-2018','ISM4930');

create table REGISTRATION(StudentID int FOREIGN KEY REFERENCES STUDENT(StudentID), SectionNo int, Semester varchar,primary key(StudentID,SectionNo));

insert into REGISTRATION VALUES (38214,2714,'I-2018');

insert into REGISTRATION VALUES (54907,2714,'I-2018');

insert into REGISTRATION VALUES (54907,2715,'I-2018');

insert into REGISTRATION VALUES (66334,2713,'I-2018');

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.