Database Design and Development

Unit:

Database Design and Development

Assignment

Introduction

This assignment requires you to demonstrate knowledge and skills you have acquired throughout this module by producing a database and producing a report that addresses the tasks given below. In order to complete the assignment, you will need to choose an appropriate organisation to research and base your database project on it.

Choosing an appropriate organisation

Step 1: select an industry in which you are interested. For example, commercial airlines, commercial banking, retail, beauty, IT, publishing, hospitality, leisure and tourism, automotive, construction etc.

Step 2: select one or more organisations in that industry to research. You should investigate the sort of data they hold and the types of transactions they carry out. For example the education college shown in the appendix holds data about the courses, staff, modules, laboratories and equipment. Their transactions might include allocating modules to courses, staff to modules, and assigning laboratories to courses and allocating equipment to laboratories.

Do not chose a college for your organisation as this has been used as an example.

You should reference the businesses or organisations that you investigate.

Step 3. Gathering materials

For your chosen type of organisation gather materials relating to their data and transactions. You could do this by looking online, through personal contacts with someone who works in the industry or simply by using a particular business’s services. Materials that you gather might include:

• Invoices
• Receipts
• Order Forms
• Customer Records
• Delivery Notes
• Complaints Forms
• Booking Forms
• Descriptions of daily tasks
• Interviews with staff members
• Company reports

Task 1 – Description of Business (10 marks)

Give a brief (about 200 – 300 word) written overview of your organisation.

You should include an outline of the context in which it operates. For example a college would operate in an environment in competition with other colleges.

You should outline their day-to-day operations, their data requirements and the types of transactions they carry out. You might include scans and/or diagrams of any documents you have gathered, which should be suitably anonymised so as not to show any personal data that might be included. You should explain the purpose of these documents in the context of the organisation.

You should outline the scope of the database project that you will undertake. This should be suitably ambitious enough to include at least ten entities in the data model. You should make clear what will be included within the scope of the project and what will not be included. This discussion of scope should include both data and operational issues.

Task 2 – ER and Data Dictionary (15 marks)

Produce an entity relationship diagram (ERD) for your organisation and an accompanying data dictionary. Your entity relationship diagram should include at least TEN (10) entities. You should use the UML format.

Define the integrity constraints on the tables in your design. You should clearly include domain constraints, business rules (Table constraints) and propagation constraints in your data dictionary.

Task 3 Normalisation (15 marks)

Show how you have used normalisation in developing your data model. You should:

Explain the purpose of normalisation, and clearly justify why each of your entities is in 3NF.

Explain how you have used normalisation to check tables are well-structured.

Using examples from your design, explain how normalisation solves the problem of update anomalies

(This section should be about 200 - 300 words excluding any diagrams)

Task 4 Assessment of Design (10 marks)

From you investigation into your chosen firm outline the requirements for their database system. Give an evaluation of how the work you have done so far has met the requirements of the firm. You should show how you have implemented the logical and physical design processes in the work you have completed. You should discuss how you have:

• Mapped logical database design to physical database design.
• Designed tables for your target DBMS
• Identified any derived data and how you will represent it.

(This section should be about 200 - 300 words excluding any diagrams)

Task 5 – Scripts to create table structures (10 marks)

Show the SQL scripts that you have used to create your database in a suitable database environment. You should include scripts for tables, columns, primary and foreign keys and any other database objects that you use. You should include screen shots of the scripts running within the database environment. For high marks, your scripts should implement integrity constraints you identified in Task 2.

You should provide an explanation of how you developed the scripts, order of running, and any issues encountered with them.

You should comment on how the use of SQL has enhanced your database including decisions about table structure, fields and data types.

Task 6 – Data population (5 marks)

You should create suitable data for your database. Show the SQL insert scripts that you have used to populate your database with this data. You should include screen shots of the scripts running within the database environment.

You should provide an explanation of how you developed the scripts, order of data population and any issues encountered with them.

Task 7 – SQL reports (10 marks)

Use SQL to produce TEN (10) queries that will be useful for your organisation. For each query you should supply the following:

• Rationale for query – what is its purpose and what is it trying to retrieve?
• SQL script running in database environment.
• Result set of query, shown in database environment.

Query scripts and results should be shown as running in the database environment with the use of screen shots. They should be well-formatted and easy to read.

To achieve higher marks, queries should be of a reasonable level of complexity. This will involve the joining of two or more tables, the use of renaming columns to ensure a user friendly result set, using sub-queries and the inclusion of descriptive columns from the tables.

Task 8 – Future development of a distributed database (15 marks)

In the future your organisation could expand by merging with other similar companies. They suggested the possibility of developing a distributed database system and they would like you to assess the advantages and disadvantages of this. You should produce a report that discusses the following in the context of the system you have developed:

• Describe the meaning of the term distributed database system.
• Describe the components of a disturbed database management system.
• Describe the factors that might make your chosen organisation consider implementing a distributed database.
• Describe how a distributed database would be implemented given the organisation’s current organisational and geographic structure. You should include discussion of replication, fragmentation and distributed database type.
• Describe how a distributed database might allow the organisation’s business to adapt to potential future expansion.

(For guidance this section should be about 400 – 500 words)

Task 9 – Evaluate the learning that you have undertaken in order to complete this assignment, using the Gibbs reflective cycle (1988) model (10 marks)

Based upon your learning, you should reflect on each element of the model in order to produce an action plan, examining what you would do if this happened again.

Appendix – Example of information collated on scenario

DO NOT use this example or a variation of it for your own assignment.

The example below shows a written scenario that could be derived from investigating an organisation. Also shown are some documents. You should be able to collect similar examples of from your own chosen firm or organisation. From this material it is possible to identify the entities that would constitute the database. Note that there is some overlap of entities between the documents.

Attributes names would be those shown in the documents. It is acceptable to introduce new attributes to take account of the data needs of the organisation. It is also worth noting that data in its current form is not necessarily normalised and it would be up to the developer to ensure that it is when the new database model is being developed.

Scenario

Lowry College is Further Education College based in Kent, United Kingdom. They specialise in science education.

They want a database to help them manage teaching and resources for their various courses. This database will not be concerned, at least initially, with the allocation of students to courses.

Each course will have a number of staff allocated to it. A member of staff might be allocated to more than one course. Staff are defined by type: Teacher, Technical Support, Administration, Other.

Courses are made up of modules. A module might be part of more than one course. Modules are defined by type: ‘Core’, ’Elective’ or ‘Optional’. Some modules such as

‘Biochemistry of Life’ are taught on different courses.

A course might have one or more laboratories associated with it. A laboratory is administered by one particular course.

A module will be taught in a particular laboratory. A laboratory might host many different modules. Laboratories have equipment in them. A piece of equipment might be allocated to more than one laboratory. Equipment is defined by type.

Examples of data are shown in the tables below.