Systems analysis and database designyou should back up your

Assignment Help Database Management System
Reference no: EM13371364

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

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

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.

FieldName

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.

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.

Reference no: EM13371364

Questions Cloud

Use a graphical illustration to describe briefly what the : use a graphical illustration to describe briefly what the influence of each of the following would be on the market
1 for each of the following t values indicate whether the t : 1. for each of the following t values indicate whether the t is statistically significant for a two-tailed test at the
Wal-mart cost of capitalwal-mart with 50 billion in sales : wal-mart cost of capitalwal-mart with 50 billion in sales in 2010 is the worlds largest retailer. it operates nearly
Mtiple comparisonsscoretukey hsdi formatj formatmean : multiple comparisonsscoretukey hsdi formatj formatmean difference i-jstd. errorsig.95 confidence
Systems analysis and database designyou should back up your : systems analysis and database designyou should back up your answers with theoryreferences from reliable sources - more
1 jean siskel is an entertainment analyst for west : 1. jean siskel is an entertainment analyst for west coast securities. he is trying to develop a model to
Question 1a corporation produces packages of paper clips : question 1a corporation produces packages of paper clips. the number of clips per package varies as indicated below for
1 consider the following correlation coefficients presented : 1. consider the following correlation coefficients presented belowr -.98nbspnbspnbspnbspnbspnbspnbspnbspnbspnbspnbsp r
1 the test averages for 40 students are listed below use 8 : 1 the test averages for 40 students are listed below. use 8 classes not bins to construct a frequency distribution

Reviews

Write a Review

 

Database Management System Questions & Answers

  Draw an initial entity-relationship model

Normalise the Project Allocation form (Fig 1) to arrive at third normal form entities. Show all the steps of normalisation clearly.

  Spreadsheet and database

Explain the differences between the storing data in Access and Excel. Why you would use a spreadsheet over the database?

  List different entities-objects-logical processes-data flows

List the different entities or objects, logical processes, data flows, and data stores that are involved, starting from the time you submitted your time sheet.

  Create a database from scratch

Create a database from scratch that contains, at a minimum, the elements listed below

  Electronic spreadsheets in data modeling

Explain in detail with supporting illustrations the six stages of the problem solving process and explain when the choice for using electronic spreadsheets is the preferred tool of choice.

  Explain database models-hierarchical and relational

Explain each of three database models, hierarchical, relational, and object-oriented, and provide specific referenced example of each of these model types implemented in a functioning database

  What is the output when the following script is executed

Determine the answer (expected result) based on your reading of from text book: -  Just show the expected output when you run the syntax.

  Convert table to 3nf and represent answer in dbdl

Convert the table to 3NF. Represent your answer in DBDL. (i.e. Give table name and fields. Underline the primary key. Draw an entity-relationship diagram showing all relationships.)

  Compare two non-sequential file structure models

Compare the two non-sequential file structure models. What advantages does the first one have over the second and what advantages does the second have over the first?

  How an organization would allow programmatic access

A few years have passed, and the restaurant has grown to become a national restaurant chain. Now, they are concerned that remote and branch offices and restaurants in all fifty states will not have ready access to the data.

  What is the highest normal form r is in

What functional dependencies can you conclude from the data above and what is the highest normal form R is in?

  Create erd models

Select one of the Real World Scenarios and use the experience you have that is related to the scenario you have chosen or research online; and then post your comments and solutions about your experience.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd