Database Lab 2

Due as specified on course webpage.

The timetable in the jensen database

The database named jensen has some tables (relations) that are filled with some data taken from the Bishop's website, with courses in CS for Winter 2020.

I have kept what was there for "Lab 0" since you may be enrolled in some, updated the timetable for cs307 and added current courses cs 203 and 211.

The schema of these relations is:

	Courses (class, credits, days, time, bldg, roomno, title, prof)
	Profs   (pname, given, office, ext, email)	
        Student (name, given, linux,  email)
	Enroll  (linux, class, grade)
	Dayx	(days, dayo, day, jour)   -- ex: MWF | 1  | Mon  | lundi

All attributes are CHARACTER, except for course, credits, grade, dayo and ext, which are integer. Primary keys are underlined.
The primary key for Students is your linux account code, since we all have them, and they are unique.
In Enroll, linux references Students(linux) and course references Courses(course).

DAYX is a relation you may find useful in creating a schedule, it gives for each "days" value observed, a relation to the days of the week, in numerical order, english, and french.

A view is simply a named, stored query, and by default is not updatable. (It is "read only.")
Finally, a view will be useful in question 7, to order your class schedule, it is based on DAYX and courses. It includes a union of courses and labs, sort of. (use \d+ schedule to see the actual definition)

	schedule (class, day, time, bldg, room, dayo, title)

INSERT

The syntax of the insert statement is (examples follow)

INSERT INTO table(column,...) VALUES ( ... );

INSERT INTO enroll VALUES ('mickey', 307, 66);
INSERT INTO enroll (class, linux) VALUES (307,'mickey');

The first example assumes the order of columns in the table. String values must be in single quotes. When using psql, you can type on many lines, and end the query with ; (semicolon)

Introduction to SQL queries

SQL is not case sensitive. The basic form of the select statement is:

SELECT columns     -- or * for all columns.  By the way, -- means the rest of a line is a comment.
FROM   table
WHERE condition

String constants must be enclosed in single quotes, and are case sensitive. For example, to find O'Hara, or courses in CSC, write

WHERE name = 'O''Hara'
WHERE code LIKE 'CSC%'

Note that you have to double the apostrophe, so as not to confuse the SQL interpreter.

Using psql:

Open a terminal, and start the client with the command psql jensen
psql commands:
- \d to see a list of tables, etc.
- \d people to see the structure of the table people
- \h NAME to get help on some sql statement, such as SELECT, INSERT, UPDATE
- \e to edit the last statement. This is easier for a multi-line statement than using up-arrow
- \q to quit the monitor
SQL statements, here is an outline of the various clauses of SELECT. You can type any sql statement in psql, they can cover several lines, and end with a semicolon (;). Up-arrow recalls previous lines.
In this outline, keywords are bold, optional things are in [square brackets] and one or more things in {curly brackets}

select {list of attributes and aggregate functions}
from tablenames
[where {conditions} ]
[group by
[having {conditions on the grouping} ]]
order by {column(s)} [descending]

JOIN constructs. {You can't use the first form for "outer" joins) for the others, inner join is the default

from people, phone where people.id = phone.id [and ...]
from people [left | right | full] join phone on (people.id = phone.id)
from people [...] join phone using (id)
from people natural [...] join phone
from (student natural join enroll) natural join courses
courses left join labs using(course) -- to get also courses without labs Natural join won't work, Why?

SUBQUERIES can be placed in the where clause, for example

where linux in (select linux from student where name='Mouse')
where not exists (select * from enroll where enroll.course = courses.course)
-- this is a co-related subquery, which must be reevaluated for each row of the outer query, involving courses

Do (at least) these queries:

In order to give you credit for the lab, please select and copy from the terminal window, and paste into a text file, all the queries (SQL), and results for those marked (copy). Suitable editors are Mousepad,, kwrite (in Applications|Accessories), xemacs, vi and nano - the simplest terminal editor. If you insist on using a word processor, use the normal extension for it, such as .abw, .odt, etc, not .txt!
Keep your text file to less than 220 lines (4 pages).
Submit it to me, instructions are at the end of this writeup.

Insert yourself into the Students table and Enroll yourself into at least 2 courses, if you have not already done so in earlier labs.
Show all the profs (Just the names) that appear in courses. Do this in alphabetical order. Get rid of the duplicates (DISTINCT)
Show course, days, time, room, title for all courses taught by Bentabet
List all the students enrolled in 307, ordered by (family) name. include all data about them that's in the student table
(copy) List all the students enrolled in 'Computer Organization' bearing in mind that this course has several course codes (include this, as well as all the information and ordering of the last question.
(copy) List all the courses that you are enrolled in, with course code, title, prof.
(copy)List your whole class schedule, giving course code, title, day, time and room. Order by dayo and time. The relation DAYX, or view SCHEDULE will help you with this.

Completion of lab:

By Friday at the latest, please.

At the end of this lab, please submit the text file into which you will have pasted the required queries and the 3 results I asked for. You may also put in any comments you think are appropriate:

Please put your name at the beginning of the file, so when printed I can identify it later! Give it to me,

At a linux command prompt (terminal): OR email it to me.

submit cs307 filename

Prepared by Lin Jensen. back to Database index
Last Modified