Database Design and Analysis for Dominican College

This document details the design and analysis of a database for Dominican College. The database is designed using an Entity-Relationship (ER) diagram and normalized to 3NF to minimize data redundancy and anomalies. The database includes tables for students, tutors, courses, units, academic managers, and administrators. The document includes ER diagrams, table designs, data sheet views, forms, reports, and SQL queries. It also discusses the benefits of using MS Access and SQL for database development. The document concludes with a discussion of the technical documentation and installation procedures for the database.

Assignment – Data Analysis and Design
1 | P a g e

2 | P a g e

Database is a set of data stored on media accessible by a computer to simultaneously satisfy
several users selectively and in a very short time. They constitute the heart of the information
system.
There are 4 types of database Models:
Hierarchical DBs: the oldest ones based on a tree modeling of the data.
Relational DBs: organization of data into tables and exploitation using a declarative language
(eg Oracle, mySQL, Access).
DB Deductive: organization of data in table form and exploitation using a logical language.
DB Objects: Organize data as instances of hierarchical classes that have their own methods of
operation.
The modeling is carried out in three main stages which correspond to three different levels of
abstraction:
I. Conceptual level: represents the content of the database in conceptual terms, regardless of
any IT considerations.
II. Relational logic level: results from the translation of the conceptual schema into a schema
specific to a type of comic strip.
III. Physical level: is used to describe the organization and data access methods of the database.
Conceptual modeling
Modeling is a fundamental step in the design of the comic book, in that, on the one hand, it
determines the content of the comic strip and, on the other hand, defines the nature of the
relationships between the main concepts.
3 | P a g e

The basic elements of the ER (Entity-Relationship) or EA (Entity-Association) model
 The entities
 Attributes
 Relationship type: cardinalities
 The ID
Entity: defines as an object that can be distinctly identified. There are two categories of entities:
- Regular entities: its existence does not depend on the existence of another entity.
- Weak entities: its existence depends on the existence of another entity.
Ex: the CONTRACT entity exists only if the corresponding CLIENT entity is present.
Attributes: characteristics or properties of entities.
An attribute may be mandatory or optional and have a domain of values.
Relationships: represent the existing links between the entities.
Unlike entities, relationships do not have their own relationships. Relationships are
characterized, like entities, by a name and possibly attributes.
Cardinality: the complete description of a relationship requires the precise definition of the
participation of the entities. Cardinality is the number of participation of an entity in a
relationship.
4 | P a g e

The hierarchical model
The information is organized in a tree-like fashion (hierarchical), accessible only from the root
of this tree. It makes it easy to add elements and modify their structure. The main defect of this
representation comes from the fact that the access point to the information is unique (it is the
root of the hierarchical tree). This implies that any search for data requires the course of all, or
at least part, of the hierarchy, through the root. The most common example is that of the tree
structure used for file systems (although it is not a DBMS itself, as we shall see later), where we
navigate from the root from the system to subdirectories and files containing the data.
The semantic networks model
This model describes the operation of a network database. This type of database works on the
principle of grouping the different elements of the database by their meaning. All information
can be associated with each other and serve as an access point. This makes it a complex model
whose data extraction is difficult. The most concrete example of such a model is the semantic
web.
The entity / association model
The entity-association model is a type of conceptual schema that is used in particular by
relational databases. The entry points are independent of the structure of the database, the
user simply enters a query, without having to navigate in the database, and the machine is
responsible for executing it to achieve the expected data. The main disadvantage is the rigidity
of the structure of the defined model and the difficulties that it entails to evolve an existing
schema when a new need appears or a design error is discovered. This includes all systems
based on the SQL language.
5 | P a g e

How to build a conceptual diagram
The construction of a conceptual diagram can be realized in the following way:
1. Determine the list of entities.
2. for each entity:
(a) Establish the list of his attributes;
b) Among these, determine an identifier.
3. Determine the relationships between the entities.
4. for each relationship:
a) List the attributes specific to the relationship;
b) Check the dimension (binary, ternary, etc.);
c) Define the cardinalities.
5. Check the diagram obtained, including:
a) Remove transitivities;
b) Ensure that the diagram is connected;
c) Ensure that he responds to requests.
6. Validate with users.
Cardinality one by one: if and only if an employee can only be director in one department and a
department has only one employee as director.
Cardinality one to many: a department can occupy several employees who perform different
functions but each employee is part of only one department.
6 | P a g e

Multi-to-many cardinality: One type of product can be manufactured in several factories and a
given factory can manufacture several types of products.
The cardinalities presented above are called maximum cardinalities in that they represent the
maximum number of participations of an entity in a relationship.
In contrast, the minimum cardinality is the minimum number of participations of an entity in a
relationship. The minimum cardinality can be 0 or 1.
The maximal and minimal cardinalities reflect the constraints specific to the entities and
relations. In a conceptual diagram, they are represented as follows:
0-1 none or only one
1-1 one and only one
0-N none or more
1-N one or more
The identifier: among all the attributes of the entity, the identifier is an attribute or a set of
attributes making it possible to determine one and only one entity within the set. Graphically
the identifiers are the underlined attributes. The weak entity will have an identifier composed
of the identifier of the entity on which it depends and another attribute.
A situation to be modeled can have several different schemes, each model having advantages
and disadvantages.
To measure the quality of ER modeling there are several criteria to use in combination:
Expressiveness: reflects the semantic richness of the schema. Can be characterized for example
by the number of concepts and / or constraints expressed in the table;
Minimality : tends to favor schemes with a minimum number of redundancies;
Readability: consists of evaluating the graphical representation itself;
7 | P a g e

Simplicity: favors schemas containing a minimum number of concepts. It can be measured for
example by calculating the number of entities and associations present on a diagram.
8 | P a g e

The relational database is designed for the Dominican college. The database is designing by using the
designing tool such as ER diagram tool and to develop the normalised data we designed the
normalisation with in all forms like 1 NF, 2 NF and 3 NF. The main purpose of the designing is to
complete all given requirement in the case scenario of user and system.
The ER Diagram which is designed to develop the database for Dominican College:
Figure 1 ER diagram
The un-normalised form of data is given as:
9 | P a g e

First normalisation
The first normalisation is done to segregate the data in to various table and to remove the anomalies:
10 | P a g e

Figure 2 1 Normalisation
2-Normalisation:
11 | P a g e

Figure 3 2-Normalisation
3 Normalisation:
The 3 Normalisation is designed to reduce the data anomalies and data redundancy and data complexity
from the database. The data is stored in secure way and able to easy search.
12 | P a g e

Figure 4 3-Normalisation
13 | P a g e

To develop the database for Dominican college first design the tables and then form to insert the data in
the database. Here, I have presented the tables which are designed in the Dominican Database to store
the information:
Tables design view:
Figure 5 Academic Manager design view
Figure 6 Unit table design view
14 | P a g e

Figure 7 Admin Design view
15 | P a g e

Figure 8 Course Design view
16 | P a g e

Figure 9 Student table design view
Figure 10 Tutor table design view
17 | P a g e

Datasheet view after store the information in the Dominican database:
Figure 11 Datasheet view of unit table
Figure 12 Tutor data sheet view
Figure 13 Student table
Figure 14 Datasheet view of course table
18 | P a g e

Figure 15 Admin datasheet view
Figure 16 Academic manager table datasheet view
19 | P a g e

There are number of database tool which are used to develop and design the database to enhancing the
user interface. The techniques are given as
Query tool which is used to create the interaction between the users and the database system with
generating the tables and queries.
Validation: this technique is applied to secure the database from the unknown users; this technique is
applied on the tables at the time of designing.
For user interface, I have designed the forms of the table to insert the data in the table and to able to
delete the data from the tables which are not required storing in the Dominican college.
Figure 17 Academic manager form
Figure 18 Admin Form
20 | P a g e

Figure 19 Course Form
Figure 20 Student Form
21 | P a g e

Figure 21 Tutor form
Figure 22 Unit Form
22 | P a g e

Reports are design to show the details which are search by designing the query.
Figure 23 Report for academic manager query
Figure 24 Academic manager should be able to list down
23 | P a g e

Figure 25 Query 3 Report
Figure 26 Query 4
24 | P a g e

Figure 27 Course detail report
Figure 28 Report of lecturer request
25 | P a g e

Figure 29 Lecturer_course report
Figure 30 Student Course Report
26 | P a g e

The Query and manipulation is used to give statements to create the interaction between the users and
system. With the use of query and manipulation tool the user can search the specific data from the data.
These tools develop the database more detailed system and save time of the organization. There are
several types of tools like SQL tool, SQLite, etc.
Benefits of the SQL using;
 It allows various views with the MS Access.
 It is portable language and it has ability to execute the query on each of the devices.
 The MS access tool is available with very low cost and effective for each of the organization.
 The queries and tables are easily developed with this tool without giving more effort.
Now, discus the benefits of the MS Access which is used to develop the database for Dominican college:
 Easily available in the market
 Users can learn it easily
 It does not required more space
 It provides various set of views of the tables and query such as design view, datasheet view and
sql view.
The evidence of that is given below:
27 | P a g e

Figure 31 Student table design view
28 | P a g e

Figure 32 Tutor table design view
Datasheet view after store the information in the Dominican database:
Figure 33 Datasheet view of unit table
Figure 34 Tutor data sheet view
29 | P a g e

To implement the query language, I have used the SQL query language that helps to develop the query.
The query is designing to search the specific information from the database of Dominican college and to
present, query data, I have design the reports for each query.
Now, I have presented the query with all views:
Figure 35 SQL view
Figure 36 Design view of Query 1Academiclecturer
30 | P a g e

Figure 37 Design view of Query 2-Academic manager query
Figure 38 SQL View for Academic manager query
31 | P a g e

Figure 39 Output
Query 3:
Figure 40 Admin Course query
Figure 41 Output
Query 4:
32 | P a g e

Figure 42 Desugn view of admin query 4
Figure 43 Admin Query 4 SQL view
33 | P a g e

Figure 44 Datasheet view
QUERY 5: Admin-student
Figure 45 SQL view of query 5
34 | P a g e

Figure 46 Output of Query 5
Query 6
Figure 47 SQL view of query 6-lecturer course
35 | P a g e

Figure 48 Output view of query 6-lecturer course
Query 7
Figure 49 SQL view of Lectures request
36 | P a g e

Figure 50 Lecturer request output
Query 8
Figure 51 SQL view of Student Course
Figure 52 Output of student course query
37 | P a g e

The database is designed to store the information of the student and tutor, courses and other important
entity of the Dominican college. To extract the information the data is designed and it is designed by
using the MS Access and SQL. Here I have used the MS ACCESS tool to develop the database and to
generate the query I have used the SQL query language as explain in above section. The advantages of
the SQL are given below:
 It allows various views with the MS Access.
 It is portable language and it has ability to execute the query on each of the devices.
 The MS access tool is available with very low cost and effective for each of the organization.
 The queries and tables are easily developed with this tool without giving more effort.
Mostly the SQL is used to develop the database and query to fulfil the user and system requirement
which is given in the case scenario of the Dominican college. With using the SQL language te developed
query are given below:
Figure 53 SQL view
Figure 54 SQL View for Academic manager query
Query 3:
38 | P a g e

Figure 55 Admin Course query
Query 4:
Figure 56 Admin Query 4 SQL view
QUERY 5: Admin-student
Figure 57 SQL view of query 5
39 | P a g e

Query 6
Figure 58 SQL view of query 6-lecturer course
Query 7
Figure 59 SQL view of Lectures request
Query 8
Figure 60 SQL view of Student Course
The data manipulation language consists of four main instructions:
SELECT: for querying one or more tables;
INSERT: for adding rows to a table;
UPDATE: for the modification of lines;
DELETE: for the deletion of lines.
40 | P a g e

Column constraints and table constraints materialize the various integrity constraints that the
SBD supports systematic verification:
The unique constraint (UNIQUE clause): ensures that there is no duplicate value in the column
The constraint of obligation (NOT NULL clause) authorizes and manages a particular value called
the null value. This null value reflects both the missing value and the non-existent value (e.g.
the maiden name for a man). NOT NULL is always a column constraint, and cannot be a table
constraint.
The primary key constraint (PRIMARY KEY clause): allows you to choose a single column (or a
group of columns) privileged in a table;
The referential integrity constraint admits two syntaxes depending on whether it relates to a
column (column constraint) or to several (table constraint). In the first case, we use the
REFERENCES clause. In the second, we use the expression FOREIGN KEY. The foreign key refers
to the primary key of another Table. It translates a semantic link with another Table.
The semantic constraint (CHECK clause): allows to specify the logical conditions relating to one
or more columns of the same table.
41 | P a g e

Now, I have presented the query with all views:
Figure 61 SQL view
Figure 62 Design view of Query 1Academiclecturer
42 | P a g e

Figure 63 Design view of Query 2-Academic manager query
Figure 64 SQL View for Academic manager query
43 | P a g e

Figure 65 Output
Query 3:
Figure 66 Admin Course query
Figure 67 Output
44 | P a g e

Query 4:
Figure 68 Desugn view of admin query 4
Figure 69 Admin Query 4 SQL view
45 | P a g e

Figure 70 Datasheet view
QUERY 5: Admin-student
Figure 71 SQL view of query 5
46 | P a g e

Figure 72 Output of Query 5
Query 6
Figure 73 SQL view of query 6-lecturer course
47 | P a g e

Figure 74 Output view of query 6-lecturer course
Query 7
Figure 75 SQL view of Lectures request
48 | P a g e

Figure 76 Lecturer request output
Query 8
Figure 77 SQL view of Student Course
Figure 78 Output of student course query
49 | P a g e

Figure 79 SQL view
Figure 80 SQL View for Academic manager query
Query 3:
Figure 81 Admin Course query
Query 4:
50 | P a g e

Figure 82 Admin Query 4 SQL view
QUERY 5: Admin-student
Figure 83 SQL view of query 5
Query 6
Figure 84 SQL view of query 6-lecturer course
Query 7
51 | P a g e

Figure 85 SQL view of Lectures request
Query 8
Figure 86 SQL view of Student Course
The database is developed with the MS Database Management System ACCESS 2016. The
program consists of 2 DB files. It contains all forms, reports and programs. This file
communicates with the data file by means of linked tables. This file is launched when the user
clicks on the launch icon. This file contains no data, its contents are not changed during the
operation of the software. The Data file contains all the data tables. This file contains all the
data entered by the user, it must be saved daily. The program can operate either as a single
station or as a network. The database can be tested as a
 Single-user operation: the 2 files (program and data) are installed in the same folder on
the hard drive.
 Network operation: the data file is installed on the server and the file program is
installed on each of the client
 In this manner the system can be tested as a single user mode or a network mode.
52 | P a g e

Tables
Tables design view:
Figure 87 Academic Manager design view
Figure 88 Unit table design view
53 | P a g e

Figure 89 Admin Design view
54 | P a g e

Figure 90 Course Design view
55 | P a g e

Figure 91 Student table design view
Figure 92 Tutor table design view
56 | P a g e

Datasheet view after store the information in the Dominican database:
Figure 93 Datasheet view of unit table
Figure 94 Tutor data sheet view
Figure 95 Student table
Figure 96 Datasheet view of course table
57 | P a g e

Figure 97 Admin datasheet view
Figure 98 Academic manager table datasheet view
Reports
Figure 99 Academic manager form
Figure 100 Admin Form
58 | P a g e

Figure 101 Course Form
Figure 102 Student Form
59 | P a g e

Figure 103 Tutor form
Figure 104 Unit Form
60 | P a g e

Reports are design to show the details which are search by designing the query.
Figure 105 Report for academic manager query
Figure 106 Academic manager should be able to list down
61 | P a g e

Figure 107 Query 3 Report
Figure 108 Query 4
62 | P a g e

Figure 109 Course detail report
Figure 110 Report of lecturer request
63 | P a g e

Figure 111 Lecturer_course report
Figure 112 Student Course Report
64 | P a g e

Forms
Figure 113 Academic manager form
Figure 114 Admin Form
Figure 115 Course Form
65 | P a g e

Figure 116 Student Form
Figure 117 Tutor form
66 | P a g e

Figure 118 Unit Form
The technical documentation presents:
 Specifications necessary for the installation and configuration of the program;
 The logical structures of the database;
 The relational model;
 The tables of nomenclature
The documentation is intended for anyone who wants to install the database and exploit the
data with custom queries as well as IT and designers who will have to administer and / or
change the structure of the databases.
Required configuration
Hardware configuration: the program requires a PC computer having the following
characteristics: • Processor speed: 1GHz minimum (recommended 2GHz), • RAM: 1 GB
minimum (recommended 2 GB) • Hard disk at least 100 GB (recommended 250 GB) the space
required to install the program is 135 MB. The program requires a free space of 300 MB on the
hard disk. 2.2.2. Software configuration Operating system: Windows XP, Vista or Windows
7Software: Excel and MS ACCES versions 2003, 2007 or 2010
67 | P a g e

Database Queries
Figure 119 SQL view
Figure 120 SQL View for Academic manager query
Query 3:
Figure 121 Admin Course query
Query 4:
68 | P a g e

Figure 122 Admin Query 4 SQL view
QUERY 5: Admin-student
Figure 123 SQL view of query 5
Query 6
Figure 124 SQL view of query 6-lecturer course
69 | P a g e

Query 7
Figure 125 SQL view of Lectures request
Query 8
Figure 126 SQL view of Student Course
Standalone installation: the stand-alone installation procedure can be used for data entry or for
consultation. However, if the entry is on a single-user installation,
Single station tree: The tree of the program installed in a single station is composed of a root
folder whose name and location on the hard drive is left to the user's choice.
1) Create the root folder on the hard disk.
2) Create a subfolder named "BD" on the root of the program and copy into this folder the 2
files
3) Copy the subfolders with their content on the root of the program.
4) The subfolders "Excel" and "BD_Save" will be created automatically by theprogram.
5) Create a shortcut on the desktop to launch the file.
6) Launch the program file Karst_Jura.mdb once, if it cannot find the data file, it will suggest you
locate it on the hard drive using the login screen of the database.
70 | P a g e

The verification and the validation can be done through the configuration manager. The
configuration of the program is done at the first start of the program. If the program does not
find the data file, the next screen offers you to choose the location of the data file:The program
home screen will appear and the name and full path of the database data appear at the bottom
right of the screen If the database selection screen does not appear on the first launch, you can
use the "Configuration" command on the program's home screen to change the path to the
data.
Figure 127 SQL view
Figure 128 SQL View for Academic manager query
71 | P a g e

Query 3:
Figure 129 Admin Course query
Query 4:
Figure 130 Admin Query 4 SQL view
QUERY 5: Admin-student
Figure 131 SQL view of query 5
Query 6
72 | P a g e

Figure 132 SQL view of query 6-lecturer course
Query 7
Figure 133 SQL view of Lectures request
Query 8
Figure 134 SQL view of Student Course
.
73 | P a g e

Anyone who starts the program can view all the data and edit the datasheets in a consultation
database (the one that is delivered on the CD). To make entries in the complete database
delivered to the client, we can use the "Access Code" command and enter the passcode. Each
user is given the login id and passcode as a control mechanism.
Different Level that is Created.
Figure 135 Academic manager form
Figure 136 Admin Form
74 | P a g e

Figure 137 Course Form
Figure 138 Student Form
75 | P a g e

Figure 139 Tutor form
Figure 140 Unit Form
76 | P a g e

References
Rouse, M. (2007), Entity-Relationship Diagram
Mohanty, A. (2014). How to Draw E R Diagram Using MySQL Database Engine?
Coronel, C. (2013), Morris, S. (2013), Rob, P. (2013). Database Systems design, implementation
and management 10th edition
77 | P a g e