Dorm = Dorm.Dorm;
Either syntax is acceptable. In both cases, the column named Dorm, which exists in both tables, must be
???disambiguated??? by qualifying each use of Dorm with the name of the table to which it applies.
One might add two more tables to the University database to track clubs and student participation in them.
Many students can join any club, and each student can belong to many clubs, so the relationship will be M:N.
One must create a table to track the clubs, and an intersection table to track club membership:
CREATE TABLE Club
( Cname VarChar(20) Not Null,
Dues Integer,
Building VarChar(20),
Room Integer
CONSTRAINT ClubPK PRIMARY KEY (Cname)
);
CREATE TABLE ClubMembership
( Cname VarChar(20) Not Null,
MemberName VarChar(20) Not Null
CONSTRAINT ClubMembershipPK PRIMARY KEY (Cname,
MemberName),
CONSTRAINT ClubMembership_Club_FK
FOREIGN KEY (Cname) REFERENCES Club( Cname );
CONSTRAINT Member_FK
FOREIGN KEY (MemberName) REFERENCES Student( Sname );
);
To retrieve a list of students, their majors, and the clubs to which they belong, one could use this query that
joins the Student and ClubMembership tables:
SELECT Sname, Major, Cname
FROM Student JOIN ClubMembership
ON Student.
Pages:
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421