sql

I308 Team Final Project

Week 12

This is the week when you really need to dive in and start building your team’s Student Records System. There’s a lot to do, and it is probably a good idea to consider how your team is going to divide up the work. Your goals for this week should concentrate on the following:

· Implement your schema by building the tables in MySQL, setting up the necessary primary keys and foreign keys. You will need to decide on the appropriate data type and size for each column in each table.

· Generate data to populate the tables. Students often ask how much data is needed. The following are the basic minimums:

· Item	· Minimum
· Students	· 15
· Faculty	· 10
· Majors	· 4
· Semesters	· 4
· Buildings	· 3
· Classrooms	· 8
· Offices	· 10
· Departments	· 3
· Courses	· 20
· Sections per Semester	· 12
· Students enrolled in Sections	· Lots!

Some Hints:

· Write your CREATE and INSERT statements in a script that you can correct and rerun as and when necessary. This is especially useful if you find yourself tangled up in foreign key problems. It may sound drastic, but you can start your script with a series of DROP TABLE statements to clear out everything before rebuilding the tables correctly.

· Be careful to create the tables and insert the data in the right order. Specifically, parent tables need to be created before child tables. For example, you cannot enroll a student in a section of a course until you have created the student, the course, and the section.

· Remember that we are not trying to create a huge university like IU. Keep things small enough so that they can be tested. For example, when setting graduation requirements for the majors, require something like 15 or 20 hours, not 120. (You won’t have any students who have taken 120 hours to test your system!)

· Even so, to make it somewhat realistic, you will need to generate a lot of student enrolments in sections—certainly in the hundreds.

· You will need to generate random data using Mockaroo or something similar, at least for the larger tables. As well as this random data, you will need to make sure that you include special test cases that demonstrate your queries work, and you will be asked to provide these examples when your project is graded.

· The system must be created in your team database. It is a good idea to adopt a naming convention for your tables in the project to distinguish them from other tables in the team account. For example, FP_STUDENT (Final Project).

Your assignment this week is as follows:

· Create all the tables for the Student Records System and insert all the data if possible. At least make sure you can go ahead with writing and testing the SELECT queries.

· Choose the queries you plan to develop and submit based on the list below. You are not required to stick with your preliminary selection, and it is a good idea to work on a couple of “spare” queries so that you will have enough to submit in case something goes wrong at the last minute.

· Complete the Week 12 Progress Report and submit it on Canvas. This progress report requires you to provide a plan of action for how you will proceed.

Required Queries

You must select 50 points worth of queries from the following list with the following restrictions:

· You must include at least one “b” and one “c” query.

· You may not select more than one query from the same number group.

· You must choose at least two queries where the results are based on user selection of a value (using dropdowns in PHP). These values are marked with asterisks in the query description. (Any query selected that has asterisks must have a dropdown.)

1a Produce a roster for a *specified section* sorted by student’s last name, first name (5 points)

1b Produce a class roster for a *specified section*. At the end, include the average grade (GPA for the class.) (10 points)

2a Produce a list of rooms that are equipped with *some feature*—e.g., “wired instructor station”. (5 points)

2b Produce a list of rooms that are equipped with *some feature*—e.g., “wired instructor station”—and how many sections have taken place in each. (10 points)

3a Produce a list of all faculty and all the courses they have ever taught. Show how many times they have taught each course. (5 points)

3b Produce a list of faculty that includes their name, rank, the department they belong to, and the name of the chair of that department. Be sure to include any faculty that are not assigned to a department (and make sure your data includes such an example). (10 points)

3c Produce a list of faculty who have never taught a *specified course*. (15 points)

4a Produce a list of students who are eligible to register for a *specified course* that has a prerequisite. (5 points)

4b Produce a list of students who are eligible to register for a *specified course* that has a prerequisite, but do not include those students who have already taken the course that has the prerequisite. (10 points)

4c Produce a list of all students who took a course that had a prerequisite but the student had not taken the prerequisite. Include the semester, the course subject and number, and the grade the student received. (15 points)

5a Produce a chronological list (transcript-like) of all courses taken by a *specified student*. Show grades earned. (5 points)

5b Produce a chronological list of all courses taken by a *specified student*. Show grades earned. Include overall hours taken and GPA at the end. (10 points)

5c Produce a chronological list of all courses taken by a *specified student*. Show grades earned. Include overall hours earned and GPA at the end. (Hint: An F does not earn hours.) (15 points)

6b Produce a list of students and faculty who currently have a class in a *specified building*. (10 points)

6c Produce a list of students and faculty who currently have a class in *specified building*. Also include in the list faculty who have offices in that building. (15 points)

7a Identify the building that has the most classroom seats. (5 points)

8b Produce an alphabetical list of students who have not attended during the two most recent semesters along with their parents’ phone number. (10 points)

9a Produce a list of majors offered, along with the department that offers them and their requirements to graduate (hours earned and overall GPA). (5 points)

9b Produce a list of students with hours earned who have met graduation requirements for a *specified major*. (10 points)

9c Produce a list of students with hours earned and overall GPA who have met the graduation requirements for any major. Sort by major and then by student. (15 points)