A database schema consisting of three relations STUDENT, COURSE, and STAFF is created as follows:

CREATE TABLE STUDENT (STU_ID CHAR(4),

STUDENT_NAME CHAR(20),

ADDRESS CHAR(20),

BIRTHDATE DATE,

GENDER CHAR(6));

CREATE TABLE COURSE (COURSE_ID CHAR(6),

COURSE_TITLE CHAR(20),

STAFF_ID CHAR(3),

SECTION NUMBER(2));

CREATE TABLE STAFF (STAFF_ID CHAR(3),

STAFF_NAME CHAR(20),

GENDER CHAR(6),

DEPARTMENT CHAR(20),

BOSS_ID CHAR(3)

SALARY NUMBER(8,2));

Write down SQL statement for each query below:

1) Find out the information of staff members who are female and earn either below $5,000 or above $30,000. [5 marks] 2) List all staff members who are not in the Accounting nor the History department. [5 marks] 3) List all students whose name contains the substring "JONES". List the females before the males in chronological order (by birthdate). [6 marks] 4) List all the courses taught by Raymond J. JOHNSON. [6 marks] 5) Find the names of all staff members who earn more than their bosses. [8 marks] 6) List all staff members who are either in the same department as Amy Dancer or Jack Nelson. [8 marks] 7) Find the names of the staff members who make more money than every member of the Accounting department. [7 marks] 8) Find the average salary for each department with more than one staff member. [5 marks]

Imagine you are tasked with designing a database that encompasses the Florida Tech Learning Management System (LMS) that you use on a daily basis. The following requirements hold:

• Typical online student enrollment is 3,000 annually
• Access is password protected
• There should be a feature to create new accounts with different roles
• A role (faculty, student, staff) may have access to only certain features
• Students can be enrolled in multiple courses
• Faculty can teach multiple courses/sections
• Student records must be kept indefinitely
• Users should have near 24/7 access (with one hour of downtime allotted per month)
• The most frequent operations from a student perspective are posting discussions, taking exams, uploading assignments, checking grades, and emails
• The most frequent operations for faculty are posting discussions, changing course content, grading, and emails
• The most frequent operations for staff are to enroll students, create new sections, and assign sections to faculty

What would be your initial design? Provide both a logical and physical approach. For your logical suggestion, provide an E-R model of how the system might look and then provide support that it will be efficient given the operations. For the physical design, provide an outline of strategies and infrastructure you would put into place to support the business requirements.

Using draw.io diagram tool, design a database schema for an application that tracks and manages students’ applications for a university. Make sure to create proper primary key for each entity. Add relations and specify cardinalities.

• All entities and attributes must be named using the underscore convention. Highlight primary keys in red
• List any assumptions you have made

• Use diamonds and the association on the arrow.

• THINGS TO KEEP IN MIND :

• The university needs to keep track of students’ application. The application contains information about student: name, address, phone number, DOB, SSN, and generate a unique ID for each student.
• Education records has to be submitted as well. The education records contain information about previous degrees, GPA, name of school attended and dates ( from – to dates)
• Test scores are also submitted along with applications. The student can submit more than one test score. The test score consist of test type, date and score.
• The student should also specify which major they are applying to, for what term
• The university assign one employee from the admission office to process the application. The employee will handle multiple application at the same time.
• Applications can be submitted to different departments.

You need to design a Web Server, Database Server and a Backup server.

If you had to choose from the following list of resources which ones would you place a priority on and state why you would do so. List these for each server type. Hint: You need to think about the functionality of the server. Based on this information, which resource would you emphasize on the most to increase the performance of the server.

• CPU utilization and speed
• Multiprocessing
• Memory
• Cache
• Storage capacity
• Hard drive seek and access time
• Network Bandwidth

There are four tables in the database.

1. students (sno, sname, sgender, sbirthday, class)

- sno: student number

- sname: student name

- sgender: male or female

- sbirthday: date of birth

- class: class number

- primary key: sno

2. courses (cno, cname, tno)

- cno: course number

- cname: course name

- tno: teacher number

- primary key, cno, tno

3. scores (sno, cno, grade)

- sno: student number

- cno: course number

- grade: grade

- primary key, sno, cno

4. teachers (tno, tname, tgender, tbirthday, title, department)

- tno: teacher number

- tname: teacher name

- tgender: teacher gender

- tbirthday: date of birth

- title: title of the teacher, e.g. professor, lecture, or TA

- department: department name, e.g. CS, EE.

Question 1: In the score table, find the student number that has all the grades in between 90 and 70.

Question 2: For all the courses that took by class 15033, calculate the average grade.

Question 3: Find the class number that has at least two male students.

Question 4: Find the teacher's name in CS and EE department, where they have different title. Return both name and title.

Question 5: Find the students, who took the course number "3-105" and have earned a grade, at least, higher than the students who took "3- 245" course. Return the results in a descending order of grade.

Question 6: Find the students, who took more than 1 course, and return the students' names that is not the one with highest grade.

Question 7: For each course, find the students who earned a grade less than the average grade of this course.

For Cloud Database Encryption Technology Based on Combinatorial Encryption

You are required to research and report on this topic according to the Detail of Question below.

A. understand in order to present three main parts:

1. Summary:

o Provide a 200-300 word summary of the paper under review, from the background to the results being presented, and further work proposed. Please do NOT copy the abstract into this space!

2. Main points:

o The main issues as you see them.

o This is different than the summary.

3. Strengths and Weaknesses:

o Provide some critical analysis of the paper under review, positive and/or negative.

The following tables form part of a database held in a relational DBMS:

Hotel (hotelNo, hotelName, hotelAddress, country)

Room (roomNo, hotelNo, type, price)

Guest (guestNo, guestName, guestAddress, country)

Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo)

Write the SQL statements for the following questions:

1. List the rooms that are currently unoccupied at the Grosvenor Hotel, for:

(a) Use 2019-10-01 as today's date. Include all 'Grosvenor' hotels. List in hotelNo, roomNo order. Use NOT IN to perform the difference operation

(b) Use 2019-10-01 as today's date. Include all 'Grosvenor' hotels. List in hotelNo, roomNo order. Use NOT EXISTS to perform the difference operation.

(c) Use 2019-10-01 as today's date. Include all 'Grosvenor' hotels. List in hotelNo, roomNo order. Use LEFT JOIN to perform the difference operation.

(d) Use 2019-10-01 as today's date. Include all 'Grosvenor' hotels. List in hotelNo, roomNo order. Use MINUS to perform the difference operation.

Draw an ER diagram and write a database design outline for the following prompt:

You run a coaching service to help high school students prepare for the SAT exam. You have a staff of coaches, each of which has an employee ID, an hourly rate (such as $20 per hour), and personal information consisting of their first name, last name, middle name/initial, address information, phone number, mobile phone number, and e-mail address. For each high school student, you want to keep similar personal information and a student ID number, date of birth, and expected date of graduation from high school. Coaching takes place in sessions, to each of which you assign a unique ID number. Each session consists of one coach and one student. In addition to being able to identify the coach and student involved in each session, you want to store its start date/time and end date/time.

Create the database with at least 10 employees and 2 managers are populated. Important: your name should be in the employee table.

Write a Python code that can do the following:

    1) Take an employee ID from user

    2) Display the output of the SQL, select * from employee;

Improve the Python code above that can do the following:

    1) Take an employee ID from the user. Let it be idEntered.

    2) Get the departmentID for the given employeeID, idEntered. Let it be deptFound.

    3) Check if idEntered is in the table manager

    4) If yes, then execute: query = ("select * from employee where dID = %s; "). Note that since a variable deptFound is included in SQL, if it should be included, then use %s in SQL. And then declare a variable, data_tuple = (deptFound, ) in a separate line. Finally, execute the query cursor.execute(query, data_tuple).

    5) if No, simply execute the SQL: select * from employee where eid = %s; do the similarly way for idEntered as above