Dad - 16 - 001

Assignment Brief
BTEC Level 4-5 HNC/HND Diploma (QCF)
To be filled by the Student

Name of the Student :
Edexcel No : Registration No: Batch No:
Unit Assessment Information
Qualification : Higher National Diploma in Computing and Systems Development
Unit Code & Title : H/601/1991 - Unit 33 - Data Analysis and Design
Assessment Title & No’s: Database solution for Stafford University - (DAD – 16 – 001)
Learning outcomes and grading opportunities:

LO 01: Understand data models and database technologies
Learning LO1.1 LO1.2 LO1.3
Outcomes
LO 02: Be able to design and implement relational database systems
Outcomes
LO 03: Be able to use manipulation and querying tools
Outcomes
LO 04: Be able to test and document relational database systems
Learning LO4.1 LO4.2 LO4.3 LO4.4 LO4.5
Outcomes
Merit and Distinction Descriptor

M1 M2 M3 D1 D2 D3
Date Issued : Date Due :
Date of Submission:
Assessor : Date Assessed:
Internal Verifier (IV): Date of IV:
Name Reg No: DAD Page 1 of 95

General Guidelines
1. A Cover page or title page – You should always attach a title page to your assignment.
Use previous page as your cover sheet and be sure to fill the details correctly.
2. This entire brief should be attached in first before you start answering.
3. All the assignments should prepare using word processing software.
4. All the assignments should print in A4 sized paper, and make sure to only use one side
printing.
5. Allow 1” margin on each side of the paper. But on the left side you will need to leave
room for binging.
6. Ensure that your assignment is stapled or secured together in a binder of some sort and
attach the Softcopy (CD) of your final document, system on last page.
Word Processing Rules

1. Use a font type that will make easy for your examiner to read. The font size should be 12
point, and should be in the style of Time New Roman.
2. Use 1.5 line word-processing. Left justify all paragraphs.
3. Ensure that all headings are consistent in terms of size and font style.
4. Use footer function on the word processor to insert Your Name, Subject, Assignment No,
and Page Number on each page. This is useful if individual sheets become detached for
any reason.
5. Use word processing application spell check and grammar check function to help edit
your assignment.
6. Ensure that your printer’s output is of a good quality and that you have enough ink to
print your entire assignment.
Important Points:
1. Check carefully the hand in date and the instructions given with the assignment. Late
submissions will not be accepted.
2. Ensure that you give yourself enough time to complete the assignment by the due date.
3. Don’t leave things such as printing to the last minute – excuses of this nature will not be
accepted for failure to hand in the work on time.
4. You must take responsibility for managing your own time effectively.
5. If you are unable to hand in your assignment on time and have valid reasons such as
illness, you may apply (in writing) for an extension.
6. Failure to achieve at least a PASS grade will result in a REFERRAL grade being given.
7. Non-submission of work without valid reasons will lead to an automatic REFERRAL.
You will then be asked to complete an alternative assignment.
8. Take great care that if you use other people’s work or ideas in your assignment, you
properly reference them, using the HARVARD referencing system, in you text and any
bibliography, otherwise you may be guilty of plagiarism.

9. If you are caught plagiarizing you could have your grade reduced to A REFERRAL or at
worst you could be excluded from the course.
Statement of Originality and Student Declaration
I hereby, declare that I know what plagiarism entails, namely to use another’s work and to
present it as my own without attributing the sources in the correct way. I further understand what
it means to copy another’s work.
1. I know that plagiarism is a punishable offence because it constitutes theft.

2. I understand the plagiarism and copying policy of the Edexcel UK.
3. I know what the consequences will be if I plagiaries or copy another’s work in any of the
assignments for this program.
4. I declare therefore that all work presented by me for every aspects of my program, will be
my own, and where I have made use of another’s work, I will attribute the source in the
correct way.
5. I acknowledge that the attachment of this document signed or not, constitutes a binding
agreement between myself and Edexcel UK.
6. I understand that my assignment will not be considered as submitted if this document is
not attached to the attached.
Student’s Signature: …………………………… Date:.

………………

Case study
Stafford University
Stafford University is a large institution which provides education to vast number of students.
The University consists of a number of faculties, such as the Art Faculty, the Science Faculty,
and so on. Each faculty has a name, dean and building. Each faculty offers different programs
and each program can be offered by only one faculty. Each program has a unique code, title,
level and duration. Some programs may have one or more prerequisite courses and one course
can be the prerequisite course of some other courses.
Students work on courses and are awarded a grade in any course if he/she passes the course.
Otherwise the student has to re-take the failed course. The system needs to record the year and
term in which the course was taken and the grade awarded to the student. Every student has a
unique ID. The system also keeps the student name, birthday and the year he/she enrolled in the
course.
The school employs lecturers to teach the students. Each lecturer is assigned an ID which is
unique across the whole university. The system keeps the lecturer’s name, title and the office
room. A supervisor maybe in charge of several lecturers, but a lecturer, however reports to only
one supervisor. A lecturer can teach many different courses. A course may also have been taught
by many different lecturers.
The university is operated by committees. Each faculty has to have a number of committees with
the same titles across the university, such as the Faculty Executive, the Post Graduate Studies
Committee, the Health and Sanity Committee, and so on. A committee’s members are all
lecturers. A lecturer may be a member of several committees.

Task 1: LO 1: Understand data models and database technologies
Critically compare different data models such as Hierarchical, Network, Relational and justify
which data model best fits the scenario (AC 1.1) (D 1.1)
Prepare a power point presentation to critically discuss the benefits and limitations of different
database technologies such as distributed database, data warehouse, Data mining, Multimedia
database. (AC 1.2)
Analyze different approaches such as Top down and Bottom up which can be used to design a
database and justify how these approaches relate to database design (AC 1.3)
Task 2: LO 2: Be able to design and implement relational database systems
2.1 Identify entities and attributes of the given scenario and draw an ER diagram by showing
different attribute types and cardinality constraints
2.2 Translate the ER diagram into relational schemas by showing the relation with each through
primary keys and foreign keys (AC 2.1)
2.3 Design a relational database system according to the ER diagram you have created (Use SQL
DDL statements) (AC 2.2)
2.4 Provide evidence of the use of a suitable IDE to create a simple interface to insert, update and
delete data in the database (AC 2.3)
Task 3: LO 3: Be able to use manipulation and querying tools
3.1 Explain the benefits of different manipulation and query tools which can be used to design
the relational database system (AC 3.1)
3.2 Perform following SQL queries using the tables you have created and provide the output
gained
(AC 3.2) (M 1.2)
Display the details of students who have awarded by a grade with particular lecturer’s ID and the
lecturer’s name using Join queries
Delete all the details of the students who have not achieved a PASSS grade
Write an update query with the help of Join queries to update a particular record in the database.
3.3 Critically evaluate how different query tools can be used to obtain meaningful data with its
unique functionalities (AC 3.3)

Task 4: LO 4: Be able to test and document relational database systems
4.1 Provide a suitable test plan to support the documentation of the testing process (AC 4.2)
4.2 Provide relevant test cases for the database you have implemented (AC 4.1) (D 3.2)
(Note: Learner needs to give expected results in a tabular format and screenshots of the actual
results with the conclusion)
4.3 Create user documentation for a developed relational database system with the help of help
menus, Pop ups (AC 4.3)
4.4 Provide evidences on how to address verification and validation (AC 4.4)
4.5 explain how control mechanisms have been used and the importance of these mechanisms for
the security of the database (AC 4.5)

Observation Sheet
Activit Activity Learning Date Feedback

y Outcome
(LO) (Pass/ Redo)
No
1 Create tables with Entity integrity and LO2.2

Referential integrity
2 Data manipulations LO3.2
(INSERT/Update/Delete)
3 Perform join queries M 1.2
4 Drop Foreign key of a table (ON LO4.1

DELETE RESTRICT)
Drop Foreign key of a table (ON
DELETE CASCADE)
Comments:
Assessor Name :…………………………………………….
Assessor Signature :…………………………………………….

PRESENTATION EVALUATION FORM
Context – This presentation assessment criteria sheet makes provision for the assessment of
student engagement with the group and the handling of questions. The different features of the
assessment criteria can be adjusted and given different weightings in the overall grid as required.
Subject- Student/s, Name/s-
Excellent Very Good Meets Below

Good Standard Standard
Information Detailed Detailed Generally Limited Very Limited
Accurate Accurate accurate and knowledge with many
Relevant Relevant relevant, but with some errors and
Highlight some gaps significant gaps
the key and/or gaps and/or
points irrelevant errors
material
Structure Rigorousl Generally Not always Argument Muddled,
y argued, clearly logical may underdevelo incoherent
Logical, argued be overly ped and not
easy to and influenced clearly
follow logical by mentioned
secondary
literature
rather than
the
requirement
of the topic
Interpretation Extensive Attempts Little Fairly Entirely
evidence to go attempt to superficial derivative
of beyond go beyond and Generally
independ the ideas or criticize generally superficial
ent presented secondary derivative
thought in the literature and
and secondary uncritical
critical literature
analysis
Use of Key Most Some Some Little or no
Evidence points points illustrative evidence evidence
supported illustrated material, but mentioned, discussed
with with not critically but not
evidence, relevant evaluated integrated in
critically evidence to
evaluated presentation
or evaluated

Presentation Clearly, Generally Conveys Not always Clumsy,
skills Lively, clear and meaning, clear or easy disjointed,
Imaginati lively, use but to follow, difficult to
ve, good of sometimes unimaginati follow dull
use of appropriat unclear or ve and not
visual e visual clumsy engaging
aids aids
Time Perfectly Well More or less Significantl Significantly
Management timed organized, right length, y over time, under or
well more or but some material overtime,
organized less to materials fairly clearly have
time not covered disorganize not tested out
properly as d and material
a result of rushed beforehand,
overruns by disorganized
five minutes
or more
Best Features-
Suggestions to improve-
Assessor- Date-
IV- Date-

Possible
Outcomes/Criteria for PASS Page Feedback
evidence
LO1- Understand data models and database technologies
1.1 critically compare different data models and schemas Task 1.1
1.2 critically discuss the benefits and limitations of different database Task 1.2
technologies
1.3 analyze different approaches to database design Task 1.3
LO2 - Be able to design and implement relational database systems

2.1 design a relational database system to meet a given requirement Task 2.1 – 2.2
2.2 build a relational database system based on a prepared design Task 2.3
2.3 apply a range of database tools and techniques to enhance the user Task 2.4
interface
LO3 -Be able to use manipulation and querying tools
3.1 explain the benefits of using manipulation and query tools in a Task 3.1
relational database system
3.2 implement a query language into the relational database system Task 3.2
3.3 critically evaluate how meaningful data has been extracted Task 3.3
through the use of query tools
LO4 - Be able to test and document relational database systems
4.1 critically review and test a relational database system Task 4.1

4.2 create documentation to support the implementation and testing of Task 4.1
a relational database system
4.3 create user documentation for a developed relational database Task 4.2
system
4.4 explain how verification and validation have been addressed Task 4.3
4.5 explain how control mechanisms have been used. Task 4.4
Grade Descriptor for MERIT Possible evidence Feedback

M1 Identify and apply strategies to find appropriate Task 3.2
solutions Perform join queries to get the
desired outcome by summarizing
M1.2 Complex problems with more than one variable have data from two or more tables
been explored
M2 Select / design appropriate methods / techniques Proper use of Harvard referencing
M2.3 A range of sources of information has been used has been used and considerable
amount of references have
produced from different sources.
Use of correct in text citations
and the end list references
M3 Present and communicate appropriate findings Documentation is well structured
adhering to the formatting
M3.1 The appropriate structure and approach has been used guidelines with non-overlapping
facts.
Data provided are accurate,
reliable and consistent

Grade Descriptor for DISTINCTION Possible evidence Feedback
D1 Use critical reflection to evaluate own work and Task 1.1
justify valid conclusions Clearly justify by giving a
conclusion, why relational
D1.1 Conclusions have been arrived at through synthesis model best fits the scenario with
of ideas and have been justified a proper comparison with other
data models
D2 Take responsibility for managing and organizing Provide a Gantt chart in the
activities Appendix section and submit the
assignment on time
D2.3 Activities have been managed
D3 Demonstrate convergent / lateral / creative Task 4.2
thinking Include proper test cases to
make sure that the built database
D3.2 Self-evaluation has taken place system is working accurately by
self-evaluating your own work

Strengths: Weaknesses:
Future Improvements & Assessor Comment:
Assessor: Signature: Date: ____/____/______
Internal Verifier’s Comments:
Internal Verifier: Signature: Date: ____/____/______

Table of Contents
Table of Contents-------------------------------------------------------------------------------------14
Acknowledgement------------------------------------------------------------------------------------16
Task 1: Understand data models and database technologies-----------------------------------17
1.1 Critically compare different data models such as Hierarchical, Network, and Relational and justify which data model best fits the scenario
17
1.1.2 Justification of the suitable data model-----------------------------------------------18
1.2 Prepare a power point presentation to critically discuss the benefits and limitations of different database technologies such as distributed
database, data warehouse, Data mining, Multimedia database---------------------------------19
1.2.1Advantages and Disadvantages of Data Warehouse---------------------------------20
1.2.2 Advantages and Disadvantages of Data Mining-------------------------------------21
1.3 Analyze different approaches such as Top down and Bottom up which can be used to design a database and justify how these approaches
relate to database design-----------------------------------------------------------------------------23
1.3.1 Different Approaches-------------------------------------------------------------------23
1.3.2 Approaches to Database Design-------------------------------------------------------24
1.3.2.1 Differences between Top down and Bottom Up----------------------------------24
1.3.3 Database Planning-----------------------------------------------------------------------25
1.3.4 Systems Definition----------------------------------------------------------------------25
1.3.5 Requirements Collection and Analysis-----------------------------------------------25
1.3.6 Database Design-------------------------------------------------------------------------26
1.3.7 Database Management System Selection---------------------------------------------26

1.3.8 Application Design----------------------------------------------------------------------26
1.3.9 Prototyping-------------------------------------------------------------------------------26
1.3.10 Implementation-------------------------------------------------------------------------27
1.3.11 Data Conversion and Loading--------------------------------------------------------27
1.3.12 Testing-----------------------------------------------------------------------------------27
1.3.13 Operational Maintenance--------------------------------------------------------------27
Task 2: Be able to design and implement relational database systems------------------------28
2.1 Identify entities and attributes of the given scenario and draw an ER diagram by showing different attribute types and cardinality
constraints------------------------------------------------------------------------------------------28
2.2 Translate the ER diagram into relational schemas by showing the relation with each through primary keys and foreign keys 29
2.3 Design a relational database system according to the ER diagram you have created (Use SQL DDL statements) 30
2.4 Provide evidence of the use of a suitable IDE to create a simple interface to insert, update and delete data in the database 32
Task 3: Be able to use manipulation and querying tools----------------------------------------39
3.1 Explain the benefits of different manipulation and query tools which can be used to design the relational database system39
3.2 Perform following SQL queries using the tables you have created and provide the output gained 40
3.3 Critically evaluate how different query tools can be used to obtain meaningful data with its unique functionalities 42
3.3.1 Explanation of query tools--------------------------------------------------------------42
Task 4: Be able to test and document relational database systems----------------------------43
4.2 Provide relevant test cases for the database you have implemented-------------------48
4.3 Create user documentation for a developed relational database system with the help of help menus, pop ups 50
4.3.1 Steps to create a database---------------------------------------------------------------51
4.4 Provide evidences on how to address verification and validation----------------------54

4.4.1 Examples for verification---------------------------------------------------------------55
4.4.2 Example for Validation-----------------------------------------------------------------55
4.5 explain how control mechanisms have been used and the importance of these mechanisms for the security of the database 56
Gantt chart---------------------------------------------------------------------------------------------57
Summary-----------------------------------------------------------------------------------------------58
Self-Criticism-----------------------------------------------------------------------------------------59
References---------------------------------------------------------------------------------------------60
Indexes-------------------------------------------------------------------------------------------------61
Glossary------------------------------------------------------------------------------------------------62
Table of Figures
Figure 1 database technologies---------------------------------------------------------------------20

Figure 2 advantages of distributed database------------------------------------------------------20
Figure 3 Disadvantages of Distributed database-------------------------------------------------21
Figure 4 Advantages of Data Warehouse---------------------------------------------------------21

Figure 5 Disadvantages of Data warehouse-------------------------------------------------------22
Figure 6 Advantages of Data Mining--------------------------------------------------------------22
Figure 7 Disadvantages of Data Mining-----------------------------------------------------------23
Figure 8 Database design----------------------------------------------------------------------------24
Figure 9 ERD Diagram------------------------------------------------------------------------------29
Figure 10 Relational diagram-----------------------------------------------------------------------30
Figure 11 Login form--------------------------------------------------------------------------------33
Figure 12 Main form---------------------------------------------------------------------------------33
Figure 13 Course form------------------------------------------------------------------------------34
Figure 14 Faculty form------------------------------------------------------------------------------35
Figure 15 Lecturer form-----------------------------------------------------------------------------37
Figure 16 Student form------------------------------------------------------------------------------38
Figure 17 Details of Students using join query---------------------------------------------------41
Figure 18 Delete query where grade is Fail-------------------------------------------------------41
Figure 19 Update using Join query-----------------------------------------------------------------41
Figure 20 Update in Course-------------------------------------------------------------------------42
Figure 21 Update in Lecturer-----------------------------------------------------------------------42
Figure 22 Connecting using the server name-----------------------------------------------------52
Figure 23 steps to creating new database----------------------------------------------------------52
Figure 24 Steps for creating new query-----------------------------------------------------------53
Figure 25 List of tables in query-------------------------------------------------------------------53
Figure 26 Executing table in query----------------------------------------------------------------54

Figure 27 Data inserted------------------------------------------------------------------------------54
Figure 28 result---------------------------------------------------------------------------------------54
Figure 29 Create procedure-------------------------------------------------------------------------56
Figure 30 after insert trigger------------------------------------------------------------------------56
Figure 31 Gantt chart--------------------------------------------------------------------------------58
List of Tables
Table 1 Login form results...................................................................................................45

Table 3 Course table details.................................................................................................47
Table 4 Lecturer table details...............................................................................................48
Table 5 Faculty table details................................................................................................49
Table 6 Test case- query......................................................................................................50
Table 7 Test case – query 2..................................................................................................51
Table 8 Description of validation and verification...............................................................56

Acknowledgement
I would like to express my sincere thoughts and gratitude to our lecturer, ____________________ who has always been there for us, whenever
we needed their assistance, by their teaching it is certainly a new experience to us.
It is our main opportunity to express our deep sense of gratitude and constant encouragement as well as motivation that has helped sustain our
efforts in assisting us in completing this assignment
I have worked hard in completing this assignment, and im proud to say that all my effort and energy has been put into this in order to complete
it. And most of all, I have gained an immense knowledge on databases that we have never learnt before
Special thanks to Esoft, for inspiring us, and my sincere thanks to all the lecturers, who has helped us overcome these assignments. Last but not
the least, my sincere gratitude to my parents, who has always been by my side and through their assistance I was able to complete this
assignment.
[Author has kept this space blank on purpose]

Task 1: Understand data models and database technologies
1.1 Critically compare different data models such as Hierarchical, Network, and Relational and justify which data model best fits the
scenario
1.1.1 Different data model types
The definition of data models specifies the structure of the database and how it is modelled. This is to introduce abstraction in a DBMS . This
shows how data is been connected to each other and by what means they are been processed and the ways they are stored inside the system.
There are various types of data models. They are explained below:
Hierarchical Model – it covers a large area, it refers to a lot of sets that are compared like various multi-level models where all the essential
information is compared to a larger form. It is quite similar to the network model as it displays a collection of records that are found in trees
besides arbitrary graphs

Network model – may be a database model that is intended similarly as an adaptable methodology on speaking to Questions Furthermore their
associations. An exceptional characteristic of the organize model will be its diagram which is seen as An chart the place association sorts are arcs
and item sorts need aid hubs. Dissimilar to other database models, the organize model’s diagram is not limited to a chance to be a grid or
progressive structure.
Relational model – this model may be the theoretical premise of social databases. It will be a system for structuring information utilizing
relations which would grid such as scientific structures comprising from claiming columns What's more rows. In the social model, know
information must a chance to be put away done relations and every connection comprises about rows. The principle trademark of the social
model will be the utilization about keys. These are uncommonly designated columns inside a connection used to request information alternately
relate information to different relations. A standout amongst those the majority essential fact that those elementary key, which is used to
particularly distinguish each column for information. Source (Techopedia, 2019)
1.1.2 Justification of the suitable data model

For this sure case study, I have chosen the relational model due to the fact this model is the conceptual basis of relational databases the place it is
been used as a approach of structuring data the usage of family members which are grid like mathematical constructions that consists of columns
and rows. Thus this mannequin borrows heavily from arithmetic and makes use of mathematical phrases such as domains, unions and ranges, for
this reason the features and conditions that it describes are convenient to outline the usage of easy English. In the relational model, all statistic
sought to be saved in members of the family such as tables the place each relation consists of rows and columns. Each relation need to have a
header and body. The header is definitely the listing of columns in the relation. The body is the set of statistics that simply populates the
relation, accordingly organizing them into rows. You can extrapolate that the junction of one column and one row will result in a unique value,
this fee is called a tuple. The second fundamental characteristic of the relational model is the utilization of keys. These

are specifically exact columns within a relation, used to order records or relate facts to different relations. One of the most vital key is the most
important key, which is used to uniquely discover every row of data.
The hierarchical mannequin is not like the different two models. In the hierarchical model, facts is stored in a described hierarchy. For instance,
an organization is made of departments and every department has employees. So a tree structure is created with the employer at the root of the
tree. To get to all employees in the company, one would have to traverse the whole tree. The hierarchical mannequin does exist
today, however commonly in legacy applications. The community model was once the first strive to tackle the inefficiencies of the hierarchical
model. In the community model, you ought to create a network displaying how facts associated to each other. The network mannequin in no
way caught on, and was once sooner or later changed by way of the relational model. The relational mannequin has confirmed to be the
most efficient and most bendy database model in use today. There are many advantages of the relational model over the different models, which
is why the most famous databases in use nowadays hire this methodology. The hierarchical model surely only implements 1:1 relations,
as defined in the strict hierarchy. The different two models do put in force 1: m relationships.
Source (Tech target, 2019)
1.2 Prepare a power point presentation to critically discuss the benefits and limitations of different database technologies such as
distributed database, data warehouse, Data mining, Multimedia database

Figure 1 database technologies

Figure 2 advantages of distributed database

Figure 3 Disadvantages of Distributed database
1.2.1Advantages and Disadvantages of Data Warehouse

Figure 4 Advantages of Data Warehouse

Figure 5 Disadvantages of Data warehouse
1.2.2 Advantages and Disadvantages of Data Mining

Figure 6 Advantages of Data Mining

Figure 7 Disadvantages of Data Mining

1.3 Analyze different approaches such as Top down and Bottom up which can be used to design a database and justify how these
approaches relate to database design
Database Design
Database format is the process of producing a detailed statistics mannequin of a database. This information mannequin carries all

the wished logical and physical layout alternatives and bodily storage parameters that are wished to generate a plan in an information definition
language, which can be used to create a database. Source (www.tutorialpoint.com)

1.3.1 Different Approaches
The database is known as a fundamental component of the information system that is used mainly in business oriented systems. It is considered
as a part of system development
Database design
Figure 8 Database design

1.3.2 Approaches to Database Design
Top down approach
The top-down approach, also recognized as step-wise design, truly breaks a machine or model down into factor sub-systems, each of

which might also in addition damaged down further. However, no first-level gadget are described – you won’t describe a for loop, or define the
attributes of an object in the top-down approach. Systems stop up as a series of ‘black boxes’; factors that have particular inputs and
outputs, however no precise internal structure.
Bottom up
Bottom-up layout consists of defining and coding the very basic, definite parts of the machine to be designed, then linking these components
collectively to form the whole. It additionally identifies the statistics factors (items) and then groups them collectively in facts sets. In different
words, it first defines attributes, and then companies tend to form them. Bottom up starts off evolved with the specific important
points and moves up to the general.
1.3.2.1 Differences between Top down and Bottom Up
Top Down
 It starts from abstract to in the end reaching a strong design

 This strategy is most in many instances employed
 It begins with the top stage module and progresses downward to the lowest level module
 It is convenient to visualize and offers feel of completeness

Bottom Up
 It’s just the reverse as it begins with the concrete sketch to get the abstract entity

 It proceeds with the sketch of lowest level module
 It has the blessings of strong commercial enterprise common sense and the capability to write suitable unit take a look at and to ease
which modifications can be managed and modified
1.3.3 Database Planning
The database planning consists of the activities that permit the tiers of the database system development lifecycle to be realized
as correctly and correctly as possible. This section must be built-in with the normal Information System strategy of the organization. The very
first step in database planning is to outline the mission assertion and objectives for the database system. It is the major aim, the purpose and the
supported tasks of the database device which are known as the resources of the database system
1.3.4 Systems Definition
In the systems definition phase, the scope and boundaries of the database software are described. This description includes
 Links with the other records systems of the organization
 What the deliberate gadget is going to do now and in the future
 Who the users are now and in the future
 The most important user views are additionally described. I.e. what is required of a database machine from

the perspectives of particular job roles or employer utility areas?

1.3.5 Requirements Collection and Analysis
During the requirements series and analysis phase, the series and analysis of the facts about the part of the enterprise to be served by the database

are completed. The effects can also consist of e.g.:
 The description of the statistics that is used or generated
 The important points how the data is to be used or generated
 Any extra requirements wished for the new database. Source (www.amk.fi)
1.3.6 Database Design
The database layout section is divided into three steps:
 Conceptual database design

 Logical database design
 Physical database design

In the conceptual plan phase, the model of the statistics to be used is to be impartial of all bodily considerations which are to be constructed.
The mannequin is primarily based on the requirements specification. In the bodily database format phase, the description of the implementation
of the database on secondary storage is created. The base relations, indexes, integrity constraints, security, etc. are defined the use of the SQL
language.
1.3.7 Database Management System Selection
This is a non-compulsory phase. When there is a need for a new database administration machine (DBMS), this segment is done.

DBMS potential a database device like Access, SQL Server, MYSQL, and Oracle. In this segment the criteria for the new DBMS are defined.
Then a number of merchandise are evaluated according to the criteria. Finally the recommendation for the decision is decided.
1.3.8 Application Design
In the application graph phase, the format of the consumer interface and the utility programs that use and manner the database are described and

designed. It consists of database issues such as interfacing SQL with typical programming languages. Source (www.amk.fi)
1.3.9 Prototyping
The motive of a prototype is to allow the users to use the prototype to pick out the points of the machine using a computer. There are horizontal
and vertical prototypes. A horizontal prototype has many facets (e.g. user interfaces) however they are not in a working condition. A vertical
prototype has very few points however they are in best working condition.

1.3.10 Implementation
During the implementation phase, the physical realizations of the database and application designs are to be done. This is the
programming phase of the structures development.
1.3.11 Data Conversion and Loading
This segment is wished when a new database is replacing an old system. During this segment the present statistics will be transferred into the

new database.
1.3.12 Testing
Before the new gadget is going to live, it be fully tested. The aim of checking out is to discover errors. The purpose is not to prove the software
program is working well.
1.3.13 Operational Maintenance
The operational renovation is the technique of monitoring and maintaining the database system. Monitoring ability that the performance of
the gadget is observed. If the overall performance of the machine falls beneath a perfect level, tuning or reorganization of the database may be
required. Source (www.amk.fi)

Task 2: Be able to design and implement relational database systems
2.1 Identify entities and attributes of the given scenario and draw an ER diagram by showing different attribute types and cardinality
constraints

Figure 9 ERD Diagram
2.2 Translate the ER diagram into relational schemas by showing the relation with each through primary keys and foreign keys

Figure 10 Relational diagram

2.3 Design a relational database system according to the ER diagram you have created (Use SQL DDL statements)
Create table Login (
Username Varchar (50),
Password Varchar (50),
Primary key (Username)
);
Create table Student (
Student ID Int,
Coursed Int,
Lecturer_ID Int,
Student name Varchar (50),

Birthday date,
Enroll_year int,
primary key (Student_ID),
foreign key (Lecturer_ID) references Lecturer (Lecturer_ID),
);
create table Lecturer(
Lecturer_ID int,
Student_ID int,
Lecturer_Name varchar (50),
Lecturer_Title varchar (50),
Committee varchar (50),
primary key (Lecturer_ID),
);
create table Faculty(
Lecturer_ID int,
Student_ID int,
Faculty_Name varchar (50),

Faculty_dean varchar (50),
Faculty_building varchar (50),
primary key (Lecturer_ID),
);
Create table Course(
Course_ID int,
Student_ID int,
Lecturer_ID int,
Course_title varchar (50),
Course_level varchar (50),
Course_duration varchar (50),
Start_year int,
Semester varchar (50),
Grade varchar (50),
primary key (Student_ID),
foreign key (Lecturer_ID) references Lecturer (Lecturer_ID),
);


2.4 Provide evidence of the use of a suitable IDE to create a simple interface to insert, update and delete data in the database
Figure 11 Login form

Figure 12 Main form

Figure 13 Course form
Insert code
private void btnInsert_Click(object sender, EventArgs e)

{
SqlConnection con = new SqlConnection(cs);
string sql = "Insert into Course (Course_ID,Student_ID,
Lecturer_ID,Course_title,Course_level,Course_duration,Start_year,Semester,Grade) values ('" + cmbCourse_ID.Text + "','" +

cmbStudent_ID.Text + "','"+cmbLecturer_ID.Text+"'," + cmbCourse_title.Text + "','" + cmbCourse_level.Text + "','" +
cmbCourse_duration.Text + "','" + cmbStart_year.Text + "','" + cmbSemester.Text + "','" + cmbGrade.Text + "')";
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.ExecuteNonQuery();
MessageBox.Show("Data is inserted successfully !!!");
con.Close();
}
Update code
private void btnUpdate_Click(object sender, EventArgs e)

{
string sql = "Update Course set Course_ID = '" + cmbCourse_ID.Text + "', Student_ID = '" + cmbStudent_ID.Text + "', Lecturer_ID =
'"+cmbLecturer_ID.Text+"', Course_title = '" + cmbCourse_title.Text + "', Course_level = '" + cmbCourse_level.Text + "', Course_duration = '"
+ cmbCourse_duration.Text + "', Start_year = '" + cmbStart_year.Text + "', Semester = '" + cmbSemester.Text + "', Grade = '" + cmbGrade.Text
+ "'";
con.Open();
MessageBox.Show("Data is updated successfully !!!");
con.Close();
}
}
}
Delete code
private void btnDelete_Click(object sender, EventArgs e)

{

string sql = "Delete from Course where Course_ID = '" + cmbCourse_ID.Text + "'";
con.Open();
MessageBox.Show("Data is deleted successfully !!!");
con.Close();
}

Figure 14 Faculty form
Insert code

{
string sql = "Insert into Faculty (Lecturer_ID,Student_ID,Faculty_Name,Faculty_dean,Faculty_building) values ('" +
cmbLecturer_ID.Text + "','" + cmbStudent_ID.Text + "','" + cmbFaculty_Name.Text + "','" + cmbFaculty_dean.Text + "','" +
cmbFaculty_building.Text + "')";

con.Open();
con.Close();
}
Update code

{
string sql = "Update Faculty set Lecturer_ID = '" + cmbLecturer_ID.Text + "', Student_ID = '" + cmbStudent_ID.Text + "',
Faculty_Name = '" + cmbFaculty_Name.Text + "', Faculty_dean = '" + cmbFaculty_dean.Text + "', Faculty_building = '" +
cmbFaculty_building.Text + "'";
con.Open();
con.Close();
}
Delete code

{
string sql = "Delete from Faculty where Lecturer_ID = '" + cmbLecturer_ID.Text + "'";
con.Open();

con.Close();
}
Figure 15 Lecturer form

Insert code

{
string sql = "Insert into Lecturer (Lecturer_ID,Student_ID,Lecturer_Name,Lecturer_Title,Committee) values ('" + cmbLecturer_ID.Text
+ "','" + cmbStudent_ID.Text + "','" + cmbLecturer_Name.Text + "','" + cmbLecturer_Title.Text + "','" + cmbCommittee.Text + "')";
con.Open();
con.Close();
}
Update code

{
string sql = "Update Lecturer set Lecturer_ID = '" + cmbLecturer_ID.Text + "', Student_ID = '" + cmbStudent_ID.Text + "',
Lecturer_Name = '" + cmbLecturer_Name.Text + "', Lecturer_Title = '" + cmbLecturer_Title.Text + "', Committee = '" + cmbCommittee.Text +
"'";
con.Open();
con.Close();
}
Delete code

{

string sql = "Delete from Lecturer where Lecturer_ID = '" + cmbLecturer_ID.Text + "'";
con.Open();
con.Close();
}
Figure 16 Student form
Insert code

{

string sql = "Insert into Student (Student_ID,Course_ID,Lecturer_ID,Student_name,Birthday,Enroll_year) values ('" +
cmbStudent_ID.Text + "','"+cmbCourse_ID.Text+"','"+cmbLecturer_ID.Text +"','" + cmbStudent_name.Text + "','" + dtpBirthday.Text + "','" +
cmbEnroll_year.Text + "')";
con.Open();
con.Close();
}
Update code

{
string sql = "Update Student set Student_ID = '" + cmbStudent_ID.Text + "', Course_ID = '"+cmbCourse_ID.Text +"', Lecturer_ID =
'"+cmbLecturer_ID.Text +"', Student_name= '" + cmbStudent_name.Text + "', Birthday = '" + dtpBirthday.Text + "', Enroll_year = '" +
cmbEnroll_year.Text + "'";
con.Open();
con.Close();
}
Delete code

{
string sql = "Delete from Student where Student_ID = '" + cmbStudent_ID.Text + "'";

con.Open();
con.Close();
}
[Author has kept the page blank on purpose]
Task 3: Be able to use manipulation and querying tools
3.1 Explain the benefits of different manipulation and query tools which can be used to design the relational database system
The benefits of query tool is that there are various types of tools that are been used nowadays. But from this we realize that it helps to extract
relevant information from a database, thus the use of a query helps to define the categories of information that will be sought
Queries are the primary mechanism for retrieving from a database and they consist of questions that are presented to the database in a specific
format. These database management systems use the Structured Query Language. It helps to retrieve data from the database in a readable format
that is used per the user’s requirement. There are some few advantages it is shown below

 Storage space
While you are creating a database that is completely based totally on information specifications. There are no pointless bytes or characters saved
in the SQL database. This saves storage space
 Efficient statistics retrieval
Queries can be used to retrieve giant amounts of data from a database efficiently and speedily.
 Database safety
Security receives higher because you can enable particular data to be stored in the SQL database.
 Speed
The database is environment friendly and makes use of the right personalize and record types, query speed will improve

3.2 Perform following SQL queries using the tables you have created and provide the output gained
Figure 17 Details of Students using join query
Figure 18 Delete query where grade is Fail

Figure 19 Update using Join query
Figure 20 Update in Course

Figure 21 Update in Lecturer

3.3 Critically evaluate how different query tools can be used to obtain meaningful data with its unique functionalities
3.3.1 Explanation of query tools
Although query tools operate via extracting records from a traditional database engine such as SQL server. It refers to the truth that
the data is stored and referenced `according to numerous dimensions. They have changed, with the aid of making storage, retrieval and accuracy
of records that is stored greater effectively positive and reliable. It permits us to execute arbitrary SQL commands. The approach would be
to pick out aspects that meet some criteria or that are placed in a particular place.
We can build SQL queries to choose particular features or rows from the source data. For example, the ‘Select’ tool permits us to use a
SQL query to make a new feature class of facets that are chosen from a present class. As we specify about question tools, they help analyze
the records in a database in which they furnish question building enhancing and summarizes functionalities.
Examples
The most fundamental SELECT announcement has two parts. They are what columns you prefer to return and what table these columns come
from.
To retrieve all of the facts about the whole Student in the Add_New_Student table, we can use the asterisk (*) mark as a shortcut for all of the
columns.
SELECT * FROM Student

Task 4: Be able to test and document relational database systems

Design By : Mohammed Nazurdeen Design Date : 2019/03/19
Description: Login Details form – Please insert Username and Password, Successful
and Unsuccessful Login
Test Action User System Response Pass/
Case Expected Fail
No Result
Please insert Pass

Username Display
1.1
and Message
Password

Successful Display Pass
2.1
Login Message
Unsuccessful Display Pass

3.1
Login Message
4.1 Provide a suitable test plan to support the documentation of the testing process
Table 1 Login form results

Description: Student Details form – Insert, Update and Delete
Case Expected Fail
No Result
Pass
Inserting
Display
1.1 Student
Message
details
Pass
Updating Display
2.1
Student Message
details
Deleting Pass
Display
3.1 Student
Message
details

Table 2 Students table result

Description: Course Details form – Insert, Update and Delete

Case Expected Fail
No Result
Pass
Inserting
Display
1.1 Course
Message
details
Pass
Updating Display
2.1
Course Message
details
3.1 Deleting Display

Course Message
details
Pass

Description: Lecturer Details form – Insert, Update and Delete

Case Expected Fail
No Result

Pass
Inserting
Display
1.1 Lecturer
Message
details
Pass
Updating Display
2.1
Lecturer Message
details
Deleting Pass
Display
3.1 Lecturer
Message
details

Description: Faculty Details form – Insert, Update and Delete

Case Expected Fail
No Result
Pass
Inserting
Display
1.1 Faculty
Message
details
Pass
Updating Display
2.1
Faculty Message
details
3.1 Deleting Display

Faculty Message
details
Pass


4.2 Provide relevant test cases for the database you have implemented
Test Case: 1 Test Case Name : DDL Statements
Description: Execute database tables and get following results
Pass
Record Display
2.1
Successfully Message
Pass
Syntax Display
2.2
Errors Message
Display
Object Pass
Error
2.3 Errors
Message

Display
Data Type
2.4 Error Pass
Errors
Message
Table 5 Test case- query
Test Case: 1 Test Case Name : DDL Statements

Description: Execute database tables and get following results
Test Action User System Response Pass
case Expecte /
No d Result Fail

Pass
Insert Record Display
3.1
Successfully Message
Pass
Display
Display
3.2 Record
Data
Successfully
Pass
Syntax Display
3.3
Errors Message
Pass
Display
3.4 Object Errors Error
Message
Pass
Display
Column
3.5 Error
Errors
Message
Pass
Table Display
3.6 Definition Error
don’t Match Message

4.3 Create user documentation for a developed relational database system with the help of help menus, pop ups
4.3.1 Steps to create a database
Stafford
University
Management
System
Figure 22 Connecting using the server name
Then in Object Explorer, there is databases, right click and create new database

Figure 23 steps to creating new database
As database is created, press New Query and insert your Query

Figure 24 Steps for creating new query
Once the new query is created the user can create tables in the following query

Figure 25 List of tables in query

In order to get the result the user has to execute the tables in the query and insert data in order to get the result
Figure 26 Executing table in query
Inserting data to table

Figure 27 Data inserted
Viewing the executed result
Figure 28 result
4.4 Provide evidences on how to address verification and validation
The V-Model in software development, also known as verification and validation model. The execution process takes place in a sequential
manner and each phase must be completed before the next phase begins.

Verification Validation
 This process in software development life
 This process in software development life
cycle checks whether or not we are in the
cycle determines whether the final product
right track of developing the product or
is capable of meeting business needs.
software.
 It involves checking that all documents and

requirement specification that are per the
 It involves checking the important document
requirement and are fit to be used. In an
like ER Diagrams, database design, and so
intended environment, it is to be checked
on
in order the product is been fulfilled as per
its requirement
This process is done before software  This involves the execution of software
execution
 all techniques of static testing are involved
 whereas this process includes all ways of
in this process dynamic testing
 Examples includes various types of testing

 Examples include : Reviews, inspection,
such as Regression, System, User
walkthrough
Acceptance , and smoke testing
 All necessary documents have been verified

 After the completion of the system, it has the
and based on requirements they have been possibility of undergoing various types of

properly addressed testing that is most suitable
Table 7 Description of validation and verification
Source (tutorial point, 2018)
4.4.1 Examples for verification
Figure 29 Create procedure

4.4.2 Example for Validation
Figure 30 after insert trigger

4.5 explain how control mechanisms have been used and the importance of these mechanisms for the security of the database
 Management mechanisms are strategies or method to outline and manage variables in a very fascinating method. For

instance a check manager at readying web site would possibly install a range of management mechanisms to assist them
monitor numerous testing activities in code testing life cycle.
 We’d like to use management mechanisms over our system to make sure that a high commonplace of quality is met. To stay the
complete method heading in the right direction, it's vital to possess internal control. It helps us to work out the matter, fix it
and conjointly helps us in judging the effectiveness of enforced resolution
 The complete method of internal control should last swimmingly. An effect feature should be devised, to manage major portion of
labor in standardized manner.
 If within the entire method of dominant the system work flow, we have a tendency to aren't ready to accomplish any task by straight
technique we’d like to plan some different management, that adjusts to the standardized strategies4
 Within the method of dominant, one should conjointly understand how to react just in case of defects. The areas of
system wherever defects are presumably to occur should be monitored fastidiously, in order that the defects is caught and
glued there then, instead of continued the work flow more. Defects should be caught and treated in their emerging stage or must be
reduced to close zero, in order that we are able to say that six sigma is earned. Source (Techopedia, 2018)

Gantt chart

Figure 31 Gantt chart

Summary
This assignment known as DAD tells us about new things that we have never heard before, we are pretty confused when everyone mentions
about it. But what is a database. Its precisely an organized collection of data where it contains collection of schemas, tables and so on. But
besides all this it is barely a design known as Database design
This certain design includes the introduction of a database data model. Where it contains all logical and physical design choices and physical
storage parameters needed to generate a design in a data definition language which can be used to create a database. A fully attributed data
model contains detailed attributes for each entity
It can be used to describe many different parts of the design of a database system. Basically, and most correctly, it can be thought as the logical
design of the database and the data structures that are used to store the data. However, the term database design could be used to apply to the

overall process of designing, not just the data structures but also the forms and queries that are used as part of the overall database application
within the database management system
Self-Criticism

This assignment was certainly of great understanding to me. Through this I came to know about various types of data models, and other database
technologies. But this was mostly concerned about the database.
Realizing what a database is means so many things. I basically achieved many objectives that are concerned on data insertions, updating and
deleting all details and most importantly on how to secure a database with stored procedures and triggers
The following system that was given to us was S. It gave me a wide knowledge on relation design and various attributes and entities and ways to
create a successful relation between each entity by giving a valid primary key, plus to make a difference between entity and attributes. Im very
satisfied with this project that I have achieved and giving my utmost effort in completing this following assignment

References
Techopedia - Where Information Technology and Business Meet. 2018. Techopedia - Where Information Technology and Business Meet.
[ONLINE] Available at: https://www.techopedia.com/. [Accessed 17 May 2019].
Test bytes. 2018. Test bytes: Software Testing and QA Consulting Company. [ONLINE] Available at: http://www.testbytes.net/. [Accessed 17
May 2019].
DIGMA-oppimisympäristö. 2018. DIGMA-oppimisympäristö. [ONLINE] Available at: https://moodle.amk.fi/. [Accessed 17 May 2019].
tutorialspoint.com. 2018. Ansible, LOLCODE, Current Affairs 2018, Apache Commons Collections, Intellij Idea, Java 9, GSON, Test Link,
Inter Process Communication (IPC), Logo, PySpark, Google Tag Manager, Adobe InDesign, IFSC Code, SAP Workflow, Scipy, Angular 4,
D3js, dcjs, Rich Faces, Spring Boot, SAP Hybris, Flexbox, Azure RP, Open Shift, Apache Bench, qTest, Test Lodge, Power BI, Python,
Financial Accounting, Text and Video Tutorials for UPSC, IAS, PCS, Civil Services, Banking, Aptitude, Questions, Answers, Explanation,
Interview, Entrance, Exams, Solutions, Examples, Online, Quiz, Current Affairs 2017, Aptitude Test, Verbal Ability, General Knowledge,
Reasoning, Mock test, Kubernetes, Spring JDBC, Java Concurrency, Spring AOP, Gerrit, Spring MVC, Indian Polity, History, Geography,
Biology, Physics, Chemistry and Economy. [ONLINE] Available at: https://www.tutorialspoint.com/. [Accessed 17 May 2019].

More Process. 2018. More Process - Relevant Technology Articles and Tutorials for Everyone. [ONLINE] Available
at: http://www.moreprocess.com/. [Accessed 17 May 2019].
Entrepreneur. 2018. Entrepreneur - Start, run and grow your business. [ONLINE] Available at: https://www.entrepreneur.com/. [Accessed 17
May 2019].
TechTarget. 2018. Fuel Your Pipeline Faster - TechTarget. [ONLINE] Available at: http://www.techtarget.com/. [Accessed 17 May 2019].
Indexes
D M
DBMS, 15, 24, 60 MYSQL, 24

DDL, 5, 28, 46, 47, 60
S
E SQL, 5, 24, 28, 37, 38, 40, 60
ER, 26, 27, 28, 52
V
I V-Model, 52
ID, 28, 29, 31, 32, 33, 34, 35, 36

Glossary
ID – Identity
DAD – Database Analysis and Design
DBMS – Database Management System
DDL – Data Definition Language
ER – Entity Relationship
IDE – Integrated Development Environment
SQL – Structured Query Language


Dad - 16 - 001

Uploaded by

Copyright:

Available Formats

Dad - 16 - 001

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Dad - 16 - 001

Uploaded by

Copyright:

Available Formats

Assignment Brief

BTEC Level 4-5 HNC/HND Diploma (QCF)

To be filled by the Student

Edexcel No : Registration No: Batch No:

Unit Assessment Information

Qualification : Higher National Diploma in Computing and Systems Development

Learning outcomes and grading opportunities:

Merit and Distinction Descriptor

Date Issued : Date Due :

Assessor : Date Assessed:

Internal Verifier (IV): Date of IV:

Name Reg No: DAD Page 1 of 95

Word Processing Rules

Name Reg No: DAD Page 2 of 95

Statement of Originality and Student Declaration

1. I know that plagiarism is a punishable offence because it constitutes theft.

Student’s Signature: …………………………… Date:.

Name Reg No: DAD Page 3 of 95

Name Reg No: DAD Page 4 of 95

Task 2: LO 2: Be able to design and implement relational database systems

Task 3: LO 3: Be able to use manipulation and querying tools

Name Reg No: DAD Page 5 of 95

Name Reg No: DAD Page 6 of 95

Activit Activity Learning Date Feedback

1 Create tables with Entity integrity and LO2.2

3 Perform join queries M 1.2

4 Drop Foreign key of a table (ON LO4.1

Assessor Name :…………………………………………….

Assessor Signature :…………………………………………….

Name Reg No: DAD Page 7 of 95

Subject- Student/s, Name/s-

Excellent Very Good Meets Below

Name Reg No: DAD Page 8 of 95

Name Reg No: DAD Page 9 of 95

LO2 - Be able to design and implement relational database systems

Name Reg No: DAD Page 10 of 95

Grade Descriptor for MERIT Possible evidence Feedback

Name Reg No: DAD Page 11 of 95

Name Reg No: DAD Page 12 of 95

Future Improvements & Assessor Comment:

Assessor: Signature: Date: ____/____/______

Internal Verifier’s Comments:

Internal Verifier: Signature: Date: ____/____/______

Name Reg No: DAD Page 13 of 95

Name Reg No: DAD Page 14 of 95

Name Reg No: DAD Page 15 of 95

Figure 1 database technologies---------------------------------------------------------------------20

Name Reg No: DAD Page 16 of 95

Name Reg No: DAD Page 17 of 95

Table 1 Login form results...................................................................................................45

Name Reg No: DAD Page 18 of 95

[Author has kept this space blank on purpose]

Name Reg No: DAD Page 19 of 95

1.1.1 Different data model types

Name Reg No: DAD Page 20 of 95

1.1.2 Justification of the suitable data model

Name Reg No: DAD Page 21 of 95

Name Reg No: DAD Page 22 of 95

Assessor: Signature: Date: //__

Internal Verifier: Signature: Date: //__