INTRODUCTION TO DATABASE (IT244)

ASSIGNMENT 1

(LAST DATE TO SUBMIT: 20/02/2016)

Q1: Consider the below schema for a library database and write relational algebra expressions for the following queries

1.Author ( authorname, citizenship, birthyear)

2.Book (isbn, title, authorname)

3.Topic (isbn, subject)

4.Branch (libname, city)

5.Instock (isbn, libname, quantity)

1.Give all authors names born after 1940. [0.25 Point]

2.Give the names of libraries in Sydney. [0.25 Point]

3.Give the cities where each book is held. [0.25 Point]

4.Give the title of each book on the topic of either alcohol or drugs. [0.25 Point]

5.Give the title and author of each book of which at least two copies are held in a branch located in Melbourne.

Q2. What are the responsibilities of a DBA? If we assume that the DBA is never interested in running his or her own queries, does the DBA still need to understand query optimization? Why? [1.5 Marks]

Q3: What are the drawbacks of using file system over database management system? [1 Marks]

Q4: Consider the advisor relation shown in Figure 2, with s_id as the primary key of advisor. Suppose a student can have more than one advisor. Then, would s_id still be a primary key of the advisor relation? If not, what should the primary key of advisor be? [1 Marks]

Schema Diagram for University Database.