| Aim of Module | To give the student an understanding of the principles of database design within a commercial environment. |
| Credits | 7 |
| Student Learning hours | 70 |
| Content Revised | 2004 |
| Prescription Expiry Date | Nov 2007 |
| Topics | Highest Skill Level | Suggested Assessment Percentage | |
| 1 DBMS Management Issues | C | 25 | |
| 2 Design Overview | C | 10 | |
| 3 Database Standards | C | 5 | |
| 4 Data Dictionaries | C | 5 | |
| 5 Logical Design | A | 20 | |
| 6 Implementation - Physical Design | A | 25 | |
| 7 Review of DBMSs | P | 10 | |
| 100 | |||
| LEARNING OUTCOMES The Student Will | ||
| C | 1 | Discuss issues which influence the selection of a DBMS product and describe how security and recovery are handled in a typical database environment. |
| C | 2 | Explain reasons for a database design approach, including factors such as concurrent and multi-user capability, data integrity and consistency, standardisation and productivity. |
| C | 3 | Outline the generally accepted standards of good database design. |
| C | 4 | Describe the use of a data dictionary. |
| A | 5 | Describe the relationship between data modelling and database design and prepare a logical database design for a realistic business example. |
| A | 6 | Implement a well-designed database for a realistic business example. |
| P | 7 | Critically evaluate DBMS by the evaluation of existing implemented database systems in order to identify specific design issues, strengths, weaknesses and possible improvements. |
Where possible two different DBMSs should be used and compared (eg Access & Interbase, Access & SQL Server). | ||
| 1 | DBMS Management Issues |
| > | Issues which affect selection include: DBMS features and tools, underlying model, cost/benefit analysis. | |
| > | The essential characteristics and differences of a variety of DBMS products should be investigated. | |
| > | Detailed description of the handling of security in typical DBMS products (eg views, groups, users, rights). | |
| > | Recovery should cover procedures for both hard and soft crashes. | |
| 2 | Design Overview |
| Explain the reasons for a database design approach, including concurrent and multi-user capability, data integrity and consistency, standardisation and productivity. | ||
| 3 | Database Standards |
| > | Standards such as naming conventions, normalised relations, ERD conventions. | |
| 4 | Data Dictionaries |
| > | Schema, sub-schema, standardisation. | |
| 5 | Logical Design |
| > | Examples used should be of substantial size and/or complexity with an emphasis on good design. | |
| > | ER diagrams, schema depiction, normalisation to 3rd normal form should be used and understood. | |
| > | Performance issues, denormalisation, use of automatically generated primary keys should be discussed. | |
| 6 | Implementation - Physical Design |
| > | It is expected that SQL will be used to create: | |
| - tables with integrity checks on the attributes | ||
| - triggers which implement business rules | ||
| - views for complex queries and for controlling access rights | ||
| 7 | Review of DBMSs |
| > | Analysis of real commercial business examples is expected. | |