Database Analysis and Design: A Case Study of Dominican College

Database Analysis and Design
1

Contents
Introduction................................................................................................................................7
LO1 Understand data models and database technologies..........................................................8
1.1 Critically compare different data models and schemas...............................................8
1.2 Critically discuss the benefits and limitations of different database technologies....13
1.3 Analyze different approaches to database design......................................................16
LO2 Be able to design and implement relational database systems........................................21
2.1 Design a relational database system to meet a given requirement.................................21
2.2 Build a relational database system based on a prepared design.....................................24
2.3 Apply a range of database tools and techniques to enhance the user interface..............36
LO3 Be able to use manipulation and querying tools..............................................................49
3.1 Explain the benefits of using manipulation and query tools in a relational database
system...................................................................................................................................49
3.2 Implement a query language into the relational database system..................................57
3.3 Critically evaluate how meaningful data has been extracted through the use of query
tools......................................................................................................................................68
LO4 Be able to test and document relational database systems...............................................75
4.1 Critically review and test a relational database system..................................................75
4.2 Create documentation to support the implementation and testing of a relational
database system....................................................................................................................80
4.3 Create user documentation for a developed relational database system........................89
4.4 Explain how verification and validation has been addressed.........................................93
4.5 Explain how control mechanisms have been used.........................................................98
Conclusion............................................................................................................................99
References..............................................................................................................................100
Appendix................................................................................................................................101
2

List of Figures
Figure 1: Hierarchical Database Model.....................................................................................8
Figure 2: Network Database Model...........................................................................................9
Figure 3: Relational Database Model.........................................................................................9
Figure 4: Object-oriented Database Model..............................................................................10
Figure 5: Database Schema......................................................................................................11
Figure 6: A Top-Down design approach..................................................................................16
Figure 7: Bottom-up Design Approach....................................................................................17
Figure 8: ERD Diagram...........................................................................................................18
Figure 9: Entities......................................................................................................................18
Figure 10: Attributes................................................................................................................18
Figure 11: Relationship............................................................................................................19
Figure 12 : 1NF........................................................................................................................19
Figure 13: 2NF.........................................................................................................................20
Figure 14: 3NF.........................................................................................................................20
Figure 15: Datasheet View of Academic Manager..................................................................24
Figure 16: Datasheet View of Admin......................................................................................24
Figure 17: Datasheet View of Campus....................................................................................25
Figure 18: Datasheet View of Course......................................................................................25
Figure 19: Datasheet View of Lecturer....................................................................................25
Figure 20: Datasheet View of Student.....................................................................................26
Figure 21: Datasheet View of Unit..........................................................................................26
Figure 22: Design View of Academic Manager......................................................................27
Figure 23: Design View of Admin...........................................................................................28
Figure 24: Design View of Campus.........................................................................................29
Figure 25: Design View of Course...........................................................................................30
Figure 26: Design View of Lecturer........................................................................................31
Figure 27: Design View of Student..........................................................................................32
Figure 28: Design View of Unit...............................................................................................33
Figure 29: Description of Academic Manager From...............................................................36
Figure 30: Description of Admin From...................................................................................36
Figure 31: Description of Campus From.................................................................................37
Figure 32: Description of Course From...................................................................................37
Figure 33: Description of Lecturer From.................................................................................38
Figure 34: Description of Student From..................................................................................38
Figure 35: Description of Unit From.......................................................................................39
Figure 36: Datasheet View of Academic Manager..................................................................39
Figure 37: Datasheet View of Admin......................................................................................40
Figure 38: Datasheet View of Campus....................................................................................40
Figure 39: Datasheet View of Course......................................................................................40
Figure 40: Datasheet View of Lecturer....................................................................................40
Figure 41: Datasheet View of Student.....................................................................................41
Figure 42: Datasheet View of Unit..........................................................................................41
3

Figure 43: Query 1...................................................................................................................41
Figure 44: SQL view of Query 1..............................................................................................42
Figure 45: Query 2...................................................................................................................42
Figure 46: SQL view of Query 2..............................................................................................42
Figure 47: Query 3...................................................................................................................42
Figure 48: SQL view of Query 3..............................................................................................43
Figure 49: Query 4...................................................................................................................43
Figure 50: SQL view of Query 4..............................................................................................43
Figure 51: Query 5...................................................................................................................43
Figure 52: SQL view of Query 5..............................................................................................44
Figure 53: Query 6...................................................................................................................44
Figure 54: SQL view of Query 6..............................................................................................44
Figure 55: Query 7...................................................................................................................44
Figure 56: SQL view of Query 7..............................................................................................45
Figure 57: Query 8...................................................................................................................45
Figure 58: SQL view of Query 8..............................................................................................45
Figure 59: Query 1 Report.......................................................................................................45
Figure 60: Query 2 Report.......................................................................................................46
Figure 61: Query 3 Report.......................................................................................................46
Figure 62: Query 4 Report.......................................................................................................46
Figure 63: Query 5 Report.......................................................................................................47
Figure 64: Query 6 Report.......................................................................................................47
Figure 65: Query 7 Report.......................................................................................................47
Figure 66: Query 8 Report.......................................................................................................48
Figure 67: Relationship Diagram.............................................................................................48
Figure 68: Query 1...................................................................................................................49
Figure 69: Design View of Query 1.........................................................................................50
Figure 70: SQL view of Query 1..............................................................................................50
Figure 71: Query 2...................................................................................................................50
Figure 72: Design view of Query 2..........................................................................................50
Figure 73: SQL view of Query 2..............................................................................................51
Figure 74: Query 3...................................................................................................................51
Figure 75: Design view of Query 3..........................................................................................51
Figure 76: SQL view of Query 3..............................................................................................51
Figure 77: Query 4...................................................................................................................52
Figure 78: Design view of Query 4..........................................................................................52
Figure 79: SQL view of Query 4..............................................................................................52
Figure 80: Query 5...................................................................................................................53
Figure 81: Design view of Query 5..........................................................................................53
Figure 82: SQL view of Query 5..............................................................................................53
Figure 83: Query 6...................................................................................................................54
Figure 84: Design view of Query 6..........................................................................................54
Figure 85: SQL view of Query 6..............................................................................................54
Figure 86: Query 7...................................................................................................................55
4

Figure 87: Design view of Query 7..........................................................................................55
Figure 88: SQL view of Query 7..............................................................................................55
Figure 89: Query 8...................................................................................................................56
Figure 90: Design view of Query 8..........................................................................................56
Figure 91: SQL view of Query 8..............................................................................................56
Figure 92: Query 1...................................................................................................................57
Figure 93: Design View of Query 1.........................................................................................57
Figure 94: SQL view of Query 1..............................................................................................57
Figure 95: Query 1 Report.......................................................................................................58
Figure 96: Query 2...................................................................................................................58
Figure 97: Design view of Query 2..........................................................................................58
Figure 98: SQL view of Query 2..............................................................................................59
Figure 99: Query 2 Report.......................................................................................................59
Figure 100: Query 3.................................................................................................................59
Figure 101: Design view of Query 3........................................................................................60
Figure 102: SQL view of Query 3............................................................................................60
Figure 103: Query 3 Report.....................................................................................................60
Figure 104: Query 4.................................................................................................................61
Figure 105: Design view of Query 4........................................................................................61
Figure 106: SQL view of Query 4............................................................................................61
Figure 107: Query 4 Report.....................................................................................................62
Figure 108: Query 5.................................................................................................................62
Figure 109: Design view of Query 5........................................................................................63
Figure 110: SQL view of Query 5............................................................................................63
Figure 111: Query 5 Report.....................................................................................................63
Figure 112: Query 6.................................................................................................................64
Figure 113: Design view of Query 6........................................................................................64
Figure 114: SQL view of Query 6............................................................................................64
Figure 115: Query 6 Report.....................................................................................................64
Figure 116: Query 7.................................................................................................................65
Figure 117: Design view of Query 7........................................................................................65
Figure 118: SQL view of Query 7............................................................................................65
Figure 119: Query 7 Report.....................................................................................................66
Figure 120: Query 8.................................................................................................................66
Figure 121: Design view of Query 8........................................................................................67
Figure 122: SQL view of Query 8............................................................................................67
Figure 123: Query 8 Report.....................................................................................................67
Figure 124: Query....................................................................................................................68
Figure 125: Query 1 Report.....................................................................................................68
Figure 126: Query 2.................................................................................................................69
Figure 127: Query 2 Report.....................................................................................................69
Figure 128: Query 3.................................................................................................................69
Figure 129: Query 3 Report.....................................................................................................70
Figure 130: Query 4.................................................................................................................70
5

Figure 131: Query 4 Report.....................................................................................................70
Figure 132: Query 5.................................................................................................................71
Figure 133: Query 5 Report.....................................................................................................71
Figure 134: Query 6.................................................................................................................72
Figure 135: Query 6 Report.....................................................................................................72
Figure 136: Query 7.................................................................................................................72
Figure 137: Query 7 Report.....................................................................................................73
Figure 138: Query 8.................................................................................................................73
Figure 139: Query 8 Report.....................................................................................................74
Figure 140: Validation Applied in Test case 1.........................................................................80
Figure 141: Validation Applied in Test case 2.........................................................................81
Figure 142: Validation Applied in Test case 3.........................................................................82
Figure 143: Validation Applied in Test case 4.........................................................................83
Figure 144: Validation Applied in Test case 5.........................................................................84
Figure 145: Validation Applied in Test case 6.........................................................................85
Figure 146: Validation Applied in Test case 7.........................................................................86
Figure 147: Validation Applied in Test case 8.........................................................................87
Figure 148: Validation Applied in Test case 9.........................................................................88
Figure 149: Description of Academic Manager From.............................................................89
Figure 150: Description of Admin From.................................................................................89
Figure 151: Description of Campus From...............................................................................90
Figure 152: Description of Course From.................................................................................90
Figure 153: Description of Lecturer From...............................................................................91
Figure 154: Description of Student From................................................................................91
Figure 155: Description of Unit From.....................................................................................92
Figure 156: Validation Applied in Test case 1.........................................................................93
Figure 157: Validation Applied in Test case 2.........................................................................94
Figure 158: Validation Applied in Test case 3.........................................................................94
Figure 159: Validation Applied in Test case 4.........................................................................95
Figure 160: Validation Applied in Test case 5.........................................................................95
Figure 161: Validation Applied in Test case 6.........................................................................96
Figure 162: Validation Applied in Test case 7.........................................................................96
Figure 163: Validation Applied in Test case 8.........................................................................97
Figure 164: Validation Applied in Test case 9.........................................................................97
6

Introduction
The entire analysis of the database is a procedure so as to design a database in respect to the
requirements and desires of the user. The created database is being used in order to handle the
data along with a reduction in the redundancy of the data. The database normally presents
different approaches in which it can be created that are data related models and dictionary of
data etc. The database development is normally started with the generation of various
diagrams that are both logical and conceptual based relationship.
The college named as Dominican wants to develop a database for it using the MS Access.
The given college is located in a city of New York. This college has overall three campuses
that have various courses associated that are being taught to all the learners. The database and
its report are being developed to meet all the desires of the user. The development of the
database begins with the table’s creation where users analyse those data which are being
stored. In database further phase various forms are developed that allows users to put their
data which is valuable. In the final stage, several queries are being created depending on
desires of data so that they can filter data.
7

LO1 Understand data models and database technologies
1.1 Critically compare different data models and schemas
A database model is known as representation, layout or logical structure of the database and it
tells us that how will data be processed, stored and managed within it. So it is helpful for
designing the database. The database has many models. Which are:
Hierarchical Database Model:
Hierarchical is the very oldest model of the database. In this model, the database is stored in
form of the tree structure it means every child node have an only one parent node. The tree of
the database contains one or many field’s groups which are called segments.
Figure 1: Hierarchical Database Model
Network Database Model:
This model was alternative of the hierarchical database model. It provides many paths
between segments of the database to expands the all the possibility of the hierarchical data
model. It removes many drawbacks of the hierarchical database model.
8