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
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:
Class | ClassName |
---|---|
FR | Freshman |
SO | Sophomore |
JU | Junior |
SR | Senior |
MS | Masters |
PH | PhD |
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:
-------------------------- -- Step #0 --------------------------
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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started