3.19
1. Missing "Area_code" field
2. I would store them in the "Local_phone" and "Cell_phone" attributes
3. Advangatges of splitting name field:
a. If someone changes their last name it is easy fo find
b. Easy to sort by first letter of any of their (First, Middle or Last) names
Disadvangatges of splitting name field:
c. More storage space is being filled up
d. More work on the database managers side
4. If it will make things faster or easier in the future seperate them
5. Two different designs if a student can have between 0-5 phones:
a. Have 5 fields with nulls in them, until filled with a phone number
b. Have one field with all the phone numbers seperated by a delimiting character
4.12
1. Retrieve the names of all senior students majoring in ‘CS’ (computer science).
a. SELECT Name FROM STUDENT WHERE Class = 4 AND Major = 'CS';
2. Retrieve the names of all courses taught by Professor King in 2007 and 2008
a. SELECT Course.Course_name
FROM section s JOIN course c ON s.Course_number = c.Course_number
WHERE s.Instructor = "King" AND (s.Year = "08" OR s.Year = "07");
3. For each section taught by Professor King, retrieve the course number, semester, year, and number of students who took the section.
a. SELECT s.Course_number, s.Semester, s.Year, COUNT(g.Student_number) AS Num_Students
FROM section s JOIN grade_report g ON s.Section_identifier = g.Section_identifier
WHERE s.Instructor = "King"
4. Retrieve the name and transcript of each senior student (Class = 4) majoring in CS. A transcript includes course name, course number, credit hours, semester, year, and grade for each course completed by the student.
a. SELECT st.name, c.Course_name, c.Course_number, c.Credit_hours, s.Semester, s.Year, g.Grade
FROM student st, course c, grade_report g, section s
WHERE st.Student_number = g.Student_number
AND g.Section_identifier = s.Section_identifier
AND s.Course_number = c.Course_number
AND st.Class = "4";