Question

Design and implement a relational database application of your choice using MS Workbench on

MySQL

a) Declare two relations (tables) using the SQL DDL. To each relation name, add the last 4 digits

of your Student-ID. Each relation (table) should have at least 4 attributes. Insert data to both

relations (tables); (15%)

b) Based on your expected use of the database, choose some of the attributes of each relation as

your primary keys (indexes). To each Primary Key name, add the last 4 digits of your Student-

ID by using Alter command. Explain why you choose them as the primary keys; (10%)

c) Specify one Update, one Delete, one Select, one Join, and one View in SQL that are needed by

your database application

Answer 1

Replace 4SID with your StudentId
a) Creating two tables

CREATE TABLE `course4SID` (
`CourseId` INT NOT NULL,
`CourseName` VARCHAR(45) NULL,
`TotalMarks` INT NULL,
`Teacher` VARCHAR(45) NULL,
PRIMARY KEY (`CourseId`));

CREATE TABLE `sql_0112191`.`student4sid` (
`StudentID` INT NOT NULL,
`StudentName` VARCHAR(45) NULL,
`Course1` INT NULL,
`Course2` INT NULL,
PRIMARY KEY (`StudentID`),
INDEX `CourseId_idx` (`Course1` ASC) VISIBLE,
CONSTRAINT `CourseId`
FOREIGN KEY (`Course1`)
REFERENCES `sql_0112191`.`course4sid` (`CouseId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);

Inserting data

INSERT INTO `course4sid` (`CourseId`, `CourseName`, `TotalMarks`, `Teacher`) VALUES ('1', 'Maths', '150', 'Teacher1');
INSERT INTO `course4sid` (`CourseId`, `CourseName`, `TotalMarks`, `Teacher`) VALUES ('2', 'Physics', '100', 'Teacher2');
INSERT INTO `course4sid` (`CourseId`, `CourseName`, `TotalMarks`, `Teacher`) VALUES ('3', 'Chemistry', '80', 'Teacher3');
INSERT INTO `course4sid` (`CourseId`, `CourseName`, `TotalMarks`, `Teacher`) VALUES ('4', 'Computers', '100', 'Teacher4');

INSERT INTO `student4sid` (`StudentID`, `StudentName`, `Course1`, `Course2`) VALUES ('101', 'John', '1', '2');
INSERT INTO `student4sid` (`StudentID`, `StudentName`, `Course1`, `Course2`) VALUES ('102', 'Max', '1', '3');
INSERT INTO `student4sid` (`StudentID`, `StudentName`, `Course1`, `Course2`) VALUES ('103', 'Kevin', '2', '3');
INSERT INTO `student4sid` (`StudentID`, `StudentName`, `Course1`, `Course2`) VALUES ('104', 'Peter', '2', '4');

1	Maths	150	Teacher1
2	Physics	100	Teacher2
3	Chemistry	80	Teacher3
4	Computers	100	Teacher4

101	John	1	2
102	Max	1	3
103	Kevin	2	3
104	Peter	2	4

b) Adding 4 Digits of student id to the column

ALTER TABLE `course4sid` CHANGE COLUMN `CourseId` `CourseId4SID` INT NOT NULL ;

ALTER TABLE `student4sid` CHANGE COLUMN `StudentID` `StudentID4SID` INT NOT NULL ;

We chose the CourseId4SID and StudentID4SID as the Primary Key because they can uniquely identify each row of the table

CourseId4SID	int(11)	NO	PRI
CourseName	varchar(45)	YES
TotalMarks	int(11)	YES
Teacher	varchar(45)	YES

StudentID4SID	int(11)	NO	PRI
StudentName	varchar(45)	YES
Course1	int(11)	YES	MUL
Course2	int(11)	YES

c) SQL Statements
UPDATE `student4sid` SET `Course2` = '4' WHERE (`StudentID4SID` = '102');

101	John	1	2
102	Max	1	4
103	Kevin	2	3
104	Peter	2	4

DELETE FROM `student4sid` WHERE (`StudentID4SID` = '104');

101	John	1	2
102	Max	1	4
103	Kevin	2	3

SELECT * FROM student4sid;

101	John	1	2
102	Max	1	4
103	Kevin	2	3

SELECT StudentID4SID,StudentName,CourseName FROM student4sid INNER JOIN course4sid WHERE Course1=CourseId4SID;

Dispaying the student with their Course1 Names

101	John	Maths
102	Max	Maths
103	Kevin	Physics

CREATE OR REPLACE VIEW `new_view` AS SELECT StudentID4SID,StudentName,CourseName FROM student4sid INNER JOIN course4sid WHERE Course1=CourseId4SID;;

101	John	Maths
102	Max	Maths
103	Kevin	Physics