It244
Pg. 04
! ” ” Heading 1 .
Assignment #1
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:
SSN
Name
Phone
Age
234-356
Sara Fahad
340-675-9876
[emailprotected]
22
673-980
Mohammad Ahmed
234-896-2567
[emailprotected]
28
129-678
Waad Saud
280-986-8736
[emailprotected]
31
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]
