Data Analysis and Design: Designing and Implementing a Relational Database System for Dominican College
Added on 2024-05-31
59 Pages6056 Words489 Views
|
|
|
Data Analysis and Design
Table of Contents
Introduction....................................................................................................................................................6
LO1 Understand data models and database technologies..............................................................................7
1.1 critically compare different data models and schemas........................................................................7
1.2 critically discuss the benefits and limitations of different database technologies...............................7
1.3 Analyze different approaches to database design................................................................................9
LO2 Be able to design and implement relational database systems............................................................10
2.1 Design a relational database system to meet a given requirement.....................................................10
ER DIAGRAM....................................................................................................................................10
NORMALIZATION............................................................................................................................11
2.2 Build a relational database system based on a prepared design.........................................................12
Tables Design View.............................................................................................................................12
Tables Datasheet View........................................................................................................................14
2.3 Apply a range of database tools and techniques to enhance the user interface.................................15
Forms...................................................................................................................................................15
Buttons.................................................................................................................................................18
..............................................................................................................................................................18
Reports.................................................................................................................................................18
Relationship.........................................................................................................................................21
Queries.................................................................................................................................................23
LO3 Be able to use manipulation and querying tools..................................................................................24
3.1 Explain the benefits of using manipulation and query tools in a relational database system............24
Query Language...................................................................................................................................24
Data Manipulation:..............................................................................................................................24
3.2 implement a query language into the relational database system......................................................27
Query 1.................................................................................................................................................27
Query 2.................................................................................................................................................28
Query 3.................................................................................................................................................30
Query 4.................................................................................................................................................32
Query 5.................................................................................................................................................33
Query 6.................................................................................................................................................34
Query 7.................................................................................................................................................35
Query 8.................................................................................................................................................37
3.3 Critically evaluate how meaningful data has been extracted through the use of query tools............39
Introduction....................................................................................................................................................6
LO1 Understand data models and database technologies..............................................................................7
1.1 critically compare different data models and schemas........................................................................7
1.2 critically discuss the benefits and limitations of different database technologies...............................7
1.3 Analyze different approaches to database design................................................................................9
LO2 Be able to design and implement relational database systems............................................................10
2.1 Design a relational database system to meet a given requirement.....................................................10
ER DIAGRAM....................................................................................................................................10
NORMALIZATION............................................................................................................................11
2.2 Build a relational database system based on a prepared design.........................................................12
Tables Design View.............................................................................................................................12
Tables Datasheet View........................................................................................................................14
2.3 Apply a range of database tools and techniques to enhance the user interface.................................15
Forms...................................................................................................................................................15
Buttons.................................................................................................................................................18
..............................................................................................................................................................18
Reports.................................................................................................................................................18
Relationship.........................................................................................................................................21
Queries.................................................................................................................................................23
LO3 Be able to use manipulation and querying tools..................................................................................24
3.1 Explain the benefits of using manipulation and query tools in a relational database system............24
Query Language...................................................................................................................................24
Data Manipulation:..............................................................................................................................24
3.2 implement a query language into the relational database system......................................................27
Query 1.................................................................................................................................................27
Query 2.................................................................................................................................................28
Query 3.................................................................................................................................................30
Query 4.................................................................................................................................................32
Query 5.................................................................................................................................................33
Query 6.................................................................................................................................................34
Query 7.................................................................................................................................................35
Query 8.................................................................................................................................................37
3.3 Critically evaluate how meaningful data has been extracted through the use of query tools............39
LO4 Be able to test and document relational database systems..................................................................45
4.1 Critically review and test a relational database system.....................................................................45
Questionnaire.......................................................................................................................................45
Testing strategies.................................................................................................................................46
4.2 Create documentation to support the implementation and testing of a relational database system...46
4.3 create user documentation for a developed relational database system.............................................49
Screenshots of the dashboard...............................................................................................................49
Screenshots of all the panels................................................................................................................51
How to create Queries?........................................................................................................................54
How to create Reports?........................................................................................................................54
4.4 Explain how verification and validation has been addressed............................................................54
Phases of V- Model..............................................................................................................................54
Screenshots of the Validation done......................................................................................................55
4.5 explain how control mechanisms have been used.............................................................................56
Conclusion...................................................................................................................................................58
References....................................................................................................................................................59
Appendix:.....................................................................................................................................................60
List of Tables
Table 1- 1st normalization...........................................................................................................................11
Table 2- 2nd normalization..........................................................................................................................11
Table 3- Questionnaire.................................................................................................................................45
Table 4- Test Case Documentation..............................................................................................................46
List of Figures
Figure 1- ER Diagram..................................................................................................................................10
Figure 2- Student Design View....................................................................................................................12
Figure 3- Lecturer Design View..................................................................................................................12
Figure 4- Course Design View....................................................................................................................13
Figure 5- Unit Design View.........................................................................................................................13
Figure 6- Academic manager Design View.................................................................................................13
Figure 7- College Admin Design View.......................................................................................................13
Figure 8- Student Datasheet View...............................................................................................................14
Figure 9- Lecturer Datasheet View..............................................................................................................14
4.1 Critically review and test a relational database system.....................................................................45
Questionnaire.......................................................................................................................................45
Testing strategies.................................................................................................................................46
4.2 Create documentation to support the implementation and testing of a relational database system...46
4.3 create user documentation for a developed relational database system.............................................49
Screenshots of the dashboard...............................................................................................................49
Screenshots of all the panels................................................................................................................51
How to create Queries?........................................................................................................................54
How to create Reports?........................................................................................................................54
4.4 Explain how verification and validation has been addressed............................................................54
Phases of V- Model..............................................................................................................................54
Screenshots of the Validation done......................................................................................................55
4.5 explain how control mechanisms have been used.............................................................................56
Conclusion...................................................................................................................................................58
References....................................................................................................................................................59
Appendix:.....................................................................................................................................................60
List of Tables
Table 1- 1st normalization...........................................................................................................................11
Table 2- 2nd normalization..........................................................................................................................11
Table 3- Questionnaire.................................................................................................................................45
Table 4- Test Case Documentation..............................................................................................................46
List of Figures
Figure 1- ER Diagram..................................................................................................................................10
Figure 2- Student Design View....................................................................................................................12
Figure 3- Lecturer Design View..................................................................................................................12
Figure 4- Course Design View....................................................................................................................13
Figure 5- Unit Design View.........................................................................................................................13
Figure 6- Academic manager Design View.................................................................................................13
Figure 7- College Admin Design View.......................................................................................................13
Figure 8- Student Datasheet View...............................................................................................................14
Figure 9- Lecturer Datasheet View..............................................................................................................14
Figure 10- Unit Datasheet View..................................................................................................................14
Figure 11- Academic Manager Datasheet View..........................................................................................14
Figure 12- Course Datasheet View..............................................................................................................15
Figure 13- College Admin Datasheet View.................................................................................................15
Figure 14- Academic Manager Form View.................................................................................................16
Figure 15- College Admin Form View........................................................................................................16
Figure 16- Course Form View.....................................................................................................................16
Figure 17- Lecturer Form View...................................................................................................................17
Figure 18- Student Form View....................................................................................................................17
Figure 19- Unit Form View.........................................................................................................................18
Figure 20- Academic Manager Table Report view......................................................................................19
Figure 21- College Admin Table Report view............................................................................................19
Figure 22- Course Table Report view..........................................................................................................20
Figure 23- Lecturer Table Report view.......................................................................................................20
Figure 24- Student Table Report view.........................................................................................................20
Figure 25- Unit Table Report view..............................................................................................................21
Figure 26- Relationship................................................................................................................................22
Figure 27- Query Datasheet View...............................................................................................................23
Figure 28- Unit Table Data..........................................................................................................................25
Figure 29- Count Output..............................................................................................................................25
Figure 30- Course Table data.......................................................................................................................26
Figure 31- Sum Output................................................................................................................................26
Figure 32- MIN Output................................................................................................................................26
Figure 33- MAX Output..............................................................................................................................27
Figure 34- Query 1 SQL view.....................................................................................................................27
Figure 35- Query 1 Design View.................................................................................................................27
Figure 36- Query 1 Result............................................................................................................................28
Figure 37- Query 2 Part A SQL view..........................................................................................................28
Figure 38- Query 2 Part A Design View.....................................................................................................29
Figure 39- Query 2 Part A Result................................................................................................................29
Figure 40- Query 2 Part B SQL View.........................................................................................................29
Figure 41- Query 2 Part B Design View......................................................................................................30
Figure 42-Query 2 Part B Result.................................................................................................................30
Figure 43- Query 3 SQL..............................................................................................................................31
Figure 44- Query 3 Design View.................................................................................................................31
Figure 45- Query 3 Result............................................................................................................................32
Figure 46- Query 4 SQL..............................................................................................................................32
Figure 47-Query 4 Design View..................................................................................................................32
Figure 48- Query 4 Result............................................................................................................................33
Figure 49- Query 5 SQL..............................................................................................................................33
Figure 50-Query 5 Design View..................................................................................................................33
Figure 51- Query 5 Result............................................................................................................................34
Figure 52- Query 6 SQL..............................................................................................................................34
Figure 53-Query 6 Design View..................................................................................................................35
Figure 54- Query 6 Result............................................................................................................................35
Figure 55- Query 7 SQL View.....................................................................................................................35
Figure 56-Query 7 Design View..................................................................................................................36
Figure 11- Academic Manager Datasheet View..........................................................................................14
Figure 12- Course Datasheet View..............................................................................................................15
Figure 13- College Admin Datasheet View.................................................................................................15
Figure 14- Academic Manager Form View.................................................................................................16
Figure 15- College Admin Form View........................................................................................................16
Figure 16- Course Form View.....................................................................................................................16
Figure 17- Lecturer Form View...................................................................................................................17
Figure 18- Student Form View....................................................................................................................17
Figure 19- Unit Form View.........................................................................................................................18
Figure 20- Academic Manager Table Report view......................................................................................19
Figure 21- College Admin Table Report view............................................................................................19
Figure 22- Course Table Report view..........................................................................................................20
Figure 23- Lecturer Table Report view.......................................................................................................20
Figure 24- Student Table Report view.........................................................................................................20
Figure 25- Unit Table Report view..............................................................................................................21
Figure 26- Relationship................................................................................................................................22
Figure 27- Query Datasheet View...............................................................................................................23
Figure 28- Unit Table Data..........................................................................................................................25
Figure 29- Count Output..............................................................................................................................25
Figure 30- Course Table data.......................................................................................................................26
Figure 31- Sum Output................................................................................................................................26
Figure 32- MIN Output................................................................................................................................26
Figure 33- MAX Output..............................................................................................................................27
Figure 34- Query 1 SQL view.....................................................................................................................27
Figure 35- Query 1 Design View.................................................................................................................27
Figure 36- Query 1 Result............................................................................................................................28
Figure 37- Query 2 Part A SQL view..........................................................................................................28
Figure 38- Query 2 Part A Design View.....................................................................................................29
Figure 39- Query 2 Part A Result................................................................................................................29
Figure 40- Query 2 Part B SQL View.........................................................................................................29
Figure 41- Query 2 Part B Design View......................................................................................................30
Figure 42-Query 2 Part B Result.................................................................................................................30
Figure 43- Query 3 SQL..............................................................................................................................31
Figure 44- Query 3 Design View.................................................................................................................31
Figure 45- Query 3 Result............................................................................................................................32
Figure 46- Query 4 SQL..............................................................................................................................32
Figure 47-Query 4 Design View..................................................................................................................32
Figure 48- Query 4 Result............................................................................................................................33
Figure 49- Query 5 SQL..............................................................................................................................33
Figure 50-Query 5 Design View..................................................................................................................33
Figure 51- Query 5 Result............................................................................................................................34
Figure 52- Query 6 SQL..............................................................................................................................34
Figure 53-Query 6 Design View..................................................................................................................35
Figure 54- Query 6 Result............................................................................................................................35
Figure 55- Query 7 SQL View.....................................................................................................................35
Figure 56-Query 7 Design View..................................................................................................................36
Figure 57- Query 7 Result............................................................................................................................36
Figure 58- Academic Manager form to add, amend and delete...................................................................36
Figure 59- Query 8 SQL View.....................................................................................................................37
Figure 60-Query 8 Design View..................................................................................................................37
Figure 61- Query 8 Result............................................................................................................................38
Figure 62- College Admin form to add, amend and delete..........................................................................38
Figure 63- Query 1 Report...........................................................................................................................39
Figure 64- Query 2 Part A report.................................................................................................................40
Figure 65- Query 2 Part B report.................................................................................................................41
Figure 66- Query 3 report............................................................................................................................41
Figure 67- Query 4 Report...........................................................................................................................42
Figure 68- Query 5 Report...........................................................................................................................42
Figure 69- Query 6 Report...........................................................................................................................43
Figure 70- Query 7 report............................................................................................................................43
Figure 71- Query 8 report............................................................................................................................44
Figure 72- Foreign Key Validation..............................................................................................................47
Figure 73- Name Validation........................................................................................................................47
Figure 74- Contact Number Validation.......................................................................................................48
Figure 75- Email address Validation...........................................................................................................48
Figure 76- Academic_Manager Dashboard.................................................................................................49
Figure 77- College Admin Dashboard.........................................................................................................49
Figure 78- Course dashboard.......................................................................................................................49
Figure 79- Lecturer Dashboard....................................................................................................................50
Figure 80- Student dashboard......................................................................................................................50
Figure 81- Unit Dashboard..........................................................................................................................51
Figure 82- Academic_manager panel..........................................................................................................51
Figure 83- CollegeAdmin panel...................................................................................................................52
Figure 84- Course panel...............................................................................................................................52
Figure 85- Lecturer panel.............................................................................................................................52
Figure 86- Student panel..............................................................................................................................53
Figure 87- Unit panel...................................................................................................................................53
Figure 88- Tutor Name Validation..............................................................................................................55
Figure 89- Contact Number Validation.......................................................................................................55
Figure 90- Email Address Validation..........................................................................................................55
Figure 91- Timing Validation......................................................................................................................55
Figure 92- Control Mechanism in Course Table.........................................................................................56
Figure 93- Control Mechanism in Lecturer Table.......................................................................................56
Figure 94- Control Mechanism in Student Table........................................................................................56
Figure 95-Control Mechanism in Unit Table...............................................................................................56
Figure 96- Referential Integrity...................................................................................................................57
Figure 58- Academic Manager form to add, amend and delete...................................................................36
Figure 59- Query 8 SQL View.....................................................................................................................37
Figure 60-Query 8 Design View..................................................................................................................37
Figure 61- Query 8 Result............................................................................................................................38
Figure 62- College Admin form to add, amend and delete..........................................................................38
Figure 63- Query 1 Report...........................................................................................................................39
Figure 64- Query 2 Part A report.................................................................................................................40
Figure 65- Query 2 Part B report.................................................................................................................41
Figure 66- Query 3 report............................................................................................................................41
Figure 67- Query 4 Report...........................................................................................................................42
Figure 68- Query 5 Report...........................................................................................................................42
Figure 69- Query 6 Report...........................................................................................................................43
Figure 70- Query 7 report............................................................................................................................43
Figure 71- Query 8 report............................................................................................................................44
Figure 72- Foreign Key Validation..............................................................................................................47
Figure 73- Name Validation........................................................................................................................47
Figure 74- Contact Number Validation.......................................................................................................48
Figure 75- Email address Validation...........................................................................................................48
Figure 76- Academic_Manager Dashboard.................................................................................................49
Figure 77- College Admin Dashboard.........................................................................................................49
Figure 78- Course dashboard.......................................................................................................................49
Figure 79- Lecturer Dashboard....................................................................................................................50
Figure 80- Student dashboard......................................................................................................................50
Figure 81- Unit Dashboard..........................................................................................................................51
Figure 82- Academic_manager panel..........................................................................................................51
Figure 83- CollegeAdmin panel...................................................................................................................52
Figure 84- Course panel...............................................................................................................................52
Figure 85- Lecturer panel.............................................................................................................................52
Figure 86- Student panel..............................................................................................................................53
Figure 87- Unit panel...................................................................................................................................53
Figure 88- Tutor Name Validation..............................................................................................................55
Figure 89- Contact Number Validation.......................................................................................................55
Figure 90- Email Address Validation..........................................................................................................55
Figure 91- Timing Validation......................................................................................................................55
Figure 92- Control Mechanism in Course Table.........................................................................................56
Figure 93- Control Mechanism in Lecturer Table.......................................................................................56
Figure 94- Control Mechanism in Student Table........................................................................................56
Figure 95-Control Mechanism in Unit Table...............................................................................................56
Figure 96- Referential Integrity...................................................................................................................57
Introduction
The report will discuss on the various approaches of creating the database. The different technologies that
can be used for creating the database will be discussed. The database will be designed and the modelling
of the entities, attributes and relationships will be done. The database for the Dominican college will be
build on MS- Access. The report will present all the user documentation on different elements of the
database. The report will discuss on tables in design and datasheet view, the user interaction of the
database through the forms and reports, query processing, verification and validation through forms,
dashboards, panels and reports of the queries. The report will also discuss about the testing of the
database.
The report will discuss on the various approaches of creating the database. The different technologies that
can be used for creating the database will be discussed. The database will be designed and the modelling
of the entities, attributes and relationships will be done. The database for the Dominican college will be
build on MS- Access. The report will present all the user documentation on different elements of the
database. The report will discuss on tables in design and datasheet view, the user interaction of the
database through the forms and reports, query processing, verification and validation through forms,
dashboards, panels and reports of the queries. The report will also discuss about the testing of the
database.
LO1 Understand data models and database technologies
1.1 critically compare different data models and schemas
A database display or an information demonstrate is graphical portrayal of how information is overseen in
a database. It denes the capacity, administration and association of information inside the database. Most
normally utilized database models are:
• Hierarchical Model
• Relational Model
• Network Model
Hierarchical Model: In this kind of model, one parent (additionally called root) can have at least one
youngsters. Control streams from guardians to kid’s elements.
Relational Model: in this sort of structure information is composed in social table
Network Model: In this model every element is associated with in excess of one element, shaping a
diagram sort of structure. Every element can be gotten to from in excess of one way.
1.2 critically discuss the benefits and limitations of different database technologies
MS access is the database developed the Microsoft for creation and designing of the database. No matter
it’s the small-scale business or the large-scale business.
Advantages of the MS access:
• Very easy to install no programming knowledge required.
• Can be easily implemented with many languages such as php, java, c# etc.
• It can store data in bulk and can save a lot of money as it comes in the package provided by the
Microsoft office.
• It can store up to 2GB of data at a time.
• It also provides the multi user support action
• It can be easily imported with any hardcore knowledge.
Limitations:
• The data limitation binds to 2GB beyond this cannot be handled by the MS Access.
1.1 critically compare different data models and schemas
A database display or an information demonstrate is graphical portrayal of how information is overseen in
a database. It denes the capacity, administration and association of information inside the database. Most
normally utilized database models are:
• Hierarchical Model
• Relational Model
• Network Model
Hierarchical Model: In this kind of model, one parent (additionally called root) can have at least one
youngsters. Control streams from guardians to kid’s elements.
Relational Model: in this sort of structure information is composed in social table
Network Model: In this model every element is associated with in excess of one element, shaping a
diagram sort of structure. Every element can be gotten to from in excess of one way.
1.2 critically discuss the benefits and limitations of different database technologies
MS access is the database developed the Microsoft for creation and designing of the database. No matter
it’s the small-scale business or the large-scale business.
Advantages of the MS access:
• Very easy to install no programming knowledge required.
• Can be easily implemented with many languages such as php, java, c# etc.
• It can store data in bulk and can save a lot of money as it comes in the package provided by the
Microsoft office.
• It can store up to 2GB of data at a time.
• It also provides the multi user support action
• It can be easily imported with any hardcore knowledge.
Limitations:
• The data limitation binds to 2GB beyond this cannot be handled by the MS Access.
• It stores all the data such as forms, reports queries and the data all in one file.
• Only 10 users can assess it at a time. Here user do bind as only 10 multiple users can use it at one
time.
Oracle is a database administration framework produced by SUN Microsystems. One of the real preferred
standpoint and in addition burden of oracle is that it is helpful just for expansive associations with
complex database prerequisites.
Subsequently focal points of Oracle are:
• Oracle bolsters expansive databases.
• It plays out every exchange independently and the resultant isn't noticeable to other exchange until the
point that it is finished, which makes database secure. It makes utilization of triggers and cursors making
programming simple.
• Can handle databases of any size and number of records.
Hindrance of Oracle:
• Only valuable for overseeing expansive applications can't be utilized with little associations.
• Price and equipment cost required to run it are high.
Microsoft Structured Programming Language or SQL is most broadly utilized question dialect with parcel
numerous points of interest:
• It is broadly utilized for overseeing social databases
• It is intelligent, simple to learn and get it
• It is versatile and can keep running on any sort of gadget like, pc's, workstations, centralized servers
and cell phones.
• It is dependable and has vigorous security instrument
• It can take a shot at customer server engineering model, by interfacing front end application with back
end database
• It can bolster question-based programming and can coordinate with larger amount programming
dialects like java by utilizing and API called JDBC (Java database availability)
• Only 10 users can assess it at a time. Here user do bind as only 10 multiple users can use it at one
time.
Oracle is a database administration framework produced by SUN Microsystems. One of the real preferred
standpoint and in addition burden of oracle is that it is helpful just for expansive associations with
complex database prerequisites.
Subsequently focal points of Oracle are:
• Oracle bolsters expansive databases.
• It plays out every exchange independently and the resultant isn't noticeable to other exchange until the
point that it is finished, which makes database secure. It makes utilization of triggers and cursors making
programming simple.
• Can handle databases of any size and number of records.
Hindrance of Oracle:
• Only valuable for overseeing expansive applications can't be utilized with little associations.
• Price and equipment cost required to run it are high.
Microsoft Structured Programming Language or SQL is most broadly utilized question dialect with parcel
numerous points of interest:
• It is broadly utilized for overseeing social databases
• It is intelligent, simple to learn and get it
• It is versatile and can keep running on any sort of gadget like, pc's, workstations, centralized servers
and cell phones.
• It is dependable and has vigorous security instrument
• It can take a shot at customer server engineering model, by interfacing front end application with back
end database
• It can bolster question-based programming and can coordinate with larger amount programming
dialects like java by utilizing and API called JDBC (Java database availability)
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents
Data Analysis and Design for Dominican College Course Management Systemlg...
|62
|6553
|328
Relational Database Design and Implementation for Dominican Collegelg...
|24
|1416
|172
Database Design and Implementation for Dominican Collegelg...
|60
|5189
|463
Relational Database Design and Implementation for Dominican Collegelg...
|62
|6951
|171
Database Analysis and Design: A Case Study of Dominican Collegelg...
|102
|8346
|148
Database Design and Development for Shoengalleric Art Gallerylg...
|66
|5608
|329