I’m trying to learn for my MySQL class and I’m stuck. Can you help?
Columbus State maintains a system for the displaying of the schedule of classes for a given semester. You can see the existing system on the CSCC website. (Link to schedule page here.)
There are two screens involved with this system on the CSCI website.
The following screen allows you to select the course subject for which you wish to see the schedule:
In the following screen, we have selected the Computer Science Department, and can see the list of classes for the subject.For this assignment, you have the data for both the Computer Science and Info. Tech. Support Technician courses.
Obviously, this web page relies on a backend database. For this exam you are given the schedule of classes for two departments for a semester as a CSV (comma separated value) file.
Your work is to prepare a report detailing how you would design a database system to accommodate the data you see in the list.
This assignment requires you design (not create and not implement) a database based on the data provided you. You have no information other than what is contained in the data file. You must understand that data for yourself, and build your design around that understanding.
The following is the minimum requirements for the design of your database. You can add additional features and functionality to your design, but this serves as the minimum requirements:
- Accommodate unlimited classes and departments. The data with which you have been provided contains the class listings for only two departments, but a well-functioning system should be able to handle any number of classes across any number of departments.
- Ability to track instructors. The new database must be capable of tracking instructors allowing for the creation of reports detailing which class or classes have been assigned to a single instructor.
- Handle multiple instructors per class. While it is common to list only one instructor per class this system must be capable of assigning more than one instructor to an individual class.
- Handle course section reporting. The new database should have the ability to easily report each course (like CSCI-1320) and report all the sections of this particular class that are running, including such information as buildings, rooms, instructors, days, and times.
- Handle classroom reporting. The new database should have the ability to easily report on individual classrooms detailing the classes that will be taught in a physical space and on what days/times.
You will write a report as a Word document that exhibits all the following:
- Write a well-formed paragraph that demonstrates your knowledge of the data you have received. What do you see in the data that raises questions that should be answered? Are there any issues with the data itself (specific columns) that you wish would be formatted differently, if possible? Are there any columns where the data does not make sense or where the data should be presented differently than what it currently is? Are there any columns of data that are not needed? Which ones and why? This paragraph should demonstrate that you really understand the data contained in the list and should define the meaning of CSV or flat-file.
- Ideally, you would want to gather additional information about this list and the school’s specific needs around the database. Of course, this assignment doesn’t provide you this luxury. But, if it did what would you want to know? Formulate a list of 10 questions that you would want to ask in interviews and identify the people with whom you would want to conduct such an interview. Refer to chapter 2 from Harrington on requirements analysis. When identifying the people whom you would interview, identify the college positions (not necessarily the names) of those whom might have the information you need.
- Choose one other method of requirements gathering (observation, questionnaires, focus groups, and brainstorming sessions), also from chapter 2 of Harrington, that you would conduct. After identifying one of the above four methods write a well-formed paragraph in which you provide the design for the methodology you have chosen: who will be involved, what questions will be asked, what you hope to gain from this particular requirement gathering session.
- Create a graphical design (refer to chapter 4 from Harrington) using Draw.IO of your database design. You need to design the database to meet the minimum requirements as listed above. Your design must include the tables, their attributes (columns), the primary key for each, and the relationships between tables (refer to chapter 5 from Harrington). Make sure to export the image from Draw.IO and insert it into your Word document.
Important: Make sure you read the above steps carefully. When you are finished with all steps above—submit only your Word document to Blackboard
Important: The above image does not capture all the rows or columns. You will have to use a text editor like Notepad or Notepad++ to open this file.
Notepad++ is an excellent upgrade from the standard Notepad text editor. It’s free and open source. And it’s a tool you should have in your toolbelt. (Link to Notepad++ website and downloads.)
The above Example Data is taken from a CSV file to which you have access.