Database Design and Implementation for Dominican College
Added on 2024-06-07
60 Pages5189 Words463 Views
|
|
|
Contents
List of figures.........................................................................................................................................1
List of table............................................................................................................................................3
Introduction...........................................................................................................................................4
LO1 Understand data models and database technologies....................................................................5
1.1 Critically compare different data models and schemas...............................................................5
1.2 Critically discuss the benefits and limitations of different database technologies......................8
1.3 Analyze different approaches to database design.....................................................................10
LO2 Be able to design and implement relational database systems....................................................13
2.1 Design a relational database system to meet a given requirement...........................................13
2.2 Build a relational database system based on a prepared design...............................................16
2.3 Apply a range of database tools and techniques to enhance the user interface.......................26
LO3 Use of manipulation and querying tools......................................................................................36
3.1 Explain the benefits of using manipulation and query tools in relational database systems per
the requirements are given in the case study..................................................................................36
3.2 Implement a query language into the relational database system......................................36
3.3 Critically evaluate how meaningful data has been extracted through the use of query tools for
the given case study........................................................................................................................39
LO4 Be able to test and document relational database systems.........................................................41
4.1 Critically reviews and tests the implemented relational database system................................41
4.2 Create documentation to support the implementation and testing of a relational database
system.............................................................................................................................................41
4.3 Create user documentation for a developed relational database system.................................45
4.4 Explain how verification and validation has been addressed.....................................................49
4.5 Explain how control mechanisms have been used....................................................................51
Conclusion...........................................................................................................................................59
References...........................................................................................................................................60
List of figures
Figure 1 Hierarchical data model...........................................................................................................5
Figure 2 Relational Model......................................................................................................................6
Figure 3 Network Model........................................................................................................................7
Figure 4 Categories of the database schema.........................................................................................8
Figure 5 Top-down Approach..............................................................................................................10
Figure 6 Bottom-up Approach.............................................................................................................11
List of figures.........................................................................................................................................1
List of table............................................................................................................................................3
Introduction...........................................................................................................................................4
LO1 Understand data models and database technologies....................................................................5
1.1 Critically compare different data models and schemas...............................................................5
1.2 Critically discuss the benefits and limitations of different database technologies......................8
1.3 Analyze different approaches to database design.....................................................................10
LO2 Be able to design and implement relational database systems....................................................13
2.1 Design a relational database system to meet a given requirement...........................................13
2.2 Build a relational database system based on a prepared design...............................................16
2.3 Apply a range of database tools and techniques to enhance the user interface.......................26
LO3 Use of manipulation and querying tools......................................................................................36
3.1 Explain the benefits of using manipulation and query tools in relational database systems per
the requirements are given in the case study..................................................................................36
3.2 Implement a query language into the relational database system......................................36
3.3 Critically evaluate how meaningful data has been extracted through the use of query tools for
the given case study........................................................................................................................39
LO4 Be able to test and document relational database systems.........................................................41
4.1 Critically reviews and tests the implemented relational database system................................41
4.2 Create documentation to support the implementation and testing of a relational database
system.............................................................................................................................................41
4.3 Create user documentation for a developed relational database system.................................45
4.4 Explain how verification and validation has been addressed.....................................................49
4.5 Explain how control mechanisms have been used....................................................................51
Conclusion...........................................................................................................................................59
References...........................................................................................................................................60
List of figures
Figure 1 Hierarchical data model...........................................................................................................5
Figure 2 Relational Model......................................................................................................................6
Figure 3 Network Model........................................................................................................................7
Figure 4 Categories of the database schema.........................................................................................8
Figure 5 Top-down Approach..............................................................................................................10
Figure 6 Bottom-up Approach.............................................................................................................11
Figure 7 Centralized approach.............................................................................................................11
Figure 8 Decentralized approach.........................................................................................................12
Figure 1 ER diagram.............................................................................................................................13
Figure 2 Entity Relationship diagram...................................................................................................14
Figure 3 academic manager Design view.............................................................................................17
Figure 4 academic manager datasheet table.......................................................................................17
Figure 5 campus table Design view......................................................................................................18
Figure 6 campus table datasheet table................................................................................................18
Figure 7 admin table Design view........................................................................................................19
Figure 8 admin table datasheet table..................................................................................................20
Figure 9 lecturer table Design view.....................................................................................................21
Figure 10 lecturer table datasheet table.............................................................................................21
Figure 11 Course table Design view.....................................................................................................22
Figure 12 Course table datasheet table...............................................................................................22
Figure 13 student table Design view....................................................................................................23
Figure 14 student table datasheet table..............................................................................................24
Figure 15 units table Design view.......................................................................................................25
Figure 16 units table datasheet table..................................................................................................26
Figure 17 Form 1..................................................................................................................................26
Figure 18 Form 2..................................................................................................................................27
Figure 19 Form 3..................................................................................................................................28
Figure 20 Form 4..................................................................................................................................28
Figure 21 Form 5..................................................................................................................................29
Figure 22 Form 6..................................................................................................................................29
Figure 23 Form 7..................................................................................................................................30
Figure 24 report 1................................................................................................................................30
Figure 25 report 2................................................................................................................................31
Figure 26 report 3................................................................................................................................31
Figure 27 report 4................................................................................................................................32
Figure 28 report 5................................................................................................................................32
Figure 29 report 6................................................................................................................................32
Figure 30 report 7................................................................................................................................33
Figure 31 report 8................................................................................................................................33
Figure 32 Entity relationship diagram..................................................................................................34
Figure 33 design view..........................................................................................................................35
Figure 34 datasheet view.....................................................................................................................35
Figure 35 Query 1................................................................................................................................36
Figure 36 Query 2................................................................................................................................37
Figure 37 Query 3................................................................................................................................37
Figure 38 Query 4................................................................................................................................37
Figure 39 Query 5................................................................................................................................38
Figure 40 Query 6................................................................................................................................38
Figure 41 Query 7................................................................................................................................38
Figure 42 Query 8................................................................................................................................39
Figure 43 test case 1............................................................................................................................43
Figure 8 Decentralized approach.........................................................................................................12
Figure 1 ER diagram.............................................................................................................................13
Figure 2 Entity Relationship diagram...................................................................................................14
Figure 3 academic manager Design view.............................................................................................17
Figure 4 academic manager datasheet table.......................................................................................17
Figure 5 campus table Design view......................................................................................................18
Figure 6 campus table datasheet table................................................................................................18
Figure 7 admin table Design view........................................................................................................19
Figure 8 admin table datasheet table..................................................................................................20
Figure 9 lecturer table Design view.....................................................................................................21
Figure 10 lecturer table datasheet table.............................................................................................21
Figure 11 Course table Design view.....................................................................................................22
Figure 12 Course table datasheet table...............................................................................................22
Figure 13 student table Design view....................................................................................................23
Figure 14 student table datasheet table..............................................................................................24
Figure 15 units table Design view.......................................................................................................25
Figure 16 units table datasheet table..................................................................................................26
Figure 17 Form 1..................................................................................................................................26
Figure 18 Form 2..................................................................................................................................27
Figure 19 Form 3..................................................................................................................................28
Figure 20 Form 4..................................................................................................................................28
Figure 21 Form 5..................................................................................................................................29
Figure 22 Form 6..................................................................................................................................29
Figure 23 Form 7..................................................................................................................................30
Figure 24 report 1................................................................................................................................30
Figure 25 report 2................................................................................................................................31
Figure 26 report 3................................................................................................................................31
Figure 27 report 4................................................................................................................................32
Figure 28 report 5................................................................................................................................32
Figure 29 report 6................................................................................................................................32
Figure 30 report 7................................................................................................................................33
Figure 31 report 8................................................................................................................................33
Figure 32 Entity relationship diagram..................................................................................................34
Figure 33 design view..........................................................................................................................35
Figure 34 datasheet view.....................................................................................................................35
Figure 35 Query 1................................................................................................................................36
Figure 36 Query 2................................................................................................................................37
Figure 37 Query 3................................................................................................................................37
Figure 38 Query 4................................................................................................................................37
Figure 39 Query 5................................................................................................................................38
Figure 40 Query 6................................................................................................................................38
Figure 41 Query 7................................................................................................................................38
Figure 42 Query 8................................................................................................................................39
Figure 43 test case 1............................................................................................................................43
Figure 44 test case 2............................................................................................................................43
Figure 45 test case 3............................................................................................................................44
Figure 46 test case 4............................................................................................................................44
Figure 47 test case 5............................................................................................................................44
Figure 48 test case 6............................................................................................................................45
Figure 49 test case 7............................................................................................................................45
Figure 50 test case 8............................................................................................................................45
Figure 51 form 1..................................................................................................................................46
Figure 52 form 2..................................................................................................................................46
Figure 53 form 3..................................................................................................................................47
Figure 54 form 4..................................................................................................................................47
Figure 55 form 5..................................................................................................................................48
Figure 56 form 6..................................................................................................................................48
Figure 57 form 7..................................................................................................................................49
Figure 58 validation rule......................................................................................................................50
Figure 59 verification of data types.....................................................................................................51
Figure 60 academic control mechanism..............................................................................................52
Figure 61 admin control mechanism...................................................................................................53
Figure 62 campus control mechanism.................................................................................................54
Figure 63 course control mechanism...................................................................................................55
Figure 64 lecturer control mechanism.................................................................................................56
Figure 65 student control mechanism.................................................................................................57
Figure 66 unit control mechanism.......................................................................................................58
List of table
Table 1 1 NF table................................................................................................................................14
Table 2 2 NF table................................................................................................................................15
Table 3 3NF table.................................................................................................................................15
Table 4 reviews table...........................................................................................................................41
Table 5 table of test cases...................................................................................................................41
Figure 45 test case 3............................................................................................................................44
Figure 46 test case 4............................................................................................................................44
Figure 47 test case 5............................................................................................................................44
Figure 48 test case 6............................................................................................................................45
Figure 49 test case 7............................................................................................................................45
Figure 50 test case 8............................................................................................................................45
Figure 51 form 1..................................................................................................................................46
Figure 52 form 2..................................................................................................................................46
Figure 53 form 3..................................................................................................................................47
Figure 54 form 4..................................................................................................................................47
Figure 55 form 5..................................................................................................................................48
Figure 56 form 6..................................................................................................................................48
Figure 57 form 7..................................................................................................................................49
Figure 58 validation rule......................................................................................................................50
Figure 59 verification of data types.....................................................................................................51
Figure 60 academic control mechanism..............................................................................................52
Figure 61 admin control mechanism...................................................................................................53
Figure 62 campus control mechanism.................................................................................................54
Figure 63 course control mechanism...................................................................................................55
Figure 64 lecturer control mechanism.................................................................................................56
Figure 65 student control mechanism.................................................................................................57
Figure 66 unit control mechanism.......................................................................................................58
List of table
Table 1 1 NF table................................................................................................................................14
Table 2 2 NF table................................................................................................................................15
Table 3 3NF table.................................................................................................................................15
Table 4 reviews table...........................................................................................................................41
Table 5 table of test cases...................................................................................................................41
Introduction
This report is developed for Dominican college who wants to provide a database to the user in which
they can store information of college members, students, course, lecturer, units and more. This
database system is designed in such way that it fulfills the requirement of Dominican College and
provides a fully functional system to store information. The report determines various database
technologies and entity relationship diagram which give an idea to design database. The testing will
be provided to validate and ensure the quality of database design. The user documentation explains
the forms and their use by showing screen captures. The report is prepared on database design
which describes SQL queries, forms, ER diagrams to help in providing an understanding of this
system in a proper manner.
This report is developed for Dominican college who wants to provide a database to the user in which
they can store information of college members, students, course, lecturer, units and more. This
database system is designed in such way that it fulfills the requirement of Dominican College and
provides a fully functional system to store information. The report determines various database
technologies and entity relationship diagram which give an idea to design database. The testing will
be provided to validate and ensure the quality of database design. The user documentation explains
the forms and their use by showing screen captures. The report is prepared on database design
which describes SQL queries, forms, ER diagrams to help in providing an understanding of this
system in a proper manner.
LO1 Understand data models and database
technologies
1.1 Critically compare different data models and schemas
Database Model: This shows the logical structure of a database in addition to constraints and
relationships enabling how the data is going to be accessed and stored. The relationship within the
database depicts how the data is retrieved and stored. It is a kind of data model which explains how
data is going to be organized, manipulated and stored. Database model has been categorized in
following types:
Hierarchical data model:
It organizes data in a tree-like form in which every record has either root or single parent. The record
of a sibling arranged in a specific order & that is going to be utilized as a physical order to store the
database. It is helpful in describing a lot of real-world relationships.
Figure 1 Hierarchical data model
This database model makes it compulsory that a child only has a single parent but the parent will have
single or more than a single child. For retrieving data in a hierarchical database traversing of the tree
will be done from the root node. This model was first generated by IBM in the 60s.
Relational data model:
technologies
1.1 Critically compare different data models and schemas
Database Model: This shows the logical structure of a database in addition to constraints and
relationships enabling how the data is going to be accessed and stored. The relationship within the
database depicts how the data is retrieved and stored. It is a kind of data model which explains how
data is going to be organized, manipulated and stored. Database model has been categorized in
following types:
Hierarchical data model:
It organizes data in a tree-like form in which every record has either root or single parent. The record
of a sibling arranged in a specific order & that is going to be utilized as a physical order to store the
database. It is helpful in describing a lot of real-world relationships.
Figure 1 Hierarchical data model
This database model makes it compulsory that a child only has a single parent but the parent will have
single or more than a single child. For retrieving data in a hierarchical database traversing of the tree
will be done from the root node. This model was first generated by IBM in the 60s.
Relational data model:
This model is the most commonly known database model which performs sorting of data in tables
considered as relations. In every relation, there are rows and columns. Columns list an attribute of the
entity according to the question. All attributes combined are considered as a domain. The primary key
is either a specific attribute or attributes combination which can be referred to other tables known as a
foreign key.
Individual’s row is known as tuple which has data of a particular instance of an entity. This model
comprises of the relation between tables known as many-to-many, one-to-many, one-to-one
relationships.
Figure 2 Relational Model
Tables can be normalized within the database which makes the database adaptable, scalable and
flexible. When tables are normalized every data is either broken into smaller useful pieces or atomic.
A relational database is written into the standard query language.
Network data model:
This model is a flexible approach to represent objects and relations between them. By allowing many-
to-many relationships among records which are linked, the network model can be built on the
hierarchical model. Set of related records constructs the model. Every set has parent record or an
owner and child records or one or more member.
considered as relations. In every relation, there are rows and columns. Columns list an attribute of the
entity according to the question. All attributes combined are considered as a domain. The primary key
is either a specific attribute or attributes combination which can be referred to other tables known as a
foreign key.
Individual’s row is known as tuple which has data of a particular instance of an entity. This model
comprises of the relation between tables known as many-to-many, one-to-many, one-to-one
relationships.
Figure 2 Relational Model
Tables can be normalized within the database which makes the database adaptable, scalable and
flexible. When tables are normalized every data is either broken into smaller useful pieces or atomic.
A relational database is written into the standard query language.
Network data model:
This model is a flexible approach to represent objects and relations between them. By allowing many-
to-many relationships among records which are linked, the network model can be built on the
hierarchical model. Set of related records constructs the model. Every set has parent record or an
owner and child records or one or more member.
Figure 3 Network Model
Object-Oriented data model:
The database is defined as a set of objects, or software elements which are reusable in addition to
associated methods and features. The object-oriented model has been categorized as:
Media is incorporated through the multimedia database. A hypertext database allows linking of one
object to another object.
This model does incorporate tables but is not confined to tables. This type of model is known as
hybrid database models.
Database Schema:
The logical view of the entire database is represented through database schema. It helps in defining
how the relations are associated. Its entities and relationship among them are defined. It has the
detailed description of the database.
Object-Oriented data model:
The database is defined as a set of objects, or software elements which are reusable in addition to
associated methods and features. The object-oriented model has been categorized as:
Media is incorporated through the multimedia database. A hypertext database allows linking of one
object to another object.
This model does incorporate tables but is not confined to tables. This type of model is known as
hybrid database models.
Database Schema:
The logical view of the entire database is represented through database schema. It helps in defining
how the relations are associated. Its entities and relationship among them are defined. It has the
detailed description of the database.
Figure 4 Categories of the database schema
Database Schema has been categorized into two types includes:
Physical Schema: The way data is going to be stored in the database is defined through the physical
schema.
Logical Schema: Logical constraints are defined through this schema which requires to be
implemented on stored data. Integrity constraints, views, tables are defined through this schema.
1.2 Critically discuss the benefits and limitations of different database technologies
One of the technologies of the database in which any individuals as well as small organization use for
managing the small data. Microsoft SQL server can be used for managing large databases, to create
reports, tables, graphs, charts, matrices, and forms. The effective database only requires basic
knowledge of database and query language (Elmasri & Navathe, 2010).
Benefits of Microsoft Access:
It is easy to apply and install.
Integration is easy with all front-end applications.
It does not handle a large amount of data but provides a similar function to any other
application or database.
2GB of data can be stored in this.
Multi-user support is provided.
Database Schema has been categorized into two types includes:
Physical Schema: The way data is going to be stored in the database is defined through the physical
schema.
Logical Schema: Logical constraints are defined through this schema which requires to be
implemented on stored data. Integrity constraints, views, tables are defined through this schema.
1.2 Critically discuss the benefits and limitations of different database technologies
One of the technologies of the database in which any individuals as well as small organization use for
managing the small data. Microsoft SQL server can be used for managing large databases, to create
reports, tables, graphs, charts, matrices, and forms. The effective database only requires basic
knowledge of database and query language (Elmasri & Navathe, 2010).
Benefits of Microsoft Access:
It is easy to apply and install.
Integration is easy with all front-end applications.
It does not handle a large amount of data but provides a similar function to any other
application or database.
2GB of data can be stored in this.
Multi-user support is provided.
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
Database Analysis and Design: A Case Study of Dominican Collegelg...
|102
|8346
|148
Dominican College Course Management Database: Design, Implementation, and Documentationlg...
|59
|6147
|338
Relational Database Design and Implementation for Dominican Collegelg...
|62
|6951
|171
Database Design and Development for Shoengalleric Art Gallerylg...
|74
|6401
|66
Database Design and Development for Shoengalleric Art Gallerylg...
|66
|5608
|329