Download Database Design & Programming Exam for IT Support and Management Programs and more Exams Multimedia Applications in PDF only on Docsity!
CORK INSTITUTE OF TECHNOLOGY
INSTITIÚID TEICNEOLAÍOCHTA CHORCAÍ
Summer Examinations 2012
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 2012
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: Design a database for a machine assembly company. 40 Marks
The company builds machines to order for its customers. A customer places an order for a machine to be assembled from a set of component parts i.e. the customer selects the parts required and the company assembles the machine to that specification. An order is identified by an OrdId. Each order can only have one type(spec) of machine, but the customer can specify the quantity of that machine to be made up in a given order e.g. I want 3 machines made to the desired spec of components (MachineQty) The system records that an employee (one) is assigned to do the assembly of the machine(s) in an order. An employee is identified by their PPS# and a given employee can have many orders assigned to him/her at the same time. The employee name (not unique) and date of birth (DOB) is stored. Each customer is allocated a unique CID, and their name (not unique) and address are stored. Note in the table given below the values are shortened just for display purposes; you can assume full address fields i.e. address is not a design problem. For each order, the list of component parts required is recorded; all parts are known and defined by a unique PartId, name (not unique) and price. Each machine can have a number of a partId used in the spec (PartQty) e.g. Order O200, 3 C3 cogs are used. Machine_Spec PPS# EName Client Address PartQty OrdId PartId CID PName Price DOB MachineQty 132 Smith.J EMC 1,Douglas,Cork 1 O121 P1 C1 Cog 20 1/1/70 12 132 Smith.J UCC 2,Wilton,Cork 2 O125 P1 C2 Cog 20 1/1/70 1 145 Treacy.B DIT 65 Crumlin,Dublin 3 O200 P1 C3 Cog 20 2/2/50 2 145 Treacy.B DIT 65 Crumlin,Dublin 2 O200 P23 C3 Cam 100 2/2/50 2 132 Smith.J UCC 2 Wilton Cork 1 O125 P2 C2 Wheel 50 1/1/70 1
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 Machine_spec application. b) Define a primary key for the Machine_spec table above. c) Describe 3 processing anomalies/problems that may arise with the above Machine_spec 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. Define primary keys in the new tables. e) Devise a UML diagram to model the Machine_Spec application.
Q3. General Concepts: (Answer 1 of 2) 30 Marks
3.1. Hashed file organisation Describe the Hashed file organisation When is a Hashed file suitable for a database application? Explain using SQL code. Give some SQL Select queries that a Hash file is NOT suitable for? Collisions are a problem for a hashed file organisation? Explain the term collision in a Hashed file; how can they occur?
3.2 Data storage: Explain how the data storage requirements of a database system are calculated? Choosing suitable data types are essential in efficient data storage for a database system. Explain. In standard database design, primary keys are chosen from the existing data attributes in a database application. However, on occasion the designer might decide to introduce a new column to act as a unique identifier. Discuss this statement in relation to data storage and performance.
The following data is a small sample of data for question 2. Your code must work for any possible set of data.
EMPS
Order_Details
Products PNo Pname Price DeptName P1 Dell Xp22 450 Computer P2 Acer 323 600 Computer P3 Hoover Fridge 500 Home P4 Red Sofa 750 Furniture P5 Apple P 870 Computer
PrsiNo EName DeptName E1 Jim Furniture E2 Byron Computer
E3 Frank Computer E4 Jane Home
Orders OrdNo CNo ODate Paid PrsiNo O1 C1 1/6/2009 Yes E O2 C1 10/2/2010 No E O3 C2 20/4/2010 No E O4 C1 30/1/2010 Yes E O5 C3 3/3/2009 Yes E
Ordno Pno Qty O1 P1 1 O1 P2 4 O2 P2 1 O2 P3 2 O2 P4 3 O2 P1 2 O3 P3 1 O4 P1 4 O5 P5 2
Customers CNo Cname Address Sex C1 B.Treacy London Male
C2 J.Jones Paris Female C3 B.Blake Paris Female C4 C.Clark London Male C5 A.Adams Athens Male
