These eight original questions cover entities, keys, diagrams and simple SQL. Try each before opening the answer.
This set belongs to data and databases. A teacher can review your working in the one-hour trial class (from RM50), a taught lesson on the Computer Science topic you choose.
Questions
Question 1. In a scenario about a gym, sort these: Member, membership fee, joins, Trainer. Give the type of each.
Answer
Member and Trainer are entities. Membership fee is an attribute, probably of Member. Joins is a relationship, linking Member to the gym or a class.
Question 2. A Student table has StudentID, Name and Class. Which column is the best primary key and why?
Answer
StudentID. It is unique, never empty and stable. Name can repeat, and Class changes when a student moves.
Question 3. The Loan table has LoanID, MemberID and BookID. Which columns are foreign keys?
Answer
MemberID and BookID. Each holds the primary key value of a row in the Member and Book tables. LoanID is the primary key of Loan.
Question 4. State the relationship: one school house has many students, and each student belongs to one house.
Answer
One-to-many, from House to Student. The foreign key HouseID sits in the Student table.
Question 5. A diagram shows STUDENT (M) attends (M) SUBJECT. What must you add to store this in tables?
Answer
An intermediate table, for example Enrolment, holding StudentID and SubjectID. A many-to-many relationship cannot be stored with a single foreign key.
Question 6. Use the table Item (ItemName, Price, Category) with rows (Teh Tarik, 2.50, Drink), (Nasi Lemak, 4.00, Food), (Milo, 3.00, Drink). What does SELECT ItemName FROM Item WHERE Price > 2.50; return?
Answer
Price above 2.50 passes for Nasi Lemak (4.00) and Milo (3.00). Teh Tarik is exactly 2.50, which is not greater than 2.50. The output is Nasi Lemak and Milo.
Question 7. Write a query for the same table that shows all drinks, cheapest first.
Answer
SELECT ItemName, Price
FROM Item
WHERE Category = 'Drink'
ORDER BY Price;The result lists Teh Tarik (2.50), then Milo (3.00).
Question 8. A student wrote WHERE Category = Drink. What is wrong?
Answer
Text values need quotation marks, so it should be Category = 'Drink'. Without quotation marks, the system reads Drink as a column name.
If you got these wrong
For question 1, review entities, attributes and relationships. For questions 2 and 3, read choosing primary and foreign keys.
For questions 4 and 5, use reading an entity relationship diagram. For questions 6 to 8, see writing simple SQL queries.
To go deeper, try relational data modelling. For a teacher’s help, see online one-to-one Computer Science tuition.