Systems Analysis and Database Design

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.

tblWineTable

tblSupplier Table

B1.Give the SQL statement that would provide the information for the following:

a. The name and price of the wine whose type is Sparkling

b. The WineID, Name and Price of all the wines from 2009

c. The number of wines that are of strength 1

d.Give the name of the wine, type and town of all wines from the supplier Fresnels

Discuss the redundancy problems and anomalies that might arise through using the system described below (veterinary clinic), and how those problems could be eliminated.

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.

B1.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.

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.

How much you can allocate the amount of costs?

• Initial cost- hardware and software.
• Ongoing cost- maintaining system, including for proprietary software, hosting, and support.
• Upgrade cost and expected lifespan of systems of upgrade.
• Opportunity cost- how much potential revenue is lost by not implementing the system?
• What are your competitors doing this area?

a. Discuss another technique you could use to get the information you require. Compare the merits and disadvantages of this technique with interviewing.

B2. 

a. Transactions should pass the ACID test.  Discuss what is meant by the acronym ACID in relation to database transactions.

b. 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.

This part is based on the case study (starting page 6 of this document) describing Bike Mania, which is a local bicycle sales and repair shop. As a computing student, you have been asked to investigate the current system detailed in the case study with a view to specifying a computerised information system to meet the needs of the business.

You must complete all tasks to a satisfactory standard in order to pass this module.

Your tasks are as follows:

1. Highlight problems with the operation of Bike Mania, giving reasons as to why those things may cause problems.

2. Based on the information in the Bike Mania case study, use a suitable CASE tool or drawing package to draw up a set of diagrams showing the current physical view of the systemusing the unified modeling language (UML 2.0) notation.  You should include:

a. A Use Case Diagram

b. You must include at two of the following to pass this assessment :

     i. A textual description for one of the use cases identified above

     ii. An Activity Diagram for one of the use cases identified above

    iii. ADomain Class Diagram

3. Following your analysis, you must list (at least 5) possible requirements that the users would probably expect to see in the proposed new system.  Explain why the users would require each one.

You should include any assumptions you have made where you have found the case study information to be incomplete or inconclusive.