Database Design Basics
130 Questions
8 Views

Choose a study mode

Play Quiz
Study Flashcards
Spaced Repetition
Chat to lesson

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

What does the ON UPDATE CASCADE option accomplish in a foreign key constraint?

  • It prevents any updates to the referenced table.
  • It allows for updates without affecting the referenced keys.
  • It propagates updates to the referenced table to the table with the foreign key. (correct)
  • It deletes entries in the referenced table if a corresponding entry is deleted.

    • What should be the typical approach regarding NULL values in attributes according to good practices?

  • All attributes should be constrained to NOT NULL unless there's a strong reason. (correct)
  • Only numeric attributes should be constrained to NOT NULL.
  • Attributes should always be allowed to be NULL to ensure flexibility.
  • NULL attributes should be used only for foreign keys.

    • Which of the following is NOT an option for the action taken on delete in a foreign key constraint?

  • SET DEFAULT
  • SET NULL
  • CASCADE
  • IGNORE (correct)

    • Defer is usually recommended for which type of constraints?

    <p>Constraints that can be safely modified after the initial transaction.</p> Signup and view all the answers

    What could be a consequence of using ON DELETE CASCADE?

    <p>It may cause a chain reaction of deletions in related tables.</p> Signup and view all the answers

    Which of these practices is a part of good database design?

    <p>Controlling database updates with triggers for generalization.</p> Signup and view all the answers

    What is the main caution regarding foreign keys and the CASCADE option?

    <p>Consideration should be given to which foreign keys should be subject to cascade.</p> Signup and view all the answers

    What action does the SET NULL option perform when applied to a foreign key constraint?

    <p>It sets the foreign key's values to NULL when the referenced row is deleted.</p> Signup and view all the answers

    What defines a consistent state of the database?

    <p>The state must comply with business rules and constraints.</p> Signup and view all the answers

    Which transaction control keywords are used to manage transactions in SQL?

    <p>BEGIN, COMMIT, ROLLBACK</p> Signup and view all the answers

    What is a primary advantage of deferred integrity constraints?

    <p>They allow for all constraints to be checked together at the end of transactions.</p> Signup and view all the answers

    What property ensures that all parts of a transaction are completed or none at all?

    <p>Atomicity</p> Signup and view all the answers

    Which of the following describes integrity constraints?

    <p>They enforce the business rules defined in the schema.</p> Signup and view all the answers

    In practice, what is a limitation of many systems regarding integrity constraints?

    <p>They only allow constraints to be deferred in PostgreSQL.</p> Signup and view all the answers

    What scenario does the term 'atomicity' apply to in database transactions?

    <p>The need for all operations in a transaction to be treated as a single unit.</p> Signup and view all the answers

    Which aspect of transactions is NOT considered a characteristic of the ACID properties?

    <p>Dynamism</p> Signup and view all the answers

    Which of the following is NOT a kind of integrity constraint supported by SQL?

    <p>DEFAULT</p> Signup and view all the answers

    What happens when you execute the command 'DELETE FROM customers;'?

    <p>It deletes all records in the customers table, preserving its definition.</p> Signup and view all the answers

    Which statement about a primary key is true?

    <p>A primary key uniquely identifies a record within a table.</p> Signup and view all the answers

    Which command would you use to permanently remove both the data and structure of the customers table?

    <p>DROP TABLE customers;</p> Signup and view all the answers

    What is the maximum number of primary keys a single table can have?

    <p>One</p> Signup and view all the answers

    What behavior does the CHECK constraint enforce?

    <p>It defines the values that a column can hold.</p> Signup and view all the answers

    Which of the following statements is false regarding foreign keys?

    <p>A foreign key can reference a non-existent table.</p> Signup and view all the answers

    Which of the following accurately defines an integrity constraint?

    <p>A condition that data must satisfy to maintain data integrity.</p> Signup and view all the answers

    What defines a primary key in SQL?

    <p>It cannot contain NULL values.</p> Signup and view all the answers

    What does a UNIQUE constraint ensure in a database table?

    <p>No two records can have the same value in the specified column.</p> Signup and view all the answers

    Which of the following statements about the UNIQUE constraint in SQL is correct?

    <p>It can be applied to multiple columns in combination.</p> Signup and view all the answers

    What happens if you attempt to create a record in a table with a duplicate value in a UNIQUE column?

    <p>An error occurs, and the record is not inserted.</p> Signup and view all the answers

    In the given table creation example, which column has a UNIQUE constraint applied?

    <p>email</p> Signup and view all the answers

    How is a primary key related to the UNIQUE constraint in SQL?

    <p>Both ensure unique values but have different behaviors regarding NULL.</p> Signup and view all the answers

    Which keyword is used to specify that a column must not allow NULL values in SQL?

    <p>NOT NULL</p> Signup and view all the answers

    What is the purpose of the 'Check' constraint in a SQL table creation statement?

    <p>To ensure a condition is met for the values in the specified column.</p> Signup and view all the answers

    What defines a relation in the context of a relational database?

    <p>A relation is a subset of the Cartesian product of the domains of its attributes.</p> Signup and view all the answers

    Which component of a table corresponds to the columns?

    <p>Fields</p> Signup and view all the answers

    What is implied by a field's position in a table?

    <p>Its implicit order in the creation statement</p> Signup and view all the answers

    What does logical design mainly involve?

    <p>Selecting the appropriate schema for the database application.</p> Signup and view all the answers

    In a relational database, what value can a column field potentially hold aside from data types?

    <p>NULL values</p> Signup and view all the answers

    What does the term 'multi-set' imply about the rows in a relational table?

    <p>Rows can appear multiple times within a table.</p> Signup and view all the answers

    Which aspect of tables is not typically considered during logical design?

    <p>The physical location of data storage</p> Signup and view all the answers

    What constitutes the domains in a relational database context?

    <p>The data types that define possible values for a column.</p> Signup and view all the answers

    Which condition correctly filters customers based on their country and date of birth in a SQL SELECT statement?

    <p>country IN ('Indonesia', 'Singapore') AND dob &gt;= '2000-01-01' AND dob &lt;= '2016-12-01'</p> Signup and view all the answers

    Which SQL clause correctly retrieves names from the games table with specific version conditions?

    <p>WHERE version IN ('1.0', '1.1')</p> Signup and view all the answers

    What is the main goal of the last_name LIKE 'B%' condition in the SQL SELECT statement?

    <p>To filter last names that start with 'B'</p> Signup and view all the answers

    Which SQL method is most effective at handling duplicates when retrieving data from a table?

    <p>DISTINCT</p> Signup and view all the answers

    In data retrieval practices, which SQL feature helps to ensure optimized performance for large datasets when applying filters?

    <p>Inserting indexes on columns used in WHERE clauses</p> Signup and view all the answers

    What is the outcome of selecting a subset of columns from a table without applying an ORDER BY clause?

    <p>Duplicate rows may still be returned even with a primary key.</p> Signup and view all the answers

    How does the ORDER BY clause behave when only some columns are specified for ordering?

    <p>Columns not mentioned can appear in any order.</p> Signup and view all the answers

    What is a recommended best practice when using the ORDER BY clause?

    <p>It is advisable to explicitly specify ASC and DESC.</p> Signup and view all the answers

    What may occur when a result is ordered by a column not included in the output?

    <p>It can interfere with the interpretation of the results.</p> Signup and view all the answers

    What is the default ordering behavior of the ORDER BY clause in SQL?

    <p>Ascending order (ASC).</p> Signup and view all the answers

    When projecting certain columns from a table, what should one be cautious of regarding duplicates?

    <p>Duplicates cannot be controlled without using DISTINCT.</p> Signup and view all the answers

    What might a lack of an ORDER BY clause result in when extracting data from a SQL table?

    <p>The data will be retrieved in random order.</p> Signup and view all the answers

    In SQL, which of the following is true about the use of the ORDER BY clause?

    <p>Partially ordering results is allowed but may yield unpredictable outcomes.</p> Signup and view all the answers

    What does the SELECT clause in a SQL query primarily indicate?

    <p>The columns to be displayed in the result set.</p> Signup and view all the answers

    Which wildcard character is used in SQL to select all columns from a table?

    <ul> <li></li> </ul> Signup and view all the answers

    In a SQL query, how can duplicates in the results be addressed?

    <p>Utilize the DISTINCT keyword.</p> Signup and view all the answers

    Which clause in a SQL query indicates which records should be returned based on specific criteria?

    <p>WHERE</p> Signup and view all the answers

    What is the primary benefit of using the SELECT * syntax in a SQL query?

    <p>It retrieves all columns from the specified table.</p> Signup and view all the answers

    When utilizing the ORDER BY clause in a SQL query, what is primarily defined?

    <p>The sequence in which the result set is sorted.</p> Signup and view all the answers

    Which is a recommended best practice when retrieving data using a SQL query?

    <p>Include specific columns in the SELECT clause.</p> Signup and view all the answers

    What is a potential drawback of using the asterisk (*) in a SELECT statement frequently?

    <p>It may lead to unnecessary data retrieval.</p> Signup and view all the answers

    What is the purpose of using a LEFT OUTER JOIN in a SQL query?

    <p>To include all rows from the left table and matched rows from the right table.</p> Signup and view all the answers

    Which statement accurately describes the difference between INNER JOIN and OUTER JOIN?

    <p>OUTER JOIN includes unmatched records from one or both tables, while INNER JOIN only includes matched records.</p> Signup and view all the answers

    What is the impact of using the keyword ALL with the UNION operator in SQL?

    <p>It includes all rows, even duplicates, in the combined result set.</p> Signup and view all the answers

    When using the GROUP BY statement in SQL, what is its primary function?

    <p>To organize rows that have the same values in specified columns for aggregate functions.</p> Signup and view all the answers

    Which of the following is NOT a synonym for an OUTER JOIN?

    <p>INNER JOIN</p> Signup and view all the answers

    How does SQL handle duplicates in result sets when using the INTERSECT operator?

    <p>It returns a single occurrence of each matching row.</p> Signup and view all the answers

    What potential issue may arise from executing a SQL query with multiple JOINs?

    <p>Increased complexity leading to higher chances of errors.</p> Signup and view all the answers

    What is the role of aggregate functions in SQL?

    <p>To summarize data across multiple rows and return a single value.</p> Signup and view all the answers

    What is a requirement for the first_name and last_name fields in a SQL query that includes aggregation functions and GROUP BY clause?

    <p>They must be unique.</p> Signup and view all the answers

    What does the GROUP BY clause accomplish in the provided SQL queries?

    <p>It aggregates data into logical groups based on specified columns.</p> Signup and view all the answers

    Which of the following statements is true about the ORDER BY clause in SQL?

    <p>It will only affect the display order of the results.</p> Signup and view all the answers

    What is the main purpose of the HAVING clause in SQL?

    <p>To apply conditions on aggregated data after grouping</p> Signup and view all the answers

    In an SQL query using the EXTRACT function, what is the purpose of using 'AS regyear' in the SELECT statement?

    <p>To rename the extracted year for clarity in the output.</p> Signup and view all the answers

    Why might the GROUP BY clause produce different results in PostgreSQL compared to SQLite?

    <p>PostgreSQL requires all selected fields to be mentioned in the GROUP BY clause.</p> Signup and view all the answers

    In which scenario is the HAVING clause necessary instead of the WHERE clause?

    <p>When applying conditions based on aggregate functions</p> Signup and view all the answers

    What does the use of aggregate functions in SQL allow for?

    <p>It allows calculation of single-value summaries from multiple rows.</p> Signup and view all the answers

    What does the GROUP BY statement do in an SQL query?

    <p>It aggregates data into summary rows based on specified columns</p> Signup and view all the answers

    When renaming columns in an aggregate query with GROUP BY, what is the effect on the GROUP BY clause?

    <p>Renamed columns can be used directly in GROUP BY.</p> Signup and view all the answers

    Which of the following statements correctly uses the HAVING clause to filter results?

    <p>SELECT country FROM customers GROUP BY country HAVING COUNT(*) &gt;= 100</p> Signup and view all the answers

    What is an important consideration when constructing a SQL query with both JOINs and GROUP BY?

    <p>All JOIN clauses must precede GROUP BY.</p> Signup and view all the answers

    What type of aggregate functions can be used in the HAVING clause?

    <p>Any aggregate functions like SUM(), AVG(), COUNT(), etc.</p> Signup and view all the answers

    Which SQL join type is used to combine rows from two or more tables based on a related column?

    <p>INNER JOIN</p> Signup and view all the answers

    When optimizing an SQL query, which strategy can improve performance when dealing with large datasets?

    <p>Applying filters using the WHERE clause as early as possible</p> Signup and view all the answers

    What is the primary benefit of using the HAVING clause after GROUP BY in an SQL query?

    <p>To restrict which groups are included in the result set after aggregation</p> Signup and view all the answers

    What is the primary purpose of the primary key in a database table?

    <p>To ensure each record is uniquely identifiable</p> Signup and view all the answers

    How are relationship sets typically mapped in a relational database schema?

    <p>They are mapped to relations with attributes from relationships and entity keys.</p> Signup and view all the answers

    In the provided CREATE TABLE statement for the person table, what combination constitutes the primary key?

    <p>first_name and last_name</p> Signup and view all the answers

    Which statement accurately describes the attributes of a relation in schema translation?

    <p>Attributes are taken from both entity and relationship sets.</p> Signup and view all the answers

    What is a weak entity in the context of relational databases?

    <p>An entity that does not have a primary key of its own.</p> Signup and view all the answers

    What happens to attributes when an entity set is mapped to a relation in schema translation?

    <p>They are directly mapped to the attributes of the relation.</p> Signup and view all the answers

    Which of the following is a correct statement about foreign key constraints?

    <p>A foreign key creates a relationship between two tables.</p> Signup and view all the answers

    In SQL, which keyword is used to specify that a column must allow NULL values?

    <p>ALLOW NULL</p> Signup and view all the answers

    Which statement correctly defines a one-to-many relationship in database schema design?

    <p>It allows one record in a table to be associated with multiple records in another table.</p> Signup and view all the answers

    What is the correct SQL statement to define a weak entity in a CREATE TABLE command?

    <p>CREATE TABLE weak_entity (id INT PRIMARY KEY, foreign_id INT, CONSTRAINT FOREIGN KEY (foreign_id) REFERENCES parent_entity(id));</p> Signup and view all the answers

    In the CREATE TABLE statement for the 'contract' table, what is the role of the PRIMARY KEY constraint?

    <p>To enforce uniqueness for a combination of the 'first_name', 'last_name', and 'name' columns.</p> Signup and view all the answers

    When creating foreign key constraints in SQL, what does the phrase 'REFERENCES company(name)' specify?

    <p>It ensures the 'name' in the 'contract' table must exist in the 'company' table.</p> Signup and view all the answers

    What is the significance of specifying NOT NULL in the 'CREATE TABLE' statement?

    <p>It indicates that a column must always have a value; it cannot accept NULL.</p> Signup and view all the answers

    Which of the following is NOT a characteristic of a weak entity?

    <p>It can exist independently of the strong entity.</p> Signup and view all the answers

    What is an incorrect way to define a relationship in the context of schema translation?

    <p>Creating a circular reference between two tables.</p> Signup and view all the answers

    In the provided table definitions, which of the following columns is set as the primary key in the 'work_for' table?

    <p>enumber, cname</p> Signup and view all the answers

    In the context of schema translation, what is a defining characteristic of a weak entity?

    <p>It relies on a strong entity for identification.</p> Signup and view all the answers

    What is the correct SQL syntax to create a table that properly includes a foreign key constraint referencing another table?

    <p>CREATE TABLE table_name (column1 INT, column2 VARCHAR(50), FOREIGN KEY(column2) REFERENCES other_table(field) ON DELETE CASCADE);</p> Signup and view all the answers

    Which statement accurately describes One-to-Many relationships in the context of database design?

    <p>A one side entity can relate to zero or more records in a many side entity.</p> Signup and view all the answers

    Which of the following SQL statements correctly establishes a foreign key relationship in the given CREATE TABLE examples?

    <p>Both table creations effectively show foreign key relationships.</p> Signup and view all the answers

    What characteristic is common to the foreign key constraints in the provided SQL examples?

    <p>They denote relationships that require non-null values.</p> Signup and view all the answers

    What is a requirement for a scalar subquery used in the SELECT clause?

    <p>It must return only one column and one row.</p> Signup and view all the answers

    Which statement best describes the use of subqueries in SQL?

    <p>Subqueries can lead to less readable and efficient queries if used excessively.</p> Signup and view all the answers

    What should you consider for readability and maintainability when constructing SQL queries?

    <p>Employing copies, temporary tables, and views judiciously.</p> Signup and view all the answers

    In the context of nested queries, what role does the IN clause serve?

    <p>It allows filtering based on multiple values from another query.</p> Signup and view all the answers

    What is a common issue with deeply nested SQL queries?

    <p>They can make the maintenance of SQL queries challenging.</p> Signup and view all the answers

    How can common table expressions (CTEs) improve the readability of SQL queries?

    <p>By allowing complex queries to be broken down into simpler parts.</p> Signup and view all the answers

    Which of the following is NOT a recommended practice when using nested queries?

    <p>Opting for a single, highly nested query over multiple simpler ones.</p> Signup and view all the answers

    What is a potential downside of using nested queries in SQL?

    <p>Higher likelihood of encountering errors during execution.</p> Signup and view all the answers

    What does a common table expression (CTE) allow you to do in SQL?

    <p>Create a temporary result set for a specific query</p> Signup and view all the answers

    Which of the following optimally improves SQL query readability?

    <p>Organizing complex queries using subqueries or CTEs</p> Signup and view all the answers

    How can the use of views benefit data retrieval in SQL?

    <p>They can simplify complex joins and queries</p> Signup and view all the answers

    What drawback is associated with unmaterialized views in SQL?

    <p>They require frequent updates and maintenance</p> Signup and view all the answers

    What benefit does rewriting complex queries into simpler queries provide?

    <p>Enhances performance and maintainability</p> Signup and view all the answers

    What is a common practice when using subqueries in SQL?

    <p>Naming the subquery results for easy reference</p> Signup and view all the answers

    Which of the following best describes how a subquery functions within an SQL statement?

    <p>It is executed separately before the main query</p> Signup and view all the answers

    What is the purpose of using the 'AS' operator when defining a subquery in the FROM clause?

    <p>To rename the result set for easier reference</p> Signup and view all the answers

    What does the keyword EXISTS evaluate in a subquery context?

    <p>True if the subquery has at least one result</p> Signup and view all the answers

    Which of the following statements accurately describes a correlated subquery?

    <p>It refers to columns of the outer query in its WHERE clause</p> Signup and view all the answers

    Which SQL construct could improve clarity and understanding in complex queries?

    <p>Common Table Expressions (CTEs)</p> Signup and view all the answers

    What limitation is common with nested subqueries regarding optimization?

    <p>They may lead to performance issues if not properly structured</p> Signup and view all the answers

    When using subqueries, which keyword is crucial for checking both empty and non-empty results?

    <p>EXISTS</p> Signup and view all the answers

    Which scenario is more appropriate for using nested queries?

    <p>When conditions in the inner query depend on the outer query</p> Signup and view all the answers

    In SQL, which clause normally follows a SELECT statement in a nested query to limit results?

    <p>WHERE</p> Signup and view all the answers

    What is a potential downside of using nested queries compared to JOINs?

    <p>They may lead to multiple executions and increased processing time</p> Signup and view all the answers

    Study Notes

    Database Design

    • Database logical design involves determining the schema for a database application, including the number of tables, table names, number of columns per table, column names, and data types.
    • Relational database management systems organize data in tables.
    • Tables are multi-sets of rows or records, not lists.
    • Rows or records contain fields that correspond to the table's columns.
    • Columns or fields have a name and an implicit position determined by their order in creation statements.
    • Columns or fields have a domain, which is a type with the possibility of a NULL value.
    • Logical design involves selecting an appropriate schema for the database application.

    Constraints

    • A consistent state of the database adheres to the business rules defined by structural and integrity constraints in the schema.
    • SQL supports five types of integrity constraints: NOT NULL, PRIMARY KEY, UNIQUE, FOREIGN KEY, and CHECK.
    • A primary key is a set of columns that uniquely identifies a record in a table.
    • Each table can contain at most one primary key.
    • An instance of a table cannot have two records with the same value or combination of values in the primary key columns.
    • Primary key columns cannot contain null values.

    Update and Delete Actions

    • For more powerful generalization, triggers can be used.
    • ON UPDATE CASCADE can cascade the update or deletion to other tables using foreign key relationships.
    • Other options include NO ACTION, SET DEFAULT, and SET NULL, but these may cause chain reactions.
    • It is generally considered good practice to constrain all attributes to be not NULL unless there is a compelling design or tuning reason for not doing so.
    • It is generally considered good practice to defer all constraints that can be deferred.
    • DELETE FROM deletes the content of a table without altering its definition.
    • DROP TABLE deletes both the content and definition of a table.
    • DELETE is used to delete the content of a table, while DROP is used to delete both the content and definition of a table.

    Foreign Keys

    • ON DELETE CASCADE propagates the update or deletion to other tables.
    • A unique constraint on a column or combination of columns ensures that a table cannot contain two records with the same value in the corresponding column or combination of columns.
    • According to SQL standards, PRIMARY KEY is equivalent to UNIQUE and NOT NULL, but there are differences in side-effects such as foreign-key, index, deferrability, etc.

    Transactions

    • Transactions in SQL or application code can be specified using blocks with keywords such as BEGIN, END, COMMIT, ABORT, and ROLLBACK.
    • Integrity constraints are usually immediate (checked after each operation) but should ideally be deferred to be checked after the end of transactions.
    • Most systems, like PostgreSQL, only allow certain constraints to be deferred.

    Single Table Operations

    • Basic Operations
      • A simple SQL query includes SELECT, FROM, and WHERE clauses.
      • SELECT clause indicates the columns to be output.
      • FROM clause indicates the tables to be queried.
      • WHERE clause indicates the conditions on the records.
      • The asterisk (*) is a shorthand for selecting all columns.

    Duplicate Ordering

    • Ordering columns may result in duplicate rows.
    • Columns can be ordered in ascending (ASC) or descending (DESC) order.
    • Default order is ASC.
    • If ordering on all columns, the result is a distinct ordering
    • If only ordering on a subset of columns, the unmentioned columns are in no particular order.

    Boolean Operations

    • De Morgan's Law: ¬(A ∧ B) ≡ ¬A ∨ ¬B and ¬(A ∨ B) ≡ ¬A ∧ ¬B
    • The IN operator can be used to check if a value is within a set of values.
    • NOT operator negates a condition.
    • BETWEEN operator checks if the value is within a range.

    Joins

    • Outer Join
      • Conditions in the ON clause are not equivalent to conditions in the WHERE clause.
      • Conditions in the ON clause determine which rows are dangling.
    • LEFT JOIN is a synonym for LEFT OUTER JOIN
    • RIGHT JOIN is a synonym for RIGHT OUTER JOIN
    • FULL JOIN is a synonym for FULL OUTER JOIN

    Set Operators

    • UNION, INTERSECT, and EXCEPT return the union, intersection, and non-symmetric difference of the results of two queries, respectively.
    • Set operators eliminate duplicates unless annotated with the keyword ALL.

    Aggregation

    • Aggregate functions can be used in conditions, but not in WHERE clause.
    • Aggregate functions are evaluated after groups are formed.
    • HAVING clause is performed after GROUP BY clause.
    • HAVING clause can only use aggregate functions, columns listed in the GROUP BY clause, and subqueries.
    • The order of columns in GROUP BY clause does not change the meaning of the query.
    • The logical groups remain the same.

    Aggregation Functions

    • The following query does not work in PostgreSQL (but works in SQLite with potentially incorrect result).
      • SELECT c.customerid, c.first_name, c.last_name, SUM(g.price) FROM customers c, downloads d, games g WHERE c.customerid = d.customerid AND d.name = g.name and d.version = g.version GROUP BY c.first_name, c.last_name;
    • The above query works only because first_name and last_name are guaranteed unique.
      • Do NOT write such queries for readability and portability.

    Grouping

    • Renamed columns can be used in GROUP BY clause.
    • The following query displays the number of downloads by country and year of birth (using EXTRACT).
      • SELECT c.country, EXTRACT(YEAR FROM c.since) AS regyear, COUNT(*) AS total FROM customers c, downloads d WHERE c.customerid = d.customerid GROUP BY c.country, regyear ORDER BY regyear ASC, c.country ASC;

    Grouping

    • The order of columns in GROUP BY clause does not change the meaning of the query.
    • The logical groups remain the same.
      • SELECT c.country, EXTRACT(YEAR FROM c.since) AS regyear, COUNT(*) AS total FROM customers c, downloads d WHERE c.customerid = d.customerid GROUP BY regyear, c.country ORDER BY regyear ASC, c.country ASC;

    Having

    • SELECT c.country FROM customers c WHERE COUNT(*) >= 100 GROUP BY c.country;
    • The query on the left finds the countries in which there are more than 100 customers.

    Having

    • The following query on the left finds the countries in which there are more than 100 customers.
      • SELECT c.country FROM customers c GROUP BY c.country HAVING COUNT(*) >= 100;

    Creating Database Tables

    • Entities are mapped to relations.
    • Entity set attributes are mapped to attributes of the relation.
    • Keys are mapped to the primary key when creating tables.
    • Relationships are mapped to relations.
    • The attributes of the relation consist of the attributes of the relationship set.
    • The keys of the participating entities are also included.

    Exceptions to Rules

    • One-to-Many: One-to-many relationship sets, without a weak entity, can be represented as separate tables containing the participating entity keys.
    • Weak Entity: Weak entities are not represented with a primary key in the relationship table.
    • Entities in a one-to-many relationship with a weak entity can be represented with a single table containing the weak entity key as the primary key.
    • The primary key of a weak entity must be foreign key of the strong entity in the relationship.
    • The table represents the relationship, not the weak entity.
    • When representing one-to-many relationships with a weak entity and a non-weak entity, the table should include the keys for both entities.
    • The key in the table should be the combined key of the participating entities.
    • When representing one-to-many relationships with a weak entity and a non-weak entity, the primary key of the table is not the key of the weak entity.
    • The table should include the weak entity’s key.
    • When representing a relationship between a weak entity and its strong entity, the non-weak entity must be represented in a different table. This table's primary key should be a foreign key in the relationship table.

    Schema Translation Tables

    • company table:

      • name: VARCHAR(64), primary key
      • address: VARCHAR(128)

    • person table:

      • first_name: VARCHAR(32)
      • last_name: VARCHAR(32), primary key
      • address: VARCHAR(128) NOT NULL, primary key

    • contract table:

      • start: DATE NOT NULL, primary key
      • end: DATE NOT NULL, primary key
      • first_name: VARCHAR(32), primary key
      • last_name: VARCHAR(32), primary key
      • name: VARCHAR(64), primary key
      • Foreign key (first_name, last_name) references person table
      • Foreign key name references company table.

    • work_for table:

      • start: DATE, NOT NULL, primary key
      • end: DATE, NOT NULL, primary key
      • enumber: CHAR(8)
      • cname: VARCHAR(32)
      • Foreign key enumber references employee table.
      • Foreign key cname references company table.

    • employee_work_for table:

      • start: DATE, NOT NULL, primary key
      • end: DATE, NOT NULL, primary key
      • enumber: CHAR(8), primary key
      • ename: CHAR(32), NOT NULL
      • cname: VARCHAR(32), NOT NULL
      • Foreign key cname references company table.

    ### Splitting Query

    • Subqueries can be used in the SELECT, FROM, and WHERE clauses of a query.
    • Subqueries in the SELECT clause must return a single column and a single row.
    • Subqueries in the FROM clause can be used to create a temporary table for the query.
    • Subqueries in the WHERE clause can be used to filter the results of a query.
    • Common Table Expressions (CTEs) are temporary tables that only exist for the duration of a query.
    • Views are pre-compiled queries that can be used like tables.
    • Subqueries can be nested, meaning a subquery can be used within another subquery.

    Nesting Query

    • Subqueries can be used in the WHERE clause to filter the results of a query, using the IN, ANY, ALL, and EXISTS operators.
    • IN operator evaluates to true if the value in the outer query is found in the subquery.
    • ANY operator evaluates to true if at least one value in the subquery matches the value in the outer query.
    • ALL operator evaluates to true if all the values in the subquery match the value in the outer query.
    • EXISTS operator evaluates to true if the subquery returns at least one row.
    • Subqueries can be correlated, meaning that the subquery relates to the outer query, often through a common column.
    • Correlated subqueries are executed for each row of the outer query.
    • Subqueries can be used in the HAVING clause to filter the results of a grouped query.

    ### Query Performance

    • Queries with nested subqueries can be harder to read and understand than simpler queries.
    • Nested subqueries can be inefficient if the database needs to execute multiple queries to calculate the results.
    • It is often possible to rewrite nested subqueries as simpler queries that are easier to read and more efficient.
    • Consider alternative methods to achieve the same results, such as joins or CTEs.

    Studying That Suits You

    Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

    Quiz Team

    Related Documents

    L01.pdf
    L02.pdf
    L03.pdf
    L04.pdf
    L05.pdf

    Description

    This quiz covers essential concepts in database logical design, including schema creation, table structures, and integrity constraints. You'll explore key elements like primary keys and data types, vital for relational database management systems.

    More Like This

    Use Quizgecko on...
    Browser
    Open
    Browser
    Browser