Question

SQL DATABASE

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

Task 4,part 2