Project: CIS Course Schema¶
This project will help you become familiar with structuring data into tables and lists. The goal of the project is to get you thinking about the data and metadata that’s necessary to organize information.
You will use the data from the Cabrillo Spring 2020 course schedule:
Your database should contain all of the classes in the CIS department. You don’t have to enter classes from other departments. You will also need to get information about all of the CIS instructors from Cabrillo’s SALSA directory:
Creating your Schema¶
You should have already completed the Your First Schema lab. The lab shows you how to create tables in MySQL. I also did a walkthrough in class.
Start with Metadata¶
You should capture as much information as possible from the the schedule and SALSA. What items of data do you want to capture? How will you name those data items? Some answers are easier than others and you have the flexibility to decide for yourself. For example you could represent in a table like this:
|CIS||54||1||Introduction to Databases|
Or you could do this:
|CIS-54||1||Introduction to Databases|
Using your metadata as columns, create two tables in MySQL. One for courses and one for instructors.
Choose a Key¶
Your tables must have a key. Choose one of the columns in your schema to be the key for the table. In MySQL Workbench you will select the “PK” checkbox for your key.
IMPORTANT: Two rows cannot have the same primary key. Choose your key carefully!
Enter the Data¶
With your tables created go through all the courses and enter the data. Double check your information to be sure the data is complete. The purpose of this project is not data entry but having all the data is essential.
Export Your Schema¶
Export your schema into an SQL file called
cis_courses.sql. You will submit the file with this week’s assignments.
Answer the following questions about your schema:
What are the related columns between your two tables?
What are the keys in your data?
Name one modification problem your schema might have and why.
Answer the questions in a file named
cis_courses_answers. The file can be any document format.
Submit your files on Canvas.