ISOM 3260 - Database Design and Administration
Final Exam Practice Questions

To access the final exam review, press here.

To access the suggested solution for the practice qustions, press here.

Section 1 - Logical Database Design (Relational Database)

Question 4-33

The following is an EER diagram for a simplified credit card environment. There are two types of card accounts: debit cards and credit cards. Credit card accounts accumulate charges with merchants. Each charge is identified by the date and time of the charge as well as the primaryd keys of merchant and credit card.

Translate the EER diagram into relations. Normalize all relations to third normal form. Show all functional dependencies, entity integrity, and referential integrity in the diagram.
Imgur

Question 4-35

The following is an EER diagram for a restaurant, its tables, and the waiters and waiting staff managers who work at the restaurant.

Translate the EER diagram into relations. Normalize all relations to third normal form. Show all functional dependencies, entity integrity, and referential integrity in the diagram.

Imgur

Question 4-44

The following is an EER diagram for Vacation Property Rentals. This organization rents preferred properties in several states. As shown in the figure, there are two basic types of properties: beach properties and mountain properties. Note that a main property can have several smaller property attached.

Translate the EER diagram into relations. Normalize all relations to third normal form. Show all functional dependencies, entity integrity, and referential integrity in the diagram.

Imgur

Question 4-47

The following is an EER diagram describing a publisher specializing in large edited works.

Translate the EER diagram into relations. Normalize all relations to third normal form. Show all functional dependencies, entity integrity, and referential integrity in the diagram.

Imgur

Question 5

Consider the following BookTransaction relation.

bookID bookShopID bookName bookShopName transactionID totalAmount

Draw the functional dependencies of the table.

Question 6

Ms Lee is organising a debating competition. She uses a relation DEBATE to sotre information on debates in the competition.

DEBATE

Field Name Description
MID Identity code of the debate
PROID Identity code of the pro team (argue in favour)
PRONAME Name of the pro team
PROM Mark of the pro team
CONID Identity of the con team (argue in opposition)
CONNAME Name of the con team
CONM Mark of the con team
DD Date of the debate
TD Topic of the debate

Some sample records are shown below:

MID PROID PRONAME PROM CONID CONNAME CONM DD TD
M01 T01 Tiger 80 T02 Rockets 92 10-MAR-18 Homework should be banned.
M03 T03 Dragon 95 T02 Rockets 60 17-MAR-18 Hong Kong should collect no tax.
M08 T04 Lucky 90 T03 Dragon 99 24-MAR-18 Money can buy you happiness.
\dots \dots \dots \dots \dots \dots \dots \dots \dots

a) Draw the functional dependencies of the table.
b) Convert the table into 2NF and draw the functional dependencies.
c) Convert the table into 3NF and draw the functional dependencies.

Question 7

A club uses a relation ROUTE to store information on hiking routes and sightseeing spots in country parks. Study the following data dictionary.

ROUTE

Field Name Data Type Primary Key Description Example
RID VARCHAR2(3) X Identity Code of the Route R02
RNAME VARCHAR2(30) Name of the Route The South
SID VARCHAR2(3) Identity Code of the Sightseeing Spot S40
SNAME VARCHAR2(50) Name of the Sightseeing Spot Aberdeen Reservior
SN NUMBER(1,0) X Sequence Number of the Sightseeing Spot on this Route. 0 represents the starting point. 0
DIFF NUMBER(1,0) Difficulty Level of the Route 4

a) Draw the functional dependencies of the table.
b) Convert the table into 2NF and draw the functional dependencies.
c) Convert the table into 3NF and draw the functional dependencies.

Section 3A - Structured Query Language - Data Definition Language (SQL - DDL)

Question 1

Create the relation(s) you’ve specified in question 4-33 of section 1. Specify the correct data type, domain constraints, entity intergrity constraints, and referential integrity constraints.

Question 2

Create the relation(s) you’ve specified in question 4-47 of section 1. Specify the correct data type, domain constraints, entity intergrity constraints, and referential integrity constraints.

Question 3

Aubrey wants to create the following table (FRIEND).

FRI_Name FRI_Gender FRI_DateOfBirth
Wise Ho M 18-APR-1988
Benny Kwan M 19-FEB-1999

Assuming that FRI_Gender can only take two values: M or F.

(a) Write a SQL statement to define the above table.
(b) Write SQL statements to insert the above two records.

Question 4

Study the following data dictionary excrept.
BOOK

Field Name Data Type Field Length Decimal Places Description
bookID Integer 6 / The primary key of the BOOK table
title Character 40 / The title of a book
price Decimal 5 1 The price of a book

(a) Write a SQL statement to define the above table.
(b) Write SQL statements to add the following fields to the table BOOK.

Field Name Data Type Field Length Decimal Places Description
weight Decimal 4 1 The weight of a book in gram
publishDate Date / / The date of publication of a book
numOfPage Integer 4 / The number of pages of a book
quantity Integer 3 / The number of books in stock

(c) Write a SQL statement to change the data type of the field bookID to character.

Below is the excrept of the same data dictionary on another table PUBLISHER that resides in the same database as BOOK.
PUBLISHER

Field Name Data Type Field Length Decimal Places Description
pID Character 6 / The primary key of the PUBLISHER table
pName Character 50 / The name of a publisher
pPhoneNo Character 8 / The phone number of a publisher
pAddress Character 100 / The address of a publisher
pContactPerson Character 255 / A contact person of a publisher

(d) Write a SQL statement to define the PUBLISHER table.
(e) Write SQL statements to alter the BOOK table and add a referential integrity constraint between BOOK and PUBLISHER.
(f) Write SQL statements to drop both BOOK and PUBLISHER tables.

Question 5

Given the following relation ALUMNI -

ALU_ID ALU_FirstName ALU_LastName ALU_GradYear \dots

Tim wants to copy the records of alumni graduated in 2016 and insert it into another relation ALUMNI_2016, which has the identical schema. Write an INSERT statement to complete this task.

Section 3B - Structured Query Language - Data Manipulation Language (SQL - DML) (Part 1)

For the following questions, refer to these relations. Not shown in the figure are data for an ASSIGNMENT relation, which represents a many-to-many relationship between FACULTY and SECTION. Note that values of the SectionNo column do not repeat across semesters.
Imgur

Question 6-37

Because of referential integrity, before any row can be entered into SECTION, the CourseID to be entered must already exist in COURSE. Write a SQL to modify the relevant table to enforce the foreign key.

Question 6-38

Write SQL data definition statements for each of the following questions:
(a) How would you add an attribute, Class, to STUDENT?
(b) How would you remove REGISTRATION?
(c) How would you change the FacultyName column from 25 characters from 40 characters?

Question 6-40

Write SQL statements to answer the following questions:
(a) Which students have an ID number that is less than 50000?
(b) What is the name of the faculty member whose ID is 4756?
(c) What is the smallest section number used in the semester 'I-2015'?

Question 6-44

Write SQL statements to answer the following questions (You are allowed to use subquery or equi-joining if necessary_:
(a) List the numbers of all sections of course ISM 3113 that are offered during the seumester I-2015.
(b) List the course IDs and names of all courses that start with the letters 'Data'.
(c) List the IDs of all faculty members who are qualified to teach both ISM 3112 and ISM 3113.
(d) Modify the query above in part c so that both qualifications must have been earned after the year 2005.
(e) List the ID of the faculty member who has been assigned to teach ISM 4212 during the semester II-2015.

For the following questions, refer to these relations. The dtabase tracks an adult literacy programme. Tutors complete a certification class offered by the agency. Studetns complete an assessment interview that results in a report for the tutor annd a recorded Read score. When matched with a student, a tutor meets with the student for one to four hours per week. Some students work wiht the same tutor for uears, some for less than a month. Other students change tutors if their learning style does not match the tutor’s tutoring style. Many tutors are retired and are available to tutor only part of the year. Tutor status is recorded as Active, Temp Stop, or Dropped.
Imgur

Question 6-45

(a) Write a SQL statement to show the number of tutors who have a status of Temp Stop.
(b) Write a sQL statement to show the tutors who are active.

Question 6-46

(a) What is the average Read score for all students?
(b) What is the minimum Read score for all students?
(c) What is the maximum Read score for all students?

Question 6-47

Without using sub-queries, list the IDs of the tutors who are currently tutoring more than one student.

Question 6-48

Without using sub-queries, what are the TutorIDs for tutors who have not yet tutored anyone?

Question 6-49

How many students were matched with someone in the first five months of 2015?

Question 6-51

Without considering records without an end date, how long had each student studied in the adult literacy programme?

Question 6-52

Which tutors have a Dropped status and have achieved their certification after 4-JAN-2015?

Section 3C - Structured Query Language - Data Manipulation Language (SQL - DML) (Part 2)

For the following questions, refer to these relations. Not shown in the figure are data for an ASSIGNMENT relation, which represents a many-to-many relationship between FACULTY and SECTION. Note that values of the SectionNo column do not repeat across semesters.
Imgur

Question 7-28

Write SQL statements to answer to following questions:
(a) What are the names of the course(s) that student Alvater took during the semester I-2015?
(b) List names of the students who have taken at least one course that Professor Collins is qualified to teach.
(c) How many students did Professor Collins teach during the semester I-2015?

Question 7-30

(a) Using joins, list the students who were not enrolled in any courses during semester I-2015.
(b) Using non-correlated subqueries, list the students who were not enrolled in any courses during semester I-2015.
(c) Using correlated subqueries, list the students who were not enrolled in any courses during semester I-2015.

Question 3

ABC school launches a programming competition. Any three students can form a team to join the competition. Each team is required to store information relating to the competition. Study the following data dictionary.

TEAM

Field Name Data Type Description Example
TID VARCHAR2(3) Primary Key. Identity Code of the Team T1
TNAME VARCHAR2(20) Name of the Team Happy
MIDC VARCHAR2(4) Identity Code of the Captain S001
MID1 VARCHAR2(4) Identity Code of the First Member S005
MID2 VARCHAR2(4) Identity Code of the Second Member S008

PTASK

Field Name Data Type Description Example
PID VARCHAR2(2) Primary Key. Identity Code of the Programming Task P1
TITLE VARCHAR2(20) Title of the Programming Task Bubble Sort
FAST VARCHAR2(3) Identity Code of the Fastest Team to Solve the Programming Task T1

RESULT

Field Name Data Type Description Example
PID VARCHAR2(2) Primary Key. Identity Code fo the Programming Task P1
TID VARCHAR2(3) Primary Key. Identity Code of the team T1
MARK NUMBER(3,0) Mark Obtained (from 0 to 100) 100

Write SQL statements to complete tasks (a) to (e) below.
(a) Assuming RESULT relation has been created. Alter RELATION to enforce the range check on MARK.
(b) PTASK indicates the fastest team at solving each programming task. List the titles of the programming tasks and the names of the corresponding fastest teams.
(c) List the total number of programming tasks whose titles contain the string 'sort'.
(d) List the results of all programming tasks including the tiles of the programming tasks, the names of the teams and the corresponding marks obtained which should be larger than 50. The list should be in ascending order of the title of the programming task and then in descemding order according to the mark obtained.
(e) List the names of the teams that are the fastest teams at solving more than one programming task.

(f) Suppose that TEAM contains 5 records and ABC school uses a SQL command for checking, as shown below:

SELECT T.MIDC FROM TEAM T
WHERE EXISTS (
	SELECT E.MIDC FROM TEAM E
	WHERE T.MIDC = E.MID1 OR T.MIDC = E.MID2
);
TID TNAME MIDC MID1 MID2
T1 Happy S001 S005 S008
T2 Love S010 S002 S007
T3 Winner S111 S010 S006
T4 Grace S008 S003 S011
T5 Song S009 S012 S016

(i) List the result produced by the SQL command.
(ii) Briefy explain the purpose of the SQL command.

Question 4

A charity organisation launches a series of volunteer activities for members to take part in. Three relations, MEM, ACT, and APP, are used to store relevant information.

MEM

Field Name Data Type Description Example
MID VARCHAR2(5) Identity Code of the Member 10001
MNAME VARCHAR2(60) Name of the Member Rita Lam

ACT

Field Name Data Type Description Example
VID VARCHAR2(3) Identity Code of the Activity V03
VNAME VARCHAR2(60) Name of the Activity ABC Hospital Flag Selling Day
VDATE DATE Date of Launching the Activity 25-NOV-2015
QUOTA NUMBER(3,0) Number of Volunteers Needed 30

APP

Field Name Data Type Description Example
MID VARCHAR2(5) Identity Code of the Member 10001
VID VARCHAR2(3) Identity Code of the Activity that the Member Applies For V03

Write SQL statements to complete tasks (a) to (d) below.
(a) List the identity codes of the activities whose names consist of 'flag selling'. The list should be in descending order of the date of launching the activity.
(b) List the names of the activities which were held from January to March in 2016.
(c) List the names of the members who have not applied for any activity.
(d) List the identity codes of the activities that the number of applications from members is less than the number of volunteers needed.