Define each of the following terms:
- dynamic view
- correlated subquery
- materialized view
- base table
- Join
- equi-join
- self join
- outer join
- virtualized table
(a)
Define Dynamic View.
Explanation of Solution
Dynamic view is not a temporary table and its definition is stored in the system catalog. The content of this view is materialized as a result of an SQL query that uses the view.
(b)
Define Correlated Subquery.
Explanation of Solution
In SQL, Correlated subquery is a subquery that processes the inner query depending on the data from the outer query.
(c)
Define Materialized View.
Explanation of Solution
Materialized view is different from the dynamic view. It is stored on a disk and is refreshed when used or at some intervals. Copies of data is created in the same way as dynamic view.
(d)
Define Base Table.
Explanation of Solution
Base Table is a table from relational data model that contains the inserted raw data. These tables are correspond to those relations which are identified in the conceptual schema of database.
(e)
Define Join.
Explanation of Solution
Join is a relational operation that is used to combine two tables with a common domain into a single view or table.
(f)
Define Equi Join.
Explanation of Solution
Equi-join is a form of join in which the joining is performed on the basis of the equality between values in the columns that are common. Those common columns appear in the result table redundantly.
(g)
Define Self Join.
Explanation of Solution
Sometimes a join requires match of rows in a table with other rows in that same table which means joining of table with itself. This is known as self join.
(h)
Define Outer Join.
Explanation of Solution
Outer join is a form of join in which those rows are not included in the resulting table that do not have similar values in the common columns.
(i)
Define Virtualized Table.
Explanation of Solution
Virtualized tables are not maintained as the tables with real data. These are constructed automatically as the requirement of a DBMS.
Want to see more full solutions like this?
Chapter 6 Solutions
Modern Database Management
Additional Engineering Textbook Solutions
Introduction to Java Programming and Data Structures, Comprehensive Version (11th Edition)
Concepts Of Programming Languages
Modern Database Management (12th Edition)
Computer Science: An Overview (13th Edition) (What's New in Computer Science)
Computer Systems: A Programmer's Perspective (3rd Edition)
- Using tables named T1 and T2, write a query example for each of the three join types you described in Question 2. Assume that T1 and T2 share a common column named Cl.arrow_forwardWhat three join types are included in the outer join classification?arrow_forwardWhat is a cross join? Give an example of its syntax.arrow_forward
- When part of the data in a table depends on a field in the table that isn’t the table’s primary key, it’s known as: transitive dependency partial dependency psychological dependency a foreign keyarrow_forwardA table can have a maximum of how many PRIMARY KEY constraints?arrow_forwardWhat is a relation?arrow_forward
- Define second normal form. What types of problems might you encounter using tables that are not in second normal form?arrow_forwardHow is adding a NOT NULL constraint to an existing table different from adding other types of constraints?arrow_forwardAccess path A database table is composed of records and fields hold data. Data is stored in records. A field is a part of a record and contains a single piece of data for the subject of the record. The access path for finding the list of books is AUTHOR: AuthorID, Lname BOOKAUTHOR: AuthorID, ISBN BOOKS: Pubdate, ISBNarrow_forward
- 1arrow_forwardImplement a new independent entity phone in the Sakila database. Attributes and relationships are shown in the diagram below The diagram uses Sakila naming conventions. Follow the Sakila conventions for your table and column names: All lower case Underscore separator between root and suffix Foreign keys have the same name as referenced primary key Write CREATE TABLE and ALTER TABLE statements that: Implement the entity as a new phone table. Implement the has relationships as foreign keys in the Sakila customer, staff, and store tables. Remove the existing phone column from the Sakila address table. Step 2 requires adding a foreign key constraint to an existing table. Ex: ALTER TABLE customer ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE; Specify data types as follows: phone_id, phone_number, and country_code have data type INT. phone_type has date type VARCHAR(12) and contains strings like 'Home', 'Mobile', and 'Other'. Apply these…arrow_forwardAssignment Brief: Some of the most difficult decisions that you face as a database developer are what tables to create and what columns to place in each table, as well as how to relate the tables that you create. Normalization is the process of applying a series of rules to ensure that your database achieves optimal structure. Normal forms are a progression of these rules. Each successive normal form achieves a better database design than the previous form did. Based on your understanding for the various normal form respond to the following problems and provide the optimal answer/solution. Problem#1: 1st Normal Form (1NF) Consider the Students table, with the primary key underlined, and the following data: Students: Alpha 100111 Email doe@usna.edu Name Courses GradePoints NN204, SI204, IT221 SM223, EE301 SI204 John Doe 2,3,3 Matt Smith 092244 113221 smith@usna.edu black@usna.edu 4,4 Melinda 3 Black NN204, SI204, IT221 090112 Tom Johnson Johnson@usna.ed 4,2,3 u a) Is the Students table…arrow_forward
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology PtrDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning
- Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage LearningProgramming with Microsoft Visual Basic 2017Computer ScienceISBN:9781337102124Author:Diane ZakPublisher:Cengage Learning