FACULTY OF BUSINESS SAMPLE EXAMINATION ITC114 DATABASE SYSTEMS MANAGEMENT DAY & DATE: WRITING TIME: Two (2) hours MATERIALS SUPPLIED BY UNIVERSITY: MATERIALS PERMITTED IN EXAMINATION: 2B pencil/eraser NUMBER OF QUESTIONS: 5 (five) TIME: READING TIME: Ten (10) Minutes 1 x 24 Page Answer booklet General Purpose Answer Sheet Writing implements, including a INSTRUCTIONS TO CANDIDATES: 1. Enter your name and student number and sign the space provided at the bottom of the page. 2. This is a closed book examination; therefore no written material, reference books or notes will be permitted in the examination room. 3. The examination paper, together with all answer booklets, must be returned at the completion of the examination. 4. Questions 1 - 4 consist of written questions. The number of marks allocated to each question is indicated. Write your answers in the answer booklet provided. 5. Question 5 consists of 40 multiple choice questions, each worth 1 mark. The answers to these must be marked on the GPAS supplied with a 2B pencil. 6. You should answer ALL questions. INSTRUCTIONS TO INVIGILATORS: Question Paper MAY NOT be retained by the Candidate. STUDENT NAME: .................................................................. STUDENT No: ........................... STUDENT SIGNATURE: ................................................................................................................ Page 1 of 8 pages QUESTION 1 Each question is worth 1 mark ( 20 Q) (20 marks in total) It is to be answered using a 2B pencil on the General Purpose Answer Sheet provided, by filling in the circle corresponding to the most appropriate alternative for each part. Be sure to complete your name (your family name, then a space, then your first given name) and student number (in the identification number field) on this sheet. You do not need to fill in the fields for sex, grade, birth date or special codes. 1. A system may exhibit data independence if: a. increasing the length of a text field forces changes in data access methods b. it stores information and data c. access programs do not require change when a field type changes from integer to decimal d. access is dependent on data structure 1. Data redundancy A assures the existence of correct data.. B increases the accuracy of the database. C is not permitted in a consistent database. D s unnecessary duplication of data. A database model A represents the data reports and the data forms found within the database. B represents the data structure and the data relationships found within the database. C represents the data structure and the data values found within the database. D represents the data values and the data relationships found within the database. The relational model uses __________ to link tables. A data values from the foreign keys B the relational DBMS C data values from the secondary keys D data values from non-key attributes Foreign keys A must not be null, and go in the table at the “many” end of a “1 to many” relationship. B must not be null, and go in the tables at both ends of a “many to many” relationship. C can be null, and go in the table at the “many” end of a “1 to many” relationship. D can be null, and go in the tables at both ends of a “many to many” relationship. In order to maintain referential integrity when linking multiple tables A a candidate key must reference a foreign key. B a foreign key must reference a primary key. C a primary key must reference a foreign key. D a primary key must reference another primary key. 2. 3. 4. 5. Page 2 of 8 pages 6. Which attribute(s) make up the primary key in the table definition CLASS (CRS_CODE, CLASS_SECTION, CLASS_TIME, CLASS_ROOM, PROF_NUM)? A CRS_CODE B CLASS_SECTION C CRS_CODE and CLASS_SECTION D There is no primary key …. QUESTION 2 Answer all questions. 1, Define the following terms: (5 marks) a. Business Rule (1.5 marks) 2. 3. 4. 5. (25 marks in total) Page 3 of 8 pages QUESTION 3 (20 marks in total) Answer all questions – Each question is worth 4 marks The Thirlemere Thoroughbreds Database contains the following tables and sample data. ENTRY Event_id 0101 0101 0101 0101 0102 0103 0201 0301 0401 0501 0501 horse_id 101 102 201 301 201 102 101 301 102 102 301 place 1 2 3 4 2 3 1 2 7 1 3 04 Smith Sydney PRIZE event_code 0101 0101 0101 0102 0102 0103 0103 0103 0201 0201 0401 0401 0401 0501 0501 place 1 2 3 1 2 1 2 3 1 2 1 2 3 1 2 prizemoney $120.00 $60.00 $30.00 $10.00 $5.00 $100.00 $60.00 $40.00 $10.00 $5.00 $1,000.00 $500.00 $250.00 $10.00 $5.00 EVENT Event_id 0101 0102 0103 0201 0301 0401 0501 0502 show_id 01 01 01 02 03 04 05 05 event_name Dressage Jumping Led in Led in Led in Dressage Dressage judge_id 01 02 01 02 01 04 01 Flag and Pole 02 SHOW show_id show_name show_held 01 Dubbo Young Castle Hill Royal Easter Dubbo show_address 7/5/95 23 Wingewarra St, Dubbo 9/13/95 13 Cherry Lane, Young 5/4/96 Showground Rd, Castle Hill 4/21/96 PO Box 13, GPO Sydney 7/1/96 17 Fitzroy St, Dubbo HORSE Horse _id 101 102 201 301 302 401 501 502 name Flash Star Boxer Daisy Tinkle Snowy Bluebell Sally colou sire dam born died gende r r white grey white white grey white 201 401 401 301 302 501 502 501 1990 1991 1980 1981 S M S M brown 201 02 03 04 05 brown 401 1981 1994 M 1976 1984 S 1975 1982 M 1974 1987 M JUDGE Judge_id 01 02 03 name Smith Green Gates address Melbourne Cootamundra Dunkeld Page 4 of 8 pages Notes on the data: • Primary keys have been underlined. • The mother of a horse is called its Dam. • The father of a horse is called its Sire. • The gender of a horse is recorded as “M” for mare (female) or “S” for stallion (male). Notes on the requirements: • Remove duplicate rows from your results where appropriate. • Do not use VIEWs or the INCLUDE statement. Write SQL SELECT statements to retrieve the following information from the database. 3.1 Show the SQL data definition language (DDL) syntax that would have been used to create the JUDGE table structure. 3.2 List the horse_id and name of all horses that were entered in event 0101. 3.3 List the year, and the number of horses born in each year as num_born. 3.4 List the name of horses that have never finished either first or second in an event. 3.5 List event_id, horse_id, place and money for every entry. Sort the results into order on event_id and then horse_id. QUESTION 4 (15 marks) Represent the following problem, which is represented by means of text and an Entity Relationship Diagram (ERD), as a Relational Data Model (RDM). Ling Chemical Company (LCC) main area of business lies in buying and selling industrial and agricultural chemicals in relatively small quantities. The products that the company deals in are of three types: general chemicals, paints, and animal feedstuff additives. Each product that LCC deals in is the main responsibility of one of the company’s area sales representatives. Each representative, however, may be responsible for a number of different products. Some of the area sales representatives act as supervisors. Supervisors are directly responsible for managing teams of fellow area sales representatives. Each of the company’s customers will only deal with LCC through one particular sales representative. Each sales representative also covers one or more geographical sales areas, for eliciting new business from prospective customers. Several different sales representatives may cover the same geographical area, and each sales representative has a supervisor how is also a sales representative for that area, and who is directly responsible to management for sales performance (of the other representatives) in that area, in terms of hitting sales targets, for example. ITC114 Page 5 of 8 pages The following is a list of data items/ attributes from the scenario, and a description of their meaning: Prod_No Prod_Name Prod_Desc Prod_Price Prod_Sales_Rep_ID Sales Rep ID Sales Rep Name Salary Sup_id Area_ID Area name Cust_id Cust_name Address Cust_area_ID Acc_no Cust_Sales_rep_ID Unique identifier for a product The name of a product Text description of a product The price of a product The identifier of the sales representative responsible for a product The unique identifier of an area sales representative The name of an area sales representative The salary of an area sales representative The identifier of the supervisor of an area sales representative Unique identifier of a geographical sales area Name of a geographical sales area Unique identifier of a LCC customer name of a LCC customer Address of a LCC Customer identifier of the geographical sales area in which this customer is located account number for a LCC customer Identifier of the area sales representative who deals with this customer Notes: • Indicate primary keys by means of underlining. • Indicate foreign keys by means of notes after each table. • State any assumptions you make. • Your set of tables must be in Third Normal Form. • Your set of tables should show only those attributes required to make the situation described above work. You may add attributes to those listed above (but you must justify their inclusion). Do not add attributes just because they seem like a good idea, nor spilt up attributes into smaller parts unless the question specifically requests it. It may not be appropriate to store some of these attributes due to design principles. ITC114 Page 6 of 8 pages QUESTION 5 Represent this problem by means of: i) An Entity Relationship Diagram (ERD), and ii) A Relational Data Model (RDM). (20 marks) A garage needs to keep track of costs involved in repairing customer's cars. Thus the information that it needs to keep relate to the cars, the work done on the cars and the parts used. A customer may have one or many cars. Each time the customer wishes work to be performed on her car(s) she makes a booking with the garage receptionist. Many employees may work on the car whilst it is in the garage. Each employee is required to enter the number of hours they have worked on the car for the one booking (that is, if they worked on the car for 4 hours the first day and 3 hours the second day, they only enter the total hours for the booking, which is 7.) The employee also records a description of the work they performed. Employees are classified as either A (=apprentice, who are charged out at $25 per hour), M (=mechanic- $40 per hour), or E (=engineer -$50 per hour.) Each time an employee uses a part on the car, record the part and quantity used. Data to be stored are customer-id, cust-name, phone, address, rego-no, make, model, year, carcolour, date-car-in, reason-for-booking, part-no (for parts used), part-description, part-cost, partquantity-used, employee-no, employee-type, hours-spent, work-description, date-car-out, totalcost, employee_type_charge_out_rate. Notes: • The Entity Relationship (E-R) diagram should show names for both entities and relationships. • The E-R model should indicate the cardinality (ie. one-to-one, one-to-many or many-tomany) of all relationships. • The E-R diagram need not show attributes. • Your set of tables must be in Third Normal Form. • Your set of tables should show only those attributes required to make the situation described above work. You may add attributes to those listed above (but you must justify their inclusion). Do not add attributes just because they seem like a good idea, nor spilt up attributes into smaller parts unless the question specifically requests it • Indicate primary keys by means of underlining. Indicate alternate and foreign keys by means of notes after each table. • The size or type of attributes in your set of tables need not be indicated. • List any assumptions that you make. • Marks will be deducted if your E-R diagram does not correspond to your relational data model. ITC114 Page 7 of 8 pages CUSTOM ER ow ns CAR EM PLO YEE TYPE needs of PART B O O K IN G EM PLO YEE d e s c r ib e d in r e q u ir e s has by PARTSUSED W ORKDONE ITC114 Page 8 of 8 pages