Reading a relational schema

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. In a relational schema, how is the primary key usually marked?

A It is written in brackets with a question mark, e.g. Student((StudentID?), Name)

B It is underlined, e.g. Student(StudentID, Name)

2. A schema reads: Enrolment(EnrolID, StudentID (FK), CourseID (FK)). What does FK tell you?

A The field links to the primary key of another table

B The field is the primary key of this table

3. Which line is a correctly written schema entry for a table?

A Book(BookID, Title, AuthorID (FK))

B Book = BookID + Title + AuthorID (FK)

4. Schema: Author(AuthorID, Name) and Book(BookID, Title, AuthorID (FK)). Which table does Book link to through AuthorID?

A Author, because AuthorID is its primary key

B Nothing, because AuthorID appears in both tables by accident

5. Schema: Student(StudentID, Name) and Result(ResultID, StudentID (FK), Mark). Which field would you use to join Result to Student?

A ResultID

B StudentID

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1Student(StudentID, FirstName, House)		AOrder(OrderID, StudentID FK) -> Student(StudentID)
2Result(StudentID FK, SubjectID FK, Mark)		BA table called Student with three fields; the underlined StudentID is its primary key, which identifies each record
3The primary key is underlined in a schema so it stands out as the field that uniquely identifies each record		CThe foreign key is in Order, not Student. Student holds the primary key that StudentID FK in Order points to.
4To trace a link between tables, find the foreign key in one table and match it to the primary key of another table		DA table whose StudentID and SubjectID fields are foreign keys linking each result to a student and a subject
5Order(OrderID, StudentID FK, Qty) Student(StudentID, Name) Reading: Student has the FK		EThe primary key is the underlined TeacherID, not Room. Room is an ordinary field that may repeat across teachers.
6Teacher(TeacherID, Name, Room) Reading: Room is the primary key		FProduct(ProductID, Name, Price)

Definitions

Table(PK, field, FK)

Fill in the Blanks

Complete each line of code by filling in the missing parts.

Student(StudentID __, Name, House)

Result(ResultID PK, StudentID __, Mark)

Subject(SubjectID PK, Name) Result(ResultID PK, _________ FK, Mark)

Student(StudentID PK, Name) Orders(OrderID PK, StudentID FK, Total) Link: Orders.StudentID -> _______.StudentID

Author(AuthorID PK, Name) Book(BookID PK, AuthorID FK, Title) Book.AuthorID __ Author.AuthorID

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A Student(StuID PK, Name)

B Subject(SubID PK, Title)

C Result(ResID PK, StuID FK)

D Result.StuID -> Subject.SubID

Question 2

A Book(BookID, Title PK)

B Loan(LoanID PK, BookID FK)

C Loan.BookID -> Book.BookID

Question 3

A Team(TeamID PK, Name)

B Player(PlayID PK, TeamID FK)

C Player.TeamID -> Team.Name

Question 4

A Client(CliID PK, Name)

B Order(OrdID PK, CliID FK)

C Item(ItemID FK, Price)

D Order.CliID -> Client.CliID

Question 5

A Booking(BookID PK, RoomID)

B Room(RoomID PK, Floor)

C Booking.RoomID -> Room.RoomID

Answer Key

Reading a relational schema

Section A: Quick Questions

B — It is underlined, e.g. Student(StudentID, Name)

A — The field links to the primary key of another table

A — Book(BookID, Title, AuthorID (FK))

A — Author, because AuthorID is its primary key

B — StudentID

Section B: Match the Pairs

Student(StudentID, FirstName, House) → A table called Student with three fields; the underlined StudentID is its primary key, which identifies each record

Result(StudentID FK, SubjectID FK, Mark) → A table whose StudentID and SubjectID fields are foreign keys linking each result to a student and a subject

The primary key is underlined in a schema so it stands out as the field that uniquely identifies each record → Product(ProductID, Name, Price)

To trace a link between tables, find the foreign key in one table and match it to the primary key of another table → Order(OrderID, StudentID FK) -> Student(StudentID)

Order(OrderID, StudentID FK, Qty) Student(StudentID, Name) Reading: Student has the FK → The foreign key is in Order, not Student. Student holds the primary key that StudentID FK in Order points to.

Teacher(TeacherID, Name, Room) Reading: Room is the primary key → The primary key is the underlined TeacherID, not Room. Room is an ordinary field that may repeat across teachers.

Section C: Definitions

Table(PK, field, FK) — A relational schema lists each table and its fields, marks the primary key (underlined) and foreign keys (FK), and shows how tables link.

Section D: Fill in the Blanks

Student(StudentID PK, Name, House)

Result(ResultID PK, StudentID FK, Mark)

Subject(SubjectID PK, Name)
Result(ResultID PK, SubjectID FK, Mark)

Student(StudentID PK, Name)
Orders(OrderID PK, StudentID FK, Total)
Link: Orders.StudentID -> Student.StudentID

Author(AuthorID PK, Name)
Book(BookID PK, AuthorID FK, Title)
Book.AuthorID -> Author.AuthorID

Section E: Spot the Error

D — StuID in Result is a foreign key to the Student table, so the link must be Result.StuID -> Student.StuID, not to Subject.SubID.

A — Title is not the primary key. BookID uniquely identifies each book, so it should be marked PK: Book(BookID PK, Title).

C — A foreign key links to the primary key of the other table. It must point to Team.TeamID, not Team.Name.

C — ItemID identifies each row in Item, so it is the table's own primary key and should be marked PK, not FK.

A — RoomID in Booking links to the Room table, so it must be marked FK: Booking(BookID PK, RoomID FK).