Assignment 1/5 CS 2005 Database Systems

National University of Computer & Emerging Sciences, Karachi
Assignment 1/5
CS 2005 Database Systems

           
Instructions:
•    There will be total 5 assignments. 
•    Each assignment has same weightage which is 2.5
•    Out of 5, the 4 best assignments will be considered.
•    In case of plagiarism, straight zero marks will be assigned.

Note: Consider the database state in Figure 1 (given below) for the following questions.

Question #01: Identify all possible Candidate Keys (except primary keys) in the given schema, stating any assumptions you make.

Question #02: Identify all the FOREIGN KEYS present in the given schema, stating any assumptions you make.

Question #03: Suppose that each of the following Update operations is applied directly to the database state shown in figure 1, discuss all constraints violated by each operation, if any?

•    Insert <SA9, 'Scott', 'Jeff', 'Clerk', 1, '1977-10-01', 58000, B007> into STAFF
•    Insert <CR56, '', '', '1990-12-07'> into REGISTRATION
•    Delete the Branch tuple with branchNo = ‘B002’
•    Delete the PrivateOwner tuple with the Name = ‘Tony Shaw’
•    Modify the clientNo of the VIEWING tuple with clientNo = ‘CR62’ to ‘CR97’
•    Modify the propertyNo attribute of the PropertyForRent tuple with propertyNo = ‘PA14’ to ‘PA16’
•    Modify the branchNo attribute of the PropertyForRent tuple with branchNo = ‘B007’ to NULL

Question #04: Write appropriate SQL DDL statements to define each of the following tables in the database with the specified additional constraints. The defined statements should execute the constraint efficiently when updates are applied to the database.

Staff 
No staff member should be older than 60 years as of today’s date
Salary must remain in between 9000 to 30000.
Every attribute of staff must have some valid value
PropertyForRent
‘House’ should be the default type for any property.
A property must at least have 3 rooms
PrivateOwner
telNo must be DISTINCT for every Owner
eMail must be DISTINCT for evert Owner
Viewing

Question #05: Write SQL Queries for each of the following:
a)    Make a list of all property numbers for properties that involve a Female Staff member.

b)    Retrieve all privateOwners who own a ‘House’
c)    Show the resulting salaries if every staff member working in ‘London’ is given a 15% raise
d)    Retrieve all the properties which were viewed in ‘May 2013’
e)    Retrieve all the staff members whose first name starts with ‘D’ or last name starts with ‘W’ 
 
Figure 1





Database Schema
Branch (branchNo, street, city, postcode)
Staff (staffNo, fName, IName, position, sex, DOB, salary, branchNo)
PropertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo,
staffNo, branchNo)
Client (clientNo, fName, IName, telNo, prefType, maxRent, eMail)
PrivateOwner (ownerNo, fName, IName, address, telNo, eMail, password)
Viewing (clientNo, propertyNo, viewDate, comment)
Registration (clientNo, branchNo, staffNo, dateJoined)