• courseId is a unique identifier for a course, e.g. CS122 is the id for the course "Using Relational Databases and SQL", and no other course has the same id.
  
• sectionNumer is not a unique identifier for a section. It is only used to distinguish multiple sections of the same course offered in the same semester. For example, we may have two CS122 sections with sectionNumber 1 and 2, respectively.
• units for a course does not change. For example, CS122 is a 4-unit course, no matter when it is offered.
• You may add addtional assumptions about the data, as long as your assumptions are clearly stated and reasonable.
  

Assumption A. a student can repeat a course (in a different semester, of course)

Primary Key: (studentID, courseID, semester, year)

Partial Dependencies:

• (studentID) -> (lastName, firstName)
• (courseID) -> (courseTitle, units)

Transitive Dependencies:

None.

The only possible transitive dependency is (courseTitle) -> (units), and whether it is indeed one depends on the answer to the question "can two courses have the same title but different units?". My answer to this question is yes, for example, we may have two different courses both called "Introduction to Programming", while one of them is for CS majors and 4 units, and the other one is for non-CS majors and 2 units. Therefore, units does not transitively depend on courseTitle.

Note that if you do assume that courseTitle is unique and (couseTitle) -> (unit), then you must also have (courseTitle) -> (courseID), as well as (studentID, courseTitle, semester, year) being another key.

Assumption B. a student cannot repeat a course

Primary Key: (studentID, courseID)

Partial Dependencies:

• (studentID) -> (lastName, firstName)
• (couseID) -> (courseTitle, units)
  

Transitive Dependencies:

None.

Interestingly enough, in both cases we'll end up with the same tables:

Students: <studentID, lastName, firstName>, primary key (studentID)
Courses: <courseID, courseTitle, units>, primary key (courseID)
Grades: <studentID, courseID, sectionNumber, semester, year, meetingTime, meetingRoom, grade>

• primary key (studentID, courseID, semester, year) -- with Assumption A
• primary key (studentID, courseID) -- with Assumption B
  

Since there are no transitive dependencies, the 2NF tables are already 3NF. On the other hand, we do note that meetingTime and meetingRoom seem to be out of place in the Grades table, and indeed they are, because we have another dependency:

(courseID, sectionNumber, semester, year) -> (meetingtime, meetingRoom)

By definition this dependency is neither a partial dependency nor a transitive dependency, so although intuitively the Grades table is not properly designed, it is still 3NF, which is a good example showing the need for 4NF.

Omitted.

SELECT g.Genre FROM Genre g WHERE NOT EXISTS (SELECT 1 FROM Titles t WHERE t.Genre = g.Genre);

SELECT studioName as [name], webAddress FROM Studios WHERE webAddress IS NOT NULL AND LEN(TRIM(webAddress)) <> 0;
UNION
SELECT artistName, webAddress FROM Artists WHERE webAddress IS NOT NULL AND LEN(TRIM(webAddress)) <> 0;

The difficulty of this problem is not coming up with a query, but the fact that MS Access does not support nesting LEFT JOIN with INNER JOIN, e.g. you'll get a syntax error if you replace the first "INNER JOIN" in the following query with "LEFT JOIN". A walkaround for this is to use  nested INNER JOINs to get the genres that have recorded tracks, and union the results with the genres that do not.

SELECT g.Genre, SUM(t2.LengthSeconds)/60 AS [Recorded Length in Minutes] FROM Genre g INNER JOIN (Titles t1 INNER JOIN Tracks t2 ON t1.TitleID = t2.TitleID)  ON g.Genre = t1.Genre GROUP BY g.Genre
UNION
SELECT g.Genre, 0 FROM Genre g WHERE NOT EXISTS (SELECT 1 FROM Titles t1, Tracks t2 WHERE g.Genre = t1.Genre AND t1.TitleID = t2.TitleID);

SELECT s1.FirstName+' '+s1.LastName FROM SalesPeople s1, SalesPeople s2 WHERE s1.SuperVisor = s2.SalesID and s2.Supervisor IS NULL;

SELECT m.FirstName + " " + m.LastName AS [Member], s1.FirstName + " " + s1.LastName as [Primary Contact], s2.FirstName + " " + s2.LastName as [Secondary Contact] FROM Members m, SalesPeople s1, SalesPeople s2 WHERE m.SalesID = s1.SalesID AND s1.Supervisor = s2.SalesID;

INSERT INTO Artists (ArtistID, ArtistName, City, Region, Country, EntryDate, LeadSource) VALUES (12, 'November', 'New Orleans', 'LA', 'USA', DATE(), 'Directmail');

My bad on this one. I thought ArtistID was a text field and the query would be just like the one we did before with author emails, but it turns out that ArtistID is a numerical field which has to be converted to string using the STR function. Since we did not cover STR in class, everybody gets 5pt for this one.

UPDATE Artists SET WebAddress = 'www.lyricmusic.com/'+TRIM(STR(ArtistID)) WHERE WebAddress LIKE 'www.lyricmusic*';

Note that removing MP3 and Real Audio downloads for Times Flies does not  mean removing all  information about the tracks, and it certainly does not mean drop the MP3 and ReadAud columns in the Tracks table.

UPDATE Tracks SET MP3=NO, RealAud=NO WHERE TitleID = (SELECT TitleID FROM Titles WHERE Title = 'Time Flies');