Option #2: SQL Server Deployment Congratulations! Recently, you responded to a R
ID: 3739805 • Letter: O
Question
Option #2: SQL Server Deployment
Congratulations! Recently, you responded to a Request for Proposal (RFP), and you were awarded the contract to deploy, configure, and manage the SQL Server for the CSU Global Student Management System.
You are required to perform the following steps:
Install SQL Server 2012/2014, using unattended method to standardize installs.
Configure the TCP to run on port TCP 4459.
Create a database named CSUGlobalEdu with following settings:
Data file initial size: 100MB
Log file initial size: 50MB
Set to Autogrow
No limit on maximum file size.
Create following tables using scripts:
1. Class
Name
Data Type
Constraint
Course Number
Int
Primary Key
Course Name
Nvarchar (30)
Not null
Time of the Day
Time
Not null
Room
Char (10)
Not null
Room Capacity
Int
Not null
Course Description
Nvarchar (3000)
Not null
Credit Hours
Int
Not Null
2. Instructor
Name
Data Type
Constraint
Address
Nvarchar (100)
Not Null
InstructorID
Int
Primary Key
Phone Number
Nvarchar(10)
Not null
Office
Int
Not null
Office Hours
Nvarchar (30)
Not null
Department
Nvarchar (15)
Not null
Department ID
Int
Not null
First Name
Nvarchar (30)
Not null
Last Name
Nvarchar (30)
Not null
3. Student
Name
Data Type
Constraint
Address
Nvarchar (100
Not Null
StudentID
Int
Primary Key
Phone Number
Nvarchar(10)
Not null
Major
Char (10)
Not null
GPA
Decimal (3,2)
Not null
First Name
Nvarchar (30)
Not null
Last Name
Nvarchar (30)
Not null
Develop scripts to INSERT data into Class, Instructor, and Student tables. Load the tables with sample data with at least 10 rows.
Construct and execute queries that match the following criteria:
List all classes sorted by course number followed by course name.
List all instructors by last name followed by first name.
List all instructors by last name, first name, InstructorID.
List all students by last name followed by first name.
List all students by last name, first name, GPA, Major.
Generate a certificate for the database.
Develop a SQL query to generate a new symmetric key for encrypting data.
Develop a SQL query to alter the Student table and a new column names StudentDataEncrypt with data type varbinary(128).
Create a backup plan with following criteria:
The backups should occur on Sundays starting at 9:00 AM.
CSU Global Campus cannot afford to risk data loss of more than one hour.
Name
Data Type
Constraint
Course Number
Int
Primary Key
Course Name
Nvarchar (30)
Not null
Time of the Day
Time
Not null
Room
Char (10)
Not null
Room Capacity
Int
Not null
Course Description
Nvarchar (3000)
Not null
Credit Hours
Int
Not Null
Explanation / Answer
Solution:
The first four subparts have been answered as per Chegg guidelines, please repost others.
List all classes sorted by course number followed by course name.
Query:
SELECT Course Number, Course Name FROM Class ORDER BY Course Number, Course Name
List all instructors by last name followed by first name.
SELECT Last Name, First Name FROM Instructor ORDER BY Last Name, First Name
List all instructors by last name, first name, InstructorID.
SELECT First Name, Last Name, InstructorID FROM Instructor ORDER BY First Name, Last Name, InstructorID
List all students by last name followed by first name.
SELECT Last Name, First Name FROM Student ORDER BY Last Name, First Name
I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.