Database Design and Implementation using MS Access for Student Records

This assignment presents a comprehensive database design and implementation project using Microsoft Access. The document begins with an introduction to databases and database management systems, emphasizing the relational database model and MS Access. The main body details the data model, data dictionary, and the structure of the database, including the relationships between tables such as Course, Department, Faculty, and Student. It showcases the design of the database tables with their respective fields and data types. The assignment includes the creation of SQL queries to retrieve specific data, such as student information for a particular course or department details. Furthermore, it illustrates the implementation of forms for data entry and reports for data presentation. The report generated displays the details stored in the department table, providing a summary of the database's functionality. The conclusion reiterates the successful implementation of the database, highlighting the importance of data modeling and structured data storage for efficient information management and decision-making.

DATABASE DESIGN AND
IMPLEMENTATION USING MS
ACCESS

TABLE OF CONTENTS
INTRODUCTION...........................................................................................................................1
MAIN BODY..................................................................................................................................1
Data model for the persistent data...............................................................................................1
Data dictionary for the database..................................................................................................2
Database tables............................................................................................................................7
Queries.........................................................................................................................................9
Database forms..........................................................................................................................10
Database reports.........................................................................................................................11
CONCLUSION..............................................................................................................................12
REFERENCES..............................................................................................................................13

INTRODUCTION
There are various kinds of information system that helps an organization to transform
data into information so that required information can be gathered that can be further used for
decision making. These information systems are designed in such a manner that they can accept
the data, edit the data, find appropriate information and many more operations. Database is
designed for exactly same purpose (Harrington, 2016). Database can be defined as a set of
organized collection of information that can be made available to the user in any required
manner for decision making. All the information within the database should be related to each
other and all kind of unrelated information should be saved into new or other database. Most of
these databases consist of multiple tables with many different fields. The system that contains
database is known as database management system. there are various kinds of databases and
database management system. Some of the most commonly known databases are: relational
database, NoSQL, object-oriented database, desktop database programs and many more. For this
assignment Microsoft Access database (Connolly and Begg, 2015). Microsoft Access is a kind of
database management system offered by Microsoft that combines both GUI with relational
Microsoft Jet Database engines and various kinds of software development tools. It was initially
released on November, 1992 available in Microsoft office suite. This assignment will lay
emphasis on data model, data dictionary for the database, implementation of database,
populating database with test data, database queries, implementation of form for entering data
into the database and Implement reports for presenting information stored in the database.
Database created in MS Access will help in displaying student’s records, faculty details,
department details and course details as well as relationship between all the tables.
MAIN BODY
Data model for the persistent data
Data model can be defined as an abstract model that helps in organizing all the elements
of data and also helps in standardizing the way they relate to each other as well as real world
entity problems. In simple words it helps in defining structure of overall database and its tables
(Klochkov and et.al., 2016). Data models plays a vital role in supporting development of all
kinds of information system as these information systems are completely based upon accuracy of
databases. Compatibility of data within database can be achieved if with the help of appropriate
and correct data model in which relationship between all the entities and attributes has been
1

shown clearly. Accuracy in database and compatibility of data can help in increasing efficiency
and accuracy of the database.
Figure 1 Data model
Above data model helps in defining relationship between all the tables. These
relationships help in prevention of any kind of missing or deleted data. Relationship among
tables also consist of primary and foreign keys through which data model is created.
Data dictionary for the database
Data dictionary is also known as metadata repository and can be defined as central
repository of information about data such as usage, origin, meaning, format, relationship with
other data and many more (Jukic, Vrbsky and Nestorov, 2016). According to Oracle it can also
be defined as collection of metadata and tables. So, it can be said that definition and meaning of
this term completely relate to databases as well as database management system. it is used for
following things such as: describing database, integrating components of database and its
structure and act as a middleware that supports database. Here, data dictionary of entire database
schema has been explained as well as specification of all the tables with required details has been
explained.
 Data dictionary of entire schema of database
Below data dictionary table will help in explaining All the entities and their attributes of
the database. In the below data dictionary four entities have been described: Course which has
2

Course_id as primary key. Department which has department_id as primary key. Faculty which
hasfaculty_id as primary key and department_id and course_id as foreign key. Student which has
student_id as primary key and course_id and department_id as foreign key.
Primary key: primary key can be defined as an uniquely identified record present in a table
which is unique and not null i.e. it should have unique values and should not be null. All the
tables of this database have one primary key specified in the below data dictionary table.
Foreign key: Foreign key can be defined as a record present in a table which is primary key in
another table. Tables can have more than one foreign key and can accept null values.
Entities Attributes
Course  Course id (PK)
 Course_name
 Course_duration
Department  Department_id (PK)
 Department_name
 Head_of_department
Faculty  Faculty id (PK)
 Faculty_name
 Faculty_phoneNo
 Faculty_address
 Designation
 Department_id (FK)
 Course_id (FK)
Students  Student ID (PK)
 Student_name
 Student_phoneNo
 Student_address
 Course_id (FK)
 Department_id (FK)
 Specification of each table (Design view of each table in Microsoft Access)
3

Here, specification of tables with their attributes and data types will be explained.
Specification of all the tables includes field name and data type of each type of field.
1. Course table
Figure 2 Specification of course table
Figure 3 Field properties of course table
2. Department table
Figure 4 Specification of department table
4

Figure 5 Field property of department table
3. Faculty
Figure 6 Specification of faculty table
5

Figure 7 Field property of Faculty table
4. Student
Figure 8 Specification of Student Table
6

Figure 9 Field property of Student table
Database tables
In databases table can be defined as set of values or elements using vertical columns as
well as horizontal rows where cell is the intersection unit where columns and horizontal rows
interact. A table has fixed number of columns but can have any number of rows ( Di Crescenzo,
and et.al., 2016). A database can have any number of tables but each table holds different amount
of data but is related to each other. The main purpose of tables is to store data in an organized
and structured manner so that it can help an organization in achieving main aim and objectives of
an organization. Database tables helps an organization to extract required data for decision
making or any other purpose. It also helps an organization to identify any kind of mistakes or
issues organization is facing in any of their function or any other process of their business. In this
database there are four main tables that have been created.
Data types of table fields
Each table had various fields and each field had different data type as per the data stored
in it. There are various kinds of data types but in these tables only few of them have been used
which were appropriate for them as per their populated data.
Number: It is a kind of data type which is mostly used for fields that contains only numbers. Data
stored in such field can be up to 8 bytes.
7

Short text: It is a kind of data type which is mostly used for fields that consist of text but these
can also consist of numbers and characters as well. Data stored in such field can be up to 255
characters (Johnson, Near and Song, 2018).
Long text: It is a kind of data type which is mostly used for fields that consist of text but these
can also consist of numbers and characters as well. Data stored in such field can be up to 63,999
characters.
 First table is course table which consist of all the details related to course i.e. course id,
course name and course duration. This table consist of five rows that consist of details of five
different courses.
Figure 10 Course Table
 Second table is department table which consist of all the details related to department i.e.
department id, department name and head of the department. This table consist of six rows
that consist of details of six different department.
Figure 11 Department table
 Third table is Student table which consist of all the details related to students i.e. Student id,
Student name and Student phone number, student address, course id and department id. This
table consist of seven rows that consist of details of five different courses.
8

Figure 12 Student table
 Last table is faculty table which consist of all the details related to faculty i.e. faculty id,
faculty name, faculty phone number, faculty address, department id, designation and course
id. This table consist of five rows that consist of details of five different courses.
Figure 13 Faculty Table
Queries
SQL queries is also known as structured query language which is used in database and can
manipulate table data. It is one of the most suitable language that can be used in database in
order to perform various kinds of operations such as insertion, deletion and modification of data
in various manner (Bechtel and et.al., 2015). These queries are designed as per the understanding
and desired results of the user. In this database as well to different types of queries have been
used. First is to display data of department table and second is to display all the details of
students who are studying courses 101 i.e. B.Tech.
 Query 1:
SQL query:
SELECT Student.Student_ID, Student.Student_name, Course.Course_id,
department.Department_id
FROM department INNER JOIN (Course INNER JOIN Student ON Course.Course_id =
Student.Course_id) ON department.Department_id = Student.Department_id
9

WHERE (((Course.Course_id)=101));
Figure 14 Datasheet view of Query 1
 Query 2:
SQL query:
SELECT department.Department_id, department.Department_name,
department.Head_of_department
FROM department;
Figure 15 Data Sheet view of Query 2
Database forms
Forms in Microsoft Access can be defined as a kind of database object that can be used to
create a user interface for database applications (Saran and et.al., 2017). It directly connects with
the data source such as queries or tables that can be used to edit, display or enter data either from
or to data source. For this database a form has been created that can be used for inserting new
information into department table. It consists of three text boxes where details can be filled, and
two buttons. One button can be used for closing the form and second button can be used for
inserting values in the department table. Any kind of data inserted in this form will directly get
saved into department table.
10