代写ENG2003 Assignment代做留学生SQL 程序

ENG2003 Assignment

*Please note that the hand-in document must be in Word or PDF format.

Question 1. Given that, a Library Management System has the following database with four tables: MEMBER, BOOK, AUTHOR,  and BORROW.

MEMBER

MemberID	FirstName	LastName	JoinDate
1	Alice	Green	2020-03-15
2	Tom	Harris	2021-07-22
3	Bob	Allen	2022-08-23

BOOK

BookID	Title	AuthorID
101	The Silent Forest	501
102	Beyond the Horizon	502
103	The Lost Expedition	502

AUTHOR

AuthorID	FirstName	LastName	Nationality
501	Emily	Stone	American
502	James	Carter	British

BORROW

BorrowID	MemberID	BookID	BorrowDate	DueDate	ReturnStatus
1001	1	101	2023-11-01	2023-11-15	Overdue
1002	2	102	2023-11-05	2023-11-19	Returned
1103	3	102	2024-01-03	2024-01-08	Returned

(1)  Please identify the primary keys and foreign keys for these four tables.

(2)  Draw the RM diagram for these four tables (remember to show the attributes and keys of the entities in the diagram).

(3)  Write the SQL code to count the number of author from the AUTHOR table

(4)  Write a SQL query to list all authors and the number of books they have written. The result should include the author's first name, last name, and the book count.

(5)  Draw the table to show the results of the following query:

SELECT

BorrowID, MemberID, BookID, BorrowDate, DueDate, ReturnStatus

FROM

BORROW

WHERE

BorrowDate BETWEEN '2023-11-01'AND '2023-11-10'

ORDER BY

BorrowDate ASC;

Question 2. The university's academic management database is designed to streamline the administration of courses, departments, professors, students, and facilities. This comprehensive system supports efficient scheduling, class management, and enrollment processes. By capturing the intricate relationships between these entities, the database ensures smooth coordination of academic activities, from assigning professors to classes, to managing student enrollments and classroom allocations.

Here are the RM diagram for the database:

 

Please write SQL statements to answer the following queries according to the above RM diagram:

(1)      List the names of all students who have not enrolled in any classes.

(2)      Find the average number of credits per course offered by the "Mathematics" department.

(3)      List the names of all students enrolled in courses taught by professors with the rank of "Associate Professor."

(4)      List the names of all courses that do not have any enrollments.

(5)      Find the total number of students enrolled in each course, sorted by enrollment count in descending order.