Systems Analysis and Database Design Homework Help

Section A

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  CompanyAIDress  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 aIDress  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 aIDs 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