Highlight your answer choice for each question as per the example below. Do not delete the options. You should highlight your choice of answer like this: This is an example question, showing how to answer the multiple choice section This is an example question, showing how to answer the multiple choice section When should collection of test data for checking a system occur? • After coding is complete • At the beginning of the implementation phase • During the analysis phase • During installation and conversion
Each correct answer is worth 2 marks, each wrong answer will have ½ mark deducted, each question not attempted will be given 0 marks
• User training for a business system should focus on: • all the capabilities of the new system • helping the users to accomplish their jobs • how to use the system • not using the system • A database is considered “self-describing” because: • all the users’ data is in one place • it reduces data duplication • it contains a description of its own structure • it contains a listing of all the programs that use it • Entering invalid data into a field is known as a/an: • appending error • truncating error • transcription error • transposing error • A functional dependency is a relationship between two: • attributes • operations • objects • tables • Which of the following tasks is not covered by a database administrator? • Backing up data • Assigning user access rights to data • Normalising data to identify tables and relationships • Recovering data • Magnetic characters are produced on your bank cheques by: • bar-code readers • mice • MICR inscribers • OCR • A point of synchronisation between the database and the transaction log is called a/an: • before image • after image • transaction • checkpoint • Which of the following is the most important principle of input design? • Capture input electronically as close to the source as possible • Minimize keystrokes • Never use on-line processing • Use batch processing when appropriate • What type of check ensures that the numeric data entered is within the correct minimum or maximum values? • Check digit • Completeness • Consistency • Range • A Use Case is a model of a/an: • computer process • computation • event • requirement
• In a database with two tables (courses & students – 1:M relationship) implementing the ‘on delete cascade’ function will: • Delete all related students if a course is deleted. • Prohibit the deletion of a course if there are any associated students. • Set the course_id (foreign key) in the student table to null. • Have no affect on the student table if a course is deleted. • Which type of installation places the heaviest demand on system resources? • Direct • Phased • Parallel • Single location • eXtreme programming (XP) is uniquely characterized by: • heavy emphasis on formal validation techniques • use of traditional coding practices • involvement of two-programmer teams • simultaneous coding and analysis • Copies of changes made by a transaction, that are saved for use in database recovery, are called: • before images • after images • transactions • checkpoints • Which of the following is NOT a deliverable resulting from the requirements determination process? • Interview transcripts • Documentation of existing system • Procedure manuals • Project schedule • A query that involves retrieval of data from multiple tables, based on connecting primary keys and foreign keys, is called a: • join query • merge query • composite query • key query • Which of the following supports the use of prototyping as a means for requirements determination? • Many users and stakeholders are involved • Formal systems requirements documentation is desired • The system is to be integrated and shares data with other systems • User requirements are not clear or well-understood
• In an Access database a primary key: • may be a null value • is used to uniquely identify records in a database table • may contain duplicates but not null values • is not always required • Locks that are placed under the assumption that a conflict will occur, are called ___________ locks. • pessimistic • optimistic • implicit • explicit • A structured design methodology that proceeds in a sequence from one stage to the next, is a ___________ development. • phased • prototyping • rapid application • waterfall • Use Cases are likely to be used during which phase of the life cycle? • Planning • Analysis • Design • Implementation • Referential integrity dictates that the value of a: • primary key must appear in a foreign key of the related table • primary key cannot appear in a foreign key of the related table • foreign key must appear in a primary key of the related table • foreign key cannot appear in a primary key of the related table • A relation is considered to be in second normal form if it is in first normal form and it has no: • referential dependencies • functional dependencies • partial key dependencies • transitive dependencies • The purpose of an SQL SELECT statement is to: • retrieve data from the database • select a table for creation • place new data into a table • make modifications to existing data in a table • What type of model is a class diagram? • Static • Dynamic • Evolving • Obsolete
Section B Answer ALL questions from section B. Each question is worth the marks indicated. Where appropriate, you should back up your answers with theory/references from reliable sources – more marks will be awarded for well referenced work. Incorrect answers in this section will not be negatively marked. The following datasets have been extracted from a company’s ordering system. They are related as follows: One Supplier can supply many wines. tblWine Table WineID CompanyID Name Winery Year Strength Type Price 5551 DF452322 Muscadet Transval 2000 2 White £91.16 5552 PM054231 Chateau neuf Roistons 1977 2 Red £32.07 5553 VW01222 Chardonnay Manstons 1989 1 White £25.50 5554 DF452322 Piesporter Kookerboro 2008 3 White £109.12 5555 DF452322 Chateau plaom Manstons 1998 2 Fortified £90.69 5556 FR456236 Merlot Roistons 1997 1 Red £41.25 5557 FW676767 Champagne Manstons 2000 1 Sparkling £61.20 5558 PM054231 Old git Roistons 1999 5 White £66.53 5560 FW676767 Muscadet Kookerboro 2009 2 White £81.24 5561 BB784575 Merlot Riccardsons 2009 1 Red £72.27 5562 FR456236 Muscadet Manstons 1998 2 White £102.28 5563 FR456236 Chardonnay Kookerboro 2012 1 White £34.62 tblSupplier Table CompanyID CompanyName CompanyAddress CompanyTown BB784575 Bargain Bottles 21 Cheapster Street Exeter DF452322 Dagins & Fogart 29 Dover Square Cirencester FR456236 Fresnels 8a Red Square Bootle
• Give the SQL statement that would provide the information for the following: • The name and price of the wine whose type is Sparkling (2 marks) • The WineID, Name and Price of all the wines from 2009 (2 marks) • The number of wines that are of strength 1 (2 marks) • Give the name of the wine, type and town of all wines from the supplier Fresnels (4 marks)
• Discuss the redundancy problems and anomalies that might arise through using the system described below (veterinary clinic), and how those problems could be eliminated. (10 marks) Your local veterinary clinic currently uses a file-based system. The company has a master file containing details for each pet: Owner’s name Pet’s name Account number Owner’s address Owner’s phone number Account balance A second file maintains a list of medicines/treatments given to each pet.
This file contains: Account number Pet’s name Date of the treatment Type of treatment Cost of treatment Owner’s name Owner’s phone number
There are also files storing information about the different veterinary surgeons that work at the clinic and a list of medicines that are stocked at the clinic.
• You are designing an information system for a movie rental business. You have identified the need for tables (entities) called DVD, Actor, rental, member. Provide a data dictionary for the entity DVD in the format described below. (10 marks)? Field Name Data Type Field Size Other Validation Information (primary keys, input masks, other constraints, etc)
• You have been employed by a local small business owner to create an information system for his sandwich shop and bakery. Thinking about the big picture and main requirements, make a list of questions for an initial interview with him. (5 marks) • Discuss another technique you could use to get the information you require. Compare the merits and disadvantages of this technique with interviewing. (5 marks) • • Transactions should pass the ACID test. Discuss what is meant by the acronym ACID in relation to database transactions. (4 marks)
• Bernice Benson has a bank account with an initial balance of £2000. By coincidence, the account is being updated simultaneously by 2 database transactions; Transaction A is a monthly transfer of funds from Bernice’s current account to her savings account and Transaction B is Bernice depositing a cheque into her current account. Transaction A reads the initial balance of £2000 into the memory buffer, and deducts the £300 from the account, leaving a balance of £1700. Transaction B reads the balance of £1700 and adds the deposit of £600, giving a new balance of £2300. Transaction A fails before completion and executes a rollback, returning the balance to £2000. Transaction B now writes its in-memory balance value of £2300 to the account balance within the database and commits the transaction. Explain why the database has become corrupted and describe how both transactions can be carried out whilst maintaining the integrity of the database. (6 marks)
Note: Don’t forget to include references for section B answers – more marks will be awarded for well referenced work.
Order a similar paper now