Summary: Studyguide For Database System Concepts By Abraham Silberschatz, Isbn 9780073523323 | 9781490290898 | Cram101 Textbook Reviews

Read the summary and the most important questions on Studyguide for Database System Concepts by Abraham Silberschatz, ISBN 9780073523323 | 9781490290898 | Cram101 Textbook Reviews

• 2 Introduction to the Relational Model

  This is a preview. There are 4 more flashcards available for chapter 2
  Show more cards here

• The relational model is today the primary data model for commercial data-processing applications.What problems of previously used data models does it solve and how does it do that?

  • Single-purpose Tables.
    
  • Previous models tried to put all data within one table.
  • Inefficient, a lot of repetition, and not clear to use.
  • Relationship models use multiple tables, of which every table has a single purpose (e.g., a table with all professors of the History & Art faculty).
  • The Relationship table.
  • Relation: Each table is called a relation.
  • Attribute: A column of the table (e.g., ID, salary, etc.).
  • Tuple: A row of the table, which represents one complete record (e.g., all info of one professor).

• What is the schema diagram of a relational database?

  A schema diagram visually represents the structure of a relational database.
  

  • Relations are visualised as a box, with the relation's name in the top.
  • Primary key attributes are underlined.
  • Foreign keys are connected to the primary key of the referenced relation through arrows. 

• 3 Introduction to SQL

  This is a preview. There are 6 more flashcards available for chapter 3
  Show more cards here

• The fundamental structure of an SQL query consists of three clauses.Can you name and describe these three clauses and connect each to a relational algebra operation?

  • SELECT.
  • Relational Algebra Operation: Projection (Π).
  • Select distinct.
  • By default, SQL allows duplicates in query results.
  • By adding 'distinct' to your query, you can eliminate duplicates.
  • Select all.
  • While they are retained by default, adding the keyword 'all' explicitly retains duplicates in query results. 
  • Select *.
  • By adding the asterisk you are saying select all attributes.
  • Arithmetic expressions.
  • Select can contain arithmetic expressions involving the operators (+, -, *, /). 
  • E.g., selectID, name, dept name, salary * 1.1from instructor;
    
  • FROM.
  • Relational Algebra Operation: Cartesian Product (Χ) .

  • WHERE.
  • Relational Algebra Operation: Selection (σ).

• 6 Formal Relational Query Languages

  This is a preview. There are 10 more flashcards available for chapter 6
  Show more cards here

• What is Relational Algebra and why do we use it for SQL and working with databases in in general?

  Relational Algebra.
  

  • A procedural query language, containing a collection of operations.
  • These operations help to create a new relation from one/two existing relations:

  • Input:
  • It always takes one or two relations as input.
  • Output:
  • It always produces a new relation.

• We can divide relational algebra's fundamental operations into unary and binary operations. What is the difference between the two and which operations fall under which category?

  Unary Operations:
  

  • Operations that work on a single relation.
  • Fundamental Unary Operations:
  • Selection (σ).
  • Projection (π).
  • Renaming (ρ).

  Binary Operations:
  

  • Operations that work on two relations.
  • Fundamental Binary Operations:
  • Union (∪).
  • Difference (−).
  • Cartesian Product (×).
  • Join (⨝).

• Consider the following query in Tuple Relational Calculus.What does ∈ mean?

  • It means "is an element of."
    
  • Used tot state that an object (in this case a tuple) belongs to a collection.
  • E.g., t = {1, 2, 3, 4}.
  • We can say: 2 ∈ t.
  • 2 is an element of t.

  • In {t | t  ∈ instructor... }:
  • Means: find all tuples ( t ) that are members of the set of tuples that makes up the instructor relation.

• 7 Database Design and the E-R Model

  This is a preview. There are 2 more flashcards available for chapter 7
  Show more cards here

• In an E-R Model, entities’ roles in a relationship are often implicit, but there are exceptions.Can you explain when entity roles become important in relationships?

  • Recursive relationship.
    
  • Relationship in which the same entity set participates more than once. 
  • E.g., Relationship set 'prerequisite.' 
  • Entity set 'course' appears twice in this relationship set.
  • One course is the dependent course.
  • The course that needs a prerequisite course.
  • One course is the independent, or prerequisite course. 
  • Entity Role
  • In such cases, the entity role becomes important as it clarifies which entity set is which. 

• 8 Relational Database Design

  This is a preview. There are 13 more flashcards available for chapter 8
  Show more cards here

• Consider the following statement:"Let R be a relation schema, and let α ⊆ R and β ⊆ R."What does ⊆ mean and what is the connection of α and β to R?

  • Subset of.
    
  • ⊆ Means that the variable on the left is a subset of the variable on the right.
  • Subsets α and β.
  • α and β are both sets of attributes that belong to the same relation R.
  • R is a relation schema, and therefore represents a set of all attributes.
  • α and β are subsets of R and have some of the attributes of R.
  • I.e., all attributes in α and β always have to be in R as well. 

• You have a set of functional dependencies that we call F.What is its closure ( F+ ) would contain?

  • Closure.
    
  • All functional dependencies that can be logically derived from the FD set F, without being explicitly stated.
  • When you have a set of FDs, they can imply other dependencies that you could logically derive from F.
  • E.g., F contains A → B and B → C.
  • We can logically infer that this means that A → C.
  • This FD would be part of F+. 

• What is a canonical cover?

  • 'Cleaned-Up' F.
    
  • A canonical cover ( Fc ) is a simplified version of a set of functional dependencies ( F ).
  • It differs from 'F' in that all redundant information is removed.
  • Every FD in 'Fc' and every attribute in it is necessary to preserve the same meaning as the original 'F'.
  • F+ = Fc+.
  • The closure of Fc is equal to the closure of F. 
  • 3NF.
  • Canonical covers are used in the 3NF decomposition algorithm.