It244

Deadline: Tuesday 29/09/2020 @ 11:59

[Total Marks for this Assignment are 5]

Introduction to Database

IT244

College of Computing and Informatics

Question One

1.5 Marks

Learning Outcome(s):

Create entity-relationship model, relational model, and write SQL queries

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 city/cities and “isbn” 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]

Question Two

1 Mark

Learning Outcome(s):

Explain database concepts, systems, and architectures

What is the different between the database schema and the database instance? Give an example for each.

Question Three

1 Mark

Learning Outcome(s):

Design a database starting from the conceptual design to the implementation of database schemas

Name at least three tables that might be used to store information in the following systems:

a. An e-commerce system such as Amazon. [0.25 Point]

b. A social media system such as Twitter. [0.25 Point]

c. A health care system. [0.25 Point]

d. A university system. [0.25 Point]

Question Four

1.5 Marks

Learning Outcome(s):

Create entity-relationship model, relational model, and write SQL queries

Given the relation below named Contact_Info, answer the following questions:

a. What is the relational schema of the relation Contact_Info? [0.25 Point]

b. What is the degree of the giving relation? [0.25 Point]

c. Is the column Name an atomic attribute/value? If no, justify your answer? [0.25 Point]

d. What are the outputs of the given operations?

1. (Contact_Info) [0.50 Point]

2. (Contact_Info) [0.25 Point]