Data Analysis and Design: Implementation of Relational Databases
VerifiedAdded on 2024/06/13
|60
|6440
|175
Report
AI Summary
This report provides a comprehensive analysis and design of a relational database system, addressing key aspects such as data models, database technologies, and database design approaches. It critically compares different data models, discusses the benefits and limitations of various database technologies, and analyzes different approaches to database design. The report details the design and implementation of a relational database system to meet specific requirements outlined in a case study, including the use of appropriate database software and techniques to enhance the user interface. It explains the benefits of using manipulation and query tools, implements a query language, and evaluates the extraction of meaningful data. Furthermore, the report critically reviews and tests the implemented system, creates supporting documentation, develops user documentation, and explains how verification, validation, and control mechanisms have been addressed within the developed relational database system. Several figures and tables are included to illustrate the database structure, queries, and design elements.
DATA ANALYSIS AND DESIGN
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Contents
Introduction..............................................................................................................................................................4
LO1 –Task 1 Understand data models and database technologies.........................................................................5
A.C.1.1 Critically compare different data models................................................................................................5
A.C.1.2 Critically discuss the benefits and limitations of different database technologies...............................12
A.C. 1.3 Analyse different approaches to database design................................................................................17
LO2 Task 2. Be able to design and implement relational database systems.........................................................21
A.C.2.1 Design a relational database system to meet given requirements provided in the given case study..21
A.C.2.2- Build a relational database system for the database design in A.C 2.1 using an appropriate database
software application...........................................................................................................................................24
A.C.2.3 Apply a range of database tools and techniques to enhance the user interface to meet the given case
study requirements............................................................................................................................................28
LO3 - Task 3. Be able to use manipulation and querying tools..............................................................................31
A.C. 3.1 Explain the benefits of using manipulation and query tools in a relational database system as per the
requirements gave in the case study..................................................................................................................31
A.C. 3.2 Implement a query language into the relational database system that has been implemented in task
2.2.......................................................................................................................................................................34
A.C. 3.3 Critically evaluate how meaningful data has been extracted through the use of query tools for the
given case study.................................................................................................................................................39
LO4 -Task 4. Be able to test and document relational database systems.............................................................42
A.C. 4.1 Critically review and test the implemented relational database system.............................................42
A.C. 4.2 Create documentation to support the implementation and testing of the relational database system
that has been implemented in Task 2.2.............................................................................................................43
A.C. 4.3 Create user documentation for the developed relational database system in task 2.2......................47
A.C. 4.4 Explain how verification and validation have been addressed in the developed relational database
system................................................................................................................................................................50
A.C. 4.5 Explain how control mechanisms have been used in the developed relational database system......53
Conclusion-.............................................................................................................................................................57
References-............................................................................................................................................................58
Appendix................................................................................................................................................................59
1
Introduction..............................................................................................................................................................4
LO1 –Task 1 Understand data models and database technologies.........................................................................5
A.C.1.1 Critically compare different data models................................................................................................5
A.C.1.2 Critically discuss the benefits and limitations of different database technologies...............................12
A.C. 1.3 Analyse different approaches to database design................................................................................17
LO2 Task 2. Be able to design and implement relational database systems.........................................................21
A.C.2.1 Design a relational database system to meet given requirements provided in the given case study..21
A.C.2.2- Build a relational database system for the database design in A.C 2.1 using an appropriate database
software application...........................................................................................................................................24
A.C.2.3 Apply a range of database tools and techniques to enhance the user interface to meet the given case
study requirements............................................................................................................................................28
LO3 - Task 3. Be able to use manipulation and querying tools..............................................................................31
A.C. 3.1 Explain the benefits of using manipulation and query tools in a relational database system as per the
requirements gave in the case study..................................................................................................................31
A.C. 3.2 Implement a query language into the relational database system that has been implemented in task
2.2.......................................................................................................................................................................34
A.C. 3.3 Critically evaluate how meaningful data has been extracted through the use of query tools for the
given case study.................................................................................................................................................39
LO4 -Task 4. Be able to test and document relational database systems.............................................................42
A.C. 4.1 Critically review and test the implemented relational database system.............................................42
A.C. 4.2 Create documentation to support the implementation and testing of the relational database system
that has been implemented in Task 2.2.............................................................................................................43
A.C. 4.3 Create user documentation for the developed relational database system in task 2.2......................47
A.C. 4.4 Explain how verification and validation have been addressed in the developed relational database
system................................................................................................................................................................50
A.C. 4.5 Explain how control mechanisms have been used in the developed relational database system......53
Conclusion-.............................................................................................................................................................57
References-............................................................................................................................................................58
Appendix................................................................................................................................................................59
1
List of figures:
Figure 1: OOBM.......................................................................................................................................................6
Figure 2 Relational model.......................................................................................................................................7
Figure 3:-Top-Down design...................................................................................................................................19
Figure 4: Bottom-up design...................................................................................................................................20
Figure 5: Academic manager table.......................................................................................................................24
Figure 6: Admin table............................................................................................................................................25
Figure 7: College table...........................................................................................................................................25
Figure 8: Course table............................................................................................................................................25
Figure 9: Lecturer table.........................................................................................................................................26
Figure 10: Student table........................................................................................................................................26
Figure 11: Unit table..............................................................................................................................................26
Figure 12: Academic manager tables of Form......................................................................................................28
Figure 13: Admin tables of Form...........................................................................................................................28
Figure 14: College tables of Form.........................................................................................................................28
Figure 15: Course tables of Form..........................................................................................................................29
Figure 16: Lecturer tables of Form........................................................................................................................29
Figure 17: Students tables of Form.......................................................................................................................30
Figure 18: Units tables of Form.............................................................................................................................30
Figure 19:Acedemic_manager query....................................................................................................................32
Figure 20:Students query......................................................................................................................................32
Figure 21:Acedemic_manager SQL query.............................................................................................................33
Figure 22: Students SQL query..............................................................................................................................33
Figure 23: Query 1.................................................................................................................................................33
Figure 24Query 2...................................................................................................................................................33
Figure 25: Query 3.................................................................................................................................................34
Figure 26Query 4...................................................................................................................................................34
Figure 27: Query 5.................................................................................................................................................34
Figure 28: Query 6.................................................................................................................................................35
Figure 29: Query 7.................................................................................................................................................35
Figure 30: Query 8.................................................................................................................................................35
Figure 31: Design View of Query1.........................................................................................................................35
Figure 32: Design View of Query2.........................................................................................................................36
Figure 33: Design View of Query3.........................................................................................................................36
Figure 34: Design View of Query4.........................................................................................................................36
Figure 35: Design View of Query5.........................................................................................................................36
Figure 36: Design View of Query6.........................................................................................................................37
Figure 37: Design View of Query 7........................................................................................................................37
Figure 38: Query 8 Design View............................................................................................................................37
Figure 39: Query1..................................................................................................................................................38
Figure 40: Query2..................................................................................................................................................38
Figure 41: Query3..................................................................................................................................................38
Figure 42: Query4..................................................................................................................................................39
Figure 43: Query5..................................................................................................................................................39
Figure 44: Query6..................................................................................................................................................39
Figure 45: Query7..................................................................................................................................................40
Figure 46: Query8..................................................................................................................................................40
Figure 47: Objective Plan 1....................................................................................................................................44
Figure 48: Objective Plan 2....................................................................................................................................44
2
Figure 1: OOBM.......................................................................................................................................................6
Figure 2 Relational model.......................................................................................................................................7
Figure 3:-Top-Down design...................................................................................................................................19
Figure 4: Bottom-up design...................................................................................................................................20
Figure 5: Academic manager table.......................................................................................................................24
Figure 6: Admin table............................................................................................................................................25
Figure 7: College table...........................................................................................................................................25
Figure 8: Course table............................................................................................................................................25
Figure 9: Lecturer table.........................................................................................................................................26
Figure 10: Student table........................................................................................................................................26
Figure 11: Unit table..............................................................................................................................................26
Figure 12: Academic manager tables of Form......................................................................................................28
Figure 13: Admin tables of Form...........................................................................................................................28
Figure 14: College tables of Form.........................................................................................................................28
Figure 15: Course tables of Form..........................................................................................................................29
Figure 16: Lecturer tables of Form........................................................................................................................29
Figure 17: Students tables of Form.......................................................................................................................30
Figure 18: Units tables of Form.............................................................................................................................30
Figure 19:Acedemic_manager query....................................................................................................................32
Figure 20:Students query......................................................................................................................................32
Figure 21:Acedemic_manager SQL query.............................................................................................................33
Figure 22: Students SQL query..............................................................................................................................33
Figure 23: Query 1.................................................................................................................................................33
Figure 24Query 2...................................................................................................................................................33
Figure 25: Query 3.................................................................................................................................................34
Figure 26Query 4...................................................................................................................................................34
Figure 27: Query 5.................................................................................................................................................34
Figure 28: Query 6.................................................................................................................................................35
Figure 29: Query 7.................................................................................................................................................35
Figure 30: Query 8.................................................................................................................................................35
Figure 31: Design View of Query1.........................................................................................................................35
Figure 32: Design View of Query2.........................................................................................................................36
Figure 33: Design View of Query3.........................................................................................................................36
Figure 34: Design View of Query4.........................................................................................................................36
Figure 35: Design View of Query5.........................................................................................................................36
Figure 36: Design View of Query6.........................................................................................................................37
Figure 37: Design View of Query 7........................................................................................................................37
Figure 38: Query 8 Design View............................................................................................................................37
Figure 39: Query1..................................................................................................................................................38
Figure 40: Query2..................................................................................................................................................38
Figure 41: Query3..................................................................................................................................................38
Figure 42: Query4..................................................................................................................................................39
Figure 43: Query5..................................................................................................................................................39
Figure 44: Query6..................................................................................................................................................39
Figure 45: Query7..................................................................................................................................................40
Figure 46: Query8..................................................................................................................................................40
Figure 47: Objective Plan 1....................................................................................................................................44
Figure 48: Objective Plan 2....................................................................................................................................44
2
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.
Trusted by 1+ million students worldwide
Figure 49: Objective Plan 3....................................................................................................................................45
Figure 50: Objective plan 4....................................................................................................................................45
Figure 51: Objective Plan 5....................................................................................................................................45
Figure 52: Objective Plan 6....................................................................................................................................46
Figure 53: form of Accedemic_Manager..............................................................................................................48
Figure 54: Form of Admin......................................................................................................................................48
Figure 55: Form of College....................................................................................................................................48
Figure 56: Form of Course.....................................................................................................................................48
Figure 57: Form of Lecturer...................................................................................................................................49
Figure 58: Form of Students..................................................................................................................................49
Figure 59: Form of Units........................................................................................................................................49
Figure 60: Design of V-Model................................................................................................................................50
Figure 61:Verification Phases................................................................................................................................51
Figure 62:Validation Phases..................................................................................................................................52
Figure 63: Acedemic_manager for access control mechanisms..........................................................................53
Figure 64: Admin for access control mechanisms................................................................................................54
Figure 65: college for access control mechanisms...............................................................................................54
Figure 66: Course for access control mechanisms................................................................................................55
Figure 67: lecturer for access control mechanisms..............................................................................................55
Figure 68: Students for Access Control Mechanisms...........................................................................................55
Figure 69: Units for access control mechanisms..................................................................................................56
3
Figure 50: Objective plan 4....................................................................................................................................45
Figure 51: Objective Plan 5....................................................................................................................................45
Figure 52: Objective Plan 6....................................................................................................................................46
Figure 53: form of Accedemic_Manager..............................................................................................................48
Figure 54: Form of Admin......................................................................................................................................48
Figure 55: Form of College....................................................................................................................................48
Figure 56: Form of Course.....................................................................................................................................48
Figure 57: Form of Lecturer...................................................................................................................................49
Figure 58: Form of Students..................................................................................................................................49
Figure 59: Form of Units........................................................................................................................................49
Figure 60: Design of V-Model................................................................................................................................50
Figure 61:Verification Phases................................................................................................................................51
Figure 62:Validation Phases..................................................................................................................................52
Figure 63: Acedemic_manager for access control mechanisms..........................................................................53
Figure 64: Admin for access control mechanisms................................................................................................54
Figure 65: college for access control mechanisms...............................................................................................54
Figure 66: Course for access control mechanisms................................................................................................55
Figure 67: lecturer for access control mechanisms..............................................................................................55
Figure 68: Students for Access Control Mechanisms...........................................................................................55
Figure 69: Units for access control mechanisms..................................................................................................56
3
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Introduction
In this Dominican college, there are different types of issues as they are not managing their
database properly so that they have face problems in the database. Here we described all
the tables which they are facing problems, we have made seven different types of tables so
that we can relate them to their queries and solved them queries. Describe E-R Diagrams
and data models and database technologies of implementation, normalization process and
all screen sorts of the table which is used in the structured query language.
4
In this Dominican college, there are different types of issues as they are not managing their
database properly so that they have face problems in the database. Here we described all
the tables which they are facing problems, we have made seven different types of tables so
that we can relate them to their queries and solved them queries. Describe E-R Diagrams
and data models and database technologies of implementation, normalization process and
all screen sorts of the table which is used in the structured query language.
4
LO1 –Task 1 Understand data models and database technologies
A.C.1.1 Critically compare different data models
What is Data model?
In the database model, including their relationships and constraints that decide how data
can keep and retrieve, in this model reveal the logical structure of a database. All the single
database models are a plan to build by their rules and conception. All the data models are
constituted follow by their database diagram.
Types of database models
The various types of data models are-
1. Object-oriented database model
2. Relational of model
3. Entity-Relationship model
4. Networking model
5. Hierarchical database model
1. Object-oriented DBM (Database Model)
An object-oriented database model is a group of reusable, software elements,
objects & property, and techniques. There are many types of object-oriented
databases: -
Hypertext database: -In the hypertext database permit there any objects to
connect to any others object and its use for any corporation to a large amount of
data, but it’s not perfect for the numerical survey.
Multimedia database: -In the multimedia database they could not store any
images so a database is integrated media.
Since it integrated tables, the OODM is famous familiar upright-relational DBM.
These types of OODM are also called as hybrid DBM.
5
A.C.1.1 Critically compare different data models
What is Data model?
In the database model, including their relationships and constraints that decide how data
can keep and retrieve, in this model reveal the logical structure of a database. All the single
database models are a plan to build by their rules and conception. All the data models are
constituted follow by their database diagram.
Types of database models
The various types of data models are-
1. Object-oriented database model
2. Relational of model
3. Entity-Relationship model
4. Networking model
5. Hierarchical database model
1. Object-oriented DBM (Database Model)
An object-oriented database model is a group of reusable, software elements,
objects & property, and techniques. There are many types of object-oriented
databases: -
Hypertext database: -In the hypertext database permit there any objects to
connect to any others object and its use for any corporation to a large amount of
data, but it’s not perfect for the numerical survey.
Multimedia database: -In the multimedia database they could not store any
images so a database is integrated media.
Since it integrated tables, the OODM is famous familiar upright-relational DBM.
These types of OODM are also called as hybrid DBM.
5
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.
Trusted by 1+ million students worldwide
Figure 1: OOBM
2. Relational of model
The popular model, each table are consisting of column and rows, so this model
sorted statistics into tables called relations. Each one column lists have an attribute
of the entity. The domain is called the attribute in a relation. The primary key is
chosen as a specific attribute or combination of attributes which was referred by
another table, then it’s called foreign key.
Each row of a table is a tuple, involves data into some particular examples of the
entity.
There are kinds of connections between the tables, many-to-many, one-to-many,
one-to-one, and connections. Below some examples are given: -
6
2. Relational of model
The popular model, each table are consisting of column and rows, so this model
sorted statistics into tables called relations. Each one column lists have an attribute
of the entity. The domain is called the attribute in a relation. The primary key is
chosen as a specific attribute or combination of attributes which was referred by
another table, then it’s called foreign key.
Each row of a table is a tuple, involves data into some particular examples of the
entity.
There are kinds of connections between the tables, many-to-many, one-to-many,
one-to-one, and connections. Below some examples are given: -
6
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Figure 2 Relational model
3. Entity-Relationship model
This model is similar to the network model, and relationships between real-world
entities, but the physical structure of the database is not directly tied in this model.
Figure 3: Entity-Relationship model
4. Networking Model
7
3. Entity-Relationship model
This model is similar to the network model, and relationships between real-world
entities, but the physical structure of the database is not directly tied in this model.
Figure 3: Entity-Relationship model
4. Networking Model
7
To construct on a hierarchical model to allow many-to-many connections between
associated data. They built with puts of interconnected data found on the
arithmetically set hypothesis.
It is defined the CODASYL after it was most popular in the 70s.
Figure 4: a Network model
5. Hierarchical model
In these hierarchical model, the data are well-ordered into a tree-like structure,
where single parents or root have each record. Records of the siblings are sorted
into specific order. For keeping the DB that physical order used as an order. Actual
earth connections are describing in this hierarchical model.
8
associated data. They built with puts of interconnected data found on the
arithmetically set hypothesis.
It is defined the CODASYL after it was most popular in the 70s.
Figure 4: a Network model
5. Hierarchical model
In these hierarchical model, the data are well-ordered into a tree-like structure,
where single parents or root have each record. Records of the siblings are sorted
into specific order. For keeping the DB that physical order used as an order. Actual
earth connections are describing in this hierarchical model.
8
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.
Trusted by 1+ million students worldwide
Figure 5: Hierarchical model
Advantages and Disadvantages of each database model
1. Object-oriented database model
Advantages
Removal of impedance mismatch
In this, control the impedance inconsistency is a one language interface
between the DML (Data Manipulation Language) and the programming
language.
Improved Performance
Over relational DBMS, there have been numbers of specifications that have
been propose OODBMS give remarkable show advance.
Efficient of holds a huge variety of data types
Hierarchical, network or relational database are a different conventional
database, they are efficient to keep dis-similar kinds of records such as text,
voice video pictures and etc.
Disadvantages
Competition
Lack of standard
Lack of support for security
Complexity
9
Advantages and Disadvantages of each database model
1. Object-oriented database model
Advantages
Removal of impedance mismatch
In this, control the impedance inconsistency is a one language interface
between the DML (Data Manipulation Language) and the programming
language.
Improved Performance
Over relational DBMS, there have been numbers of specifications that have
been propose OODBMS give remarkable show advance.
Efficient of holds a huge variety of data types
Hierarchical, network or relational database are a different conventional
database, they are efficient to keep dis-similar kinds of records such as text,
voice video pictures and etc.
Disadvantages
Competition
Lack of standard
Lack of support for security
Complexity
9
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
2. Hierarchical database model
Advantages
For new information easy addition and deletion
Easy to work
It works one-to-many relationships
Disadvantages
Sophisticated relationships
Repetitively data store in many different entities
Slow when searching for information
3. Relational model
Advantages
Data independence
Data Manipulation
Security
Disadvantages
Slow extraction of meaning from data
Performance
Physical storage consumption
4. Entity-Relationship model
Advantages
Easy conversion
Effective communication tools
Conceptually it is very simple
Disadvantages
Limited relationship representation
Popular for high-level design
Loss of information content
5. Network model
10
Advantages
For new information easy addition and deletion
Easy to work
It works one-to-many relationships
Disadvantages
Sophisticated relationships
Repetitively data store in many different entities
Slow when searching for information
3. Relational model
Advantages
Data independence
Data Manipulation
Security
Disadvantages
Slow extraction of meaning from data
Performance
Physical storage consumption
4. Entity-Relationship model
Advantages
Easy conversion
Effective communication tools
Conceptually it is very simple
Disadvantages
Limited relationship representation
Popular for high-level design
Loss of information content
5. Network model
10
Advantages
Data independence
Ease of data access
Conceptual simplicity
Disadvantages
Operational anomalies
The absence of structural independence
System complexity
11
Data independence
Ease of data access
Conceptual simplicity
Disadvantages
Operational anomalies
The absence of structural independence
System complexity
11
⊘ This is a preview!⊘
Do you want full access?
Subscribe today to unlock all pages.
Trusted by 1+ million students worldwide
1 out of 60
Related Documents
Your All-in-One AI-Powered Toolkit for Academic Success.
+13062052269
info@desklib.com
Available 24*7 on WhatsApp / Email
![[object Object]](/_next/static/media/star-bottom.7253800d.svg)
Unlock your academic potential
Copyright © 2020–2025 A2Z Services. All Rights Reserved. Developed and managed by ZUCOL.