Part A: Multiple-choice Questions (2 marks each; 30 marks total)

Select the single best answer for each of the following questions.

1.   Program-data dependence is caused by

a.    file descriptions being stored in each database application.

b.   data descriptions being stored on a server.

c.    data descriptions being written into programming code.

d.   data cohabiting with programs.

2.   What does the following figure show an example of?

a.    a one-to-many relationship

b.   a strong entity and its associated weak entity

c.    a co-dependent relationship

d.   a double-walled relationship

3.   An entity that associates the instances of one or more entity types and contains attributes specific to the relationships is called a(n)

a.    associative entity.

b.   build entity.

c.    gateway entity.

d.   smush entity.

4.   In the following diagram, which answer is true?

a.    Each nurse can supervise one nurse, no nurses, or many nurses.

b.   Each nurse can be in charge of many care centres.

c  Each nurse works in more than one care centre.

d  Each nurse was discharged from its supervision responsibility.

5.   An entity cluster can be formed by

a.    deleting a supertype and its subtype.

b.   combining metadata.

c.    combining a strong entity and its weak entities.

d  deleting metadata.

6.   The entity integrity rule states that

a.    no primary key attribute can be null.

b.   referential integrity must be maintained across all entities.

c.    each entity must have a primary key.

d.   a primary key must have only one attribute.

7.   When all multivalued attributes have been removed from a relation, it is said to be in

a.    first normal form.

b.   second normal form.

c.    Boyce-Codd normal form.

d  third normal form.

8.   A PC configured to handle user interfaces with limited local storage is called a(n)

a.    fat client.

b.   thin client.

c.    light client.

d  overweight client.

9.   For a transaction to be consistent,

a.    it must run the same way all the time.

b.   it must tell the truth.

c.    it must run using the same amount of memory.

d  any database constraints that must be true before the transaction must also be true after the transaction.

10. An optimistic approach to concurrency control is called

a.    versioning.

b.   denormalization.

c  deadlock resolution.

d.   Happy Control.

11. A file organization where files are not stored in any particular order is considered a

a.    hashed file organization.

b.   hash key.

c.   multi-indexed file organization.

d.   heap file organization.

12. Which of the following factors drives the need for data warehousing?

a.    Businesses need an integrated view of company information.

b.   Informational data must be kept together with operational data.

c.    Data warehouses generally have better security.

d.   Reduce virus and Trojan horse threats.

13. A data mart is a(n)

a.    enterprise-wide data warehouse.

b.   smaller system built upon file processing technology.

c.    data warehouse that is limited in scope.

d.   generic online shopping site.

14. NoSQL focuses on

a.    avoidance of replication of data.

b.   minimizing storage space.

c  normalized data.

d  flexibility.

15. When an organization must decide on optimization and simulation tools to make things happen, the organization is using

a.    predictive analytics.

b.   descriptive analytics.

c.   prescriptive analytics.

d.   comparative analytics.

Part B: Short-answer Questions (70 marks total)

Answer each question.

1.   A real estate company uses a database to store information about customers, property, and contracts. The following relations are used in the database:

Customer: Customer number (unique), Name, Mailing address, Balance, Lawyer name, Discount

Contract: Customer number (unique), Agent number (unique), Property_ID (one per contract), Date of contract, Property addresses, Property value, Type, Number of rooms, Land size, Built size, Tax value)

The following functional dependencies apply.

customer_no ---> name

customer_no ---> mail_address

customer_no --->  balance

customer_no ---> lawyer_name

customer_no ---> discount

{customer_no, Agent_no} ---> property_id

{customer_no, Agent_no} ---> date_contract

property_id ---> property_address

property_id ---> value

property_id ---> type

property_id ---> number_rooms

property_id ---> land_size

property_id ---> built_size

property_id ---> tax_value

Transform these relations into 3NF. (Please use only the attributes described above. Do not add any new attributes).

2.   An airline company has a number of planes. The attributes of a plane include plane-id (unique),

name, and vendor. The airline company serves many destinations. The attributes of a destination

include destination-id (unique), name, flight-date. Each plane flies to one or more destinations, or it   may be used as backup, or it may be under maintenance and therefore not fly to any destinations. For each plane undergoing maintenance, the company records the name of the technician and the type of maintenance performed. A destination may be served by one or more planes. Each plane’s service

charges vary by the number of destinations the plane serves. The airline company maintains records of the service charges for each plane when it flies to a certain destination. At the end of each year,

the airline company applies a degenerating percentage to each plane. The airline company calculates each plane’s degenerating percentage based on the amount of service charges and the total hours of  flying for that plane.

Answer the following questions. (No diagram drawings are required.)

a.    List all the entities that need to be included in the ER diagram for this situation.

b.   List all the relationships that need to be included in the ER diagram for this situation.

c.    Identify which of the listed entities in your answer are association entities.

d.   Are there any weak entities? If yes, list them.

3.   Consider the following three relations:

Manager (manager_no, name, address, specialization, salary)

Employee (emp_no, name, address, rank, salary)

Project_assignment (proj_no, emp_no, duration)

Project (Proj_no, manager_no, name, location, budget)

Write the SQL queries corresponding to the following questions.

a.    Find the number of different employees who are assigned to projects.

b.   Find the managers’ average salary.

c.    List the name and number of projects supervised by each manager.

d  Show the names and salaries of managers who are managing projects with employees of rank “beginner” in ascending order of salary.

e.    Show the names of managers with total sum of budgets for projects they are managing.

f  Show the names and salaries of managers who are in charge of projects having more than 20 employees.