Relational Database Design and Implementation for Dominican College

Table of Contents
List of Figures:..................................................................................................................2
List of Tables:...................................................................................................................4
Introduction...................................................................................................................... 5
LO1 Understand data models and database technologies..............................................6
1.1 Critically compare different data models and schemas.......................................6
1.2 Critically discuss the benefits and limitations of different database technologies. 11
1.3 Analyse different approaches to database design................................................14
LO2 Be able to design and implement relational database systems..............................16
2.1 design a relational database system to meet a given requirement.......................16
2.2 Build a relational database system based on a prepared design.........................19
2.3 Apply a range of database tools and techniques to enhance the user interface. .25
LO3 Be able to use manipulation and querying tools.....................................................30
3.1 Explain the benefits of using manipulation and query tools in a relational database
system........................................................................................................................ 30
3.2 Implement a query language into the relational database system that has been
implemented...............................................................................................................31
3.3. Critically evaluates how meaningful data has been extracted through the use of
query tools for the given case study...........................................................................40
LO4 Be able to test and document relational database systems...................................43
4.1 critically reviews and tests the implemented relational database system.............43
4.2 Create documentation to support the implementation and testing of a relational
database system........................................................................................................ 44
4.3 Create user documentation for a developed relational database system.............47
4.4 Explain how verification and validation has been addressed................................47

4.5 Explain how control mechanisms have been used...............................................48
Conclusion:.................................................................................................................... 49
References.....................................................................................................................50

List of Figures:
Figure 1 Example of Database Model..............................................................................7
Figure 2 Network Model...................................................................................................8
Figure 3 Relational Model................................................................................................9
Figure 4 Object Oriented Data Model.............................................................................10
Figure 5 Different Level of Database Schema...............................................................11
Figure 6 Top-Down Approach........................................................................................15
Figure 7 Relationship Diagram.......................................................................................17
Figure 8 ERD Diagram...................................................................................................18
Figure 9 Design View of Admin Table............................................................................21
Figure 10 Datasheet View of Admin Table.....................................................................21
Figure 11 Design View of Manager Academic Table.....................................................21
Figure 12 Datasheet View of Manager Academic Table................................................22
Figure 13 Design View of Campus Table.......................................................................22
Figure 14 Datasheet View of Campus Table..................................................................22
Figure 15 Design View of Course Table.........................................................................23
Figure 16 Datasheet View of Course Table...................................................................23
Figure 17 Design View of Student Table........................................................................24
Figure 18 Datasheet View of Student Table...................................................................24
Figure 19 Design View of Lecturer Table.......................................................................25
Figure 20 Datasheet View of Lecturer Table..................................................................25
Figure 21 Design View of Unit Table..............................................................................26
Figure 22 Datasheet View of Unit Table.........................................................................26
Figure 23 Form view of Student Table...........................................................................27
Figure 24 Form view of Lecturer Table..........................................................................27
Figure 25 Report View of Admin....................................................................................28
Figure 26 Report View of Campus.................................................................................28
Figure 27 Relationship Diagram of Table.......................................................................29
Figure 28 Design View of Table.....................................................................................29
Figure 29 Datasheet View of Table................................................................................30
Figure 30 Design view of Query 1..................................................................................33
Figure 31 Output of Query 1.......................................................................................... 33
Figure 32 Report View of Query 1..................................................................................34
Figure 33 Design View of Query 2.................................................................................35
Figure 34 Output of Query 2.......................................................................................... 35
Figure 35 Report View of Query 2..................................................................................36
Figure 36 Design View of Query 3.................................................................................37
Figure 37 Output of Query 3.......................................................................................... 37
Figure 38 Report View of Query3...................................................................................38
Figure 39 Design View of Query 4.................................................................................39
Figure 40 Output of Query 4.......................................................................................... 39
Figure 41 Report View of Query 4..................................................................................40

Figure 42 Design View of Query 5.................................................................................41
Figure 43 Output in Query 5...........................................................................................41
Figure 44 Report view of Query 5..................................................................................42
Figure 45 Design View of Query 6.................................................................................43
Figure 46 Output of Query 6.......................................................................................... 43
Figure 47 Report View of Query 6..................................................................................43
Figure 48 SQL View Manager Academic Query............................................................44
Figure 49 Output of Manager Academic Query..............................................................44
Figure 50 SQL View of Admin Query.............................................................................45
Figure 51 Output of Admin Query..................................................................................45
Figure 52 SQL view of Student Query............................................................................45
Figure 53 Output of Student Query................................................................................46
Figure 54 Test Case Plan 1........................................................................................... 49
Figure 55 Test Case Plan 2........................................................................................... 50
Figure 56 Test Case Plan 3........................................................................................... 50
Figure 57 Test Case Plan 4........................................................................................... 51
Figure 58 Test Case Plan 5........................................................................................... 51
Figure 59 Test Case Plan 6........................................................................................... 52
Figure 60 Form view of Student Table...........................................................................53
Figure 61 Form view of Lecturer Table..........................................................................53
Figure 62 Form View of Unit Table.................................................................................54
Figure 63 Form view of Manager Academic Table.........................................................54
Figure 64 Form View Of Course Table...........................................................................55
Figure 65 Validation on the Student Table.....................................................................56
Figure 66 Control Mechanism of Admin table................................................................57
Figure 67 Control Mechanism of Lecturer Table............................................................57
Figure 68 Control Mechanism of unit table.....................................................................58
List of Tables:
Table 1 First Normalization............................................................................................ 18
Table 2 Second Noramalization.....................................................................................18
Table 3 Third Normalization...........................................................................................19
Table 4 Feedback table..................................................................................................43
Table 5 Test Case Plan..................................................................................................44

Introduction
Analysis of system and its design is simply the entire procedure of breaking down of the
framework. In this assignment report, the discussion is made on the key issues and the
various related theoretical concept of the relational database. In the very first part of the
assignment, the discussion will be made on the different data models and schema.
Also, the database technologies and approaches will also be discussed. In the second
part of the assignment, the discussion will be made on ERD model and normalization on
the database of the Dominican College. Also, the database is implemented for the
Dominican college. In addition to, the database tools and the technique related to it will
be discussed.
In the third part of the assignment, the discussion will be made on the manipulation of
the data and various query tools and languages. In the very last section of this
assignment, the discussion will be made on various feedbacks will be observed by
different users, and various test case plan, the user documentation, verification and
validation, and control mechanism will be discussed in detail. The purpose of this
assignment is to make the user learn about the concepts of designing and analysis of
the data.

LO1 Understand data models and database technologies
1.1 Critically compare different data models and schemas
Database Models:
By this term database models, an individual can identify that it is the schema of the
abstract model where data elements are organized and standardize that how both
models are related to each other and in addition to the properties present in real-world
elements. It is also used for showing the database structure at the logical level, in
addition to the objects as well as the relationships and verifying the key constraints to
analyze how the data can be accessed and stored. Data models can be classified into
various types. Let us examine each one by one (Creately, 2017).
 Hierarchical Model: Hierarchy means organized the data in a structure of tree
having the records, each having a single unit on a root or single parent. The
order of the siblings is sorted on some specific and particular order. To describe
the relationship in the real world elements this model is very good.
Figure 1 Example of Database Model
Source: (Creately, 2017)
Limitations and Benefits:

The main benefit is that it is easy to work on this model and also, it allows the
information to be deleted and added to the database.
Limitation of this is that sometimes it can be very slow while searching the node
entities and also it can have only relationship of one-to-many.
 Network Model: It is the similar approach that of the hierarchical model which
usually allow many-to-many relationships in between the various linked records,
in addition to the many parent records. This model is constructed through the
sets of the related records which is based on mathematical theory of set. A set is
consisting of the parent record and a child record. In this model, there can be
more than one parent node possible and it is more organized in the form of
graph.
Figure 2 Network Model
Source: (Creately, 2017)
Limitations and Benefits:
The main benefits of using this model are that it gives many-many relationship
mostly and it is more flexible and easy to work access than other models.
As the database is more complex structured in this model and if it changes then
complete structure is changed which is very difficult. So this is the limitation of
this model.