Database
Pg. 02
Question Four
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. The answer should be in your own words.
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
sara@gmail.com
22
673-980
Mohammad Ahmed
234-896-2567
Moh_3@gmail.com
28
129-678
Waad Saud
280-986-8736
Wad2@hotmail.com
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]