(1) Install MySQL in your computer. You may select to install XAMPP instead. XAMPP contains a package of server software, including MariaDB, which can be considered as the same as MySQL in this course. XAMPP includes other useful software such as Apache, which is useful in later assignment. Install a toy university database modeled on top of the one from our textbook by executing the MySQL script for creating and populating the database: c4333u.sql.txt. Provide the MySQL commands for the following queries.

(a) List the last names and first names of students minoring in CSCI and having 'F110' as faculty advisor.

+----------+-----------+ | lastName | firstName | +----------+-----------+ | McCarthy | Owen | | Jones | Mary | +----------+-----------+ 2 rows in set (0.03 sec)

(b) List the last names, first names, number of credits, and faculty advisor id of students having 'F110' or 'F115' as faculty advisor.

+----------+-----------+---------+---------+ | lastName | firstName | credits | advisor | +----------+-----------+---------+---------+ | Smith | Tom | 90 | F115 | | Chin | Ann | 36 | F110 | | Lee | Perry | 3 | F115 | | McCarthy | Owen | 0 | F110 | | Jones | Mary | 42 | F110 | +----------+-----------+---------+---------+ 5 rows in set (0.00 sec) (c) List the last names and first names of students, the courses they have enrolled in, and their grades. Only list the results with a grade of A.

+----------+-----------+-------------+-------+ | lastName | firstName | classNumber | grade | +----------+-----------+-------------+-------+ | Smith | Tom | ART103A | A | | Lee | Perry | HST205A | A | | Burns | Edward | CSC201A | A | | Rivera | Jane | MTH101B | A | +----------+-----------+-------------+-------+ 4 rows in set (0.00 sec) (d) List the last names and first names of students, the courses they have enrolled in, the instructor ids of the courses, and their grades. Only list the results with grades of A or B.

+----------+-----------+-------------+-------+-------+ | lastName | firstName | classNumber | facId | grade | +----------+-----------+-------------+-------+-------+ | Smith | Tom | ART103A | F101 | A | | Lee | Perry | ART103A | F101 | B | | Burns | Edward | CSC201A | F105 | A | | Jones | Mary | CSC201A | F105 | B | | Rivera | Jane | CSC201A | F105 | B | | Rivera | Jane | MTH101B | F110 | A | | Chin | Ann | MTH103C | F110 | B | | Lee | Perry | HST205A | F115 | A | +----------+-----------+-------------+-------+-------+ 8 rows in set (0.00 sec) (2) A relation R(A,B,C,D,E) has exactly four candidate keys: CDE, ACDE, BCDE and ABCDE. State the candidate key(s).

(3) It is known that R(A,B,C,D,E) have two candidate keys A and BCDE. How many superkeys are there?

(4) Consider the relation

Enrollment(StudentId, CourseId, InstructorId, Grade).

It stores grades of courses students enrolled in, together with the id of the instructors of the courses. For example, ('S1', 'C101', 'F402', 'A') indicates that the studnet 'S1' has enrolled in the course 'C101', taught by instructor 'F402', and received a grade of 'A'. A student can take many courses but may not have grades yet (semesters not yet completed, for example). A course may have many students but may not have an official instructor.

(a) Can StudentId have a null value? (b) Can CourseId have a null value? (c) Can InstructorId have a null value? (d) Can Grade have a null value? (e) Show the candidate key(s).

Here is the sql file:

-- -- Create a very simplified university database -- modeled on Ricardo. -- -- Drop tables for house-keeping if necessary --

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

DROP SCHEMA IF EXISTS c4333u; CREATE SCHEMA c4333u; USE c4333u;

DROP TABLE IF EXISTS Enroll; DROP TABLE IF EXISTS Student; DROP TABLE IF EXISTS Class; DROP TABLE IF EXISTS Faculty; DROP TABLE IF EXISTS Department; -- CREATE TABLE IF NOT EXISTS Department( deptCode varchar(4), deptName varchar(20), CONSTRAINT Department_deptCode_pk PRIMARY KEY (deptCode), CONSTRAINT Department_name_ck UNIQUE (deptName) );

CREATE TABLE IF NOT EXISTS Faculty( facId varchar(6), name varchar(20) NOT NULL, deptCode varchar(4), rank varchar(10), CONSTRAINT Faculty_facId_pk PRIMARY KEY (facId), CONSTRAINT Faculty_deptCode_fk FOREIGN KEY (deptCode) REFERENCES Department(deptCode));

CREATE TABLE IF NOT EXISTS Class ( classNumber varchar(8), facId varchar(6) NOT NULL, schedule varchar(8), room varchar(6), CONSTRAINT Class_classNumber_pk PRIMARY KEY (classNumber), CONSTRAINT Class_facId_fk FOREIGN KEY (facId) REFERENCES Faculty (facId) ON DELETE CASCADE, CONSTRAINT Class_schedule_room_uk UNIQUE (schedule, room));

CREATE TABLE IF NOT EXISTS Student ( stuId varchar(6), lastName varchar(20) NOT NULL, firstName varchar(20) NOT NULL, major varchar(4), minor varchar(4) NULL, credits integer(3) DEFAULT 0, advisor varchar(6) NULL, CONSTRAINT Student_stuId_pk PRIMARY KEY (stuId), CONSTRAINT Student_credits_cc CHECK ((credits>=0) AND (credits < 150)), CONSTRAINT Student_major_fk FOREIGN KEY (major) REFERENCES Department(deptCode) ON DELETE CASCADE, CONSTRAINT Student_minor_fk FOREIGN KEY (minor) REFERENCES Department(deptCode) ON DELETE CASCADE, CONSTRAINT Student_advisor_fk FOREIGN KEY (advisor) REFERENCES Faculty(facId) );

CREATE TABLE IF NOT EXISTS Enroll ( stuId varchar(6), classNumber varchar(8), grade varchar(2), CONSTRAINT Enroll_classNumber_stuId_pk PRIMARY KEY (classNumber, stuId), CONSTRAINT Enroll_classNumber_fk FOREIGN KEY (classNumber) REFERENCES Class(classNumber) ON DELETE CASCADE, CONSTRAINT Enroll_stuId_fk FOREIGN KEY (stuId) REFERENCES Student (stuId) ON DELETE CASCADE);

INSERT INTO DEPARTMENT VALUES('CSCI','Computer Science'); INSERT INTO DEPARTMENT VALUES('MATH','Mathematics'); INSERT INTO DEPARTMENT VALUES('HIST','History'); INSERT INTO DEPARTMENT VALUES('ARTS','Arts'); INSERT INTO DEPARTMENT VALUES('PHYS','Physics'); INSERT INTO DEPARTMENT VALUES('MARK','Marketing');

INSERT INTO FACULTY VALUES('F101','Adams','ARTS','Professor'); INSERT INTO FACULTY VALUES('F105','Tanaka','CSCI','Instructor'); INSERT INTO FACULTY VALUES('F110','Byrne','MATH','Assistant'); INSERT INTO FACULTY VALUES('F115','Smith','HIST','Associate'); INSERT INTO FACULTY VALUES('F221','Smith','CSCI','Professor'); INSERT INTO FACULTY VALUES('F230','Johnson','PHYS','Associate'); INSERT INTO CLASS VALUES('ART103A','F101','MWF9','H221'); INSERT INTO CLASS VALUES('CSC201A','F105','TuThF10','M110'); INSERT INTO CLASS VALUES('CSC203A','F105','MThF12','M110'); INSERT INTO CLASS VALUES('HST205A','F115','MWF11','H221'); INSERT INTO CLASS VALUES('MTH101B','F110','MTuTh9','H225'); INSERT INTO CLASS VALUES('MTH103C','F110','MWF11','H225'); INSERT INTO CLASS VALUES('PHY333A','F230','MWF3','H225');

INSERT INTO STUDENT VALUES('S1001','Smith','Tom','HIST','MARK',90,'F115'); INSERT INTO STUDENT VALUES('S1002','Chin','Ann','MATH','PHYS',36,'F110'); INSERT INTO STUDENT VALUES('S1005','Lee','Perry','HIST','ARTS',3,'F115'); INSERT INTO STUDENT VALUES('S1010','Burns','Edward','ARTS','CSCI',63,'F101'); INSERT INTO STUDENT VALUES('S1013','McCarthy','Owen','MATH','CSCI',0,'F110'); INSERT INTO STUDENT VALUES('S1015','Jones','Mary','MATH','CSCI',42,'F110'); INSERT INTO STUDENT VALUES('S1020','Rivera','Jane','CSCI','MATH',15,'F105');

INSERT INTO ENROLL VALUES('S1001','ART103A','A'); INSERT INTO ENROLL VALUES('S1001','HST205A','C'); INSERT INTO ENROLL VALUES('S1002','ART103A','D'); INSERT INTO ENROLL VALUES('S1002','CSC201A','F'); INSERT INTO ENROLL VALUES('S1002','MTH101B','C'); INSERT INTO ENROLL VALUES('S1002','MTH103C','B'); INSERT INTO ENROLL(stuId,classNumber) VALUES('S1010','ART103A'); INSERT INTO ENROLL(stuId,classNumber) VALUES('S1010','MTH103C'); INSERT INTO ENROLL VALUES('S1020','CSC201A','B'); INSERT INTO ENROLL VALUES('S1020','MTH101B','A'); INSERT INTO ENROLL VALUES('S1020','MTH103C','C'); INSERT INTO ENROLL VALUES('S1005','HST205A','A'); INSERT INTO ENROLL VALUES('S1005','ART103A','B'); INSERT INTO ENROLL VALUES('S1005','CSC201A','C'); INSERT INTO ENROLL VALUES('S1010','CSC201A','A'); INSERT INTO ENROLL VALUES('S1015','CSC201A','B');

SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;