Introduction to Databases
CMPT 110
Course Description
This course introduces students to the basics of using a database to store data. Microsoft Access is used as the interface to access the database. The course mainly introduces the main concepts of database management and using access, but also takes a brief look at some more advanced concepts like SQL.
Artifact Description
This artifact is the final project of the course. For the project, we needed to create a database that contained the following:
For this project, I chose to catalog each episode and season of South Park. For each episode, I stored the name, original air date, writers, and a screenshot. For the seasons, I stored the season number, total number of episodes, and years.
When you open the database, you are presented with the Main Switchboard. A switchboard is simply a menu you can create to provide simple navigation through the database. The main switchboard has 4 buttons, one to view tables, one for forms, one for reports, and one to quit Access. Clicking to view tables, forms, or reports will show another menu.
The Tables Switchboard shows 3 options: view the Episodes table, view the Seasons Table, and Return to Main Switchboard.
The Episodes Table
The Seasons Table
The Forms switchboard has 3 options: View the Episodes Form, view the Seasons Form, and return to Main Switchboard.
The Episode Form shows each field of the Episode table, and creates a nice interface to add, edit, or view information about an episode.
The Seasons Form shows the 3 fields from the Seasons table, and shows each episode in the season. This is possible because I setup a relationship between the tables. You can see the relationship I setup at the end.
The Reports Switchboard has 3 options: view the Episodes Report, view the Episodes Report with Screenshots, and return to Main Switchboard.
The Episodes Report displays a list of each episode and its characteristics, grouped by season.
The Episodes Report with Screenshots is the same as the other report, except that this one also shows a screenshot from each episode, so it takes up more room.
As you can see in the screenshot below, I have 3 tables. The Writers table was automatically created and maintained by Access. Because each episode had multiple writers, and there were relatively few writers who would write some episodes but not others, I created a checklist for the writers field in the Episodes table. Access then created the Writers table and automatically setup a relationship.
Since there are many seasons, each containing multiple episodes, I setup a one-to-many relationship between the Season field in the Episodes table and the Season field in the Seasons table. One-to-many means that each value will only occur once in the Seasons table, but can occur multiple times in the Episodes table. For example, there is only 1 season 2, but there are 18 episodes in season 2. Therefore, the value "2" will only occur once in the Season field in the Seasons table, but will occur 18 times in the Season field in the Episodes table. Because I have set up this relationship, I am able to create a form that shows each season, and automatically shows each episode from that season.
- At least 2 tables
- At least 2 forms
- At least 2 reports
- A switchboard (menu) to access each element of the database
- At least one relationship between 2 fields in different tables
Download the Final Project
If you have Access 2007 or higher installed, you may download this database above. I have provided screenshots of the project below.For this project, I chose to catalog each episode and season of South Park. For each episode, I stored the name, original air date, writers, and a screenshot. For the seasons, I stored the season number, total number of episodes, and years.
When you open the database, you are presented with the Main Switchboard. A switchboard is simply a menu you can create to provide simple navigation through the database. The main switchboard has 4 buttons, one to view tables, one for forms, one for reports, and one to quit Access. Clicking to view tables, forms, or reports will show another menu.
The Tables Switchboard shows 3 options: view the Episodes table, view the Seasons Table, and Return to Main Switchboard.
The Episodes Table
The Seasons Table
The Forms switchboard has 3 options: View the Episodes Form, view the Seasons Form, and return to Main Switchboard.
The Episode Form shows each field of the Episode table, and creates a nice interface to add, edit, or view information about an episode.
The Seasons Form shows the 3 fields from the Seasons table, and shows each episode in the season. This is possible because I setup a relationship between the tables. You can see the relationship I setup at the end.
The Reports Switchboard has 3 options: view the Episodes Report, view the Episodes Report with Screenshots, and return to Main Switchboard.
The Episodes Report displays a list of each episode and its characteristics, grouped by season.
The Episodes Report with Screenshots is the same as the other report, except that this one also shows a screenshot from each episode, so it takes up more room.
As you can see in the screenshot below, I have 3 tables. The Writers table was automatically created and maintained by Access. Because each episode had multiple writers, and there were relatively few writers who would write some episodes but not others, I created a checklist for the writers field in the Episodes table. Access then created the Writers table and automatically setup a relationship.
Since there are many seasons, each containing multiple episodes, I setup a one-to-many relationship between the Season field in the Episodes table and the Season field in the Seasons table. One-to-many means that each value will only occur once in the Seasons table, but can occur multiple times in the Episodes table. For example, there is only 1 season 2, but there are 18 episodes in season 2. Therefore, the value "2" will only occur once in the Season field in the Seasons table, but will occur 18 times in the Season field in the Episodes table. Because I have set up this relationship, I am able to create a form that shows each season, and automatically shows each episode from that season.
Grades
| Assignment Grade | Course Grade |
|---|---|
| A | A |
Course Reflection
Prior to taking this course, I had taken a Computer Applications course in high school which covered Microsoft Office 2000 (including Access). The year before I also took CMPT 101, an intoduction to Microsoft Office 2007 which did not cover Access. While I had seen most of the things learned in the course before, they were things that I had rarely used so I felt like I was learning them for the first time. I don't know how helpful the Access-specific concepts will be, because I don't think many companies use Access to store their databases. However, the fundamentals of designing and maintaining the database are very important and apply to any database regardless of the software, and I feel that that is an important skill.
