Question

SQL DATABASE

Task 2 [10.5 marks] using the AdditionCollege database

For task 2, we have provided you with the creation script for the AdditionCollege database. Run this script in MySQL Workbench to create the database. You should execute your query solutions to extract the necessary information.

The script is based on the following schematic:

Unit (Unit_code, Staff_id, [UnitName])

Staff (Staff_id, StaffName, Position, Gender)

Taught_by (Unit_code, Staff_id, weekday)

Student (Student_id, Student_name, Address, Gender)

TuteGroup (TuteGroup_code, Unit_code, DayHrCode, Room_Nr)

TuteGroup_List (TuteGroup_code, Student_id)

Notes

• Units are taught between Monday to Friday.
• Staff position should be “Tutor”, “Lecturer” or “Senior Lecturer”.
• Staff and student gender should be “M”, “F”, or “O”.
• Unit codes are 3 letters and then 2 numbers.
• Student and staff names include both first name and surname.
• Day hour code is the Day and time code.
• Tute group codes are the combination of numbers and letters.

Query 1 (1 mark)

Write a query to list the student ID, name, gender of students whose gender is “M”.

Query 2 (1.5 marks)

Write a query to list the staff ID of all staffs that have taught more than two units.

Query 3 (1.5 marks)

Write a query to list the names of all students of the unit with the unit code “TNS01”.

Query 4

Write a query to determine the total number of different students that the staff with staff_id “S1” is teaching.

Query 5

Write a query to list all students’ information who attended Monday classes. Sort the result set with the ascending order of address.

Query 6 (2.5 marks)

Write a query to list all staffs’ information who have taught more than 6 students. Please note that the same student in a different unit will be counted as different students.

Task 3 [3 marks]

Insert (1 mark)

Write an INSERT command to insert the data of a new student into student table. You need to analyze existing student id information to decide this new student’s ID. The name of the new student is ‘Sybil Stark’. The address of the new student ‘302/28 Dengate Lane, St Lucia, QLD 4067’. The new student is a female.

Delete (1 mark)

Write a DELETE command to remove the data of the staff from the staff table, who do not teach any units.

Update (1 mark)

Write an UPDATE comment to change the position of the staff with the last name ‘Rostov’ to ‘Senior Lecturer’.

Task 4 [1.5 marks]

Create Index (0.5 marks)

Currently, the database only contains a small number of records. However, the data contained within it is expected to grow significantly in the future. Creating indexes on commonly searched columns is a way performance issues can be minimized.

Write a command to create an index on student_name column of the student table.

Create view – 1 mark

Write a command to create a view to list the student ID and student name of students who have classes at room ‘RM123’.

Task 5 [2 marks]

Working as a Database Administrator for MySQL AdditionCollege database, write the following commands for two users, Vanessa and Jessica to achieve the following database security requirements:

1. User Vanessa is no longer allowed to add data to the Staff table (0.5 marks)
2. User Vanessa is no longer allowed to delete records from the Staff table (0.5 marks)
3. User Jessica must be able to add records to the TuteGroup table (0.5 marks)
4. User Jessica must be able to remove records from the TuteGroup table (0.5 marks)

Assume usernames of users namely Vanessa and Jessica are vanessa and jessica respectively.

Task 6 [4 marks]

Hint: Going through the examples of functional dependencies and normalization covered in the Week 6 Lecture will help you to work on this question.

Using the following table structure, identify all functional dependencies and then decompose this table into a set of 3NF relations. Your answer should:

• List the functional dependences
• Convert the relational schema for the relations in 3NF.

NUM	PROD_NUM	SALE_DATE	PROD_ DESCRIPTION	VEND_ CODE	VEND_ NAME	NUMBER_SOLD	PROD_PRICE
12345	EE-A3422Z	22_JUL_2020	Camera	502	City Shop	5	$69.95
12345	AC-2002X	22_JUL_2020	Cable	502	City Shop	6	$3.45
12345	SV-4534Y	22_JUL_2020	Keyboard	609	COVID-19 Shop	5	$39.99
12346	EE-A3422Z	22_JUL_2020	Camera	502	City Shop	3	$69.95
12347	BC-7783P	23_JUL_2020	Monitor	257	Online Shop	5	$87.75

additional information

create database AdditionCollege;
use AdditionCollege;

/*table staff */
create table Staff(
staff_id varchar(30) not null,
staffname varchar(60) not null,
position varchar(60) not null,
gender char(1) ,
primary key(staff_id)
);

/* Table Unit*/
create table Unit (
unit_code varchar(30) not null,
staff_id varchar(30) not null,
unitname varchar(60),
primary key(unit_code),
foreign key(staff_id) references staff(staff_id)
);

/*table taught_by*/
Create table Taught_By(
unit_code varchar(30) not null,
staff_id varchar(30) not null,
weekday enum ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday') not null,
primary key(unit_code, staff_id),
foreign key(unit_code) references unit(unit_code),
foreign key(staff_id) references staff(staff_id)
);

/*table student*/
create table Student(
student_id varchar(30) not null,
student_name varchar(60) not null,
address varchar(60) not null,
gender char(1) not null,
primary key(student_id)
);

/*table TuteGroup*/
create table TuteGroup(
tuteGroup_code varchar(30) not null,
unit_code varchar(30) not null,
dayHrCode varchar(30) not null,
room_nr varchar(30) not null,
primary key(tuteGroup_code),
foreign key(unit_code) references unit(unit_code),
unique key (dayHrCode, room_nr)
);

/*tutegroup_list table*/

Create table TuteGroup_List(
tuteGroup_code varchar(60) not null,
student_id varchar(30) not null,
primary key (tuteGroup_code, student_id),
foreign key(tuteGroup_code) references tuteGroup(tuteGroup_code)
);

/* Insert into Staff*/
Insert into Staff values ('S1', 'Vladimir Rostov', 'Lecturer', 'M');
Insert into Staff values ('S2', 'Rory Reid', "Tutor", "O");
Insert into Staff values ('S3', 'Andy Murray', "Tutor", "O");
Insert into Staff values ('S4', 'Novak Djokovic', "Tutor", "M");
Insert into Staff values ('S5', 'Chloe Lai', "Senior Lecturer", "F");

/*Insert into Unit */
insert into Unit values ('TNS01', 'S1', 'Database Essentials');
insert into Unit values ('TNS02', 'S2', 'Database Design and Development');
insert into Unit values ('TNS03', 'S3', 'Essential SQL');
insert into Unit values ('TNS04', 'S4', 'Datawarehousing');
insert into Unit values ('TNS05', 'S4', 'All about MySQL');
insert into Unit values ('TNS06', 'S1', 'SQL Query Performance Tuning');
insert into Unit values ('TNS07', 'S2', 'Database Administration and Maintenance');

/* Insert into Taught_By */
insert into Taught_By values ('TNS01', 'S1', 'Monday');
insert into Taught_By values ('TNS02', 'S1', 'Tuesday');
insert into Taught_By values ('TNS02', 'S2', 'Tuesday');
insert into Taught_By values ('TNS03', 'S3', 'Monday');
insert into Taught_By values ('TNS04', 'S4', 'Thursday');
insert into Taught_By values ('TNS05', 'S4', 'Wednesday');
insert into Taught_By values ('TNS06', 'S1', 'Friday');
insert into Taught_By values ('TNS07', 'S2', 'Thursday');

/* Insert into Student */
insert into Student values ('STD01', 'Siobhan Curran', "Lvl19/ 239 George St, Brisbane, QLD 4000" ,"F");
insert into Student values ('STD02', 'Eddie Murphy', "18 Dorchester St, South Brisbane, QLD 4101", "M");
insert into Student values ('STD03', 'Tony Murphy', "252 Wellington Rd, East Brisbane, QLD 4169", "O");
insert into Student values ('STD04', 'Joe Bloggs', "63 Geelong St, East Brisbane, QLD 4169", "M");
insert into Student values ('STD05', 'Janet Volker', "12 Cairns St, East Brisbane, QLD 4169 ", "F");
insert into Student values ('STD06', 'Jason Crawford', "303 Adelaide St, Brisbane, QLD 4000", "M");
insert into Student values ('STD07', 'Zoha Zidane', "48 Montague Rd, South Brisbane, QLD 4101", "F");
insert into Student values ('STD08', 'Victor Lazslo', "23 Wellington St, Brisbane, QLD 4000", "M");
insert into Student values ('STD09', 'Antony Lawrence', "9 Latrobe St, East Brisbane, QLD 4169", "M");
insert into Student values ('STD10', 'Erwin Schroedinger', "5 Blackall Tce, East Brisbane, QLD 4169", "O");

/*Insert into TuteGroup */
insert into TuteGroup values('TG1', 'TNS01', 'Mon 9 AM', 'RM123');
insert into TuteGroup values('TG2', 'TNS02', 'Tue 2 PM', 'RM123');
insert into TuteGroup values('TG3', 'TNS04', 'Thu 4 PM', 'RM889');
insert into TuteGroup values('TG4', 'TNS05', 'Wed 11 AM', 'RM600');
insert into TuteGroup values('TG5', 'TNS03', 'Mon 9 AM', 'RM889');
insert into TuteGroup values('TG6', 'TNS06', 'Fri 2 AM', 'RM600');

/*Insert into TuteGroup_List*/
insert into TuteGroup_List values ('TG1', 'STD01');
insert into TuteGroup_List values ('TG1', 'STD02');
insert into TuteGroup_List values ('TG1', 'STD03');
insert into TuteGroup_List values ('TG1', 'STD09');
insert into TuteGroup_List values ('TG2', 'STD10');
insert into TuteGroup_List values ('TG2', 'STD04');
insert into TuteGroup_List values ('TG2', 'STD07');
insert into TuteGroup_List values ('TG3', 'STD05');
insert into TuteGroup_List values ('TG3', 'STD06');
insert into TuteGroup_List values ('TG3', 'STD08');
insert into TuteGroup_List values ('TG3', 'STD04');
insert into TuteGroup_List values ('TG4', 'STD01');
insert into TuteGroup_List values ('TG4', 'STD09');
insert into TuteGroup_List values ('TG4', 'STD10');
insert into TuteGroup_List values ('TG4', 'STD03');
insert into TuteGroup_List values ('TG5', 'STD02');
insert into TuteGroup_List values ('TG5', 'STD07');
insert into TuteGroup_List values ('TG5', 'STD06');
insert into TuteGroup_List values ('TG5', 'STD10');
insert into TuteGroup_List values ('TG5', 'STD08');
insert into TuteGroup_List values ('TG6', 'STD09');
insert into TuteGroup_List values ('TG6', 'STD05');
insert into TuteGroup_List values ('TG6', 'STD04');
insert into TuteGroup_List values ('TG6', 'STD10');

Answer 1

Due to time constraint, i will be answering Task 2 and Task 3.

Task 2:

Query 1: Write a query to list the student ID, name, gender of students whose gender is “M”.

select student_id, student_name, gender from student where gender = 'M';

Query 2: Write a query to list the staff ID of all staffs that have taught more than two units.

select staff_id from taught_by group by staff_id having count(*)>2;

Query 3: Write a query to list the names of all students of the unit with the unit code “TNS01”.

select student_name from student s, tuteGroup tg, tuteGroup_list tgl where tg.unit_code = 'TNS01' and tg.tuteGroup_code = tgl.tuteGroup_code and tgl.student_id = s.student_id;

Query 4: Write a query to determine the total number of different students that the staff with staff_id “S1” is teaching.

select count(distinct(tgl.student_id)) as No_Of_Student_Taught_By_S1 from tuteGroup_list tgl, tuteGroup tg, taught_by tb where tb.staff_id = 'S1' and tb.unit_code = tg.unit_code and tg.tuteGroup_code = tgl.tuteGroup_code;

Note: There is some discrepancy in data in UNIT & TAUGHT_BY tables. In UNIT table, staff_id S2 is assigned for unit TNS02 but in TAUGHT_BY table S1 staff_id is assigned to TNS02 unit. This query is based on taught by table.

Query 5: Write a query to list all students’ information who attended Monday classes. Sort the result set with the ascending order of address.

select s.* from student s, taught_by tb, tuteGroup tg, tuteGroup_list tgl where tb.weekday = 'Monday' and tb.unit_code = tg.unit_code and tg.tuteGroup_code = tgl.tuteGroup_code and tgl.student_id = s.student_id group by s.student_id order by s.address;

Query 6: Write a query to list all staffs’ information who have taught more than 6 students. Please note that the same student in a different unit will be counted as different students.

select s.* from staff s, taught_by tb, tuteGroup tg, tuteGroup_list tgl where tb.unit_code = tg.unit_code and tg.tuteGroup_code = tgl.tuteGroup_code and tb.staff_id = s.staff_id group by s.staff_id having count(tgl.student_id) > 6;

Task 3:

Query: Write a DELETE command to remove the data of the staff from the staff table, who do not teach any units.

delete from staff where staff.staff_id not in (select distinct(staff_id) from unit);

Note: S5 staff_id is deleted as this staff does not teach any unit. Added result of staff table after deletion.

Query: Write an UPDATE comment to change the position of the staff with the last name ‘Rostov’ to ‘Senior Lecturer’.

update staff set position = 'Senior Lecturer' where substring_index(staffname,' ',-1) = 'Rostov';

Query: Write an INSERT command to insert the data of a new student into student table. You need to analyze existing student id information to decide this new student’s ID. The name of the new student is ‘Sybil Stark’. The address of the new student ‘302/28 Dengate Lane, St Lucia, QLD 4067’. The new student is a female.

Note: First create a variable (count_student) to increment the integer part of primary key and then concatenate this variable's value to the string 'STD' in insert query to generate new incremented varchar id

//selecting integer part as substring of the largest student_id present in table and then incrementing it.

set @count_student = (select substring(student_id,4,length(student_id)) from student order by student_id desc limit 1)+1;

// then insert student_id as concat('STD',@count_student)

insert into student (student_id, student_name, address, gender) values(concat('STD',@count_student),'Sybil Stark', '302/28 Dengate Lane, St Lucia, QLD 4067', 'F');

In below screenshot, you can see the new record with student_id as 'STD11'.