Create a SQL Server Database, Tables and Stored Procedures that would satisfy the following
requirements below:
1. Create a table for “Person” records that have the following columns/fields.
a. Person_ID (Primary Key)
b. Last Name (Length: Maximum of 100 characters)
c. Given Name (Length: Maximum of 100 characters)
d. Middle Name (Length: Maximum of 100 characters)
2. Create a table for “Student” records that have the following columns/fields.
a. Student_ID (Primary Key)
b. Student Number (Length: Exactly 10 characters)
c. Year (Accepts values from 1-5)
d. Person_ID (Foreign Key)
e. Program_ID (Foreign Key)
3. Create a table for “Program” records that have the following columns/fields.
a. Program_ID (Primary Key)
b. Program Description (Length: Maximum of 200 characters)
4. Create the following Stored Procedures based from the requirements below:
a. Stored Procedure to “Add Person” record (last name, given name, middle name)
b. Stored Procedure to “Edit Person” record (last name, given name, middle name)
c. Stored Procedure to “Delete Person” record
d. Stored Procedure to “View Person” specific record only
e. Stored Procedure to “Add Student” record (student number, program, year) provided
that the “Person” record already exists
f. Stored Procedure to “Edit Student” record (student number, program, year)
g. Stored Procedure to “Delete Student” record
h. Stored Procedure to “View Student” specific record only
i. Stored Procedure to “Add Program” record (program description)
j. Stored Procedure to “Edit Program” record (program description)
k. Stored Procedure to “Delete Program” record
l. Stored Procedure to “View Program” specific record only
m. Stored Procedure to “View Student Detailed” specific record only with the following
format (last name, given name, middle name, student number, program, year)
n. Stored Procedure to “Delete Student Detailed” specific record (Person and Student
records)