Texts: You should produce the following DDL statements in MySQL:
1. CREATE TABLE Statements.
- Include constraints specified in the diagram or derived from the sample data, including DEFAULT, UNIQUE, CHECK, PRIMARY KEY & FOREIGN KEY where appropriate.
2. CREATE VIEW Statements.
- Produce a view that returns the sessions in the future and rejects any attempt to insert or update sessions in the past.
- Write a simple statement to test rejection.
3. CREATE PROCEDURE Statements.
- Produce a procedure that assigns a new schedule for a given course. The procedure should accept a course code & start date as arguments, then schedule each of the given courseās modules on consecutive (working / week) days. The start date must be at least a month in the future; otherwise, a suitable error should be raised.
- Tips: CURSOR, LOOP, SIGNAL
4. CREATE TRIGGER Statements.
- Produce an audit trail (in a separate table) that records the current username, system date & grade change when someone attempts to update a delegateās grade.
The file DBSyS_CourseWork_01.xlsx on Canvas contains sample data for each of the five tables and should be inserted to ensure consistent testing data.
You should produce the following DML statements in MySQL:
Write the appropriate statements to insert the sample data.
1. Fetch every moduleās code, name & credits.
2. Fetch every delegateās no & name in descending order by name.
3. Fetch the courseās code, name & credits where the name contains the string āNetworkā.
4. Calculate the highest grade in any module.
5. Modify the query from Q4 to now fetch only the delegate no.
- Tips: Sub-Query
6. Modify the query from Q5 to also fetch the delegate name.
- Tips: Nested Sub-Query
7. Fetch the sessionās code & date for sessions which are running in the next year and for which no room has been allocated.
- Tips: CURRENT_DATE(), ADD_DATE(), IS NULL
8. Fetch the delegateās no & name along with the moduleās code & name for delegates who have taken a module but have a failing grade.
- Tips: INNER JOIN
9. Solve the problem from Q6 using JOINS where possible.
- Tips: INNER JOIN, Sub-Query
10. Calculate and display every delegateās no & name along with their attained credits versus the courseās code, name & credits.
- Tips: SUM(), INNER JOIN, GROUP BY
11. Modify the query from Q10 to only show a delegate when they have attained the courseās credits.
Sample Data:
code name credits
WSD Web Systems Development 75
DDM Database Design & Management 100
NSF Network Security & Forensics 75
code name cost credits course_code
A2 ASP.NET 250 25 WSD
A3 PHP 250 25 WSD
A4 JavaFX 350 25 WSD
B2 Oracle 750 50 DDM
B3 SQLS 750 50 DDM
C2 Law 250 25 NSF
C3 Forensics 350 25 NSF
C4 Networks 250 25 NSF
code date room
A2 2023.06.05 305
A3 2023.06.06 307
A4 2023.06.07 305
B2 2023.08.22 208
B3 2023.08.23 208
A2 2024.05.01 303
A3 2024.05.02 305
A4 2024.05.03 303
B2 2024.07.10 NULL
B3 2024.07.11 NULL
no name phone
2001 Mike
2002 Andy
2003 Sarah
2004 Karen
2005 Lucy
2006 Steve
2007 Jenny
2008 Tom
no code grade
2003 A2 68
2003 A3 72
2003 A4 53
2005 A2 48
2005 A3 52
2002 A2 20
2002 A3 30
2002 A4 50
2008 B2 90
2007 B2 73
2007 B3 63
The diagram below shows a training centre database:
session code CHAR(2)
date DATE
room VARCHAR(30)
delegate no INT
name VARCHAR(30)
phone VARCHAR(30)
module code CHAR(2)
name VARCHAR(30)
cost DECIMAL(8,2)
credits TINYINT
course_code CHAR(3)
take no INT
code CHAR(2)
course code CHAR(3)
name VARCHAR(30)
credits TINYINT