PROG32356 School DB

Create an Entity-Framework console app using SchoolDB from SQL Server:

• Your app should be able to perform CRUD operations (read, insert, update, and delete) for each item of data. Use LINQ-To-Entity queries to select your data for each table (Student, Teacher and Course) with minimum 5 entities in each table.
• Start an app by loading a menu with multiple options for each operation. Show an error message for incorrect type (Provide adequate validations).
• For Read, Insert, and update operations, your queries should successfully perform required tasks to all relevant fields based on table design.
• For course table, three courses must be available in the database - Mathematics, Physics and Chemistry. Make sure, each course name has/linked to proper course Id.
• In addition to CRUD operations, your app should also perform other operations - Search a Student and Teacher by their name, Sort the output list for Students and Teachers in ascending or descending order based on user selection.
• Add code to calculate the total grade and average grade for any student from the courses - Mathematics, Physics and Chemistry. Display the grades output in adequate format.
• Remember, grades are not part of database, add code accordingly asking user to enter grades for each respective course. Calculate the total and average grades and merge into your application for respective output.

Note:

• You will add any required SQL queries or joins to complete all the required operations.
• You should display a message asking the user to confirm quitting the application.
• Display a message asking the user to confirm if they wish to delete or update the data.

Display information for a table:

Fetch all records from the Student table and display the following columns:

StudentID StudentName CourseID

Fetch all records from the Teacher table and display the following columns:

TeacherID TeacherName TeacherType

Fetch all records from the Course table and display the following columns:

CourseID CourseName TeacherID

Fetch all records from the Student and Student Address tables - for search by student name option and display the following columns:

StudentID StudentName Address City Province

Refer to the following diagram which shows Class Hierarchy:

Fig. SchoolDB Diagram showing class relations: see image.