Q$1.$ Create a table named Employee with the following structure:

EmployeeID $($integer$,$ primary key$)$

FirstName $($varchar$,$ not null$)$

LastName $($varchar$,$ not null$)$

Department $($varchar$,$ not null$)$

Salary $($decimal$,$ not null$)$

Insert three rows of data into the Employee table:

EmployeeID: $1,$ FirstName: 'John', LastName: 'Doe', Department: $\text{'}$IT$\text{'},$ Salary: $75000\mathrm{.}00$

EmployeeID: $2,$ FirstName: 'Jane', LastName: 'Smith', Department: $\text{'}$HR$\text{'},$ Salary: $60000\mathrm{.}00$

EmployeeID: $3,$ FirstName: 'Bob', LastName: 'Johnson', Department: 'Finance', Salary: $80000\mathrm{.}00$

Q$2.$ Create a table named Orders with the following structure:

OrderID $($integer$,$ primary key$)$

CustomerID $($integer$,$ not null$)$

OrderDate $($date$,$ not null$)$

TotalAmount $($decimal$,$ not null$)$

Insert two rows of data into the Orders table:

OrderID: $1,$ CustomerID: $101,$ OrderDate: $\text{'}2024-02-19\text{'},$ TotalAmount: $150\mathrm{.}50$

OrderID: $2,$ CustomerID: $102,$ OrderDate: $\text{'}2024-02-20\text{'},$ TotalAmount: $200\mathrm{.}75$

Q$3.$ Write an SQL query to retrieve the first and last names of employees in the $\text{'}$IT$\text{'}$ department with a salary greater than $70000.$

Q$4.$ Write an SQL query to find the total number of orders and the average total amount per order.

Q$5.$ Write an SQL query to retrieve a list of unique departments from the Employee table in alphabetical order.

Q$6.$ Case Study:University Enrollment System

The ABC University is planning to develop an Enrollment System to efficiently manage student enrollments, courses, and faculty assignments. The university currently offers various undergraduate and postgraduate programs across different departments. The system aims to streamline the enrollment process, facilitate course management, and ensure proper assignment of faculty members to courses.

Background:

The university has multiple departments such as Computer Science, Mathematics, Physics, and Business Administration. Each department offers a set of courses that students can enroll in based on their program of study. Students can pursue different programs like Bachelor of Science in Computer Science, Master of Business Administration, etc.

Entities:

Student:

Students enroll in programs offered by departments.

Each student has a unique student ID$,$ name, and contact details.

Students can be part of multiple programs during their academic journey.

Program:

Programs are offered by specific departments.

Each program has a program code, title, and duration.

Department:

Departments oversee the programs and courses offered.

Each department has a department code, name, and location.

Course:

Courses are specific subjects offered by departments.

Each course has a course code, title, and credit hours.

Faculty:

Faculty members are assigned to teach courses within departments.

Each faculty member has a faculty ID$,$ name, and specialization.

Relationships:

Enrollment:

Students enroll in programs offered by departments.

Programs have multiple enrolled students.

Departmental Oversight:

Each department oversees multiple programs.

Programs belong to specific departments.

Course Offering:

Departments offer various courses.

Courses are offered by specific departments.

Faculty Assignment:

Faculty members are assigned to teach courses.

Courses have assigned faculty members.

Scenario:

A student, John, enrolls in the Bachelor of Science in Computer Science program offered by the Computer Science Department. Within this program, he enrolls in specific courses like "Database Management" and "Software Engineering." The Computer Science Department oversees the program, and faculty members are assigned to teach the courses.

The Enrollment System needs to capture these relationships and entities to ensure seamless management of student enrollments, programs, courses, departments, and faculty assignments.

Evaluation $(5$ Marks$)$:

Entity Identification $(1$ Mark$)$:

Awarded for correctly identifying and defining entities such as Student, Program, Department, Course, and Faculty.

Relationship Identification $(2$ Marks$)$:

Awarded for recognizing and defining relationships such as Enrollment, Departmental Oversight, Course Offering, and Faculty Assignment.

Completeness and Coherence $(2$ Marks$)$:

Awarded for ensuring that the identified entities and relationships align with the university's scenario and goals, demonstrating a comprehensive understanding of the system's requirements.