Download Database Design & Programming Examination, Cork Institute of Technology, Summer 2011 and more Exams Multimedia Applications in PDF only on Docsity!
CORK INSTITUTE OF TECHNOLOGY
INSTITIÚID TEICNEOLAÍOCHTA CHORCAÍ
Summer Examinations 2011
Module Title: Database Design & Programming
Module Code: SOFT 7002
School: Computing and Mathematics
Programme Title: Bachelor of Science in Information Technology Support – Year 3 Bachelor of Science (Honours) in IT Management – Year 2
Programme Code: KITSU_7_Y KITMN_8_Y
External Examiner(s): Mr. Aidan Quilligan Internal Examiner(s): Mr. Byron Treacy
Instructions: Answer all questions. Internal choice in questions 2 and 3.
Duration: 2 Hours
Sitting: Summer 2011
Requirements for this examination:
Note to Candidates: Please check the Programme Title and the Module Title to ensure that you have received the correct examination paper. If in doubt please contact an Invigilator.
Q1. Database Design 40 Marks Database design: normalisation:
You are to design a database for a company purchase requisition management system. A requisition is a list of items to purchase.
The company has a number of staff members. Staff members make requisitions for particular items from time to time The employees are given unique staffIds. The system stores the staff name, address and phone extension for the employees. The date of each requisition is recorded (as is the Staff member who made the requisition). Each department has a name (A,B,C etc) and a budget and administrator (admin). Each admin is a staff member so values in that column will be staff id values. Dept B currently has no admin assigned. For each requisition the list of items to purchase is recorded; each item has a set description and cost associated with it. NB. Descriptions are simple text fields, not reusable objects, e.g. I1 on R121 is Pens, but I1 on R125 is a Fan.
Requisition StaffID Name Dept Address PhExt ReqNo Item Desc Cost€ Date Admin Budget 132 Smith.J A Douglas 445 R121 I 1 Pens 20 12/12/09 132 12000 132 Smith.J A Douglas 445 R125 I 1 Fan 90 3/3/10 132 12000 145 Treacy.B B Cobh 666 R200 I 1 Mouse 20 2/5/2011 9500 145 Treacy.B B Cobh 666 R200 I2 Disk 100 2/5/2011 9500 132 Smith.J A Douglas 445 R125 I 2 RAM 50 3/3/10 132 12000
Note: you should have enough information to make a design but if you are confused about any aspect of the problem, discuss the confusing issue(s) and then proceed with a design.
a) Define a set of functional dependencies for the above application. b) Define a primary key for the requisition table above. c) Describe 3 processing anomalies/problems that may arise with the above requisition table i.e. 1 Insert, 1 update and 1 delete problem. d) Use normalisation to make a new design for the application that adheres to at least 3NF. e) Define primary keys in the new tables. f) Devise a UML diagram to model the above requisition system.
Q2. Answer 2 of the following 30 Marks (2 * 15)
2.1 Find the names of staff that have worked on more than one project.
2.2 Find the names of staff that work in admin roles on projects that have a budget more than 100,
2.3. Date data type: Write a note on the Date data type, and Explain why dates are important from a programming (SQL) perspective. What is a dialect (in relation to SQL) and explain how Date data types are linked to Dialect?
Q3. General Concepts: (Answer 2 of 3) 30 Marks (2 * 15)
3.1 Database Design: UML V’s Normalisation: Describe and contrast the two approaches to database design.
3.2 Btrees & Indexing: Explain how a Btree can be an index only or a full data file organsiation? Explain what is meant by a balanced Btree? Explain why Balanced Btrees offer good performance?
3.3 Database Systems Management Systems (DBMS) were developed to manage the data definitions used in an application. What are the benefits of centralised management of data definitions? DBS control (i.e. constrain) data definition in different ways. Using your experience of database table definition (i.e. the SQL Create table command), explain the different controls (constraints) that DBS designers can place on data. Comment on why these control/constraints are advantageous? i.e. why place all these constraints on data?
