Question

SQL DATABASE Task 4 [1.5 marks] Create Index (0.5 marks) Currently, the database only contains a...

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');

Homework Answers

Answer #1

Task 4,part 2

Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
Created the database and all the tables in phpMyadmin. In addition, write all the sql statements...
Created the database and all the tables in phpMyadmin. In addition, write all the sql statements used in perforing the tasks in a word document and submit that for grading. Lab 1: CREATE STATEMENT This statement is used to create a database or a table. CREATE DATABASE This statement is used to create a database Syntax: CREATE DATABASE database_name; OR CREATE SCHEMA database_name; Example: CREATE DATABASE Nyumbani; OR CREATE SCHEMA Nyumbani ; CREATE TABLE This SQL statement is used to...
SQL Data: you can just copy paste it into mysql to. CREATE DATABASE University;USE University; CREATE...
SQL Data: you can just copy paste it into mysql to. CREATE DATABASE University;USE University; CREATE TABLE Student (  sid INT PRIMARY KEY,  name VARCHAR(20) NOT NULL,  address VARCHAR(20) NOT NULL,  major CHAR(2)); CREATE TABLE Professor (  pid INT PRIMARY KEY,  name VARCHAR(20) NOT NULL,  department VARCHAR(10) NOT NULL); CREATE TABLE Course (  cid INT PRIMARY KEY,  title VARCHAR(20) NOT NULL UNIQUE,  credits INT NOT NULL,  area VARCHAR(5) NOT NULL); CREATE TABLE Transcript (  sid INT,  cid INT,  pid INT,  semester VARCHAR(9),  year YEAR,  grade CHAR(1) NOT NULL,  PRIMARY KEY (sid, cid, semester, year),  FOREIGN KEY (sid) REFERENCES Student...
1. For the Pet Database described in the previous practical, write an SQL statement with a...
1. For the Pet Database described in the previous practical, write an SQL statement with a subquery to list all the people in the Owner table who own more than one pet. should be a collum of the total number of pets owned, owner id, first name and last name -- DROP TABLE PetAndOwner, Pet, PetType, Owner; CREATE TABLE PetType ( petTypeId VARCHAR(10) PRIMARY KEY, animalType VARCHAR(20), breed VARCHAR(20) ); CREATE TABLE Owner ( ownerId VARCHAR(10) PRIMARY KEY, firstName VARCHAR(20),...
PLEASE USE MICROSOFT SQL SERVER MANAGEMENT STUDIO 18 The purpose of this task is to create...
PLEASE USE MICROSOFT SQL SERVER MANAGEMENT STUDIO 18 The purpose of this task is to create a student table such that it has 2 partitions. One that will include all student id's through 500 and a second one that will include all student id's above 500. StudentID                   int           StudentName              varchar(30) *StudentID should be a a primary key. **StudentName should be required. Do you not use the master filegroup. Each partition should be associated with a different physical file. Use any...
Create 2 stored procedures: sp_AddNewUser and sp_AddNewRole. Use the ManyToMany script as your base table structures....
Create 2 stored procedures: sp_AddNewUser and sp_AddNewRole. Use the ManyToMany script as your base table structures. The stored procedure should accept the parameters needed to input the data for each table. NOTE: You do not need to input the UserID or RoleID. These are surrogate keys and the system automatically inserts them when you insert a row in the tables.   On execution, the stored procedure should check the database to see if the user exists, if so, return a message...
Question 1: Group by and Aggregates: Write SQL statements to answer the following questions using Assignment...
Question 1: Group by and Aggregates: Write SQL statements to answer the following questions using Assignment 4’s schema (Customer-Invoice-Line-Product-Vendor). Make sure that your SQL script runs without any errors. Submit your answers in a .SQL file. 1 (2 Points) - Find the count of distinctvendors thatsupplied products that are priced lowerthan 185? 2 (2 Points) - For each vendor, find their product that has the lowest product quantity. Your output should include vendor code, vendor name, product description and product...
Task 7.1.2. Create a view named nc_jobs which shows all the bookjob information for a job...
Task 7.1.2. Create a view named nc_jobs which shows all the bookjob information for a job with a jobtype of N for publisher with a creditcode of C. CREATE TABLE publishers ( cust_id       CHAR(3) NOT NULL, name        CHAR(10), city        CHAR(10), phone        CHAR(8), creditcode    CHAR(1), primary key (cust_id) ); CREATE TABLE bookjobs ( job_id       CHAR(3) NOT NULL, cust_id       CHAR(3), job_date    DATE, descr CHAR(10), jobtype CHAR(1), primary key (job_id), foreign key...
Describe what the following query does using lyrics database with mysql. lyrics database is posted below...
Describe what the following query does using lyrics database with mysql. lyrics database is posted below 1.) select m.lastname, m.firstname, s.lastname from members m inner join salespeople s using (salesID) order by m.lastname asc; 2.) select studioID, studioname, base from salespeople sa inner join studios st on (sa.salesID = st.salesid) where base < 300 3.) SELECT artistName FROM Artists WHERE artistID IN (SELECT artistID FROM Titles) DROP TABLES IF EXISTS Artists,Genre, Members, Titles, Tracks,SalesPeople,Studios,XrefArtistsMembers; DROP TABLES IF EXISTS Authors,Publishers,Titles,Title_Authors,Royalties; DROP...
-- Define the database CREATE TABLE Product ( ProdName VARCHAR2(10) NOT NULL, Price INTEGER NOT NULL,...
-- Define the database CREATE TABLE Product ( ProdName VARCHAR2(10) NOT NULL, Price INTEGER NOT NULL, CONSTRAINT PRODUCT_PK PRIMARY KEY (ProdName) ); CREATE TABLE Customer ( ID INTEGER NOT NULL, ProdName VARCHAR2(10), Rating INTEGER, CustName VARCHAR2(10), CONSTRAINT CUSTOMER_PK PRIMARY KEY (ID) ); CREATE TABLE Buys ( ID INTEGER NOT NULL, ProdName VARCHAR2(10) NOT NULL, Quantity INTEGER NOT NULL, CONSTRAINT BUYS_PK PRIMARY KEY (ID, ProdName) ); ALTER TABLE Buys ADD CONSTRAINT PRODUCT_BUYS_FK FOREIGN KEY (ProdName) REFERENCES Product (ProdName) NOT DEFERRABLE; ALTER...
/* myCompany.SQL Introduction to SQL Script file for ORACLE DBMS This script file creates the following...
/* myCompany.SQL Introduction to SQL Script file for ORACLE DBMS This script file creates the following tables: VENDOR, PRODUCT, CUSTOMER, INVOICE, LINE EMPLOYEE and loads the default data rows */ set echo on; set serveroutput on; select systimestamp from dual; show user; ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'; DROP TABLE LINE CASCADE CONSTRAINTS; DROP TABLE INVOICE CASCADE CONSTRAINTS; DROP TABLE CUSTOMER CASCADE CONSTRAINTS; DROP TABLE PRODUCT CASCADE CONSTRAINTS; DROP TABLE VENDOR CASCADE CONSTRAINTS; DROP TABLE EMPLOYEE CASCADE CONSTRAINTS; CREATE TABLE...