SQL Tugas dan Jawaban


Perhatikan satu set relasi berikut:

STUDENT(snum: integer, sname: string, major: string, level: string, age: integer);
CLASS(name: string, meets_at: time, room: string, fid: integer);
ENROLLED(snum: integer , cname: string);
FACULTY(fid: integer, fname: string, deptid: integer);

Makna dari relasi-relasi di atas dapat diinterpretasi sendiri dengan mudah. Misalnya, Relasi ”Enrolled” memiliki satu record untuk setiap pasang ”student-class” sedemikian rupa sehingga student dengan identitas ”snum” enrolled (terdaftar) pada class ”cname”

1. Write the SQL statements required to create these relations, including approriate versions of all primary and foreign key constraints.
Jawaban:
CREATE TABLE Student (snum INTEGER,
sname CHAR(20),
major CHAR(20),
level CHAR(20),
age INTEGER,
PRIMARY KEY (snum))

CREATE TABLE Class (name CHAR(20),
meets_at TIME,
room CHAR(10),
fid INTEGER,
PRIMARY KEY (name),
FOREIGN KEY (fid) REFERENCES Faculty)

CREATE TABLE Enrolled (snum INTEGER,
cname CHAR(20),
PRIMARY KEY (snum, cname),
FOREIGN KEY (snum) REFERENCES Student,
FOREIGN KEY (cname) REFERENCES Class))

CREATE TABLE Faculty (fid INTEGER,
fname CHAR(20),
deptid INTEGER,
PRIMARY KEY (fnum))

2. Express each of the following integrity constraints in SQL unless it is implied by the primary and foreign key constraint; if so, explain how it is implied. If the constraints cannot be expressed in SQL, say no. For each constraint, state what operations (inserts, deletes, and updates on specific relations) must be monitored to enforce the constraint.

a) Every class has a minimum enrollment of 5 students and a maximum enrollment of 30 students.
Jawaban:
CREATE TABLE Enrolled (snum INTEGER,
cname CHAR(20),
PRIMARY KEY(snum, cname),
FOREIGN KEY(snum) REFERENCES Student,
FOREIGN KEY(cname) REFERENCES Class,
CHECK((SELECT COUNT (E.snum)
FROM Enrolled E
GROUP BY E.cname) >= 5),
CHECK((SELECT COUNT (E.snum)
FROM Enrolled E
GROUP BY E.cname) <= 30))

b) At least one class meets in each room.
Jawaban:
-

c) Every faculty member must teach at least two courses.
Jawaban:
CREATE ASSERTION 2Courses
CHECK((SELECT COUNT (*)
FROM Faculty F, Class C
WHERE F.fid = C.fid
GROUP BY C.fid
HAVING COUNT (*) 3) = 0)

e) Every student must be enrolled in the course called Math101
Jawaban:
CREATE ASSERTION Math101
CHECK((SELECT COUNT (*)
FROM Student S
WHERE S.snum NOT IN (SELECT E.snum
FROM Enrolled E
WHERE E.cname = ’Math101’)) = 0)

f) The room in which the earliest scheduled class (with the smallest meets_at value) meets should not be the same as the room in which the latest scheduled class meets.
Jawaban:
CREATE TABLE Class (name CHAR(20),
meets at TIME,
room CHAR(10),
fid INTEGER,
PRIMARY KEY (name),
FOREIGN KEY (fid) REFERENCES Faculty,
CHECK((SELECT MIN(meets_at)
FROM Class) (SELECT MAX(meets_at)
FROM Class)))

g) Two classes cannot meet in the same room at the same time.
Jawaban:
CREATE TABLE Class (name CHAR(20),
meets at TIME,
room CHAR(10),
fid INTEGER,
PRIMARY KEY (name),
FOREIGN KEY (fid) REFERENCES Faculty,
CHECK((SELECT COUNT (*)
FROM (SELECT C.room, C.meets
FROM Class C
GROUP BY C.room, C.meets
HAVING COUNT (*) > 1)) = 0))

h) The department with the most faculty member must have fewer than twice the number of faculty members in the department with the fewest faculty members.
Jawaban:
CREATE TABLE Faculty (fid INTEGER,
fname CHAR(20),
deptid INTEGER,
PRIMARY KEY (fnum),
CHECK((SELECT MAX (*)
FROM (SELECT COUNT (*)
FROM Faculty F
GROUP BY F.deptid))
10) = 0))

j) A student cannot add more than two courses at a time, in a single update statement.
Jawaban:
-

k) The number of CS majors must be more than the number of Math majors
Jawaban:
CREATE TABLE Student (snum INTEGER,
sname CHAR(20),
major CHAR(20),
level CHAR(20),
age INTEGER,
PRIMARY KEY (snum),
CHECK((SELECT COUNT (*)
FROM Student S
WHERE S.major = ’CS’)
>
(SELECT COUNT (*)
FROM Student S
WHERE S.major = ’Math’)))

l) The number of distinct courses in which CS majors are enrolled is greater than the number of distinct courses in which Math majors are enrolled.
Jawaban:
CREATE ASSERTION EnrolledCSMajors
CHECK((SELECT COUNT (E.cname)
FROM Enrolled E, Student S
WHERE S.snum = E.snum AND S.major = ’CS’)
>
(SELECT COUNT (E.cname)
FROM Enrolled E, Student S
WHERE S.snum = E.snum AND S.major = ’Math’))

m) The total enrollment in courses taught by faculty in the department with deptid=66 is greater than the number of Math majors.
Jawaban:
CREATE ASSERTION EnrolledGreaterThanMath
CHECK((SELECT COUNT (E.snum)
FROM Enrolled E, Faculty F, Class C
WHERE E.cname = C.name
AND C.fid = F.fid AND F.deptid = 33)
>
(SELECT COUNT (E.snum)
FROM Student S
WHERE S.major = ’Math’))

n) There must be at least one CS major if there are any students whatsoever.
Jawaban:
CREATE TABLE Student (snum INTEGER,
sname CHAR(20),
major CHAR(20),
level CHAR(20),
age INTEGER,
PRIMARY KEY (snum),
CHECK((SELECT COUNT (S.snum)
FROM Student S
WHERE S.major = ’CS’) > 0 ))

o) Faculty members from different departments cannot teach in the same room.
Jawaban:
CREATE ASSERTION BedaRuang
CHECK((SELECT COUNT (*)
FROM Faculty F1, Faculty F2, Class C1, Class C2
WHERE F1.fid = C1.fid
AND F2.fid = C2.fid
AND C1.room = C2.room
AND F1.deptid _= F2.deptid) = 0)

3. Perhatikan skema relasi di bawah ini (attribut yang digarisbawahi adalah primary keys). Attribut pidmgr menyatakan ID pegawai dari manajer suatu departemen (mengacu ke attribut pid pada relasi Pegawai). Tuliskan SQL trigger untuk mejamin bahwa contraint berikut berlaku dalam basis data di atas. Apabila gaji seorang manajer dinaikkan menjadi suatu nilai gaji tertentu, maka semua karyawan yang dipimpinnya harus dinaikkan sebesar 15% dari kenaikan gaji manajer tersebut. Selain itu, bujet dari departemen yang dipimpinnya harus ditambah dengan besarnya kenaikan gaji si manajer ditambah total kenaikan gaji dari semua pegawai yang dipimpinnya.
Pegawai (pid, nama, usia, gaji)
Bekerja (pid, did, tglbekerja)
Departemen (did, nama, bujet, pidmgr)

Jawaban:
SQL untuk membuat tabel:
CREATE TABLE Pegawai (pid integer,
nama varchar(10),
usia integer,
gaji decimal(9,2),
primary key (pid));

CREATE TABLE Departemen (did integer,
nama varchar(10),
bujet decimal(9,2),
pidmgr integer,
primary key (did),
foreign key (pidmgr) references pegawai(pid));

CREATE TABLE Bekerja (pid integer,
did integer,
tglbekerja date,
primary key (pid,did),
foreign key (pid) references pegawai(pid),
foreign key (did) references departemen(did));

SQL TRIGGER:
CREATE TRIGGER NaikGaji15Persen
AFTER UPDATE on Pegawai
Referencing old row as O, new row as N
When O.gaji < N.gaji
For each row
Begin
UPDATE Pegawai p
Set p.gaji=p.gaji+(15/100*(N.gaji-O.gaji)) where p.pid in (select b.pid from bekerja b, departemen d, pegawai p whereb.did=d.did and d.pidmgr=p.pid and d.pidmgr=N.pid) and p.pid N.pid);

About these ads

One response

  1. Am in contradiction, where to use create table command and where to apply create asertion

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: