Relational Database System: Analysis, Design, Implementation

This report provides a comprehensive analysis and design of a relational database system for Dominican College, covering various aspects from data models and database technologies to the design, implementation, testing, and documentation of the system. It critically compares different data models such as hierarchical, network, relational, and object-oriented models, discussing their benefits and limitations. The report details the design and implementation of a relational database using MS Access, including table creation, form development, and query implementation using SQL. It also evaluates the effectiveness of query tools in extracting meaningful data and includes thorough testing and documentation of the database system, along with user documentation and explanations of verification, validation, and control mechanisms. Figures and diagrams are used to illustrate the database structure, relationships, and query results.

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

Figure 2: Network Database Model
Relational Database Model:
In relational database model, all the database represents in form of the two-dimensional tables
which is called relations. In the relational table, each row should have a unique key and this
field called a tuple. Column’s data entity called as attributes.
For Example, College table has a primary key which is Batch_Year and it has a second key
which is Course_ID and Student_ID.
Figure 3: Relational Database Model
9

Object-oriented Database Model:
In object-oriented database model, we can define our database access methods these methods
are used to manipulate and represent the database. So object-oriented database model
maintains and stores the database.
Figure 4: Object-oriented Database Model
Comparisons between Database Models:
Comparison Hierarchical Network Relational Object Oriented
Data Structure Use tree
structure for
store database
Use network
form structure
for store
database
Use table
structure for
store database
Use objects
structure to store
database
Relationship One to one and
one to many
One to one, one
to many and
many to many
One to one and
one to many
Many to many
Data types
support
It supports all
types of data
It supports all
types of data
It supports only
primitive data
It supports all
basics of
primitive data
Mathematical
support
It doesn’t
support
It doesn’t
support
It supports
mathematical
functions
It supports only
user-defined
methods and
functions.
Advantage The database
can be updated
It provides the
multiple paths
It provides the
flexibility to
It can easily
support numbers
10

and retrieval
easily.
for the database
structure.
change the
database.
of servers.
Disadvantage We can’t be
modified data
structure.
It is difficult to
maintain all
links
It is difficult to
navigate
It has a slow
performance
Database Schema:
Figure 5: Database Schema
The database schemas are work as a warehouse for data which is used to store database
structure. It is anything for an entity. We have three layers.
Internal Level:
11

 It describes the internal storage of data’s structure in the database.
 It is used to store data in form of bits at the lowest level.
 It can use to view data in form of files at the highest level.
Conceptual Level:
 It describes the structure of the database for grouping the users.
 This schema is used to generate the external records.
 It can hide internal data.
 It represents the entire data of the database.
External Level:
 It is used to view data by particular end users.
 The external level is nearest to end user.
 External level includes no. of views of the user.
12