1.5 Marks
                Learning     Outcome(s):
Create entity-relationship     model, relational model, and write SQL queries
 
 
 
 
 
 
      Question One

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]




   

                        
1 Mark
                Learning     Outcome(s):
Explain database concepts,     systems, and architectures
 
 
 
 
 
      Question Two

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



   

                        
1 Mark
                Learning     Outcome(s):
Design a database starting from     the conceptual design to the implementation of database schemas
 
 
 
 
 
      Question Three

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]


   

                        
1.5 Marks
                Learning     Outcome(s):
Create entity-relationship     model, relational model, and write SQL queries
 
 
 
 
      Question Four

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

  

SSN


Name


Phone


Email


Age

 

234-356


Sara Fahad


340-675-9876


[email protected]


22

 

673-980


Mohammad Ahmed


234-896-2567


[email protected]


28

 

129-678


Waad Saud


280-986-8736


[email protected]


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]