Explore databases from file-based systems to modern DBMS, covering data modeling, functional dependencies, and normalization for data integrity.

Computer Science Bootcamp

A database is a structured collection of data stored in a computer system. Data modeling involves designing the structure of a database to ensure efficient data storage, retrieval, and manipulation. Learning data modeling and design is essential as it enables the effective creation of database systems. This module introduces us to databases to gain a competitive edge in the tech industry. We’ll start by exploring the evolution of databases, from file-based systems to the modern era of database management systems (DBMS). Then, we’ll dive deep into fundamental concepts such as data modeling, entity-relationship models, and the relational data model. Next, we’ll learn about functional dependencies, normalization techniques, and their benefits in ensuring data integrity. By the end of this module, we’ll have a solid understanding of databases and their management, empowering us to excel in our careers as a developer and leverage this knowledge to contribute to innovative projects in the industry.

Data Modeling and Design

The values of a column are of the same data-type.

Which of the following is NOT a property of tables?

<p>Which of the following is NOT a property of tables?</p>


We can arbitrarily choose any super key to be the candidate key.

What is the difference between a super key and a candidate key?

<p>What is the difference between a super key and a candidate key?</p>


Which of the following constraints is being violated in the table below?


<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
  border: 1px solid black;
  border-collapse: collapse;
}
</style>
</head>
<body>

<table style="width:100%">
  <tr>
    <th><u>Customer_Id</u></th>
    <th>Name</th> 
    <th>Item_Ordered</th>
  </tr>
  <tr>
    <td>1 </td>
    <td>Brandon </td>
    <td>Shoes   </td>
  </tr>
  <tr>
    <td>2</td>
    <td>Don</td>
    <td>Shirts</td>
  </tr>
  <tr>
    <td>3</td>
    <td>Jeff</td>
    <td>Laptop</td>
  </tr>
  <tr>
    <td>4</td>
    <td>Jack</td>
    <td>Smartphone</td>
  </tr>
  <tr>
    <td>3</td>
    <td>Jeff</td>
    <td>TV</td>
  </tr>
</table>

</body>
</html>


<p>Which of the following constraints is being violated in the table below?</p>
<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
  border: 1px solid black;
  border-collapse: collapse;
}
</style>
</head>
<body>
<table style="width:100%">
  <tr>
    <th><u>Customer_Id</u></th>
    <th>Name</th> 
    <th>Item_Ordered</th>
  </tr>
  <tr>
    <td>1 </td>
    <td>Brandon </td>
    <td>Shoes   </td>
  </tr>
  <tr>
    <td>2</td>
    <td>Don</td>
    <td>Shirts</td>
  </tr>
  <tr>
    <td>3</td>
    <td>Jeff</td>
    <td>Laptop</td>
  </tr>
  <tr>
    <td>4</td>
    <td>Jack</td>
    <td>Smartphone</td>
  </tr>
  <tr>
    <td>3</td>
    <td>Jeff</td>
    <td>TV</td>
  </tr>
</table>
</body>
</html>


Which of the following constraints is being violated in the tables below?

Customer Table

<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
  border: 1px solid black;
  border-collapse: collapse;
}
</style>
</head>
<body>

<table style="width:100%">
  <tr>
    <th><u>Customer_Id</u></th>
    <th>Name</th> 
    <th>Order_Id</th>
  </tr>
  <tr>
    <td>1 </td>
    <td>Brandon </td>
    <td>102  </td>
  </tr>
  <tr>
    <td>2</td>
    <td>Don</td>
    <td>234</td>
  </tr>
  <tr>
    <td>3</td>
    <td>Jeff</td>
    <td>867</td>
  </tr>
  <tr>
    <td>4</td>
    <td>Jack</td>
    <td>420</td>
  </tr>
</table>

</body>
</html>


<br></br>


Order Table

<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
  border: 1px solid black;
  border-collapse: collapse;
}
</style>
</head>
<body>

<table style="width:100%">
  <tr>
    <th><u>Order_Id</u></th>
    <th>Item_Name</th> 
    <th>Item_Category</th>
  </tr>
  <tr>
    <td>102 </td>
    <td>Shoes </td>
    <td>Clothing  </td>
  </tr>
  <tr>
    <td>234</td>
    <td>Shoes</td>
    <td>Clothing</td>
  </tr>
  <tr>
    <td>420</td>
    <td>Laptop</td>
    <td>Electronics</td>
  </tr>
  <tr>
    <td>390</td>
    <td>TV</td>
    <td>Electronics</td>
  </tr>
    <tr>
    <td>111</td>
    <td>Smartphones</td>
    <td>Electronics</td>
  </tr>
</table>

</body>
</html>




<p>Which of the following constraints is being violated in the tables below?</p>
<p>Customer Table</p>
<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
  border: 1px solid black;
  border-collapse: collapse;
}
</style>
</head>
<body>
<table style="width:100%">
  <tr>
    <th><u>Customer_Id</u></th>
    <th>Name</th> 
    <th>Order_Id</th>
  </tr>
  <tr>
    <td>1 </td>
    <td>Brandon </td>
    <td>102  </td>
  </tr>
  <tr>
    <td>2</td>
    <td>Don</td>
    <td>234</td>
  </tr>
  <tr>
    <td>3</td>
    <td>Jeff</td>
    <td>867</td>
  </tr>
  <tr>
    <td>4</td>
    <td>Jack</td>
    <td>420</td>
  </tr>
</table>
</body>
</html>
<p><br></br></p>
<p>Order Table</p>
<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
  border: 1px solid black;
  border-collapse: collapse;
}
</style>
</head>
<body>
<table style="width:100%">
  <tr>
    <th><u>Order_Id</u></th>
    <th>Item_Name</th> 
    <th>Item_Category</th>
  </tr>
  <tr>
    <td>102 </td>
    <td>Shoes </td>
    <td>Clothing  </td>
  </tr>
  <tr>
    <td>234</td>
    <td>Shoes</td>
    <td>Clothing</td>
  </tr>
  <tr>
    <td>420</td>
    <td>Laptop</td>
    <td>Electronics</td>
  </tr>
  <tr>
    <td>390</td>
    <td>TV</td>
    <td>Electronics</td>
  </tr>
    <tr>
    <td>111</td>
    <td>Smartphones</td>
    <td>Electronics</td>
  </tr>
</table>
</body>
</html>


Would the following INSERT operation violate any integrity constraint?

Operation: Insert <5, "Jack Ryan", "2300 Mcfarland Blvd, Northport, AL", 21, 3> into STUDENT.

Student Table

<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
  border: 1px solid black;
  border-collapse: collapse;
}
</style>
</head>
<body>

<table style="width:100%">
  <tr>
    <th><u>Student_Id</u></th>
    <th>Name</th> 
    <th>Address</th>
    <th>Age</th>
    <th>Class</th>
  </tr>
  <tr>
    <td>1</td>
    <td> Chloe Carter</td>
    <td> 777 Brockton Avenue, Abington, MA  </td>
     <td> 22 </td>
    <td>Senior </td>
  </tr>
   <tr>
    <td>2</td>
    <td> Neil Drukmen</td>
    <td> 3018 East Ave, Central Square, NY </td>
     <td>  20  </td>
    <td>Sophomore</td>
  </tr>
  <tr>
    <td> 3 </td>
    <td> Randy Pitchford</td>
    <td> 80 Town Line Rd, Rocky Hill, CT </td>
     <td> 21</td>
    <td> Junior </td>
  </tr>
   <tr>
    <td> 4 </td>
    <td>Kyle Boseman</td>
    <td> 5710 Mcfarland Blvd, Northport, AL</td>
     <td>  22 </td>
    <td>Senior </td>
  </tr>
</table>

</body>
</html>



<p>Would the following INSERT operation violate any integrity constraint?</p>
<p>Operation: Insert &lt;5, “Jack Ryan”, “2300 Mcfarland Blvd, Northport, AL”, 21, 3&gt; into STUDENT.</p>
<p>Student Table</p>
<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
  border: 1px solid black;
  border-collapse: collapse;
}
</style>
</head>
<body>
<table style="width:100%">
  <tr>
    <th><u>Student_Id</u></th>
    <th>Name</th> 
    <th>Address</th>
    <th>Age</th>
    <th>Class</th>
  </tr>
  <tr>
    <td>1</td>
    <td> Chloe Carter</td>
    <td> 777 Brockton Avenue, Abington, MA  </td>
     <td> 22 </td>
    <td>Senior </td>
  </tr>
   <tr>
    <td>2</td>
    <td> Neil Drukmen</td>
    <td> 3018 East Ave, Central Square, NY </td>
     <td>  20  </td>
    <td>Sophomore</td>
  </tr>
  <tr>
    <td> 3 </td>
    <td> Randy Pitchford</td>
    <td> 80 Town Line Rd, Rocky Hill, CT </td>
     <td> 21</td>
    <td> Junior </td>
  </tr>
   <tr>
    <td> 4 </td>
    <td>Kyle Boseman</td>
    <td> 5710 Mcfarland Blvd, Northport, AL</td>
     <td>  22 </td>
    <td>Senior </td>
  </tr>
</table>
</body>
</html>


Would the following DELETE operation violate any integrity constraint?

Operation: Delete the STUDENT tuple with Student_Id = 2.


Student Table

<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
  border: 1px solid black;
  border-collapse: collapse;
}
</style>
</head>
<body>

<table style="width:100%">
  <tr>
    <th><u>Student_Id</u></th>
    <th>Name</th> 
    <th>Address</th>
    <th>Age</th>
    <th>Class</th>
  </tr>
  <tr>
    <td>1</td>
    <td> Chloe Carter</td>
    <td> 777 Brockton Avenue, Abington, MA  </td>
     <td> 22 </td>
    <td>Senior </td>
  </tr>
   <tr>
    <td>2</td>
    <td> Neil Drukmen</td>
    <td> 3018 East Ave, Central Square, NY </td>
     <td>  20  </td>
    <td>Sophomore</td>
  </tr>
  <tr>
    <td> 3 </td>
    <td> Randy Pitchford</td>
    <td> 80 Town Line Rd, Rocky Hill, CT </td>
     <td> 21</td>
    <td> Junior </td>
  </tr>
   <tr>
    <td> 4 </td>
    <td>Kyle Boseman</td>
    <td> 5710 Mcfarland Blvd, Northport, AL</td>
     <td>  22 </td>
    <td>Senior </td>
  </tr>
</table>

</body>
</html>

<p>Would the following DELETE operation violate any integrity constraint?</p>
<p>Operation: Delete the STUDENT tuple with Student_Id = 2.</p>
<p>Student Table</p>
<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
  border: 1px solid black;
  border-collapse: collapse;
}
</style>
</head>
<body>
<table style="width:100%">
  <tr>
    <th><u>Student_Id</u></th>
    <th>Name</th> 
    <th>Address</th>
    <th>Age</th>
    <th>Class</th>
  </tr>
  <tr>
    <td>1</td>
    <td> Chloe Carter</td>
    <td> 777 Brockton Avenue, Abington, MA  </td>
     <td> 22 </td>
    <td>Senior </td>
  </tr>
   <tr>
    <td>2</td>
    <td> Neil Drukmen</td>
    <td> 3018 East Ave, Central Square, NY </td>
     <td>  20  </td>
    <td>Sophomore</td>
  </tr>
  <tr>
    <td> 3 </td>
    <td> Randy Pitchford</td>
    <td> 80 Town Line Rd, Rocky Hill, CT </td>
     <td> 21</td>
    <td> Junior </td>
  </tr>
   <tr>
    <td> 4 </td>
    <td>Kyle Boseman</td>
    <td> 5710 Mcfarland Blvd, Northport, AL</td>
     <td>  22 </td>
    <td>Senior </td>
  </tr>
</table>
</body>
</html>

Would the following UPDATE operation violate any integrity constraint?

Operation: Update the `Student_Id` of the STUDENT tuple with `Student_Id` = 4 to 1.


Student Table

<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
  border: 1px solid black;
  border-collapse: collapse;
}
</style>
</head>
<body>

<table style="width:100%">
  <tr>
    <th><u>Student_Id</u></th>
    <th>Name</th> 
    <th>Address</th>
    <th>Age</th>
    <th>Class</th>
  </tr>
  <tr>
    <td>1</td>
    <td> Chloe Carter</td>
    <td> 777 Brockton Avenue, Abington, MA  </td>
     <td> 22 </td>
    <td>Senior </td>
  </tr>
   <tr>
    <td>2</td>
    <td> Neil Drukmen</td>
    <td> 3018 East Ave, Central Square, NY </td>
     <td>  20  </td>
    <td>Sophomore</td>
  </tr>
  <tr>
    <td> 3 </td>
    <td> Randy Pitchford</td>
    <td> 80 Town Line Rd, Rocky Hill, CT </td>
     <td> 21</td>
    <td> Junior </td>
  </tr>
   <tr>
    <td> 4 </td>
    <td>Kyle Boseman</td>
    <td> 5710 Mcfarland Blvd, Northport, AL</td>
     <td>  22 </td>
    <td>Senior </td>
  </tr>
</table>

</body>
</html>

<p>Would the following UPDATE operation violate any integrity constraint?</p>
<p>Operation: Update the <code>Student_Id</code> of the STUDENT tuple with <code>Student_Id</code> = 4 to 1.</p>
<p>Student Table</p>
<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
  border: 1px solid black;
  border-collapse: collapse;
}
</style>
</head>
<body>
<table style="width:100%">
  <tr>
    <th><u>Student_Id</u></th>
    <th>Name</th> 
    <th>Address</th>
    <th>Age</th>
    <th>Class</th>
  </tr>
  <tr>
    <td>1</td>
    <td> Chloe Carter</td>
    <td> 777 Brockton Avenue, Abington, MA  </td>
     <td> 22 </td>
    <td>Senior </td>
  </tr>
   <tr>
    <td>2</td>
    <td> Neil Drukmen</td>
    <td> 3018 East Ave, Central Square, NY </td>
     <td>  20  </td>
    <td>Sophomore</td>
  </tr>
  <tr>
    <td> 3 </td>
    <td> Randy Pitchford</td>
    <td> 80 Town Line Rd, Rocky Hill, CT </td>
     <td> 21</td>
    <td> Junior </td>
  </tr>
   <tr>
    <td> 4 </td>
    <td>Kyle Boseman</td>
    <td> 5710 Mcfarland Blvd, Northport, AL</td>
     <td>  22 </td>
    <td>Senior </td>
  </tr>
</table>
</body>
</html>

---
Congratulations! You have reached the end of this chapter. At this point, you should be comfortable with ideas such as keys, constraints, and operations regarding relational databases.  

In the next chapter, we will tackle the concepts behind functional dependencies. 

This quiz will test your knowledge of relational databases.

Introduction

Fundamental Concepts

Data Modeling

Entity-Relationship Data Model

The Relational Data Model

Functional Dependencies

Normalization

Conclusion

Quiz!