$1$ of $2$

Problem $1$

Create a MyScript.sql script for SQL Server to create a database named "StudentProg",

containing two tables described below:

table "Programs" with a column "ProgId" INT NOT NULL and

a column "Name" VARCHAR $(50)$ NOT NULL;

table "Students" with a column "StudId" INT NOT NULL,

a column "Name" VARCHAR $(50)$ NOT NULL,

a colum "YearEnrolment" INT NOT NULL

a column "ProgId" INT NOT NULL;

The primary key of the "Programs" table is "ProgId" and the primary key of the

"Students" table is "StudId". In addition, the attribute "ProgId" in the "Students" table is a

foreign key that references the attribute "ProgId" of the "Programs" table. This foreign

key must not allow us to delete a program if there are students enrolled to that program.

However, updates in Programs table $($ProgId$)$ must be propagated to the Student table.

Your MyScript.sql must also insert the following data $($at a minimum$)$:

Programs $(1,$ 'Programming'$)$;

Programs $(2,$ 'Fashion'$)$;

Programs $(3,$ 'Management'$)$;

Programs $(4,$ 'Tourism'$)$;

Students $(1,$ 'Mary', $2022,3)$;

Students $(3,$ 'John', $2021,1)$;

Students $(7,$ 'Brian', $2021,2)$;

Students $(14,$ 'Anne', $2020,4)$;

Students $(32,$ 'James', $2020,1)$;

Run your script to create this database and its tables.

$2$ of $2$

Problem $2$

Create a $3-$tiers C#$,$ Windows Form, ADO.NET application with two options at the

main menu of the window for:

$1.$ Showing a DataGridView adapted for the "Students" table, which allows you to see

the data in the table, add new rows, modify rows and delete them $($deleting several

rows$).$ Use SQLAdaper.

$2.$ Showing a DataGridView adapted for the "Programs" table, which allows you to see

the data in the table, add new rows, modify rows and delete them $($deleting several

rows$).$ Use SQLAdaper.

Note: To correctly handle the deletions and modifications of rows in the case of

restriction by the foreign key, the same foreign key must be defined between the

DataTables. Therefore, both DataTables must be loaded in memory before using any of

these two tables for the first time.

Problem $3$

Include in your solution the business rules that no student can have year of enrolment

neither less than $2017$ nor greater than $2023.$ Make sure to produce specific messages.