Database Management System Exam Questions and Solutions

Question 1 (15 marks)

Answer the following questions (250 words max/question).

1. Explain the concept of program-data independence, and explain how it is achieved in the database approach.
2. Briefly contrast and compare the following development approaches: the systems development life cycle and the prototyping
3. Discuss the difference between entity type and entityinstance.

Give a simple example of an E-R diagram for each of the following concepts:

1. Associativeentity
2. Weakentity
3. Unaryrelationship

As an independent consultant, you have a contract with Athabasca University to develop an application to support course administration. Read the following detailed description of this application, and complete the tasks listed below.

• A course has a unique course number and title, and is assigned to one or more areas of the Computer Science program. Each course has an instructor, at least one teaching or research assistant, an online conference whiteboard, a time (interval), and a capacity (maximum number of participants). A course need not be offered each term, but the challenge option is open throughout the entire year. A course may have some prerequisite courses, and a student is not allowed to register for a course if s/he has not fulfilled the prerequisites or their equivalent. In addition, a student who would like to challenge a course should have the course prerequisites or authorization from the course
• An instructor has a first name, last name, email address, phone number, fax number, and is assignedto several areas of expertise. Note that two different instructors might have the same (first and last) names, and they might share the same fax number.
• A student is given a unique student number. For each student, we want to record first name andlast

name, email address, phone number, postal address, and GPA. Again, the same names may belong to two different individuals. A student may be enrolled in several courses each term. For each enrolment, we want to record the grade. There are two types of student: graduate and undergraduate.

Undergraduate students have a study major, while graduate students have both a specialization and a thesis topic.

• A teaching assistant is a graduate student, and we need to record his/her first name, last name,year, and GPA, as well as how many courses (including the current one) the teaching assistant has already taught.
• A research assistant is a student for whom we want to record the usual basic information, plusthe number of years of work experience. The research assistant provides laboratory support for students, manages the conference whiteboard, and does some programming tasks.
• An area of study is described by its name. An area may be divided into several sub-areas. These areasare used to categorize instructors, as well as courses. For example, the area of game development consists of sub-areas such as Graphics, Networking, and Human-Computer Interaction; the area of E-Services Technology consists of Artificial Intelligence, Networking, and Distributed Systems.

Hint: For each relation without a unique attribute or combination of attributes, introducing an artificial primary key might be a good idea.

Design an ER diagram for this course administration system. Draw the complete ER diagrams, including all aspects discussed in the course. Clearly state any further assumptions made, but note that you must not override the specifications above.

For each of the following relations, identify the Normal Form(s) each relation satisfies, and transform it into 3NF.

1. Consider the relation STUDENT, where a student can have only one major: RELATION = STUDENT (StuID, StuName, Major), Primary Key ={StuID}.
2. Consider the relation EMPLOYEE, where an employee can have more than one specialization: RELATION = EMPLOYEE (EmpID, Name, Specialization), Primary Key ={EmpID}.
3. Consider the relation LEASE, where a person can live in only one building, and a building can charge only one rentalrate:

Relation= Lease (PersonID, BuildingID, Rent), Primary Key – {PersonID}.

Consider a one-relation database with the following attributes:

Employee number (emp_no), Date hired (date), Job title (job), Phone number (phone_no), Office number (office_no), Area (area), Salary (sal), project number (proj_no), Project budget (p_budget), Department number (dep_no), Department budget (d_budget), and Department manager employee number (mgr_emp_no). The following business rules apply:

• No employee can manage more than one department at a
• No employee can work in more than one department at a
• No employee can work on more than one project at a
• No employee can have more than one office at a
• No employee can have more than one phone at a
• No employee can have more than one job at a
• No project can be assigned to more than one department at a
• No office can be assigned to more than one department at a
• Department numbers, employee numbers, project numbers, office numbers, and phone numbers are all “globally”

• emp_no g phone, emp_no g office_no, emp_no g dep_no, emp_no gproj_no
• {emp_no, date} g job, {emp_no, date} gsal
• phone_no g office_no, office_nog area, office_no gdep_no
• proj_no g dep_no, proj_no gp_budget
• dep_no g mgr_emp_no, dep_no gd_budget
• mgr_emp_no gdep_no

Transform this relation into 3 NF. Justify any decomposition.

Consider the following relations:

Emp(E_id: integer, E_name: string, Age: integer, Salary: real) Works(E_id: integer, Dep_id: integer, affectation: date)

Dept(Dep_id: integer, Dep_name: string, budget: real, Manager_id: integer)

1. What referential integrity constraints exist between theserelations?
2. What are the options for enforcing these constraints when a user attempts to delete a Depttuple?

Consider the following EER diagram for the Royal Victoria Hospital (RVH) database.

Data volume and access for this diagram are as follows:

• There are 1 000 patients and 500 items yielding a total of 10 000 usage records in the
• There are 50 physicians and a total of 4 000 prescriptions in the
• There are 200 treatments in the
• There are 50 accesses per day for patient records; of these, 30 request access to both prescription and usage
• There are 20 accesses per day for physician records; of these, 20 request access to
• There are 50 accesses per day to item records; of these, 10 request access to usage
• There are 5 direct accesses per day for treatment
• Of the total accesses to prescription records, 20 request access to patients, 30 request access to physicians, and 35 request access to
• Of the total accesses to usage records, 10 request access to patients and 30 request access to

Draw a composite usage map for the RVH database.

Answer the following questions (250 words max/question).

1. What are the typical integrity controls performed in both data integrity and referentialintegrity?
2. Using an example for each situation, illustrate the three common situations that suggest relations should be denormalized.
3. What are the advantages and disadvantages of horizontal and verticalpartitioning?

Answer the following questions (250 words max/question).

1. What factors should be considered when choosing a fileorganization?
2. What is the purpose of clustering data in afile?
3. Compare hashed file organization versus indexed file organization. List two advantages of indexed over hashed, and two advantages of hashed over

Consider the following database:

Employee(emp-no, name, department, salary), ProjAssigned(emp-no, proj-no, worked-hours)

1. Write one SELECT SQL query to list the numbers and names of all employees with a salary greater than 66 000 who are assigned to projects, the projects they are assigned to, and the corresponding hours worked. Your list should be sorted by employee
2. Define indexes on selected attributes to speed up your query, and justify your
3. Write SQL queries to create the indexes you defined

Consider the following three relations: TRAVEL_AGENT (name, age, salary)

CUSTOMER (name, departure_city, destination, journey_class) TRANSACTION (number, cust_name, travel_agent_name, amount_paid)

Write SQL statements to answer the following questions

1. Compute the number of different customers who have a
2. Display the name of the oldest travel
3. List the total number of transactions for each travel agent. Consider only those transactions where the amount paid exceeds 1
4. Displaythe names and ages of the travel agents who have arranged journeys for customer “John Smith”, in descending order of age (use a subquery).
5. Displaythe names and ages of travel agents who have arranged journeys for customer “John Smith”, in descending order of age (do not use a subquery).
6. Display the age of travel agents who have arranged journeys for customer “John Smith” to “Ottawa” (use a subquery).
7. Display the age of travel agents who have arranged journeys for customer “John Smith” to “Ottawa” (do not use asubquery).
8. Display the names and salaries of all travel agents who did not arrange journeys for customer “John Smith”, in ascending order of salary.
9. Display the names of travel agents who have five or more
10. Display the names of all travel agents who have arranged at least ten journeys to“Ottawa”.

Consider the following dimensions, dimension attributes, and dimension sizes for Farm Coop-Insurance.

• Member(member_ID, Name, Address). On average, there are two members for each policy and item covered by the
• InsuredItem(Item_ID, Description, Coverage_Type). There is an average of ten covered items per
• CoopOffice(Office_ID, Address, Manager_name). Each policy is registered and managed by only one office of the
• Policy(Policy_ID, Type). The company has approximately one million policies at the present

Approximately five percent of these policies experience some change each month.

• Period(Date_Key, Fiscal_Period). The length of the fiscal period is one month. The decision system reports are supposed to be based on five years of
• Claim(Claim_ID, Claim_Description,Claim_Type).

The facts to be recorded for each combination of these dimensions are: Policy_Premium, Deductible, and Monthly_Claim_Total.

1. Design a star schema for this
2. Using the assumptions stated above, estimate the number of rows in the fact
3. Estimate the total size of the fact table (in bytes), assuming an average of 5 bytes per

Question 2 (15 marks)

Suggest an appropriate recovery technique that a database administrator could use to resolve each of the following situations.

1. A network disconnection occurs while a user is entering a transaction at an ATM bank
2. A disk drive fails during regular operations while a clerk is entering data about newly registered
3. The registration office at a university entered an incorrect amount for a student tuition payment. The error was discovered by the financial services department several weeks
4. The database administrator of a financial institution performed a full database backup, but forgot to activate the journalizing facility. Afterwards, data entry clerks at the financial institution entered transactions for two hours before the database became corrupt. It is discovered that the journalizing facility of the database has not been activated since the backup was

Suggest the most appropriate security measures for each of the situations described below.

1. The Western Union bank uses an electronic funds transfer (EFT) system to transmit sensitive financial data between its branches all over the world.
2. A fighter jet simulation company has set up an off-site computer-based training centre for the F35. The company wishes to restrict access to the site to authorized employees. Since each employee's use of the centre is occasional, it does not wish to provide the employees with keys to access the
3. A golf club uses a simple password system to protect its database. The club has created a new Web site to allow both its members and its employees to use the new Web-based system to access and update information. The club finds that it needs a more comprehensive security system to grant different privileges (such as read-only versus create or update) to different
4. A training centre at the Northern Technical University has experienced considerable difficulty with unauthorizedusers who access files and databases by appropriating passwords from legitimate 

The UBS broker company has a database server with three disks. Both the accounting and stock exchange applications share the same disk, and they are experiencing performance problems. Discuss potential reasons for the performance problem, and suggest how to reduce I/O contention.

Consider the concurrent execution of the following transactions.

List two problems that may occur from the concurrent execution of these two transactions.

The Edmonton International Airport would like to implement a database that will be used to keep track of airplanes, their owners, airport employees, and pilots. From the requirements for this database, the following information was collected.

• Each airplane has a registration number, is of a particular plane type, and is stored in a particular
• Each hangar is managed by an employee who supervises the maintenance services performed in that hangar.
• Each plane type has a model number, a capacity, and a
• Each hangar has a number, a capacity, and a
• The database keeps track of the owner of each plane, and the employees who have maintained each plane.
• The database keeps track of each airplane’s purchase
• Each maintenance service record is identified by a work code, and includes the employee who performed the service, the date and time of the service, and the number of hours the maintenance service
• Each plane undergoes service many times, and all its service records are
• An owner can be either a person or a
• A person can be an owner, a pilot, or an employee of the
• Each pilot has specific attributes, including license number and
• Each employee has specific attributes, including salary and shift
• The database stores social insurance number, name, address, and telephone number for all person entities.
• The database stores name, address, and telephone number for all corporation
• The database also keeps track of the types of plane each pilot is authorized to fly, and the types of plane each employee is qualified to

Draw an object-oriented diagram for the Edmonton International Airport database.

Study the information given below, and answer the questions.

For a long time, the Royal Victoria Hospital (RVH) worked with an information system that consisted of a mix of paper- based files and small independent databases developed within some departments. The new administration has created an information system (IS) department, and they hired you as information officer: head of the team in charge of the design and implementation of a new global information system. The following information was collected by the first team from the IS department, who conducted interviews with some of the hospital administration and staff to identify entity types for the hospital.

The hospital depends primarily on four groups of people: employees, physicians, patients, and volunteers. Of course, some common attributes are shared by all of these groups: person_ID (identifier), name, address, birth date, and phone number. Each group also has at least one unique attribute of its own. Employees have a date hired, volunteers have a skill, physicians have a specialty and a pager number, and patients have a contact date (date of the first contact with the hospital). Some people may belong to two or more of these groups at a given time (e.g., patient and volunteer).

Patient: A person who is either admitted to the hospital, or is registered in an outpatient program. One, and only one, physician is responsible for each patient. Patients are divided into two groups: resident and outpatient. Each outpatient is scheduled for zero or more visits. The entity visit has two attributes: date (partial identifier), and comments. Note that an instance of visit cannot exist without an outpatient owner entity. Only resident patients are assigned to a bed, and a bed may or may not be assigned to a patient.

Question 3 (20 marks)

Physician: A member of the hospital staff who may admit patients to the hospital, and who can administer medical treatments. A given physician can be responsible for zero or more patients at a given time.

A patient must be referred to the hospital by exactly one physician. A physician can refer any number of patients, or may not refer any patient.

Physicians may perform any number of treatments on behalf of any number of patients, or may not perform any treatment. A patient may have treatments performed by any number of physicians. For each treatment performed on behalf of a given patient by a particular physician, the hospital records the treatment date, treatment time, and results.

Employee: Any person employed as part of the hospital staff. Employees are subdivided into three groups: nurse, staff, and technician. Only nurse has the attribute certificate, which indicates a qualification (RN, LPN, etc.). Only staff has the attribute job class, and only technician has the attribute skill. Each nurse is assigned to one (and only one) care centre. Each technician is assigned to one or more laboratories.

Care centre: A treatment centre within the hospital. Examples of care centres are maternity, emergency, and cardiology. Attributes of care centre are name (identifier) and location. A care centre may have one or more nurses assigned to it. Also, one of the nurses assigned to each care centre is appointed nurse-in-charge. A nurse cannot be appointed nurse-in- charge of a care centre unless s/he has an RN certificate.

Each hospital employee is assigned to work in one or more care centre. Each care centre has at least one employee, and may have any number of employees. The hospital records the number of hours per week that a given employee works in a particular care centre. Each physician can be assigned to one or more care centres, and a care centre can have one or more physicians assigned to it.

Laboratory: A unit in the hospital where clinical tests (i.e., blood, urine, tissue, etc.) are performed to obtain information about the health of a patient. Attributes of laboratory include name (identifier) and location. A laboratory must have one or more technicians assigned to it.

Bed: A hospital bed that may be assigned to a resident patient who is admitted to the hospital. Each bed has a bed number, a room number, and a care centre ID. There may be no bed assigned to a care centre, or a care centre may have one or more beds assigned to it.

Item: Any medical or surgical item that is used in treating a patient. Each item has an item number, description, and unit cost.

A patient may optionally consume any number of items. A given item may be consumed by one or more patients, or may not be consumed. For each item consumed by a patient, the hospital records the date, time, quantity, and total cost (which can be computed by multiplying quantity by unit cost).

Treatment: Any test or procedure performed by a physician on behalf of a patient. Each treatment has a treatment ID, which consists of a treatment number and a treatment name.

1. Is the ability to model subtype/supertype relationships likely to be important in a hospital environment such as the RVH? (4marks)
2. Can the business rules paradigm, and the ability to easily define, implement, and maintain business rules, be used as a competitive advantage in a hospital environment such as the RVH? (4marks)
3. Do there appear to be any weak entities in the description of the data requirements in this project module? (4marks)
4. Drawan EER diagram to accurately represent this set of  State any assumption you had to make in developing the diagram. (14 marks)
5. Are there any universal data models that can be reused as a starting point for modeling RVH’s data requirements? (4 marks)

Use the relational schema of the EER diagram you developed in Module 1 to answer the following questions.

1. Should the RVH use normalization when designing its database? (3marks)
2. Why are entity integrity and referential integrity constraints of importance to the hospital?(3marks)
3. Map the EER diagram to a relational schema, and transform the relation into 3NF. (10marks)
4. Besides the 3NF relations, what additional types of information are required to create a physical database design? (3 marks)
5. Are there opportunities for horizontal or vertical partitioning of the database? Are there other opportunities to denormalize the relations of this database? If no, explain why? If yes, how might you denormalize the database? (3marks)
6. Suppose the date treatment performed was not entered. What procedures are required to handle the missing data? (3marks)
7. Consider the following query against the RVH

For each treatment performed in the past two weeks, list the physicians performing the treatment (grouped by treatment), and the number of times this physician performed that particular treatment, on that particular day. Order the list by treatment ID, and by reverse chronological order for each treatment ID.

Create secondary key indexes to optimize the performance of this query. State any assumptions. (5 marks)

Consider the following relations:

• Patients(pid, name, address, telephone,care_centre_id)
• Care_centres(cid, name, location,nurse_charge_id)
• Treatments(tid, patient_id, physician_id, treatment_name,date)
• Nurses(nid, name, care_centre_id, certificate_type, telephone,salary)
• Physicians(phid, name, pager_number, specialization,salary).

Use Oracle to complete the following tasks.

1. Create the tables that correspond to these relations in your Oracle home database. (6marks)
2. If not automatically created by the DBMS, create indexes corresponding to the primary and foreign (6 marks)
3. Populate these tables with some sample data, and write SQL queries that show the content of each table after entering the data. (6marks)
4. For some strategic decisions, the president of the hospital needs summary data about the care centres. For each care centre, s/he needs to know the number of nurses holding an RN certificate, as well as their total and average salaries. Does the following view answer the president’s request? If not, write the correct view that will satisfy the president’s request. (7marks)