University Systems Database Documentation

Mirpur University of Science &Technology,(MUST)

DEPARTMENT: Software Engineering

SUBJECT: Information & Communication Technology

SUBMITED BY: KUBRA ZAFAR

SUBMITED TO: Engr.Umair

ROLL No: FA24-BSE-037

SECTION: A
SESSION: 2024

1
 University Systems Database Documentation

Table of Contents
1. Create a database ................................................................................................................................ 5
2. Creating Tables ................................................................................................................................... 5
...................................................................................................................................................................... 7
...................................................................................................................................................................... 7
...................................................................................................................................................................... 7
3. Create Relationships ............................................................................................................................... 7
4. Create Quires .......................................................................................................................................... 9
4.1 Database System Query .................................................................................................................... 9
4.2 CourseEnrollement Count Query.................................................................................................. 10
.................................................................................................................................................................... 10
.................................................................................................................................................................... 10
5. Create Form .......................................................................................................................................... 11
6. Create Report ........................................................................................................................................ 18

2
 University Systems Database Documentation

List of Figures:
Figure 1 Create database ............................................................................................................................... 5
Figure 2 Save database................................................................................................................................. 5
Figure 3 Default table ................................................................................................................................... 5
Figure 4 Save table........................................................................................................................................ 5
Figure 5 Add Fields in table.......................................................................................................................... 6
Figure 6 Adding Data in Datasheet View ..................................................................................................... 6
Figure 7 Design view of Courses table ......................................................................................................... 6
Figure 8 Datasheet View of courses ............................................................................................................. 7
Figure 9 Design View of Enrollments .......................................................................................................... 7
Figure 10 Datasheet View of Enrollment ..................................................................................................... 7
Figure 11 Relationship tab ............................................................................................................................ 8
Figure 12 Selection of table for relationships ............................................................................................... 8
Figure 13 Relationships ................................................................................................................................ 8
Figure 14 Query Design ................................................................................................................................ 9
Figure 15 Dialog box for query design ......................................................................................................... 9
Figure 16 Query Design and set criteria ....................................................................................................... 9
Figure 17 DatabaseSystems_A_Grade query................................................................................................ 9
Figure 18 Dialog box of query 2 ................................................................................................................. 10
Figure 19 Designing and setting of query 2 ................................................................................................ 10
Figure 20 CourseEnrollementCount query ................................................................................................. 10
Figure 21 Form Wizard ............................................................................................................................... 11
Figure 22 Table selection for form ............................................................................................................. 11
Figure 23 Selection of one field .................................................................................................................. 11
Figure 24 Selection of all fields in form ..................................................................................................... 12
Figure 25 Form Layout ............................................................................................................................... 12
Figure 26 Save Form name ......................................................................................................................... 12
Figure 27 Form View .................................................................................................................................. 13
Figure 28 Design view of form ................................................................................................................... 13
Figure 29 Insert button from Control group................................................................................................ 13
Figure 30 Command operations .................................................................................................................. 14
Figure 31 Command Layout ....................................................................................................................... 14
Figure 32 Name for command .................................................................................................................... 14
Figure 33 Button created ............................................................................................................................. 15
Figure 34 Combo box wizard...................................................................................................................... 15
Figure 35 Select table.................................................................................................................................. 15
Figure 36 Select field .................................................................................................................................. 16
Figure 37 Sorting ........................................................................................................................................ 16
Figure 38 Adjust column width .................................................................................................................. 16
Figure 39 Select condition for dropdown ................................................................................................... 17
Figure 40 Label ........................................................................................................................................... 17
Figure 41 Design view of Enrollments ....................................................................................................... 17

3
 University Systems Database Documentation

Figure 42 Form View .................................................................................................................................. 18

Figure 43 Query Design for report .............................................................................................................. 18
Figure 44 Query View ................................................................................................................................ 18
Figure 45 Report Wizard ............................................................................................................................ 19
Figure 46 Select Table and Fields ............................................................................................................... 19
Figure 47 View data .................................................................................................................................... 19
Figure 48 Grouping ..................................................................................................................................... 19
Figure 49 Sorting ........................................................................................................................................ 20
Figure 50 Summary options ........................................................................................................................ 20
Figure 51 Layout ......................................................................................................................................... 20
Figure 52 Title to Report............................................................................................................................. 21

4
 University Systems Database Documentation

University Systems Database

1. Create a database
 Open MS Access.
 Display the Getting Start with Access.
 Create a blank database.
 Then by pressing Create button.
 Save the database as University Systems Database.

Figure 1 Create database

Figure 2 Save database

2. Creating Tables
 The new database opens with one table named as table1
By default.
 Right Click on Table 1 and Select Save from menu. Give Students name and click OK.

Figure 4 Save TABLE with

Name

Figure 3 Default Table

Figure 3 Default table

Figure 4 Save table

5
 University Systems Database Documentation

 Adding fields in Design view and types.

Figure 5 Add Fields in table

 After inserting field go to datasheet view, and insert data about fields in datasheet view.
 Go to Home, Click on View and select Datasheet View.

Figure 6 Adding Data in Datasheet View

 After adding data, save table.

 Follow these steps and create courses and Enrollments table.

Figure 7 Design view of Courses table

6
 University Systems Database Documentation

Figure 8 Datasheet View of courses

 Create Enrollment table following the above steps.

Figure 9 Design View of Enrollments

Figure 10 Datasheet View of

Enrollment
3. Create Relationships
 In students table, student Id is the foreign key in Enrollment table.
 In Courses table, Course Id is the foreign key in Enrollment table.
 To make relationship, go to database tools and select relationship.

7
 University Systems Database Documentation

Figure 11 Relationship tab

 After selecting relationship tab, a dialog box is opened, add all tables and close.
 Tables are displayed.

Figure 12 Selection of table for relationships

 Drag Student ID field of Student table on Student ID field of Enrollments table.

 Drag Course ID field of Course table on Student ID field of Enrollments table
 A dialog box is open of name edit relationships.
 Click on Create button.
 Relationships created.

Figure 13 Relationships

8
 University Systems Database Documentation

4. Create Quires
4.1 Database System Query
 Go to create and click on query design.

Figure 14 Query Design

 A dialog box is shown, select tables.

Figure 15 Dialog box

for query design
 Select tables and Join tables using “Student ID” and “Course ID”.
 Display name from student table, Couse name from course table, and grade from enrollments
table.
 Set criteria for Course name is “Database Systems” and for Grade is “A”.

Figure 16 Query Design and set

criteria
 Run the query and save as “DatabaseSystems_A_Grade”.

Figure 17 DatabaseSystems_A_Grade query

9
 University Systems Database Documentation

4.2 CourseEnrollement Count Query

 Go to create and click on query design.
 Add courses and enrollments table.

Figure 18 Dialog box of query 2

 Click on course ID and click Totals (Σ icon).

 Display Course name and enrollment ID.

 Set course name to group by and Enrollment ID to sum in totals.

Figure 19 Designing and setting of query 2

 Run and save as CourseEnrollementCount.

Figure 20 CourseEnrollementCount query

10
 University Systems Database Documentation

5. Create Form
 Click on create ribbon and choose Form wizard tab.

Figure 21 Form Wizard

 Select table from Table dropdown menu and then select the require fields. Click Next.

Figure 22 Table selection for form

 When fields selection required, Click on single angle symbol”>”.

Figure 23 Selection of one field

 If all fields are required in form.

 For selecting all fields once click on Double angle sign”<<”.
 Click on Next.

11
 University Systems Database Documentation

Figure 24 Selection of all fields in form

 Select the layout of form. Click Next.

Figure 25 Form Layout

 Give Title to form and then click Finish.

Figure 26 Save Form name

 Newly created form with same source name appear on screen.

12
 University Systems Database Documentation

Figure 27 Form View

 To add buttons, go to Design view form and click the Button commands in the control
groups on the ribbon.

Figure 28 Design view of form

Figure 29 Insert button from Control group

 Select button and drag it on place where you want to insert it.
 Command Button Wizard open, Select the type of command from the categories list.
 Select the specific action the command button to perform from the actions list.
 Click Next.

13
 University Systems Database Documentation

Figure 30 Command operations

 In the next step, if you wants text to appear on the button, enter it in the text box.
 If you wants a picture to appear on the button, select one or using the Browse button.
 Click Next.

Figure 31 Command Layout

 Give the button meaningful name.

 Click Finish, the command button appears.

Figure 32 Name for command

14
 University Systems Database Documentation

 Button appears in form.

Figure 33 Button created

 For dropdown list on fields, insert combo box.

 For combo button, go to control group on the ribbon.
 Select combo box and select relevant field.

Figure 34 Combo box wizard

Figure 35 Select table

15
 University Systems Database Documentation

 Select table, and fields and click on Next.

 Select combo box for student ID and Course ID.

Figure 36 Select field

 Select layout and click on Next.

Figure 37 Sorting

 Adjust width of Column.

 Click Next.

Figure 38 Adjust column width

16
 University Systems Database Documentation

 Select the condition click on Next.

Figure 39 Select condition for

dropdown
 Name the field and click on Finish.

Figure 40 Label

 View the Form and go to, form view.

Figure 41 Design view of Enrollments

17
 University Systems Database Documentation

 From button add new data and refresh all, all data will be automatically saved in
tables

Figure 42 Form View

6. Create Report
 Create a query for report.
 Go to create and select query design.
 Add tables and select fields.

Figure 43 Query Design for report

 Run the query and save as “Students Courses Query”.

Figure 44 Query View

18
 University Systems Database Documentation

 After creating query, go to “Create” and click on Report Wizard.

Figure 45 Report Wizard

 Select StudentCourse query.

 Select all fields and click on Next.

Figure 46 Select Table and Fields

 View your data and click on Next.

 Grouping by Student name. Figure 47 View data

Figure 48 Grouping
19
 University Systems Database Documentation

 Sort by Course name and click on summary options.

Figure 49 Sorting
 Select sum option and click on Next.

Figure 50 Summary options

 Select Layout stepped.

Figure 51 Layout
 Title name “Student Course Report”.

20
 University Systems Database Documentation

 Click on Next.

Figure 52 Title to Report

 Report Created.

21