Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

ICT285

Databases

Final Examination

S2 2017

QUESTION 1 (20 MARKS)

1.         (a)       List ONE(1) advantage and ONE(1) disadvantage of the relational database model compared with the older navigational (hierarchical and network)         database models.

(b)       Codd listed 8 services that should be provided by any full-scale DBMS. List

any FOUR (4) of these services. (4 marks)

2.        Compare and contrast the responsibilities of the data administrator (DA) and database administrator (DBA) roles in a large organisation. (4 marks)

3.         Define each of the following terms, and give examples of your own to illustrate them:

•   entity integrity constraint

•   referential integrity constraint

•   logical data independence (6 marks)

4.         (a)       Explain the difference between Business Intelligence (BI) systems and operational database systems.

(b)       What is data cleaning (data cleansing), and why is it an essential part of

preparing data for a data warehouse? (4 marks)

5.         Describe in general terms what NoSQL databases are, and explain the processing needs that they have arisen to address. (2 marks)

QUESTION 2 (20 MARKS)

1.        This question is based on the relations shown below:

PROPERTY

LEASE

PropertyNo

City

P1

P2

P3

Perth

Melbourne

Sydney

(a)       Write a relational algebra expression that lists the lease start and lease end

dates for all the properties located in the city of Perth.

Show the relations (structure and data) that would result from the following relational algebra expressions on the relations below:

(b)       PROPERTY LEFT JOIN Property.PropertyNo=Lease.PropertyNo LEASE

(c)       PropertyNo (PROPERTY)   INTERSECT  PropertyNo (LEASE) (6 marks)

2.        A company called FastCabs provides a taxi service to clients. The table below           displays some details of client bookings for taxis. A taxi driver is assigned to a single taxi, but a taxi can be assigned to more than one driver.

JobID

JobDateTime

DriverID

DriverName

TaxiID

ClientID

Client

Name

Pickup

100

25/07/15 10:00

D1

Joe Bull

T1

C1

Ann Woo

1 Storrie Rd

200

29/07/15 10:00

D1

Joe Bull

T1

C1

Ann Woo

1 Storrie Rd

300

30/07/14 11:00

D2

Tom Win

T2

C1

Ann Woo

3 High St

400

2/08/15 13:00

D3

Jim Jones

T3

C2

Mark Tim

1 Lady Lane

500

2/08/15 13:00

D4

Steven Win

T1

C3

John Seal

22 Red Rd

600

25/08/15 10:00

D2

Tom Win

T2

C4

Karen Bow

17 High St

(a)       What is the candidate key of the relation?

(b)       What normal form is the relation in? Explain your reasoning.

(c)       Convert the relation to a set of relations each in Third Normal Form (3NF). Indicate all primary keys and foreign keys. (6 marks)

3.         This question is based on the tables listed below:

PILOT

PilotCode

LastName

License

AGT

ART

BDK

GPK

Tannenbaum Tallahoga      Krooms          Komenski

COM

ATP

ATP

ATP

FLIGHT

AIRCRAFT

Aircraft

Code

AircraftType

Total

Hours

1484P

2289L

2778V

4278Y

PA-23-250 Aztec

C-90A King Air

PA-31-350 Navajo

PA-31-350 Navajo

10796

5294

9211

3487

CharterDate

PilotCode

AircraftCode

Destination

26- May-2017

28- May-2017

06-June-2017

12-June-2017

BDK

ART

BDK

GPK

2289L

2778V

2289L

1484P

STL

TYS

ATL

MQY

Give SQL statements for each of the following operations.

(a)         List the PilotCode and LastName of all pilots who piloted a flight during

May 2017.

(b)         List each type of aircraft, and the total number of aircraft of that type.

(c)         Change aircraft 1484P’s total hours to 13000.

(d)         Delete any pilots that have never piloted a flight. (8 marks)

QUESTION 3 (20 MARKS)

1.        Create an entity-relationship diagram (ERD) based on the following description. Use the crow’s feet notation that we used during this semester.

A real estate company advertises its properties for sale in various newspapers. The      company stores information about the newspapers they deal with, including contact name and phone. They also store information about the advertisement itself, including the date it appeared, the newspaper it was in, its cost, and the total number of inquiries received from the advertisement. Properties may be advertised in several newspapers  on the same day, and may be advertised in the same newspaper on more than one day. Property information stored by the real estate company includes the property number, location, and staff member who is dealing with the sale. Each staff member may deal  with several properties, but a property is managed by only one staff member. Staff information includes their staff number, name and contact phone.

Your ERD should show all entities, attributes, relationships, maximum cardinality and minimum cardinalty (participation). Indicate all primary keys and foreign keys. Include a legend to your diagram. (8 marks)

2.         (a)       What is the purpose of the physical design phase of database design? List two activities involved in physical design.

(b)       The database administrator often needs to make decisions about adding

indexes on table attributes to improve query processing effiency.

•   Which attributes (apart from the primary key and foreign key) are likely to BENEFIT from being indexed?

•   Which attributes would you generally AVOID indexing?

(c)       A derived attribute can be stored in a table or calculated on each occasion that it is required. What factors should be taken into account in making the

decision whether to store or calculate derived data? (6 marks)

3.        Views are virtual’ tables unlike the base tables they are derived from, they do not contain any actual data.

(a)       List two uses for views.

(b)       What is meant by saying a view is updateable(4 marks)

4.         Give an SQL statement that would give the user group STUDENTS the ability to read, update and add records to the table ARTIST. (2 marks)

QUESTION 4 (20 MARKS)

1.         Define the following terms, as they relate to transaction management:

•   Atomicity

•   Serialisable schedule

•   Shared lock

•   Durability (4 marks)

2.        There are still 10 seats left for a concert. Ticket agent A reads the number of available seats and makes a reservation of 6 seats for a customer. At the same time ticket agent B, in another office, reads the available seats and makes a reservation of 5 seats for     another customer.

(a)       Use this example to explain how the lost update problem may occur when

transactions are processed concurrently.

(b)       Use the same example to define the two-phase locking (TPL) protocol and

explain how it can be used to prevent the lost update problem. (4 marks)

3.         (a)       What is indicated by the entry commit in the transaction log?

(b)       Explain how the transaction log can be used to recover the database following

a failure involving loss of volatile memory.

(c)       What is a checkpoint in the transaction log? How is it used to streamline recovery? (6 marks)

4.         (a)       List and describe at least TWO (2) potential advantages and TWO (2)             challenges involved in a distributing a database across a number of locations.

(b)       Explain what is meant by transparency in the context of distributed DBMSs.

List TWO (2) types of DDBMS transparency. (6 marks)