Compiled Contents of:Week 2 The Database Design Life Cycle

 

Content File: Topic 2 Objectives


Topic 2 Objectives

Topic 2 Objectives

After completing this weeks study students should be able to:

  1. Describe the 3 level ANSI SPARC Database Architecture and the advantages which its inherent data abstraction provide to the database developer
  2. Explain the role of database development within an information system
  3. Describe the steps involved in the Systems Development Life Cycle (SDLC)
  4. Explain the steps involved with the Database Life Cycle (DBLC)
    1. Initial Study
    2. Database Design
    3. Implementation and loading
    4. Testing and evaluation
    5. Operation
    6. Maintenance and evaluation
  5. Explain, in detail, within the Database Design phase the role of
    1. ER modelling and Normalisation
    2. Data Model Verification
    3. Distributed Database Design
    4. Logical and Physical Design
  6. Describe the database design strategies which exist
    1. Top-down vs. bottom-up design
    2. Centralized vs. decentralized design
  7. Create and load a simple table which can be used for storage of binary data such as images, movies and sound

Topic 2 - Where are We

 

Content File: FIT1004SG2.pdf


FIT1004SG2.pdf
 

Content File: fit1004l2.pdf


fit1004l2.pdf
 

Content File: Tutorial 2 - Database Life Cycle


Tutorial 2 - Database Life Cycle

Tutorial 2 – Database Life Cycle

Reference:

Rob, P. & Coronel, C. Database Systems: Design, Implementation & Management, 6th Edition, 2004. Chapter 8, Review Questions 1 – 10; Problem 1-3.

Rob, P. & Coronel, C. Database Systems: Design, Implementation & Management, 7th Edition, 2006. Chapter 9, Review Questions 1 – 11; Problem 1-3.

Review Questions:

1. What is an information system? What is its purpose?

2. How do systems analysis and systems development fit into a discussion about information systems?

3. Discuss the distinction between data and information.

4. What does the acronym SDLC mean, and what does an SDLC portray?

5. What does the acronym DBLC mean, and what does a DBLC portray?

6. Discuss the distinction between centralized and decentralized conceptual database design.

7. What is the minimal data rule in conceptual design? Why is it important?

8. Discuss the distinction between top-down and bottom-up approaches to database design.

9. What are business rules? Why are they important to a database designer?

10. What is the data dictionary's function in database design?

11. What steps are required in the development of an ER diagram (Hint: See Table 9.1)?

12. List and briefly explain the activities involved in the verification of an ER model.

13. What factors are important in a DBMS software selection?

Problems

1. The ABC Car Service & Repair Centres are owned by the SILENT car dealer; they service and repair only that brand of car. The three ABC Car Service & Repair Centres provide service and repair for the entire state.

Each of the three centres is independently managed and operated, and each is operated by a shop manager, a receptionist, and at least eight mechanics. Each centre also maintains a fully stocked parts inventory.

Each centre maintains a manual file system in which each car's maintenance history is kept: repairs made, parts used, costs, service dates, owner, and so on. Files are also kept to keep track of inventory, purchasing, billing, employees, and payroll.

You have been contacted by the manager of one of the centres to design and implement a computerized system. Given the preceding information, do the following:

a. Indicate the most appropriate sequence of activities by labelling each of the following steps in the correct order. (For example, if you think that "Load the database" is the appropriate first step, label it "1.")

____ Normalize the conceptual model.

____ Obtain a general description of company operations.

____ Load the database.

____ Create a description of each system process.

___ Test the system.

____ Draw a data flow diagram and system flow charts.

____ Create a conceptual model, using E R diagrams.

___ Create the application programs.

____ Interview the mechanics.

____ Create the file (table) structures.

____ Interview the shop manager.

b. Describe the different modules that you believe the system should include.

c. How will a data dictionary help you to develop the system? Give examples.

d. What general (system) recommendations might you make to the general manager? (For example, if the system will be integrated, what modules will be integrated? What benefits would be derived from such an integrated system? Include several general recommendations.)

e. What do you think is the best approach to conceptual database design? Why?

f. Name and describe at least four reports that you believe the system should have, and explain their use. Who will use those reports?

2. Suppose you have been asked to create an information system for a manufacturing plant that produces nuts and bolts of many shapes, sizes, and functions. What questions would you ask, and how would the answers to those questions affect the database design?

a. What do you envision the SDLC to be?

b. What do you envision the DBLC to be?

3. Suppose you perform the same functions noted in Problem 2 for a larger warehousing operation. How are the two sets of procedures similar? How and why are they different?

Practical Issues - Using Oracle:

From last weeks tutorial:

You should create three separate text files and test run them within SQL

  • a schema file called swimschm.sql to create the tables (swimmer, event and then entry)
  • a file called swimload.sql containing several insert statements to load data into all three tables, and
  • a file containing drop table statements (eg. drop table swimmer purge) to drop all tables - ensure the tables are dropped in the reverse of the order they are created ie child tables before parent tables.

Where multiple SQL statements exist in one file be sure to terminate each with a semi-colon (;). You can check the contents of your loaded tables by using the SQL select command eg. select * from swimmer

Your tutor will check these files during todays lab.

Tutorial as a PDF file

 

 

Content File: wk02-tut.pdf


wk02-tut.pdf
 

Content File: fit1004t2-soln.pdf


fit1004t2-soln.pdf