Administering Windows SQL Server $2014-$ Assignment #$2$

Creating your own relational database

Goal:

Create a relational database that maintains a list of College Programs and students.

There should be $($at least$)$ two tables.

$1.$ One for students

$2.$ One for school programs

$3.**$if you feel there needs to be more tables, please explain your design

Requirements:

$1.$ For each student, we need a student ID$,$ Social insurance number, firstname, lastname,

email address, phone number, and start date

a$.$ Student ID & Social Insurance numbers, should be unique

b$.$ Social insurance number format must be: NNN$-$NNN$-$NNN: Example: $123-456-789$

$2.$ For each program, we need the program code, program name, program contact email

address, program contact name, and program start$-$date

a$.$ A program should be unique by name and start$-$date

$3.$ A student cannot exist without being part of a program

$4.$ A student can only belong to one program at a time.

$5.$ There should be at least $10$ programs

$6.$ There should be at least $20$ students

$7.$ Create a stored procedure for adding students

$8.$ Create a stored procedure for adding programs

$9.$ Create an index on the students' names and email address

$10.$ Create an index on the program name, contact name, and program email

$11.$ Create a view that leverages these indexed columns to display a students and their

respective programs

a$.$ not show social insurance number in this view

$12.$ Populate an initial list of programs using a script

$13.$ Populate an initial list of students using a script

Deliverables

$1.$ A full backup of the database

$2.$ A copy of the scripts used to insert the initial students & program data found in requirements

$(12$ &$13)$

$3.$ Instructions on how to use your stored procedures