Assignment 

A Learning Management System (LMS) provides an online learning environment for teachers and students. LMS’s arewidely used by many universities and schools to aid the delivery of their course material; the myElearning system is anexample of such a LMS. The following relational schema represents a portion of a typical LMS database. The UserId,password and details of each user are stored in the User and UserDetail tables; UserId in the UserDetail table is also aforeign key referencing the User table. Data about the courses are stored in the course table; this includes the UserIdwho created the course; the DateCreated in the Course table is set to the current date. When a user is enrolled in acourse, he is assigned a role (teacher, student etc…); a user can have different roles in different courses. When a user isenrolled in a course, the DateEnrolled is set to the current date and the DateRemoved is set to null; when the user isunenrolled from the course, the DateRemoved is updated.

User (UserId, Password, ExpiryDate)
UserDetail (UserId, FirstName, LastName, Email, Country)
Course (CourseCode, CourseName, DateCreated, OwnerUserId)
Enrollment (EnrollmentId, CourseCode, UserId, RoleId, DateEnrolled, DateRemoved)
Role (RoleId, RoleName)

Section A

For this section, you are required to upload a single executable SQL script file. The name of the script should beyour id number (E.g. if your id number is 12345, the name of your script file should be 12345.sql). A hard copyshould also be printed together with your solutions to Sections B and C and placed in the assignment box.

Question One

Write SQL create table statements to create all the tables in the LMS database according to the relational schemagiven above. Use appropriate data types, sizes and constraints. Use sequences to generate the primary keys of theEnrollment tables. Use default values where necessary.

Question Two

Write SQL insert statements to capture the following scenario in the order in which the events occur. Your insertstatements should allow automatic use of default values where required.

Four new users are created with the following information.

UserId Password ExpiryDate FirstName LastName Email Country
jSmith John!23 23 Dec 2017 John Smith [email protected] Trinidad
jAlfred [email protected] 23 Dec 2017 Jane Alfred [email protected] Trinidad
rLewis Ki!!3r 23 Dec 2017 Rick Lewis [email protected] Trinidad
aFisher [email protected]$%22 23 Dec 2017 Al Fisher [email protected] Trinidad

Two new courses are created

CourseCode CourseName OwnerUserId
COMP1111 Data Structures rLewis
COMP2222 Internet Security aFisher

John Smith, Jane Alfred and Rick Lewis are all enrolled as Students (Role Id: S001) intoCOMP2222.
Al Fisher is enrolled into COMP2222 as a teacher (RoleId : T001).
Rick Lewis is enrolled into COMP1111 as a teacher (RoleId: T001).

Question Three

Write SQL update statements to capture the following operations.
· Rick Lewis changed his password to ‘[email protected]
· Al Fisher changed his country of residence to Antigua.
· On the 25th September 2017, John Smith was unenrolled from COMP2222.

Question Four

Write SQL select statements for the following tasks
· Display the password for the user with userId jSmith.
· Display the names of all courses for which user jAlfred is enrolled.
· Display the course code and user id for all enrollments made after the 2nd September, 2017.
· Display the course name for all courses which have at least one user enrolled. A course code should not
appear more than once.

Section B

Question Five
Consider your Insert statement of Question 2 to enroll Al Fisher into COMP2222 as a teacher. Explain the
operations that the DBMS would perform to enforce Referential Integrity when the Insert statement is executed(explain exactly what tables/columns would be searched and what would happen if the check is
successful/unsuccessful).

Question Six

State whether each of the following statements is true or false.
i. A NOT NULL constraint should not be specified on the DateRemoved attribute in the Enrollment table.
ii. The following SQL statement is guaranteed to include all tuples from the role and enrollment tables
Select * from Role,EnrollmentWhere Role.RoleId = Enrollment.RoleId;
Choose the appropriate response for the following multiple choice questions.
iii. Which SQL capabilities are achieved by executing the following query: Select CourseName from Course;
a. Restriction only
b. Projection only
c. Restriction and Projection
d. Joining and Projection
iv. Which of the following queries is best suited for displaying only the first name and country of all users?
a. Select * from Userdetail;
b. Select Firstname, Lastname from Userdetail;
c. Select Firstname, Country from Userdetail;
d. Select Lastname, Email from Userdetail;
v. If an attribute is declared of data type number(4,2), when the user enters 45.375. What value will be stored?
a. 46
b. 45.00
c. 45.38
d. 45.37
e. This value cannot be stored using the current data type number declaration

Question Seven

Write an SQL statement to delete course COMP1111 from the Course table. Explain what actions the DBMS
performs when this statement is executed.
Question Eight

  1. Describe the relations that would be produced by the following relational algebra expression. Give the
    corresponding SQL Select statement; describe the intermediate relation that is formed, then the final output.
    Explain the concept of closure of relational operations
    PCourseCode, DateEnrolled (sUserId =’jSmith’ (Enrollment))
    b. Consider the following query
    Select UserId, Date EnrolledFrom User, EnrollmentWhere User.UserId = Enrollment.UserIdAnd DateRemoved is nullAnd ExpiryDate > to_date(’29-DEC-2017’,’DD-MON-YYYY’);
    Give two equivalent relational algebra expressions for the above query

SECTION C

Question Nine

  1. Draw and Entity Relationship Diagram using UML notation for the LMS database based on the schema
    provided. Show the minimum and maximum cardinalities on each side of all relationships.
    b. Consider the relationship between Course and Enrollment; carefully explain the 4 values which you have chosenfor the minimum and maximum cardinalities on either side of this relationship. 

Solution 

## 1

create table `User` (

userId VARCHAR(255),

password text(255),

expireDate date,

PRIMARY KEY (userId)

);

create table `UserDetail` (

userId VARCHAR(255),

firstName text(255),

lastName text(255),

email text(255),

country text(255),

PRIMARY KEY (userId)

);

create table `Course` (

courseCode VARCHAR(255),

CourseName text(255),

DateCreated date,

OwnerUserId VARCHAR(255),

PRIMARY KEY (courseCode),

FOREIGN KEY (OwnerUserId) REFERENCES `User`(`userId`)

);

create table `Role` (

RoleId VARCHAR(255),

RoleName text(255),

PRIMARY KEY (RoleId)

);

create table `Enrollment` (

EnrollmentId VARCHAR(255),

CourseCode  VARCHAR(255),

UserId  VARCHAR(255),

RoleId  VARCHAR(255),

DateEnrolled date,

DateRemoved date,

PRIMARY KEY (EnrollmentId),

FOREIGN KEY (CourseCode) REFERENCES `Course`(`CourseCode`),

FOREIGN KEY (UserId) REFERENCES `User`(`UserId`),

FOREIGN KEY (RoleId) REFERENCES `Role`(`RoleId`)

);

##  2

INSERT into `User`

VALUES(‘jSmith’, ‘John!23′, STR_TO_DATE(’23 Dec 2017′,’%d %b %Y’));

INSERT INTO `UserDetail`

VALUES (‘jSmith’,’John’, ‘Smith’, ‘[email protected]’, ‘Trinidad’);

INSERT into `User`

VALUES(‘jAlfred’, ‘[email protected]′, STR_TO_DATE(’23 Dec 2017′,’%d %b %Y’));

INSERT INTO `UserDetail`

VALUES (‘jAlfred’,’Jane’, ‘Alfred’, ‘[email protected]’, ‘Trinidad’);

INSERT into `User`

VALUES(‘rLewis’, ‘Ki!!3r’, STR_TO_DATE(’23 Dec 2017′,’%d %b %Y’));

INSERT INTO `UserDetail`

VALUES (‘rLewis’,’Rick’, ‘Lewis’, ‘[email protected]’, ‘Trinidad’);

INSERT into `User`

VALUES(‘aFisher’, ‘[email protected]$%22′, STR_TO_DATE(’23 Dec 2017′,’%d %b %Y’));

INSERT INTO `UserDetail`

VALUES (‘aFisher’,’Al’, ‘Fisher’, ‘[email protected]’, ‘Trinidad’);

— courses

INSERT INTO `Course`

VALUES (‘COMP1111’, ‘Data Structures’, now(),’rLewis’);

INSERT INTO `Course`

VALUES (‘COMP2222’, ‘Internet Security’, now(),’aFisher’);

INSERT INTO `Role`

VALUES (‘S001′,’Students’);

INSERT INTO `Role`

VALUES (‘T001′,’teacher’);

INSERT INTO Enrollment

VALUES (‘1’, ‘COMP2222′,’jSmith’,’S001′,now(),null);

INSERT INTO Enrollment

VALUES (‘2’, ‘COMP2222′,’jAlfred’,’S001′,now(),null);

INSERT INTO Enrollment

VALUES (‘4’, ‘COMP2222′,’rLewis’,’S001′,now(),null);

INSERT INTO Enrollment

VALUES (‘5’, ‘COMP2222′,’aFisher’,’T001′,now(),null);

 

INSERT INTO Enrollment

VALUES (‘6’, ‘COMP1111′,’rLewis’,’T001′,now(),null);

# 3

UPDATE `User`

SET `password` = ‘[email protected]

where userId = ‘rLewis’;

update `UserDetail`

SET `country` = ‘Antigua’

WHERE `lastName` = ‘Fisher’

UPDATE Enrollment

SET `DateRemoved` = STR_TO_DATE(‘2 Sep 2017′,’%d %b %Y’)

where UserId = ‘jAlfred’

# 4

SELECT `password`

FROM `User`

WHERE userId = ‘jSmith’;

SELECT c.`CourseName`

FROM `Course` c, `Enrollment` e

WHERE e.UserId = ‘jAlfred’

AND e.CourseCode = c.courseCode;

SELECT c.`courseCode`, e.`UserId`

FROM `Course` c, `Enrollment` e

WHERE e.DateEnrolled > STR_TO_DATE(‘2 Sep 2017′,’%d %b %Y’)

AND e.CourseCode = c.courseCode;

SELECT c.`CourseName`

FROM `Course` c, `Enrollment` e

WHERE e.CourseCode = c.courseCode;

# 5

/*

To enforce Referential Integrity the DBMS will do select statement

on the table users to unsure that the user jAlfred exist

*/

# 6

/*

  1. i) True, because this column can be null.
  2. ii) True

iii) a

  1. iv) c
  2. v) c

*/

# 7

delete FROM Enrollment

where  `courseCode` = ‘COMP1111’;

delete  FROM Course

where `courseCode` = ‘COMP1111’

/*

if we want delete just the course the DBMS will check if exist in Enrollment table

and will transaction will fail because the course COMP1111 exit in enrollement table

*/

# 8

/*

the relations mean:

selection from the table Enrollment of entries which have UserId =’jSmith’ and the

projection from this selection of the columns CourseCode, DateEnrolled

The corresponding SQL:

SELECT CourseCode, DateEnrolled

FROM Enrollment

WHERE UserId =’jSmith’

OutPut:

+————+————–+

| CourseCode | DateEnrolled |

+————+————–+

| COMP2222   | 2017-10-16   |

+————+————–+

PI CourseCode, DateEnrolled ( SIGMA UserId =’jSmith’ and DateRomoved=null and ExpiryDate > ’29-DEC-2017’

(Enrollment >-< Select))

>-< is EQUIJOIN operator

*/

# 9 b

/*

– From course side

minimum cardinality: 0 because the Course is not necessary has Enrollment

maximum cardinality: N because the number of Enrollments who has each Course is not

limited

– From Enrollment side

minimum cardinality = minimum cardinality: 1 Each enrolement should have one course.

*/