In this video, we aim to understand the structure of our database and add relevant fields to better keep track of enrollments and registrations.

We also examine relationships and how they become useful to us; in this case, performing a cascade delete.

Homework:

Add data to your database to represent at least 25 students and 15 subjects. Ensure that all students are at least enrolled in 6 subjects.

© 2021  Vedesh Kungebeharry. All rights reserved. 

Follow along with the video using the provided files and tutorial.

The steps in the video can be found in the tutorial and files below:

Files

https://drive.google.com/drive/folders/1gs2GYfnGU7IaexgjjcMEccklvb_KG6iQ?usp=sharing

Tutorial

In this tutorial , we wish to import the following data from RegionData.xlsx:

NOTE: If you don’t have the provided excel file, you can always copy and paste the table above into a new excel document, save it, and use it for this tutorial.

1. Add a new table named Region. The design View Is shown Below:
   

Close the table after creation.

2. “RegionData.xlsx” contains data formatted to match the region and population size of our Region table. The column names are the same in both tables. Start the process to Import the data from “RegionData.xlsx”:

a) Select the  “External Data” tab

b) Choose “New Data Source”

c) Hover over “From File”

d) Choose Excel

3. Use the wizard:

a) Browse to and select “RegionData.xlsx”

b) Choose “Append a copy…”

c) Select the region table from the dropdown

4. Choose Next:

5. Choose Finish

6. Choose Close:

7. Done! Open your table to verify that the data was imported:

© 2021  Vedesh Kungebeharry. All rights reserved. 

This post is a continuation of :

1. Introduction To Databases and
2. Database Terminology in Context

See the video tutorial below on creating forms in Access.

© 2021  Vedesh Kungebeharry. All rights reserved. 

In this post, we observe our first database created in our last tutorial and learn the terminology associated with it. See the video below and the notes that follow:

Terminology Summary

Terminology With examples and explanation

Record – A single row in a database table. (also known as tuple). A record consists of 2 or more fields. E.g, a single row in our subject table.

Field – A single item of data in a record. It can be thought of as a  column in a table . (also known as an attribute) E.g  “First Name” in our student Table

Data type – The type of data that is allowed to be saved in a field. The database management system (DBMS) only allows the specified datatype for each field.  For example, The DBMS would not allow text data to be saved in a field that has a data type of “Date/Time”.

Primary Key  – A field that uniquely identifies each row in a table or entity. It is always present for each row, i.e It is never allowed to be blank or empty. E.g Student ID

Foreign key – This is a field used to store primary key data from another table. This allows for tables to be related. E.g Student ID in our enrolment table.

Key (or Key field) – Either a primary key or foreign key. Primary keys and foreign keys are called keys because they can be used to represent a record in a table. E.g any primary or secondary key found in the database tutorial.

Composite Key – when two or more keys are combined to form another. E.g (Student ID, Subject Code) from our Enrolment Table.

Composite Primary Key  – When 2 Keys are combined to form a primary key. For example, in our student table, we could use a students last name, first name and DOB combined to be the primary key for our table .(This is assuming that we never encounter two students with the same names and date of birth!)

Candidate Key  – A key or composite key that can be reasonably assumed to uniquely identify a row in a table. E.g (First Name, Last Name, DOB) in the student table.

© 2021  Vedesh Kungebeharry. All rights reserved.