LIKE THE BLOG? DID MY COURSEWORK HELP YOU?

Feel free to donate to support me and be able to host this site as a free educational tool for everyone to use.

Any amount is appreciated and is not required!

Unit 18 (P3, P4, P5, P6, M2, M3, M4): Creating a Relational Database

Create and Populate a Database

Create and populate a database (P3)

I created a new desktop database with the name 'ITSupport.accdb'


I created each table and populated it within the database. Each table that I created followed my design that I did before creating the database. The table name, field name, data type and field size has been correctly inputted.


Below shows that I have correctly inputted the validation rule for gender that I specified in my design.


If I do not put 'Male' or 'Female' value in the field then it will come up with this message:


I put >"IT000" as the validation rule for the 'DepartmentCode' as I want all values to start with ‘IT’ and end with 3 numbers after that.


After I created the tables in the database, I then would need to make relationships with them as described in my design. This is an example of one of the relationships (This one has been referential integrity enforced and has cascade update and delete options ticked):


This is an overview of all the relationships:

Creating Features in Data Entry Forms

Create features in data entry forms to ensure validity and integrity of data (P4)

Forms allow users to easily manage data in the database without directly messing with the database itself. Sometimes you do not want employees in your company to edit the database manually, thus, making forms will reduce the complexity and the chance that the database produce errors.


Each table will have a form to easily manage the data in it. I already designed the forms for each table and now I am going to create the forms in the database. To do this, I clicked on the create tab at the top of the database then I clicked on form.


From here, a form is automatically created, but it is different to my initial design. This means that I would need to change it so it matches my initial design idea:


Old


New

The buttons below the form are added to give it extra simplicity to the design and easy use for any user who is adding/removing information in the database.


To ensure integrity of the database and to make sure that the data is accurate, I added in combo boxes to the form which only allows the user to enter a certain value such as ‘Male’ or ‘Female’:

This insures that no other value apart from the values in the combo box is entered which means that the database will not produce errors as the values will be correct and accurate.


In addition to this, I also set the DateCreated field in the TicketT table to automatically input the current date to pre-populate new tickets:


I did this by making an input mask to '"DD"/"MM"/"YYYY";;_' which automatically sets the date in this format (I also did this with the 'DateCompleted' field too). I then set the default value of 'Date()' which means that the current date will be placed into this field when a new ticket is created. This insures the accuracy of the data inputted into the table as you can only input data in a certain format.



Database Queries

Perform queries using multiple tables and multiple criteria (P5)

Queries allow you to filter your database for a specific need. If you want to find something, then a query would be the best way to get to the information you are looking for.


To make a query; you click on the create tab at the top of the document and then click Query Design to create a query from scratch. Add the tables you need from the show table menu into the query. You can then define the criteria of your query in the section at the bottom of the document.


Query 1

All female clients who work for the company IT4U (ID of 3).



Query 2

All tickets that have been made by the company PCFixer (ID of 4) that still needs completion.