CS430

Date is an example of which type of attribute. Simple attribute. Composite attribute. Multivalued attribute. Derived attribute.

Composite attribute.

The _________________ command is used to insert data into a relation. (fill in the blank)

INSERT INTO

What is canonical cover? How is it used?

- A canonical cover is a simplified and reduced version of the given set of functional dependencies. Since it is a reduced version, it is also called as Irreducible set. - In case of a violation of functional dependencies in the new database state, the rollback of the system must take place. A canonical cover or irreducible a set of functional dependencies FD is a simplified set of FD that has a similar closure as the original set FD.

What is a functional dependency? What does it mean for a FD to hold on a relation? When an instance of a relation satisfy a set of FDs?

- A functional dependency (FD) is a relationship between two attributes, typically between the PK and other non-key attributes within a table. - A functional dependency A->B in a relation holds if two tuples having same value of attribute A also have same value for attribute B - If every instance of a relation will make a FD true, then the relation satisfies the FD

What is BCNF? What defines a relation being in BCNF? Is a decomposition into BCNF always lossless? Dependency preserving?

- BCNF is the usual objective of the database designer; BCNF is based on the notions of candidate key (CK) and functional dependency (FD). - A relation R is in BCNF if R is in 1NF and every determinant of a non-trivial functional dependency in R is a candidate key. - BCNF is NOT always lossless - does not ensure dependency preservation

What is the ER model? What are the 3 basic concepts of the ER model?

- Entity Relationship Model (ER Modeling) is a graphical approach to database design - Concepts: entities, attributes and relationships

Match the component of a DBMS to its associated responsibility. - Query optimizer - File and access methods layer - Buffer manager - Disk space manager - Transaction manager - Lock manager - Recovery manager

- Query optimizer: Uses information about how the data is stored to produce an efficient execution plan for evaluating the query. - File and access methods layer: Supports the concept of a file, which in a DBMS is a collection of pages or a collection of records. - Buffer manager: Brings pages in from disk to memory as needed in respons to read requests. - Disk space manager: Deals with management of space on disk, where the data is stored. - Transaction manager: Ensures that transactions request and release locks according to a suitable locking protocol. - Lock manager: Keeps track of requests for locks and grants locks on database objects when they become available. - Recovery manager: Responsible for maintaining a log and restoring the system to a consistent state after a crash.

What is RAID 0? RAID 1? RAID 5?

- RAID 0 (disk striping) is the process of dividing a body of data into blocks and spreading the data blocks across multiple storage devices Fastest - RAID 1 system is probably one of the simplest. It works on the principle of mirroring. In other words, the disks in the cluster are organized in pairs. - RAID 5 is a redundant array of independent disks configuration that uses disk striping with parity. Because data and parity are striped evenly across all of the disks, no single disk is a bottleneck. Striping also allows users to reconstruct data in case of a disk failure.

Match the component of an E-R diagram to its description. - Rectangles - Items within the rectangles - Diamonds - Lines - Double line - Line with arrow - Double line rectangle w/ associated double line diamond

- Rectangles: Entity sets. - Items within the rectangles: Attributes. - Diamonds: Relationship sets. - Lines: Link attributes to entity sets and entity sets to relationship sets. - Double line: Shows total participation of the entity in the relationship - Line with arrow: Shows a key constraint from the entity to the relationship - Double line rectangle w/ associated double line diamond: Weak entity set

Match the term used in relationship sets to its definition. - Relationship - Relationship set - Participation - Relationship instance

- Relationship: An association among several entities. - Relationship set: A set of associations among several entities of the same type. - Participation: When an entity set is a part of a relationship set. - Relationship instance: In an E-R schema, this represents an association between the named entities in the real world enterprise being modeled.

The types of access for a data manipulation language are: - Retrieval of information - Defining domain contraints - Establishing referential integrity constraints - Insertion and deletion of data

- Retrieval of information - Insertion and deletion of data

Match the elements below to their position in the storage-device hierarchy, according to their speed. 1-Fastest 2 3 4 5-Slowest

1-Fastest: cache 2: main memory 3: flash memory 4: disk 5-Slowest: magnetic tapes

The _________________ command is used to insert data into a relation.

INSERT INTO

Match the join type to the expected effect in the resulting relation. - Right outer join - Left outer join - Full outer join

- Right outer join: Tuples from the right-hand-side relation that do not match any tuple in the left-hand-side relation are padded with nulls. - Left outer join: Tuples from the left-hand-side relation that do not match any tuple in the right-hand-side relation are padded with nulls. - Full outer join: Both the left and the right side relations are extended and filled with nulls for the non-existent values.

What are some of the optimizations of disk block access?

- Scheduling - File organization - Nonvolatile write buffers - Log disk

Match the definitions - seek time - rotational delay - transfer time

- Seek time: the time it takes the head to move from the current track to where the data is - Rotational delay: the time it takes to get the correct sector - Transfer time: the time it tasks to transfer data from the disk to the host system

Match the attribute type to its desciption. - Simple attributes - Composite attributes - Single-valued attribute - Multi-valued attribute - Derived attribute

- Simple attributes: Attributes that have not been subdivided into sub-parts. - Composite attributes: Attributes that are divided into other attributes. - Single-valued attribute: Attributes that have a single value for a particular entity. - Multi-valued attribute: Attributes that have a set of values for a particular entity. - Derived attribute: An attribute whose value is computed from the values of other attributes.

What is the closure of a set of FDs? Of an attribute?

- The closure of F, denoted as F+, is the set of all regular FDs that can be derived from F. - The closure of a set of attributes X is the set of those attributes that can be functionally determined from X. The closure of X is denoted as X+

Views are considered updatable if which of the following conditions are satisfied: - The from clause only had one database relation - The select clause contains only attribute names of the relation and does not have an expressions, aggregates or distinct specification. - Any attribute not listed in the select clause can be set to null. - The query has group by or having clauses.

- The from clause only had one database relation - The select clause contains only attribute names of the relation and does not have an expressions, aggregates or distinct specification. - Any attribute not listed in the select clause can be set to null.

Which of the following about a database table are stored in the system catalog? The integrity constraints on the table Attribute name and type for each attribute in the table The JDBC connector information The name of the table The file structure for the table

- The integrity constraints on the table - Attribute name and type for each attribute in the table - The name of the table - The file structure for the table

Which of the following are disadvantages to file processing systems? - We must protect the data from inconsistent changes made by different users accessing the data concurrently. - We need special programs to answer each question a user may want to ask about the data. - We must ensure the data is restored to a consistent state if the system cashes while changes are being made. - File systems can routinely handle all the data in main memory. - Operating systems provide only a password mechanism for access control.

- We must protect the data from inconsistent changes made by different users accessing the data concurrently. - We need special programs to answer each question a user may want to ask about the data. - We must ensure the data is restored to a consistent state if the system cashes while changes are being made. - Operating systems provide only a password mechanism for access control.

Match with definitions - attribute - domain - entity - relationship - entity set - relationship set - one-to-many relationship - many-to-many relationship - participation constraint (minimal cardinality constraint) - overlap constraint - covering constraint - weak entity set - aggregation - role indicator

- attribute: descriptive properties possessed by each member of an entity set - domain: a set of permitted values - entity: a thing/object in the real world that is distinguishable from all other objects - relationship: an association among several entities - entity set: a set of objects of the same type that shared the same properties - relationship set: a set of associations among several entities of the same type - one-to-many relationship: the primary key of the 'many' is a minimal superkey and is used as the primary key - many-to-many relationship: the preceding union of the primary keys are minimal superkeys and are chosen as the primary keys - participation constraint (minimal cardinality constraint): specifies the number of instances of an entity that are participating in the relationship type - overlap constraint: determines whether or not two subclasses can contain the same entity - covering constraint: determines where the entities in the subclasses collectively include all entities in the superclass - weak entity set: an entity that is dependent on another entity (its identifying entity) and cannot be uniquely identified by its attributes alone - aggregation: an abstraction through which relationships are treated as higher-level entities - role indicator: describe different purposes in a relationship if an entity set has more than one role

Match the type of key to its definition. - superkey - candidate key - primary key - foreign key

- superkey: a set of one or more attributes that allow us to identify an entity in the entity set. - candidate key: A superkey that contains no extraneous attributes. - primary key: A candidate key that is chosen by the database designer as the principal means of identifying entities within an entity set. - foreign key: An attribute in one relation that references the primary key of another relation.

What is hot swapping? What can you do to reduce swap time?

- the process of removing and replacing a board or module while the system remains up and running. - you can manually disable swap usage of any kind through the terminal

Consider the following collection of relations and dependencies. Assume that each relation is obtained through decomposition from a relation with attributes ABCDEFGHI and that all the known dependencies over relation ABCDEFGHI are listed for each question. (The questions are independent of each other.) For each (sub)relation: (1) State the strongest normal form that the relation is in. (2) If it is not in BCNF, decompose it into a collection of BCNF relations. 1. R1(A,C,B,D,E), A → B, C → D 2. R2(A,B,F), AC → E, B → F 3. R3(A,D,G), D → G, G → H 4. R4(D,C,H,G), A → I, I → A 5. R5(A,I,C,E)

1. 1NF only. Decomposition: AB, CD, ACE 2. 1NF only. Decomposition: AB, BF 3. in BCNF 4. in BCNF 5. in BCNF

Consider a disk with a sector size of 512 bytes, 2000 tracks per surface, 50 sectors per track, five double-sided platters, and average seek time of 10 msec. 1. What is the capacity of a track in bytes? 2. What is the capacity of each surface? 3. What is the capacity of the disk? 4. How many cylinders does the disk have? 5. Give examples of valid block sizes. Is 256 bytes a valid block size? 2048? 51200? 6. If the disk platters rotate at 5400 rpm (revolutions per minute), what is the maximum rotational delay? 7. If one track of data can be transferred per revolution, what is the transfer rate?

1. 25 K 2. 50,000 K 3. 500,000 K 4. 2,000 5. 2048 is a valid block size while 256 and 51200 are not because the block size cannot exceed the track size and be a multiple of the sector size. 6. 0.011 seconds 7. 2,250 Kbytes/sec

Consider a relation R with five attributes ABCDE. You are given the following dependencies: A → B, BC → E, and ED → A. 1. List all keys for R. 2. Is R in 3NF? 3. Is R in BCNF?

1. ACD, BCD, ECD 2. Yes, because all are parts of keys 3. No, because A, BC, and ED do not have keys

Suppose that we pick the following three tuples from a legal instance of a relation S (S has 100 tuples in total). Relation S has the following schema: (A : integer, B : integer, C : integer). The three tuples are: (1,2,3), (4,2,3), and (5,3,3). 1. Which of the following dependencies can you infer does not hold over S? A → B, BC → A, C → B 2. Can you identify any dependencies that hold over S?

1. BC → A 2. No, because although dependencies are not violated they may not be held with respect to S

A relation schema R is in third normal form with respecto to set F of functional dependencies, if for all functional dependencies in F+ of the form A -> B where A is a subset of R, B is an attribute of R, and at least on e of the following holds: A -> B is a trivial functional dependency A is a superkey for R. B is part of a key for R. B is a superkey for R. A is part of a key for R.

A -> B is a trivial functional dependency A is a superkey for R. B is part of a key for R.

A relation schema R is in BCNF with respect to a set F of functional dependencies if, for all functional dependencies in F+ of the form A -> B, where A is a subset of R, B is a subset of R - and at least one of the following hold: A -> B is a trivial functional dependency (that is, B is a subset of A) A is a superkey for schema R. A -> B is an atomic dependency on R. B is a superkey for schema R. A and B are both atomic.

A -> B is a trivial functional dependency (that is, B is a subset of A)

What is a block with respect to database accesses?

A data block is the smallest unit of data used by a database.

A language in which a user specifies a database schema is called: - A structured language. - A data definition language - COBOL. - Object-oriented language. - A data manipulation language

A data definition language

What is the ditty for 1NF?

A relation is said to be in 1NF (first normal form), if it doesn't contain any multi-valued attribute. In other words you can say that a relation is in 1NF if each attribute contains only atomic(single) value only. As per the rule of first normal form, an attribute (column) of a table cannot hold multiple values. It should hold only atomic values.

What is the ditty for 3NF?

A table design is said to be in 3NF if both the following conditions hold: Table must be in 2NF Transitive functional dependency of non-prime attribute on any super key should be removed. An attribute that is not part of any candidate key is known as non-prime attribute.

What is the ditty for 2NF?

A table is said to be in 2NF if both the following conditions hold: Table is in 1NF (First normal form) No non-prime attribute is dependent on the proper subset of any candidate key of table. An attribute that is not part of any candidate key is known as non-prime attribute.

One very important advantage of using a DBMS is that it offers data independence. This is defined as what? - Application programs are insulated from changes in the way data is structured and stored. - Actual data within the database is stored independently from the disk drives. - Commonly used terms in the database.

Application programs are insulated from changes in the way data is structured and stored.

Match the type of data storage to its definition. Cache Main memory Magnetic-disk storage Tape Storage

Cache: The fastest and most costly form of storage. Main memory: The storage medium used for data that is available to be operated on. Magnetic-disk storage: The primary medium for long-term on-line storage of data. Tape Storage: Used primarily as a backup storage medium.

A snapshot of the data in a database at a given time is called which of the following? Database instance Database schema Relation schema Relation instance

Database instance

A transaction is a single operation that performs multiple logical functions within a database. (T/F)

False

An assertion is a predicate expressing a condition that you want to ensure never occurs within the database. (T/F)

False

An attribute takes a null value when the value of the attribute is computed to be zero. (T/F)

False

An object-oriented data model permits the specification of data where individual data items of the same type many have different sets of attributes. (T/F)

False

It is not possible for several attributes to have the same domain, we require they be unique. (T/F)

False

Relational schemas and other metadata about relations are stored in a structure called system cache. (T/F)

False

The DDL interpreter of the query processor is responsible for query optimization. (T/F)

False

The ORDER BY clause lists items in descending order by default. (T/F)

False

The order of how a tuple appears in a relations is extremely important as that is what determines the primary key. (T/F)

False

The result of an SQL query is a single value. (T/F)

False

We can disallow null values by specifying NOT NULL as a part of the field definition. This MUST be specified when declaring the primary key. (T/F)

False

We create a table in SQL using the insert table command. (T/F)

False

Match each of the following functional dependency rules of inference with the name of the Armstrong Axiom or Implication to which it belongs. If Y is a subset of X, then X -> Y If X -> Y and Y -> Z, then X -> Z If X -> Y and Z is a set of attributes, then XZ -> YZ (or XunionZ -> YunionZ) If X -> Y and X -> Z, then X-> YZ If X-> YZ, then X -> Y and X -> Z

If Y is a subset of X, then X -> Y: Rule of Reflexity If X -> Y and Y -> Z, then X -> Z: Rule of Transitivity If X -> Y and Z is a set of attributes, then XZ -> YZ (or XunionZ -> YunionZ): Rule of Augmentation (Unions) If X -> Y and X -> Z, then X-> YZ: Rule of Union (Additive) If X-> YZ, then X -> Y and X -> Z: Rule of Decomposition (Projection)

Which of the following defines a functional dependency? If there were a schema (A,B), then A is able to serve as a primary key. For schema A, primary key B exists. For schema B, primary key A exists. If there were a schema A, two primary keys are necessary to identify it. If there exists a schema A, primary key B is a candidate key.

If there were a schema (A,B), then A is able to serve as a primary key.

Which buffer replacement policy is optimal for a database system in all scenarios? Least Recently Used (LRU) It depends - no one strategy is always optimal. Most Recently Used (MRU) Toss Immediately (TI)

It depends - no one strategy is always optimal.

In SQL, we can using pattern matching to match strings. The pattern '__% Cubs' does which of the following (note, there are 2 underline characters before the %): - Matches any string of at least 2 characters followed by a space and Cubs. - Matches any string of exactly 3 characters followed by a space and Cubs - Matches any string of exactly 3 characters preceeded by a space and Cubs. - Matches any string ending in % and followed by a space and Cubs - Matches any string preceeded by Ernie Banks.

Matches any string of at least 2 characters followed by a space and Cubs.

In which phases does the design move from an abstract data mode to the design of the database - No answer text provided. - Physical design phase - Logical design phase - Conceptual design phase

Physical design phase Logical design phase

Which of the following are shown in a schema diagram: Primary keys Foreign key dependencies The database schema Referential integrity constraints

Primary keys Foreign key dependencies The database schema Referential integrity constraints

What are the three basic clauses of a SELECT statement? SELECT clause FROM clause WHERE clause KEY clause SANTA clause

SELECT clause FROM clause WHERE clause

Do the queries below return the same result? SELECT name, employee_deptFROM employee, emp_deptWHERE employee.id = emp_dept.id; SELECT name, employee_deptFROM employee NATURAL JOIN emp_dept; - Same - Different

Same

A database schema is depicted by which of the following? Schema diagram Relation diagram Schema descriptions Relation descriptions

Schema diagram

Match the disk movement term to its appropriate description. Seek time Rotational delay Transfer time

Seek time: Moving arms to postition the disk head on track Rotational delay: Time for the block to rotate under the head Transfer time: Time to move the data to/from the surface

What describes the kinds of operations or transactions that will be performed in the database? Logical design phase Specification of functional requirements Conceptual design phase Physical design phase

Specification of functional requirements

Explain what the buffer manager must do to process a read request for a page. What happens if the requested page is in the pool but not pinned?

The buffer manager processes a read request for a page and will allocate/deallocate the pages on the disk and bring up the bufferpage into poll then pin it to the disk. If the page is in the pool but not pinned then the requed page is fetched from the DB but is unable to display.

What performance characteristics do we use for disks?

The main measures of the qualities of a disk are capacity, access time, data transfer rate, and reliability,

With respect to mag disks, what are sectors? Tracks? Platters? Cylinders?

The surfaces are usually divided into concentric rings, called tracks, and these in turn are divided into sectors. Platter (or disk) is the circular disk on which magnetic data is stored in a hard disk drive. A cylinder is any set of all of tracks of equal diameter in a hard disk drive

What is the difference between volatile and non-volatile memory?

The volatile memory stores data and computer programs that the CPU may need in real-time, and it erases them once a user switches off the computer. Non-volatile memory, on the other hand, is static. It remains in a computer even after a user switches it off.

We create views for which of the following purposes? To provide a restricted set of tables to a set of users. To provide a simplified view of the database for users. To oversee all the activities of a database. To overlook Poudre Canyon.

To provide a restricted set of tables to a set of users. To provide a simplified view of the database for users.

A file is a collection of pages each containing a collection of records. Support must include inserts, deletes, modifies, and reads among other operations. (T/F)

True

A foreign key is the primary key of another relation schema.

True

A trigger is a procedure that is automatically invoked by the DBMS in response to a specified change to the database. (T/F)

True

Aggregation is an abstraction through which relationships are treated as higher level entities. (T/F)

True

An index on an attribute of a relation is a data structure that allows the database system to find those tuples in the relation that have a specified value for that attribute efficiently. (T/F)

True

For each attribute, there is a set of permitted values called the domain or the value set. (T/F)

True

For two relation instances to be union compatible, the must have the same number of fields and the corresponding fields must have the same domain. (T/F)

True

It is always possible to decompose a relation into 3NF form that is both lossless join and dependency preserving. (T/F)

True

SQL allows the use of null values to indicate absence of information about the value of an attribute. (T/F)

True

The HAVING clause, if it is present, is applied to each group of the GROUP BY clause. (T/F)

True

The output of a relational algebra operation is a single relation. (T/F)

True

View names may appear in a query any place where a relation name may appear. (T/F)

True

We define a view in SQL by using the create view command.

True

When using a heap file organization, any record can be placed anywhere in the file where there is space for the record. (T/F)

True

Lossless join decomposition

a decomposition of a relation R into relations R_{1}, R_{2} such that a natural join of the two smaller relations yields back the original relation. This is central in removing redundancy safely from databases while preserving the original data.

First Normal Form

a property of a relation in a relational database. A relation is in first normal form if and only if no attribute domain has relations as elements. Or more informally, that no table column can have tables as values

A domain is _____________________ if the elements of the domain are considered to be indivisible units. atomic trivial derived syntactic composite

atomic

The elements within a(n) ___________________ domain are not divisible. composite descriptive trivial atomic derived

atomic

A ___________________ is a minimal superkey. candidate key superkey composite key skeleton key trivial key

candidate key

If a birthdate attribute has three components (birthDay, birthMonth, birthYear), it could be treated as a ____________________ attribute. trivial multi-valued composite descriptive derived

composite

A ___________ nested query is a nested query where the inner query is dependent on the row being examined by the outer query.

correlated

A ___________ nested query is a nested query where the inner query is dependent on the row being examined by the outer query. (fill in the blank)

correlated

Below is the pseudocode for the BCNF _______________ algorithm. result := {R}; done := false; compute F+; while (not done) do if (there is a schema R, in result that is not in BCNF) then begin let A-> B be a non-trivial functional dependency that holds on R,such that A -> R is not in F+ and A intersection B = the empty set;result := (result = Ri) U (Ri-B) U (A,B); end else done := true;

decomposition

A triggering event can be which of the following: insert delete update select create

insert delete update

If a phoneNumber attribute could include more than one phone number, it could be treated as a ____________________ attribute. composite derived trivial multi-valued descriptive

multi-valued

One domain value that is a member of any possible domain is what? Zero. One. null. Infinity. Negative infinity.

null.

A ___________________ is a candidate key. primary key superkey composite key skeleton key trivial key

primary key

The ______________ stores information on a sector magnetically, and there is one for each side of each platter of a disk. track spindle cylinder read-write head arm platter sector

read-write head

The ______________s are kept as close as possible to the disk surface to increase the recording density. track read-write head sector platter arm cylinder spindle

read-write head

A __________________ is a set of one or more attributes that uniquely identify an entity (or tuple). superkey candidate key composite key skeleton key trivial key

superkey

The cardinality of a database relation is ______________________. the number of attributes in the table the number of related functional dependencies the number of tuples in the table the number of relationships in which the entity is involved a red bird

the number of relationships in which the entity is involved

Why do we use different tiers of the storage hierarchy?

to reduce overall storage costs and improve the performance and availability of mission-critical applications

A ____________________ is a statement that the system executes automatically as a side effect of a database modification. privilege assertion constraint check clause trigger

trigger

Which of the following are set operations within SQL? union intersect except order by distinct

union intersect except