Database Management Systems(COP 5725)

HW1
Instructor: Dr. Daisy Zhe Wang

TA:
Kun Li, Yang Chen, Yibin Wang
kli, yang, yibwang@cise.uf l.edu
September 29, 2012

Name:
UFID:
Email Address:

Pledge(Must be signed according to UF Honor Code)

On my honor, I have neither given nor received unauthorized aid in doing this assignment.

Signature

Department of Computer and Information Science and Engineering

University of Florida

1
Exercise 1(Knowledge Questions)

1. What are the differences between logical data independence and physical data inde-
pendence in database?

• Logical data independence: Protection from changes in logical structure of data.

• Physical data independence: Protection from changes in physical structure of
data.

2. Briefly discuss the architecture of a DBMS.

• Query Optimization and Execution

• Relational Operators
• Files and Access Methods
• Buffer Management
• Disk Space Management

3. What are the basic design steps of a Database?

• Requirement Analysis
• Conceptual Database Design
• Logical Database Design
• Schema Refinement
• Physical Database Design
• Application and Security Design

4. Describe concisely but precisely the different levels of abstraction in a DBMS?

• External schema describes how users see the data

• Conceptual schema defines logical structure.
• Physical schema describes the files and indexes used.

5. What is the difference between Theata-Join and Outerjoin?

Theata-Join: the result does not contain those tuples that did not find a partner.
Outerjoin: the result contains those tuples that did not find a partner. The result
tuples are filled with null values.

2
 Exercise 2(Database design)

This problem is concerned with modeling of a database that contains information about
employees of academic institutions. A employee can either be a professor or a lecturer.

• For each employee, his/her id, name and year of birth.

• For each institution, its name, country, and inauguration year.

• For each institution, the names of its schools (e.g. School of Business, School of Com-
puter Science,. . .). A school belongs to exactly one institution.

• Information about authorships, i.e., which employee have authored a research paper.
The titles, ids of research papers should also be stored.

• For each employee, information on his/her highest degree (BSc, MSc or PhD), including
who was the main supervisor(must be a professor), and at what school.

• For each lecturer, information on the total number of courses he/she has taught.

• For each professor, information on his/her title and research projects (title, start date,
end date and the amount of grant money) in which he/she is a principal investigator.
Assuming that there is one but only one principal investigator for each project.

1. Draw an E/R diagram for the relations described above. Your diagram should not
contain redundant entity sets, relationships, or attributes. Also, use relationships
whenever appropriate.

2. Convert your E/R diagram into relations, and write SQL statements to create the rela-
tions. Make any reasonable choice of data types. Remember to include any constraints
such as primary key and foreign key constraints.

3
• Part 1)

ID Paper title

author

name birth name

ID Employee School name

HighDegree
ISA
at

Lecturer Professor title

country Institution year

ncourses
pi
name
grant start

title Project ID

end

• Part 2)
CREATE TABLE Employee (
ID int PRIMARY KEY,
name VARCHAR(50),
birth INT
);
CREATE TABLE Professor (
ID int REFERENCES Employee(ID),
title VARCHAR(50)
);
CREATE TABLE Lecturer (
ID int REFERENCES Employee(ID),
ncourses INT
);
CREATE TABLE Paper (
ID int PRIMARY KEY,
title VARCHAR(50)
);
CREATE TABLE Project (
ID int PRIMARY KEY,
title VARCHAR(50),
start DATE,
end DATE,
grant DOUBLE PRECISON );
CREATE TABLE School (
name VARCHAR(50),
instititionName VARCHAR(50) REFERENCES Institution(name),
PRIMARY KEY (name, instititionName)
);
CREATE TABLE Institution (
name VARCHAR(50),
country VARCHAR(50),
year INT,
PRIMARY KEY (name, country)
);
CREATE TABLE Author (
employeeID INT REFERENCES Employee(ID),
paperID INT REFERENCES Paper(ID),
PRIMARY KEY (employeeID, paperID)
);
CREATE TABLE Pi (
professorID INT REFERENCES Professor(ID),
projectID INT, PRIMARY KEY (projectID)
);
CREATE TABLE HighDegree (
professorID INT REFERENCES Professor(ID),
studentID INT REFERENCES Employee(ID),
schoolName VARCHAR(50),
institutionName VARCHAR(50),
FOREIGN KEY (schoolName,institutionName) REFERENCES School(name,insitutionName),
PRIMARY KEY (professorID, studentID, schoolName, institutionName)
);

5
 Exercise 3(Oracle SQL DDL and DML)

Consider the following ’car’ table which includes information about used cars managed
by a company. (dealer id, car id) is the key.

dealer id car id first name last name model year mileage price

0 1 Bill Gates dodge 2005 100000 5600

1 2 Larry Pages bmw 2008 20000 20000

2 3 Sergey Brin audi 2009 30000 21000

3 4 Tim Cook porsche 2005 80000 40000

4 5 Jeffrey Bezos honda 2002 70000 5000

5 6 Larry Ellison toyota 2008 60000 8000

6 7 Steve Jobs infinit 2005 80000 6000

Provide SQL statements for the following operations:

1. Assume a ’car2’ table which has the same schema, insert all the tupes in ’car’ table
into ’car2’ table.
insert into car2 select * from car;

2. Delete the records for dealer(s) with first name ’Larry’

Delete from car where first name = ’Larry’;
3. Update the price of cars owned by dealer(s) with last name ’Gates’ to 2000.
Update car set price = 2000 where last name = ’Gates’;
4. Create a new table called ’property’ which has schema: (dealer id, first name, last name,
total price). Then insert the data calculated from the car table to the new table using
one SQL clause.
create table property(dealer id int, first name varchar(20), last name varchar(20), to-
tal price double precision, primary key (dealer id));
insert into property
select dealer id, first name, last name, sum(price)
from car
group by dealer id, first name, last name;
5. What does this SQL statement return?
Select max(price)
From car
Where price < (select max(price) from car)
The second most expensive car

6
 Exercise 4(Relational Algebra)

Consider the following, self-explanatory UF CISE course database schemas: (primary

key attributes are underlined). Express the following queries in relational algebra assuming
set semantics.

• student(sid, sname, age, sex, department)

• course(cid, cname, instructor)

• record(sid, cid, grade)

1. Find names of all female students who have taken at least one of Wang’s courses.
πsname (σsex=0 f emale0 ∧instructor=0 W ang0 (student ./ record ./ course))

2. Find ids of the students who have taken two courses at least.
ρr1 (record)
ρr2 (record)
πsid (σr1.sid=r2.sid∧r1.cid6=r2.cid (r1 × r2))

3. Find names and ids of the courses taken by all the students.
πcid,cname (course ./ (πsid,cid (record)/πsid (student)))

4. Find ids of the students who are not CISE students but took all the CISE courses.
πsid,cid (σdepartment!=0 CISE 0 (record ./ student))/πcid (course)

5. Find id of the student who got the highest score in the database course cop5725.
ρR1 (σcid=0 cop57250 (record))
ρR2 (R1)
ρR3(1→sid,2→cid,3→grade) (σR1.score<R2.score (R1 × R2))
πsid (R2 − R3)

7
 Exercise 5(Oracle SQLs)

Consider the following, self-explanatory database schemas:

• books(bid,title,year)

• ratings(bid, cid, rating, time)

Primary key attributes are underlined. bid is the unique id for each book, cid is the unique
id for each customer. The time is the timestamp when the books are rated by the book
reviewer.

1. Find the most active customer and the number of books he/she reviewed.

2. Find the total number of distinct customers.

3. Find the top-5 rated books.

4. For each book give its bid and average rating.

5. Find all the peers of the customer with cid = 50 (well call him Alice). A peer for Alice
is a customer who has rated at least one book with exactly the same rating as Alice.
The list of peers should be in sorted ascending order on the cid.

1. SELECT cid, count(*)

FROM ratings
GROUP BY cid
HAVING count(*) >= ALL(SELECT count(*) FROM ratings GROUP BY cid);

2. SELECT count(distinct cid)

FROM ratings;

3. CREATE VIEW TopBooks AS

FROM( SELECT bid, avg(rating) as arating
FROM ratings
GROUP BY bid
ORDER BY avg(rating) DESC)
WHERE ROWNUM<=5;

SELECT books.bid, books.title

FROM books, TopBooks
WHERE books.bid = TopBooks.bid
ORDER BY books.bid;

8
4. CREATE VIEW BooksAvgRating AS
SELECT bid, avg(rating) as arating
FROM ratings
GROUP BY bid
ORDER BY avg(rating) DESC;
SELECT books.bid, BooksAvgRating.arating
FROM books LEFT OUTER JOIN BooksAvgRating
ON books.bid = BooksAvgRating.bid
ORDER BY books.bid;

5. SELECT DISTINCT r2.cid

FROM ratings AS r1, ratings AS r2
WHERE r1.bid = r2.bid AND r1.rating = r2.rating AND r1.cid = 50 AND r2.cid !=
50
ORDER BY cid;