COMPSCI 351 : Fundamentals of Database Systems S1 C – Lab 01: Three-Schema Architecture, DDL and SQL
Computer Science
COMPSCI 351 S1 C – Lab 01
15 marks in total = 1.5% of the final grade
This lab has an attendance component of [3 marks]
1. What are the advantages of using a database solution as compared to the traditional
file storage mechanism? What is the role of DBMS to a database system?
[2 marks] 2. What is the ‘Three-Schema Architecture’? What is the difference between the ‘Three-
Schema Architecture’ and the ‘Three Tier Client-Server Architecture’?
[2 marks]
3. Using the phpMyAdmin tool, enter the following DDL statements under the ‘SQL’ tab to create the student-course database schema.
CREATE TABLE STUDENT (
Name VARCHAR(30) NOT NULL, Student_number INTEGER NOT NULL, Class CHAR NOT NULL,
Major CHAR(4),
PRIMARY KEY (Student_number) );
CREATE TABLE COURSE ( Course_name VARCHAR(30) NOT NULL, Course_number CHAR(8) NOT NULL, Credit_hours INTEGER,
Department CHAR(4),
PRIMARY KEY (Course_number), UNIQUE (Course_name) );
CREATE TABLE PREREQUISITE (
Course_number CHAR(8) NOT NULL,
Prerequisite_number CHAR(8) NOT NULL,
PRIMARY KEY (Course_number, Prerequisite_number),
FOREIGN KEY (Course_number) REFERENCES COURSE (Course_number), FOREIGN KEY (Prerequisite_number) REFERENCES COURSE (Course_number) );
CREATE TABLE SECTION (
Section_identifier INTEGER NOT NULL,
Course_number CHAR(8) NOT NULL,
Semester VARCHAR(6) NOT NULL,
Year CHAR(4) NOT NULL,
Instructor VARCHAR(15),
PRIMARY KEY (Section_identifier),
FOREIGN KEY (Course_number) REFERENCES COURSE (Course_number) );
CREATE TABLE GRADE_REPORT ( Student_number INTEGER NOT NULL, Section_identifier INTEGER NOT NULL, Grade CHAR,
PRIMARY KEY (Student_number, Section_identifier),
FOREIGN KEY (Student_number) REFERENCES STUDENT (Student_number), FOREIGN KEY (Section_identifier) REFERENCES SECTION (Section_identifier) );
Populate the above database with the following DML statements under the ‘SQL’ tab.
INSERT INTO `STUDENT` (`Name`, `Student_number`, `Class`, `Major`) VALUES ('Smith', 17, '1', 'CS'),
('Brown', 8, '2', 'CS');
INSERT INTO `COURSE` (`Course_name`, `Course_number`, `Credit_hours`, `Department`) VALUES
('Intro to Computer Science', 'CS1310', 4, 'CS'),
('Data Structures', 'CS3320', 4, 'CS'),
('Discrete Mathematics', 'MATH2410', 3, 'MATH'), ('Database', 'CS3380', 3, 'CS');
INSERT INTO `SECTION` (`Section_identifier`, `Course_number`, `Semester`, `Year`, `Instructor`) VALUES
(85, 'MATH2410', 'Fall', '07', 'King'),
(92, 'CS1310', 'Fall', '07', 'Anderson'),
(102, 'CS3320', 'Spring', '08', 'Knuth'), (112, 'MATH2410', 'Fall', '08', 'Chang'), (119, 'CS1310', 'Fall', '08', 'Anderson'), (135, 'CS3380', 'Fall', '08', 'Stone');
INSERT INTO `GRADE_REPORT` (`Student_number`, `Section_identifier`, `Grade`) VALUES
(17, 112, 'B'),
(17, 119, 'C'),
(8, 85, 'A'), (8, 92, 'A'), (8, 102, 'B'), (8, 135, 'A');
INSERT INTO `PREREQUISITE` (`Course_number`, `Prerequisite_number`) VALUES ('CS3380', 'CS3320'),
('CS3380', 'MATH2410'),
('CS3320', 'CS1310');
You can download the “Lab01_Q3_Database.sql” file and execute the table creation and data population statements to obtain the above database state.
(a) Show content of the database state (i.e., the screenshot of each table displayed by the PhpMyAdmin tool).
The syntax of a basic database DML query statement is as follows.
[2 marks]
For example, the query statement to retrieve all the course information offered by the Computer Science department is as follows.
SELECT Course_name, Course_number, Credit_hours FROM COURSE
WHERE Department='CS'
The query output as the screenshot by the PhpMyAdmin tool is:
Based on the query syntax and the example, answer the following questions and show the query output as screenshots similar to the above.
- (b) Retrieve all the section information taught by the instructor 'Anderson'.
[2 marks]
- (c) Retrieve all the grade and section information of the student with the ID number '8'.
[2 marks]
- (d) Retrieve the perquisite course information of the course ‘CS3380’. Please display all the course information, such as the course number, name, credit and offering department.
[2 marks]
