Relational Model Practice Problems
Consider this schema from the textbook for a university database:
- What does each box represent?1
- What is the meaning of the underlined attributes?2
- What do the arrows represent?3
- Why are both
course_idandprereq_idpart of the primary key for the prereq relation?4
Consider this schema for an employee database:
Give a relational algebra expression for each of the following queries:5
- Find the name of each employee who lives in city "Miami".
- Find the name of each employee whose salary is greater than $100000.
- Find the name of each employee who lives in "Miami" and whose salary is greater than $100000.
Footnotes:
A relation. The name of the relation is in blue, with the attributes of the relation listed underneath.
They form the primary key for that relation. A primary key uniquely identifies each tuple in the relation. Every tuple must have a value for these attributes (i.e., they cannot be NULL)
These are foreign key relationships, where an attribute in one relation refers to the primary key in another relation.
A course could have multiple prerequisites, and so might need to appear in multiple tuples. Thus, both attributes are required to uniquely identify a tuple.
- \(\Pi_{person\_name}(\sigma_{city = "Miami"}(employee))\)
- \(\Pi_{person\_name}(\sigma_{salary > 100000}(works))\)
- \(\Pi_{person\_name}(\sigma_{city = "Miami" \;\land\; salary > 100000}(employee \bowtie_{person\_name} works))\)