Question details

Fundamentals of Databases
$ 20.00

INTRODUCTION TO DATABASE (IT244)

ASSIGNMENT 1

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

Text Box: 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.                                                                        [0.50 Point]

 

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_idas the primary key of advisor. Suppose a student can have more than one advisor. Then, would s_idstill 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.

INTRODUCTION TO DATABASE (IT244)

ASSIGNMENT 1

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

Text Box: 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.                                                                        [0.50 Point]

 

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_idas the primary key of advisor. Suppose a student can have more than one advisor. Then, would s_idstill 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.

 

Available solutions