Worksheet 2: Introduction to relational models

Exercise 1: Understanding Relational Database Concepts

rubric: {accuracy = 12}

Objective

Test your understanding of key properties of relations, different types of keys, and the four integrity rules in relational databases.

Sample Dataset: Bookstore

Books Table

BookID	Title	Genre	Price	AuthorID
1	The Great Gatsby	Fiction	10.99	101
2	To Kill a Mockingbird	Fiction	8.99	102
3	A Brief History of Time	Non-Fiction	15.99	103

Authors Table

AuthorID	FirstName	LastName
101	F. Scott	Fitzgerald
102	Harper	Lee
103	Stephen	Hawking

1.1: Key Properties of Relations

Questions

1. Attributes:

   • Define what an attribute is in the context of a relational database.

     • Answer: The properties that define a relation. Also known as columns or fields.

   • Provide an example of an attribute in the Books table.

     • Answer: Genre, or Price, or Title

2. Tuples:

   • Define what a tuple is in the context of a relational database.

     • Answer: Each one of the rows or records in a relation.

   • Provide an example of a tuple in the Books table.

     • Answer: The second tuple in the table is: | 2 | To Kill a Mockingbird| Fiction | 8.99 | 102 |

3. Domain:

   • Define what a domain is in the context of a relational database.

     • Answer: A unique set of possible values permitted for an attribute in a relation.

   • Provide an example of a domain for the Price attribute in the Books table.

     • Answer: The prices could be non-negative decimal numbers with 2 decimal places.

4. Degree:

   • Define what the degree of a relation is.

     • Answer: The number of attributes in a relation.

   • Determine the degree of the Books table.

     • Answer: 5

1.2: Different Types of Keys

Questions

1. Primary Key:

   • Define what a primary key is.

     • Answer: A column or set of columns that uniquely identify each tuple in a relation.

   • Identify the primary key in the Books table.

     • Answer: BookID

2. Foreign Key:

   • Define what a foreign key is.

     • Answer: A column or set of columns in a relation that refer to a primary key in another relation.

   • Identify the foreign key in the Books table.

     • Answer: AuthorID

3. Candidate Key:

   • Define what a candidate key is.

     • Answer: A column or set of columns that can uniquely identify any row in a relation. A relation can have multiple Candidate Keys, but only one is chosen.

   • Identify any candidate keys in the Books table.

     • Answer: Book title + Author ID

4. Composite Key:

   • Define what a composite key is.

     • Answer: A key that consists of 2 or more columns.

   • Provide an example of a composite key in a hypothetical Books table.

     • Answer: Book title + Genre

1.3: The Four Integrity Rules

Questions

1. Entity Integrity:

   • Define what entity integrity is.

     • Answer: Each relation should have a primary key, and the primary key must be unique and not null.

   • Explain how entity integrity is enforced in the Books table.

     • Answer: The BookID is an auto incremental primary key, so the system ensures it is unique and not null.

2. Referential Integrity:

   • Define what referential integrity is.

     • Answer: A foreign key must be a valid primary key in another relation.

   • Explain how referential integrity is enforced between the Books and Authors tables.

     • Answer: All the AuthorId (foreign key) values in the Books relation also exist as primary keys in the Authors relation.

3. Domain Integrity:

   • Define what domain integrity is.

     • Answer: All values in an attribute must fall within a defined domain (ser of permissible values).

   • Provide an example of domain integrity in the Books table.

     • Answer: The price attribute probably has domain integrity contraints, e.g., price must be non-negative numeric format

4. User-Defined Integrity:

   • Define what user-defined integrity is.

     • Answer: Custom rules defined by the user to enforce specific business requirements.

   • Provide an example of a user-defined integrity rule in the Books table.

     • Answer: The length of title should be less than 1000 characteres

Exercise 2: Understanding Relational Algebra with SQL

rubric: {accuracy = 7}

Objective

Test your understanding of relational algebra operations using SQL queries on a sample dataset from a bookstore. Predict the type of relational algebra operation based on the given SQL query and its output. Choose the correct operation from the provided list of options.

Sample Dataset: Bookstore

Books Table

BookID	Title	Genre	Price	AuthorID
1	The Great Gatsby	Fiction	10.99	101
2	To Kill a Mockingbird	Fiction	8.99	102
3	A Brief History of Time	Non-Fiction	15.99	103
4	The Catcher in the Rye	Fiction	7.99	104
5	Sapiens	Non-Fiction	18.99	105

Authors Table

AuthorID	FirstName	LastName
101	F. Scott	Fitzgerald
102	Harper	Lee
103	Stephen	Hawking
104	J.D.	Salinger
105	Yuval	Harari

Relational Algebra Operations Options

1. Selection (σ)

2. Projection (π)

3. Union (∪)

4. Set Difference (−)

5. Cartesian Product (×)

6. Join (⨝)

7. Intersection (∩)

For each of the query below, I have provided a sample SQL query and the output from that query. Your task is to determine what type of relational algebra operation it is (e.g. select, projection, union, product, join, etc…)

Questions

1. Query 1:

   • SQL Query:

     SELECT * FROM Books WHERE Genre = 'Fiction';
     

   • Output:

     BookID	Title	Genre	Price	AuthorID
     1	The Great Gatsby	Fiction	10.99	101
     2	To Kill a Mockingbird	Fiction	8.99	102
     4	The Catcher in the Rye	Fiction	7.99	104

• Predict the type of relational algebra operation:

  • Answer: Selection

2. Query 2:

   • SQL Query:

     SELECT Title, Price FROM Books;
     

   • Output:

     Title	Price
     The Great Gatsby	10.99
     To Kill a Mockingbird	8.99
     A Brief History of Time	15.99
     The Catcher in the Rye	7.99
     Sapiens	18.99

• Predict the type of relational algebra operation:

  • Answer: Projection

3. Query 3:

   • SQL Query:

     SELECT * FROM Books WHERE Price < 10
     UNION
     SELECT * FROM Books WHERE Price > 15;
     

   • Output:

     BookID	Title	Genre	Price	AuthorID
     4	The Catcher in the Rye	Fiction	7.99	104
     5	Sapiens	Non-Fiction	18.99	105
     3	A Brief History of Time	Non-Fiction	15.99	103

• Predict the type of relational algebra operation:

  • Answer: Union

4. Query 4:

   • SQL Query:

     SELECT * FROM Books WHERE Genre = 'Fiction' AND AuthorID != 102;
     

   • Output:

     BookID	Title	Genre	Price	AuthorID
     1	The Great Gatsby	Fiction	10.99	101
     4	The Catcher in the Rye	Fiction	7.99	104

• Predict the type of relational algebra operation:

  • Answer: Selection.

5. Query 5:

   • SQL Query:

     SELECT * FROM Books, Authors;
     

   • Output: (Note: This will produce a large number of rows, showing all possible combinations of Books and Authors.)

• Predict the type of relational algebra operation:

  • Answer: Cartesian Product

6. Query 6:

   • SQL Query:

     SELECT * FROM Books JOIN Authors ON Books.AuthorID = Authors.AuthorID;
     

   • Output:

     BookID	Title	Genre	Price	AuthorID	FirstName	LastName
     1	The Great Gatsby	Fiction	10.99	101	F. Scott	Fitzgerald
     2	To Kill a Mockingbird	Fiction	8.99	102	Harper	Lee
     3	A Brief History of Time	Non-Fiction	15.99	103	Stephen	Hawking
     4	The Catcher in the Rye	Fiction	7.99	104	J.D.	Salinger
     5	Sapiens	Non-Fiction	18.99	105	Yuval	Harari

• Predict the type of relational algebra operation:

  • Answer: Join

7. Query 7:

   • SQL Query:

     SELECT * FROM Books WHERE Genre = 'Fiction'
     EXCEPT
     SELECT * FROM Books WHERE AuthorID = 102;
     

   • Output:

     BookID	Title	Genre	Price	AuthorID
     1	The Great Gatsby	Fiction	10.99	101
     4	The Catcher in the Rye	Fiction	7.99	104

• Predict the type of relational algebra operation:

  • Answer Set Difference

Exercise 3: Understanding Different Types of Relationships in Relational Databases

rubric: {accuracy = 4}

Objective

Test your understanding of different types of relationships in relational databases using a sample dataset from a bookstore. Identify the type of relationship based on the given tables and their relationships. Justify your choice

Sample Dataset: Bookstore

Books Table

BookID	Title	GenreID	Price	AuthorID
1	The Great Gatsby	1	10.99	101
2	To Kill a Mockingbird	1	8.99	102
3	A Brief History of Time	2	15.99	103
4	The Catcher in the Rye	1	7.99	104
5	Sapiens	2	18.99	105

Authors Table

AuthorID	FirstName	LastName
101	F. Scott	Fitzgerald
102	Harper	Lee
103	Stephen	Hawking
104	J.D.	Salinger
105	Yuval	Harari

Sales Table

SaleID	BookID	Quantity	SaleDate
1	1	2	2023-01-15
2	3	1	2023-01-16
3	2	3	2023-01-17
4	4	1	2023-01-18
5	5	2	2023-01-19

Genres Table

GenreID	GenreName
1	Fiction
2	Non-Fiction

Types of Relationships

1. One-to-One (1:1)

2. One-to-Many (1:N)

3. Many-to-Many (M:N)

Questions

1. Relationship 1:

   • Tables: Books and Authors

• Type of Relationship and Description:

  • Sample Answer: One-to-Many (1:N). Each book is written by one author, and each author can write multiple books

2. Relationship 2:

   • Tables: Books and Sales

• Type of Relationship and Description:

  • Answer: One-to-Many (1:N). Each Sale is associated with one Book, but each Book can be sold in multiple Sales.

  • Using a different design with a linking table, a Sale could be associated with several Books for a Many-to-Many relationship, but that idea is not shown in the example.

  • Both 1-N or N-N is accepted

3. Relationship 3:

   • Tables: Authors and Sales

• Type of Relationship and Description:

  • Answer: One-to-Many (1:N). It is an indirect relationship through the Books table. Each Sale is associated with one Book, and each Book is associated with one Author, but one Author can have multiple Books, and each Book can show in multiple Sales.

4. Relationship 4:

   • Tables: Books and Genres

• Type of Relationship and Description:

  • Answer: One-to-Many (1:N). Each book is associated with one Genre, but one Genre can show in multiple Books.

5. Relationship 5:

   • Tables: Authors and Genres

• Type of Relationship and Description:

  • Answer: Many-to-Many (M:N). It is an indirect relationship through the Books table. One Author can have multiple Books, each with one Genre. One Genre can have multiple Books, each with one Author.

Submission instructions

{rubric: mechanics = 5}

• Make sure the notebook can run from top to bottom without any error. Restart the kernel and run all cells.

• Commit and push your notebook to the github repo

• Double check your notebook is rendered properly on Github and you can see all the outputs clearly

• Submit URL of your Github repo to Moodle under worksheet 2