ICT285 Databases Final Examination S2 2017
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)
2022-12-08